众所周知&＃xff0c;binlog日志对于mysql数据库来说是十分重要的。在数据丢失的紧急情况下&＃xff0c;我们往往会想到用binlog日志功能进行数据恢复(定时全备份&＃43;binlog日志恢复增量数据部分)&＃xff0c;化险为夷&＃xff01;
废话不多说&＃xff0c;下面是梳理的binlog日志操作解说&＃xff1a;
一、初步了解binlog
MySQL的二进制日志binlog可以说是MySQL最重要的日志&＃xff0c;它记录了所有的DDL和DML语句(除了数据查询语句select)&＃xff0c;以事件形式记录&＃xff0c;还包含语句所执行的消耗的时间&＃xff0c;MySQL的二进制日志是事务安全型的。
----------------------------------------------------------------------------------------------------------------------------------------------
DDL
----Data Definition Language 数据库定义语言
主要的命令有CREATE、ALTER、DROP等&＃xff0c;DDL主要是用在定义或改变表(TABLE)的结构&＃xff0c;数据类型&＃xff0c;表之间的链接和约束等初始化工作上&＃xff0c;他们大多在建立表时使用。
DML
----Data Manipulation Language 数据操纵语言
主要的命令是SELECT、UPDATE、INSERT、DELETE&＃xff0c;就象它的名字一样&＃xff0c;这4条命令是用来对数据库里的数据进行操作的语言
----------------------------------------------------------------------------------------------------------------------------------------------
mysqlbinlog常见的选项有以下几个&＃xff1a;
--start-datetime&＃xff1a;从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间
--stop-datetime&＃xff1a;从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样
--start-position&＃xff1a;从二进制日志中读取指定position 事件位置作为开始。
--stop-position&＃xff1a;从二进制日志中读取指定position 事件位置作为事件截至
*********************************************************************
一般来说开启binlog日志大概会有1%的性能损耗。
binlog日志有两个最重要的使用场景:
1)MySQL主从复制&＃xff1a;MySQL Replication在Master端开启binlog&＃xff0c;Master把它的二进制日志传递给slaves来达到
master-slave数据一致的目的。
2)自然就是数据恢复了&＃xff0c;通过使用mysqlbinlog工具来使恢复数据。
binlog日志包括两类文件&＃xff1a;
1)二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件
2)二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句select)语句事件。
二、开启binlog日志&＃xff1a;
1)编辑打开mysql配置文件/etc/mys.cnf
[root&＃64;vm-002 ~]# vim /etc/my.cnf
在[mysqld] 区块添加
log-bin&＃61;mysql-bin 确认是打开状态(mysql-bin 是日志的基本名或前缀名)&＃xff1b;
2)重启mysqld服务使配置生效
[root&＃64;vm-002 ~]# /etc/init.d/mysqld stop
[root&＃64;vm-002 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
3)查看binlog日志是否开启
mysql> show variables like &＃39;log_%&＃39;;
&＃43;---------------------------------&＃43;---------------------&＃43;
| Variable_name | Value |
&＃43;---------------------------------&＃43;---------------------&＃43;
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_bin_trust_routine_creators | OFF |
| log_error | /var/log/mysqld.log |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
&＃43;---------------------------------&＃43;---------------------&＃43;
9 rows in set (0.00 sec)
三、常用的binlog日志操作命令
1)查看所有binlog日志列表
mysql> show master logs;
&＃43;------------------&＃43;-----------&＃43;
| Log_name | File_size |
&＃43;------------------&＃43;-----------&＃43;
| mysql-bin.000001 | 149 |
| mysql-bin.000002 | 4102 |
&＃43;------------------&＃43;-----------&＃43;
2 rows in set (0.00 sec)
2)查看master状态&＃xff0c;即最后(最新)一个binlog日志的编号名称&＃xff0c;及其最后一个操作事件pos结束点(Position)值
mysql> show master status;
&＃43;------------------&＃43;----------&＃43;--------------&＃43;------------------&＃43;
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
&＃43;------------------&＃43;----------&＃43;--------------&＃43;------------------&＃43;
| mysql-bin.000002 | 4102 | | |
&＃43;------------------&＃43;----------&＃43;--------------&＃43;------------------&＃43;
1 row in set (0.00 sec)
3)flush刷新log日志&＃xff0c;自此刻开始产生一个新编号的binlog日志文件
mysql> flush logs;
Query OK, 0 rows affected (0.13 sec)
mysql> show master logs;
&＃43;------------------&＃43;-----------&＃43;
| Log_name | File_size |
&＃43;------------------&＃43;-----------&＃43;
| mysql-bin.000001 | 149 |
| mysql-bin.000002 | 4145 |
| mysql-bin.000003 | 106 |
&＃43;------------------&＃43;-----------&＃43;
3 rows in set (0.00 sec)
注意&＃xff1a;
每当mysqld服务重启时&＃xff0c;会自动执行此命令&＃xff0c;刷新binlog日志&＃xff1b;在mysqldump备份数据时加 -F 选项也会刷新binlog日志&＃xff1b;
4)重置(清空)所有binlog日志
mysql> reset master;
Query OK, 0 rows affected (0.12 sec)
mysql> show master logs;
&＃43;------------------&＃43;-----------&＃43;
| Log_name | File_size |
&＃43;------------------&＃43;-----------&＃43;
| mysql-bin.000001 | 106 |
&＃43;------------------&＃43;-----------&＃43;
1 row in set (0.00 sec)
四、查看binlog日志内容&＃xff0c;常用有两种方式&＃xff1a;
1)使用mysqlbinlog自带查看命令法&＃xff1a;
注意&＃xff1a;
-->binlog是二进制文件&＃xff0c;普通文件查看器cat、more、vim等都无法打开&＃xff0c;必须使用自带的mysqlbinlog命令查看
-->binlog日志与数据库文件在同目录中
-->在MySQL5.5以下版本使用mysqlbinlog命令时如果报错&＃xff0c;就加上 “--no-defaults”选项
查看mysql的数据存放目录&＃xff0c;从下面结果可知是/var/lib//mysql
[root&＃64;vm-002 ~]# ps -ef|grep mysql
root 9791 1 0 21:18 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir&＃61;/var/lib/mysql --socket&＃61;/var/lib/mysql/mysql.sock --pid-file&＃61;/var/run/mysqld/mysqld.pid --basedir&＃61;/usr --user&＃61;mysql
mysql 9896 9791 0 21:18 pts/0 00:00:00 /usr/libexec/mysqld --basedir&＃61;/usr --datadir&＃61;/var/lib/mysql --user&＃61;mysql --log-error&＃61;/var/log/mysqld.log --pid-file&＃61;/var/run/mysqld/mysqld.pid --socket&＃61;/var/lib/mysql/mysql.sock
root 9916 9699 0 21:18 pts/0 00:00:00 mysql -px xxxx
root 9919 9715 0 21:23 pts/1 00:00:00 grep --color mysql
[root&＃64;vm-002 ~]# cd /var/lib/mysql/
[root&＃64;vm-002 mysql]# ls
ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.index mysql.sock ops test
使用mysqlbinlog命令查看binlog日志内容&＃xff0c;下面截取其中的一个片段分析&＃xff1a;
[root&＃64;vm-002 mysql]# mysqlbinlog mysql-bin.000002
..............
# at 624
#160925 21:29:53 server id 1 end_log_pos 796 Querythread_id&＃61;3exec_time&＃61;0error_code&＃61;0
SET TIMESTAMP&＃61;1474810193/*!*/;
insert into member(&＃96;name&＃96;,&＃96;sex&＃96;,&＃96;age&＃96;,&＃96;classid&＃96;) values(&＃39;wangshibo&＃39;,&＃39;m&＃39;,27,&＃39;cls1&＃39;),(&＃39;guohuihui&＃39;,&＃39;w&＃39;,27,&＃39;cls2&＃39;) #执行的sql语句
/*!*/;
# at 796
#160925 21:29:53 server id 1 end_log_pos 823 Xid &＃61; 17 #执行的时间
.............
解释&＃xff1a;
server id 1 &＃xff1a; 数据库主机的服务号&＃xff1b;
end_log_pos 796&＃xff1a; sql结束时的pos节点
thread_id&＃61;11&＃xff1a; 线程号
2)上面这种办法读取出binlog日志的全文内容比较多&＃xff0c;不容易分辨查看到pos点信息
下面介绍一种更为方便的查询命令&＃xff1a;
命令格式&＃xff1a;
mysql> show binlog events [IN &＃39;log_name&＃39;] [FROM pos] [LIMIT [offset,] row_count];
参数解释&＃xff1a;
IN &＃39;log_name&＃39; &＃xff1a;指定要查询的binlog文件名(不指定就是第一个binlog文件)
FROM pos &＃xff1a;指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
LIMIT [offset,] &＃xff1a;偏移量(不指定就是0)
row_count &＃xff1a;查询总条数(不指定就是所有行)
mysql> show master logs;
&＃43;------------------&＃43;-----------&＃43;
| Log_name | File_size |
&＃43;------------------&＃43;-----------&＃43;
| mysql-bin.000001 | 125 |
| mysql-bin.000002 | 823 |
&＃43;------------------&＃43;-----------&＃43;
2 rows in set (0.00 sec)
mysql> show binlog events in &＃39;mysql-bin.000002&＃39;\G;
*************************** 1. row ***************************
Log_name: mysql-bin.000002
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 106
Info: Server ver: 5.1.73-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin.000002
Pos: 106
Event_type: Query
Server_id: 1
End_log_pos: 188
Info: use &＃96;ops&＃96;; drop table customers
*************************** 3. row ***************************
Log_name: mysql-bin.000002
Pos: 188
Event_type: Query
Server_id: 1
End_log_pos: 529
Info: use &＃96;ops&＃96;; CREATE TABLE IF NOT EXISTS &＃96;member&＃96; (
&＃96;id&＃96; int(10) unsigned NOT NULL AUTO_INCREMENT,
&＃96;name&＃96; varchar(16) NOT NULL,
&＃96;sex&＃96; enum(&＃39;m&＃39;,&＃39;w&＃39;) NOT NULL DEFAULT &＃39;m&＃39;,
&＃96;age&＃96; tinyint(3) unsigned NOT NULL,
&＃96;classid&＃96; char(6) DEFAULT NULL,
PRIMARY KEY (&＃96;id&＃96;)
) ENGINE&＃61;InnoDB DEFAULT CHARSET&＃61;utf8
*************************** 4. row ***************************
Log_name: mysql-bin.000002
Pos: 529
Event_type: Query
Server_id: 1
End_log_pos: 596
Info: BEGIN
*************************** 5. row ***************************
Log_name: mysql-bin.000002
Pos: 596
Event_type: Intvar
Server_id: 1
End_log_pos: 624
Info: INSERT_ID&＃61;1
*************************** 6. row ***************************
Log_name: mysql-bin.000002
Pos: 624
Event_type: Query
Server_id: 1
End_log_pos: 796
Info: use &＃96;ops&＃96;; insert into member(&＃96;name&＃96;,&＃96;sex&＃96;,&＃96;age&＃96;,&＃96;classid&＃96;) values(&＃39;wangshibo&＃39;,&＃39;m&＃39;,27,&＃39;cls1&＃39;),(&＃39;guohuihui&＃39;,&＃39;w&＃39;,27,&＃39;cls2&＃39;)
*************************** 7. row ***************************
Log_name: mysql-bin.000002
Pos: 796
Event_type: Xid
Server_id: 1
End_log_pos: 823
Info: COMMIT /* xid&＃61;17 */
7 rows in set (0.00 sec)
ERROR:
No query specified
mysql>
上面这条语句可以将指定的binlog日志文件&＃xff0c;分成有效事件行的方式返回&＃xff0c;并可使用limit指定pos点的起始偏移&＃xff0c;查询条数&＃xff01;
如下操作示例&＃xff1a;
a)查询第一个(最早)的binlog日志&＃xff1a;
mysql> show binlog events\G;
b)指定查询 mysql-bin.000002这个文件&＃xff1a;
mysql> show binlog events in &＃39;mysql-bin.000002&＃39;\G;
c)指定查询 mysql-bin.000002这个文件&＃xff0c;从pos点:624开始查起&＃xff1a;
mysql> show binlog events in &＃39;mysql-bin.000002&＃39; from 624\G;
d)指定查询 mysql-bin.000002这个文件&＃xff0c;从pos点:624开始查起&＃xff0c;查询10条(即10条语句)
mysql> show binlog events in &＃39;mysql-bin.000002&＃39; from 624 limit 10\G;
e)指定查询 mysql-bin.000002这个文件&＃xff0c;从pos点:624开始查起&＃xff0c;偏移2行(即中间跳过2个)&＃xff0c;查询10条
mysql> show binlog events in &＃39;mysql-bin.000002&＃39; from 624 limit 2,10\G;
五、利用binlog日志恢复mysql数据
以下对ops库的member表进行操作
mysql> use ops&＃xff1b;
mysql> CREATE TABLE IF NOT EXISTS &＃96;member&＃96; (
-> &＃96;id&＃96; int(10) unsigned NOT NULL AUTO_INCREMENT,
-> &＃96;name&＃96; varchar(16) NOT NULL,
-> &＃96;sex&＃96; enum(&＃39;m&＃39;,&＃39;w&＃39;) NOT NULL DEFAULT &＃39;m&＃39;,
-> &＃96;age&＃96; tinyint(3) unsigned NOT NULL,
-> &＃96;classid&＃96; char(6) DEFAULT NULL,
-> PRIMARY KEY (&＃96;id&＃96;)
-> ) ENGINE&＃61;InnoDB DEFAULT CHARSET&＃61;utf8;
Query OK, 0 rows affected (0.10 sec)
mysql> show tables;
&＃43;---------------&＃43;
| Tables_in_ops |
&＃43;---------------&＃43;
| member |
&＃43;---------------&＃43;
1 row in set (0.00 sec)
mysql> desc member;
&＃43;---------&＃43;---------------------&＃43;------&＃43;-----&＃43;---------&＃43;----------------&＃43;
| Field | Type | Null | Key | Default | Extra |
&＃43;---------&＃43;---------------------&＃43;------&＃43;-----&＃43;---------&＃43;----------------&＃43;
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(16) | NO | | NULL | |
| sex | enum(&＃39;m&＃39;,&＃39;w&＃39;) | NO | | m | |
| age | tinyint(3) unsigned | NO | | NULL | |
| classid | char(6) | YES | | NULL | |
&＃43;---------&＃43;---------------------&＃43;------&＃43;-----&＃43;---------&＃43;----------------&＃43;
5 rows in set (0.00 sec)
事先插入两条数据
mysql> insert into member(&＃96;name&＃96;,&＃96;sex&＃96;,&＃96;age&＃96;,&＃96;classid&＃96;) values(&＃39;wangshibo&＃39;,&＃39;m&＃39;,27,&＃39;cls1&＃39;),(&＃39;guohuihui&＃39;,&＃39;w&＃39;,27,&＃39;cls2&＃39;);
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from member;
&＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43;
| id | name | sex | age | classid |
&＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43;
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
&＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43;
2 rows in set (0.00 sec)
下面开始进行场景模拟&＃xff1a;
1)
ops库会在每天凌晨4点进行一次完全备份的定时计划任务&＃xff0c;如下&＃xff1a;
[root&＃64;vm-002 ~]# crontab -l
0 4 * * * /usr/bin/mysqldump -uroot -p -B -F -R -x --master-data&＃61;2 ops|gzip >/opt/backup/ops_$(date &＃43;%F).sql.gz
这里手动执行下&＃xff0c;将ops数据库备份到/opt/backup/ops_$(date &＃43;%F).sql.gz文件中&＃xff1a;
[root&＃64;vm-002 ~]# mysqldump -uroot -p -B -F -R -x --master-data&＃61;2 ops|gzip >/opt/backup/ops_$(date &＃43;%F).sql.gz
Enter password:
[root&＃64;vm-002 ~]# ls /opt/backup/
ops_2016-09-25.sql.gz
-----------------
参数说明&＃xff1a;
-B&＃xff1a;指定数据库
-F&＃xff1a;刷新日志
-R&＃xff1a;备份存储过程等
-x&＃xff1a;锁表
--master-data&＃xff1a;在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息
-----------------
待到数据库备份完成&＃xff0c;就不用担心数据丢失了&＃xff0c;因为有完全备份数据在&＃xff01;&＃xff01;
由于上面在全备份的时候使用了-F选项&＃xff0c;那么当数据备份操作刚开始的时候系统就会自动刷新log&＃xff0c;这样就会自动产生
一个新的binlog日志&＃xff0c;这个新的binlog日志就会用来记录备份之后的数据库“增删改”操作
查看一下&＃xff1a;
mysql> show master status;
&＃43;------------------&＃43;----------&＃43;--------------&＃43;------------------&＃43;
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
&＃43;------------------&＃43;----------&＃43;--------------&＃43;------------------&＃43;
| mysql-bin.000003 | 106 | | |
&＃43;------------------&＃43;----------&＃43;--------------&＃43;------------------&＃43;
1 row in set (0.00 sec)
也就是说&＃xff0c; mysql-bin.000003 是用来记录4:00之后对数据库的所有“增删改”操作。
2)
早上9点上班了&＃xff0c;由于业务的需求会对数据库进行各种“增删改”操作。
比如&＃xff1a;在ops库下member表内插入、修改了数据等等&＃xff1a;
先是早上进行插入数据&＃xff1a;
mysql> insert into ops.member(&＃96;name&＃96;,&＃96;sex&＃96;,&＃96;age&＃96;,&＃96;classid&＃96;) values(&＃39;yiyi&＃39;,&＃39;w&＃39;,20,&＃39;cls1&＃39;),(&＃39;xiaoer&＃39;,&＃39;m&＃39;,22,&＃39;cls3&＃39;),(&＃39;zhangsan&＃39;,&＃39;w&＃39;,21,&＃39;cls5&＃39;),(&＃39;lisi&＃39;,&＃39;m&＃39;,20,&＃39;cls4&＃39;),(&＃39;wangwu&＃39;,&＃39;w&＃39;,26,&＃39;cls6&＃39;);
Query OK, 5 rows affected (0.08 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from member;
&＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43;
| id | name | sex | age | classid |
&＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43;
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | xiaoer | m | 22 | cls3 |
| 5 | zhangsan | w | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
&＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43;
7 rows in set (0.00 sec)
3)
中午又执行了修改数据操作&＃xff1a;
mysql> update ops.member set name&＃61;&＃39;李四&＃39; where id&＃61;4;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update ops.member set name&＃61;&＃39;小二&＃39; where id&＃61;2;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from member;
&＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43;
| id | name | sex | age | classid |
&＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43;
| 1 | wangshibo | m | 27 | cls1 |
| 2 | 小二 | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | 李四 | m | 22 | cls3 |
| 5 | zhangsan | w | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
&＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43;
7 rows in set (0.00 sec)
4)
在下午18:00的时候&＃xff0c;悲剧莫名其妙的出现了&＃xff01;
手贱执行了drop语句&＃xff0c;直接删除了ops库&＃xff01;吓尿&＃xff01;
mysql> drop database ops;
Query OK, 1 row affected (0.02 sec)
5)
这种时候&＃xff0c;一定不要慌张&＃xff01;&＃xff01;&＃xff01;
先仔细查看最后一个binlog日志&＃xff0c;并记录下关键的pos点&＃xff0c;到底是哪个pos点的操作导致了数据库的破坏(通常在最后几步)&＃xff1b;
先备份一下最后一个binlog日志文件&＃xff1a;
[root&＃64;vm-002 ~]# cd /var/lib/mysql/
[root&＃64;vm-002 mysql]# cp -v mysql-bin.000003 /opt/backup/
&＃96;mysql-bin.000003&＃39; -> &＃96;/opt/backup/mysql-bin.000003&＃39;
[root&＃64;vm-002 mysql]# ls /opt/backup/
mysql-bin.000003 ops_2016-09-25.sql.gz
接着执行一次刷新日志索引操作&＃xff0c;重新开始新的binlog日志记录文件。按理说mysql-bin.000003
这个文件不会再有后续写入了&＃xff0c;因为便于我们分析原因及查找ops节点&＃xff0c;以后所有数据库操作都会写入到下一个日志文件。
mysql> flush logs;
Query OK, 0 rows affected (0.13 sec)
mysql> show master status;
&＃43;------------------&＃43;----------&＃43;--------------&＃43;------------------&＃43;
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
&＃43;------------------&＃43;----------&＃43;--------------&＃43;------------------&＃43;
| mysql-bin.000004 | 106 | | |
&＃43;------------------&＃43;----------&＃43;--------------&＃43;------------------&＃43;
1 row in set (0.00 sec)
6)
读取binlog日志&＃xff0c;分析问题。
读取binlog日志的方法上面已经说到。
方法一&＃xff1a;使用mysqlbinlog读取binlog日志&＃xff1a;
[root&＃64;vm-002 ~]# cd /var/lib/mysql/
[root&＃64;vm-002 mysql]# mysqlbinlog mysql-bin.000003
方法二&＃xff1a;登录服务器&＃xff0c;并查看(推荐此种方法)
mysql> show binlog events in &＃39;mysql-bin.000003&＃39;;
&＃43;------------------&＃43;-----&＃43;-------------&＃43;-----------&＃43;-------------&＃43;----------------------------------------------------------------------------------------------------------------------------&＃43;
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
&＃43;------------------&＃43;-----&＃43;-------------&＃43;-----------&＃43;-------------&＃43;----------------------------------------------------------------------------------------------------------------------------&＃43;
| mysql-bin.000003 | 4 | Format_desc | 1 | 106 | Server ver: 5.1.73-log, Binlog ver: 4 |
| mysql-bin.000003 | 106 | Query | 1 | 173 | BEGIN |
| mysql-bin.000003 | 173 | Intvar | 1 | 201 | INSERT_ID&＃61;3 |
| mysql-bin.000003 | 201 | Query | 1 | 444 | use &＃96;ops&＃96;; insert into ops.member(&＃96;name&＃96;,&＃96;sex&＃96;,&＃96;age&＃96;,&＃96;gsan&＃39;,&＃39;w&＃39;,21,&＃39;cls5&＃39;),(&＃39;lisi&＃39;,&＃39;m&＃39;,20,&＃39;cls4&＃39;),(&＃39;wangwu&＃39;,&＃39;w&＃39;,26,&＃39;cls6&＃39;) |
| mysql-bin.000003 | 444 | Xid | 1 | 471 | COMMIT /* xid&＃61;66 */ |
| mysql-bin.000003 | 471 | Query | 1 | 538 | BEGIN |
| mysql-bin.000003 | 538 | Query | 1 | 646 | use &＃96;ops&＃96;; update ops.member set name&＃61;&＃39;李四&＃39; where id&＃61; |
| mysql-bin.000003 | 646 | Xid | 1 | 673 | COMMIT /* xid&＃61;68 */ |
| mysql-bin.000003 | 673 | Query | 1 | 740 | BEGIN |
| mysql-bin.000003 | 740 | Query | 1 | 848 | use &＃96;ops&＃96;; update ops.member set name&＃61;&＃39;小二&＃39; where id&＃61; |
| mysql-bin.000003 | 848 | Xid | 1 | 875 | COMMIT /* xid&＃61;69 */ |
| mysql-bin.000003 | 875 | Query | 1 | 954 | drop database ops |
| mysql-bin.000003 | 954 | Rotate | 1 | 997 | mysql-bin.000004;pos&＃61;4 |
&＃43;------------------&＃43;-----&＃43;-------------&＃43;-----------&＃43;-------------&＃43;----------------------------------------------------------------------------------------------------------------------------&＃43;
13 rows in set (0.00 sec)
或者&＃xff1a;
mysql> show binlog events in &＃39;mysql-bin.000003&＃39;\G;
.........
.........
*************************** 12. row ***************************
Log_name: mysql-bin.000003
Pos: 875
Event_type: Query
Server_id: 1
End_log_pos: 954
Info: drop database ops
*************************** 13. row ***************************
Log_name: mysql-bin.000003
Pos: 954
Event_type: Rotate
Server_id: 1
End_log_pos: 997
Info: mysql-bin.000004;pos&＃61;4
13 rows in set (0.00 sec)
通过分析&＃xff0c;造成数据库破坏的pos点区间是介于 875--954 之间(这是按照日志区间的pos节点算的)&＃xff0c;只要恢复到875前就可。
7)
先把凌晨4点全备份的数据恢复&＃xff1a;
[root&＃64;vm-002 ~]# cd /opt/backup/
[root&＃64;vm-002 backup]# ls
mysql-bin.000003 ops_2016-09-25.sql.gz
[root&＃64;vm-002 backup]# gzip -d ops_2016-09-25.sql.gz
[root&＃64;vm-002 backup]# mysql -uroot -p -v Enter password: -------------- /*!40101 SET &＃64;OLD_CHARACTER_SET_CLIENT&＃61;&＃64;&＃64;CHARACTER_SET_CLIENT */ -------------- -------------- /*!40101 SET &＃64;OLD_CHARACTER_SET_RESULTS&＃61;&＃64;&＃64;CHARACTER_SET_RESULTS */ -------------- ............. ............. -------------- /*!40111 SET SQL_NOTES&＃61;&＃64;OLD_SQL_NOTES */ -------------- 这样就恢复了截至当日凌晨(4:00)前的备份数据都恢复了。 mysql> show databases; #发现ops库已经恢复回来了 mysql> use ops; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; &＃43;---------------&＃43; | Tables_in_ops | &＃43;---------------&＃43; | member | &＃43;---------------&＃43; 1 row in set (0.00 sec) mysql> select * from member; &＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43; | id | name | sex | age | classid | &＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43; | 1 | wangshibo | m | 27 | cls1 | | 2 | guohuihui | w | 27 | cls2 | &＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43; 2 rows in set (0.00 sec) mysql> 但是这仅仅只是恢复了当天凌晨4点之前的数据&＃xff0c;在4:00--18:00之间的数据还没有恢复回来&＃xff01;&＃xff01; 怎么办呢&＃xff1f; 莫慌&＃xff01;这可以根据前面提到的mysql-bin.000003的新binlog日志进行恢复。 8) 从binlog日志恢复数据 恢复命令的语法格式&＃xff1a; mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名 -------------------------------------------------------- 常用参数选项解释&＃xff1a; --start-position&＃61;875 起始pos点 --stop-position&＃61;954 结束pos点 --start-datetime&＃61;"2016-9-25 22:01:08" 起始时间点 --stop-datetime&＃61;"2019-9-25 22:09:46" 结束时间点 --database&＃61;zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库&＃xff0c;只限本地log日志) -------------------------------------------------------- 不常用选项&＃xff1a; -u --user&＃61;name 连接到远程主机的用户名 -p --password[&＃61;name] 连接到远程主机的密码 -h --host&＃61;name 从远程主机上获取binlog日志 --read-from-remote-server 从某个MySQL服务器上读取binlog日志 -------------------------------------------------------- 小结&＃xff1a;实际是将读出的binlog日志内容&＃xff0c;通过管道符传递给mysql命令。这些命令、文件尽量写成绝对路径&＃xff1b; a)完全恢复(需要手动vim编辑mysql-bin.000003&＃xff0c;将那条drop语句剔除掉) [root&＃64;vm-002 backup]# /usr/bin/mysqlbinlog /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops b)指定pos结束点恢复(部分恢复)&＃xff1a; --stop-position&＃61;471 pos结束节点(按照事务区间算&＃xff0c;是471) 注意&＃xff1a; 此pos结束节点介于“member表原始数据”与更新“name&＃61;&＃39;李四&＃39;”之前的数据&＃xff0c;这样就可以恢复到更改“name&＃61;&＃39;李四&＃39;”之前的数据了。 操作如下&＃xff1a; [root&＃64;vm-002 ~]# /usr/bin/mysqlbinlog --stop-position&＃61;471 --database&＃61;ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops mysql> select * from member; &＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43; | id | name | sex | age | classid | &＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43; | 1 | wangshibo | m | 27 | cls1 | | 2 | guohuihui | w | 27 | cls2 | | 3 | yiyi | w | 20 | cls1 | | 4 | xiaoer | m | 22 | cls3 | | 5 | zhangsan | w | 21 | cls5 | | 6 | lisi | m | 20 | cls4 | | 7 | wangwu | w | 26 | cls6 | &＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43; 7 rows in set (0.00 sec) 恢复截止到更改“name&＃61;&＃39;李四&＃39;”之间的数据(按照事务区间算&＃xff0c;是673) [root&＃64;vm-002 ~]# /usr/bin/mysqlbinlog --stop-position&＃61;673 --database&＃61;ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops mysql> select * from member; &＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43; | id | name | sex | age | classid | &＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43; | 1 | wangshibo | m | 27 | cls1 | | 2 | guohuihui | w | 27 | cls2 | | 3 | yiyi | w | 20 | cls1 | | 4 | 李四 | m | 22 | cls3 | | 5 | zhangsan | w | 21 | cls5 | | 6 | lisi | m | 20 | cls4 | | 7 | wangwu | w | 26 | cls6 | &＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43; 7 rows in set (0.00 sec) c)指定pso点区间恢复(部分恢复)&＃xff1a; 更新 name&＃61;&＃39;李四&＃39; 这条数据&＃xff0c;日志区间是Pos[538] --> End_log_pos[646]&＃xff0c;按事务区间是&＃xff1a;Pos[471] --> End_log_pos[673] 更新 name&＃61;&＃39;小二&＃39; 这条数据&＃xff0c;日志区间是Pos[740] --> End_log_pos[848]&＃xff0c;按事务区间是&＃xff1a;Pos[673] --> End_log_pos[875] c1) 单独恢复 name&＃61;&＃39;李四&＃39; 这步操作&＃xff0c;可这样&＃xff1a; 按照binlog日志区间单独恢复&＃xff1a; [root&＃64;vm-002 ~]# /usr/bin/mysqlbinlog --start-position&＃61;538 --stop-position&＃61;646 --database&＃61;ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops 按照事务区间单独恢复 [root&＃64;vm-002 ~]# /usr/bin/mysqlbinlog --start-position&＃61;471 --stop-position&＃61;673 --database&＃61;ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops c2) 单独恢复 name&＃61;&＃39;小二&＃39; 这步操作&＃xff0c;可这样&＃xff1a; 按照binlog日志区间单独恢复&＃xff1a; [root&＃64;vm-002 ~]# /usr/bin/mysqlbinlog --start-position&＃61;740 --stop-position&＃61;848 --database&＃61;ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops 按照事务区间单独恢复 [root&＃64;vm-002 ~]# /usr/bin/mysqlbinlog --start-position&＃61;673 --stop-position&＃61;875 --database&＃61;ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops c3) 将 name&＃61;&＃39;李四&＃39;、name&＃61;&＃39;小二&＃39; 多步操作一起恢复&＃xff0c;需要按事务区间&＃xff0c;可这样&＃xff1a; [root&＃64;vm-002 ~]# /usr/bin/mysqlbinlog --start-position&＃61;471 --stop-position&＃61;875 --database&＃61;ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops 查看数据库&＃xff1a; mysql> select * from member; &＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43; | id | name | sex | age | classid | &＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43; | 1 | wangshibo | m | 27 | cls1 | | 2 | 小二 | w | 27 | cls2 | | 3 | yiyi | w | 20 | cls1 | | 4 | 李四 | m | 22 | cls3 | | 5 | zhangsan | w | 21 | cls5 | | 6 | lisi | m | 20 | cls4 | | 7 | wangwu | w | 26 | cls6 | &＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43; 7 rows in set (0.00 sec) 这样&＃xff0c;就恢复了删除前的数据状态了&＃xff01;&＃xff01; ----------------- 另外&＃xff1a; 也可指定时间节点区间恢复(部分恢复)&＃xff1a; 除了用pos节点的办法进行恢复&＃xff0c;也可以通过指定时间节点区间进行恢复&＃xff0c;按时间恢复需要用mysqlbinlog命令读取binlog日志内容&＃xff0c;找时间节点。 如上&＃xff0c;误删除ops库后&＃xff1a; 先进行全备份恢复 [root&＃64;vm-002 backup]# mysql -uroot -p -v 查看ops数据库 mysql> select * from member; &＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43; | id | name | sex | age | classid | &＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43; | 1 | wangshibo | m | 27 | cls1 | | 2 | guohuihui | w | 27 | cls2 | &＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43; 2 rows in set (0.00 sec) mysql> 查看mysq-bin00003日志&＃xff0c;找出时间节点 [root&＃64;vm-002 ~]# cd /var/lib/mysql [root&＃64;vm-002 mysql]# mysqlbinlog mysql-bin.000003 ............. ............. BEGIN /*!*/; # at 173 #160925 21:57:19 server id 1 end_log_pos 201 Intvar SET INSERT_ID&＃61;3/*!*/; # at 201 #160925 21:57:19 server id 1 end_log_pos 444 Query thread_id&＃61;3 exec_time&＃61;0 error_code&＃61;0 use &＃96;ops&＃96;/*!*/; SET TIMESTAMP&＃61;1474811839/*!*/; insert into ops.member(&＃96;name&＃96;,&＃96;sex&＃96;,&＃96;age&＃96;,&＃96;classid&＃96;) values(&＃39;yiyi&＃39;,&＃39;w&＃39;,20,&＃39;cls1&＃39;),(&＃39;xiaoer&＃39;,&＃39;m&＃39;,22,&＃39;cls3&＃39;),(&＃39;zhangsan&＃39;,&＃39;w&＃39;,21,&＃39;cls5&＃39;),(&＃39;lisi&＃39;,&＃39;m&＃39;,20,&＃39;cls4&＃39;),(&＃39;wangwu&＃39;,&＃39;w&＃39;,26,&＃39;cls6&＃39;) #执行的sql语句 /*!*/; # at 444 #160925 21:57:19 server id 1 end_log_pos 471 Xid &＃61; 66 #开始执行的时间 COMMIT/*!*/; # at 471 #160925 21:58:41 server id 1 end_log_pos 538 Query thread_id&＃61;3 exec_time&＃61;0 error_code&＃61;0 #结束时间 SET TIMESTAMP&＃61;1474811921/*!*/; BEGIN /*!*/; # at 538 #160925 21:58:41 server id 1 end_log_pos 646 Query thread_id&＃61;3 exec_time&＃61;0 error_code&＃61;0 SET TIMESTAMP&＃61;1474811921/*!*/; update ops.member set name&＃61;&＃39;李四&＃39; where id&＃61;4 #执行的sql语句 /*!*/; # at 646 #160925 21:58:41 server id 1 end_log_pos 673 Xid &＃61; 68 #开始执行的时间 COMMIT/*!*/; # at 673 #160925 21:58:56 server id 1 end_log_pos 740 Query thread_id&＃61;3 exec_time&＃61;0 error_code&＃61;0 #结束时间 SET TIMESTAMP&＃61;1474811936/*!*/; BEGIN /*!*/; # at 740 #160925 21:58:56 server id 1 end_log_pos 848 Query thread_id&＃61;3 exec_time&＃61;0 error_code&＃61;0 SET TIMESTAMP&＃61;1474811936/*!*/; update ops.member set name&＃61;&＃39;小二&＃39; where id&＃61;2 #执行的sql语句 /*!*/; # at 848 #160925 21:58:56 server id 1 end_log_pos 875 Xid &＃61; 69 #开始执行的时间 COMMIT/*!*/; # at 875 #160925 22:01:08 server id 1 end_log_pos 954 Query thread_id&＃61;3 exec_time&＃61;0 error_code&＃61;0 #结束时间 SET TIMESTAMP&＃61;1474812068/*!*/; drop database ops /*!*/; # at 954 #160925 22:09:46 server id 1 end_log_pos 997 Rotate to mysql-bin.000004 pos: 4 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE&＃61;&＃64;OLD_COMPLETION_TYPE*/; 恢复到更改“name&＃61;&＃39;李四&＃39;”之前的数据 [root&＃64;vm-002 ~]# /usr/bin/mysqlbinlog --start-datetime&＃61;"2016-09-25 21:57:19" --stop-datetime&＃61;"2016-09-25 21:58:41" --database&＃61;ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops mysql> select * from member; &＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43; | id | name | sex | age | classid | &＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43; | 1 | wangshibo | m | 27 | cls1 | | 2 | guohuihui | w | 27 | cls2 | | 3 | yiyi | w | 20 | cls1 | | 4 | xiaoer | m | 22 | cls3 | | 5 | zhangsan | w | 21 | cls5 | | 6 | lisi | m | 20 | cls4 | | 7 | wangwu | w | 26 | cls6 | &＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43; 7 rows in set (0.00 sec) [root&＃64;vm-002 ~]# /usr/bin/mysqlbinlog --start-datetime&＃61;"2016-09-25 21:58:41" --stop-datetime&＃61;"2016-09-25 21:58:56" --database&＃61;ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops mysql> select * from member; &＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43; | id | name | sex | age | classid | &＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43; | 1 | wangshibo | m | 27 | cls1 | | 2 | guohuihui | w | 27 | cls2 | | 3 | yiyi | w | 20 | cls1 | | 4 | 李四 | m | 22 | cls3 | | 5 | zhangsan | w | 21 | cls5 | | 6 | lisi | m | 20 | cls4 | | 7 | wangwu | w | 26 | cls6 | &＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43; 7 rows in set (0.00 sec) [root&＃64;vm-002 ~]# /usr/bin/mysqlbinlog --start-datetime&＃61;"2016-09-25 21:58:56" --stop-datetime&＃61;"2016-09-25 22:01:08" --database&＃61;ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops mysql> select * from member; &＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43; | id | name | sex | age | classid | &＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43; | 1 | wangshibo | m | 27 | cls1 | | 2 | 小二 | w | 27 | cls2 | | 3 | yiyi | w | 20 | cls1 | | 4 | 李四 | m | 22 | cls3 | | 5 | zhangsan | w | 21 | cls5 | | 6 | lisi | m | 20 | cls4 | | 7 | wangwu | w | 26 | cls6 | &＃43;----&＃43;-----------&＃43;-----&＃43;-----&＃43;---------&＃43; 7 rows in set (0.00 sec) 这样&＃xff0c;就恢复了删除前的状态了&＃xff01; 总结&＃xff1a; 所谓恢复&＃xff0c;就是让mysql将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已。
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有