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 to include the below parameters. The configuration file location might vary depending on the Linux distribution. In RHEL6, it is present under
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.
By setting a couple of flags before importing database dumps, we can dramatically speed up the restore process:
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:
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.
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