做网站最便宜,西安建设工程招投标信息网,海外广告推广,包工头如何找工程项目文章目录 一、 Mysql8.0 的bin log 日志关闭1.1、查看是否已开启 bin log 日志1.2、关闭 bin log 日志1.3、 设置 bin log 日志的时长1.3.1、第一种设置方式#xff1a;1.3.2、第二种设置方式 一、 Mysql8.0 的bin log 日志关闭
Mysql8.0默认开启 binlog 记录功能#xff0c… 文章目录 一、 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的数据存放目录下
[rootcloudsino ~]# cd /home/mysqldata/
[rootcloudsino 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
[rootcloudsino mysqldata]# 1.1、查看是否已开启 bin log 日志
[rootcloudsino ~]# mysql -uroot -pAgan3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 578
Server version: 8.0.25 MySQL Community Server - GPLCopyright (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 服务后才生效。[rootcloudsino ~]# 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. Its default setting is log_binbinlog
# 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-pluginmysql_native_password#datadir/var/lib/mysql
socket/var/lib/mysql/mysql.sock
skip-log-bin // 添加这一行
...省略N
[rootcloudsino ~]# systemctl restart mysqld // 重启让其生效
[rootcloudsino ~]#
[rootcloudsino ~]# mysql -uroot -pAgan3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 578
Server version: 8.0.25 MySQL Community Server - GPLCopyright (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
[rootcloudsino ~]#
[rootcloudsino ~]# 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
[rootcloudsino ~]# 1.3、 设置 bin log 日志的时长
1.3.1、第一种设置方式
编辑 /etc/my.cnf 文件在 [mysqld] 节点中增加如下两行
# 重启 mysql 服务才能生效
log-binmysql-bin #启动日志
max_binlog_size 200M # 默认是 1G
expire_logs_days 7max_binlog_sizebin 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_days60;# 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_seconds259200;[rootcloudsino ~]# mysql -V
mysql Ver 8.0.25 for Linux on x86_64 (MySQL Community Server - GPL)
[rootcloudsino ~]# mysql -uroot -pAgan3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 308
Server version: 8.0.25 MySQL Community Server - GPLCopyright (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_seconds259200; // 设置保存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
[rootcloudsino ~]#
[rootcloudsino ~]#
