Saturday, November 1, 2014

How to restore MySQL Database (MySQL won't start because InnoDB Corruption and Recovery)

Here's the information when i show the error log of MySQL:
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.
Googling and fix the problem:
sudo service mysqld stop
Backup your Database:
mkdir -p /opt/database/backup/
cp -r /var/lib/mysql/* /opt/database/backup/
Adding script for recovery:
vi /etc/my.cnf
then add this line
innodb_force_recovery = 4
Restart your mysql:
service mysqld restart
Dump all tables:
mysqldump -u root -p db_name > dump.sql
Drop all databases which need recovery:
mysql>DROP TABLE table_name;
Stop MySQL:
service mysqld stop
rm -rf /var/lib/mysql/ib*
Comment out recovery script:
vi /etc/my.cnf
#innodb_force_recovery
Restart your mysql:
service mysqld restart
Restore your tables of database:
mysql -u root -p db_name < dump.sql

1 comment:

  1. With Stellar Database Repair for MySQL,you can repair all the corrupt databases and restore the data from unreadable table and table spaces. It allows the user to recover the database completely and it ensures recovery of tables and other objects without changing original format.

    To know more about this software here: http://www.mannatsoftware.com/stellar-phoenix-database-repair-for-mysql.html

    ReplyDelete