本文摘自PHP中文网,作者齐天大圣,侵删。
MySQL二进制日志里保存会造成或可能造成数据变化的SQL语句。通过二进制日志可以完成如实时异地容灾备份、读写分离、数据恢复等功能。下面,我们一起来看看Mysql二进制日志。开启bin-log日志
Mysql默认是没有开启bin-log日志的,需要我们自己去添加配置。
1 2 3 4 | log-bin=mysql-bin
binlog_format=mixed
server-id = 1
expire_logs_days = 10
|
log-bin 配置了该项后,即表示开启二进制日志功能。mysql-bin是bin-log日志文件名。
expire_logs_days = 10 表明只存放最近10天的bin-log日志。
一般bin-log日志存放在 mysql安装路径/var/下面
运维提示:二进制日志文件与数据库数据文件最好不要放在同一块硬盘上,如果存放数据文件的硬盘坏了,可以用另一块硬盘的二进制日志来恢复数据
几个有用的命令
mysql > show master status

Mysql日志的查看
因为该日志是二进制日志,所以用一般的命令cat或vim查看会是一推乱码。Mysql为我们提供了工具mysqlbinlog。用它就可以来查看了。
1 2 3 4 5 6 7 8 9 10 11 | ./mysqlbinlog ../var/mysql-bin.000015
……
# at 123
#200601 8:35:19 server id 1 end_log_pos 154 CRC32 0xd25b404e Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
……
|
mysqlbinlog常见的选项有以下几个:
--start-datetime:从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间
--stop-datetime:从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样
--start-position:从二进制日志中读取指定position 事件位置作为开始。
--stop-position:从二进制日志中读取指定position 事件位置作为事件截至
-d,--database=name:只查看指定数据库的日志操作
使用bin-log日志来恢复数据
现在模拟一种场景:一数据库每晚3点定时备份,第二天网站正常运行了半天,突然在下午5点钟的时候,程序员小A不小心,DELETE 时候没有加WHERE 条件,然后其中某张表数据全没了。然后小A找到技术总监大圣,让大圣帮忙恢复数据。
binlog_test 数据库只有一张user表
凌晨三点时没备份前的数据如下:
1 2 3 4 5 6 7 | +
| user_id | username | add_time |
+
| 1 | gwx | 2018-07-05 13:00:31 |
| 2 | snn | 2018-07-05 14:00:00 |
| 3 | zy | 2018-07-05 15:00:00 |
+
|
凌晨3点到了,备份数据
1 2 3 4 5 | mysqldump binlog_test -l -F > /root/sql_backup/20180706.sql
ll /root/sql_backup/
总用量 4
-rw-r
=======数据备份完成=========
|
网站正常运行一段时间,有许多用户注册
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | INSERT INTO ` user ` (username) values ( 'user1' ),( 'user2' ),( 'user3' );
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
select * from user ;
+
| user_id | username | add_time |
+
| 1 | gwx | 2018-07-05 13:00:31 |
| 2 | snn | 2018-07-05 14:00:00 |
| 3 | zy | 2018-07-05 15:00:00 |
| 4 | user1 | 2018-07-06 15:01:18 |
| 5 | user2 | 2018-07-06 15:01:18 |
| 6 | user3 | 2018-07-06 15:01:18 |
+
==============新增了3个用户user1 user2 及user3==============
|
到了下午5点钟,小A开始犯傻了
1 2 3 | DELETE FROM user ;
Query OK, 6 rows affected (0.00 sec)
=========没 where 条件,数据全没了===========
|
小A找到大圣帮忙恢复数据,大圣先把昨晚凌晨三点数据给恢复了
1 2 3 4 5 6 7 8 9 10 | service nginx stop; # 大圣先关闭了nginx,使网站用户暂时访问不了数据库
Stoping nginx... done
MariaDB [binlog_test]> flush logs; #生成新的binlog日志
MariaDB [binlog_test]> show master status;
+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+
| mysql-bin.000003 | 1536 | | |
+
mysql -v -f binlog_test < /root/sql_backup/20180706.sql
|
这时候大圣已经把昨晚凌晨3点时候数据恢复了
1 2 3 4 5 6 7 8 9 | MariaDB [binlog_test]> select * from user ;
+
| user_id | username | add_time |
+
| 1 | gwx | 2018-07-05 13:00:31 |
| 2 | snn | 2018-07-05 14:00:00 |
| 3 | zy | 2018-07-05 15:00:00 |
+
=============昨晚凌晨三点数据恢复完成===============
|
接下来恢复凌晨三点到DELETE间段数据
首先找到delete的pos点,备份之后log是000002 删除之后也flush logs为000003,所以只要找000002 delete之前的pos即可
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | # /usr/ local /mariadb/bin/mysqlbinlog
'mysql-bin.000002' >
| mysql binlog_test;
MariaDB [binlog_test]> select * from user ;
+
| user_id | username | add_time |
+
| 1 | gwx | 2018-07-05 13:00:31 |
| 2 | snn | 2018-07-05 14:00:00 |
| 3 | zy | 2018-07-05 15:00:00 |
| 4 | user1 | 2018-07-06 15:01:18 |
| 5 | user2 | 2018-07-06 15:01:18 |
| 6 | user3 | 2018-07-06 15:01:18 |
+
==============数据都回来了========================
|
以上就是MySQL运维之二进制日志的详细内容,更多文章请关注木庄网络博客!
相关阅读 >>
mysql自动记录慢查询日志实例
mysql日志文件在哪
详细解读mysql中的权限
分享一些mysql数据库的优化技巧
mysql如何查询navicat中的blob类型内容
mysql怎么使用delete
mysq详细讲解如何解决库存并发问题
mysql数据库超时设置配置的方法实例
mysql数据库增量备份的实现思路方法介绍
mysql 和 sql server 的区别?
更多相关阅读请进入《mysql》频道 >>
机械工业出版社
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。
转载请注明出处:木庄网络博客 » MySQL运维之二进制日志