Scenario: Sometimes the binary files in the disk are deleted incorrectly due to disk damage or human error. As a result, the show binary logs record in mysql does not match the binary files in the actual physical disk.
# Binlogdiff. sh
#! /Bin/sh
# Ocpyang@126.com
# Purpose: Check whether the binary log files recorded by show binary logs in mysql are compared with the actual physical files.
# Whether the binary log file corresponding to the disk is lost
Source/usr/local/mysql/scripts/mysql_env.ini
Binlog_init = tmpinit. 'date when policyymm1_d1_h1_m1_s'.txt
Binlog_midd = tmpmidd. 'date when policyymm1_d1_h1_m1_s'.txt # log file recorded by show binary logs in mysql
Binlog_res = tmpres. 'date when policyymm1_d1_h1_m1_s'.txt # binlog log file in the physical disk path
My_cnf =/usr/local/mysql/my. cnf # mysql configuration file
Log_bin_path = 'grep-I "^ log-bin" $ my_cnf | cut-d =-f 2'
Log_bin = 'dirname $ log_bin_path'
# Lookup binlog records in mysql
Mysql-h $ {MYSQL_HOST}-u $ {MYSQL_USER}-p $ {MYSQL_PASS}-e "show binary logs;" >$ {binlog_init}
Cat $ {binlog_init} | cut-f 1 | cut-d.-f 2 | grep-I '^ [0-9]' | sort-n >$ {binlog_midd}
# Lookup binlog records in disk
Ls-t $ {log_bin} | grep '^ mysql-bin' | cut-d. -f 2 | grep-I '^ [0-9]' | sort-n >$ {binlog_res}
Diff-c $ {binlog_midd} $ binlog_res
# Clean temp file
Rm-rf $ {binlog_init}
Rm-rf $ {binlog_midd}
Rm-rf $ {binlog_res}