一、開啟binlog。
首先查看binlog是否開啟
|
1
2
3
4
5
6
7
|
mysql> show variables like "log_bin";+---------------+-------+|Variable_name | Value +---------------+-------+| log_bin OFF+---------------+-------+1 row in set (0.00 sec) |
值為OFF，需開啟，開啟binlog方式如下：
|
1
|
#vim /etc/my.cnf |
在[mysqld]中加入
|
1
2
|
log-bin = mysql-binlog-bin = /usr/local/mysql/log/mysql-bin.log |
重啟mysql服務(wù)
|
1
2
|
#service mysqld stop#service mysqld start |
二、模擬數(shù)據(jù)寫入
建庫(kù)
|
1
|
create database backup; |
建表
|
1
2
3
4
5
|
CREATE TABLE `number` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '編號(hào)', `updatetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
寫入數(shù)據(jù)
程序2-1
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
#coding:utf8#python2.7import MySQLdbimport timedef connect_mysql(db_host="192.168.11.169",user="martin",passwd="martin",db="backup",charset="utf8"): conn = MySQLdb.connect(host=db_host,user=user,passwd=passwd,db=db,charset=charset) conn.autocommit(True)return conn.cursor()#數(shù)據(jù)插入for i in range(0,10):#time=time.strftime("%Y-%m-%d %H:%M:%S")sql = 'insert into number(updatetime) values(%s)'values = [(time.strftime("%Y-%m-%d %H:%M:%S"))] db1 = connect_mysql()print db1.executemany(sql,values) |
查詢數(shù)據(jù)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql> select * from number;+-------+------------------------+| id | updatetime +--------------------------------+| 1 | 2016-06-29 23:27:15 || 2 | 2016-06-29 23:27:15 || 3 | 2016-06-29 23:27:15 || 4 | 2016-06-29 23:27:15 || 5 | 2016-06-29 23:27:15 || 6 | 2016-06-29 23:27:15 || 7 | 2016-06-29 23:27:15 || 8 | 2016-06-29 23:27:15 || 9 | 2016-06-29 23:27:15 || 10 | 2016-06-29 23:27:15 |+-------+------------------------+10 rows in set (0.00 sec) |
三、全量備份
|
1
|
mysqldump -uroot -p -F --master-data=2 backup |gzip> /martin/data/backup_$(date +%F).sql.gz |
注：加-F能刷新binlog，方便恢復(fù)時(shí)操作。
四、模擬寫入增量數(shù)據(jù)
繼續(xù)執(zhí)行程序2-1。
查詢數(shù)據(jù)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
mysql> select * from number;+----+---------------------------+| id | updatetime |+----+---------------------------+| 1 | 2016-06-29 23:27:15 || 2 | 2016-06-29 23:27:15 || 3 | 2016-06-29 23:27:15 || 4 | 2016-06-29 23:27:15 || 5 | 2016-06-29 23:27:15 || 6 | 2016-06-29 23:27:15 || 7 | 2016-06-29 23:27:15 || 8 | 2016-06-29 23:27:15 || 9 | 2016-06-29 23:27:15 || 10 | 2016-06-29 23:27:15 || 11 | 2016-06-29 23:31:03 || 12 | 2016-06-29 23:31:03 || 13 | 2016-06-29 23:31:03 || 14 | 2016-06-29 23:31:03 || 15 | 2016-06-29 23:31:03 || 16 | 2016-06-29 23:31:03 || 17 | 2016-06-29 23:31:03 || 18 | 2016-06-29 23:31:03 || 19 | 2016-06-29 23:31:03 || 20 | 2016-06-29 23:31:03 |+-------+---------------------+20 rows in set (0.00 sec) |
五、增量備份
保留mysql-bin.000002及之后的binlog即可。
六、模擬誤操作
|
1
|
delete from number; |
七、再次寫入增量數(shù)據(jù)
執(zhí)行程序2-1
select * from bumber;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
+------+------------------------+| id | updatetime |+------+------------------------+| 21 | 2016-06-29 23:41:06 || 22 | 2016-06-29 23:41:06 || 23 | 2016-06-29 23:41:06 || 24 | 2016-06-29 23:41:06 || 25 | 2016-06-29 23:41:06 || 26 | 2016-06-29 23:41:06 || 27 | 2016-06-29 23:41:06 || 28 | 2016-06-29 23:41:06 || 29 | 2016-06-29 23:41:06 || 30 | 2016-06-29 23:41:06 |+------+------------------------+10 rows in set (0.00 sec) |
八、恢復(fù)
此時(shí)發(fā)現(xiàn)之前的delete操作為誤操作，急需恢復(fù)，恢復(fù)過(guò)程如下
給該表加上讀鎖
|
1
|
lock table number read; |
將全量備份的數(shù)據(jù)導(dǎo)入
|
1
2
3
4
|
#cd /martin/data/#gzip -d number_2016-06-29.sql.gz#grep -i "change" *.sql-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=107; |
刷新日志
|
1
2
3
4
5
6
|
#mysqladmin -uroot -p'martin' flush-logs#cd /usr/local/mysql/log#ls|grep mysql-bin|grep -v indexmysql-bin.000001mysql-bin.000002mysql-bin.000003 |
可確定mysql-bin.000002為增量數(shù)據(jù)binlog
導(dǎo)入全量備份
|
1
2
3
4
5
|
#cd /martin/data/#mysql -uroot -p backup < number_2016-06-29.sql#cp /usr/local/mysql/log/mysql-bin.000002 /martin/data/#mysqlbinlog mysql-bin.000002 >bin.sql#vim bin.sql |
在bin.sql找到之前的delete語(yǔ)句，刪除
|
1
|
mysql -uroot -p <bin.sql |
九、確認(rèn)已恢復(fù)數(shù)據(jù)
登錄mysql
|
1
2
|
#mysql -uroot -p'martin' backupselect * from number; |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
+----+---------------------+| id | updatetime |+----+---------------------+| 1 | 2016-06-29 23:27:15 || 2 | 2016-06-29 23:27:15 || 3 | 2016-06-29 23:27:15 || 4 | 2016-06-29 23:27:15 || 5 | 2016-06-29 23:27:15 || 6 | 2016-06-29 23:27:15 || 7 | 2016-06-29 23:27:15 || 8 | 2016-06-29 23:27:15 || 9 | 2016-06-29 23:27:15 || 10 | 2016-06-29 23:27:15 || 11 | 2016-06-29 23:31:03 || 12 | 2016-06-29 23:31:03 || 13 | 2016-06-29 23:31:03 || 14 | 2016-06-29 23:31:03 || 15 | 2016-06-29 23:31:03 || 16 | 2016-06-29 23:31:03 || 17 | 2016-06-29 23:31:03 || 18 | 2016-06-29 23:31:03 || 19 | 2016-06-29 23:31:03 || 20 | 2016-06-29 23:31:03 || 21 | 2016-06-29 23:41:06 || 22 | 2016-06-29 23:41:06 || 23 | 2016-06-29 23:41:06 || 24 | 2016-06-29 23:41:06 || 25 | 2016-06-29 23:41:06 || 26 | 2016-06-29 23:41:06 || 27 | 2016-06-29 23:41:06 || 28 | 2016-06-29 23:41:06 || 29 | 2016-06-29 23:41:06 || 30 | 2016-06-29 23:41:06 |+----+---------------------+30 rows in set (0.00 sec) |
恢復(fù)完成！以上就是本文的全部?jī)?nèi)容，在操作數(shù)據(jù)庫(kù)時(shí)候要多加小心盡量避免誤操作，如果萬(wàn)一遇到了，希望本文能夠幫助大家。
原文鏈接：http://529876181.blog.51cto.com/9524887/1826896
