MySQLbinLog查询教程

MySQL binLog 查询教程

查看binlog文件

show binary logs;


查看当前正在写入的binlog文件

show master status;


查看指定的binlog文件

show binlog events in &＃39;mysql-bin.000060&＃39;


mysql-bin.000060 是当前binlog文件名称

从指定位置 pos 的偏移量是 offset位置查看&＃xff0c;查询 row_count 条记录

show binlog events [in &＃39;log_name&＃39;] [FROM pos] [limit [offset,] row_count]


例如&＃xff1a;

show binlog events in &＃39;mysql-bin.000060&＃39; from 21125164 limit 1,3;


备份binlog文件

/usr/bin/mysqlbinlog --no-defaults --database&＃61;lyws-repair --set-charset&＃61;utf8 --start-datetime&＃61;"2022-09-15 00:30:00" /home/mysql/mysql_master/data/mysql-bin.000060 > /home/lyws/lyws_dev/mysql_data/mysql-bin.000060


查询最新的binlog文件

awk &＃39;END {print}&＃39; /var/lib/mysql/mysql-bin.index


远程备份binlog脚本

#!/bin/sh
BACKUP_BIN&＃61;/usr/bin/mysqlbinlog
LOCAL_BACKUP_DIR&＃61;/root/cron/binlog/
BACKUP_LOG&＃61;/root/cron/binlog/backuplog
REMOTE_HOST&＃61;192.168.1.220
REMOTE_PORT&＃61;3310
REMOTE_USER&＃61;root
REMOTE_PASS&＃61;db&＃64;lyws
FIRST_BINLOG&＃61;mysql-bin.000034
## time to wait before reconnecting after failure
SLEEP_SECONDS&＃61;10
## create local_backup_dir if necessary
mkdir -p ${LOCAL_BACKUP_DIR}
cd ${LOCAL_BACKUP_DIR}
## 运行while循环&＃xff0c;连接断开后等待指定时间&＃xff0c;重新连接
while :
doif [ &＃96;ls -A "${LOCAL_BACKUP_DIR}" |wc -l&＃96; -eq 0 ];thenLAST_FILE&＃61;${FIRST_BINLOG}elseLAST_FILE&＃61;&＃96;ls -l ${LOCAL_BACKUP_DIR} | grep -v backuplog |tail -n 1 |awk &＃39;{print $9}&＃39;&＃96;fi## ${BACKUP_BIN} --raw --read-from-remote-server --stop-never --host&＃61;${REMOTE_HOST} --port&＃61;${REMOTE_PORT} --user&＃61;${REMOTE_USER} --password&＃61;${REMOTE_PASS} ${LAST_FILE}${BACKUP_BIN} --raw --read-from-remote-server --stop-never --host&＃61;${REMOTE_HOST} --port&＃61;${REMOTE_PORT} --user&＃61;${REMOTE_USER} --password&＃61;${REMOTE_PASS} ${LAST_FILE}echo "&＃96;date &＃43;"%Y/%m/%d %H:%M:%S"&＃96; mysqlbinlog停止&＃xff0c;返回代码&＃xff1a;$?" | tee -a ${BACKUP_LOG}echo "${SLEEP_SECONDS}秒后再次连接并继续备份" | tee -a ${BACKUP_LOG} sleep ${SLEEP_SECONDS}
done


查询binglog版本

msyqlbinlog --version