I am taking daily mysqldump on my servers. I am always tring to speed up MySQL restore and backup using some tricks. Many times restoring mysql backup is like big pain. There will be many types of issues with restoring mysql dump to mysql server.
How can I speed up MySQL restore and backup?
Here is my trick to restore database dump fast.
You need to put following sql statement at the top of the dump SQL file.
SET @OLD_AUTOCOMMIT=@@AUTOCOMMIT, AUTOCOMMIT = 0;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS = 0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0;
You need to put following sql statement at the end of the SQL file
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET AUTOCOMMIT = @OLD_AUTOCOMMIT;
COMMIT;
Using above trick, I am able to restore any database much faster than earlier.
For more information you can look following URL:
http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html
Lets talk about taking mysqldump faster.
There is utility called “MYDUMPER” which will take mysqldump much faster than mysqldump command. which is native command of mysql.
“Mydumper” and its companion “myloader”, are relatively new open
source database utilities written by Domas Mituzas and colleagues, providing
built-from-scratch implementations of mysqldump and mysql load
functionality. They clean up a bunch of command line inconsistencies
found in the mysql utilities, making them easier to script. They also fix
some errors and omissions. But their main feature is speed. By
paralleling the operation of dumping and loading database tables, quite dramatic
speed-up can be obtained.
The new Mydumper 0.9.1 version, which includes many new features and bug fixes, is now available. You can download the code from here.
https://launchpad.net/mydumper
