Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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 !!!

share|improve this answer
    
Thanks for your detailed answer. It works for me. –  Mughil Mar 26 '12 at 11:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.