关闭

一个完全恢复案例

326人阅读 评论(0) 收藏 举报
分类:

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

 

 

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

 

 

 

 

 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:186184次
    • 积分:5031
    • 等级:
    • 排名:第5542名
    • 原创:327篇
    • 转载:46篇
    • 译文:0篇
    • 评论:3条
    最新评论