innobackupex+binlog備份測試實驗

全備

原始數據：test庫

開始全備：

備份目錄/backup/full/20191126

innobackupex ?--socket=/tmp/mysql.sock ?--user=root --password=123456 ?/backup/full/20191126 ?--no-timestamp ?--no-lock ?--throttle=100

--no-timestamp 不使用時間戳生成目錄，默認備份目錄是一個時間戳目錄

--throttle 每秒IO

--no-lock 關閉表鎖，適用場景見innobackupex 幫助

xtrabackup_binlo_info記錄了二進制日志的位置，我們二進制日志恢復需要使用到

開始做數據操作，創建一張表t，并添加2條數據

查看二進制日志：

[root@localhost?logs]#?mysqlbinlog?-vvv?mysql-bin.000016?
/*!50530?SET?@@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003?SET?@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER?/*!*/;
#?at?4
#191126?10:25:02?server?id?1??end_log_pos?123?CRC32?0xfaeb9c84??Start:?binlog?v?4,?server?v?5.7.26-log?created?191126?10:25:02?at?startup
#?Warning:?this?binlog?is?either?in?use?or?was?not?closed?properly.
ROLLBACK/*!*/;
BINLOG?'
/ozcXQ8BAAAAdwAAAHsAAAABAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAD+jNxdEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AYSc6/o=
'/*!*/;
#?at?123
#191126?10:25:02?server?id?1??end_log_pos?194?CRC32?0xdfc752d1??Previous-GTIDs
#?044c1532-f7c9-11e9-8ac4-00505637b733:1-3112
#?at?194??????#新的操作開始的位置
#191126?14:17:58?server?id?1??end_log_pos?259?CRC32?0x894b1a31??GTID????last_committed=0????sequence_number=1???rbr_only=no
SET?@@SESSION.GTID_NEXT=?'044c1532-f7c9-11e9-8ac4-00505637b733:3113'/*!*/;
#?at?259
#191126?14:17:58?server?id?1??end_log_pos?376?CRC32?0x4eb46aa2??Query???thread_id=22????exec_time=0?error_code=0
use?`test`/*!*/;
SET?TIMESTAMP=1574749078/*!*/;
SET?@@session.pseudo_thread_id=22/*!*/;
SET?@@session.foreign_key_checks=1,?@@session.sql_auto_is_null=0,?@@session.unique_checks=1,?@@session.autocommit=1/*!*/;
SET?@@session.sql_mode=1436549120/*!*/;
SET?@@session.auto_increment_increment=1,?@@session.auto_increment_offset=1/*!*/;
/*!\C?utf8?*//*!*/;
SET?@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET?@@session.lc_time_names=0/*!*/;
SET?@@session.collation_database=DEFAULT/*!*/;
create?table?t(id?int)?default?charset=utf8
/*!*/;
#?at?376
#191126?14:24:57?server?id?1??end_log_pos?441?CRC32?0x0f84381a??GTID????last_committed=1????sequence_number=2???rbr_only=yes
/*!50718?SET?TRANSACTION?ISOLATION?LEVEL?READ?COMMITTED*//*!*/;
SET?@@SESSION.GTID_NEXT=?'044c1532-f7c9-11e9-8ac4-00505637b733:3114'/*!*/;
#?at?441
#191126?14:24:57?server?id?1??end_log_pos?513?CRC32?0x6de752e9??Query???thread_id=23????exec_time=0?error_code=0
SET?TIMESTAMP=1574749497/*!*/;
BEGIN
/*!*/;
#?at?513
#191126?14:24:57?server?id?1??end_log_pos?557?CRC32?0x95e465d0??Table_map:?`test`.`t`?mapped?to?number?275
#?at?557
#191126?14:24:57?server?id?1??end_log_pos?602?CRC32?0x5e3fb325??Write_rows:?table?id?275?flags:?STMT_END_F

BINLOG?'
OcXcXRMBAAAALAAAAC0CAAAAABMBAAAAAAEABHRlc3QAAXQAAQMAAdBl5JU=
OcXcXR4BAAAALQAAAFoCAAAAABMBAAAAAAEAAgAB//4BAAAA/gIAAAAlsz9e
'/*!*/;
###?INSERT?INTO?`test`.`t`
###?SET
###???@1=1?/*?INT?meta=0?nullable=1?is_null=0?*/
###?INSERT?INTO?`test`.`t`
###?SET
###???@1=2?/*?INT?meta=0?nullable=1?is_null=0?*/
#?at?602
#191126?14:24:57?server?id?1??end_log_pos?633?CRC32?0x11f291f2??Xid?=?139
COMMIT/*!*/;
SET?@@SESSION.GTID_NEXT=?'AUTOMATIC'?/*?added?by?mysqlbinlog?*/?/*!*/;
DELIMITER?;
#?End?of?log?file
/*!50003?SET?COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530?SET?@@SESSION.PSEUDO_SLAVE_MODE=0*/;

刪庫操作：直接刪除mysql數據庫的data目錄下的所有數據，因為還原的時候需要這個目錄為空，正好就直接刪除這里

恢復操作：

prepare步驟：

innobackupex --default-file=/backup/full/20191126/backup-my.cnf --user=root --password=123456 --apply-log /backup/full/20191126/ ?

copy_back 步驟：

innobackupex --default-file=/backup/full/20191126/backup-my.cnf --user=root --copy-back /backup/full/20191126/

恢復后，需要修改mysql數據目錄下的屬主屬組為mysql（我是mysql用戶啟動，默認root用戶），重啟mysql

登陸驗證：

恢復到了原始表位置。

想恢復后面做的操作怎么辦?使用二進制日志恢復。這里使用binlog2sql工具（安裝略）。

通過備份的xtrabackup_binlog_info找到我們需要恢復的二進制日志的起止點。

python ?binlog2sql/binlog2sql.py -h227.0.0.1 -P3306 -uadmin -padmin ? --start-file='mysql-bin.000016' --start-position="259" --stop-position="602" > /tmp/tmp.sql

這里需要注意一下，tmp.log里的USE 語句有點錯誤，需要修改一下，另外可以把tmp.log改成tmp.sql。

恢復：

mysql -p < /tmp/tmp.sql

注意事項： mysql在做恢復的時候需要關閉二進制日志 set sql_log_bin=0，數據恢復后在開啟; 在恢復之前需要重啟mysql，而且還會報表t已經存在。

增量備份：

為了防止干擾，把/backup/full/20191126清空，并把最新的表數據作為原始數據。

當前原始數據5張表，t表2條數據（主要以t表做測試）

全備：

innobackupex --socket=/tmp/mysql.sock --user=root --password=123456 /backup/full/20191126 --no-timestamp --no-lock --throttle=100

做數據操作：刪除了2，新增了3，4，5

第一次增量備份

innobackupex --defaults-file=/etc/my.cnf ? --socket=/tmp/mysql.sock --user=root --password=123456 --no-timestamp --incremental-basedir=/backup/full/20191126 ?--incremental ?/backup/incre/20191126

第二次增量備份

數據庫再做一些操作

新增數據6，7

innobackupex --defaults-file=/etc/my.cnf ? --socket=/tmp/mysql.sock --user=root --password=123456 --no-timestamp --incremental-basedir=/backup/incre/20191126 ?--incremental ?/backup/incre/20191127?

差異增備，基于上一次的增備目錄

備份恢復：

prepare過程

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --apply-log --redo-only /backup/full/20191126

合并增量備份到初始備份：

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --apply-log --redo-only /backup/full/20191126 ?--incremental-dir=/backup/incre/20191126

copy-back過程：

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --copy-back /backup/full/20191126/

修改mysqldata目錄下數據的屬組屬主，重啟mysql

目前這里合并增量備份只合并了第一次的增量備份，故只有第一次的增量備份數據恢復了。

合并第二次的增量備份

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --apply-log --redo-only /backup/full/20191126 --incremental-dir=/backup/incre/20191127

copy-back：

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --copy-back /backup/full/20191126/

依然修改屬主屬組，重啟mysql。這次數據就恢復到第二次備份了

這里實際生產中可以先合并完增量備份后，再進行copy-back操作。

二進制日志恢復補充：

對于誤刪除操作的sql語句，可以使用--flashback來閃回sql語句。

例： 我操作刪除了7 這條數據，現在需要恢復，一樣的還是找到最近備份的二進制起止位置。

不加--flashback時：

?python ?binlog2sql/binlog2sql.py -h227.0.0.1 -P3306 -uroot -p123456 ? --start-file='mysql-bin.000019' ?--start-position="4" ?--stop-position="375" > /tmp/tmp.sql

可以看到你的刪除操作的SQL語句

添加--flashback:

python ?binlog2sql/binlog2sql.py -h227.0.0.1 -P3306 -uroot -p123456 --flashback ?--start-file='mysql-bin.000019' ?--start-position="4" ?--stop-position="375" > /tmp/tmp.sql

可以通過閃回的語句進行數據恢復。

當然，實際的情況可能比較這更加的復雜，比如我們有了誤刪除，但是也有新的正確的數據插入進來，故就不能一概的全部都用閃回，或者全都不使用閃回，那么得準確的找到誤刪除的點。