mysql8 binlog查看

说明：MySQL 8.0默认开启binlog，相当于默认有 log_bin=binlog 参数
即使在my.ini或my.cnf的[mysqld]部分没有log_bin或log_bin = mysql-bin
binlog默认在Linux的/var/lib/mysql目录下，文件名为binlog.000001格式，在Windows的data目录下，文件名为binlog.000001格式

参考：
mysql8 设置binlog过期时间
MySQL 8.0关闭binlog

关闭binlog

在my.ini或my.cnf的[mysqld]部分添加skip-log-bin，然后重启mysql即可。

• 查看log_bin状态

mysql> show global 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                         |
+---------------------------------+-----------------------------+
5 rows in set (0.01 sec)

• binlog默认保存30天

mysql> show global variables like '%logs%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| binlog_expire_logs_seconds | 2592000 |
| expire_logs_days           | 0       |
| innodb_print_ddl_logs      | OFF     |
+----------------------------+---------+
3 rows in set (0.01 sec)

• binlog格式

mysql> show global variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

mysql8 设置binlog过期时间

mysql8中 使用binlog_expire_logs_seconds 设置日志过期时间，废弃参数：expire_logs_days

mysql> set global binlog_expire_logs_seconds=604800;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%logs%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| binlog_expire_logs_seconds | 604800 |
| expire_logs_days           | 0      |
| innodb_print_ddl_logs      | OFF    |
+----------------------------+--------+
3 rows in set (0.00 sec)

mysql> set global expire_logs_days=7;
ERROR 3683 (HY000): The option expire_logs_days and binlog_expire_logs_seconds cannot be used together. Please use binlog_expire_logs_seconds to set the expire time (expire_logs_days is deprecated)

查看binlog

查看binary logs名称列表

mysql>  show binary logs;
+---------------+------------+-----------+
| Log_name      | File_size  | Encrypted |
+---------------+------------+-----------+
| binlog.000012 |          0 | No        |
| binlog.000013 |          0 | No        |
| binlog.000014 |          0 | No        |
| binlog.000015 |          0 | No        |
| binlog.000016 |          0 | No        |
| binlog.000017 |          0 | No        |
| binlog.000018 |          0 | No        |
| binlog.000019 |          0 | No        |
| binlog.000020 | 1092751744 | No        |
| binlog.000021 | 1092751276 | No        |
| binlog.000022 | 1092751276 | No        |
| binlog.000023 | 1092755349 | No        |
| binlog.000024 | 1100627079 | No        |
| binlog.000025 | 1105025425 | No        |
| binlog.000026 | 1102904640 | No        |
| binlog.000027 | 1091770526 | No        |
| binlog.000028 | 1091044815 | No        |
| binlog.000029 | 1084623602 | No        |
| binlog.000030 | 1074122474 | No        |
| binlog.000031 | 1073813712 | No        |
| binlog.000032 | 1073837594 | No        |
| binlog.000033 |  490675736 | No        |
| binlog.000034 | 1073984472 | No        |
| binlog.000035 | 1073742240 | No        |
| binlog.000036 | 1073747843 | No        |
| binlog.000037 | 1073769856 | No        |
| binlog.000038 | 1074114169 | No        |
| binlog.000039 | 1073848360 | No        |
| binlog.000040 | 1073742229 | No        |
| binlog.000041 | 1073748260 | No        |
| binlog.000042 |  269012957 | No        |
| binlog.000043 |   45657446 | No        |
| binlog.000044 |    9510533 | No        |
| binlog.000045 |     659327 | No        |
| binlog.000046 | 1373497328 | No        |
| binlog.000047 |  679548061 | No        |
+---------------+------------+-----------+
36 rows in set (0.08 sec)

查看binlog内容

mysql> show binlog events;

手工刷新日志

mysql> flush  logs;

手动清理日志

#将指定时间之前的日志清理
purge binary logs before '2018-02-01 12:00:00';

#将指定日志文件之前的日志清除
purge binary logs to 'mysql-bin.000003';

mysql> purge binary logs to 'HURYER-PC-bin.000029';
Query OK, 0 rows affected (0.01 sec)

mysql> show binary logs;
+----------------------+-----------+-----------+
| Log_name             | File_size | Encrypted |
+----------------------+-----------+-----------+
| HURYER-PC-bin.000029 |       155 | No        |
+----------------------+-----------+-----------+
1 row in set (0.00 sec)
mysql> purge binary logs before '2020-03-08';
Query OK, 0 rows affected (0.02 sec)