1、
https://blog.csdn.net/m0_58805648/article/details/131689915
文章目录
一、 Mysql8.0 的bin log 日志关闭
1.1、查看是否已开启 bin log 日志
1.2、关闭 bin log 日志
1.3、 设置 bin log 日志的时长
1.3.1、第一种设置方式：
1.3.2、第二种设置方式
一、 Mysql8.0 的bin log 日志关闭
Mysql8.0默认开启 binlog 记录功能，如果开启 binlog 日志会增加磁盘I/O等的压力。
// 进入MySQL的数据存放目录下
[root@cloudsino ~]# cd /home/mysqldata/
[root@cloudsino mysqldata]# ll binlog* // 以下都是 binlog 日志
-rw-r----- 1 mysql mysql 56961557 6月 13 17:25 binlog.000045
-rw-r----- 1 mysql mysql 90378 6月 14 23:05 binlog.000046
-rw-r----- 1 mysql mysql 59466966 6月 16 09:57 binlog.000047
-rw-r----- 1 mysql mysql 751828 6月 19 09:07 binlog.000048
-rw-r----- 1 mysql mysql 12685 6月 19 09:30 binlog.000049
-rw-r----- 1 mysql mysql 22958 6月 19 09:47 binlog.000050
-rw-r----- 1 mysql mysql 883441 6月 20 15:07 binlog.000051
-rw-r----- 1 mysql mysql 2598766 6月 26 08:46 binlog.000052
-rw-r----- 1 mysql mysql 1277318 6月 28 10:01 binlog.000053
-rw-r----- 1 mysql mysql 179 6月 28 10:01 binlog.000054
-rw-r----- 1 mysql mysql 4348744 6月 28 16:01 binlog.000055
-rw-r----- 1 mysql mysql 585765 6月 29 16:50 binlog.000056
-rw-r----- 1 mysql mysql 5011 6月 30 17:14 binlog.000057
-rw-r----- 1 mysql mysql 49526 6月 30 17:50 binlog.000058
-rw-r----- 1 mysql mysql 8440556 7月 3 15:27 binlog.000059
-rw-r----- 1 mysql mysql 534 7月 3 15:30 binlog.000060
-rw-r----- 1 mysql mysql 37368758 7月 6 12:27 binlog.000061
-rw-r----- 1 mysql mysql 179 7月 6 12:27 binlog.000062
-rw-r----- 1 mysql mysql 147165 7月 6 17:32 binlog.000063
-rw-r----- 1 mysql mysql 549347 7月 7 11:35 binlog.000064
-rw-r----- 1 mysql mysql 9371743 7月 12 16:33 binlog.000065
-rw-r----- 1 mysql mysql 2285728 7月 12 19:25 binlog.000066
-rw-r----- 1 mysql mysql 352 7月 12 16:33 binlog.index
[root@cloudsino mysqldata]#
1.1、查看是否已开启 bin log 日志
[root@cloudsino ~]# mysql -uroot -pAgan@3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 578
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like 'log_bin'; // 查看bin log 日志是否开启。on 表示开启，off表示关闭
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show master logs; // 查看现有在用的binlog日志
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000045 | 56961557 | No |
| binlog.000046 | 90378 | No |
| binlog.000047 | 59466966 | No |
| binlog.000048 | 751828 | No |
| binlog.000049 | 12685 | No |
| binlog.000050 | 22958 | No |
| binlog.000051 | 883441 | No |
| binlog.000052 | 2598766 | No |
| binlog.000053 | 1277318 | No |
| binlog.000054 | 179 | No |
| binlog.000055 | 4348744 | No |
| binlog.000056 | 585765 | No |
| binlog.000057 | 5011 | No |
| binlog.000058 | 49526 | No |
| binlog.000059 | 8440556 | No |
| binlog.000060 | 534 | No |
| binlog.000061 | 37368758 | No |
| binlog.000062 | 179 | No |
| binlog.000063 | 147165 | No |
| binlog.000064 | 549347 | No |
| binlog.000065 | 9371743 | No |
| binlog.000066 | 3079494 | No |
+---------------+-----------+-----------+
22 rows in set (0.00 sec)
mysql> reset master; // 手动清除binlog日志
Query OK, 0 rows affected (0.02 sec)
mysql> exit // 退出
1.2、关闭 bin log 日志
配置文件的方式 my.cnf :
vim /etc/my.cnf
[mysqld]
skip-log-bin // 添加这一行，重启 mysql 服务后才生效。
[root@cloudsino ~]# vim /etc/my.cnf
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
#datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-log-bin // 添加这一行
...省略N
[root@cloudsino ~]# systemctl restart mysqld // 重启让其生效
[root@cloudsino ~]#
[root@cloudsino ~]# mysql -uroot -pAgan@3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 578
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like 'log_bin'; // 查看bin log 日志是否开启。on 表示开启，off表示关闭
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.01 sec)
mysql>
mysql> show master logs; // 查看binlog日志报错提醒没有开启binlog
ERROR 1381 (HY000): You are not using binary logging
mysql>
mysql> exit
Bye
[root@cloudsino ~]#
[root@cloudsino ~]# ll /home/mysqldata/
总用量 201332
-rw-r----- 1 mysql mysql 56 2月 28 14:15 auto.cnf
-rw------- 1 mysql mysql 1676 2月 28 14:15 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 2月 28 14:15 ca.pem
-rw-r--r-- 1 mysql mysql 1112 2月 28 14:15 client-cert.pem
-rw------- 1 mysql mysql 1680 2月 28 14:15 client-key.pem
-rw-r----- 1 mysql mysql 196608 7月 12 20:04 #ib_16384_0.dblwr
-rw-r----- 1 mysql mysql 8585216 7月 4 14:41 #ib_16384_1.dblwr
-rw-r----- 1 mysql mysql 6344 7月 12 20:02 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 7月 12 20:04 ibdata1
-rw-r----- 1 mysql mysql 50331648 7月 12 20:04 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 7月 12 20:04 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 7月 12 20:02 ibtmp1
drwxr-x--- 2 mysql mysql 187 7月 12 20:02 #innodb_temp
drwxr-x--- 2 mysql mysql 143 2月 28 14:15 mysql
-rw-r----- 1 mysql mysql 37748736 7月 12 20:04 mysql.ibd
drwxr-x--- 2 mysql mysql 8192 2月 28 14:15 performance_schema
-rw------- 1 mysql mysql 1680 2月 28 14:15 private_key.pem
-rw-r--r-- 1 mysql mysql 452 2月 28 14:15 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 2月 28 14:15 server-cert.pem
-rw------- 1 mysql mysql 1680 2月 28 14:15 server-key.pem
drwxr-x--- 2 mysql mysql 28 2月 28 14:15 sys
-rw-r----- 1 mysql mysql 16777216 7月 12 20:04 undo_001
-rw-r----- 1 mysql mysql 16777216 7月 12 20:04 undo_002
[root@cloudsino ~]#
1.3、 设置 bin log 日志的时长
1.3.1、第一种设置方式：
编辑 /etc/my.cnf 文件，在 [mysqld] 节点中增加如下两行
# 重启 mysql 服务才能生效
log-bin=mysql-bin #启动日志
max_binlog_size = 200M # 默认是 1G
expire_logs_days = 7
max_binlog_size：bin log日志每达到设定大小后，会使用新的bin log日志。如mysql-bin.000002达到200M后，创建并使用mysql-bin.000003文件作为日志记录。
expire_logs_days：保留指定日期范围内的bin log历史日志，上示例设置的7天内。
1.3.2、第二种设置方式
# mysql8.0以下版本查看当前数据库日志binlog保存时效 以天为单位，默认0 永不过期，最多只能设置99天
# show variables like 'expire_logs_days';
# set global expire_logs_days=60;
# mysql8.0以上版本通过设置全局参数binlog_expire_logs_seconds修改binlog保存时间 以秒为单位；
# 默认2592000 30天；
# 14400 4小时；
# 86400 1天；
# 259200 3天
# show variables like '%binlog_expire_logs_seconds%';
# set global binlog_expire_logs_seconds=259200;
[root@cloudsino ~]# mysql -V
mysql Ver 8.0.25 for Linux on x86_64 (MySQL Community Server - GPL)
[root@cloudsino ~]# mysql -uroot -pAgan@3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 308
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like '%binlog_expire_logs_seconds%'; // 默认 30 天
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
| binlog_expire_logs_seconds | 2592000 |
+----------------------------+---------+
mysql> set global binlog_expire_logs_seconds=259200; // 设置保存3天，重启后失效。
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%binlog_expire_logs_seconds%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| binlog_expire_logs_seconds | 259200 |
+----------------------------+--------+
1 row in set (0.00 sec)
mysql> exit
Bye
[root@cloudsino ~]#
[root@cloudsino ~]#
文章
————————————————
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
原文链接：https://blog.csdn.net/m0_58805648/article/details/131689915
2、
