《Mysql學(xué)習(xí)詳解MySQL誤操作后怎樣進(jìn)行數(shù)據(jù)恢復(fù)》要點(diǎn)：
本文介紹了Mysql學(xué)習(xí)詳解MySQL誤操作后怎樣進(jìn)行數(shù)據(jù)恢復(fù)，希望對您有用。如果有疑問，可以聯(lián)系我們。
MYSQL入門一、開啟binlog.
MYSQL入門首先查看binlog是否開啟
MYSQL入門
mysql> show variables like "log_bin";
+---------------+-------+
|Variable_name | Value
+---------------+-------+
| log_bin OFF
+---------------+-------+
1 row in set (0.00 sec)
MYSQL入門值為OFF,需開啟,開啟binlog方式如下：
MYSQL入門
#vim /etc/my.cnf
MYSQL入門在[mysqld]中加入
MYSQL入門
log-bin = mysql-bin
log-bin = /usr/local/mysql/log/mysql-bin.log
MYSQL入門重啟mysql服務(wù)
MYSQL入門
#service mysqld stop
#service mysqld start
MYSQL入門二、模擬數(shù)據(jù)寫入
MYSQL入門建庫
MYSQL入門
create database backup;
MYSQL入門建表
MYSQL入門
CREATE TABLE `number` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '編號',
`updatetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
MYSQL入門寫入數(shù)據(jù)
MYSQL入門程序2-1
MYSQL入門
#coding:utf8
#python2.7
import MySQLdb
import time
def 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)
MYSQL入門查詢數(shù)據(jù)
MYSQL入門
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)
MYSQL入門三、全量備份
MYSQL入門
mysqldump -uroot -p -F --master-data=2 backup |gzip> /martin/data/backup_$(date +%F).sql.gz
MYSQL入門注：加-F能刷新binlog,方便恢復(fù)時(shí)操作.
MYSQL入門四、模擬寫入增量數(shù)據(jù)
MYSQL入門繼續(xù)執(zhí)行程序2-1.
MYSQL入門查詢數(shù)據(jù)
MYSQL入門
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入門五、增量備份
MYSQL入門保留mysql-bin.000002及之后的binlog即可.
MYSQL入門六、模擬誤操作
MYSQL入門
delete from number;
MYSQL入門七、再次寫入增量數(shù)據(jù)
MYSQL入門執(zhí)行程序2-1
MYSQL入門select * from bumber;
MYSQL入門
+------+------------------------+
| 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)
MYSQL入門八、恢復(fù)
MYSQL入門此時(shí)發(fā)現(xiàn)之前的delete操作為誤操作,急需恢復(fù),恢復(fù)過程如下
MYSQL入門給該表加上讀鎖
MYSQL入門
lock table number read;
MYSQL入門將全量備份的數(shù)據(jù)導(dǎo)入
MYSQL入門
#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;
MYSQL入門刷新日志
MYSQL入門
#mysqladmin -uroot -p'martin' flush-logs
#cd /usr/local/mysql/log
#ls|grep mysql-bin|grep -v index
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
MYSQL入門可確定mysql-bin.000002為增量數(shù)據(jù)binlog
MYSQL入門導(dǎo)入全量備份
MYSQL入門
#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
MYSQL入門在bin.sql找到之前的delete語句,刪除
MYSQL入門
mysql -uroot -p <bin.sql
MYSQL入門九、確認(rèn)已恢復(fù)數(shù)據(jù)
MYSQL入門登錄mysql
MYSQL入門
#mysql -uroot -p'martin' backup
select * from number;
MYSQL入門
+----+---------------------+
| 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)
MYSQL入門恢復(fù)完成!以上就是本文的全部內(nèi)容,在操作數(shù)據(jù)庫時(shí)候要多加小心盡量避免誤操作,如果萬一遇到了,希望本文能夠幫助大家.
轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/5409.html
