二进制日志恢复数据库数据

一、新建一个目录来存放二进制日志文件，并在配置文件中添加log-bin字段，指定存放二进制日志文件的位置

[root@linux-node2 ~]#  mkdir /var/lib/mysql-log
[root@linux-node2 ~]# chown mysql. /var/lib/mysql-log -R
[root@linux-node2 ~]# grep 'log-bin' /etc/my.cnf
log-bin=/var/lib/mysql-log/master
[root@linux-node2 ~]# systemctl restart mariadb
[root@linux-node2 ~]# ls /var/lib/mysql-log
master.000001 master.index

二、对数据库进行操作

1、全备份数据

[root@linux-node2 ~]# mysqldump --all-databases -uroot -p123 --single-transaction --flush-logs --master-data=2 > /tmp/mysql.all.sql
[root@linux-node2 ~]# ll /var/lib/mysql-log
total 12
-rw-rw---- 1 mysql mysql 373 Mar  7 14:00 master.000001
-rw-rw---- 1 mysql mysql 245 Mar  7 14:00 master.000002
-rw-rw---- 1 mysql mysql  68 Mar  7 14:00 master.index
[root@linux-node2 ~]# sed -n '22p' /tmp/mysql.all.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='master.000002', MASTER_LOG_POS=791;

备注：
–all-databases 参数是备份说有的库和表,这个参数和 -A 效果一样；
–single-transaction 参数对innodb引擎的表备份时保持数据一致性；
–flush-logs 参数在备份完成后刷新二进制日志，就是重新开始一个日志,可以看到下面多了一个master.000002；
–master-data=2 参数会在备份好的文件/tmp/mysql.all.sql 中插入与master有关的数据，一般在第22行，包括 MASTER_LOG_FILE和 MASTER_LOG_POS分别代表master备份命令执行后新生成的日志文件是哪个

2、对数据库进行操作，然后模拟删除数据库的操作

[root@linux-node2 ~]# mysql -uroot -p123
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.41-MariaDB-log MariaDB Server
Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> create database db1;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use db1;
Database changed
MariaDB [db1]> create table t1 (id int primary key);
Query OK, 0 rows affected (0.05 sec)
MariaDB [db1]> insert into t1 values (1);
Query OK, 1 row affected (0.04 sec)
MariaDB [db1]> insert into t1 values (2);
Query OK, 1 row affected (0.04 sec)
MariaDB [db1]> insert into t1 values (3);
Query OK, 1 row affected (0.07 sec)
MariaDB [db1]> delete from t1;                  #这一步进行了误删除的操作
Query OK, 3 rows affected (0.03 sec)
MariaDB [db1]> \q
Bye

三、数据恢复

1、恢复全备份数据

[root@linux-node2 ~]# systemctl stop mariadb
[root@linux-node2 ~]# rm -rf /var/lib/mysql/*
[root@linux-node2 ~]# systemctl start mariadb
[root@linux-node2 ~]# mysql < /tmp/mysql.all.sql     #导入备份数据
[root@linux-node2 ~]# systemctl restart mariadb
[root@linux-node2 ~]# ls /var/lib/mysql-log
master.000001  master.000003  master.000005  master.index
master.000002  master.000004  master.000006

2、增量数据恢复(有以下两种方法)
(1)通过–start-date和–stop-date
–stop-date=”时间点”运行到以时间点开头的行结束
–start-date=”时间点”运行以时间点开头的行开始

①通过mysqlbinlog来查看二进制日志文件并截取包行delete关键字的行的前三行，包括delete所在行
[root@linux-node2 ~]# mysqlbinlog /var/lib/mysql-log/master.000002|grep -B 3 "delete"
# at 1031
#160307 14:08:19 server id 1  end_log_pos 1107  Query   thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1457330899/*!*/;
delete from t1
②读取二进制日志文件，到 160307 14:08:19结束，通过管道导入到mysql
[root@linux-node2 ~]# mysqlbinlog /var/lib/mysql-log/master.000002 --stop-date="2016-03-07 14:08:19"|mysql -uroot -p123
③进入mysql查看是否恢复成功
[root@linux-node2 ~]# mysql -uroot -p123
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.01 sec)
MariaDB [(none)]> use db1;
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
MariaDB [db1]> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

(2)通过position号

①通过mysqlbinlog来查看二进制日志文件并截取包行delete关键字的行的前三行，包括delete所在行
[root@linux-node2 ~]# mysqlbinlog /var/lib/mysql-log/master.000002|grep -B 3 "delete"
# at 1031
#160307 14:08:19 server id 1  end_log_pos 1107  Query   thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1457330899/*!*/;
delete from t1

[root@linux-node2 ~]# mysqlbinlog /var/lib/mysql-log/master.000002 --stop-position="1031" |mysql -uroot -p123
或者
[root@linux-node2 ~]# mysqlbinlog /var/lib/mysql-log/master.000010 --start-position="791" --stop-position="1031" |mysql -uroot -p123

[root@linux-node2 ~]# mysql -uroot -p123
MariaDB [(none)]> show databases;
MariaDB [db1]> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+