一个完全恢复案例

(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)

 

 

注:数据怎么还存在丢失现象呢??为什么??

 

 

 

 

 

阅读更多
个人分类: mysql
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭