Binlog日志,即binary log,是二进制日志文件,有两个作用,一个是增量备份,另一个是主从复制,即主节点维护一个binlog日志文件,从节点从binlog中同步数据,也可以通过binlog日志来恢复数据
1.登录mysql查看binlog日志的状态
show variables like '%log_bin%';
如果显示log_bin为OFF则代表未开启binlog日志,ON为开启了binlog日志
2.开启binlog日志
进入mysql配置文件(vi /etc/my.cnf)
在mysqld区域内添加如下内容
①server-id = 1(单个节点id)
②log-bin= /var/lib/mysql/mysql-bin(位置一般和mysql库文件所在位置一样)
③expire_logs_days = 10(表示此日志保存时间为10天)
重启mysqld,再次查看binlog日志开启状态为ON
3.Binlog日志包括两类文件
①是二进制索引文件(后缀名为.index)
②是日志文件(后缀名为.00000*),记录数据库所有的DDL和DML(除了查询语句select)语句事件
4.查看所有binlog日志文件列表
show master logs;
5.查看最后一个binlog日志的编号名称及其最后一个操作事件pos结束点的值
show master status;
6.刷新日志
flush logs;
此刻开始产生一个新编号的binlog文件,例如:
每当mysqld服务重启时,会自动执行刷新binlog日志命令,mysqldump备份数据时加-F选项也会刷新binlog日志
7.清空所有binlog日志
reset master;
8.查看binlog文件内容
使用查看工具mysqlbinlog来查看(cat/vi/more都是无法打开的)
不过使用mysqlbinlog打开的文件是原始存储文件,非常的杂乱,推荐使用下面这个命令来查看
9.使用show binlog events查看日志
例如:
show binlog events in 'mysql-bin.000020';
10.指定查询
从pos点486开始查询,如下
show binlog events in 'mysql-bin.000020' from 486;
11.利用binlog日志恢复mysql数据
例如,现有一张数据表如图
此表位于test数据库
现在将此数据库备份(模拟每周的备份情况),备份命令如下
mysqldump -u root -p test > /root/test.sql
可以在数据备份之前或者之后执行flush logs重新生成一个binlog日志用来记录备份之后的所有增删改操作(重新生成日志更好找pos点),由于业务需求,现在对表进行插入
由于操作失误,误删除了数据库,所有数据都不见了,此时可以通过binlog日志恢复数据,由于之前有做了数据库备份,所以可以先将备份的数据导入进去,剩下缺少的就是备份之后操作所产生的内容(备份之后执行了插入内容以及更改内容),先恢复备份的数据:创建库test并选择库(use test),通过命令source /root/test.sql将数据库内容导入,然后执行
恢复的内容如下所示
上面恢复的数据只是截止到备份时间的数据,剩下缺少的数据可以通过binlog日志来恢复,由于我们备份数据库之前重新创建了mysql-bin.000021日志,所以备份后的所有操作都保存在这个日志中,可以先备份下这个日志文件,cp mysql-bin.000021 /root/ 接着执行flush logs 刷新日志,重新创建了一个binlog日志mysql-bin.000022,这样的话我们接下来所有操作的数据都会写入到日志mysql-bin.000022中,日志mysql-bin.000021中不会在写入任何数据(方便根据日志mysql-bin.000021的内容恢复数据,因为日志mysql-bin.000021的数据就是备份之后到删库之前的所有操作日志,重建日志mysql-bin.000022不会有过多的数据影响恢复)
查看日志mysql-bin.000021
show binlog events in 'mysql-bin.000021';
查看日志发现,我们对数据的操作起始pos分别为120-857,执行命令如下
/usr/bin/mysqlbinlog --stop-position=857 --database=test /www/server/data/mysql-bin.000021 | /usr/bin/mysql -uroot -p密码 -v test
(其中整个命令的含义是通过mysqlbinlog读取日志内容并通过管道传给mysql命令,-v表示执行此mysql命令)执行后查询表发现如下:
数据都回来了