How to recover the innodb when mysql data dir moved from one system to another system?

Question

I have transfer the mysql data dir from one linux box to another box. When i start the mysql server. I can find that the innodb based DB are corrupted. So, may i know how to recover the corrupted innodb

Answer 1

There are basically two ways to move mysql data from one DB server to another

OPTION 1 : Use mysqldump

mysqldump produces a logical representation of the mysql data. After dumping data as follows

mysqldump -uroot -ppassword --all-databases --routines --triggers --flush-privileges | gzip > /root/MySQLData.sql.gz

Next, scp the /root/MySQLData.sql.gz over to /root of the new DB server

Then, on the other DB, delete everything in /var/lib/mysql except /var/lib/mysql/mysql. In other words, make sure when you do ls -l /var/lib/mysql, you should only see the mysql subfolder in it.

Finally, load the data in the new DB Server

gzip -d < /root/MySQLData.sql.gz | mysql -uroot -ppassword -A

OPTION #2 : Copy /var/lib/mysql folder over to /var/lib/mysql on the new machine

This entails copying the physical representation of the data. You can tar gzip that folder and copy it as you have already done. If mysql cannot startup on the target server, check the COMMON NEEDS first, then run rm -f /var/lib/mysql/ib_logfile* and try restarting mysql on the target DB server again

COMMON NEEDS OF BOTH OPTIONS

Make sure of the following:

• Copy the /etc/my.cnf of the source DB server over to the targat DB server
• The same version of MySQL is on both servers
• There is enough space on the mount point of /var/lib/mysql on the target DB server
• Run chown -R mysql:mysql /var/lib/mysql on the target DB server

CAVEAT

If the DB on the source DB server has only .frm files, ib_logfile0, ib_logfile1, and ibdata1, chances are you have innodb_file_per_table disabled. It is very important that you have the exact same innodb setting in /etc/my.cnf on the target DB server as you had on the source.

Hope these help !!!