Difference between revisions of "How to recover corrupted MySQL data"

From Kolmisoft Wiki
Jump to navigationJump to search
 
(20 intermediate revisions by 2 users not shown)
Line 1: Line 1:
[[File:datarecovery.jpg|right|200px]]
= Description =
Sometimes MySQL data gets corrupted.
Sometimes MySQL data gets corrupted.


Line 5: Line 8:
After that when MySQL process is started, it keeps crashing.
After that when MySQL process is started, it keeps crashing.


Example how it looks in the /var/log/mysql.log:
Example how it looks in the ''/var/log/mysql.log'':


[[File:mysql_crashing_log.png|400px]]
[[File:mysql_crashing_log.png|400px]]


<br><br>
= Recovery mode =


Solution to this is into file /etc/my.cnf enter such line:
Solution to this is into file ''/etc/my.cnf'' enter such line:


  innodb_force_recovery = 4  
  innodb_force_recovery = 4  
Line 21: Line 26:


[[File:mysql_recovery_on_log.png]]
[[File:mysql_recovery_on_log.png]]
Your database will now start, but with innodb_force_recovery, all INSERTs and UPDATEs will be ignored. E.g. DB is in read-only mode.
<br><br>
= Data retrieval =
Try to retrieve data using mysqldump command:
mysqldump -u mor -pmor mor > mor.sql
It is big chance that at some point it will fail and will show which table is corrupted.
In our example it is [sessions] table:
[[File:mysql_failed_dump.png]]
We are very lucky here because [sessions] table does not hold any important information.
We will ignore this table in data export using special mysqldump key:
  --ignore-table=name Do not dump the specified table. To specify more than one
                      table to ignore, use the directive multiple times, once
                      for each table.  Each table must be specified with both
                      database and table names, e.g.
                      --ignore-table=database.table
We execute:
mysqldump -u mor -pmor mor --ignore-table=mor.sessions > mor.sql
This will export all data to ''mor.sql'' file.
If it fails at some other table, let's say [users], then exclude [users] table also:
  mysqldump -u mor -pmor mor --ignore-table=mor.sessions --ignore-table=mor.users > mor.sql
<br><br>
= Data import =
=== Into new server ===
Import '''my.sql''' file in newly formatted/prepared server using command:
mysql -u mor -pmor mor < mor.sql
or:
=== Into old server ===
(Make sure HDD has enough free space!)
# Shutdown database (''/etc/init.d/mysql stop'') and delete the data directory (''/var/lib/mysql'').
# Run mysql_install_db to create MySQL default tables
# Remove the innodb_force_recovery line from your ''/etc/my.cnf'' file and restart the database. (It should start normally now)
# Restore everything from your backup (mysql -u mor -pmor mor < mor.sql)
=== Restore corrupted tables ===
If DB is new - create excluded/corrupted tables manually, because they will not be created when importing saved ''mor.sql'' file.
In our example create sessions table manually using phpMyAdmin or mysql console command:
CREATE TABLE IF NOT EXISTS `sessions` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `session_id` varchar(255) default NULL,
  `data` longtext,
  `updated_at` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `index_sessions_on_session_id` (`session_id`),
  KEY `index_sessions_on_updated_at` (`updated_at`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
'''^^^^ This is just an EXAMPLE! Create tables which are corrupted in your DB!'''
<br><br>
=Backuping the database to an external server=
== About ==
When you are out of space in a server and have to create database dump - you can use this method with another server. Just remember to test the dump before doing anything to an original one.
== Server A (database to dump) ==
mysqldump mor | gzip -9 | nc -l 3333
== Server B (database to dump where) ==
rpm -Uvh rpm -Uvh http://pipeviewer.googlecode.com/files/pv-1.2.0-1.i386.rpm  # will show you realtime how much data was transfered
nc 94.23.196.71 3333 | pv -b > /root/mor.sql.gz
= See also =
* Based on: http://www.softwareprojects.com/resources/programming/t-how-to-fix-mysql-database-myisam-innodb-1634.html
* Can help in desperate situations: http://www.percona.com/docs/wiki/innodb-data-recovery-tool:mysql-data-recovery:start

Latest revision as of 11:04, 1 September 2011

Datarecovery.jpg

Description

Sometimes MySQL data gets corrupted.

It often happens when HDD space runs out.

After that when MySQL process is started, it keeps crashing.

Example how it looks in the /var/log/mysql.log:

Mysql crashing log.png



Recovery mode

Solution to this is into file /etc/my.cnf enter such line:

innodb_force_recovery = 4 

and restart MySQL process:

/etc/init.d/mysql restart

After that log should show that recovery mode is turned on:

Mysql recovery on log.png

Your database will now start, but with innodb_force_recovery, all INSERTs and UPDATEs will be ignored. E.g. DB is in read-only mode.



Data retrieval

Try to retrieve data using mysqldump command:

mysqldump -u mor -pmor mor > mor.sql

It is big chance that at some point it will fail and will show which table is corrupted.

In our example it is [sessions] table:

Mysql failed dump.png

We are very lucky here because [sessions] table does not hold any important information.

We will ignore this table in data export using special mysqldump key:

 --ignore-table=name Do not dump the specified table. To specify more than one
                     table to ignore, use the directive multiple times, once
                     for each table.  Each table must be specified with both
                     database and table names, e.g.
                     --ignore-table=database.table


We execute:

mysqldump -u mor -pmor mor --ignore-table=mor.sessions > mor.sql

This will export all data to mor.sql file.

If it fails at some other table, let's say [users], then exclude [users] table also:

 mysqldump -u mor -pmor mor --ignore-table=mor.sessions --ignore-table=mor.users > mor.sql




Data import

Into new server

Import my.sql file in newly formatted/prepared server using command:

mysql -u mor -pmor mor < mor.sql

or:

Into old server

(Make sure HDD has enough free space!)

  1. Shutdown database (/etc/init.d/mysql stop) and delete the data directory (/var/lib/mysql).
  2. Run mysql_install_db to create MySQL default tables
  3. Remove the innodb_force_recovery line from your /etc/my.cnf file and restart the database. (It should start normally now)
  4. Restore everything from your backup (mysql -u mor -pmor mor < mor.sql)

Restore corrupted tables

If DB is new - create excluded/corrupted tables manually, because they will not be created when importing saved mor.sql file.

In our example create sessions table manually using phpMyAdmin or mysql console command:

CREATE TABLE IF NOT EXISTS `sessions` (
 `id` int(10) unsigned NOT NULL auto_increment,
 `session_id` varchar(255) default NULL,
 `data` longtext,
 `updated_at` datetime default NULL,
 PRIMARY KEY  (`id`),
 KEY `index_sessions_on_session_id` (`session_id`),
 KEY `index_sessions_on_updated_at` (`updated_at`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

^^^^ This is just an EXAMPLE! Create tables which are corrupted in your DB!



Backuping the database to an external server

About

When you are out of space in a server and have to create database dump - you can use this method with another server. Just remember to test the dump before doing anything to an original one.

Server A (database to dump)

mysqldump mor | gzip -9 | nc -l 3333

Server B (database to dump where)

rpm -Uvh rpm -Uvh http://pipeviewer.googlecode.com/files/pv-1.2.0-1.i386.rpm   # will show you realtime how much data was transfered
nc 94.23.196.71 3333 | pv -b > /root/mor.sql.gz

See also