Binlog介绍
binlog 是记录所有数据库表结构变更(例如CREATE、ALTER TABLE…)以及表数据修改(INSERT、UPDATE、DELETE…)的二进制日志。
binlog 不会记录 SELECT 和 SHOW 这类操作,因为这类操作对数据本身并没有修改,但你可以通过查询通用日志来查看MySQL 执行过的所有语句。
二进制日志包括两类文件:二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件,二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句)语句事件。
Binlog模式
binlog有三种模式:ROW(行模式), Statement(语句模式), Mixed(混合模式)
查看。开启
查看 mysql 是否开启binlog
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
查看mysql的binlog模式
#三种模式,Mysql5.7
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
通过配置文件开启 binlog
[root@localhost ~]# vim /etc/my.cnf
server-id=1 #主从参数
log-bin=mysql-bin #开启binlog
binlog-format=ROW #指定binlog 模式
relay-log=mysql-relay
mysql中binlog 相关命令
#获取binlog文件日志列表
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 561 |
| mysql-bin.000002 | 348 |
| mysql-bin.000003 | 125 |
| mysql-bin.000004 | 106 |
+------------------+-----------+
4 rows in set (0.00 sec)
#查看当前正在写入 binlog文件
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
#查看master 的binlog文件
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 561 |
| mysql-bin.000002 | 348 |
| mysql-bin.000003 | 125 |
| mysql-bin.000004 | 106 |
+------------------+-----------+
4 rows in set (0.00 sec)
# 查看第一个binlog 文件内容
mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+---------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 106 | Server ver: 5.1.73-log, Binlog ver: 4 |
| mysql-bin.000001 | 106 | Query | 1 | 244 | grant replication slave on *.* to 'tom'@'%' identified by '123' |
| mysql-bin.000001 | 244 | Query | 1 | 319 | flush privileges