MySQL – Why Database Restore Takes Long Time

MySQLrestore

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?

Best Answer

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.

Step1 : Connect to server A where A is username of server

ssh A@192.168.9.214

Step2 : Generate the database dump of the server A as

mysqldump -uroot -proot  db1  > database_db1.sql

Step3 : Copy the dump on the server B using SCP as

scp database_db1.sql B@192.168.9.215:/home/user/Desktop

Step4 : Connect to the Server B as

ssh B@192.168.9.215

Step5 : Restore the copied dump here using mysql client utility

mysql -uroot -proot db < /home/user/Desktop/database_db1.sql

Thesql restore from data directory

I just use this backup THE WHOLE MYSQL FILES way to migrate a MySQL database, and it worked!

here is my steps

1, install MySQL server on the 2nd server with exactly same configuration like 1st server;

2, stop 1st and 2nd mysqld server

3, change the 2nd mysql data folder name, which you can find in mysql configuration file (my.cnf):

[root@ntest1 mysql]# cat /etc/my.cnf|grep dir
#dir
datadir=/usr/local/mysql/data
innodb_data_home_dir=/usr/local/mysql/data
innodb_log_group_home_dir=/usr/local/mysql/data
target_dir=/data/backups/

so I change the data/ name to data_bak/

mv data data_bak

because my innodb data(ibdata1 ...), innodb log(ib_logfile0...), binlgo(mysql-bin.000001...) and other database files (mysql/, yourdatabase/, ...) are all in /user/local/mysql/data/, so I just need copy all this files and folders to the object server with the same location

scp -r 10.88.48.111:/usr/local/mysql/data/* /usr/local/mysql/data/

note that I use root to copy, so I need to change the roles

chown -R mysql.mysql /usr/local/mysql/data

and then MySQLd could start up in the next moment