InnoDB Storage Engine Database recovery method in MySQL

MySQL's database files can be copied directly, but that refers to the "MyISAM" type of table.
Instead of using Mysql-front to create a table directly, the default is the "InnoDB" type, a table that corresponds to only one "*.frm" file on disk, unlike MyISAM. myd,*. Myi "file.
MyISAM types of tables can be directly copied to another database, but InnoDB types of tables
But not. The solution is:

Simultaneously copies the InnoDB database table "*.frm" file and the InnoDB data "ibdata1" file to the appropriate location. To start the Windows service for MySQL, if it is unsuccessful, view the data folder with a "*.err" error log file that describes the cause of the startup failure. For example, I have encountered two wrong reasons.
One is similar to an error message:

INIFile Code

InnoDB:Error:log file. Ib_logfile0 is of different size 0 10485760 bytes Innodb:than specified in the. cnf file 0 25165 824 bytes!

This is because the log file size that is configured in the MySQL configuration file does not match actual.
The solution is to delete the old "Ib_logfile0" and other log files, restart MySQL will automatically generate a new log file.
The other is the error message

INIFile Code

Innodb:operating System error number 5 in a file operation. Innodb:the error means mysqld does not have the access rights to innodb:the directory. It may also is you have created a subdirectory innodb:of the same name as a data file. Innodb:file name. Ibdata1 innodb:file operation call: ' Open '. Innodb:cannot continue operation.

After checking the original "ibdata1" file in the process of copying is somehow added to the read-only property.
The workaround is to remove the read-only property of the "Ibdata1" file.

15.2.8.1. Forced recovery

If the database page is corrupted, you may want to dump your table from the database using SELECT INTO OutFile, and most of the data obtained in this way is intact. Even so, corruption can cause a select * from Tbl_name or InnoDB background operation to crash or assert, or even make the InnoDB roll back crash. However, you can use it to force the InnoDB storage engine to start and prevent the background operation from running so that you can dump your tables. For example, you can add the following line to the [mysqld] section of the options file before restarting the server:

[Mysqld]

Innodb_force_recovery = 4

Innodb_force_recovery is allowed with a value of not 0 as follows. A larger number contains preventive measures of all smaller numbers. If you can dump your table with an option value that is mostly 4, you are safe, and only some of the data on the corrupted individual page is lost. A value of 6 is exaggerated because the database page is left in a stale state, which in turn can cause more damage to the B-tree and other database structures.

· 1 (srv_force_ignore_corrupt)

Even if the server detects a corrupted page, the server is running, and trying to have the select * from Tbl_name skip the corrupted index records and pages, which helps to dump the table.

· 2 (Srv_force_no_background)

Prevents the main thread from running, which will block if the crash may occur during the purge operation.

· 3 (Srv_force_no_trx_undo)

Transaction rollback does not run after recovery.

· 4 (Srv_force_no_ibuf_merge)

The Insert Buffer merge operation is also blocked. If you could cause a crash. It is best not to do these operations, do not calculate table tables.

· 5 (Srv_force_no_undo_log_scan)

Do not view the unfinished log at the start of the database: InnoDB treats the unfinished transaction as committed.

· 6 (Srv_force_no_log_redo)

Do not roll the log forward in the recovery connection.

The database cannot be used separately with the options allowed in these options. As a security measure, when Innodb_force_recovery is set to a value greater than 0, InnoDB prevents the user from performing an insert, update, or delete operation.

Even if forced recovery is used, you can also drop or create tables. If you know that a given table is causing a rollback crash, you can remove it. You can also use this to stop runaway rollback caused by failed bulk imports or failed ALTER table. You can kill the MYSQLD process and set the Innodb_force_recovery to 3, leaving the database suspended without the need to roll back, and then discarding the table that caused the runaway rollback.
15.2.8.2. Inspection points

InnoDB implements a checkpoint mechanism that is recognized as a "blur" checkpoint setting. InnoDB refreshes the modified database page in small batches from the buffer pool. There is no need to flush the buffer pool with a single batch, and a batch refresh operation may stop the processing of user SQL statements in the checkpoint setup process.

In crash recovery, InnoDB looks for the checkpoint tag that is written to the log. It knows that all modifications to the database before the label are rendered in the database's disk image. InnoDB then scans the log files from the checkpoint and applies the changes to the database that have been written to the log.

InnoDB writes log files in a circular fashion. All submitted modifications that make the database pages in the buffer pool different from the images on the disk must appear in the log file in case the InnoDB needs a recovery. This means that when InnoDB begins to reuse a log file, it needs to confirm that the database page image on disk contains modifications that have been written into the log file that the InnoDB is ready to reuse. In other words, InnoDB must create a checkpoint that often involves a refresh of modified database pages to disk.

The preceding narrative explains why making your log files very general in the Setup checkpoint saves disk I/O. It is usually meaningful to set the total size of the log file as large as the buffer pool or even larger than the buffer pool. The disadvantage of large log files is that crash recovery takes longer, because more information written to the log is applied to the database.
15.2.9. Move a InnoDB database to another machine

On Windows, InnoDB always stores databases and tables internally in lowercase names. To move a binary-formatted database from UNIX to Windows, or move from Windows to UNIX, you should have all tables and database names lowercase. One convenient way to do this is to add the following line to the [mysqld] section of your my.cnf or My.ini file before creating any databases and tables:

[Mysqld]

Lower_case_table_names=1

Similar to MyISAM data files, InnoDB data and log files are binary compatible on all platforms that have the same floating-point number format. You can simply move a InnoDB database by copying all the related files in the 15.2.8 section, "InnoDB Database backup and Recovery". If the floating-point format is different, but you do not use a float or double data type in the table, the procedure is the same: simply copy the related file. If the format is not available, and your table contains floating-point data, you must use mysqldump to dump your table on one machine, and then import the dump file on another machine.

If the tablespace has enough space to be used by the large rollback fragment produced by the import transaction, one way to raise performance is to turn off autocommit mode when importing data. Commits only after you import a fragment of the entire table or table.