1.查看bin-log是否开启

mysql> show global variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

2.在test库下导入t1表数据，做测试。

#!/bin/sh
create_table_FUNC(){
mysql -hlocalhost -uroot -pwwwwww -e "
CREATE DATABASE IF NOT EXISTS test;
USE test;
CREATE TABLE IF NOT EXISTS t1(id int, val varchar(20));
QUIT;
"
}

insert_data_FUNC(){
for ((i = 0; i < 100; i++))
do
    mysql -hlocalhost -uroot -pwwwwww -e "
        USE test;
        INSERT INTO t1 VALUES(1, 'aa');
        QUIT;
    "
done
}

echo "start create table"
create_table_FUNC
echo "create table end"

echo "start insert data"
insert_data_FUNC
echo "end insert data"

3.在时间a点，备份test数据库

http://www.cndba.cn/hbhe0316/article/4837

[root@mysql data]# mysqldump -hlocalhost -uroot -p --protocol tcp --port 3306 --flush-logs --databases test > /data/test.sql

1. 在时间b点，创建t2表并插入数据

#!/bin/sh
create_table_FUNC(){
mysql -hlocalhost -uroot -pwwwwww -e "
CREATE DATABASE IF NOT EXISTS test;
USE test;
CREATE TABLE IF NOT EXISTS t2(id int, val varchar(20));
QUIT;
"
}

insert_data_FUNC(){
for ((i = 0; i < 100; i++))
do
    mysql -hlocalhost -uroot -pwwwwww -e "
        USE test;
        INSERT INTO t2 VALUES(222, 'bbb');
        QUIT;
    "
done
}

echo "start create table"
create_table_FUNC
echo "create table end"

echo "start insert data"
insert_data_FUNC
echo "end insert data"

1. 要求恢复到时间点b(存在表t1和t2)
   ```shell
   mysql> drop table t1;
   Query OK, 0 rows affected (0.07 sec)

mysql> drop table t2;
Query OK, 0 rows affected (0.07 sec)

6. 先恢复全库
```shell
[root@mysql data]# mysql -uroot -p -hlocalhost --port 3306 < /data/test.sql 
Enter password:

7.挖掘log-bin日志http://www.cndba.cn/hbhe0316/article/4837

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

8.将bin-log日志转化成txt文件

http://www.cndba.cn/hbhe0316/article/4837

[root@mysql data]# mysqlbinlog /mysql/data/mysql-bin.000039 > /data/test-bin-log.txt

9.找到第一次删除t1表的位置
执行命令恢复

mysqlbinlog --start-datetime='2019-05-24 14:24:46'  --stop-datetime='2019-05-24 14:26:39' /mysql/data/mysql-bin.000002  | mysql -hlocalhost -uroot -p

查看是否恢复http://www.cndba.cn/hbhe0316/article/4837http://www.cndba.cn/hbhe0316/article/4837

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
+----------------+
2 rows in set (0.00 sec)

start-datetime=’2019-05-24 14:24:46’ 可以过通sql dump文件中最后一行得到备份的结束时间,在/data/test.sql里面最后一行查看
stop-datetime=’2019-05-24 14:26:39’ 通过mysqlbinlog发掘后，产生drop之前的时间点得到，在bin-log里面查看
主要的不足:mysqldump本身比较慢;PITR是恢复了整个库，影响比较大，通过查找也可以恢复单个表。

版权声明：本文为博主原创文章，未经博主允许不得转载。

mysql