Sử dụng Forcing Innodb Recovery để khôi phục dữ liệu MySQL

Sử dụng Forcing Innodb Recovery để khôi phục dữ liệu MySQL. Xem chi tiết tại đây!

Để đảm bảo nội dung cụ thể và tính chính xác của thông tin, bài viết này chúng tôi mạn phép sử dụng thuần Tiếng Anh chuyên ngành để chia sẻ. Mong các bạn thông cảm!

Thông tin tham khảo :

MySQL 14.2.5.2 Forcing InnoDB Recovery

MỤC LỤC

1 1. Check the size
2 2. Edit /etc/my.cnf and force recovery and log size.
3 3. Restart Mysql service
4 4. Remove innodb_force_recovery and innodb_log_file_size

1. Check the size

-rw-rw—- 1 mysql mysql 15242880 Oct 18 11:50 ib_logfile0
-rw-rw—- 1 mysql mysql 15242880 Oct  2 02:10 ib_logfile1

				1
2

						-rw-rw—- 1 mysql mysql 15242880 Oct 18 11:50 ib_logfile0
-rw-rw—- 1 mysql mysql 15242880 Oct  2 02:10 ib_logfile1

So the size is 15242880

2. Edit /etc/my.cnf and force recovery and log size.

innodb_force_recovery=4
innodb_log_file_size=15242880

				1
2

						innodb_force_recovery=4
innodb_log_file_size=15242880

3. Restart Mysql service

Now and database should start, but with innodb_force_recovery in my.cnf all Insert and Update operation will be ignored.

/etc/init.d/mysql start

1	/etc/init.d/mysql start

Once you have restarted the MySQL server with innodb_force_recovery in place, you have to dump the corrupted databases.

Single Database

mysqldump database_name > database_name.sql

1	mysqldump database_name > database_name.sql

All Databases

mysqldump –all-databases > all_the_bases.sql

1	mysqldump –all-databases > all_the_bases.sql

Once you have the databases dumped successfully, stop MySQL completely and move the ib* files from /var/lib/mysql/ out of the directory.

mkdir /var/lib/old_innodb
mv /var/lib/mysql/ib* /var/lib/old_innodb

1 2	mkdir /var/lib/old_innodb mv /var/lib/mysql/ib* /var/lib/old_innodb

4. Remove innodb_force_recovery and innodb_log_file_size

Remove innodb_force_recovery & innodb_log_file_size two lines from /etc/my.cnf and start MySQL service. Then Restore database into their respective database.

mysql  database_name < database_name.sql

1	mysql database_name < database_name.sql

All Databases

mysql < all_the_bases.sql

1	mysql < all_the_bases.sql

Once restore completed, try to repair all the database.

mysqlcheck –all-databases –repair

1	mysqlcheck –all-databases –repair

Thats all.. check your mysql logs. A simple query for finding all of your InnoDB tables in case you want to specifically target the corruption.

SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.TABLE

1 2	SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLE

Tips:

The database must not otherwise be used with any nonzero value of innodb_force_recovery.

As a safety measure, InnoDB prevents users from performing INSERT, UPDATE, or DELETE operations when innodb_force_recovery is greater than 0.

Under the mysqld section of the config file you can add innodb_force_recovery = 0 – 6.

Levels 1-4 are pretty safe as most data is preserved. Setting the level to 5 or 6 gets a bit more risky as you could lose some data.