MySQL-日志&二进制日志binlog初探

mysql> show variables like '%slow_query%';  # 我这里设置打开了，默认关闭
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | ON                              |
| slow_query_log_file | /var/lib/mysql/artisan-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.00 sec)
mysql> 

mysql> show variables like 'long_query_time';  # 默认10秒
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
mysql> 

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)
mysql>

mysql> show variables like 'log_error';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| log_error     | /var/log/mysqld.log |
+---------------+---------------------+
1 row in set (0.01 sec)
mysql> 

[root@artisan ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 44
Server version: 5.7.29-log MySQL Community Server (GPL)
.....
.....
mysql> show variables like 'binlog_format'; # 查看当前binlog的格式
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)
mysql> set session binlog_formate=statement; # 修改session级别的binlog格式为statement
mysql> set session binlog_format=statement;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'binlog_format';  #确认下当前binlog的格式
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       177 |
....
....
| mysql-bin.000049 |      177 |
+------------------+-----------+
45 rows in set (0.00 sec)
mysql> flush logs; #  刷新log ,会产生一个新的binlog
Query OK, 0 rows affected (0.05 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
....
....
| mysql-bin.000049 |      177 |
| mysql-bin.000050 |      154 |
+------------------+-----------+
46 rows in set (0.00 sec)
mysql> 下面随便搞点操作，方便观察binlog
mysql> create database artisan2;
Query OK, 1 row affected (0.00 sec)
mysql> use artisan2;
Database changed
mysql> create table t(id int , c1 varchar(10));
Query OK, 0 rows affected (0.30 sec)
mysql> insert into t values(1,'a'),(2,'b');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> 

[root@artisan binlog]# pwd
/var/lib/mysql/data/binlog
[root@artisan binlog]# mysqlbinlog  mysql-bin.000050 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200131 16:15:14 server id 1  end_log_pos 123 CRC32 0xf1d6a9f8  Start: binlog v 4, server v 5.7.29-log created 200131 16:15:14
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
EuIzXg8BAAAAdwAAAHsAAAABAAQANS43LjI5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
Afip1vE=
'/*!*/;
# at 123
#200131 16:15:14 server id 1  end_log_pos 154 CRC32 0x19c20618  Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@artisan binlog]# 

binlog_row_image = FULL | MINIMAL | NOBLOB  （3个选项，默认FULL）

mysql> show variables like 'binlog_row_image';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+
1 row in set (0.00 sec)
mysql> set session binlog_row_image=minimal;  # 修改 
Query OK, 0 rows affected (0.00 sec)

[root@artisan binlog]# mysqlbinlog -vv  mysql-bin.000050

binlog_format=MIXED

## binlog相关的命令 
```sql
# 查看是否开启binlog
mysql>show binary logs;
#查看binlog格式：
mysql>show variables like 'binlog_format';
#获取binlog文件列表：
mysql>show binary logs;
#查看当前正在写入的binlog文件：
mysql>show master status;
#查看master上的binlog：
mysql>show master logs;
#只查看第一个binlog文件的内容：
mysql>show binlog events;
#查看指定binlog文件的内容：
mysql>show binlog events in 'mysql-bin.000045';
#清空所有的bin-log：
mysql>reset master;
#生成一个新的binlog：
mysql>flush logs;