mysql 主从复制清理binlog日志
mysql 主从复制环境怎么清理binlog日志？
1.主库binlog日志确认
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000011 #当前正在用的binlog日志
Position: 194 #当前binlog的位点信息
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 9253806d-59b3-11ec-8be8-000c29355801:1-31 # uuid:xid 每提交一个事务xid都会变化
1 row in set (0.00 sec)
mysql> show variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 9253806d-59b3-11ec-8be8-000c29355801 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000010 | 582 |
| mysql-bin.000011 | 194 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql> flush binary logs; #关闭当前二进制日志文件 开启新的二进制日志文件
Query OK, 0 rows affected (0.01 sec)
* FLUSH BINARY LOGS
Closes and reopens any binary log file to which the server is writing. If binary logging is enabled, the sequence number of the binary log file is incremented by one relative to the previous file.
This operation requires the RELOAD privilege.
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000010 | 582 |
| mysql-bin.000011 | 241 |
| mysql-bin.000012 | 194 |
+------------------+-----------+
3 rows in set (0.00 sec)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000012
Position: 194
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 9253806d-59b3-11ec-8be8-000c29355801:1-31
1 row in set (0.00 sec)
mysql> update dbtest1.t611 set id=22;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000012
Position: 846
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 9253806d-59b3-11ec-8be8-000c29355801:1-32
1 row in set (0.00 sec)2.确认从库应用到的binlog日志文件，当前执行到主库的mysql-bin.000013，说明之前的binlog都可以清理。
-- Slave_IO_Running和Slave_SQL_Running都为yes状态，表示主从搭建完成，Seconds_Behind_Master表示主从之间延迟，默认单位为S
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.201
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000013 #IO线程读取的主库binlog日志文件
Read_Master_Log_Pos: 516 #IO线程读取的主库binlog的位置
Relay_Log_File: relay-bin.000008 #SQL线程读取和执行的从库中继日志文件
Relay_Log_Pos: 729 #SQL线程读取和执行的从库中继日志位置
Relay_Master_Log_File: mysql-bin.000013 #中继日志正在执行的主库binlog日志文件
Slave_IO_Running: Yes #IO线程传输正常
Slave_SQL_Running: Yes #SQL线程relay应用正常
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 516 #正在执行主库binlog日志位置
Relay_Log_Space: 1017
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0 #主从同步延迟为0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2013306
Master_UUID: 9253806d-59b3-11ec-8be8-000c29355801
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 9253806d-59b3-11ec-8be8-000c29355801:32-34 #从库已经接收的事务XID
Executed_Gtid_Set: 9253806d-59b3-11ec-8be8-000c29355801:1-34, #从库已经执行的事务XID
f7ca2f5f-07ab-11ee-a9c7-000c29871e60:1-2
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)3.清理binlog日志，保留周期10天，mysql-bin.000013
mysql> show variables like '%expire_logs_days%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 10 |
+------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like '%log_bin%';
+---------------------------------+-------------------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------------------+
| log_bin | ON |
| log_bin_basename | /mysqldata/mysql_3306/log/mysql-bin |
| log_bin_index | /mysqldata/mysql_3306/log/mysql-bin.index |
| log_bin_trust_function_creators | ON |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------------------------------------------+
mysql> system ls -lrth /mysqldata/mysql_3306/log/mysql-bin*
-rw-r----- 1 mysql mysql 582 Aug 10 17:33 /mysqldata/mysql_3306/log/mysql-bin.000010
-rw-r----- 1 mysql mysql 241 Aug 10 18:20 /mysqldata/mysql_3306/log/mysql-bin.000011
-rw-r----- 1 mysql mysql 893 Aug 10 18:27 /mysqldata/mysql_3306/log/mysql-bin.000012
-rw-r----- 1 mysql mysql 172 Aug 10 18:27 /mysqldata/mysql_3306/log/mysql-bin.index
-rw-r----- 1 mysql mysql 516 Aug 10 18:30 /mysqldata/mysql_3306/log/mysql-bin.000013
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000010 | 582 |
| mysql-bin.000011 | 241 |
| mysql-bin.000012 | 893 |
| mysql-bin.000013 | 516 |
+------------------+-----------+
4 rows in set (0.00 sec)4.清理binlog，需要先确认主从同步状态，以及确认从库应用到Relay_Master_Log_File: mysql-bin.000013 #中继日志正在执行的主库binlog日志文件
The PURGE BINARY LOGS statement deletes all the binary log files listed in the log index file prior to the specified log file name or date. and are synonyms. Deleted log files also are removed from the list recorded in the index file, so that the given log file becomes the first in the list. BINARYMASTER
PURGE BINARY LOGS requires the BINLOG_ADMIN privilege. This statement has no effect if the server was not started with the --log-bin option to enable binary logging.
Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';
The variant's datetime_expr argument should evaluate to a DATETIME value (a value in format). BEFORE'YYYY-MM-DD hh:mm:ss'
This statement is safe to run while replicas are replicating. You need not stop them. If you have an active replica that currently is reading one of the log files you are trying to delete, this statement does not delete the log file that is in use or any log files later than that one, but it deletes any earlier log files. A warning message is issued in this situation. However, if a replica is not connected and you happen to purge one of the log files it has yet to read, the replica cannot replicate after it reconnects.
此语句在副本正在复制时运行是安全的。您无需停止它们。如果您有一个活动副本当前正在读取您尝试删除的其中一个日志文件，此语句不会删除正在使用的日志文件或该文件之后的任何日志文件，但会删除任何更早的日志文件。在这种情况下会发出警告消息。但是，如果副本未连接，而您恰好清除了它尚未读取的其中一个日志文件，则副本在重新连接后无法复制。
To safely purge binary log files, follow this procedure:
1. On each replica, use SHOW SLAVE STATUS to check which log file it is reading.
2. Obtain a listing of the binary log files on the replication source server with SHOW BINARY LOGS.
3. Determine the earliest log file among all the replicas. This is the target file. If all the replicas are up to date, this is the last log file on the list.
4. Make a backup of all the log files you are about to delete. (This step is optional, but always advisable.)
5. Purge all log files up to but not including the target file.
PURGE BINARY LOGS TO 'mysql-bin.000012'; #清理mysql-bin.000012之前的binlog 不包含12
PURGE BINARY LOGS BEFORE '2024-08-10 18:20:00'; #清理2024-08-10 18:20:00之前的binlog 不包含18:20:00 时间可以从系统上查看
mysql> system ls -lrth /mysqldata/mysql_3306/log/mysql-bin*
-rw-r----- 1 mysql mysql 582 Aug 10 17:33 /mysqldata/mysql_3306/log/mysql-bin.000010
-rw-r----- 1 mysql mysql 241 Aug 10 18:20 /mysqldata/mysql_3306/log/mysql-bin.000011
-rw-r----- 1 mysql mysql 893 Aug 10 18:27 /mysqldata/mysql_3306/log/mysql-bin.000012
-rw-r----- 1 mysql mysql 172 Aug 10 18:27 /mysqldata/mysql_3306/log/mysql-bin.index
-rw-r----- 1 mysql mysql 516 Aug 10 18:30 /mysqldata/mysql_3306/log/mysql-bin.000013
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000010 | 582 |
| mysql-bin.000011 | 241 |
| mysql-bin.000012 | 893 |
| mysql-bin.000013 | 516 |
+------------------+-----------+
4 rows in set (0.00 sec)
mysql> PURGE BINARY LOGS BEFORE '2024-08-10 18:20:00';
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000011 | 241 |
| mysql-bin.000012 | 893 |
| mysql-bin.000013 | 516 |
+------------------+-----------+
3 rows in set (0.00 sec)
mysql> system ls -lrth /mysqldata/mysql_3306/log/mysql-bin*
-rw-r----- 1 mysql mysql 241 Aug 10 18:20 /mysqldata/mysql_3306/log/mysql-bin.000011
-rw-r----- 1 mysql mysql 893 Aug 10 18:27 /mysqldata/mysql_3306/log/mysql-bin.000012
-rw-r----- 1 mysql mysql 516 Aug 10 18:30 /mysqldata/mysql_3306/log/mysql-bin.000013
-rw-r----- 1 mysql mysql 129 Aug 10 18:55 /mysqldata/mysql_3306/log/mysql-bin.index
mysql> PURGE BINARY LOGS TO 'mysql-bin.000012';
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000012 | 893 |
| mysql-bin.000013 | 516 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql> system ls -lrth /mysqldata/mysql_3306/log/mysql-bin*
-rw-r----- 1 mysql mysql 893 Aug 10 18:27 /mysqldata/mysql_3306/log/mysql-bin.000012
-rw-r----- 1 mysql mysql 516 Aug 10 18:30 /mysqldata/mysql_3306/log/mysql-bin.000013
-rw-r----- 1 mysql mysql 86 Aug 10 18:56 /mysqldata/mysql_3306/log/mysql-bin.index
原文地址：https://blog.csdn.net/Story_begins/article/details/142823445
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！