初识 MySQL 日志 binlog
MySQL 重要 log, 二进制日志文件, 记录所有 DDL 和 DML 语句(除 select), 事件形式记录, 包含语句所执行的消耗时间, 事务安全型.
DDL(数据库定义语言), 主要命令有 create,alter,drop 等. DDL 主要定义或改变表 table 的结构, 数据类型. 建表时使用.
MDL(数据操纵语言), 主要命令有 select,update,insert,delete.
mysqlbinlog 常见选项:
--start-datetime: 从二进制中读取指定时间戳.
--stop-datetime: 从二进制中读取指定时间戳.
--start-position: 从二进制中读取指定 position 事件位置.
--stop-position: 从二进制中读取指定 position 事件位置.
binlog 日志使用场景:
1)MySQL 主从复制: MySQL 在 replication 在 Master 端开启 binlog,master 将二进制文件传递给 slaves 来达到 master-slave 数据一致.
2)数据恢复: 通过 mysqlbinlog 工具恢复数据
binlog 日志包括两类文件:
1)二进制日志索引文件 (文件后缀名. index) 用于记录所有二进制文件
2)二进制日志文件 (文件后缀名. 00000) 记录数据库所有 DDL 和 DML(select 除)语句事件
开启 binlog 日志和基本操作
开启 binlog:
/etc/my.cnf 文件
log-bin=MySQL-bin
MySQL 命令操作:
查看日志开启
MySQL>show variables like 'log_%';
查看所有 binlog 列表
MySQL>show master logs;
查看 master 状态, 最后一个 binlog 日志的编号名称, 及最后一个操作事件 pos 结束点 (position) 值.
MySQL>show master status;
刷新 log 日志, 开始产生一新的编号的 binlog 日志文件
MySQL>flush logs;
注: mysqld 服务重启时, 也会执行此命令, 刷新 binlog 日志.
重置 (清空) 所有 binlog
MySQL>reset master;
MySQL binlog 日志内容查看
binlog 日志为二进制文件, cat,VIM 等工具无法打开, 使用自带的 mysqlbinlog 命令查看
binlog 内容分析:
- # at 28226679
- #181116 0:20:01 server id 776499703 end_log_pos 28226710 CRC32 0xb12373d2 Xid = 54012151
- COMMIT/*!*/;
- SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
- # at 28226710
- #181116 0:20:35 server id 3095565156 end_log_pos 28226757 CRC32 0x05f2d78a Rotate to MySQL-bin.001121 pos: 4
- DELIMITER ;
- # End of log file
- ROLLBACK /* added by mysqlbinlog */;
- /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
- /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
server id : 数据库主机服务号
end_log_pos: 数据库 SQL 结束时 pos 节点
binlog 日志查看命令:
MySQL> show binlog events in 'mysql-bin.001120'\G;
binlog 从 pos 点 100 开始查, 查询 10 条
MySQL> show binlog events in 'mysql-bin.001120' from 100 limit 10\G;
利用 binlog 日志恢复数据库
1)恢复就近的完整备份库
MySQL -uusername -p -v db_test -h 192.168.1.100 < ./db_test .sql
2)通过 binlog 找到要恢复的 --stop-position 点 和 --stop-position
- binlog:MySQL-bin.001118 ,MySQL-bin.001119 ,MySQL-bin.001120
- mysqlbinlog -v --base64-output=DECODE-ROWS /data/binlog/MySQL-bin.001118 | head -10
- mysqlbinlog -v --base64-output=DECODE-ROWS /data/binlog/MySQL-bin.001120 | grep -C 30 -i "22:05:01"
3)binlog 恢复
/bin/mysqlbinlog --start-position=120 --stop-position=2174941 /data/binlog/MySQL-bin.001118 /data/binlog/MySQL-bin.001119 /data/binlog/MySQL-bin.001120 |/bin/MySQL -uusername -p -v db_test -h 192.168.1.100
--start-position 是备份后记录下的 pos 点, --stop-position 是误操前的 pos 点, 如果批多个 binlog 文件, 那么 start-position 是第一个 binlog 文件的 pos 点, stop-position 是最后一个 binlog 的 pos 点
总结: 所谓恢复, 就是用 MySQL 保存的 binlog 日志中指定的区间段的 sql 语句进行重新执行一次.
来源: http://www.bubuko.com/infodetail-2969683.html