MySQL binlog日志操作详解

发布时间:2017-7-1 11:40:40编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"MySQL binlog日志操作详解 ",主要涉及到MySQL binlog日志操作详解 方面的内容,对于MySQL binlog日志操作详解 感兴趣的同学可以参考一下。

       MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的。

binlog日志的作用

其一:MySQL Replication在Master端开启binlog,Mster把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
其二:自然就是数据恢复了,通过使用mysqlbinlog工具来使恢复数据。

      MySQL二进制日志包括两类文件:二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件,二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句)语句事件。

一、开启binlog日志

vim /etc/my.cnf[mysqld]log_bin=mysql-bin

重启mysqld服务使配置生效

/etc/init.d/mysqld restart

二、登录mysql服务器,通过mysql的变量配置表,查看二进制日志是否已开启.

mysql> show variables like 'log_%'; +----------------------------------------+--------------------------------+| Variable_name                          | Value                          |+----------------------------------------+--------------------------------+| log_bin                                | ON                             || log_bin_basename                       | /var/lib/mysql/mysql-bin       || log_bin_index                          | /var/lib/mysql/mysql-bin.index || log_bin_trust_function_creators        | OFF                            || log_bin_use_v1_row_events              | OFF                            || log_error                              | /var/log/mysqld.log            || log_output                             | FILE                           || log_queries_not_using_indexes          | OFF                            || log_slave_updates                      | OFF                            || log_slow_admin_statements              | OFF                            || log_slow_slave_statements              | OFF                            || log_throttle_queries_not_using_indexes | 0                              || log_warnings                           | 1                              |+----------------------------------------+--------------------------------+13 rows in set (0.00 sec)  

三、查看某个binlog日志内容,常用有两种方式:

1.使用mysqlbinlog自带查看命令法:

binlog是二进制文件,普通文件查看器cat more vim 等打开都是乱码,必须使用自带的 mysqlbinlog 命令查看.

[root@localhost mysql]# mysqlbinlog mysql-bin.000003

2.使用show binlog events 查看binlog日志.

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];选项解析:               IN 'log_name'   指定要查询的binlog文件名(不指定就是第一个binlog文件)               FROM pos        指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)               LIMIT [offset,] 偏移量(不指定就是0)               row_count       查询总条数(不指定就是所有行)

截取部分查询结果:

mysql> show binlog events in 'mysql-bin.000003'\G*************************** 1. row ***************************   Log_name: mysql-bin.000003                         ----> 查询的binlog日志文件名        Pos: 4			                      ----> pos起始点: Event_type: Format_desc                              ---->  事件类型  Server_id: 1                                        ----> 标识是由哪台服务器执行的End_log_pos: 120		                      ----> pos结束点:120(即:下行的pos起始点)       Info: use `juzidb`; INSERT INTO `tb1` VALUES (1,'UFO')   ---> 执行的sql语句             *************************** 2. row ***************************   Log_name: mysql-bin.000003        Pos: 542 Event_type: Query  Server_id: 1End_log_pos: 625       Info: BEGIN *************************** 3. row ***************************   Log_name: mysql-bin.000003        Pos: 946 Event_type: Query  Server_id: 1End_log_pos: 1044       Info: drop database juzidb

这条语句可以将指定的binlog日志文件,分成有效事件行的方式返回,并可使用limit指定pos点的起始偏移,查询条数;

A.查询第一个(即mysql-bin.000001)的binlog日志:

mysql> show binlog events\G

B.指定查询 mysql-bin.00002 这个文件:

mysql> show binlog events in 'mysql-bin.000002'\G

C.指定查询 mysql-bin.000002 这个文件,从pos点:534620开始查起:

mysql> show binlog events in 'mysql-bin.000002' from 534620\G

D.指定查询 mysql-bin.000002 这个文件,从pos点:534620开始查起,查询10条

mysql> show binlog events in 'mysql-bin.000002' from 534620 limit 10\G

E.指定查询 mysql-bin.000002 这个文件,从pos点:534620开始查起,偏移2行,查询10条

mysql> show binlog events in 'mysql-bin.000002' from 534620 limit 2,10\G

 四、 通过全备和binlog日志恢复数据实例:

1.假设现在是凌晨4:00,我的计划任务开始执行一次完整的数据库备份:

将juzidb数据库备份到 /tmp/bak_juzidb.sql 文件中:

[root@localhost tmp]# mysqldump -uroot -p123456 -lF --log-error=/tmp/myDump.err -B juzidb > /tmp/bak_juzidb.sql

......

大约过若干分钟,全备份就完成了,我不用担心数据丢失了,因为我有备份了,嘎嘎~~~

由于我使用了-F选项,当备份工作刚开始时系统会刷新log日志,产生新的binlog日志来记录备份之后的数据库“增删改”操作,查看一下:

mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000007 |      120 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

也就是说, mysql-bin.000007 是用来记录4:00之后对数据库的所有“增删改”操作

2.早9:30上班了,业务的需求会对数据库进行各种“增删改”操作~~~~~~~

比如:创建一个tb1表并插入、修改了数据等等:

mysql>mysql> use juzidb;mysql>CREATE TABLE IF NOT EXISTS `tb1` (      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,      `name` varchar(16) NOT NULL,      PRIMARY KEY (`id`)    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

导入数据:

mysql> insert into juzidb.tb1(name) values('DBA'),('CBA'),('NBA'),('UFO');

查看数据

mysql> select * from tb1;+----+------+| id | name |+----+------+|  1 | DBA  ||  2 | CBA  ||  3 | NBA  ||  4 | UFO  |+----+------+4 rows in set (0.00 sec)

中午时分又执行了修改数据操作

mysql> mysql> update juzidb.tb1 set name='BBA' where id=4;

修改后的结果:

mysql> select * from tb1;+----+------+| id | name |+----+------+|  1 | DBA  ||  2 | CBA  ||  3 | NBA  ||  4 | BBA  |+----+------+4 rows in set (0.00 sec)

假设此时是下午,脑子一热莫名地执行了一条悲催的SQL语句,整个数据库都没了:

mysql> drop database juzidb;

然后,就没然后了,凌乱一会,开始恢复吧!!

先仔细查看最后一个binlog日志,并记录下关键的pos点,到底是哪个pos点的操作导致了数据库的破坏(通常在最后几步);

备份一下最后一个binlog日志文件:

cp -v /var/lib/mysql/mysql-bin.000007 /tmp/

此时执行一次刷新日志索引操作,重新开始新的binlog日志记录文件,理论说 mysql-bin.000007 这个文件不会再有后续写入了(便于我们分析原因及查找pos点),以后所有数据库操作都会写入到下一个日志文件;

mysql> flush logs;Query OK, 0 rows affected (0.08 sec)mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000008 |      120 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

3.读取binlog日志,分析问题

方式一:使用mysqlbinlog读取binlog日志:/usr/bin/mysqlbinlog /var/lib/mysql/mysql-bin.000007方式二:登录服务器,并查看(推荐):以下为末尾片段:mysql> show binlog events in 'mysql-bin.000007'\G*************************** 1. row ***************************   Log_name: mysql-bin.000007        Pos: 4 Event_type: Format_desc  Server_id: 1End_log_pos: 120       Info: Server ver: 5.6.36-log, Binlog ver: 4*************************** 2. row ***************************   Log_name: mysql-bin.000007        Pos: 120 Event_type: Query  Server_id: 1End_log_pos: 386       Info: use `juzidb`; CREATE TABLE IF NOT EXISTS `tb1` (      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,      `name` varchar(16) NOT NULL,      PRIMARY KEY (`id`)    ) ENGINE=InnoDB DEFAULT CHARSET=utf8*************************** 3. row ***************************   Log_name: mysql-bin.000007        Pos: 386 Event_type: Query  Server_id: 1End_log_pos: 469       Info: BEGIN*************************** 4. row ***************************   Log_name: mysql-bin.000007        Pos: 469 Event_type: Intvar  Server_id: 1End_log_pos: 501       Info: INSERT_ID=1*************************** 5. row ***************************   Log_name: mysql-bin.000007        Pos: 501 Event_type: Query  Server_id: 1End_log_pos: 645       Info: use `juzidb`; insert into juzidb.tb1(name) values('DBA'),('CBA'),('NBA'),('UFO')*************************** 6. row ***************************   Log_name: mysql-bin.000007        Pos: 645 Event_type: Xid  Server_id: 1End_log_pos: 676       Info: COMMIT /* xid=571661 */*************************** 7. row ***************************   Log_name: mysql-bin.000007        Pos: 676 Event_type: Query  Server_id: 1End_log_pos: 759       Info: BEGIN*************************** 8. row ***************************   Log_name: mysql-bin.000007        Pos: 759 Event_type: Query  Server_id: 1End_log_pos: 880       Info: use `juzidb`; update juzidb.tb1 set name='BBA' where id=4*************************** 9. row ***************************   Log_name: mysql-bin.000007        Pos: 880 Event_type: Xid  Server_id: 1End_log_pos: 911       Info: COMMIT /* xid=571663 */*************************** 10. row ***************************   Log_name: mysql-bin.000007        Pos: 911 Event_type: Query  Server_id: 1End_log_pos: 1009       Info: drop database juzidb*************************** 11. row ***************************   Log_name: mysql-bin.000007        Pos: 1009 Event_type: Rotate  Server_id: 1End_log_pos: 1056       Info: mysql-bin.000008;pos=411 rows in set (0.00 sec)

通过分析,造成数据库破坏的pos点区间是介于 911--1009 之间,只要恢复到1009前就可。

4.现在先把凌晨备份的数据恢复:

mysql -uroot -p123456 -v < /tmp/bak_juzidb.sql

注: 至此截至当日凌晨(4:00)前的备份数据都恢复了。
但今天一整天(4:00--现在)的数据肿么办呢?就得从前文提到的 mysql-bin.000007 新日志做文章了......

5.从binlog日志恢复数据

恢复语法格式:# mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名常用选项:        --start-position=953                    起始pos点        --stop-position=1437                    结束pos点        --start-datetime="2017-6-20 13:18:54"   起始时间点        --stop-datetime="2017-6-20 13:21:53"    结束时间点        --database=juzidb                       指定只恢复juzidb数据库(一台主机上往往有多个数据库,只限本地log日志)            不常用选项:            -u --user=name              Connect to the remote server as username.连接到远程主机的用户名        -p --password[=name]        Password to connect to remote server.连接到远程主机的密码        -h --host=name              Get the binlog from server.从远程主机上获取binlog日志        --read-from-remote-server   Read binary logs from a MySQL server.从某个MySQL服务器上读取binlog日志

小结:实际是将读出的binlog日志内容,通过管道符传递给mysql命令。这些命令、文件尽量写成绝对路径;

方法1:通过binlog日志mysql-bin.000007直接恢复.
使用-d参数表示从binlog中只导出juzidb库的数据.

mysqlbinlog mysql-bin.000007 -d juzidb > mysql-bin.sql然后vim mysql-bin.sql文件,找到误操作的mysql语句,一定要删除.我们这里误操作的sql为drop database juzidb,找到并删除即可.然后倒入/tmp/mysql-bin.sql文件.mysql -uroot -p123456 -v < /tmp/mysql-bin.sql

方法2:指定pos点恢复数据(部分恢复):

A.指定pos结束点恢复某段时间的数据

@ --stop-position=759 pos结束点  注:此pos结束点介于“导入实验数据”与更新 name='UFO'之间,这样可以恢复到更改name='BBA'之前的“导入测试数据”mysqlbinlog --stop-position=759 --database=juzidb mysql-bin.000007 | mysql -uroot -p123456 -v juzidb 

在另一终端登录查看结果(成功恢复了):

mysql> select * from tb1;+----+------+| id | name |+----+------+|  1 | DBA  ||  2 | CBA  ||  3 | NBA  ||  4 | UFO  |+----+------+4 rows in set (0.00 sec)

B.指定Pos点区间恢复(部分恢复):

更新name='BBA'这条数据, Info: use `juzidb`; update juzidb.tb1 set name='BBA' where id=4mysqlbinlog --start-position=759 --stop-position=880 --database=juzidb mysql-bin.000007 | mysql -uroot -p123456 -v juzidb 

在另一终端登录查看结果(成功恢复了):

mysql> select * from tb1;+----+------+| id | name |+----+------+|  1 | DBA  ||  2 | CBA  ||  3 | NBA  ||  4 | BBA  |+----+------+4 rows in set (0.00 sec)

事务区间:INFO信息为两个BEGIN 之间的为按事务区间分 如:Pos: 469 End_log_pos: 759
日志区间:日志区间为Pos: 759 到 End_log_pos: 880

除了用Pos点的办法进行数据恢复,也可以通过指定时间区间进行恢复,按时间恢复需要用mysqlbinlog命令读取binlog日志内容,找时间节点。

例如:
@ --start-datetime="2017-6-20 13:18:54" 起始时间点
@ --stop-datetime="2013-16-20 14:21:53" 结束时间点

通过起始时间段则可以这样恢复数据

mysqlbinlog --start-datetime="2017-6-20 13:18:54" --stop-datetime="2013-16-20 14:21:53" --database=juzidb mysql-bin.000007 | mysql -uroot -p123456 -v juzidb

总结:所谓恢复,就是让MySQL将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已。

 五、常用binlog日志操作命令

1.查看所有binlog日志列表

mysql> show master logs;

2.查看master状态,即最后(最新)一个binlog日志的编号名称,及其最后一个操作事件pos结束点(Position)值

mysql> show master status;

 3.刷新log日志,自此刻开始产生一个新编号的binlog日志文件

mysql> flush logs;      注:每当mysqld服务重启时,会自动执行此命令,刷新binlog日志;在mysqldump备份数据时加 -F 选项也会刷新binlog日志;

4.binlog日志的删除

binlog的删除可以手工删除或自动删除

自动删除binlog

通过binlog参数(expire_logs_days )来实现mysql自动删除binlog

mysql> show master logs;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000001 |       167 || mysql-bin.000002 | 531688179 || mysql-bin.000003 |      1091 || mysql-bin.000004 | 360363501 || mysql-bin.000005 |      1798 || mysql-bin.000006 |       577 || mysql-bin.000007 |      1056 || mysql-bin.000008 | 720729027 || mysql-bin.000009 |      3010 |+------------------+-----------+9 rows in set (0.00 sec)mysql> show variables like 'expire_logs_days';+------------------+-------+| Variable_name    | Value |+------------------+-------+| expire_logs_days | 0     |+------------------+-------+1 row in set (0.00 sec)mysql> set global expire_logs_days=7;Query OK, 0 rows affected (0.00 sec)mysql> 

手工删除binlog

mysql> reset master;   	//清空所有的binlog日志mysql> reset slave;    	//清空slave的中继日志mysql> purge master logs before '2017-03-30 00:20:00';    //删除指定日期以前的日志索引中binlog日志文件mysql> purge master logs to 'mysql-bin.000009';           //删除指定日志文件的日志索引之前的日志文件或者直接用操作系统命令直接删除

5.显示当前注册的slave的列表。不以--report-host=slave_name选项为开头的slave不会显示在本列表中

mysql> show slave hosts;

  

 

参考文档: http://www.cnblogs.com/martinzhang/p/3454358.html


上一篇:.net core web api swagger 配置笔记
下一篇:技术架构组工作职责

相关文章

相关评论

本站评论功能暂时取消,后续此功能例行通知。

一、不得利用本站危害国家安全、泄露国家秘密,不得侵犯国家社会集体的和公民的合法权益,不得利用本站制作、复制和传播不法有害信息!

二、互相尊重,对自己的言论和行为负责。

好贷网好贷款