Demo of enabling binary logs in MySQL

Source: Internet
Author: User

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:

  1. server-id = 1 
  2. log-bin = binlog 
  3. 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:


For slave servers in the backup replication system, and files should also be backed up.

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

  1. /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:

  1. /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)


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.