mysql> create database school;

Query OK, 1 row affected (0.01 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| school             |

| sys                |

+--------------------+

5 rows in set (0.00 sec)

mysql> use school;

Database changed

mysql> select * from school.t1;

Empty set (0.00 sec)

mysql> create table t2 (id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values (1),(2);

Query OK, 2 rows affected (0.03 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;

+------+

| id   |

+------+

|    1 |

|    2 |

+------+

2 rows in set (0.00 sec)

mysql> 

[root@localhost ~]# mysqldump -uroot -p'Yanglt123.' --all-databases \

> --single-transaction \

> --routines \

> --triggers \

> --master-data= \

> --flush-logs > /tmp/`date +%F`-mysql-all.sql`

mysqldump: [Warning] Using a password on the command line interface can be insecure.    #此提示是密码明文显示的愿意

[root@localhost tmp]# 

--master-data=    #该选项将会记录binlog的日志位置与文件名并追加到文件中

参数为1和2的时候，都是把position日志截断，如果为2的话第22行为注释状态，为1的时候没有注释，建议选择1：

 [root@localhost tmp]# vim ---mysql-all.sql 可以

  -- Position to start replication or point-in-time recovery from

  --

  CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=;

  --

:set nu                                                        

先创建一个表，等一下验证恢复情况：

mysql> create table t2 (id int);

Query OK,  rows affected (0.02 sec)

mysql> insert into t2 values(),()

    -> ;

Query OK,  rows affected (0.03 sec)

Records:   Duplicates:   Warnings: 

mysql> show tables;

+------------------+

| Tables_in_school |

+------------------+

| t1               |

| t2               |

+------------------+

 rows in set (0.00 sec)

mysql> Bye

[root@localhost ~]# 

）停止数据库

[root@localhost ~]# systemctl stop mysqld

[root@localhost ~]#

）清理环境

此处暂时不删除bin-log日志

[root@localhost ~]# systemctl stop mysqld

[root@localhost ~]# rm -rf /var/lib/mysql/*

3）启动数据库

[root@localhost ~]# systemctl start mysqld

4）重置密码

[root@localhost ~]# grep 'temporary password' /var/log/mysqld.log|tail -n 1

2018-09-19T09:48:39.418109Z 1 [Note] A temporary password is generated for root@localhost: aBm<-wrj4NSV

[root@localhost ~]# mysqladmin -uroot -p'aBm<-wrj4NSV' password "Yanglt123."

mysqladmin: [Warning] Using a password on the command line interface can be insecure.

Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

[root@localhost ~]# systemctl restart mysqld

[root@localhost ~]# 

5）恢复数据

[root@localhost ~]# mysql -uroot -p'Yanglt123.' < /tmp/2018-09-19-mysql-all.sql

mysql: [Warning] Using a password on the command line interface can be insecure.

[root@localhost ~]#

可以看到它恢复到了备份点，刚才创建的表t2是在备份点之后生成的，可以看到表中没有t2：

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| school             |

| sys                |

+--------------------+

5 rows in set (0.01 sec)

mysql> use school;

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_school |

+------------------+

| t1               |

+------------------+

1 row in set (0.00 sec)

mysql> 

6） 刷新授权

改完密码后与备份点的密码可能不一致，所有我们要执行此步骤，来实现与备份点密码一致。

[root@localhost ~]# mysql -p'Yanglt123.' -e 'flush privileges'

mysql: [Warning] Using a password on the command line interface can be insecure.

[root@localhost ~]# 

7）建议在逻辑备份恢复时，暂停BINLOG

mysql> SET SQL_LOG_BIN=0;

Query OK, 0 rows affected (0.02 sec)

mysql> source /tmp/2018-09-19-mysql-all.sql;