众所周知,binlog日志对于mysql数据库来说是十分重要的,在数据丢失的紧急情况下,我们往往会想到用binlog日志功能进行数据恢复(定时全备份+binlog日志恢复增量数据部分),化险为夷!

简单了解binlog

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

DDL

  • Data Definition Language 数据库定义语言
    主要的命令有CREATE、ALTER、DROP等,DDL主要是用在定义或改变表(TABLE)的结构,数据类型,表之间的链接和约束等初始化工作上,他们大多在建立表时使用

DML

  • Data Manipulation Language 数据操纵语言
    主要的命令是SELECT、UPDATE、INSERT、DELETE,就象它的名字一样,这4条命令是用来对数据库里的数据进行操作的语言
    ========================================================
    mysqlbinlog常见的选项有以下几个:
    --start-datetime:从二进制日志中读取指定等于时间戳或者晚于本地服务器的时间
    --stop-datetime:从二进制日志中读取指定小于时间戳或者等于本地服务器的时间 取值和上述一样
    --start-position:从二进制日志中读取指定position 事件位置作为开始。
    --stop-position:从二进制日志中读取指定position 事件位置作为事件截至
    ========================================================
    一般来说开启binlog日志大概会有1%的性能损耗
    binlog日志有两个最重要的使用场景
    1)MySQL主从复制:MySQL Replication在Master端开启binlog,Master把它的二进制日志传递给slaves来达到
    master-slave数据一致的目的。
    2)自然就是数据恢复了,通过使用mysqlbinlog工具来使恢复数据。
    binlog日志包括两类文件
    1)二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件
    2)二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句select)语句事件

开启binlog日志功能

编辑打开mysql配置文件/etc/mys.cnf

[mysqld]区块添加
log-bin=mysql-bin (mysql-bin 是日志的基本名或前缀名,也可指定二进制日志生成的路径,如:log-bin=/application/mysql/data/mysql-bin)
server-id=1
binlog_format=MIXED 加入此参数才能记录到insert语句

注意:每次服务器(数据库)重启,服务器会调用flush logs;,新创建一个binlog日志!

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
basedir=/application/mysql
datadir=/application/mysql/data
port=3306
# server_id = .....
socket=/application/mysql/tmp/mysql.sock

log-bin=/application/mysql/data/mysql-bin
server-id=1
binlog_format=MIXED # 加入此参数才能记录到insert语句

#skip-grant-tables
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

重启mysqld服务使配置生效

[root@db-01 ~]# /etc/init.d/mysqld stop
[root@db-01 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]

查看binlog日志是否开启

[root@db01 ~]# mysql -uroot -p123456
mysql> show variables like 'log_%';
+----------------------------------------+-----------------------------------------+
| Variable_name                          | Value                                   |
+----------------------------------------+-----------------------------------------+
| log_bin                                | ON                                      |
| log_bin_basename                       | /application/mysql/data/mysql-bin       |
| log_bin_index                          | /application/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators        | OFF                                     |
| log_bin_use_v1_row_events              | OFF                                     |
| log_error                              |                                         |
| 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 sec123456

常用的binlog日志操作命令

查看所有binlog日志列表

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       120 |
+------------------+-----------+
1 row in set (0.00 sec)

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

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

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

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       167 |
| mysql-bin.000002 |       120 |
+------------------+-----------+
2 rows in set (0.00 sec)

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

重置(清空)所有binlog日志

mysql> reset master;
Query OK, 0 rows affected (0.01 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       120 |
+------------------+-----------+
1 row in set (0.00 sec)

查看binlog日志内容,常用有两种方式

使用mysqlbinlog自带查看命令法

注意:
-->binlog是二进制文件,普通文件查看器cat、more、vim等都无法打开,必须使用自带的mysqlbinlog命令查看
-->binlog日志与数据库文件在同目录中
-->在MySQL5.5以下版本使用mysqlbinlog命令时如果报错,就加上 “--no-defaults”选项
查看mysql的数据存放目录,从下面结果可知是/application/mysql/data

[root@db01 ~]# ps -ef|grep mysqld
mysql     62802      1  0 11:27 ?        00:00:00 /application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
root      62838  62709  0 11:40 pts/0    00:00:00 grep --color=auto mysqld
[root@db01 ~]# cd /application/mysql/data/
[root@db01 data]# ls
auto.cnf  db01.err  db01.pid  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql-bin.000001  mysql-bin.index  performance_schema  test
#使用mysqlbinlog命令查看binlog日志内容  
[root@db01 data]# mysqlbinlog mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200102 11:36:23 server id 1  end_log_pos 120 CRC32 0xe69eda32 	Start: binlog v 4, server v 5.6.40-log created 200102 11:36:23 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
N2UNXg8BAAAAdAAAAHgAAAABAAQANS42LjQwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAA3ZQ1eEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAATLa
nuY=
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

解释:
server id 1 : 数据库主机的服务号;
end_log_pos 796: sql结束时的pos节点;
thread_id=11: 线程号;

上面这种办法读取出binlog日志的全文内容比较多,不容易分辨查看到pos点信息

下面介绍一种更为方便的查询命令,命令格式:
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 master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       120 |
+------------------+-----------+
1 row in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000001'\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 120
       Info: Server ver: 5.6.40-log, Binlog ver: 4
1 row in set (0.00 sec)

上面这条语句可以将指定的binlog日志文件,分成有效事件行的方式返回,并可使用limit指定pos点的起始偏移,查询条数!
如下操作示例:

# 查询第一个(最早)的binlog日志:
mysql> show binlog events\G;

# 指定查询 mysql-bin.000002这个文件:
mysql> show binlog events in 'mysql-bin.000002'\G;

# 指定查询 mysql-bin.000002这个文件,从pos点:624开始查起:
mysql> show binlog events in 'mysql-bin.000002' from 624\G;

# 指定查询 mysql-bin.000002这个文件,从pos点:624开始查起,查询10条(即10条语句)
mysql> show binlog events in 'mysql-bin.000002' from 624 limit 10\G;

# 指定查询 mysql-bin.000002这个文件,从pos点:624开始查起,偏移2行(即中间跳过2个),查询10条
mysql> show binlog events in 'mysql-bin.000002' from 624 limit 2,10\G;

利用binlog日志恢复mysql数据

创建ops库

mysql> create database ops
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| ops                |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.01 sec)

ops库下创建member表

mysql> use ops;
mysql> CREATE TABLE IF NOT EXISTS `member` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(16) NOT NULL,`sex` enum('m','w') NOT NULL DEFAULT 'm',`age` tinyint(3) unsigned NOT NULL,`classid` char(6) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+---------------+
| Tables_in_ops |
+---------------+
| member        |
+---------------+
1 row in set (0.00 sec)

mysql> 
mysql>  desc member;
+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| id      | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name    | varchar(16)         | NO   |     | NULL    |                |
| sex     | enum('m','w')       | NO   |     | m       |                |
| age     | tinyint(3) unsigned | NO   |     | NULL    |                |
| classid | char(6)             | YES  |     | NULL    |                |
+---------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

插入两条数据

mysql> insert into member(`name`,`sex`,`age`,`classid`) values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>  select * from member;
+----+-----------+-----+-----+---------+
| id | name      | sex | age | classid |
+----+-----------+-----+-----+---------+
|  1 | wangshibo | m   |  27 | cls1    |
|  2 | guohuihui | w   |  27 | cls2    |
+----+-----------+-----+-----+---------+
2 rows in set (0.00 sec)

场景模拟

备份数据库ops

[root@db01 data]# mkdir -p /opt/backup
#每天凌晨4点进行一次完全备份的定时计划任务
[root@db01 data]# crontab -l
0 4 * * * /usr/bin/mysqldump -uroot -p -B -F -R -x --master-data=2 ops|gzip >/opt/backup/ops_$(date +%F).sql.gz
#这里手动执行
[root@db01 data]# mysqldump -uroot -p -B -F -R -X --master-data=2 ops|gzip >/opt/backup/ops_$(date +%F).sql.gz
Enter password: 
[root@db01 data]# ll /opt/backup/
总用量 4
-rw-r--r--. 1 root root 685 1月   2 13:19 ops_2020-01-02.sql.gz

参数说明:
-B:指定数据库
-F:刷新日志
-R:备份存储过程等
-x:锁表
--master-data:在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息

待到数据库备份完成,就不用担心数据丢失了,因为有完全备份数据在!!
由于上面在全备份的时候使用了-F选项,那么当数据备份操作刚开始的时候系统就会自动刷新log,这样就会自动产生
一个新的binlog日志,这个新的binlog日志就会用来记录备份之后的数据库“增删改”操作
查看一下:

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

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

第二天由于业务需求对数据库进行各种”增删改“操作 (操作时间:早上)

比如:在ops库下的member表插入、修改了数据等等

mysql> insert into ops.member(`name`,`sex`,`age`,`classid`) values('yiyi','w',20,'cls1'),('xiaoer','m',22,'cls3'),('zhangsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0
mysql> select * from member;
+----+-----------+-----+-----+---------+
| id | name      | sex | age | classid |
+----+-----------+-----+-----+---------+
|  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    |
+----+-----------+-----+-----+---------+
7 rows in set (0.00 sec)

又执行了修改数据操作 (操作时间:中午)

mysql> update ops.member set name='李四' where id=6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update ops.member set name='郭慧慧' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from member;
+----+-----------+-----+-----+---------+
| id | name      | sex | age | classid |
+----+-----------+-----+-----+---------+
|  1 | wangshibo | m   |  27 | cls1    |
|  2 | 郭慧慧    | w   |  27 | cls2    |
|  3 | yiyi      | w   |  20 | cls1    |
|  4 | xiaoer    | m   |  22 | cls3    |
|  5 | zhangsan  | w   |  21 | cls5    |
|  6 | 李四      | m   |  20 | cls4    |
|  7 | wangwu    | w   |  26 | cls6    |
+----+-----------+-----+-----+---------+
7 rows in set (0.00 sec)

手贱执行了drop语句 (操作时间:下午)

mysql> drop database ops;
Query OK, 1 row affected (0.00 sec)

这种时候,一定不要慌张!!!

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

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

[root@db01 data]# cd /application/mysql/data/
[root@db01 data]# ls
auto.cnf  db01.err  db01.pid  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql-bin.000001  mysql-bin.000002  mysql-bin.index  performance_schema  test
[root@db01 data]# cp -v mysql-bin.000002 /opt/backup/
"mysql-bin.000002" -> "/opt/backup/mysql-bin.000002"
[root@db01 data]# ls /opt/backup/
mysql-bin.000002  ops_2020-01-02.sql.gz

接着执行一次刷新日志索引操作,重新开始新的binlog日志记录文件。按理说mysql-bin.000002
这个文件不会再有后续写入了,因为便于我们分析原因及查找ops节点,以后所有数据库操作都会写入到下一个日志文件

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

读取binlog日志,分析问题

读取binlog日志的方法上面已经说到

使用mysqlbinlog读取binlog日志

[root@db01 data]# mysqlbinlog mysql-bin.000002
...
#200102 13:38:03 server id 1  end_log_pos 937 CRC32 0xb73106b2 	Query	thread_id=6	exec_time=0	error_code=0
SET TIMESTAMP=1577943483/*!*/;
update ops.member set name='郭慧慧' where id=2
/*!*/;
# at 937
#200102 13:38:03 server id 1  end_log_pos 968 CRC32 0x6a7403c3 	Xid = 104
COMMIT/*!*/;
# at 968
#200102 13:42:01 server id 1  end_log_pos 1057 CRC32 0xe9ecdbe2 	Query	thread_id=7	exec_time=0	error_code=0
SET TIMESTAMP=1577943721/*!*/;
drop database ops
/*!*/;
# at 1057
#200102 13:46:35 server id 1  end_log_pos 1104 CRC32 0x1302f9a1 	Rotate to mysql-bin.000003  pos: 4
DELIMITER ;
# End of log file
...

登录服务器,并查看(推荐此种方法)

mysql> show binlog events in 'mysql-bin.000002';
+------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                                                                                                                                                             |
+------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000002 |    4 | Format_desc |         1 |         120 | Server ver: 5.6.40-log, Binlog ver: 4                                                                                                                                                            |
| mysql-bin.000002 |  120 | Query       |         1 |         197 | BEGIN                                                                                                                                                                                            |
| mysql-bin.000002 |  197 | Intvar      |         1 |         229 | INSERT_ID=3                                                                                                                                                                                      |
| mysql-bin.000002 |  229 | Query       |         1 |         482 | use `ops`; insert into ops.member(`name`,`sex`,`age`,`classid`) values('yiyi','w',20,'cls1'),('xiaoer','m',22,'cls3'),('zhangsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6') |
| mysql-bin.000002 |  482 | Xid         |         1 |         513 | COMMIT /* xid=99 */                                                                                                                                                                              |
| mysql-bin.000002 |  513 | Query       |         1 |         590 | BEGIN                                                                                                                                                                                            |
| mysql-bin.000002 |  590 | Query       |         1 |         708 | use `ops`; update ops.member set name='李四' where id=6                                                                                                                                          |
| mysql-bin.000002 |  708 | Xid         |         1 |         739 | COMMIT /* xid=103 */                                                                                                                                                                             |
| mysql-bin.000002 |  739 | Query       |         1 |         816 | BEGIN                                                                                                                                                                                            |
| mysql-bin.000002 |  816 | Query       |         1 |         937 | use `ops`; update ops.member set name='郭慧慧' where id=2                                                                                                                                        |
| mysql-bin.000002 |  937 | Xid         |         1 |         968 | COMMIT /* xid=104 */                                                                                                                                                                             |
| mysql-bin.000002 |  968 | Query       |         1 |        1057 | drop database ops                                                                                                                                                                                |
| mysql-bin.000002 | 1057 | Rotate      |         1 |        1104 | mysql-bin.000003;pos=4                                                                                                                                                                           |
+------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13 rows in set (0.00 sec)

或者

mysql> show binlog events in 'mysql-bin.000002'\G
......
......
*************************** 12. row ***************************
   Log_name: mysql-bin.000002
        Pos: 968
 Event_type: Query
  Server_id: 1
End_log_pos: 1057
       Info: drop database ops
*************************** 13. row ***************************
   Log_name: mysql-bin.000002
        Pos: 1057
 Event_type: Rotate
  Server_id: 1
End_log_pos: 1104
       Info: mysql-bin.000003;pos=4
13 rows in set (0.00 sec)

通过分析,造成数据库破坏的pos点区间是介于 968--1057 之间(这是按照日志区间的pos节点算的),只要恢复到968前就可

先把定时任务备份的数据恢复

[root@db01 ~]# cd /opt/backup/
[root@db01 backup]# ls
mysql-bin.000002 ops_2020-01-02.sql.gz
[root@db01 backup]# gzip -d ops_2020-01-02.sql.gz 
[root@db01 backup]# mysql -uroot -p -v < ops_2020-01-02.sql 
Enter password: 
--------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */
--------------
--------------
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */
--------------
.............
.............
--------------
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */
--------------
这样就恢复了截至当日凌晨(4:00)前的备份数据都恢复了

mysql> show databases;     #发现ops库恢复回来了
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| ops                |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

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;
+---------------+
| Tables_in_ops |
+---------------+
| member        |
+---------------+
1 row in set (0.00 sec)

mysql> select * from member;
+----+-----------+-----+-----+---------+
| id | name      | sex | age | classid |
+----+-----------+-----+-----+---------+
|  1 | wangshibo | m   |  27 | cls1    |
|  2 | guohuihui | w   |  27 | cls2    |
+----+-----------+-----+-----+---------+
2 rows in set (0.00 sec)

但是这仅仅只是恢复了当天凌晨4点之前的数据,在4:00--现在之间的数据还没有恢复回来!!
怎么办呢?
莫慌!这可以根据前面提到的mysql-bin.000003的新binlog日志进行恢复
恢复命令的语法格式:
mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名

常用参数选项解释:
--start-position=875 起始pos点
--stop-position=954 结束pos点
--start-datetime="2016-9-25 22:01:08" 起始时间点
--stop-datetime="2019-9-25 22:09:46" 结束时间点
--database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库,只限本地log日志)

不常用选项:
-u --user=name 连接到远程主机的用户名
-p --password[=name] 连接到远程主机的密码
-h --host=name 从远程主机上获取binlog日志
--read-from-remote-server 从某个MySQL服务器上读取binlog日志

小结:实际是将读出的binlog日志内容,通过管道符传递给mysql命令,这些命令、文件尽量写成绝对路径!
a)完全恢复(需要手动vim编辑mysql-bin.000003,将那条drop语句剔除掉)

[root@db01 data]# cp mysql-bin.000002 /opt/backup
[root@db01 backup]# mysqlbinlog mysql-bin.000002 > /opt/backup/000003.sql
[root@db01 backup]# vim 000003.sql
#删除里面的drop语句
[root@db01 backup]# ls
000003.sql  mysql-bin.000002  ops_2020-01-02.sql
[root@db01 backup]# rm -fr mysql-bin.000002
[root@db01 backup]# ls
000003.sql  ops_2020-01-02.sql
[root@db01 backup]# mysql -uroot -p -v < /opt/backup/000003.sql
Enter password: 

温馨提示:
在恢复全备数据之前必须将该binlog文件移出,否则恢复过程中,会继续写入语句到binlog,最终导致增量恢复数据部分变得比较混乱!
可参考:https://www.cnblogs.com/kevingrace/p/5904800.html

b)指定pos结束点恢复(部分恢复):
--stop-position=937 pos结束节点(按照事务区间算,是937)
注意:
此pos结束节点介于“member表原始数据”与更新“name='李四'”之前的数据,这样就可以恢复到更改“name='李四'”之前的数据了
操作如下:

mysqlbinlog --stop-position=937 --database=ops /application/mysql/data/mysql-bin.000002 | mysql -uroot -p123456 -v 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> select * from member;
+----+-----------+-----+-----+---------+
| id | name      | sex | age | classid |
+----+-----------+-----+-----+---------+
|  1 | wangshibo | m   |  27 | cls1    |
|  2 | 郭慧慧    | w   |  27 | cls2    |
|  3 | yiyi      | w   |  20 | cls1    |
|  4 | xiaoer    | m   |  22 | cls3    |
|  5 | zhangsan  | w   |  21 | cls5    |
|  6 | 李四      | m   |  20 | cls4    |
|  7 | wangwu    | w   |  26 | cls6    |
+----+-----------+-----+-----+---------+
7 rows in set (0.00 sec)

========================================================
另外:也可以指定时间节点区间恢复(部分恢复),就是说除了用pos节点的办法进行恢复,也可以通过指定时间节点区间进行恢复,
按时间恢复需要用mysqlbinlog命令读取binlog日志内容,找时间节点

恢复到更改“name='李四'”之前的数据

mysqlbinlog --start-datetime="2020-01-02 21:57:19" --stop-datetime="2020-01-02 21:58:41" --database=ops /application/mysql/data/mysql-bin.000002 | mysql -uroot -p123456 -v ops
文章作者: 鲜花的主人
版权声明: 本站所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 爱吃可爱多
MySQL 数据库
喜欢就支持一下吧
打赏
微信 微信
支付宝 支付宝