Importing a database dump created with --single-transaction does not result in single-transaction import of the file
Hello,
I'm not sure that this qualifies as a "Newbie" question, but this forum seems to lack a more appropriate category.
I have a handful of tables, totaling 150 MB in size, that I need to dump and then import into another server on a regular basis.
The destination server is a production server on which zero downtime while replacing the tables is a must.
I thought that enclosing the dump in a single transaction would serve the intended purpose. However, in practice, each table appears to be emptied before the data is imported, which causes the table to contain incomplete data for as long as is required to import the data in its entirety. This occurs one table at a time. To summarize:
1.) Table is dropped and recreated.
2.) Data is inserted; this process requires several minutes, during which time the the number of rows in the table increases "in real-time", as the data is imported.
3.) The process is repeated for the next table.
In essence, it seems that START TRANSACTION is being ignored.
I see nothing on the relevant manual page (
http://dev.mysql.com/doc/refman/5.1/en/commit.html ) that explains this behavior when using AUTOCOMMIT=0, START TRANSACTION, and COMMIT.
The structure of the dump file is as follows:
-----------------------------------------------
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT=0;
START TRANSACTION;
SET time_zone = "+00:00";
DROP TABLE IF EXISTS `table`;
CREATE TABLE IF NOT EXISTS `table` ([...]);
INSERT INTO `table` ([...]) VALUES ([...]);
COMMIT;
-----------------------------------------------
The dumps are created with the following command:
mysqldump --add-drop-table --default-character-set=utf8 --extended-insert --host=localhost --quick --quote-names --routines --set-charset --single-transaction --triggers --tz-utc --verbose --user=root --password=password mydbname
All tables are InnoDB.
MySQL version is 5.1.66.
Thanks for any help!