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
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
chown -R mysql:mysql /var/lib/mysqlon the target DB server
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 !!!