InnoDB Chinese Reference Manual---6 backup and restore InnoDB database

Backup | reference | reference Manual | recovery | data | database | Chinese InnoDB Chinese Reference Manual---canine (heart sail) Translation 6 backup and restore InnoDB database
Secure database management is the use of regular data backups.

InnoDB Hot Backup is an online backup tool that you can use to perform online backups while the InnoDB database is running. InnoDB Hot Backup does not require you to shut down your server or add any locks or affect other normal data operations. InnoDB Hot Backup is a non free add-on tool that costs 400 euros a year for each MySQL server. Browse the Web InnoDB Hot Backup homepage To get more information and program screenshots.

If you can turn off your MySQL service, you can do a binary backup of the database in the following steps:
Turn off the MySQL database service and make sure that no errors occur at shutdown to copy all your data files to a secure location copy all the InnoDB log files to a secure location copy the MY.CNF profile to a secure location and all InnoDB tables. frm Copy to a safe place.
In a High Performance database service site, you can maintain a copy of the database via MySQL's replication features, and the MySQL replication feature also applies to InnoDB table types.

In addition to the binary backup method described above, it is best to use the mysqldump dump datasheet regularly. The reason is that the binaries may be corrupted when you don't notice, and the table dump file is saved as a text file and is simpler and more readable than a binary file. Because the dump file is simpler, it is easier to find table corruption, and the likelihood of critical data loss loops is small.

A good idea is to do a dump (dump) backup while making a binary backup of the database. In order to get a consistent snapshot of the data, all client connections must be closed. You can then make a binary backup so that you have two formats that are consistent with the data.

The MySQL binary log (binlogging) switch must be opened to enable the InnoDB database to be restored to its current state through the binary backup method described above. So you can work with the binary log and backup data to achieve Point-in-time recovery:
Mysqlbinlog yourhostname-bin.123 | Mysql

　

The only thing you can do to restore a crashed MySQL service process is to reboot. InnoDB automatically checks the log and completes the database Roll forward (Roll-forward) to the current state. Also, InnoDB automatically rolls back transactions that were not committed before the crash. During the recovery process, MYSQLD will display the following prompts:

heikki@donna:~/mysql-3.23.48/sql> mysqld 020204 23:08:31 innodb:database is not shut down normally. innodb:starting recovery from log files ... Innodb:starting log scan based on checkpoint at Innodb:log sequence number 0 177573790 innodb:doing recovery:scanned u p to log sequence number 0 177638912 innodb:doing recovery:scanned up to log sequence number 0 177704448 innodb:doing r Ecovery:scanned up to log sequence number 0 177769984 innodb:doing recovery:scanned up to log sequence number 0 1778355 Innodb:doing recovery:scanned up to log sequence number 0 177901056 innodb:doing recovery:scanned up to log Sequenc E number 0 177966592 innodb:doing recovery:scanned up to log sequence number 0 178032128 innodb:doing recovery:scanned Up to log sequence number 0 178097664 innodb:doing recovery:scanned up to log sequence number 0 178163200 Recovery:scanned up to log sequence number 0 178228736 innodb:after This prints a line for every 10th scan sweep:inNodb:doing recovery:scanned up to log sequence number 0 178884096 ... Innodb:doing recovery:scanned up to log sequence number 0 193302016 innodb:doing recovery:scanned up to log sequence n Umber 0 193957376 innodb:doing recovery:scanned up to log sequence number 0 194612736 020204 23:08:40 innodb:starting A n Apply batch of log records to the database. .. The innodb:progress in percents:0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34-35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52-53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 7-3 4-A-i-am-i-M-i-M-batch-A-M-innodb:apply-completed innodb:doing R Ecovery:scanned up to log sequence number 0 195268096 innodb:doing recovery:scanned up to log sequence number 0 1959234 56.. Innodb:doing recovery:scanned up to log sequence number 0 203132416 innodb:doing recovery:scanned up to log sequence n Umber 0 203787776Innodb:doing recovery:scanned up to log sequence number 0 204443136 innodb:5 uncommitted transaction (s) which must is R Olled Back Innodb:trx ID counter be 0 129792 innodb:starting rollback of uncommitted transactions innodb:rolling back T Rx with id 0 129400 innodb:rolling back of Trx ID 0 129400 completed innodb:rolling back trx with id 0 129217 Innodb:ro Lling back of the TRX ID 0 129217 completed innodb:rolling back TRX and ID 0 129098 innodb:rolling back of Trx ID 0 129098 Completed innodb:rolling back TRX with id 0 128743 innodb:rolling back of Trx ID 0 128743 completed innodb:rolling back Trx with ID 0 127939 innodb:rolling back of Trx ID 0 127939 completed innodb:rollback of uncommitted transactions compl eted 020204 23:08:51 innodb:starting A apply batch of log records to the database. .. The innodb:progress in percents:0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52-53-54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71-72 7 3 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91-92 innodb:apply Batch completed innodb:last MySQL binlog file offset 0 40418561, file name./donna-bin.0 020204 23:08:53 innodb:flushing modified pages from the buffer pool ... 020204 23:09:03 innodb:started Mysqld:ready for connections

If the database is corrupted or the disk fails, you will have to recover from a backup file. In the case of corruption, you can first recover an undamaged backup file. Then follow the MySQL manual prompts to recover the data from the normal log file.

In the case of some database corruption, it may be sufficient to dump (dump), undo (drop), and re-establish one or more corrupted tables. Poor thing. Check Table SQL command checks a damaged table, although check table does not find all types of corruption. You may use Innodb_tablespace_monitor to check the integrity of the file space management when data files are used.

In some cases, the database page displays corruption, which is actually due to a corrupted file cache on the operating system, and the data files on the disk are good. It's best to get your system back in the first time. This may resolve database page errors.
6.1 Force (forcing) recovery
If the database page is corrupted, the table data can be stored from the database in the SELECT into outfile, and most of the data is not damaged. However, these corruptions can cause a SELECT * from table or InnoDB background operation to crash or break (assert), or even InnoDB roll forward (roll-forward) recovery crashes. Starting with InnoDB version 3.23.44, there is a setup option in MY.CNF that forces InnoDB to start, and prevents background operations from running, so you can dump the data. For example, you can add the following setting in MY.CNF:
set-variable = Innodb_force_recovery = 4

　

Innodb_force_recovery Alternative selection will be listed below. This parameter cannot be used in other aspects of the database! When the setting value is greater than 0 o'clock, as a security metric, InnoDB prevents the user from using INSERT, UPDATE, or DELETE.

Starting with 3.23.53 and 4.0.4, you can use DROP or CREATE a table even if forced recovery is used. If you make sure that the table causes a rollback crash, you can remove it (drop). You can also use this to stop a runaway (runaway) rollback caused by importing large amounts of data or ALTER TABLE. You can kill the mysqld process and use the MY.CNF settings innodb_force_recovery=3 not use rollback. You can then DROP the table that caused the runaway (runaway) rollback.

The larger number below means that the security guard is included for all the lower numbers. In order to be able to dump the table set at least 4, this is relatively safe, only some corrupted page data lost. Option 6 is more dramatic, because database pages are left in a obsolete state, which in turn could introduce more corrupti On to B-trees and other database structures. 1 (srv_force_ignore_corrupt) Start the service even if an error is found; try to skip corrupted index records and pages using the SELECT * from table, which will help dump the table. 2 (Srv_force_no_background) prevent the main thread from running: If a crash occurs during the cleanup process, this will prevent it. 3 (Srv_force_no_trx_undo) recovery does not run a transaction rollback. 4 (Srv_force_no_ibuf_merge) prevents merge operations of Insert buffers: if they will cause a crash, it is best not to manipulate them; Do not consider table statistics (tables statistics). 5 (Srv_force_no_undo_log_scan) do not undo log when starting database (UNDO logs): InnoDB commits an unfinished transaction. 6 (Srv_force_no_log_redo) do does the LOG Roll-forward in connection with recovery.
　
6.2 Inspection points (checkpoints)
InnoDB implements the checkpoint mechanism by calling a fuzzy checkpoint. InnoDB refreshes the modified database page in small batches from the buffer pool. This does not require refreshing the entire buffer pool in a batch, which would, in truth, stop the user SQL statement from running the process for some time.

In crash recovery InnoDB checks the checkpoint label recorded in the log file during crash repair. It knows that all modifications to the database before the label have been recorded in the disk image of the database. InnoDB then scans the log in the log file after the checkpoint and logs the changes to the database.

InnoDB log files in a circular fashion. All changes made to the database pages in the buffer pool that are not the same as the disk mirrors must be recorded in the log file in case InnoDB need to be recovered. This means that InnoDB a log file in a roundabout way, it must determine that the action log results in the log file that will be reused are included in the disk image file. In other words, InnoDB must regularly set up checkpoints and update the modified database pages to disk.

The above to explain why log files are set and larger can reduce the disk I/O used to establish checkpoints. This makes it understandable that the total size of the set log file is as large or larger as the buffer pool. The disadvantage of a large log file is that it will take a long time to perform a crash repair because more action logs need to be updated to the database.