mysqldump备份与恢复

mysqldump备份与恢复

1.在一张表中插入记录,并做备份（假如周日的全备）

mysql> create database  hanhy
mysql> use  hanhy;
mysql> create  table  hanhy  (id,name)   values(1,han);
mysql> insert into hanhy.hanhy values(1,"han");
Query OK, 1 row affected (0.01 sec)
mysql> insert into hanhy.hanhy values(2,"zhang");
Query OK, 1 row affected (0.00 sec)
mysql> insert into hanhy.hanhy values(3,"wang");
Query OK, 1 row affected (0.04 sec)
mysql> insert into hanhy.hanhy values(4,"wuli");
Query OK, 1 row affected (0.00 sec)

 mysqldump --all-databases --master-data=2 --single-transaction --flush-logs --quick -R --events  > /backup/all.sql

2.周一我们还要做操作删除一条和恢复一条，但是我们有不想要会删除那一条，update那条做保留

mysql> delete  from hanhy  where  id  = 1;
Query OK, 1 row affected (0.05 sec)

mysql> select  *   from   hanhy;
+------+-------+
| id   | name  |
+------+-------+
|    2 | zhang |
|    3 | wang  |
|    4 | wuli  |
+------+-------+
3 rows in set (0.00 sec)

mysql> update hanhy  set id=5 where  name="wuli";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select  *   from   hanhy;
+------+-------+
| id   | name  |
+------+-------+
|    2 | zhang |
|    3 | wang  |
|    5 | wuli  |
+------+-------+
3 rows in set (0.00 sec)

 现在我们需要id=1 数据 update之后的

3.恢复操作（恢复全备+binlog日志）

[root@DB log-bin]# mysql -uroot -p  < /backup/all.sql 
Enter password: 

mysql> show  binlog events in 'mysql-bin.000002';
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                   |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+
| mysql-bin.000002 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.34-log, Binlog ver: 4                  |
| mysql-bin.000002 | 120 | Query       |         1 |         201 | BEGIN                                                  |
| mysql-bin.000002 | 201 | Query       |         1 |         311 | use `hanhy`; delete  from hanhy  where  id  = 1        |
| mysql-bin.000002 | 311 | Xid         |         1 |         342 | COMMIT /* xid=444 */                                   |
| mysql-bin.000002 | 342 | Query       |         1 |         423 | BEGIN                                                  |
| mysql-bin.000002 | 423 | Query       |         1 |         540 | use `hanhy`; update hanhy  set id=5 where  name="wuli" |
| mysql-bin.000002 | 540 | Xid         |         1 |         571 | COMMIT /* xid=446 */                                   |
| mysql-bin.000002 | 571 | Stop        |         1 |         594 |                                                        |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+
8 rows in set (0.00 sec)

由此可见pos=311之后就可以啦

[root@DB log-bin]# mysqlbinlog --start-position=311 --stop-position=594 --database=hanhy mysql-bin.000002 |mysql -uroot -p
Enter password: 

mysql> select  *  from hanhy;
+------+-------+
| id   | name  |
+------+-------+
|    1 | han   |
|    2 | zhang |
|    3 | wang  |
|    5 | wuli  |
+------+-------+
4 rows in set (0.00 sec)

由于自己备用，原文出自http://blog.csdn.net/nuli888/article/details/52117646，先做备份后恢复