Slime: Learn binlog configuration for MySQL

this article starting at Slime Line the world .

1 , Basic knowledge

The log is to record every change in the database into a dedicated file, which is called a log file. MySQL only turns on the error log by default, because too many logs can affect the processing performance of the system.

Text format and binary format logs are supported in previous versions of mysql5.0, but only binary format logs are supported in the mysql5.0 version. Because the binary log in the performance, information processing and other aspects have more advantages.

2 , enable MySQL binary log

MySQL binary logs are enabled by the Log-bin option for profile my.cnf.

The MySQL server defaults to creating two new files in the data root directory: xxx-bin.000001 and Xxx-bin.index. If the configuration option does not give a filename, MySQL will use Mysql-bin to name the two files, where the. index file contains a complete list of log files. As follows:

Cat/etc/my.cnf

ll/usr/local/mysql/data/

Cat/usr/local/mysql/data/mysql-bin.index

650) this.width=650; "title=" clip_image001[4] "style=" border-right-width:0px;background-image:none; border-bottom-width:0px;padding-top:0px;padding-left:0px;margin:0px;padding-right:0px;border-top-width:0px; " Border= "0" alt= "clip_image001[4" "src=" http://img1.51cto.com/attachment/201411/7/526870_1415325450dHBW.png "width = "351" height= "148"/>

650) this.width=650; "title=" clip_image002[4] "style=" border-right-width:0px;background-image:none; border-bottom-width:0px;padding-top:0px;padding-left:0px;margin:0px;padding-right:0px;border-top-width:0px; " Border= "0" alt= "clip_image002[4" "src=" http://img1.51cto.com/attachment/201411/7/526870_1415325450GXgq.png "width = "469" height= "369"/>

MySQL The user's changes to the contents and structure of all databases are recorded in the XXX-BIN.N file, but the Select and UPDATE statements with no actual updates are not logged.

Of course, we can also query the relevant command, whether MySQL has opened the Binlog log. As follows:

Show variables like ' log_% ';

650) this.width=650; "title=" clip_image003[4] "style=" border-right-width:0px;background-image:none; border-bottom-width:0px;padding-top:0px;padding-left:0px;margin:0px;padding-right:0px;border-top-width:0px; " Border= "0" alt= "clip_image003[4" "src=" http://img1.51cto.com/attachment/201411/7/526870_1415325451HRTY.png "width = "520" height= "277"/>

3 , binlog log file generation

When MySQL stops or restarts, the server logs the log file into the next log file, and MySQL generates a new log file on reboot, incrementing the file sequence number.

A new log file is also generated if the log file exceeds the upper bound of the system variable configuration in Max_binlog_size (configured in the My.cnf file).

In addition, a new log file is generated if the flush logs command is executed in the MySQL command.

650) this.width=650; "title=" clip_image004[4] "style=" border-right-width:0px;background-image:none; border-bottom-width:0px;padding-top:0px;padding-left:0px;margin:0px;padding-right:0px;border-top-width:0px; " Border= "0" alt= "clip_image004[4" "src=" http://img1.51cto.com/attachment/201411/7/526870_1415325451c8En.png "width = "377" height= "154"/>

4 , viewing the Binlog log in the system

Review the Binlog logs in the system, using the show Master Logs command. As follows:

Show master logs;

650) this.width=650; "title=" clip_image005[4] "style=" border-right-width:0px;background-image:none; border-bottom-width:0px;padding-top:0px;padding-left:0px;margin:0px;padding-right:0px;border-top-width:0px; " Border= "0" alt= "clip_image005[4" "src=" http://img1.51cto.com/attachment/201411/7/526870_1415325451gc0F.png "width = "271" height= "238"/>

5 , view binlog log files

MySQL provides the mysqlbinlog command to view the log files, and log files will record the current time when each change log is logged for database recovery. As follows:

Mysqlbinlog mysql-bin.000001| More

650) this.width=650; "title=" clip_image006[4] "style=" border-right-width:0px;background-image:none; border-bottom-width:0px;padding-top:0px;padding-left:0px;margin:0px;padding-right:0px;border-top-width:0px; " Border= "0" alt= "clip_image006[4" "src=" http://img1.51cto.com/attachment/201411/7/526870_141532545280UJ.png "width = "606" height= "446"/>

6 , using Binlog logs for database recovery

In the event of a disaster, you should restore the database with the most recent full backup, and then use the Binlog log after the backup to restore the database to the closest available state.

When using the Binlog log for recovery, you need to proceed sequentially, that is, the oldest generated log files are restored first. The command format for recovering data is as follows:

Mysqlbinlog xxx-bin.000001|mysql-u root–p Password dataname

For a database recovery using Binlog, I'll take a detailed introduction to future articles.

7 , binlog command line arguments

Log-bin [=file_name] This parameter indicates that the Binlog logging feature is enabled, and you can customize the path name by default to Mysql-bin.

Binlog_format This parameter configures the Binlog log format, which defaults to mixed.

Max_binlog_size This parameter configures the log maximum value for Binlog, and the maximum and default value is 1GB.

Max_binlog_cache_size This parameter indicates the maximum amount of memory used by Binlog.

Binlog-do-db=db_name This parameter indicates that only the binary log of the specified database is logged.

Binlog-ignore-db=db_name This parameter indicates that the binary log of the specified database is not logged.

Expire_logs_days This parameter represents the time that the Binlog log is retained, the default unit is days.

MY.CNF configuration file for Binlog configuration is as follows:

CAT/ETC/MY.CNF |grep-v ^#|grep-v ^$

650) this.width=650; "title=" clip_image007[4] "style=" border-right-width:0px;background-image:none; border-bottom-width:0px;padding-top:0px;padding-left:0px;margin:0px;padding-right:0px;border-top-width:0px; " Border= "0" alt= "clip_image007[4" "src=" http://img1.51cto.com/attachment/201411/7/526870_1415325452a5Ha.png "width = "401" height= "405"/>

8 , delete binlog log

8.1 Delete section Binlog log

Delete the Binlog section log, we can delete the log name and log generation time. The delete command is as follows:

Purge {master|binary} logs to ' Log_name ';

Deleted according to the log name, only delete log_name before the log, log_name itself will not be deleted.

650) this.width=650; "title=" clip_image008[4] "style=" border-right-width:0px;background-image:none; border-bottom-width:0px;padding-top:0px;padding-left:0px;margin:0px;padding-right:0px;border-top-width:0px; " Border= "0" alt= "clip_image008[4" "src=" http://img1.51cto.com/attachment/201411/7/526870_1415325452q7rg.png "width = "1010" height= "352"/>

As we can see, the logs before mysql-bin.000003 have been completely deleted.

Purge {master|binary} logs before ' date ';

Based on the time that the log was generated, only the log before date is deleted, and date itself is not deleted.

650) this.width=650; "title=" clip_image009[4] "style=" border-right-width:0px;background-image:none; border-bottom-width:0px;padding-top:0px;padding-left:0px;margin:0px;padding-right:0px;border-top-width:0px; " Border= "0" alt= "clip_image009[4" "src=" http://img1.51cto.com/attachment/201411/7/526870_14153254536f6V.png "width = "978" height= "312"/>

8.2 Delete all Binlog logs

Delete all previous binlog logs and regenerate the new Binlog, starting with the suffix 000001. Use the command:

Reset Master;

650) this.width=650; "title=" clip_image010 "style=" border-right-width:0px;background-image:none; border-bottom-width:0px;padding-top:0px;padding-left:0px;padding-right:0px;border-top-width:0px; "border=" 0 "alt = "clip_image010" src= "http://img1.51cto.com/attachment/201411/7/526870_1415325453evcC.png" width= "877" height= " 331 "/>

This article is from the "Slime Row World" blog, please be sure to keep this source http://ilanni.blog.51cto.com/526870/1573923

Slime: Learn binlog configuration for MySQL