1 先全備份:
mysqldump --flush-logs -u root --all-databases > alldatabase.sql
2 進行操作
mysql> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| testdb-bin.000062 | 106 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> select * from t;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 1 |
| 2 |
| 3 |
| 4 |
| 1 |
| 2 |
| 3 |
| 4 |
| 1 |
| 2 |
| 4 |
+------+
15 rows in set (0.00 sec)
mysql> select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2010-11-29 13:31:56 |
+---------------------+
1 row in set (0.00 sec)
mysql> delete from t where a=1;
Query OK, 4 rows affected (0.00 sec)
mysql> select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2010-11-29 13:32:10 |
+---------------------+
1 row in set (0.00 sec)
mysql> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| testdb-bin.000062 | 192 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
3 用全備份恢複
mysql -uroot -pyihaodian < alldatabase.sql
4 檢視資料
mysql> select * from t;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 1 |
| 2 |
| 3 |
| 4 |
| 1 |
| 2 |
| 3 |
| 4 |
| 1 |
| 2 |
| 4 |
+------+
15 rows in set (0.00 sec)
5 執行增加恢複
mysqlbinlog --start-date="2010-11-29 13:31:56" --stop-date="2010-11-29 13:32:10" /var/lib/mysql/testdb-bin.000062 | mysql -u root -p
6 檢視情況
mysql> select * from t;
+------+
| a |
+------+
| 2 |
| 3 |
| 4 |
| 2 |
| 3 |
| 4 |
| 2 |
| 3 |
| 4 |
| 2 |
| 4 |
+------+
11 rows in set (0.00 sec)