MySQL: How can I recover a MySQL database with the binary log function log-bin enabled?

MySQL binary logs are not only used for replicate \ audit, but can be used for backup and restoration like other RDBMS systems.

The procedure is as follows:

(Optional): backs up binary log files.

Step 1: Start the server mysqld: make sure to use the following options:

 
# Cancel binary startup
# Log-bin

# Allow salve I/O thread to read its own server-ID binary log records
Replicate-same-server-ID

# The slave thread is not automatically started. Be prepared,Optional
Skip-slave-start

# Define the basename of relay-Log
Relay-Log="Original log-bin (binary log) basename"
 

Step 2: Configure slave thread information change master

Change master
Relay_log_file='Start binary log file',
Relay_log_pos=Position of the starting binary log file,
Master_host='A fake unavailable host or IP address'
;
 

The "master_host" in this statement is only required by the syntax and does not actually connect to it.

Step 3: Start the slave thread and restore it.

 
Start slave;
 

Step 4: After restoration, start MySql in normal mode. Remember: Clear the salve files in the data directory!

 
Remember,Clear the slave-related files under the data directory!
 

All the key points are to establish a good backup policy to provide complete backup information. in this way, DBAs must provide the backup time point, BINLOG file index number, and even show Master Status information. otherwise, the Restore point must be confirmed logically!