Point-in-Time (Incremental) Recovery Using the Binary Log

朱雀 2022-08-11 00:41 32阅读 0赞

\[[\+/-][-]\]

[7.5.1 Point-in-Time Recovery Using Event Times][] [7.5.2 Point-in-Time Recovery Using Event Positions][]

Point-in-time recovery refers to recovery of data changes made since a given point in time. Typically, this type of recovery is performed after restoring a full backup that brings the server to its state as of the time the backup was made. (The full backup can be made in several ways, such as those listed in [Section 7.2, “Database Backup Methods”][Section_7.2_ _Database Backup Methods].) Point-in-time recovery then brings the server up to date incrementally from the time of the full backup to a more recent time.

Point-in-time recovery is based on these principles:

*  The source of information for point-in-time recovery is the set of incremental backups represented by the binary log files generated subsequent to the full backup operation. Therefore, the server must be started with the [`--log-bin`][--log-bin] option to enable binary logging (see [Section 5.2.3, “The Binary Log”][Section_5.2.3_ _The Binary Log]).
    
    To restore data from the binary log, you must know the name and location of the current binary log files. By default, the server creates binary log files in the data directory, but a path name can be specified with the [`--log-bin`][--log-bin] option to place the files in a different location. [Section 5.2.3, “The Binary Log”][Section_5.2.3_ _The Binary Log].
    
    To see a listing of all binary log files, use this statement:
    
        mysql> SHOW BINARY LOGS;
    
    To determine the name of the current binary log file, issue the following statement:
    
        mysql> SHOW MASTER STATUS;
 *  The [mysqlbinlog][] utility converts the events in the binary log files from binary format to text so that they can be executed or viewed. [mysqlbinlog][] has options for selecting sections of the binary log based on event times or position of events within the log. See [Section 4.6.7, “mysqlbinlog — Utility for Processing Binary Log Files”][mysqlbinlog].
 *  Executing events from the binary log causes the data modifications they represent to be redone. This enables recovery of data changes for a given span of time. To execute events from the binary log, process [mysqlbinlog][]output using the [mysql][] client:
    
        shell> mysqlbinlog binlog_files | mysql -u root -p
 *  Viewing log contents can be useful when you need to determine event times or positions to select partial log contents prior to executing events. To view events from the log, send [mysqlbinlog][] output into a paging program:
    
        shell> mysqlbinlog binlog_files | more
    
    Alternatively, save the output in a file and view the file in a text editor:
    
        shell> mysqlbinlog binlog_files > tmpfile
        shell> ... edit tmpfile ...
 *  Saving the output in a file is useful as a preliminary to executing the log contents with certain events removed, such as an accidental [`DROP DATABASE`][DROP DATABASE]. You can delete from the file any statements not to be executed before executing its contents. After editing the file, execute the contents as follows:
    
        shell> mysql -u root -p < tmpfile

If you have more than one binary log to execute on the MySQL server, the safe method is to process them all using a single connection to the server. Here is an example that demonstrates what may be unsafe:

shell> mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!
    shell> mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!

Processing binary logs this way using different connections to the server causes problems if the first log file contains a [`CREATE TEMPORARY TABLE`][CREATE TEMPORARY TABLE] statement and the second log contains a statement that uses the temporary table. When the first [mysql][] process terminates, the server drops the temporary table. When the second [mysql][] process attempts to use the table, the server reports “unknown table.”

To avoid problems like this, use a single connection to execute the contents of all binary logs that you want to process. Here is one way to do so:

shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

Another approach is to write all the logs to a single file and then process the file:

shell> mysqlbinlog binlog.000001 > /tmp/statements.sql
    shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql
    shell> mysql -u root -p -e "source /tmp/statements.sql"

\[[\+/-][-]\]

[7.5.1 Point-in-Time Recovery Using Event Times][] [7.5.2 Point-in-Time Recovery Using Event Positions][]

Point-in-time recovery is based on these principles:

shell> mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!
    shell> mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!

To avoid problems like this, use a single connection to execute the contents of all binary logs that you want to process. Here is one way to do so:

shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

Another approach is to write all the logs to a single file and then process the file:

shell> mysqlbinlog binlog.000001 > /tmp/statements.sql
    shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql
    shell> mysql -u root -p -e "source /tmp/statements.sql"

备注：该篇技术文档是与MySQL数据库中的“备份恢复”主题相关的技术主题。  
  
  
本文转载自：http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html

\[[\+/-][-]\]

[7.5.1 Point-in-Time Recovery Using Event Times][] [7.5.2 Point-in-Time Recovery Using Event Positions][]

Point-in-time recovery is based on these principles:

*  The source of information for point-in-time recovery is the set of incremental backups represented by the binary log files generated subsequent to the full backup operation. Therefore, the server must be started with the [`--log-bin`][--log-bin] option to enable binary logging (see [Section 5.2.3, “The Binary Log”][Section_5.2.3_ _The Binary Log]).
    
    To restore data from the binary log, you must know the name and location of the current binary log files. By default, the server creates binary log files in the data directory, but a path name can be specified with the [`--log-bin`][--log-bin] option to place the files in a different location. [Section 5.2.3, “The Binary Log”][Section_5.2.3_ _The Binary Log].
    
    To see a listing of all binary log files, use this statement:
    
        mysql> SHOW BINARY LOGS;
    
    To determine the name of the current binary log file, issue the following statement:
    
        mysql> SHOW MASTER STATUS;
 *  The [**mysqlbinlog**][mysqlbinlog] utility converts the events in the binary log files from binary format to text so that they can be executed or viewed. [**mysqlbinlog**][mysqlbinlog] has options for selecting sections of the binary log based on event times or position of events within the log. See [Section 4.6.7, “**mysqlbinlog** — Utility for Processing Binary Log Files”][mysqlbinlog].
 *  Executing events from the binary log causes the data modifications they represent to be redone. This enables recovery of data changes for a given span of time. To execute events from the binary log, process [**mysqlbinlog**][mysqlbinlog]output using the [**mysql**][mysql] client:
    
        shell> mysqlbinlog binlog_files | mysql -u root -p
 *  Viewing log contents can be useful when you need to determine event times or positions to select partial log contents prior to executing events. To view events from the log, send [**mysqlbinlog**][mysqlbinlog] output into a paging program:
    
        shell> mysqlbinlog binlog_files | more
    
    Alternatively, save the output in a file and view the file in a text editor:
    
        shell> mysqlbinlog binlog_files > tmpfile
        shell> ... edit tmpfile ...
 *  Saving the output in a file is useful as a preliminary to executing the log contents with certain events removed, such as an accidental [`DROP DATABASE`][DROP DATABASE]. You can delete from the file any statements not to be executed before executing its contents. After editing the file, execute the contents as follows:
    
        shell> mysql -u root -p < tmpfile

shell> mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!
    shell> mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!

Processing binary logs this way using different connections to the server causes problems if the first log file contains a [`CREATE TEMPORARY TABLE`][CREATE TEMPORARY TABLE] statement and the second log contains a statement that uses the temporary table. When the first [**mysql**][mysql] process terminates, the server drops the temporary table. When the second [**mysql**][mysql] process attempts to use the table, the server reports “unknown table.”

To avoid problems like this, use a *single* connection to execute the contents of all binary logs that you want to process. Here is one way to do so:

shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

Another approach is to write all the logs to a single file and then process the file:

shell> mysqlbinlog binlog.000001 > /tmp/statements.sql
    shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql
    shell> mysql -u root -p -e "source /tmp/statements.sql"

[-]: http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html#
[7.5.1 Point-in-Time Recovery Using Event Times]: http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery-times.html
[7.5.2 Point-in-Time Recovery Using Event Positions]: http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery-positions.html
[Section_7.2_ _Database Backup Methods]: http://dev.mysql.com/doc/refman/5.0/en/backup-methods.html
[--log-bin]: http://dev.mysql.com/doc/refman/5.0/en/replication-options-binary-log.html#option_mysqld_log-bin
[Section_5.2.3_ _The Binary Log]: http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
[mysqlbinlog]: http://dev.mysql.com/doc/refman/5.0/en/mysqlbinlog.html
[mysql]: http://dev.mysql.com/doc/refman/5.0/en/mysql.html
[DROP DATABASE]: http://dev.mysql.com/doc/refman/5.0/en/drop-database.html
[CREATE TEMPORARY TABLE]: http://dev.mysql.com/doc/refman/5.0/en/create-table.html