用Mysqldump實現全庫備份+binlog的數據還原

  隨著業務量的增長，數據庫也是成倍增長，原來一直使用的全庫備份不再適合現在的數據庫了，動輒就備份10G-20G,太占用磁盤空間，所以就考慮用更簡潔更快速更節省磁盤空間的備份方法，這就想到了使用binlog日志來進行備份和恢復，下面是具體實施的方法：

環境介紹：

操作系統：Centos 7.2

數據庫：Mysql 5.6

一.安裝Mysql和改配置文件

安裝就不具體介紹了，網上教程很多，配置文件需要添加以下選項：

vim /etc/my.cnf

log_bin = mysql-binlog    #開啟binlog日志功能,默認在mysql的datadir目錄下面

show variables like 'log_bin';    #進入Mysql查看binlog日志是否開啟

二.創建實驗數據

由于剛建的數據庫，日志中沒有數據，創建新的數據庫和表格來進行實驗

#創建t1庫

create database t1;

#創建tab1表

create table t1.tab1(id int primary key auto_increment,name varchar(20));

#插入兩條數據

insert into t1.tab1(name) values('zhangsan');

insert into t1.tab1(name) values('lisi');

三.進行全庫備份和日志備份

#進行全庫備份,并產生新日志

mysqldump -uroot -p123456 --flush-logs t1 > /opt/t1_`date +%Y%m%d`.sql

#備份日志文件，全庫備份前有幾個日志就備份幾個

cp /usr/local/mysql/binlog/mysql.bin.000001 /opt/

四.模擬刪除數據

delete from t1.tab1 where id=2;

#插入新數據

insert into t1.tab1(name) values('wangwu');

五.備份mysqldump之后的binlog日志文件

cp /usr/local/mysql/binlog/mysql.bin.000002 /opt/

六.用Mysqldump實現全庫備份+binlog的數據還原

1. mysql -uroot -p123456 tab1 < /opt/t1_20170626.sql  #還原刪除前的全部數據，這時候應該有兩條數據，zhangsan和lisi

2. mysqlbinlog -v /usr/local/mysql/binlog/mysql.bin.000002  #分析新開啟的binlog日志文件，里面誤操作的時間的起始位置和終止位置，只要跳過這一段時間即可

   

3. 從binlog恢復數據

   mysqlbinlog --stop-position=120 /opt/mysql.bin.000002|mysql -uroot -p123456

   mysqlbinlog --start-position=291 /opt/mysql.bin.000002|mysql -uroot -p123456

4. 查看恢復情況

   select * from t1.tab1;    #此時表中有三條數數據為恢復成功

手動備份恢復過程已經全部完成，下次說一說如何腳本化這個流程