MySQL的binlog操作
1. MySQL的binlog有三种模式: statement, row and mixed, 从5.1开始支持row, 默认是row模式
2. 设置参数
# 要配置在mysqld下
[mysqld]
binlog_format=ROW
设置binlog过期清理时间 expire_logs_days
# Should be unique server-id = 1 log-bin = master-bin # The number of days for automatic binary log file removal expire_logs_days = 14 # 0:log&flush per 1s; 1:log&flush per commit; 2:log per commit & flush per 1s innodb_flush_log_at_trx_commit=0 # The number of binary log commit groups to collect before sync to disk. sync_binlog = 10
3. 查看binlog是否开启及其参数
mysql> show variables like 'log_bin%'; +---------------------------------+---------------------------------+ | Variable_name | Value | +---------------------------------+---------------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/master-bin | | log_bin_index | /var/lib/mysql/master-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | +---------------------------------+---------------------------------+ 5 rows in set (0.01 sec) mysql> show variables like 'binlog%'; +-----------------------------------------+--------------+ | Variable_name | Value | +-----------------------------------------+--------------+ | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_direct_non_transactional_updates | OFF | | binlog_error_action | ABORT_SERVER | | binlog_format | ROW | | binlog_group_commit_sync_delay | 0 | | binlog_group_commit_sync_no_delay_count | 0 | | binlog_gtid_simple_recovery | ON | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | ON | | binlog_row_image | FULL | | binlog_rows_query_log_events | OFF | | binlog_stmt_cache_size | 32768 | +-----------------------------------------+--------------+ 13 rows in set (0.00 sec)
4. 查看服务器的binlog状态
mysql> show binary logs; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-bin.000008 | 321270 | | master-bin.000009 | 1026 | | master-bin.000010 | 10103 | | master-bin.000011 | 544150473 | | master-bin.000012 | 4155620 | | master-bin.000013 | 21550593 | +-------------------+-----------+ 6 rows in set (0.00 sec) mysql> show master logs; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-bin.000008 | 321270 | | master-bin.000009 | 1026 | | master-bin.000010 | 10103 | | master-bin.000011 | 544150473 | | master-bin.000012 | 4155620 | | master-bin.000013 | 21550593 | +-------------------+-----------+ 6 rows in set (0.00 sec) mysql> show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000013 | 21551221 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
5. 查看binlog内容
# 查看指定binlog文件的内容语法： SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
例如
mysql> show binlog events limit 0, 5; +-------------------+-----+----------------+-----------+-------------+------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+-----+----------------+-----------+-------------+------------------------------------------+ | master-bin.000008 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.14-log, Binlog ver: 4 | | master-bin.000008 | 123 | Previous_gtids | 1 | 154 | | | master-bin.000008 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | master-bin.000008 | 219 | Query | 1 | 290 | BEGIN | | master-bin.000008 | 290 | Query | 1 | 385 | use `yic`; DELETE FROM `yic`.`u_session` | +-------------------+-----+----------------+-----------+-------------+------------------------------------------+ 5 rows in set (0.00 sec)
mysql> show binlog events in 'master-bin.000013' limit 0, 5; +-------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+-----+----------------+-----------+-------------+---------------------------------------+ | master-bin.000013 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.14-log, Binlog ver: 4 | | master-bin.000013 | 123 | Previous_gtids | 1 | 154 | | | master-bin.000013 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | master-bin.000013 | 219 | Query | 1 | 290 | BEGIN | | master-bin.000013 | 290 | Table_map | 1 | 373 | table_id: 111 (yic.u_session_log) | +-------------------+-----+----------------+-----------+-------------+---------------------------------------+ 5 rows in set (0.00 sec)
注意: 都是按时间正序排列.
6. 使用mysqlbinlog查看binlog
# 提取指定的binlog日志 (参数需要用绝对路径) mysqlbinlog /opt/data/APP01bin.000001 # 根据位置提取 mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 # 根据开始结束时间提取 mysqlbinlog --start-datetime="2014-12-15 20:15:23" --stop-datetime="2014-12-15 20:30:23" /opt/data/APP01bin.000002 --result-file=extra02.sql # 对多个binlog文件, 提取指定数据库, 并转换字符集 mysqlbinlog --database=test --set-charset=utf8 /opt/data/APP01bin.000001 /opt/data/APP01bin.000002 >test.sql # 提取远程的binlog, 并输出到本地 mysqlbinlog -u someone -p -P3306 -h192.168.1.177 --read-from-remote-server -vv inst3606bin.000005 >row.sql
mysqlbinlog 查看row模式的binlog
# 使用--base64-output=decode-rows -v参数, 就能看懂了 mysqlbinlog --base64-output=decode-rows -v /var/lib/mysql/master-bin.000013|more
