4.2 查看日志
MySQL错误日志是以文本文件形式存储的，可以使用文本编辑器直接查看。
查询错误日志的存储路径：
mysql> SHOW VARIABLES LIKE 'log_err%'; +----------------------------+----------------------------------------+ | Variable_name | Value | +----------------------------+----------------------------------------+ | log_error | /var/log/mysqld.log | | log_error_services | log_filter_internal; log_sink_internal | | log_error_suppression_list | | | log_error_verbosity | 2 | +----------------------------+----------------------------------------+ 4 rows in set (0.00 sec)
执行结果中可以看到错误日志文件是mysqld.log，位于MySQL默认的数据目录下。
下面我们查看一下错误日志的内容。
2022-07-25T11:53:45.116777Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.25) initializing of server in progress as process 4260 2022-07-25T11:53:45.132158Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2022-07-25T11:53:45.508294Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2022-07-25T11:53:46.377629Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: j:hz2hS7Y0XY 2022-07-25T12:02:59.829953Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.25) starting as process 4559 2022-07-25T12:02:59.844264Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2022-07-25T12:02:59.982942Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2022-07-25T12:03:00.100569Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock 2022-07-25T12:03:00.292710Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2022-07-25T12:03:00.293057Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. 2022-07-25T12:03:00.326433Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.25' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
可以看到，错误日志文件中记录了服务器启动的时间，以及存储引擎InnoDB启动和停止等，我们在做初始化时候生成的数据库初始密码也是记录在error.log中。
4.3 删除\刷新日志
对于很久以前的错误日志，数据库管理员查看这些错误日志的可能性不大，可以将这些错误日志删除，以保证MySQL服务器上的 硬盘空间 。MySQL的错误日志是以文本文件的形式存储在文件系统中的，可以直接删除。
- 第1步(方式1)︰删除操作
rm -f /var/lib/mysql/mysqld. log
在运行状态下删除错误日志文件后，MySQL并不会自动创建日志文件。
- 第1步(方式2)︰重命名文件
mv /var/log/mysqld.log /var /log/mysqld.log.old
- 第2步：重建日志
mysqladmin -uroot -p flush-logs
可能报错
[root@atguigu01 log]# mysqladmin -uroot -p flush-logs Enter password: mysqladmin: refresh failed; error: 'Could not open file '/var/log/mysqld.log' for error logging.'
官网提示：
补充操作：
install -omysql -gmysql -m0644 /dev/null /var/log/mysqld.log
flush-logs指令操作:
MySQL 5.5.7以前的版本，flush-logs将错误日志文件重命名为filename.err_old，并创建新的日志文件。
从MySQL 5.5.7开始，flush-logs只是重新打开日志文件，并不做日志备份和创建的操作。
如果日志文件不存在，MySQL启动或者执行flush-logs时会自动创建新的日志文件。重新创建错误日志，大小为0字节。
4.4 MySQL8.0新特性
MySQL8.0里对错误日志的改进。MySQL8.o的错误日志可以理解为一个全新的日志，在这个版本里，接受了来自社区的广泛批评意见，在这些意见和建议的基础上生成了新的日志。
下面这些是来自社区的意见:
- 默认情况下内容过于冗长
- 遗漏了有用的信息
- 难以过滤某些信息
- 没有标识错误信息的子系统源
- 没有错误代码，解析消息需要识别错误
- 引导消息可能会丢失
- 固定格式
针对这些意见，MySQL做了如下改变:
- 采用组件架构，通过不同的组件执行日志的写入和过滤功能
- 写入错误日志的全部信息都具有唯一的错误代码从10000开始
- 增加了一个新的消息分类《system》用于在错误日志中始终可见的非错误但服务器状态更改事件的消息。增加了额外的附加信息，例如关机时的版本信息，谁发起的关机等等
- 两种过滤方式，Internal和Dragnet
- 三种写入形式，经典、JSON和syseventlog
小结:
通常情况下，管理员不需要查看错误日志。但是，MySQL服务器发生异常时，管理员可以从错误日志中找到发生异常的时间、原因，然后根据这些信息来解决异常。
5. 二进制日志(bin log)
binlog可以说是MySQL中比较 重要 的日志了，在日常开发及运维过程中，经常会遇到。
binlog即binary log，二进制日志文件，也叫作变更日志（update log）。它记录了数据库所有执行的DDL 和 DML 等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、show等）。
它以事件形式记录并保存在二进制文件中。通过这些信息，我们可以再现数据更新操作的全过程。
如果想要记录所有语句（例如，为了识别有问题的查询)，需要使用通用查询日志。
binlog主要应用场景:
一是用于数据恢复，如果MySQL数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。
二是用于数据复制，由于日志的延续性和时效性，master把它的二进制日志传递给slaves来达到master-slave数据—致的目的。
可以说MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据—致性。
5.1 查看默认情况
查看记录二进制日志是否开启：在MySQL8中默认情况下，二进制文件是开启的。
mysql> show variables like '%log_bin%'; +---------------------------------+-----------------------------+ | Variable_name | Value | +---------------------------------+-----------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/binlog | | log_bin_index | /var/lib/mysql/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)
log_bin_basename : 是binlog日志的基本文件名，后面会追加标识来表示每一个文件
log_bin_index:是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录
log_bin_trust_function_creators: 限制存储过程，前面我们已经讲过了，这是因为二进制日志的一个重要功能是用于主从复制，而存储函数有可能导致主从的数据不一致。所以当开启二进制日志后，需要限制存储函数的创建、修改、调用
log_bin_use_v1_row_events 此只读系统变量已弃用。ON表示使用版本1二进制日志行，OFF表示使用版本2二进制日志行(MysQL 5.6的默认值为2)。
每次服务重启，都会新创建一个binlog
5.2 日志参数设置
方式1：永久性方式
修改MySQL的 my.cnf 或 my.ini 文件可以设置二进制日志的相关参数：
[mysqld] #启用二进制日志 log-bin=atguigu-bin binlog_expire_logs_seconds=600 max_binlog_size=100M
提示:
log-bin=mysql-bin #打开日志(主机需要打开)，这个mysql-bin也可以自定义，这里也可以加上路径，如: /home/www/mysql_bin_log/mysql-bin
binlog_expire_logs_seconds:此参数控制二进制日志文件保留的时长，单位是秒，默认2592000 3(天-- 14400 4小时; 86400 1天; 259200 3天;
max_binlog_size:控制单个二进制日志大小，当前日志文件大小超过此变量时，执行切换动作。此参数的最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlo
g比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，可能不做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束。一般情况下可采取默认值
重新启动MySQL服务，查询二进制日志的信息，执行结果：
mysql> show variables like '%log_bin%'; +---------------------------------+----------------------------------+ | Variable_name | Value | +---------------------------------+----------------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/atguigu-bin | | log_bin_index | /var/lib/mysql/atguigu-bin.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)
设置带文件夹的bin-log日志存放目录
如果想改变日志文件的目录和名称，可以对my.cnf或my.ini中的log_bin参数修改如下：
[mysqld] log-bin="/var/lib/mysql/binlog/atguigu-bin"
注意：新建的文件夹需要使用mysql用户，使用下面的命令即可。
chown -R -v mysql:mysql binlog
重启MySQL服务之后，新的二进制日志文件将出现在/var/lib/mysql/binlog/文件夹下面:
mysql> show variables like '%log_bin%'; +---------------------------------+----------------------------------+ | Variable_name | Value | +---------------------------------+----------------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/atguigu-bin | | log_bin_index | /var/lib/mysql/atguigu-bin.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)
提示
数据库文件最好不要与日志文件放在同一个磁盘上! 这样，当数据库文件所在的磁盘发生故障时，可以使用日志文件恢复数据。
方式2：临时性方式
如果不希望通过修改配置文件并重启的方式设置二进制日志的话，还可以使用如下指令，需要注意的是在mysql8中只有 会话级别 的设置，没有了global级别的设置。
# global 级别 set global sql_log_bin=0; #ERROR 1228 (HY000): Variable 'sql_log_bin' is a SESSION variable and can`t be used with SET GLOBAL # session级别 SET sql_log_bin=0; #Query OK, 0 rows affected (0.01 秒)
5.3 查看日志
当MySQL创建二进制日志文件时，先创建一个以“filename”为名称、以“.index”为后缀的文件，再创建一个以“filename”为名称、以“.000001”为后缀的文件。
MySQL服务 重新启动一次 ，以“.000001”为后缀的文件就会增加一个，并且后缀名按1递增。即日志文件的个数与MySQL服务启动的次数相同；如果日志长度超过了 max_binlog_size 的上限（默认是1GB），就会创建一个新的日志文件。
查看当前的二进制日志文件列表及大小。指令如下：
SHOW BINARY LOGS; /* + -------------------- + ----------- +----------- + | Log_name | File_size | Encrypted | + -------------------- + ----------- +----------- + | atguigu-bin.000001 | 156 | No | + -------------------- + ----------- +----------- + 1 行于数据集 (0.02 秒) */
所有对数据库的修改都会记录在binglog中。但binlog是二进制文件，无法直接查看，想要更直观的观测它就要借助mysqlbinlog命令工具了。指令如下:在查看执行，先执行一条SQL语句，如下
update student set name= '张三_back ' where id=1 ;
开始查看binlog
mysqlbinlog "/var/lib/mysql/binlog/atguigu-bin.008802" #220105I9:16:37 server id 1 end_log_pos 324 CRC32 0x6b31978b Querythread_id=18exec_time=0error_code=0 SET TIMESTAMP=1641345397/*!*/ ; SET @@session.pseudo_thread_id=10/*!*/ ; SET @@session.foreign_key_checks=1，@@session.sql_auto_is_null=0，@@session.unique_checks=1,@@session.autocommit=1/*!*/; SET @@session .sql_mode=1168113696/*!*/ ; SET @@session.auto_increment_increment=1，@@session.auto_increment_offset=1/*!*/;/* ! \C utf8mb3 *//*!*/ ; SET @session.character_set_client=33,C@session.collation_connection=33,@@session.collation_server=255/* !*/; SET @@session . lc_time_names=0/*!*/ ; SET @@session.collation_database=DEFAULT/*!*/; /*!80011 SET @session.default_collation_for_utf8mb4=255*//* !*/ ;BEGIN /*!*/; #at 324 #22010509:16:37 server id 1 end_log_pos 391 CRC32 0x74f89890 Table_map :'atguigu14`.`student' mapped to number 85 #at 391 #220105 9:16:37 server id 1 end_log_pos 470 CRC32 0xc9920491 Update_rows: table id 85 flags; STMT_END_F BINLOG ' dfHUYRMBAAAAQwAAAIcBAAAAAFUAAAAAAAEACWF6Z3VpZ3UxNAAHc3R1ZGVudAADAw8PBDwAHgAGAQEAAgEhkJj4dA== dfHUYR8BAAAATWAANYBAAAAAFUAAAAAAAEAAgAD//8AAQAAAAblvKDkuIkG5LiA54+tAAEAAAAL5byg5LiJX2JhY2sG5LiA54+tkQSSyQ== '/*!*/; #at 478 #220105 9:16:37 server id 1 end_log_pos 501 CRC320xca01d30f Xid = 15
执行结果可以看到，这是一个简单的日志文件，日志中记录了用户的一些操作，这里并没有出现具体的SQL语句，这是因为binlog关键字后面的内容是经过编码后的二进制日志。
这里一个update语句包含如下事件
Query事件负责开始一个事务(BEGIN)
Table_map事件负责映射需要的表.
Update_rows事件负责写入数据
Xid事件负责结束事务
下面命令将行事件以 伪SQL的形式 表现出来
mysqlbinlog -v "/var/lib/mysql/binlog/atguigu-bin.000002" #220105 9:16:37 server id 1 end_log_pos 324 CRC32 0x6b31978b Query thread_id=10 exec_time=0 error_code=0 SET TIMESTAMP=1641345397/*!*/; SET @@session.pseudo_thread_id=10/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1168113696/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8mb3 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collatio n_server=255/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/; BEGIN /*!*/; # at 324 #220105 9:16:37 server id 1 end_log_pos 391 CRC32 0x74f89890 Table_map: `atguigu14`.`student` mapped to number 85 # at 391 #220105 9:16:37 server id 1 end_log_pos 470 CRC32 0xc9920491 Update_rows: table id 85 flags: STMT_END_F BINLOG ' dfHUYRMBAAAAQwAAAIcBAAAAAFUAAAAAAAEACWF0Z3VpZ3UxNAAHc3R1ZGVudAADAw8PBDwAHgAG AQEAAgEhkJj4dA== dfHUYR8BAAAATwAAANYBAAAAAFUAAAAAAAEAAgAD//8AAQAAAAblvKDkuIkG5LiA54+tAAEAAAAL 5byg5LiJX2JhY2sG5LiA54+tkQSSyQ== '/*!*/; ### UPDATE `atguigu`.`student` ### WHERE ### @1=1 ### @2='张三' ### @3='一班' ### SET ### @1=1 ### @2='张三_back' ### @3='一班' # at 470 #220105 9:16:37 server id 1 end_log_pos 501 CRC32 0xca01d30f Xid = 15 COMMIT/*!*/;
前面的命令同时显示binlog格式的语句，使用如下命令不显示它
mysqlbinlog -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog/atguigu-bin.000002" #220105 9:16:37 server id 1 end_log_pos 324 CRC32 0x6b31978b Query thread_id=10 exec_time=0 error_code=0 SET TIMESTAMP=1641345397/*!*/; SET @@session.pseudo_thread_id=10/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1168113696/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8mb3 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collatio n_server=255/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/; BEGIN /*!*/; # at 324 #220105 9:16:37 server id 1 end_log_pos 391 CRC32 0x74f89890 Table_map: `atguigu14`.`student` mapped to number 85 # at 391 #220105 9:16:37 server id 1 end_log_pos 470 CRC32 0xc9920491 Update_rows: table id 85 flags: STMT_END_F ### UPDATE `atguigu14`.`student` ### WHERE ### @1=1 ### @2='张三' ### @3='一班' ### SET ### @1=1 ### @2='张三_back' ### @3='一班' # at 470 #220105 9:16:37 server id 1 end_log_pos 501 CRC32 0xca01d30f Xid = 15
关于mysqlbinlog工具的使用技巧还有很多，例如只解析对某个库的操作或者某个时间段内的操作等。简单分享几个常用的语句，更多操作可以参考官方文档。
# 可查看参数帮助 mysqlbinlog --no-defaults --help # 查看最后100行 mysqlbinlog --no-defaults --base64-output=decode-rows -vv atguigu-bin.000002 |tail -100 # 根据position查找 mysqlbinlog --no-defaults --base64-output=decode-rows -vv atguigu-bin.000002 |grep -A 20 '4939002'
上面这种办法读取出binlog日志的全文内容比较多，不容易分辨查看到pos点信息，下面介绍一种更为方便的查询命令：
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
IN ‘log_name’ ：指定要查询的binlog文件名（不指定就是第一个binlog文件）
FROM pos ：指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）
LIMIT [offset]：偏移量(不指定就是0)
row_count :查询总条数（不指定就是所有行）
show binlog events in 'atguigu-bin.000002'; /* +-------------------- +----- +---------------- +----------- +------------- + --------------- -------------------------------------------------------------- + | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------- +----- +---------------- +----------- +------------- + --------------- -------------------------------------------------------------- + | atguigu-bin.000002 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.26, Binlog ver: 4 | | atguigu-bin.000002 | 125 | Previous_gtids | 1 | 156 | | | atguigu-bin.000002 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | atguigu-bin.000002 | 235 | Query | 1 | 324 | BEGIN | | atguigu-bin.000002 | 324 | Table_map | 1 | 391 | table_id: 85 (atguigu14.student) | | atguigu-bin.000002 | 391 | Update_rows | 1 | 470 | table_id: 85 flags: STMT_END_F | | atguigu-bin.000002 | 470 | Xid | 1 | 501 | COMMIT /* xid=15 */ | | atguigu-bin.000002 | 501 | Anonymous_Gtid | 1 | 578 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | atguigu-bin.000002 | 578 | Query | 1 | 721 | use `atguigu14`; create table test(id int, title varchar(100)) /* xid=19 */ | | atguigu-bin.000002 | 721 | Anonymous_Gtid | 1 | 800 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | atguigu-bin.000002 | 800 | Query | 1 | 880 | BEGIN | | atguigu-bin.000002 | 880 | Table_map | 1 | 943 | table_id: 89 (atguigu14.test) | | atguigu-bin.000002 | 943 | Write_rows | 1 | 992 | table_id: 89 flags: STMT_END_F | | atguigu-bin.000002 | 992 | Xid | 1 | 1023 | COMMIT /* xid=21 */ | +-------------------- -----+ ----------------+ -----------+ + -------------+--------------- -------------------------------------------------------------- + 14 行于数据集 (0.02 秒) */
上面这条语句可以将指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos点的起始偏移，查询条数。其它举例:
#a、查询第一个最早的binlog日志: show binlog events\G ; #b、指定查询mysql-bin.088002这个文件 show binlog events in 'atguigu-bin.000002'\G; #c、指定查询mysql-bin.0888日2这个文件，从pos点:391开始查起: show binlog events in 'atguigu-bin . 088882' from 391\G; #d、指定查询mysql-bin.000002这个文件，从pos点:391开始查起，查询5条（即5条语句 show binlog events in 'atguigu-bin.080802' from 391 limit 5\G; #e、指定查询 mysql-bin.000002这个文件，从pos点:391开始查起，偏移2行〈即中间跳过2个）查询5条（即5条语句)。 show binlog events in 'atguigu-bin.088002 ' from 391 limit 2,5\G;
上面我们讲了这么多都是基于binlog的默认格式，binlog格式查看
show variables like 'binlog_format'; /* + ---------------+------- + | Variable_name | Value | + ---------------+------- + | binlog_format | ROW | + ---------------+------- + 1 行于数据集 (0.02秒) */
除此之外，binlog还有2种格式，分别是Statement和Mixed
