นำมาฝาก How to recover MySQL data from Innodb ?

[มนตรี บอยรักยุ้ยคนเดียว]

Issue:

Unfortunately some times you need to recover MySQL data from ibdata1. It's many reasons why your getting corrupted Innodb files that cannot automatically be restored of the mysqld daemon.

Solution:
Scenario
We assume that your scenario are like following:

You got backup of your ibdata1, ib_logfile0 and ib_logfile1
You also got backup of your database folder with .frm files
You would like to restore this backup into an MySQL server that's already in production.
Before we start, just one advice: Stop crying, your data isn't lost.

Restore the data backup you got
First of all restore you data on another MySQL server, to not interrupt the services running in your normal production environment. Restore data into the normal MySQL data directory. For our scenario we use /var/lib/mysql.

Be careful to get the right permissions and owners on all your data files. Your MySQL users should own the files and the group should also be assigned to MySQL.

Before you continue you need to find the size of your Innodb logfiles. Simply run the command ls -l to find this. This will output something like this:

-rw-rw---- 1 mysql mysql 5242880 Jun 25 11:30 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Jun 25 11:30 ib_logfile1

Start up MySQL in rescue mode
Some simple steps will start up the MySQL daemon in rescue mode for you:
From your Unix shell su into the mysql user: su mysql
Start up your mysqld process with the logfile size and innodb_force_recovery as parameters.

/usr/sbin/mysqld --innodb_log_file_size=5242880 --innodb_force_recovery=6

If everything goes fine you should get a output like this:
InnoDB: The user has set SRV_FORCE_NO_LOG_REDO on
InnoDB: Skipping log redo
070625 11:59:36 InnoDB: Started; log sequence number 0 0
InnoDB: !!! innodb_force_recovery is set to 6 !!!
070625 11:59:36 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.18' socket: '/var/lib/mysql/mysql.sock' port: 3306 SUSE MySQL
Get your data
The last simple but most important step is now to get your data.

Open a new shell to the server where your MySQL daemon are running in recovery mode.
Simply run a normal mysqldump of your database:

mysqldump -u root -p database > database.sql

If you get a message looking like this, you got corrupted Innodb log files:

Got error: 1146: Table 'database.table' doesn't exist when using LOCK TABLES

What you can do to resolve this problem with keeping the ib_logfile0 file from you most current backup while you restore all the other files from a older backup. This isn't a fail-proof solution, but worth a try.

Restore you data
Now you can copy your SQL dump to your production server and simply restore the data from your MySQL dump file like this:

mysql -u root -p database < database.sql

[มนตรี บอยรักยุ้ยคนเดียว]

ควรจะปิด daemon mysql ก่อนครับ
1.#/usr/local/etc/rc.d/mysql-server.sh stop

2.#cd /var/db/mysql/ชื่อDB
 
3.#myisamchk -o *.MYI
 หรือ
#myisamchk -r *.MYI      เป็นการ recovery DB

แล้วสั่งให้ daemon mysql ทำงานครับ
4.#/usr/loca/etc/rc.d/mysql-server.sh start

 

[มนตรี บอยรักยุ้ยคนเดียว]

13.2.6.2. Forcing InnoDB Recovery
If there is database page corruption, you may want to dump your tables from the database with SELECT INTO ... OUTFILE. Usually, most of the data obtained in this way is intact. However, it is possible that the corruption might cause SELECT * FROM tbl_name statements or InnoDB background operations to crash or assert, or even cause InnoDB roll-forward recovery to crash. In such cases, you can use the innodb_force_recovery option to force the InnoDB storage engine to start up while preventing background operations from running, so that you are able to dump your tables. For example, you can add the following line to the [mysqld] section of your option file before restarting the server:

[mysqld]
innodb_force_recovery = 4
innodb_force_recovery is 0 by default (normal startup without forced recovery) The allowable nonzero values for innodb_force_recovery follow. A larger number includes all precautions of smaller numbers. If you are able to dump your tables with an option value of at most 4, then you are relatively safe that only some data on corrupt individual pages is lost. A value of 6 is more drastic because database pages are left in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures.

1 (SRV_FORCE_IGNORE_CORRUPT)

Let the server run even if it detects a corrupt page. Try to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

2 (SRV_FORCE_NO_BACKGROUND)

Prevent the main thread from running. If a crash would occur during the purge operation, this recovery value prevents it.

3 (SRV_FORCE_NO_TRX_UNDO)

Do not run transaction rollbacks after recovery.

4 (SRV_FORCE_NO_IBUF_MERGE)

Prevent insert buffer merge operations. If they would cause a crash, do not do them. Do not calculate table statistics.

5 (SRV_FORCE_NO_UNDO_LOG_SCAN)

Do not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed.

6 (SRV_FORCE_NO_LOG_REDO)

Do not do the log roll-forward in connection with recovery.

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.

You can SELECT from tables to dump them, or DROP or CREATE tables even if forced recovery is used. If you know that a given table is causing a crash on rollback, you can drop it. You can also use this to stop a runaway rollback caused by a failing mass import or ALTER TABLE. You can kill the mysqld process and set innodb_force_recovery to 3 to bring the database up without the rollback, then DROP the table that is causing the runaway rollback.

[thannawe]

ขอบคุณครับ 

[มนตรี บอยรักยุ้ยคนเดียว]

วิธีการ rebuild rpm ทำตามคำสั่งนี้ครับ

[root@tum ~]# rpmbuild --rebuild alien-8.77-1.src.rpm

จากนั้นจะได้ไฟล์ rpm จากนั้นก็ใช้คำสั่ง rpm install ได้เลย ตามคำสั่งด้านล่าง

[root@tum ~]# rpm -ihv alien-8.77-1.rpm

สำหรับ ubuntu ใช้คำสั่ง

[root@tum ~]# sudo apt-get install alien

การใช้งาน
ตัวอย่างการ convert จาก .rpm ไปเป็น .deb

[root@tum ~]# alien -d file.rpm file.deb

ตัวอย่างการ convert จาก .deb ไปเป็น .rpm

[root@tum ~]# alien -r file.deb. file.rpm