MYSLQ 增量恢復學習及實踐

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

什么情況需要增量恢復？

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

增量恢復的必需要滿足的條件

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

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

增量恢復的思路：

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

下面模擬認為誤操作刪除數據庫后 通過增量恢復的過程

創建一個測試庫 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

插入數據

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)

 此時是全備時表里的數據

修改系統時間  到0點 刷新BINLOG后對www庫進行全備

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

查看是否生產全備文件

[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

將系統時間調整到10點后 再插入幾條數據

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表數據為

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)

進行恢復操作

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

如果不先備份 全備恢復后 又會寫入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進行解析 找出誤操作的那條語句 將該條語句進行刪除

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    #如不刪除恢復的時候誤操作會被一同恢復到數據庫中 那就又回到原點白忙活了！

/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

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

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

3、恢復全備數據

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

查看一庫，到全備之前的數據已經恢復了

[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、恢復增量數據 也就是通過bin-log 文件恢復 0點 到出問題之前的數據

將 解析成SQL文件的bin-log 導入數據庫

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

查看表 到誤操作之前的數據都找回來了。

[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、數據庫里如有多個庫  -d 指定庫

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

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

3、多個binlog 文件要按順序恢復。

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

4、如果不是drop，而是updata破壞數據，解決起來就復雜，一般需要停或禁止庫被應用服務寫入，然后再恢復。

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

a.把原來指定oldboy庫導出表結構，恢復好測試庫，然后把oldboy_bin.sql語句恢復到測試庫，然后再用mysqkdump導出需要的單個表，恢復到已經恢復了全備的正式庫上。

b.www_bin.sql 最小按庫分，那么可以grep”表名”把old_bin.sql所有對于該表的記錄過濾出來。恢復到已經恢復了全備的正式庫

mysqlbinlog -d oldboy >bin.sql 后，

grep 要恢復的表 bin.sql >a.sql