这篇文章给大家分享的是MySQL二进制日志的使用方法,使用二进制日志可以实现异地容灾备份、读写分离、数据恢复等功能,通过这篇文章的总结,希望你能学会使用MySQL二进制日志。
开启bin-log日志
Mysql默认是没有开启bin-log日志的,需要我们自己去添加配置。
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/下面
运维提示:二进制日志文件与数据库数据文件最好不要放在同一块硬盘上,如果存放数据文件的硬盘坏了,可以用另一块硬盘的二进制日志来恢复数据
几个有用的命令
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 #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*/; ……
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.sql ll /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: 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开始犯傻了
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间段数据
首先找到delete的pos点,备份之后log是000002 删除之后也flush logs为000003,所以只要找000002 delete之前的pos即可
# /usr/local/mariadb/bin/mysqlbinlog --stop-position=629 > '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二进制日志的使用方法了吗?如果还想学到更多技能或想了解更多相关内容,欢迎关注亿速云行业资讯频道,感谢各位的阅读!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。