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

一、新建一个目录来存放二进制日志文件，并在配置文件中添加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.cnflog-bin=/var/lib/mysql-log/master[root@linux-node2 ~]# systemctl restart mariadb[root@linux-node2 ~]# ls /var/lib/mysql-logmaster.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-logtotal 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 -p123Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 4Server version: 5.5.41-MariaDB-log MariaDB ServerCopyright (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 changedMariaDB [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]> \qBye

三、数据恢复

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-logmaster.000001  master.000003  master.000005  master.indexmaster.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=0SET 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 -p123MariaDB [(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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [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=0SET 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 -p123MariaDB [(none)]> show databases;MariaDB [db1]> select * from t1;+----+| id |+----+|  1 ||  2 ||  3 |+----+