一个完全恢复案例

(1)上午9点，备份数据库

 

 [root@mysqldb1 ~]# mysqldump -h10.86.7.215 -uroot -p -l -F test >test.dmp
Enter password:
[root@mysqldb1 ~]#

 

其中参数-l表示给所有表加读锁，-F表示生成一个新的日志文件。

 

(2)查看表test的数据

 

[root@mysqldb1 ~]# mysql -h10.86.7.215  -uroot -pinfohow
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.6.11-ndb-7.3.2-cluster-gpl-log MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
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 test;
+------+------+
| id   | name |
+------+------+
|    2 | z2   |
|    1 | z1   |
+------+------+
2 rows in set (0.00 sec)

mysql>

（3）9点半备份完成后，然后，插入新的数据

 

mysql> insert into test values(3,'z3');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(4,'z4');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    3 | z3   |
|    2 | z2   |
|    1 | z1   |
|    4 | z4   |
+------+------+
4 rows in set (0.00 sec

 

 

(4)10点，数据库突然故障，数据无法访问，需要恢复备份

[root@mysqldb1 ~]# mysql -h10.86.7.215 -uroot -p test <test.dmp
Enter password:
[root@mysqldb1 ~]#

恢复数据后数据如下：

[root@mysqldb1 ~]# mysql -h10.86.7.215  -uroot -pinfohow
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 5.6.11-ndb-7.3.2-cluster-gpl-log MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
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 test;
+------+------+
| id   | name |
+------+------+
|    1 | z1   |
|    2 | z2   |
+------+------+
2 rows in set (0.00 sec)

mysql>

 

注：数据已经出现丢失。

 

(5)使用mysqlbinlog恢复自mysqldump备份以来的binlog.

 

[root@mysqldb1 ~]# mysqlbinlog /mysqldata/mysql-bin.000004|mysql -h10.86.7.215  -u root -p test
Enter password:
[root@mysqldb1 ~]#

 

查看恢复后的数据：

 

 

[root@mysqldb1 mysqldata]# mysql -h10.86.7.215  -uroot -pinfohow
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 56
Server version: 5.6.11-ndb-7.3.2-cluster-gpl-log MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
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 test;
+------+------+
| id   | name |
+------+------+
|    2 | z2   |
|    1 | z1   |
+------+------+
2 rows in set (0.00 sec)

 

 

注：数据怎么还存在丢失现象呢？？为什么？？