, , , , , binlog
1.binlog
binlog , , ;
、 、MySQL ( )。
sql binlog 。
2. binlog
(1) binlog_format
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.01 sec)
binlog
binlog :Statement、Row Mixed。
– SQL (statement-based replication,SBR),
– (row-based replication,RBR),
– (mixed-based replication,MBR)。
:mysql > set globle binlog_format='MIXED';
3.binlog 、
binlog
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.01 sec)
4. binlog
mysql> show binary logs;
+-----------------------+------------+
| Log_name | File_size |
+-----------------------+------------+
| mysql-bin-db02.000001 | 98131952 |
| mysql-bin-db02.000002 | 4954 |
| mysql-bin-db02.000003 | 248051834 |
| mysql-bin-db02.000004 | 22305210 |
| mysql-bin-db02.000005 | 177 |
| mysql-bin-db02.000006 | 913 |
| mysql-bin-db02.000007 | 1075503915 |
| mysql-bin-db02.000008 | 101154053 |
+-----------------------+------------+
8 rows in set (0.00 sec)
5. binlog
mysql> show master logs;
+-----------------------+------------+
| Log_name | File_size |
+-----------------------+------------+
| mysql-bin-db02.000001 | 98131952 |
| mysql-bin-db02.000002 | 4954 |
| mysql-bin-db02.000003 | 248051834 |
| mysql-bin-db02.000004 | 22305210 |
| mysql-bin-db02.000005 | 177 |
| mysql-bin-db02.000006 | 913 |
| mysql-bin-db02.000007 | 1075503915 |
| mysql-bin-db02.000008 | 101481837 |
+-----------------------+------------+
8 rows in set (0.00 sec)
6. binlog
mysql > show binlog events
7. binlog
mysql > show binlog events in 'mysql-bin.000002';
8. binlog
mysql > show master status\G
9. binlog
mysql > show binary logs;
10. mysqlbinlog
:
binlog
--force
binlog , -vv
,
/
mysqlbinlog --start-datetime='2017-05-10 00:00:00' --stop-datetime='2017-09-10 01:01:01' -d
# : mysql binlog ROW , -vv
mysqlbinlog --start-datetime='2016-12-20 09:27:54' --stop-datetime='2016-12-20 09:30:40' -vv MySQL-binlog.000001 >/root/1.log -- ,-d db1
pos
mysqlbinlog --start-postion=107 --stop-position=1000 -d
12.
/ , t.binlog
mysqlbinlog -u username -p password -h 172.16.22.7 -P3306 \
--read-from-remote-server --start-datetime='2017-05-10 23:00:00' --stop-datetime='2017-09-10 23:30:00' mysql-bin.000001 > t.binlog
t.binlog , 。