Rotten mud: Learn mysql binlog configuration, mysqlbinlog

Rotten mud: Learn mysql binlog configuration, mysqlbinlog

This article was sponsored by Xiuyi linfeng and first launched in the dark world.

1Basic knowledge

Logs record every change in the database to a dedicated file, which is called a log file. By default, only error logs are enabled for mysql, because too many logs will affect the processing performance of the system.

Logs in text and binary formats are supported in versions earlier than MySQL, but only logs in binary format are supported in Versions later than MySQL. Because binary logs have more advantages in performance and information processing.

2Enable mysql binary log

Mysql binary logs are enabled by the log-bin option of the configuration file my. cnf.

By default, the mysql server creates two new files in the data root directory: XXX-bin.000001 and XXX-bin.index. If the configuration option does not provide a file name, mysql uses mysql-bin to name these two files. The. index file contains a complete log file list. As follows:

Cat/etc/my. cnf

Ll/usr/local/mysql/data/

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

MysqlThe user's changes to the content and structure of all databases are recorded in the XXX-bin.n file, but the SELECT and UPDATE statements without actual updates are not logged.

Of course, we can also query whether mysql has enabled binlog with related commands. As follows:

Show variables like 'Log _ % ';

3Binlog log file generation

When mysql is stopped or restarted, the server will record the log file to the next log file. mysql will generate a new log file upon restart, and the file sequence number will increase sequentially.

If the log file exceeds the upper limit of the max_binlog_size (configured in my. cnf file) system variable configuration, a new log file is generated.

In addition, if you execute the flush logs command in the mysql command, a new log file is generated.

4View binlog logs in the system

View binlog logs in the system and use the show master logs command. As follows:

Show master logs;

5View binlog log files

Mysql provides the mysqlbinlog command to view log files. When recording each change log, the log file records the current time for database recovery. As follows:

Mysqlbinlog mysql-bin.000001 | more

6Use binlog for database recovery

In the event of a disaster, you should use the last full backup to restore the database, and then use the binlog log file after the backup to restore the database to the nearest available state.

Binlog logs must be restored sequentially, that is, the earliest log files must be restored first. The command format for data recovery is as follows:

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

I will introduce in detail the use of binlog for database restoration in future.

7Binlog command line parameters

Log-bin [= file_name] This parameter indicates that the binlog function is enabled and the path name can be customized. The default value is mysql-bin.

Binlog_format this parameter configures the binlog log format. The default value is mixed.

Max_binlog_size this parameter configures the maximum value of binlog logs. The maximum value and default value are 1 GB.

Max_binlog_cache_size this parameter indicates the maximum memory usage of binlog.

Binlog-do-db = db_name this parameter indicates that only binary logs of the specified database are recorded.

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

Expire_logs_days this parameter indicates the retention time of binlog logs. The default unit is day.

The binlog configuration in the my. cnf configuration file is as follows:

Cat/etc/my. cnf | grep-v ^ # | grep-v ^ $

8Delete binlog

8.1Delete some binlog logs

To delete some binlog logs, you can delete them by log name and log generation time. The DELETE command is as follows:

Purge {master | binary} logs to 'Log _ name ';

Delete logs by log name. Only logs before log_name are deleted. log_name itself is not deleted.

From this we can see that the logs before the mysql-bin.000003 have been all deleted.

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

Delete A Log Based on the log generation time. Only logs before date are deleted. The date itself is not deleted.

8.2Delete all binlog logs

Delete all the previous binlog logs and generate new binlog with the suffix starting from 000001. Run the following command:

Reset master;

Mysql binlog log Switch

This is set in the mysql configuration file ~~~ Set to enable binary log ~~~

Why MySQL binlog

The danger is simple: they don't work the way you think they do. consider the following scenario: you set binlog-ignore-db to "garbage" so data in the garbage database (which doesn't exist on the slave) isn' t replicated. (I'll come back to this in a second, so if you already see the problem, don't rush to the comment form .) now you do the following: $ mysqlmysql delete from garbage. junk; mysq L use garbage; mysql update production. users set disabled = 1 where user = "root"; You just broke replication, twice. once, because your slave is going to execute the first query and there's no such table "garbage. junk "on the slave. the second time, silently, because the update to production. users isn' t replicated, so now the root user isn' t disabled on the slave. the copy will be broketwice. For the first time, because slave tries to go west, you give the first sentence But there is no such table "garbage. junk ", the second time, implicit, because of. users will not be copied because the root account is not disabled on slave. why? Because binlog-ignore-db doesn't do what you think. the phrase I used earlier, "data in the garbage database isn' t replicated," is a fallacy. that's not what it does. in fact, itfilters out binary logging for statements issued from connections whose default database is "garbage. "In other words, filtering is not based on the contents of the query -- it is based on what database you USE. the other configuration options I mentioned work similarly. the binlog-do-db and binlog-ignore ...... remaining full text>