mysql restore from data directory

Question

I recently asked a question regarding the restoration of a pretty huge (around 300 to 400 GB) mysql database and got some nice suggestions as seen here. Now, I have backed up the data directory of mysql as well.

So, for example before reinstalling the machine the data directory is present under /mounts/mysql.

I have backed up that particular folder and I was wondering if I just restore /mounts/mysql folder back in the new machine, will it be ok? Or will there be some problem because the database is not restored using the proper mysql command?

Answer 1

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