Restoration example of InnoDB engine data table of MySQL database

Integrity and consistency are the core content of a database in business applications. MySQL uses the InnoDB engine to implement transaction processing ), therefore, more checks and restrictions are required for data tables using the InnoDB engine. Correspondingly, this is more prone to the problem that some data or even all records in the table cannot be normally read due to data consistency and integrity. Therefore, in practical application, you may have to deal with how to restore the InnoDB data table.

This document describes how to restore an InnoDB data table.

In addition, it is worth mentioning that, starting from MySQL 5.5.5, the preset engine has been changed to InnoDB.

Http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html

Starting from MySQL 5.5.5, the default storage engine for new tables is InnoDB.

(1) Get backup files ):

Data File:/var/lib/mysql/ibdata1
Logs File:/var/lib/mysql/ib_logfile0
Logs File:/var/lib/mysql/ib_logfile1

Database: bizness
Table Name: transaction
Table File:/var/lib/mysql/bizness/transaction. frm

(2) Setup temporary mysql service for recover (set a temporary database for recovery)

Create database and InnoDB table with same name engine (not matter the field name ):

Mysql> create database bizness;
Mysql> create table transaction (a INT) ENGINE = InnoDB;

Stop the temporary mysql service, and copy all the backup files to replace
Current databases files. (ibdata1, ib_logfile0, ib_logfile1, transaction. frm)

# Cp-p ibdata1/var/lib/mysql/
# Cp-p ib_logfile0/var/lib/mysql/
# Cp-p ib_logfile1/var/lib/mysql/
# Cp-p transaction. frm/var/lib/mysql/bizness/
# Chown mysql: mysql/var/lib/mysql/ibdata
# Chown mysql: mysql/var/lib/mysql/ib_logfile0
# Chown mysql: mysql/var/lib/mysql/ib_logfile1
# Chown mysql: mysql/var/lib/mysql/transaction. frm

(3) Check the old logfiles size:

#/Bin/ls-l-B/var/lib/mysql/ib_logfile *;
------------------------------------------------------------------------------
-Rw ---- 1 mysql 5242880 September 2 13:29 ib_logfile0
-Rw ---- 1 mysql 5242880 July 30 13:46 ib_logfile1
------------------------------------------------------------------------------
Note: the size is 5242880 bytes.

(4) Now start up mysql in rescue mode

#/Usr/libexec/mysqld -- innodb-log-file-size = 5242880 -- innodb-force-recovery = 6
------------------------------------------------------------------------------
InnoDB: Initializing buffer pool, size = 8.0 M
InnoDB: Completed initialization of buffer pool
InnoDB: The user has set SRV_FORCE_NO_LOG_REDO on
InnoDB: Skipping log redo
InnoDB: Started; log sequence number 0 0
InnoDB :!!! Innodb_force_recovery is set to 6 !!!
[Note] Event schedents: Loaded 0 events
[Note]/usr/libexec/mysqld: ready for connections.
Version: '5. 1.61 'socket:'/var/lib/mysql. sock 'port: 3306 Source distribution
------------------------------------------------------------------------------

And then dump the database to SQL file with mysqldump command:

# Mysqldump-u root-p bizness> bizness. SQL
Or:
# Mysqldump-u root-p bizness transaction> bizness. transaction. SQL

If the data text is exported successfully, it can be used for restoration. Based on your actual situation,
You may need to delete the collapsed data table and then import the restored SQL text. For example:

# Mysql-u root-p bizness <bizness. SQL
Or:
# Mysql-u root-p bizness <bizness. transaction. SQL

Note: mysql importing do not need to indicate the table name.

If necessary, you can view the help instructions:

#/Usr/libexec/mysqld -- verbose -- help

-- Innodb-log-file-size = #
Size of each log file in a log group.

-- Innodb-force-recovery = #
Helps to save your data in case the disk image of the database becomes before upt.
0 by default (normal startup without forced recovery)

Reference documents on the Official Website:
Http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html

1 (srv_force_ignore_0000upt)
This option is especially useful for backing up or transferring the current data even if the page is damaged.
Lets the server run even if it detects a temporary upt page. Tries to make SELECT * FROM tbl_name
Jump over tables upt index records and pages, which helps in dumping tables.

2 (SRV_FORCE_NO_BACKGROUND)
This option prevents the main thread and any cleanup thread from running. If the cleanup operation causes the server to crash, this option helps prevent it.
Prevents the master thread and any purge threads from running. If a crash
Wocould occur during the purge operation, this recovery value prevents it.

3 (SRV_FORCE_NO_TRX_UNDO)
Transactions are not rolled back after recovery.
Does not run transaction rollbacks after crash recovery.

4 (SRV_FORCE_NO_IBUF_MERGE)
If the merge operation inserted to the buffer zone causes system crash, the insertion will not be executed.
Prevents insert buffer merge operations. If they wowould cause a crash,
Does not do them. Does not calculate table statistics.

5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
When starting the database, ignore the UNDO log. InnoDB regards the unfinished transactions as completed.
Does not look at undo logs when starting the database:
InnoDB treats even incomplete transactions as committed.

6 (SRV_FORCE_NO_LOG_REDO)
When the database is started, the rollback logs related to recovery are ignored.
Does not do the redo log roll-forward in connection with recovery.

When the preceding option value is used, you can only perform basic SELECT queries, but cannot perform other queries.
With this value, you might not be able to do queries other than a basic
SELECT * FROM t, with no WHERE, order by, or other clauses. More complex
Queries cocould encounter extends upted data structures and fail.

If your uption within the table data prevents you from dumping the entire table
Contents, a query with an order by primary_key DESC clause might be able to dump
The portion of the table after the specified upted part.