1. MySQL的binlog有三种模式: statement, row and mixed, 从5.1开始支持row, 默认是row模式
# 要配置在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 |
+---------------------------------+---------------------------------+
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 |
+-----------------------------------------+--------------+
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 |
+-------------------+-----------+
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 |
+-------------------+-----------+
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 | | | |
+-------------------+----------+--------------+------------------+-------------------+
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` |
+-------------------+-----+----------------+-----------+-------------+------------------------------------------+
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