Demo of enabling binary logs in MySQL

This article describes how to enable binlog in MySQL and how to introduce SQL statements in actual operations. The following describes the main content of this article, I hope you will have a better understanding of the actual operations after browsing.

The binlog method is more flexible and labor-saving, and supports Incremental backup.

Mysqld must be restarted when binlog is enabled. First, close mysqld, open my. cnf, and add the following lines:

 
 

  
  
server-id = 1 
  
  
log-bin = binlog 
  
  
log-bin-index = binlog.index 
 
 

Start mysqld. Binlog.000001 and binlog. index will be generated during the running process. The previous file is the update operation of mysqld to record all data, and the subsequent file is the index of all binlogs, which cannot be easily deleted. For information about binlog, see the manual.

When you need to back up data, you can execute an SQL statement to stop mysqld from writing data to the current binlog, and then back up the file directly. This will achieve the purpose of Incremental Backup:

 
 

  
  
FLUSH LOGS; 
 
 

For slave servers in the backup replication system, master.info and relay-log.info files should also be backed up.

The binlog file backed up can be viewed using mysqlbinlog, a tool provided by MySQL, for example:

 
 

  
  
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001 
 
 

This tool allows you to display all the SQL statements in the specified database, and you can also limit the time range, which is quite convenient. For details, please refer to the manual.

You can use a statement similar to the following to restore data:

 
 

  
  
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001 | mysql -uyejr -pyejr db_name 
 
 

Execute the SQL statement output by mysqlbinlog directly as the input.

If you have an idle machine, use this method for backup. Because the performance requirements of the Server Load balancer are not that high, the cost is low. with low costs, Incremental Backup can be achieved and the Data Query pressure can be shared. Why not?

The above content introduces how to enable binary logs for MySQL. I hope you will get some benefits.

 

Original article title: binlog)

Connection: http://www.cnblogs.com/kfarvid/archive/2009/11/12/1601587.html