binlog日志恢复数据
众所周知，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 许可协议。转载请注明来自
爱吃可爱多！
喜欢就支持一下吧
打赏
微信
支付宝
微信
支付宝