mysql database restore takes long time

Question

I am in the process of reinstalling a Ubuntu server to rhel6 server. I have a pretty huge database. When I checked the mysql data directory size, it was around 354 GB. However, I dumped the entire database using the mysqldump command.

After reinstalling the machine, I started to restore the database using the mysql command. I started the restore process 3 days back. When I checked the mysql data directory in this newly installed machine, the size is just 31 GB. I am confused if actually the mysql command is restoring the database. I still see the command getting executed and the process is visibile from the linux top command. Is there something else I can do to speed up the process?

Answer 1

If you're using largely innodb tables increasing the innodb_buffer_pool_size as large as possible (~80-90% system assuming nothing else but mysqld is really consuming ram). This requires a mysqld restart though

Before the reload you can also set innodb_flush_log_at_trx_commit=2 to speed things up. This disables flushing to disk every commit (breaking the D in ACID), but should be fine for a restore. If there's a catastrophic failure during this process you're starting from scratch anyway. Be sure to set this back to one after the restore.

This can be changed dynamically without a bounce

Ensure innodb_flush_method = O_DIRECT is set in your cnf. This requires a restart

Is it getting pegged on CPU in top? If you have a lot of compressed table data that will be inherently slower. There's not much that can be done about this aside from getting a faster core. Multi core wouldn't help you here since the load is done serially anyway.

If you're using spinning disks, if possible ensure the .sql source file is being read from a different set of HDDs. Similarly, during the reload (unless you're trying to let the reload replicate) ensure binary logging and general logging are turned off.

If you're running 5.6 ensure performance schema is not enabled.

Answer 2

This is expected as restore from mysqldump takes longer than taking the backup.

To speedup process consider other backup tools:

1. xtrabackup. non-blocking for InnoDB, almost as fast as file copy. takes binary backup(=copies InnoDB tablespace).

2. mydumper. Takes logical backups, but can do it in multiple threads, so it's faster than mysqldump

3. mylvmbackup. Also very fast, but requires LVM, impacts performance when snapshot is created.

I'd go with option #1.

Answer 3

Usually, mysqldump will take as long to backup as xtrabackup, but much slower when restoring. because xtrabackup copy back by blocks while sql file exported by mysqldump have to execute one bye one.

I am quite sure use some ways could improve import speed, but still will cost you very long long time especially for such a huge database.

So, I truly recommend you install xtrabackup, do a full backup, and then restore to the new server.

or even more simple way, as considering you could to do a cold backup, turn off the mysql server, copy all the files include ibdata1, ib_logfile*, mysql folder, etc. to the new location, modify my.cnf with new location if it is changed. I haven't try it yet, but I didn't see any problem.

the first way is absolutely the best.

I hope this was useful!