mysql 二进制日志变化_MySQL运维之二进制日志

MySQL二进制日志里保存会造成或可能造成数据变化的SQL语句。通过二进制日志可以完成如实时异地容灾备份、读写分离、数据恢复等功能。下面，我们一起来看看Mysql二进制日志。

开启bin-log日志

Mysql默认是没有开启bin-log日志的，需要我们自己去添加配置。log-bin=mysql-binbinlog_format=mixedserver-id= 1expire_logs_days = 10log-bin 配置了该项后，即表示开启二进制日志功能。mysql-bin是bin-log日志文件名。

expire_logs_days = 10 表明只存放最近10天的bin-log日志。

一般bin-log日志存放在 mysql安装路径/var/下面

运维提示：二进制日志文件与数据库数据文件最好不要放在同一块硬盘上，如果存放数据文件的硬盘坏了，可以用另一块硬盘的二进制日志来恢复数据

几个有用的命令

flush logs:生成新的bin-log日志

show master status：查看最后一个bin-log日志状态。

reset master:清空所有bin-log文件

mysql > show master status

Mysql日志的查看

因为该日志是二进制日志，所以用一般的命令cat或vim查看会是一推乱码。Mysql为我们提供了工具mysqlbinlog。用它就可以来查看了。./mysqlbinlog ../var/mysql-bin.000015……# at 123#2006018:35:19 server id 1end_log_pos 154 CRC32 0xd25b404ePrevious-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*/;……at:sql开始时pos节点

server_id：数据库主机的服务号；

end_log_pos 154：sql结束时的pos节点

mysqlbinlog常见的选项有以下几个：

--start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间

--stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样

--start-position：从二进制日志中读取指定position 事件位置作为开始。

--stop-position：从二进制日志中读取指定position 事件位置作为事件截至

-d,--database=name：只查看指定数据库的日志操作

使用bin-log日志来恢复数据

导出sql文件命令：mysqldump 数据库名字 [数据表名字1[ 数据表名字2...]] > 外部文件目录(建议使用.sql)

sql文件导入数据库：mysql -u** -p**数据库名字 < 备份文件目录

现在模拟一种场景：一数据库每晚3点定时备份,第二天网站正常运行了半天，突然在下午5点钟的时候，程序员小A不小心，DELETE 时候没有加WHERE 条件，然后其中某张表数据全没了。然后小A找到技术总监大圣，让大圣帮忙恢复数据。

binlog_test 数据库只有一张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 |+---------+----------+---------------------+凌晨3点到了，备份数据mysqldump binlog_test -l -F > /root/sql_backup/20180706.sqlll /root/sql_backup/总用量 4-rw-r--r-- 1 root root 2149 7月6 13:42 20180706.sql=======数据备份完成=========网站正常运行一段时间，有许多用户注册INSERT INTO `user` (username) values('user1'),('user2'),('user3');Query OK, 3 rows affected (0.01 sec)Records: 3Duplicates: 0Warnings: 0select * 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开始犯傻了DELETE FROM user;Query OK, 6 rows affected (0.00 sec)=========没where条件，数据全没了===========小A找到大圣帮忙恢复数据,大圣先把昨晚凌晨三点数据给恢复了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点时候数据恢复了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间段数据