2

I have a Ubuntu 8.04 machine which has around 300 GB size mysql databases. I have dumped all the databases using the mysqldump command as below.

mysqldump -u root -p --all-databases > file.sql

Now, in the RHEL6 machine, I am trying to restore the mysql databases using:

mysql -u root -p < file.sql

However, the above command seems to take so much time and seems to execute forever. After 3 days when I check the restored database size, it shows just 30 GB as restored.

Is there an efficient way to restore the database?

2

Before posting the answer, I would like to iterate that I asked my question here and here. As one might suggest, this question belongs in dba SE. But the reason I post it here is because it involves editing the configuration file in /etc/my.cnf.

First Solution

Edit the /etc/my.cnf to include the below parameters. The configuration file location might vary depending on the Linux distribution. In RHEL6, it is present under /etc/my.cnf.

innodb_doublewrite = 0
innodb_flush_log_at_trx_commit = 0
innodb_support_xa = 0
innodb_locks_unsafe_for_binlog = 1 

This suggestion was provided by derobert and I would like to thank him for suggesting this solution.

Testing: Though, not as slow as the mysql command for restoration, this method still was taking considerable time. The command was executing for 3 days and had restored around 130 GB.

Second Solution

By setting a couple of flags before importing database dumps, we can dramatically speed up the restore process:

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

The above flags need to be set in the .sql file. Since we disabled auto-commit, we’ll also need to manually commit at the end of the restore:

COMMIT;

The above statement should be the last statement of the .sql file. Actually we can find a script to do the mysqldump from here.

I did not get a chance to test this solution but it makes a lot sense since it disables the foreign key checks.

Since we are restoring an entire database, we can speed things up by disabling unique checks and foreign key checks. Also, by committing everything at the end of the restore, rather than as the restore is in progress we get significant additional speed increases.

Third Solution

I have the entire mysql data directory backed up with me. The data directory is normally located under /var/lib/mysql. In my case, the data directory was mounted as a separate partition under /mounts/mysql. I had backed up the entire folder and so I restored the entire folder to the newer RHEL6 machine. Before restoring, we have to just make sure that the mysqld daemon is not started. Though, I restored using rsync command, I wanted to make sure that the file permissions are set currently in the new RHEL6 machine. So after restoring /mounts/mysql into the new RHEL6 system, I issued the below command.

chown -R mysql:mysql /mounts/mysql

Now, I tested the database and everything seemed perfectly cool. The restoration time was around 3 hours.

I have not seen people suggesting the above approach for mysql database restore anywhere. I see from this answer that the mysql versions have to be compatible for restoring the databases from data directories. However, with my experience in the restoration so far, this is not true. As long as the permissions are correctly set, the databases work perfectly fine.

However, we need the .sql file when we try and restore a mysql database into a sql-server database or a postgre database.

| improve this answer | |
  • The problem with backing up the datadir is that unless mysqld is stopped when you do so, the best outcome you can hope for is that the server where you restore to will attempt to recover from the crash it will believe occurred previously, because of the inevitable inconsistencies. If the new server version is the same or newer, and the my.cnf is identical in a few important ways, then yes, this works. We use a simiar process, when possible, for cloning replication slaves. – Michael - sqlbot Jul 8 '14 at 10:56
  • @Michael-sqlbot, good point. I had forgotten to mention that the mysqld daemon was stopped before the restoration. I will edit the answer to include that. – Ramesh Jul 8 '14 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.