目录：
MySQL日志管理
日志分类
| 日志类型 | 写入日志的信息 |
|---|---|
| error log | 记录mysql启动，运行或停止错误等信息 |
| General query log | 记录数据库里的所有SQL操作记录 |
| Binary log | 二进制日志，以二进制形式记录数据库中更改数据的语句（也用于复制） |
| Relay log | 从复制主服务器收到的数据更改，备库将主库的二进制日志复制到自己的中继日志 |
| Slow query log | 执行时间超过long_query_time的查询 |
重点是binlog日志
错误日志
错误日志使用log_error参数进行定义，默认存放在数据文件目录下的host_name.err;
启动时会提示错误日志位置
$ mysqld_safe --defaults-file=/mysql/etc/my.cnf --user=mysql &
[1] 21652
[mysql@diana mysql]$ 2022-09-04T07:23:10.011930Z mysqld_safe Logging to '/mysql/mariadb/mariadb.log'.
2022-09-04T07:23:10.057422Z mysqld_safe Starting mysqld daemon with databases from /data
后期查询错误日志存放路径的命令如下
mysql> show variables like '%log_error%';
+---------------------+----------------------------+
| Variable_name | Value |
+---------------------+----------------------------+
| binlog_error_action | ABORT_SERVER |
| log_error | /mysql/mariadb/mariadb.log |
| log_error_verbosity | 3 |
+---------------------+----------------------------+
3 rows in set (0.00 sec)
log_error 参数值说明：
没有给定值：默认存放在数据文件目录下的host_name.err;
只给定文件名：默认存放在数据文件目录下;
给定绝对路径：存放在指定位置stderr：日志写入操作系统日志
1、更改路径
编辑配置文件
vi /mysql/etc/my.cnf
修改以下内容
log-error=/mysql/mariadb/mysql3306.log
创建日志文件
mkdir -p /mysql/mariadb
touch /mysql/mariadb/mysql3306.log
2、重启生效
$ mysqladmin -uroot -p -S /mysql/mysql.sock shutdown
Enter password:
2022-09-04T07:42:47.346101Z mysqld_safe mysqld from pid file /mysql/mariadb/mariadb.pid ended
$ mysqld_safe --defaults-file=/mysql/etc/my.cnf --user=mysql &
[1] 21936 2022-09-04T07:42:52.504027Z mysqld_safe Logging to '/mysql/mariadb/mysql3306.log'.
2022-09-04T07:42:52.547853Z mysqld_safe Starting mysqld daemon with databases from /data
日志文件已经更改为
/mysql/mariadb/mysql3306.log
mysql> show variables like '%log_error%';
+---------------------+------------------------------+
| Variable_name | Value |
+---------------------+------------------------------+
| binlog_error_action | ABORT_SERVER |
| log_error | /mysql/mariadb/mysql3306.log |
| log_error_verbosity | 3 |
+---------------------+------------------------------+
3 rows in set (0.01 sec)
3、其他参数
错误日志相关的其他参数：
-
log_timestamps控制写入错误日志的消息中时间戳的时区（默认UTC，生产环境改成SYSTEM：log_timestamps = SYSTEM）
-
log_error_verbosity参数（默认3，一般不改）
-
log_error_verbosity=1：错误信息；
-
log_error_verbosity=2：错误信息和告警信息；
-
log_error_verbosity=3：错误信息、告警信息和通知信息。
示例：更改log_timestamps=system
持久生效
1、编辑配置文件
[mysqld]
log_timestamps=SYSTEM
注意：log_timestamps参数必须在[mysqld]组下，否则不会生效
2、重启数据库
mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)
mysql> 2022-09-04T08:12:45.491979Z mysqld_safe mysqld from pid file /mysql/mariadb/mariadb.pid ended
mysql> \q
Bye
[1]+ Done mysqld_safe --defaults-file=/mysql/etc/my.cnf --user=mysql
$ mysqld_safe --defaults-file=/mysql/etc/my.cnf --user=mysql &
[1] 24170
[mysql@diana etc]$ 2022-09-04T08:12:53.468152Z mysqld_safe Logging to '/mysql/mariadb/mysql3306.log'.
2022-09-04T08:12:53.511937Z mysqld_safe Starting mysqld daemon with databases from /data
$ mysql -uroot -p -S/mysql/mysql.sock --execute="SHOW GLOBAL VARIABLES LIKE 'log_timestamps';"
Enter password:
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| log_timestamps | SYSTEM |
+----------------+--------+
查看日志：
2022-09-04T16:12:53.683309+08:00 0 [Note] /mysql/bin/mysqld (mysqld 5.7.38) starting as process 24383 ...
2022-09-04T16:12:53.687307+08:00 0 [Note] InnoDB: PUNCH HOLE support available
日志时间戳格式已经更改
临时生效
mysql> SHOW GLOBAL VARIABLES LIKE 'log_timestamps';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| log_timestamps | SYSTEM|
+----------------+-------+
1 row in set (0.00 sec)
mysql> SET GLOBAL log_timestamps = UTC;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'log_timestamps';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| log_timestamps | UTC |
+----------------+-------+
1 row in set (0.00 sec)
登录错误测试
$ mysql -uroot -p -S/mysql/mysql.sock
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
查看日志
2022-09-04T08:21:11.956875Z 8 [Note] Access denied for user 'root'@'localhost' (using password: YES)
General log
General log一般不开启，一般用于调试，跟踪使用
如需使用可以在线设置general_log=1(or ON)来开启日志，日志的存放位置由general_log_file参数控制
mysql> show variables like '%general_log%';
+------------------+-----------------+
| Variable_name | Value |
+------------------+-----------------+
| general_log | OFF |
| general_log_file | /data/diana.log |
+------------------+-----------------+
2 rows in set (0.00 sec)
1、在线开启
mysql> set global general_log=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%general_log%';
+------------------+-----------------+
| Variable_name | Value |
+------------------+-----------------+
| general_log | ON |
| general_log_file | /data/diana.log |
+------------------+-----------------+
2 rows in set (0.00 sec)
2、执行sql并查看日志
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> \q
$ tail /data/diana.log
/mysql/bin/mysqld, Version: 5.7.38 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /mysql/mysql.sock
Time Id Command Argument
2022-09-04T08:32:48.514697Z 9 Query show variables like '%general_log%'
2022-09-04T08:33:57.159589Z 9 Query show databases
2022-09-04T08:33:59.346614Z 9 Quit
3、在线关闭
mysql> set global general_log=OFF;
Query OK, 0 rows affected (0.00 sec)
慢查询日志
1、慢日志作用
1）将mysql服务器中影响数据库性能的相关SQL语句(不论是什么语句，增删改查)记录到日志文件
2）通过对这些特殊的SQL语句分析并改进，提高数据库性能
2、配置慢日志
#慢日志默认是不开启的，生产环境建议开启
vi /etc/my.cnf
新增以下内容并重启数据库生效（持久化生效）
[mysqld]
######slow log######
#指定是否开启慢查询日志
slow_query_log = 1
#指定慢日志文件存放位置（默认在data）
slow_query_log_file=/data/3306-slow.log
#设定慢查询的阀值(默认10s)
long_query_time=1
#不使用索引的慢查询日志是否记录到日志
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10
log_slow_admin_statements = 1
log_slow_slave_statements = 1
以上参数均可以在线调整
3、慢日志测试
测试生成慢查询日志
mysql> show variables like '%long%';
+----------------------------------------------------------+----------+
| Variable_name | Value |
+----------------------------------------------------------+----------+
| long_query_time | 1.000000 |
| performance_schema_events_stages_history_long_size | 1000 |
| performance_schema_events_statements_history_long_size | 1000 |
| performance_schema_events_transactions_history_long_size | 1000 |
| performance_schema_events_waits_history_long_size | 1000 |
+----------------------------------------------------------+----------+
5 rows in set (0.00 sec)
mysql> select sleep(2);
+----------+
| sleep(2) |
+----------+
| 0 |
+----------+
1 row in set (2.00 sec)
查看慢查询日志
$ tail -100f /data/3306-slow.log
/mysql/bin/mysqld, Version: 5.7.38-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /mysql/mysql.sock
Time Id Command Argument
# Time: 2022-09-04T16:46:50.403289+08:00
# User@Host: root[root] @ localhost [] Id: 2
# Query_time: 2.001023 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1662281210;
select sleep(2);
4、慢日志分析
Mysql的慢查询日志可读性较差，而且生产环境大量写入慢日志后人工很难分析，pt-query-digest可以帮助我们分析慢查询日志并生成易读报告。
Pt工具的介绍可以参考下公司的知识库http://39.98.62.218:8090/display/oraht/Percona+Toolkit
安装pt工具集，简单粗暴一路rpm就可以了
1、安装pt工具集
将介质上传到服务器并解压
# unzip perl.zip
Archive: perl.zip
creating: perl/
inflating: perl/groff-base-1.22.2-8.el7.x86_64.rpm
inflating: perl/perl-5.16.3-294.el7_6.x86_64.rpm
inflating: perl/perl-Carp-1.26-244.el7.noarch.rpm
inflating: perl/perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm
inflating: perl/perl-Compress-Raw-Zlib-2.061-4.el7.x86_64.rpm
inflating: perl/perl-constant-1.27-2.el7.noarch.rpm
inflating: perl/perl-Data-Dumper-2.145-3.el7.x86_64.rpm
inflating: perl/perl-DBD-MySQL-4.033-1.of.el7.x86_64.rpm
inflating: perl/perl-DBI-1.627-4.el7.x86_64.rpm
inflating: perl/perl-Digest-1.17-245.el7.noarch.rpm
inflating: perl/perl-Digest-MD5-2.52-3.el7.x86_64.rpm
inflating: perl/perl-Encode-2.51-7.el7.x86_64.rpm
inflating: perl/perl-Exporter-5.68-3.el7.noarch.rpm
inflating: perl/perl-File-Path-2.09-2.el7.noarch.rpm
inflating: perl/perl-File-Temp-0.23.01-3.el7.noarch.rpm
inflating: perl/perl-Filter-1.49-3.el7.x86_64.rpm
inflating: perl/perl-Getopt-Long-2.40-3.el7.noarch.rpm
inflating: perl/perl-HTTP-Tiny-0.033-3.el7.noarch.rpm
inflating: perl/perl-IO-Compress-2.061-2.el7.noarch.rpm
inflating: perl/perl-IO-Socket-IP-0.21-5.el7.noarch.rpm
inflating: perl/perl-IO-Socket-SSL-1.94-7.el7.noarch.rpm
inflating: perl/perl-libs-5.16.3-294.el7_6.x86_64.rpm
inflating: perl/perl-macros-5.16.3-294.el7_6.x86_64.rpm
inflating: perl/perl-Mozilla-CA-20130114-5.el7.noarch.rpm
inflating: perl/perl-Net-Daemon-0.48-5.el7.noarch.rpm
inflating: perl/perl-Net-LibIDN-0.12-15.el7.x86_64.rpm
inflating: perl/perl-Net-SSLeay-1.55-6.el7.x86_64.rpm
inflating: perl/perl-parent-0.225-244.el7.noarch.rpm
inflating: perl/perl-PathTools-3.40-5.el7.x86_64.rpm
inflating: perl/perl-PlRPC-0.2020-14.el7.noarch.rpm
inflating: perl/perl-Pod-Escapes-1.04-294.el7_6.noarch.rpm
inflating: perl/perl-Pod-Perldoc-3.20-4.el7.noarch.rpm
inflating: perl/perl-Pod-Simple-3.28-4.el7.noarch.rpm
inflating: perl/perl-Pod-Usage-1.63-3.el7.noarch.rpm
inflating: perl/perl-podlators-2.5.1-3.el7.noarch.rpm
inflating: perl/perl-Scalar-List-Utils-1.27-248.el7.x86_64.rpm
inflating: perl/perl-Socket-2.010-4.el7.x86_64.rpm
inflating: perl/perl-Storable-2.45-3.el7.x86_64.rpm
inflating: perl/perl-TermReadKey-2.30-20.el7.x86_64.rpm
inflating: perl/perl-Text-ParseWords-3.29-4.el7.noarch.rpm
inflating: perl/perl-threads-1.87-4.el7.x86_64.rpm
inflating: perl/perl-threads-shared-1.43-6.el7.x86_64.rpm
inflating: perl/perl-Time-HiRes-1.9725-3.el7.x86_64.rpm
inflating: perl/perl-Time-Local-1.2300-2.el7.noarch.rpm
安装rpm
# rpm -ivh --force --nodeps --nosignature percona-toolkit-3.0.10-1.el6.x86_64.rpm
Preparing... ################################# [100%]
Updating / installing...
1:percona-toolkit-3.0.10-1.el6 ################################# [100%]
cd perl
# rpm -ivh --force --nodeps --nosignature *.rpm
Preparing... ################################# [100%]
Updating / installing...
1:groff-base-1.22.2-8.el7 ################################# [ 2%]
2:perl-parent-1:0.225-244.el7 ################################# [ 5%]
3:perl-HTTP-Tiny-0.033-3.el7 ################################# [ 7%]
4:perl-podlators-2.5.1-3.el7 ################################# [ 9%]
5:perl-Pod-Perldoc-3.20-4.el7 ################################# [ 11%]
6:perl-Encode-2.51-7.el7 ################################# [ 14%]
7:perl-Pod-Escapes-1:1.04-294.el7_6################################# [ 16%]
8:perl-Text-ParseWords-3.29-4.el7 ################################# [ 18%]
9:perl-Pod-Usage-1.63-3.el7 ################################# [ 20%]
10:perl-constant-1.27-2.el7 ################################# [ 23%]
11:perl-Carp-1.26-244.el7 ################################# [ 25%]
12:perl-Exporter-5.68-3.el7 ################################# [ 27%]
13:perl-Filter-1.49-3.el7 ################################# [ 30%]
14:perl-libs-4:5.16.3-294.el7_6 ################################# [ 32%]
15:perl-macros-4:5.16.3-294.el7_6 ################################# [ 34%]
16:perl-File-Path-2.09-2.el7 ################################# [ 36%]
17:perl-File-Temp-0.23.01-3.el7 ################################# [ 39%]
18:perl-PathTools-3.40-5.el7 ################################# [ 41%]
19:perl-Scalar-List-Utils-1.27-248.e################################# [ 43%]
20:perl-Socket-2.010-4.el7 ################################# [ 45%]
21:perl-Storable-2.45-3.el7 ################################# [ 48%]
22:perl-threads-1.87-4.el7 ################################# [ 50%]
23:perl-threads-shared-1.43-6.el7 ################################# [ 52%]
24:perl-Time-HiRes-4:1.9725-3.el7 ################################# [ 55%]
25:perl-Time-Local-1.2300-2.el7 ################################# [ 57%]
26:perl-Pod-Simple-1:3.28-4.el7 ################################# [ 59%]
27:perl-Getopt-Long-2.40-3.el7 ################################# [ 61%]
28:perl-4:5.16.3-294.el7_6 ################################# [ 64%]
29:perl-Data-Dumper-2.145-3.el7 ################################# [ 66%]
30:perl-Compress-Raw-Bzip2-2.061-3.e################################# [ 68%]
31:perl-Compress-Raw-Zlib-1:2.061-4.################################# [ 70%]
32:perl-IO-Compress-2.061-2.el7 ################################# [ 73%]
33:perl-Digest-1.17-245.el7 ################################# [ 75%]
34:perl-IO-Socket-IP-0.21-5.el7 ################################# [ 77%]
35:perl-Mozilla-CA-20130114-5.el7 ################################# [ 80%]
36:perl-Net-Daemon-0.48-5.el7 ################################# [ 82%]
37:perl-PlRPC-0.2020-14.el7 ################################# [ 84%]
38:perl-DBI-1.627-4.el7 ################################# [ 86%]
39:perl-Net-LibIDN-0.12-15.el7 ################################# [ 89%]
40:perl-Net-SSLeay-1.55-6.el7 ################################# [ 91%]
41:perl-IO-Socket-SSL-1.94-7.el7 ################################# [ 93%]
42:perl-DBD-MySQL-4.033-1.of.el7 ################################# [ 95%]
43:perl-Digest-MD5-2.52-3.el7 ################################# [ 98%]
44:perl-TermReadKey-2.30-20.el7 ################################# [100%]
2、使用pt工具分析慢日志
# pt-query-digest /data/3306-slow.log > analyze-slow.log
查看分析报告
# cat /data/analyze-slow.log
# 290ms user time, 210ms system time, 26.23M rss, 240.96M vsz
# Current date: Sun Sep 4 21:25:14 2022
# Hostname: diana
# Files: /data/3306-slow.log
# Overall: 1 total, 1 unique, 0 QPS, 0x concurrency ______________________
# Time range: all events occurred at 2022-09-04T16:46:50
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 2s 2s 2s 2s 2s 0 2s
# Lock time 0 0 0 0 0 0 0
# Rows sent 1 1 1 1 1 0 1
# Rows examine 0 0 0 0 0 0 0
# Query size 15 15 15 15 15 0 15
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ============= ===== ====== ===== ======
# 1 0xF9A57DD5A41825CA 2.0010 100.0% 1 2.0010 0.00 SELECT
# Query 1: 0 QPS, 0x concurrency, ID 0xF9A57DD5A41825CA at byte 0 ________
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2022-09-04T16:46:50
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 100 1
# Exec time 100 2s 2s 2s 2s 2s 0 2s
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 100 1 1 1 1 1 0 1
# Rows examine 0 0 0 0 0 0 0 0
# Query size 100 15 15 15 15 15 0 15
# String:
# Hosts localhost
# Users root
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+
# EXPLAIN /*!50100 PARTITIONS*/
select sleep(2)\G
Mysql自带工具集mysqldumpslow也可以分析慢查询日志
二进制日志
二进制日志简介
二进制日志即binlog日志，记录了mysql数据库所有的dml,ddl语句事件（不包含select）。记录增删改，也可以记录SQL语句及行记录变化，还可以记录这些操作事件；总之会记录所有修改操作的SQL语句。简单点说就是记录数据库的所有数据变化，类似于oracle的归档日志
开启binlog日志的好处
（1）数据恢复：可以基于时间点恢复，以及根据其进行增量与差异备份
（2）mysql主从复制，通过binlog实现数据复制
二进制日志的工作模式
生产环境只允许使用row模式
（1）语句模式：binlog_format=statement –一般不用
# 语句模式介绍
记录对数据库做出修改的原始sql语句
例如
1、插入一条带当前时间的数据
insert into table (id,create_time) values(1,NOW());
如果使用statement模式，那么这条insert语句将会被记录到二进制日志中,再次回放时NOW()的结果已经变化
# 优点
不需要记录细到每一行数据的更改变化，因此，binlog日志量小，IO压力小，性能较高
例如:
一条语句修改了100万行，该模式下只需要记录下该条语句即可
# 缺点
日志中记录的sql语句可能有上下文依赖，此时脱离了当前数据库环境就无法运行了，因此该模式下容易出现主从不一致的问题。
例如：
主库记录的某条sql语句引用了主库中的函数、触发器、存储过程等特殊功能
在从库上接收了该sql之后，可能就无法正确运行或者运行后产生不一样的结果，从而主从库数据不一致的问题。
ps：row模式是基于每一行来记录变化的，所以不会出现类似的问题。
（2）行级模式：binlog_format=row 生产环境使用
# 行级模式介绍
记录每一行数据修改的细节，即哪一条记录被修改了，修改成什么样了
例如：执行语句
insert into table (id,create_time) values(1,NOW());
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2022-09-02 07:38:13 |
+---------------------+
1 row in set (0.02 sec)
如果使用row模式，那么日志中会记录插入了一条新记录，记录中的create_time字段值为'2022-09-02 07:38:13'
# 优点
相当于把上下文依赖都记录了下来，可以更方便查看每一条数据修改的细节，并且不会出现某些特定情况下的存储过程或function以及trigger的调用和触发无法被正确复制的问题，即该模式下主从复制强一致，数据最安全。
# 缺点
日志量大
例如
一条语句修改了100万行，语句模式下只需要记录一条语句即可
而行级模式却修改记录下100万行的修改记录，binlog日志的量可能会大得惊人。
# 应用
sql语句对mysql内置功能依赖比较多，希望数据最安全，复制强一致的场景推荐行级模式
（3）混合模式：binlog_format=mixed，一般不用
# 混合模式介绍
结合 row level 与statement level的优点
混合（mixed-based）模式默认采用语句模式记录日志，在一些特定的情况下会将记录模式切换为行级模式记录，这些特殊情况包含但不限于以下情况。
•当函数中包含UUID()时。
•当表中有自增列（AUTO_INCREMENT）被更新时。
•当执行触发器（trigger）或者存储过程（stored function）等特殊功能时。
•当FOUND_ROWS()、ROW_COUNT()、USER()、CURRENT_USER()、CURRENT_USER等执行时。
# 应用场景
看上去这种方式似乎比较美好，但是在生产环境中，为了保险起见，一般会使用row模式。
二进制日志相关参数
(1) server_id
服务ID,主从库必须不一样，建议数字为:末位ip+端口，必须指定
(2) log-bin
此变量用于控制是否开启二进制日志，而且这是一个只读变量，默认值为OFF
当我们启动数据库以后，在当前数据库连接中查看此变量的值，此变量值可能为OFF，表示不记录二进制日志，如果想要记录二进制日志，只需将此值设置为二进制日志的文件名即可，但是需要注意的是，我们无法在当前会话中使用set命令设置log_bin的值，因为它是一个只读变量，我们只能通过修改my.cnf的方式，设置log_bin的值，假设，我们编辑my.cnf文件，设置log_bin的值为mybinlog，那么，在mysql的数据目录中，将会自动生成一个以mybinlog为文件名前缀的二进制日志文件，如果想要再次禁用binlog，只需要将log_bin这一行配置从my.cnf文件中注释即可，或者将其删除，重启mysql服务后，再次查看log_bin的值，其值为OFF，注意，不要直接在my.cnf文件中将log_bin的值设置为ON或者OFF，如果这样做，你将会看到以ON或者OFF为文件名前缀的二进制日志文件。换句话说，如果my.cnf配置文件中没有log_bin的配置，则表示未开启二进制日志，如果my.cnf中存在log_bin的配置，那么则表示开启了二进制日志，同时二进制日志文件的名称将会以log_bin对应的值为文件名前缀，同时，二进制日志文件的后缀名会进行自动编号，每次日志滚动后，后缀名编号自动加1。
示例：
log-bin=/var/lib/mysql/mybinlog #绝对路径
# log-bin=mybinlog #也可以用相对路径， 会在$datadir下产生mysqlbinlog-00000N
(3) log_bin_index
不设置的话，会根据log_bin值名称自动生成mybinlog.index
log_bin_index=var/lib/mysql/mybinlog.index
(4) sql_log_bin
默认为ON
此变量用于标识当前会话中的操作是否会被记录于二进制日志，此变量值设置为ON，则表示在当前数据库连接中，对数据库进行修改的语句将会被记录到binlog中，此变量值设置为OFF，则表示在当前数据库连接中，对数据库进行的修改的语句将不会被记录到binlog中，在主从复制结构中，这些语句由于没有被binlog记录，所以也不会同步到从节点中。换句话说，即使在my.cnf配置文件中设置了log_bin的值，当前会话中，如果sql_log_bin的值设置为OFF，当前会话的操作也不会记录在二进制日志中。而且需要注意的是，sql_log_bin是一个会话界别的变量，只能在当前会话中使用set sql_log_bin命令进行设置，不能使用set global sql_log_bin命令进行设置，因为它是会话级别的变量，而且，sql_log_bin也不能配置在my.cnf文件中，否则可能会无法启动mysql。
(5) binlog_format
此变量值得含义上文已经解释过，此变量的值决定了二进制日志的记录方式，此变量的值可以设置为statement、row、mixed，分别表示以语句的形式记录二进制日志，以数据修改的形式记录二进制日志，以混合的方式记录二进制日志，安全保险起见，推荐使用row的方式记录。
(6) max_binlog_size
设置单个二进制日志文件的最大大小，以字节为单位，超过此值大小，则二进制日志文件会自动滚动。
(7) sync_binlog
innodb_flush_log_at_trx_commit控制redo日志的刷盘策略，建议值为1
sync_binlog控制binlog日志的刷盘策略，建议值为1
PS：这就是mysql经常提到的“双1”
当我们把innodb_flush_log_at_trx_commit设置为1的时候，表示事务提交时，事务日志立马从内存刷写到磁盘中的事务日志文件中，而sync_binlog对于二进制日志的作用，就像innodb_flush_log_at_trx_commit对于事务日志的作用，由于二进制日志一开始存在于内存（binlog_cache）中，如果将sync_binlog设置为1，则表示每1次事务提交之后，都会立即将内存中的二进制日志立即同步到磁盘中的二进制日志文件中，如果将sync_binlog设置为0，则表示当事务提交之后，mysql不会立即将内存中的binlog刷写到磁盘中的binlog日志文件中，而是由文件系统决定什么时候刷写，这可能取决于文件系统的缓存机制，当此值设置为0时，一旦操作系统宕机，那么将丢失未从内存中同步到磁盘中的binlog，所以，当此值设置为0时，安全性最差，但是性能最高，当此值设置为1时，安全性最高，性能最差，除了将此值设置为0或1，还能设置为N，假设将此值设置为3，则表示每3次事务提交后，将binlog从内存刷写到磁盘一次，值设置的越大，有可能丢失的日志数据将会越多，当然，性能会越好，在追求安全的情况下，推荐设置为1，但是听说，此值设置为0和设置为1时在性能上的差距还是比较明显的，如果设置为0或N，最好为操作系统准备带有备用电源的缓存。
(8) 其他参数
#记录原始sql
binlog_rows_query_log_events=on
#binlog保留时间
expire_logs_days=7
# full,minimal,noblob分别表示binlog中内容全记录，只记录被操作的，和不记录二进制
binlog_row_image=full #（full,minimal,noblob），
开启binlog日志
配置开启binlog
vi /mysql/etc/my.cnf
添加以下内容
[mysqld]
######bin log######
server_id=1
log-bin=/data/binlog
binlog_format=ROW
binlog_rows_query_log_events=on
max_binlog_size=1024M
默认是关闭的：
mysql> show variables like '%log_bin%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin | OFF | | log_bin_basename | | | log_bin_index | | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+-------+ 6 rows in set (0.03 sec)
重启数据库服务
mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)
mysql> 2022-09-04T13:37:53.379187Z mysqld_safe mysqld from pid file /mysql/mariadb/mariadb.pid ended
mysql> \q
Bye
[1]+ Done mysqld_safe --defaults-file=/mysql/etc/my.cnf --user=mysql
$ mysqld_safe --defaults-file=/mysql/etc/my.cnf --user=mysql &
[1] 10276 2022-09-04T13:37:57.070797Z mysqld_safe Logging to '/mysql/mariadb/mysql3306.log'.
2022-09-04T13:37:57.117475Z mysqld_safe Starting mysqld daemon with databases from /data
查看bin log状态
mysql> show binary logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000001 | 154 |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------+
| Variable_name | Value |
+---------------------------------+--------------------+
| log_bin | ON |
| log_bin_basename | /data/binlog |
| log_bin_index | /data/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------+
6 rows in set (0.01 sec)
查看binlog日志
查看日志日志名、状态、事件：
show binary logs; show master logs; show master status; show binlog events in 'binlog.000001'; show binlog events in 'binlog.000001' limit 3;
创建测试表
mysql> create table ts_tbs(
-> id int primary key auto_increment,
-> name char(20) not null default '');
Query OK, 0 rows affected (0.37 sec)
插入测试数据
mysql> insert into ts_tbs(name) values('张飞');
Query OK, 1 row affected (0.00 sec)
mysql> insert into ts_tbs(name) values('刘备');
Query OK, 1 row affected (0.01 sec)
mysql> insert into ts_tbs(name) values('关羽');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
全表更新
mysql> update ts_tbs set name = '曹操' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update ts_tbs set name = '曹植' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update ts_tbs set name = '曹仁' where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from ts_tbs;
+----+--------+
| id | name |
+----+--------+
| 1 | 曹操 |
| 2 | 曹植 |
| 3 | 曹仁 |
+----+--------+
3 rows in set (0.00 sec)
查看日志日志名、状态、事件：
mysql> show binary logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000001 | 1395 |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 | 1210 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'binlog.000002';
+---------------+------+----------------+-----------+-------------+--------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+------+----------------+-----------+-------------+--------------------------------------------------+
| binlog.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.38-log, Binlog ver: 4 |
| binlog.000002 | 123 | Previous_gtids | 1 | 154 | |
| binlog.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 219 | Query | 1 | 292 | BEGIN |
| binlog.000002 | 292 | Rows_query | 1 | 362 | # update ts_tbs set name = '曹操' where id = 1 |
| binlog.000002 | 362 | Table_map | 1 | 415 | table_id: 118 (mysql.ts_tbs) |
| binlog.000002 | 415 | Update_rows | 1 | 475 | table_id: 118 flags: STMT_END_F |
| binlog.000002 | 475 | Xid | 1 | 506 | COMMIT /* xid=134 */ |
| binlog.000002 | 506 | Anonymous_Gtid | 1 | 571 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 571 | Query | 1 | 644 | BEGIN |
| binlog.000002 | 644 | Rows_query | 1 | 714 | # update ts_tbs set name = '曹植' where id = 2 |
| binlog.000002 | 714 | Table_map | 1 | 767 | table_id: 118 (mysql.ts_tbs) |
| binlog.000002 | 767 | Update_rows | 1 | 827 | table_id: 118 flags: STMT_END_F |
| binlog.000002 | 827 | Xid | 1 | 858 | COMMIT /* xid=135 */ |
| binlog.000002 | 858 | Anonymous_Gtid | 1 | 923 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 923 | Query | 1 | 996 | BEGIN |
| binlog.000002 | 996 | Rows_query | 1 | 1066 | # update ts_tbs set name = '曹仁' where id = 3 |
| binlog.000002 | 1066 | Table_map | 1 | 1119 | table_id: 118 (mysql.ts_tbs) |
| binlog.000002 | 1119 | Update_rows | 1 | 1179 | table_id: 118 flags: STMT_END_F |
| binlog.000002 | 1179 | Xid | 1 | 1210 | COMMIT /* xid=136 */ |
+---------------+------+----------------+-----------+-------------+--------------------------------------------------+
20 rows in set (0.00 sec)
查看日志内容
解析binlog
$ mysqlbinlog --no-defaults --base64-output=decode-rows -vv /data/binlog.000002> /data/analyze-binlog.log
查看binlog解析日志
$ cat /data/analyze-binlog.log
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220904 21:57:55 server id 1 end_log_pos 123 CRC32 0x5f15bd58 Start: binlog v 4, server v 5.7.38-log created 220904 21:57:55
# Warning: this binlog is either in use or was not closed properly.
# at 123
#220904 21:57:55 server id 1 end_log_pos 154 CRC32 0xd7cc336c 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*/;
[mysql@diana ~]$
[mysql@diana ~]$
[mysql@diana ~]$ mysqlbinlog --no-defaults --base64-output=decode-rows -vv /data/binlog.000002> /data/analyze-binlog.log
[mysql@diana ~]$ cat /data/analyze-binlog.log
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220904 21:57:55 server id 1 end_log_pos 123 CRC32 0x5f15bd58 Start: binlog v 4, server v 5.7.38-log created 220904 21:57:55
# Warning: this binlog is either in use or was not closed properly.
# at 123
#220904 21:57:55 server id 1 end_log_pos 154 CRC32 0xd7cc336c Previous-GTIDs
# [empty]
# at 154
#220904 22:21:01 server id 1 end_log_pos 219 CRC32 0x81bfd127 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#220904 22:21:01 server id 1 end_log_pos 292 CRC32 0x54cd58e2 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1662301261/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 292
#220904 22:21:01 server id 1 end_log_pos 362 CRC32 0x339e3a63 Rows_query
# update ts_tbs set name = '曹操' where id = 1
# at 362
#220904 22:21:01 server id 1 end_log_pos 415 CRC32 0xea0e3030 Table_map: `mysql`.`ts_tbs` mapped to number 118
# at 415
#220904 22:21:01 server id 1 end_log_pos 475 CRC32 0xfd5be33c Update_rows: table id 118 flags: STMT_END_F
### UPDATE `mysql`.`ts_tbs`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='赵云' /* STRING(80) meta=65104 nullable=0 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='曹操' /* STRING(80) meta=65104 nullable=0 is_null=0 */
# at 475
#220904 22:21:01 server id 1 end_log_pos 506 CRC32 0x3691e352 Xid = 134
COMMIT/*!*/;
# at 506
#220904 22:21:07 server id 1 end_log_pos 571 CRC32 0x2393d18d Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 571
#220904 22:21:07 server id 1 end_log_pos 644 CRC32 0x97a7241c Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1662301267/*!*/;
BEGIN
/*!*/;
# at 644
#220904 22:21:07 server id 1 end_log_pos 714 CRC32 0x16f45d59 Rows_query
# update ts_tbs set name = '曹植' where id = 2
# at 714
#220904 22:21:07 server id 1 end_log_pos 767 CRC32 0xe7a316a9 Table_map: `mysql`.`ts_tbs` mapped to number 118
# at 767
#220904 22:21:07 server id 1 end_log_pos 827 CRC32 0x10d16aa7 Update_rows: table id 118 flags: STMT_END_F
### UPDATE `mysql`.`ts_tbs`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='吕布' /* STRING(80) meta=65104 nullable=0 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='曹植' /* STRING(80) meta=65104 nullable=0 is_null=0 */
# at 827
#220904 22:21:07 server id 1 end_log_pos 858 CRC32 0xf4dfe1a5 Xid = 135
COMMIT/*!*/;
# at 858
#220904 22:21:11 server id 1 end_log_pos 923 CRC32 0x8131d82f Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 923
#220904 22:21:11 server id 1 end_log_pos 996 CRC32 0x5b93f0d8 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1662301271/*!*/;
BEGIN
/*!*/;
# at 996
#220904 22:21:11 server id 1 end_log_pos 1066 CRC32 0xa5e50ec6 Rows_query
# update ts_tbs set name = '曹仁' where id = 3
# at 1066
#220904 22:21:11 server id 1 end_log_pos 1119 CRC32 0x05bb1348 Table_map: `mysql`.`ts_tbs` mapped to number 118
# at 1119
#220904 22:21:11 server id 1 end_log_pos 1179 CRC32 0x0a2fc15a Update_rows: table id 118 flags: STMT_END_F
### UPDATE `mysql`.`ts_tbs`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='马超' /* STRING(80) meta=65104 nullable=0 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='曹仁' /* STRING(80) meta=65104 nullable=0 is_null=0 */
# at 1179
#220904 22:21:11 server id 1 end_log_pos 1210 CRC32 0x3e63fedf Xid = 136
COMMIT/*!*/;
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*/;
切换binlog
刷新binlog：关闭当前的二进制日志文件并创建一个新文件
1）手动执行命令刷新：flush logs;
2）重启数据库时会刷新
3）二进制日志上限（max_binlog_size）自动刷新
mysql> show binary logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000001 | 1395 |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000001 | 1439 |
| binlog.000002 | 154 |
+---------------+-----------+
2 rows in set (0.00 sec)
清理binlog
清除二进制日志原则
在存储能力范围内，能多保留则多保留
1）删除指定binlog名之前的所有binlog(保留指定的binlog)
-- binlog.000002之前的都删除掉
purge binary logs to 'binlog.000002';
#示例：
mysql> show binary logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000001 | 1439 |
| binlog.000002 | 154 |
+---------------+-----------+
2 rows in set (0.00 sec)
mysql> purge binary logs to 'binlog.000002';
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000002 | 154 |
+---------------+-----------+
1 row in set (0.00 sec)
2）删除日期之前的日志：手动执行
PURGE {MASTER | BINARY} LOGS BEFORE 'date' --用于删除日期之前的日志，BEFORE变量的date自变量可以为'YYYY-MM-DD hh:mm:ss'格式
如：（MASTER 和BINARY 在这里都是等效的）
PURGE MASTER LOGS TO 'mybinlog.00003';
purge binary logs before '2021-07-13 19:11:00';
还可以做减法：如只保留3天的
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
3）删除日期之前的日志：修改配置参数，让mysql自动执行
删除7天前的binlog
#临时生效
SET GLOBAL expire_logs_days = 7;
#永久生效
$ vi /mysql/etc/my.cnf
[mysqld]
expire_logs_days = 7
关闭binlog（会话级）
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------+
| Variable_name | Value |
+---------------------------------+--------------------+
| log_bin | ON |
| log_bin_basename | /data/binlog |
| log_bin_index | /data/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------+
6 rows in set (0.00 sec)
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------+
| Variable_name | Value |
+---------------------------------+--------------------+
| log_bin | ON |
| log_bin_basename | /data/binlog |
| log_bin_index | /data/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | OFF |
+---------------------------------+--------------------+
6 rows in set (0.00 sec)
全表更新
mysql> update ts_tbs set name = '赵云' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update ts_tbs set name = '吕布' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update ts_tbs set name = '马超' where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from ts_tbs;
+----+--------+
| id | name |
+----+--------+
| 1 | 赵云 |
| 2 | 吕布 |
| 3 | 马超 |
+----+--------+
3 rows in set (0.00 sec)
查看binlog
mysql> show binary logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000002 | 154 |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> show binlog events in 'binlog.000002';
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
| binlog.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.38-log, Binlog ver: 4 |
| binlog.000002 | 123 | Previous_gtids | 1 | 154 | |
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
解析binlog
$ mysqlbinlog --no-defaults --base64-output=decode-rows -vv /data/binlog.000002> /data/analyze-binlog.log
查看解析日志
$ cat /data/analyze-binlog.log
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220904 21:57:55 server id 1 end_log_pos 123 CRC32 0x5f15bd58 Start: binlog v 4, server v 5.7.38-log created 220904 21:57:55
# Warning: this binlog is either in use or was not closed properly.
# at 123
#220904 21:57:55 server id 1 end_log_pos 154 CRC32 0xd7cc336c 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*/;
2889
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
