How to back up and restore a MySQL database with log-bin Enabled

MySQL databaseOfBinary logNot only for Replicate \ audit, but also for backup and restoration like other RDBMS systems. Next, we will introduce how to back up and restore the MySQL database with log-bin enabled.

The procedure is as follows:

(Optional): backs up binary log files.

Step 1: Start the server mysqld: Make sure some options are used

 
 

  
  
# 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. You can leave it unspecified for the sake of security.
  
  
 
  
  
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 false 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 in the data directory!

In this way, the restoration is successful. Here is an introduction to the backup and restoration of the MySQL database with log-bin enabled. I hope you will have some benefits!