MySQL – Risks of Using ‘Single-Transaction’ and ‘Extended-Insert’ on mysqldump

importMySQLmysqldump

I'm new in db administration and I've looked for a faster import of a mysqldump.

I had a db with about 1.1 million rows and mostly InnoDB tables and wanted this to import on a managed server. Before I used the standard mysqldump command for dumping the db. At import I had a write speed of about 60.000 rows per hour which was extremly slow.

I searched for a way to speed thingsup and found a post here saying to use –single-transaction and –extended-insert on mysqldump. Now the dump is importing in under 30 seconds.

After a quick overview it seems that the data is correctly imported but I'm a little bit leery about the fast import.

Why is this soo fast after adding these two options?
Are there any disadvantages on using this options?

Thank you for your help.

Best Answer

this way is faster because

Server does not need to perform fsync() multiple times during data import. fsync is the operation of submitting data into disk
Extended insert enables sequential write to disk which means disk does not need to seek for a locations for every row.
If the table has Auto_Increment values, Server can spare adequate values for the batch insertion at once rather than once for every insertion.
The extended insert builds batch INSERT statements (many rows per INSERT). That inherently speeds things up 10-fold because of amortizing the statement overhead (Added By @Rick James in comments)

I know two side effects for this method.

undo log will get too large while performing this operation.
If an error happen during the operation, the entire import will be reverted (which might be an advantage in your case).

Related Solutions

MySQL – Import Dump Replaced Existing Records

The mysqldump, by default, will drop the table. You should specified the --no-create-info option like this:

mysqldump -u... -p... --no-create-info --skip-extended-insert mydb t1 > mydb_table.sql

That way, you only have inserts to deal with. Using --skip-extended-insert will insert one row at a time. This help deal with duplicate issues, but you will have import like this:

mysql -u...-p... --force mydb < mydb_tables.sql

The --force option is for the sole purpose of continuing INSERTs in the event a duplicate key is encountered. In that instance, the offending INSERT's error is ignored and on to the next INSERT.

Thesqldump: flush-privileges option

The documentation is misleading. I read it exactly the same way you do, which isn't what the utility does.

Adding --flush-privileges causes mysqldump to include the following in the backup file, after dumping the mysql schema...

--
-- Flush Grant Tables
--

/*! FLUSH PRIVILEGES */;

...which of course causes the server where the dump is being restored to re-read the potentially-changed grant tables.

And, that's all it does.

Confirmation of this can be found in the source code for mysqldump "10.13" (the version bundled with MySQL Server 5.5.30):

if (flush_privileges && using_mysql_db == 0)
{
  fprintf(md_result_file,"\n--\n-- Flush Grant Tables \n--\n");
  fprintf(md_result_file,"\n/*! FLUSH PRIVILEGES */;\n");
}

The using_mysql_db == 0 comparison was confusing at first, but it turns out to be the return value from a string comparison function where "0" means "identical."

Best Answer

Related Solutions

MySQL – Import Dump Replaced Existing Records

Thesqldump: flush-privileges option

Related Question