Thursday, August 19, 2010
In MYSQL, as well as most database engines, restoring a backup, or importing from an existing dump file can take a long time depending on the number of indexes and primary keys you have on each table. You can speed this process up dramatically by modifying your original dump file by surrounding it with the following:
SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;
.. your dump file ..
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;
This will force MySQL to not commit until all rows have been loaded, as well as skip all foreign keys checks. Only skip these checks if you are 100% sure that no constraint is violated. This will usually be the case when dumping from one table and inserting into another.
Filed in MySQL, Restore Backup
|
Also tagged backup, COMMIT, constraint, database, database engines, Dump, Foreign, long time, MySQL, performance, table
|
Here’s how to pass the arguments to MySQL to restore from a Dump:
mysql [database name] < [backup file name]
The “[database name]” specifies the name of the database to restore, and “[backup file name]” specifies the full path and filename to the file generated by mysqldump, such as backup.sql. You should also specify your MySQL username and password as usual using -uroot -ppassword.
If I wanted to restore the database named maindatabase from the file named backup.sql, I would run:
mysql maindatabase< backup.sql
Filed in MySQL, Restore Backup
|
Also tagged backup, backup file, backup sql, database, database name, Dump, MySQL, mysqldump, password, sql, username
|