1. Bilog cleaning of MySQL server
1.1 Using MySQL parameter control
-
expire_logs_days
- Setting the expiration date of binary log, the log after the specified number of days will be deleted automatically and can be modified dynamically.
- If a non-zero value is set, binlog file s that exceed the defined number of days may be cleaned up when mysqld starts and logs refresh
- Global variable, dynamic variable, default value is 0 (meaning that binlog will not be cleaned up automatically), integer value, range from 0 to 99
The specific implementation of automatic cleaning is: when the binlog file reaches max_binlog_size automatic switching or manual switching (flush) or MySQL startup, it traverses the index file, finds the first "last modification time" binlog file in N days, and then deletes all binlog files before the binlog file.
1.2 Manual purge Cleaning
Usually manual binlog cleaning is done using the purge command provided by MySQL. The purge command is defined as follows:
purge {binary | master} logs to "binlog-file-name" purge {binary | master} logs before "datetime-expr"
The first form of purge command cleans up all binlog files before binlog-file-name (excluding itself), while the second form of purge command cleans up binlog files (excluding itself) that last modified earlier than datetime-rxpr.
mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000006 | 1215598 | | mysql-bin.000007 | 20380128 | | mysql-bin.000008 | 57424 | | mysql-bin.000009 | 1861624 | +------------------+-----------+ 4 rows in set (0.03 sec) mysql> PURGE BINARY LOGS TO 'mysql-bin.000007'; Query OK, 0 rows affected (0.06 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000007 | 20380128 | | mysql-bin.000008 | 57424 | | mysql-bin.000009 | 1870354 | +------------------+-----------+ 3 rows in set (0.02 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2019-04-11 12:32:31 | +---------------------+ 1 row in set (0.00 sec) mysql> PURGE BINARY LOGS BEFORE '2019-04-11 12:32:31'; Query OK, 0 rows affected, 1 warning (0.11 sec) Warning (Code 1868): file /var/lib/mysql/archive/mysql-bin.000009 was not purged because it is the active log file. mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000009 | 1888784 | +------------------+-----------+ 1 row in set (0.00 sec)
1.3 script cleanup
Press binlog Number of Retention Continuous Cleaning #!/bin/bash # Bilog index file path and name binlog_index_name=/var/lib/mysql/archive/mysql-bin.index # Number of binlog s to be retained reserve_num=10 # The number of binlogs specified by the reserve_num parameter exceeds the number of binlogs specified by the reserve_num parameter, i.e. deletion, or wait for the next check. check_interv=60 # If the number of binlog s exceeds the number specified by the reserve_num parameter, the deletion will be performed. When multiple files need to be deleted, the files will be deleted one by one according to the time interval specified by the rm_interv parameter. rm_interv=0 # Execution log log_file=/tmp/`basename $0`.log rm -f $log_file # Executing Cleaning Bilog Function exec_purge_binlog() { total_num=`sed -n '$=' $binlog_index_name` let purge_num=${total_num}-${reserve_num} if [ "$purge_num" -gt "0" ];then purge_files=(`sed -n "1,${purge_num}p" $binlog_index_name`) start_file=$(basename $(sed -n '1p' $binlog_index_name)) end_file=$(basename $(sed -n "${purge_num}p" $binlog_index_name)) echo echo '==================================================================================================================================' echo "Find something to clean up binlog: binlog Total number of files: ${total_num}，The number of files needed to be retained: ${reserve_num}，Number of files to be cleaned up: ${purge_num}，File name range to be cleaned up: ${start_file}-${end_file}" echo "---------------------------`date`----------------------------------Executing binlog Cleaning up" i=0 for purge_file in "${purge_files[@]}" do echo echo "Cleaning up binlog Documents: ${purge_file} ----------------------`date`-----------------" \rm -f "$purge_file" sleep "$rm_interv" let i++ echo "Complete cleaning binlog Documents: ${purge_file} Time:`date`，Name of marked file: ${end_file}，Number of target files: ${purge_num}，Number of cleaned files: ${i}" done sed -i "1,${purge_num}d" $binlog_index_name echo "This clean-up binlog Execution Completion------------------`date`--------------------" else echo echo '==================================================================================================================================' echo "No clean-up was detected this time. binlog，Waiting for the next inspection......" fi } # Execute function calls if [ ! -f "$binlog_index_name" ];then echo "Warning, designated binlog index File does not exist, script exit!!" |tee -a $log_file exit 1 else while : do exec_purge_binlog |tee -a $log_file sleep "$check_interv" done fi
2. Bilog script cleanup of MySQL binlog server
#!/bin/bash # Bilog server file path and name binlog_dir=/opt/backup/binlog/ # Number of binlog s to be retained reserve_num=10 # The number of binlogs specified by the reserve_num parameter exceeds the number of binlogs specified by the reserve_num parameter, i.e. deletion, or wait for the next check. check_interv=60 # If the number of binlog s exceeds the number specified by the reserve_num parameter, the deletion will be performed. When multiple files need to be deleted, the files will be deleted one by one according to the time interval specified by the rm_interv parameter. rm_interv=0 # Execution log log_file=/tmp/`basename $0`.log rm -f $log_file # Executing Cleaning Bilog Function exec_purge_binlog() { total_num=`ls $binlog_dir | wc -l` let purge_num=${total_num}-${reserve_num} if [ "$purge_num" -gt "0" ];then purge_files=(`ls $binlog_dir | sort -k2 | head -$purge_num`) start_file=(`head -1 $purge_files 2>/dev/null`) end_file=(`tail -1 $purge_files 2>/dev/null`) echo echo '==================================================================================================================================' echo "Find something to clean up binlog: binlog Total number of files: ${total_num}，The number of files needed to be retained: ${reserve_num}，Number of files to be cleaned up: ${purge_num}，File name range to be cleaned up: ${start_file}-${end_file}" echo "---------------------------`date`----------------------------------Executing binlog Cleaning up" i=0 for purge_file in "${purge_files[@]}" do echo echo "Cleaning up binlog Documents: ${purge_file} ----------------------`date`-----------------" \rm -f "$binlog_dir$purge_file" sleep "$rm_interv" let i++ echo "Complete cleaning binlog Documents: ${purge_file} Time:`date`，Name of marked file: ${end_file}，Number of target files: ${purge_num}，Number of cleaned files: ${i}" done echo "This clean-up binlog Execution Completion------------------`date`--------------------" else echo echo '==================================================================================================================================' echo "No clean-up was detected this time. binlog，Waiting for the next inspection......" fi } # Execute function calls if [ ! -d "$binlog_dir" ];then echo "Warning, designated binlog File does not exist, script exit!!" |tee -a $log_file exit 1 else while : do exec_purge_binlog |tee -a $log_file sleep "$check_interv" done fi
3. relay log cleaning of MySQL server
3.1. Tools purge_relay_logs in MHA
- Manual installation of mha-node software
3.1.1 purge_relay_logs function
Create hard links for relay logs (minimizing performance problems caused by mass deletion of large files)
SET GLOBAL relay_log_purge=1; FLUSH LOGS; SET GLOBAL relay_log_purge=0;
Delete relay log (rm-f/path/to/archive_dir/*)
3.1.2 purge_relay_logs usage and parameters
- Usage:
purge_relay_logs --user=root --password=rootpass --host=127.0.0.1
-
Parameter description:
- - user: mysql username, default root
- Password: mysql password
- Port: port number
- - host: Host name, default 127.0.0.1
- Wordir: Specify the location of the hard link to create the relay log. The default is / var/tmp. After the script is executed successfully, the hard link's relay log file is deleted. Since the creation of hard-linked files in different partitions of the system will fail, the specific location of hard-linked files needs to be executed. It is recommended that the same partition be designated as relay log.
- - disable_relay_log_purge: By default, the parameter relay_log_purge=1, and the script automatically exits without any processing; by setting this parameter, the script sets relay_log_purge to 0, and after cleaning relay log, the parameter is set to OFF(0).
3.1.3 Regular cleaning of relay log s
The pureg_relay_logs script automatically cleans up the relay log without blocking the SQL thread. For relay logs that are constantly generated, deploy the script directly to crontab to achieve regular cleaning on a daily or hourly basis.
$ crontab -l # purge relay logs at 5am 0 5 * * * /usr/bin/purge_relay_logs --user=xxx --host=xxx --password=xxx --disable_relay_log_purge >> /var/log/masterha/purge_relay_logs.log 2>&1
3.1.4 Manual Cleaning Example
# purge_relay_logs --version purge_relay_logs version 0.58. # purge_relay_logs --user=xxx --host=xxx --password=xxx --disable_relay_log_purge 2019-08-07 10:28:18: purge_relay_logs script started. relay_log_purge is enabled. Disabling.. Opening /var/lib/mysql/data/relaylog/mysql-relay-bin.000005 .. Opening /var/lib/mysql/data/relaylog/mysql-relay-bin.000006 .. Executing SET GLOBAL relay_log_purge=1; FLUSH LOGS; sleeping a few seconds so that SQL thread can delete older relay log files (if it keeps up); SET GLOBAL relay_log_purge=0; .. ok. 2019-08-07 10:28:21: All relay log purging operations succeeded.
3.2 script cleanup
- Refer to the binlog cleanup script above.