MySQL Tutorial: Using Backup to recover data

The recovery process consists of two information sources---backup files and binary logs, which can be restored to the state at the time the backup was performed, and the binary logs revert to the state when the failure occurred. The following describes how to use these two files to recover a database or to recover a single datasheet.

To recover an entire database:

Copy the entire contents of the database you want to restore to a different location for backup.

Overload the database with the most recent backup file. If you use mysqldump-generated backups, you can use them as input overloads for MySQL, or if you are backing up by copying the database directory, shut down the database server, copy the backup back to the data directory, and restart the database server.

Redo the transaction through the binary log to recover the data from the error point. The specific operation is this, using Mysqlbinlog to convert the log to ASCII format, and then take it as MySQL input, and specify the--one-database option so that MySQL only executes your specified database recovery. Such as:

%　ls　-t　-r　-l　binlog.[0-9]*　|　xargs　mysqlbinlog　|　mysql　--one-database　db_name

However, the above command only applies if all log file names have the same length. Otherwise, the following Perl code is required to handle:

#!/usr/bin/perl-w
# sort_num.pl
Use strict;
My @files = <>;             #read all input File
@files = sort {My $anum = $ =~/\ $a. ( \d+) $/;         #sort them by numeric extension
                My $bnum = if $b =~/\. (\d+) $/;
                $anum <=> $bnum;
              } @files;
Print @files;              #print them
Exit (0);

Use this script as follows

% ls -l binlog.[0-9]* | sort_num.pl | xargs mysqlbinlog | mysql --one-database db_name

All of the log files are discussed above, but in most cases we only need a few log files that are generated after the backup, at which point the following commands can be used to redo.

% mysqlbinlog binlog.1234 | mysql --one-database db_name
% mysqlbinlog binlog.1235 | mysql --one_database db_name
...

If we need to recover a database that is corrupted by the execution of the DROP database,drop table or DELETE statement, we need to delete the statement in the log file, otherwise the result will be the same. So you need to convert the log to ASCII format and save it, then open the file in the editor, delete the statements, and then redo.

If you use a text change journal, you do not need to use the Mysqlbinlog program because the log file itself is in ASCII format.

Restoring a datasheet that is backed up using the Backup Table command can be a restore TABLE statement:

Backup statement:

mysql> BACKUP TABLE table_name1,table_name2 TO '/backup/table_backup';

Recovery statement:

mysql> RESTORE TABLE table_name1,table_name2 FROM '/backup/table_backup';

To restore a datasheet with a foreign key, you can use set Foreign_key_check = 0 To turn off the key word check, import the table, and then restart it, and assign a value of 1 to indicate that the check is valid.

Restores the InnoDB tablespace, and when the server restarts, the InnoDB handler performs automatic recovery and sets the level of automatic recovery through the Set-variable=innodb_force_recovery=level in the option file [mysqld] section. A typical startup value of 4 is recommended. If you need to recover from a backup file, this is the same as the method described above. The same is the case for BDB data tables, which are automatically restored when the server is started.