A brief talk on MySQL's Binlog

Check to see if Binlog is turned on

Mysql>Show variables like '%bin%';+-----------------------------------------+----------------------+|Variable_name|Value|+-----------------------------------------+----------------------+|Binlog_cache_size| 32768                ||Binlog_direct_non_transactional_updates| OFF                  ||Binlog_format|MIXED||Binlog_stmt_cache_size| 32768                ||Innodb_locks_unsafe_for_binlog| OFF                  ||Log_bin| On # #开了                   ||Log_bin_trust_function_creators| OFF                  ||Max_binlog_cache_size| 18446744073709547520 ||Max_binlog_size| 1073741824           ||Max_binlog_stmt_cache_size| 18446744073709547520 ||Sql_log_bin|  on                   ||Sync_binlog| 0                    |+-----------------------------------------+----------------------+ ARowsinch Set(0.00Sec

Open Binlog

#在my. CNF [mysqld] Log -Bin=mysql-binbinlog_format=Mixed
Expire_logs_days = #设置过期自动回收时间

#以下可选
Log_bin_basename=/var/lib/mysql/mysql-bin

#log日志默认会在数据目录里

Delete binlog file

#删除5天前的binlog日志mysql > 5  Day  0 rows affected (0.01  sec) #重置日志mysql>0 rows Affected (0.02  sec) #注意: Pay attention to manual cleaning, if there is a master-slave synchronization, not enough time to synchronize the log is cleaned, the active synchronization will result in inconsistent data

show slave status\g; #检查从服务器正在读取哪个日志, there are multiple slave servers, choosing the oldest one as the target log.

The Mysqlbinlog command uses

1. View the contents of the Binlog file

Mysqlbinlog  MySQL-bin.  000004
#
[Email protected] log]$ Mysqlbinlog mysql-bin.000004
Mysqlbinlog:unknown variable ' Default-character-set=utf8 '
The following error resolution occurs:
Mysqlbinlog--no-defaults mysql-bin.000004

2. Convert Binlog to available SQL

1> General convert all content in Binlog log to SQL file

Mysqlbinlog MySQL-bin. 000005 > bin.sql

2> extracts the Binlog content corresponding to the specified library

#-D Specify database
Mysqlbinlog–d test MySQL-bin. 000005 > bin.sql

3> to extract content at a specified location point

Mysqlbinlog MySQL-bin. 000020 –start-position=365 –stop-position=456  > bin.sql

4> to extract content at a specified point in time

Mysqlbinlog MySQL-Bin.000020–start-datetime=’ .- on- on  -: -:xx' –stop-datetime=’ .- on- on  -: $:xx’>Bin.sql

A brief talk on MySQL's Binlog