MySQLDump – How to Ignore Errors During Backup

There are options to help you in this:

  --insert-ignore     Insert rows with INSERT IGNORE.
  --replace           Use REPLACE INTO instead of INSERT INTO.
  -t, --no-create-info
                      Don't write table creation info.

Keep this paradigm in mind

• mysqldump everything from DB1 into DUMP1
• load DUMP1 into DB3
• mysqldump everything from DB2 using --replace (or --insert-ignore) and --no-create-info into DUMP2
• load DUMP2 into DB3

For your dump command, you could use the following

mysqldump --single-transaction --extended-insert -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz

Extended inserts are better.

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction The Single Transaction option is better for dumping InnoDB tables.

In your my.cnf file, make the following changes temporarily

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_doublewrite

innodb_doublewrite=0

and also

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit = 2

Restart MySQL and then remove these options after your import and restart MySQL again.

Memory Tables

If you're entire database could fit into memory, you could change the ENGINE=InnoDB to ENGINE=MEMORY in your dump sql file for every table or just the tables that are taking the longest to import, import the dump file back into your database and then change the engine back to innodb.

ALTER TABLE name_of_table ENGINE=InnoDB;

mysqlimport command

http://linux.die.net/man/1/mysqlimport

I have used mysqlimport to import data backed up using the tab format option in mysqldump. It has proven faster than importing via the mysql command.

Dump the database using mysqldump

mysqldump --tab=/some_directory/ -u [USERNAME] -p [DBNAME] [TABLENAME]

Import the data into your database.

mysqlimport -u [USERNAME] -p [DBNAME] /some_directory/tablename.sql