3.6日志管理
MySQL 支持丰富的日志类型，如下：
- 事务日志：transaction log
事务日志的写入类型为”追加”，因此其操作为”顺序IO”；通常也被称为：预写式日志 write ahead logging
事务日志文件： ib_logfile0， ib_logfile1 -
错误日志 error log
-
通用日志 general log
-
慢查询日志 slow query log
-
二进制日志 binary log
-
中继日志 reley log，在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件
3.6.1 事务日志
事务日志：transaction log
- redo log：实现 WAL（Write Ahead Log) ,数据更新前先记录redo log
- undo log：保存与执行的操作相反的操作,用于实现rollback
事务型存储引擎自行管理和使用，建议和数据文件分开存放
Innodb事务日志相关配置：
show variables like '%innodb_log%';
innodb_log_file_size 50331648 #每个日志文件大小
innodb_log_files_in_group 2 #日志组成员个数
innodb_log_group_home_dir ./ #事务文件路径
事务日志性能优化
innodb_flush_log_at_trx_commit=0|1|2
1 此为默认值，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供更好的性能，但服务器崩溃可能丢失最后一秒的事务2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些，但操作系统或停电可能导致最后一秒的交易丢失
高并发业务行业最佳实践，是使用第三种折衷配置（=2）：
1.配置为2和配置为0，性能差异并不大，因为将数据从Log Buffer拷贝到OS cache，虽然跨越用户态与内核态，但毕竟只是内存的数据拷贝，速度很快2.配置为2和配置为0，安全性差异巨大，操作系统崩溃的概率相比MySQL应用程序崩溃的概率，小很多，设置为2，只要操作系统不奔溃，也绝对不会丢数据
说明：
- 设置为1，同时sync_binlog = 1表示最高级别的容错
- innodb_use_global_flush_log_at_trx_commit=0 时，将不能用SET语句重置此变量（ MariaDB 10.2.6 后废弃）
3.6.2 错误日志
错误日志
- mysqld启动和关闭过程中输出的事件信息
- mysqld运行中产生的错误信息
- event scheduler运行一个event时产生的日志信息
- 在主从复制架构中的从服务器上启动从服务器线程时产生的信息
错误文件路径
SHOW GLOBAL VARIABLES LIKE 'log_error' ;
范例：
MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE 'log_error';+---------------+------------------------------+| Variable_name | Value |+---------------+------------------------------+| log_error | /var/log/mariadb/mariadb.log |+---------------+------------------------------+1 row in set (0.001 sec)
记录哪些警告信息至错误日志文件
#CentOS7 mariadb 5.5 默认值为1#CentOS8 mariadb 10.3 默认值为2log_warnings=0|1|2|3... #MySQL5.7之前log_error_verbosity=0|1|2|3... #MySQL8.0
MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE 'log_warnings';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_warnings | 2 |+---------------+-------+1 row in set (0.001 sec)root@db 15:13: [hellodb]SHOW GLOBAL VARIABLES LIKE 'log_error_verbosity';+---------------------+-------+| Variable_name | Value |+---------------------+-------+| log_error_verbosity | 2 |+---------------------+-------+1 row in set (0.01 sec)
3.6.3通用日志
通用日志：记录对数据库的通用操作，包括:错误的SQL语句
通用日志可以保存在：file（默认值）或 table（mysql.general_log表）
通用日志相关设置
general_log=ON|OFFgeneral_log_file=HOSTNAME.loglog_output=TABLE|FILE|NONE
启用通用日志并记录至文件中
#默认没有启用通用日志root@db 07:41: [(none)]select @@general_log;+---------------+| @@general_log |+---------------+| 0 |+---------------+1 row in set (0.00 sec)#启用root@db 07:41: [(none)]set global general_log=1;Query OK, 0 rows affected (0.03 sec)root@db 07:41: [(none)]select @@general_log;+---------------+| @@general_log |+---------------+| 1 |+---------------+1 row in set (0.00 sec)#默认通用日志存放在文件中root@db 07:41: [(none)]show global variables like 'log_output';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_output | FILE |+---------------+-------+1 row in set (0.02 sec)#通用日志存放的文件路径root@db 07:42: [(none)]select @@general_log_file;+------------------------------+| @@general_log_file |+------------------------------+| /var/lib/mysql/localhost.log |+------------------------------+1 row in set (0.00 sec)root@db 07:42: [(none)]set global log_output="table";Query OK, 0 rows affected (0.00 sec)#修改通用日志记录到表中root@db 07:42: [(none)]set global log_output="table";Query OK, 0 rows affected (0.00 sec)root@db 07:43: [(none)]SHOW GLOBAL VARIABLES LIKE 'log_output';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_output | TABLE |+---------------+-------+1 row in set (0.01 sec)#general_log表是CSV格式的存储引擎root@db 07:45: [hellodb]use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedroot@db 07:45: [mysql]show table status like 'general_log'\G*************************** 1. row *************************** Name: general_log Engine: CSV Version: 10 Row_format: Dynamic Rows: 1 Avg_row_length: 0 Data_length: 0Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2021-12-06 15:30:36 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: General log1 row in set (0.00 sec)#general_log表是CSV的文本文件[root@localhost ~]#file /var/lib/mysql/mysql/general_log.CSV/var/lib/mysql/mysql/general_log.CSV: ASCII text#查看通用日志[root@localhost ~]#head /var/lib/mysql/mysql/general_log.CSV"2021-12-09 07:43:18.228299","root[root] @ localhost []",8,1,"Query","SHOW GLOBAL VARIABLES LIKE 'log_output'""2021-12-09 07:43:41.333237","root[root] @ localhost []",8,1,"Query","SELECT DATABASE()""2021-12-09 07:43:46.210986","root[root] @ localhost []",8,1,"Query","SELECT DATABASE()""2021-12-09 07:43:46.211392","root[root] @ localhost []",8,1,"Init DB","hellodb""2021-12-09 07:43:46.211954","root[root] @ localhost []",8,1,"Query","show databases""2021-12-09 07:43:46.214147","root[root] @ localhost []",8,1,"Query","show tables""2021-12-09 07:43:46.246107","root[root] @ localhost []",8,1,"Field List","classes ""2021-12-09 07:43:46.424033","root[root] @ localhost []",8,1,"Field List","coc ""2021-12-09 07:43:46.424790","root[root] @ localhost []",8,1,"Field List","courses ""2021-12-09 07:43:46.425216","root[root] @ localhost []",8,1,"Field List","scores "
#查找执行次数最多的前三条语句root@db 07:45: [mysql] select argument,count(argument) num from mysql.general_log group by argument order by num desc limit 3;+----------------------------------------------------------------------------+-----+| argument | num |+----------------------------------------------------------------------------+-----+| 0x53454C4543542044415441424153452829 | 3 || 0x73686F77207461626C6520737461747573206C696B65202767656E6572616C5F6C6F6727 | 2 || 0x73686F7720646174616261736573 | 2 |+----------------------------------------------------------------------------+-----+3 rows in set (0.00 sec)#对访问的语句进行排序[root@localhost ~]#mysql -uroot -p'sunxiang' -e 'select argument from mysql.general_log' | awk '{sql[$0]++}END{for(i in sql){print sql[i],i}}'|sort -nr[root@localhost ~]#mysql -uroot -p'sunxiang' -e 'select argument from mysql.general_log' |sort |uniq -c |sort -nr
3.6.4慢查询日志
慢查询日志：记录执行查询时长超出指定时长的操作
慢查询相关变量
slow_query_log=ON|OFF #开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件long_query_time=N #慢查询的阀值，单位秒,默认为10sslow_query_log_file=HOSTNAME-slow.log #慢查询日志文件log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk#上述查询类型且查询时长超过long_query_time，则记录日志log_queries_not_using_indexes=ON #不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语句是否记录日志，默认OFF，即不记录log_slow_rate_limit = 1 #多少次查询才记录，mariadb特有log_slow_verbosity= Query_plan,explain #记录内容log_slow_queries = OFF #同slow_query_log，MariaDB 10.0/MySQL 5.6.1 版后已删除
慢查询分析工具mysqldumpslow
[root@localhost ~]#mysqldumpslow --helpUsage: mysqldumpslow [ OPTS... ] [ LOGS... ]Parse and summarize the MySQL slow query log. Options are --verbose verbose --debug debug --help write this text to standard output -v verbose -d debug -s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default al: average lock time ar: average rows sent at: average query time c: count l: lock time r: rows sent t: query time -r reverse the sort order (largest last instead of first) -t NUM just show the top n queries -a don't abstract all numbers to N and strings to 'S' -n NUM abstract numbers with at least n digits within names -g PATTERN grep: only consider stmts that include this string -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all -i NAME name of server instance (if using mysql.server startup script) -l don't subtract lock time from total time
#查看是否开启慢查询
root@db 08:41: [mysql]select @@slow_query_log;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id: 19
Current database: mysql
+------------------+
| @@slow_query_log |
+------------------+
| 0 |
+------------------+
1 row in set (0.04 sec)
#开启慢查询（需要全局）
root@db 08:41: [mysql]set slow_query_log=1;
ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBAL
root@db 08:42: [mysql]set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)
root@db 08:42: [mysql]select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
#查看慢查询日志文件
root@db 08:42: [mysql]select @@slow_query_log_file;
+-----------------------------------+
| @@slow_query_log_file |
+-----------------------------------+
| /var/lib/mysql/localhost-slow.log |
+-----------------------------------+
1 row in set (0.00 sec)
#通过mysqldumpslow查看
[root@localhost ~]#mysqldumpslow -s c -t 2 /var/lib/mysql/localhost-slow.log
Reading mysql slow query log from /var/lib/mysql/localhost-slow.log
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts
Died at /usr/bin/mysqldumpslow line 162, <> chunk 1.
3.6.5★★二进制日志（备份）★★
记录导致数据改变或潜在导致数据改变的SQL语句
记录已提交的日志
不依赖于存储引擎类型
功能：通过”重放”日志文件中的事件来生成数据副本
注意：建议二进制日志和数据文件分开存放（防止删库不是简单的使用dorp删库而是直接rm -rf mysql这样的如果在同一个目录下也会将二进制文件删除使得无法进行数据恢复）
二进制日志记录三种格式
基于"语句"记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下 ），日志量较少
基于"行"记录：row，记录数据，日志量较大，更加安全，建议使用的格式,MySQL8.0默认格式（空间不够就买买买，领导不批就不是我们的锅了但是自作主张用statement那到时候就要扯皮了）
混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上）
说明：
update students age=20 where stuid>10;
语句记录：对于学生表总共用25个学生，此命令会对15个学生生效。但是此时只记录update students age=20 where stuid>10;这一条命令
行记录：对15个学生生效，会每个都记录update students age=20 where stuid=11；……update students age=20 where stuid=25；
格式配置
#mysql8.0默认为行记录
root@db 08:42: [mysql]show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.02 sec)
#mariadb为混合模型（切记修改为行记录）
MariaDB [hellodb]> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.001 sec)
二进制日志文件的构成
有两类文件
1.日志文件：mysql|mariadb-bin.文件名后缀，二进制格式,如： on.000001,mariadb-bin.000002
2.索引文件：mysql|mariadb-bin.index，文本格式,记录当前已有的二进制日志文件列表
二进制日志相关的服务器变量：
sql_log_bin=ON|OFF：#是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项
log_bin=/PATH/BIN_LOG_FILE：#指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可以
binlog_format=STATEMENT|ROW|MIXED：#二进制日志记录的格式，mariadb5.5默认STATEMENT
max_binlog_size=1073741824：#单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
#说明：文件达到上限时的大小未必为指定的精确值
binlog_cache_size=4m #此变量确定在每次事务中保存二进制日志更改记录的缓存的大小（每次连接）
max_binlog_cache_size=512m #限制用于缓存多事务查询的字节大小。
sync_binlog=1|0：#设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
expire_logs_days=N：#二进制日志可以自动删除的天数。 默认为0，即不自动删除
注意：
log_bin=/PATH/BIN_LOG_FILE #而不是直接修改为on或者off，因为这是指定存放路径的默认为/var/lib/mysql/binlog.0001这是与数据库在同一位置的不安全所以需要手动指定一下
#修改配置文件指定二进制日志存储路径
[root@localhost ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
log-bin=/data/mysql/mysql-bin #log-bin为服务器选项 log_bin为系统变量（具体查看官方文档）
#创建目录
[root@localhost ~]#mkdir /data/mysql/ -pv
mkdir: created directory '/data'
mkdir: created directory '/data/mysql/'
#修改目录属主使得mysql具有权限
[root@localhost ~]#chown mysql.mysql /data/mysql/
#重启服务，查看二进制日志
[root@localhost ~]#systemctl restart mysqld.service
[root@localhost ~]#ll /data/mysql/
total 8
-rw-r----- 1 mysql mysql 156 Dec 9 10:29 mysql-bin.000001
-rw-r----- 1 mysql mysql 29 Dec 9 10:29 mysql-bin.index
二进制日志相关配置
查看mariadb自行管理使用中的二进制日志文件列表，及大小
SHOW {BINARY | MASTER} LOGS
root@db 09:38: [hellodb]show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 803 | No |
| binlog.000002 | 11276 | No |
| binlog.000003 | 596 | No |
| binlog.000004 | 179 | No |
| binlog.000005 | 179 | No |
| binlog.000006 | 179 | No |
| binlog.000007 | 179 | No |
| binlog.000008 | 179 | No |
| binlog.000009 | 179 | No |
| binlog.000010 | 179 | No |
| binlog.000011 | 179 | No |
| binlog.000012 | 179 | No |
| binlog.000013 | 179 | No |
| binlog.000014 | 512 | No |
| binlog.000015 | 179 | No |
| binlog.000016 | 179 | No |
| binlog.000017 | 1281 | No |
+---------------+-----------+-----------+
17 rows in set (0.00 sec)
查看使用中的二进制日志文件
SHOW MASTER STATUSroot@db 09:39: [hellodb]show master status;+---------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+---------------+----------+--------------+------------------+-------------------+| binlog.000017 | 1583 | | | |+---------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)
在线查看二进制文件中的指定内容
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]root@db 09:44: [hellodb]SHOW BINLOG EVENTS in 'binlog.000017' from 1505\G;*************************** 1. row *************************** Log_name: binlog.000017 Pos: 1505 Event_type: Write_rows Server_id: 1End_log_pos: 1552 Info: table_id: 109 flags: STMT_END_F*************************** 2. row *************************** Log_name: binlog.000017 Pos: 1552 Event_type: Xid Server_id: 1End_log_pos: 1583 Info: COMMIT /* xid=127 */2 rows in set (0.00 sec)ERROR: No query specified
mysqlbinlog：二进制日志的客户端命令工具，支持离线查看二进制日志
命令格式：
mysqlbinlog [OPTIONS] log_file… --start-position= # 指定开始位置 --stop-position= # 指定结束位置 --start-datetime= #时间格式：YYYY-MM-DD hh:mm:ss --stop-datetime= --base64-output[=name] -v -vvv
[root@localhost ~]#mysqlbinlog --start-position=1360 --stop-position=1522 /var/lib/mysql/binlog.000017 -vvv# The proper term is pseudo_replica_mode, but we use this compatibility alias# to make the statement usable on server versions 8.0.24 and older./*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 156#211209 7:53:15 server id 1 end_log_pos 125 CRC32 0xc9c4734d Start: binlog v 4, server v 8.0.26 created 211209 7:53:15 at startup# Warning: this binlog is either in use or was not closed properly.ROLLBACK/*!*/;BINLOG 'a0WxYQ8BAAAAeQAAAH0AAAABAAQAOC4wLjI2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABrRbFhEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQACigBTXPEyQ=='/*!*/;# at 1360#211209 9:39:24 server id 1 end_log_pos 1438 CRC32 0x83cd89f3 Query thread_id=19 exec_time=0 error_code=0SET TIMESTAMP=1639013964/*!*/;SET @@session.pseudo_thread_id=19/*!*/;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 utf8mb4 *//*!*/;SET @@session.character_set_client=255,@@session.collation_connection=255,@@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 1438#211209 9:39:24 server id 1 end_log_pos 1505 CRC32 0x073ad2d3 Table_map: `hellodb`.`teachers` mapped to number 109# at 1505#211209 9:39:24 server id 1 end_log_pos 1552 CRC32 0xb65dec45 Write_rows: table id 109 flags: STMT_END_FBINLOG 'TF6xYRMBAAAAQwAAAOEFAAAAAG0AAAAAAAEAB2hlbGxvZGIACHRlYWNoZXJzAAQCDwH+BCwB9wEIAQHAAgEh09I6Bw==TF6xYR4BAAAALwAAABAGAAAAAG0AAAAAAAEAAgAE/wAGAAUAdGVzdDIeAkXsXbY='/*!*/;### INSERT INTO `hellodb`.`teachers` #这里就是插入的语句（insert teachers values(null,'test2','30','M');）### SET### @1=6 /* SHORTINT meta=0 nullable=0 is_null=0 */### @2='test2' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */### @3=30 /* TINYINT meta=0 nullable=0 is_null=0 */### @4=2 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */ROLLBACK /* added by mysqlbinlog */ /*!*/;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@localhost ~]#
二进制日志事件的格式：
# at 328#211209 9:39:24 server id 1 end_log_pos 431 Query thread_id=1 exec_time=0 error_code=0use `mydb`/*!*/;SET TIMESTAMP=1446712300/*!*/;CREATE TABLE tb1 (id int, name char(30))/*!*/; 事件发生的日期和时间：211209 9:39:24事件发生的服务器标识：server id 1事件的结束位置：end_log_pos 431事件的类型：Query事件发生时所在服务器执行此事件的线程的ID：thread_id=1语句的时间戳与将其写入二进制文件中的时间差：exec_time=0错误代码：error_code=0事件内容：GTID：Global Transaction ID，mysql5.6以mariadb10以上版本专属属性：GTID
清除指定二进制日志
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
#删除binlog.000010之前的日志
root@db 09:44: [hellodb]purge binary logs to 'binlog.000010';
Query OK, 0 rows affected (0.00 sec)
root@db 09:50: [hellodb]show master logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000010 | 179 | No |
| binlog.000011 | 179 | No |
| binlog.000012 | 179 | No |
| binlog.000013 | 179 | No |
| binlog.000014 | 512 | No |
| binlog.000015 | 179 | No |
| binlog.000016 | 179 | No |
| binlog.000017 | 1583 | No |
+---------------+-----------+-----------+
8 rows in set (0.00 sec)
#删除指定时间之前的日志
[root@localhost ~]#mysqlbinlog --start-position=4 /var/lib/mysql/binlog.000015 -vvv
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#211208 8:01:29 server id 1 end_log_pos 125 CRC32 0xaad862a9 Start: binlog v 4, server v 8.0.26 created 211208 8:01:29 at startup #获取时间信息2021-12-08 08:01:29
ROLLBACK/*!*/;
BINLOG '
2fWvYQ8BAAAAeQAAAH0AAAAAAAQAOC4wLjI2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADZ9a9hEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigBqWLYqg==
'/*!*/;
# at 125
#211208 8:01:30 server id 1 end_log_pos 156 CRC32 0x2da5051b Previous-GTIDs
# [empty]
# at 156
#211208 17:03:24 server id 1 end_log_pos 179 CRC32 0xfd539908 Stop
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@db 09:52: [hellodb]purge binary logs before '2021-12-08 08:01:29';
Query OK, 0 rows affected (0.00 sec)
root@db 09:54: [hellodb]show master logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000015 | 179 | No |
| binlog.000016 | 179 | No |
| binlog.000017 | 1583 | No |
+---------------+-----------+-----------+
3 rows in set (0.00 sec)
删除所有二进制日志，index文件重新记数
RESET MASTER [TO #]; #删除所有二进制日志文件，并重新生成日志文件，文件名从#开始记数，默认从1开始，一般是master主机第一次启动时执行，MariaDB 10.1.6开始支持TO #
切换日志文件：
FLUSH LOGS;