Wednesday, November 20, 2013

Mysql Innodb Recovery


130306 22:02:18 mysqld_safe Number of processes running now: 0
130306 22:02:18 mysqld_safe mysqld restarted
130306 22:02:18 [Note] Plugin 'FEDERATED' is disabled.
130306 22:02:18 InnoDB: The InnoDB memory heap is disabled
130306 22:02:18 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130306 22:02:18 InnoDB: Compressed tables use zlib 1.2.3
130306 22:02:18 InnoDB: Using Linux native AIO
130306 22:02:18 InnoDB: Initializing buffer pool, size = 128.0M
130306 22:02:18 InnoDB: Completed initialization of buffer pool
130306 22:02:18 InnoDB: highest supported file format is Barracuda.
130306 22:02:18 InnoDB: 5.5.30 started; log sequence number 1629186928
130306 22:02:18 [Note] Server hostname (bind-address): ''; port: 3306
130306 22:02:18 [Note] - '' resolves to '';
130306 22:02:18 [Note] Server socket created on IP: ''.
130306 22:02:18 [Note] Event Scheduler: Loaded 0 events
130306 22:02:18 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.30-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
130306 22:02:19 InnoDB: Assertion failure in thread 47204348393792 in file trx0purge.c line 840
InnoDB: Failing assertion: purge_sys->purge_trx_no <= purge_sys->rseg->last_trx_no
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: about forcing recovery.
03:02:19 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail.

Steps to get it back up.

1. Stop mysqld.
2. Backup /var/lib/mysql/ib*
3. Add the following line into /etc/my.cnf

innodb_force_recovery = 4

4. Restart mysqld.
5. Dump all tables:# mysqldump -A > dump.sql
6. Drop all databases which need recovery.
7. Stop mysqld.
8. Remove /var/lib/mysql/ib*
9. Comment out innodb_force_recovery in /etc/my.cnf
10. Restart mysqld. Look at mysql error log. By default it should be /var/lib/mysql/server/ to see how it creates new ib* files.
11. Restore databases from the dump:mysql < dump.sql
mysqlcheck –all-databases –repair

innodb force recovery options
Mode 1 - Doesn't crash MySQL when it sees a corrupt page
Mode 2 - Doesn't run background operations
Mode 3 - Doesn't attempt to roll back transactions
Mode 4 - Doesn't calculate stats or apply stored/buffered changes
Mode 5 - Doesn't look at the undo logs during start-up
Mode 6 - Doesn't roll-forward from the redo logs (ib_logfiles) during start-up

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.
Prevent the main thread from running. If a crash would occur during the purge operation, this recovery value prevents it.
Do not run transaction rollbacks after recovery.
Prevent insert buffer merge operations. If they would cause a crash, do not do them. Do not calculate table statistics.
Do not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed.
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.
**Hint : A simple query for finding all of your InnoDB tables in case you want to specifically target the corruption.

SELECT table_schema, table_name
WHERE engine = 'innodb';

No comments:

Post a Comment