mysql 利用binlog增量備份、恢復

aaqwsh發表於2010-11-29
MySql
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)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/758322/viewspace-680533/，如需轉載，請註明出處，否則將追究法律責任。

相關文章