MYSLQ 增量恢復(fù)學(xué)習(xí)及實(shí)踐

MYSQL的架構(gòu)一般都是一主多從 或是雙主高可用模式，物理故障不需要增量恢復(fù)

什么情況需要增量恢復(fù)？

一般是由人為引起的誤操作才需要增量恢復(fù)。

增量恢復(fù)的必需要滿足的條件

1）開啟MYSQL log-bin 日志功能

2）存在一份全備加上全備之后的時刻到出問題時刻的所有增量binlog 文件備份。

增量恢復(fù)的思路：

先恢復(fù)全量，然后把全備時刻點(diǎn)以后的增量日志，按順序恢復(fù)成SQL文件，然后把文件中有問題的SQL語句刪除（也可通過時間和位置點(diǎn)），再恢復(fù)到數(shù)據(jù)庫。

下面模擬認(rèn)為誤操作刪除數(shù)據(jù)庫后 通過增量恢復(fù)的過程

創(chuàng)建一個測試庫 WWW 和 test  測試表

create database www character set utf8 collate utf8_general_ci;use www;CREATE TABLE `test` (  `id` int(4) NOT NULL,  `name` varchar(16) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入數(shù)據(jù)

insert test values(1,'xiaoming'),(2,'xiaozhang'),(3,'www'),(4,'koala');

select * from test;+----+-----------+| id | name      |+----+-----------+|  1 | xiaoming  ||  2 | xiaozhang ||  3 | www       ||  4 | koala     |+----+-----------+4 rows in set (0.00 sec)

 此時是全備時表里的數(shù)據(jù)

修改系統(tǒng)時間  到0點(diǎn) 刷新BINLOG后對www庫進(jìn)行全備

mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B -F -x -R --master-data=2 www|gzip >/server/backup/www_$(date +%F).sql.gz

查看是否生產(chǎn)全備文件

[root@db03 backup]# lltotal 8drwxr-xr-x 2 root root 4096 Jul 13 02:18 tp-rw-r--r-- 1 root root  878 Jul 15 00:01 www_2016-07-15.sql.gz

將系統(tǒng)時間調(diào)整到10點(diǎn)后 再插入幾條數(shù)據(jù)

mysql> insert test values(5,'hahaha'),(6,'changsha'),(7,'bbs');Query OK, 3 rows affected (0.00 sec)Records: 3  Duplicates: 0  Warnings: 0

此時test表數(shù)據(jù)為

mysql> select * from test;+----+-----------+| id | name      |+----+-----------+|  1 | xiaoming  ||  2 | xiaozhang ||  3 | www       ||  4 | koala     ||  5 | hahaha    ||  6 | changsha  ||  7 | bbs       |+----+-----------+7 rows in set (0.00 sec)

開始模擬人為誤操作將www 庫刪除

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || blog               || mysql              || oldboy             || oldboy_gbk         || oldgril            || performance_schema || xiaowan            |+--------------------+8 rows in set (0.00 sec)

進(jìn)行恢復(fù)操作

1、首先查看BINLOG位置 將全備之后的binlog 文件全部備份出來 

如果不先備份 全備恢復(fù)后 又會寫入LOG BIN 文件

-rw-r--r-- 1 root root  878 Jul 15 00:01 www_2016-07-15.sql.gz[root@db03 backup]# gzip -d www_2016-07-15.sql.gz [root@db03 backup]# grep CHANGE www_2016-07-15.sql-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000137', MASTER_LOG_POS=107;-rw-r----- 1 root root  150 Jul 15 00:01 mysql-bin.000135-rw-r----- 1 root root  150 Jul 15 10:10 mysql-bin.000136-rw-r----- 1 root root  397 Jul 15 10:09 mysql-bin.000137

2、對bin-log進(jìn)行解析 找出誤操作的那條語句 將該條語句進(jìn)行刪除

mysqlbinlog -d www mysql-bin.000135 mysql-bin.000136 mysql-bin.000137 >>wwwbin.sql

[root@db03 backup]# vim wwwbin.sql 

# at 318

#160715 10:06:12 server id 1  end_log_pos 397   Query   thread_id=17    exec_time=0     error_code=0

SET TIMESTAMP=1468548372/*!*/;

drop database www    #如不刪除恢復(fù)的時候誤操作會被一同恢復(fù)到數(shù)據(jù)庫中 那就又回到原點(diǎn)白忙活了！

/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

3、恢復(fù)全備數(shù)據(jù)

[root@db03 backup]# mysql -uroot -p123456 -S /data/3306/mysql.sock <www_2016-07-13.sql

查看一庫，到全備之前的數(shù)據(jù)已經(jīng)恢復(fù)了

[root@db03 backup]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "use www;select * from test;"+----+-----------+| id | name      |+----+-----------+|  1 | xiaoming  ||  2 | xiaozhang ||  3 | www       ||  4 | koala     |+----+-----------+

4、恢復(fù)增量數(shù)據(jù) 也就是通過bin-log 文件恢復(fù) 0點(diǎn) 到出問題之前的數(shù)據(jù)

將 解析成SQL文件的bin-log 導(dǎo)入數(shù)據(jù)庫

[root@db03 backup]# mysql -uroot -p123456 -S /data/3306/mysql.sock www<wwwbin.sql

查看表 到誤操作之前的數(shù)據(jù)都找回來了。

[root@db03 backup]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "use www;select * from test;"+----+-----------+| id | name      |+----+-----------+|  1 | xiaoming  ||  2 | xiaozhang ||  3 | www       ||  4 | koala     ||  5 | hahaha    ||  6 | changsha  ||  7 | bbs       |+----+-----------+

要注意的問題

1、數(shù)據(jù)庫里如有多個庫  -d 指定庫

mysqlbinlog -d www mysql-bin.000135 mysql-bin.000136 mysql-bin.000137 >>wwwbin.sql

2、如果是重要的庫出問題，那么最好停庫或禁止庫被應(yīng)用服務(wù)寫入，然后在恢復(fù)（iptables處理）。如果通過host解析的，注釋解析文件記錄，用戶中心（接口停掉）。

3、多個binlog 文件要按順序恢復(fù)。

mysqlbinlog -d www 01 02 03 04>bin.sql 或者for 循環(huán)讀取順序很重要。

4、如果不是drop，而是updata破壞數(shù)據(jù)，解決起來就復(fù)雜，一般需要?；蚪箮毂粦?yīng)用服務(wù)寫入，然后再恢復(fù)。

如果是刪除了某個庫的某張表 則需要從bin-log 中分離出表的SQL語句的思路：

a.把原來指定oldboy庫導(dǎo)出表結(jié)構(gòu)，恢復(fù)好測試庫，然后把oldboy_bin.sql語句恢復(fù)到測試庫，然后再用mysqkdump導(dǎo)出需要的單個表，恢復(fù)到已經(jīng)恢復(fù)了全備的正式庫上。

b.www_bin.sql 最小按庫分，那么可以grep”表名”把old_bin.sql所有對于該表的記錄過濾出來?；謴?fù)到已經(jīng)恢復(fù)了全備的正式庫

mysqlbinlog -d oldboy >bin.sql 后，

grep 要恢復(fù)的表 bin.sql >a.sql