利用xtrabackup和mysqlbinlog完全恢复数据库

1. 说明

现在MySQL的在线备份一般都使用xtrabackup进行备份了,xtrabackup一般只备份到某个时间点,这个时间点之后的数据如果需要进行恢复,就需要用到MySQL的binlog了。
所以这里mysql的binlog就相当于Oracle里的归档日志了。
下面就从一个恢复例子里看一下如果使用xtrabackup和binlog来完全恢复数据

2. xtrabackup和备份脚本

脚本写得比较简单,利用xtrabackup全备数据库,然后压缩打包传到远程服务器。
在本地MySQL数据库服务器上不存备份文件

#!/bin/sh

####################################################
##
## xtrabackup script
##      backup data at remote host
##      you should config ssh trust
## 
## Usage:
##   1.You must check and modify the variable 
##     in variable section
##
## Recover Step:
##  in remote host
##  $ pwd
##  /data/backup
##  $ mkdir data
##  $ tar xvfz $BAKFILE -C ./data
##  $ innobackupex --defaults-file=/etc/my.cnf --user=root --password=$ROOTPWD --use-memory=4G --apply-log /data/backup/data
##  start mysql server
##
## PeiZhengfeng 2017.04.12
## hthorizion
##
## History : 
## 
## Platform for Linux
## 
####################################################

BAKDIR=/data/backup
BAKFILEPRE=data
BAKFILE=$BAKFILEPRE-`date +%Y%m%d`
LOGFILE=xtrabackup_log-`date +%Y%m%d`.log
REMOTE_HOST=192.168.1.46
REMOTE_BAKDIR=/data/backup
MYCNF=/etc/my.cnf
ROOTPWD=123456
SOCKET=/data/mysql/mysql.sock

innobackupex --defaults-file=$MYCNF --user=root --password=$ROOTPWD --socket=$SOCKET \
--stream=tar $BAKDIR 2>$BAKDIR/$LOGFILE \
|ssh $REMOTE_HOST "gzip ->$REMOTE_BAKDIR/$BAKFILE.tar.gz"

# check backup log
CHECKOK=`tail -1 $BAKDIR/$LOGFILE | grep "completed OK\!" | wc -l`
if [ $CHECKOK -ne 1 ]
then
  echo "[ WARNING ] Backup failed!"
  exit
fi

# clean backup piece, delete 14 day before
ssh $REMOTE_HOST "find $REMOTE_BAKDIR/$BAKFILEPRE* -mtime +14 -type f -maxdepth 1 | xargs rm -rf {}"

3. 当前备份信息

在192.168.1.46异机上

# ls -l /data/backup
total 1044
-rw-rw-r--. 1 mysql mysql 531970 Apr 12 15:55 data-20170412.tar.gz
-rw-rw-r--. 1 mysql mysql 531971 Apr 13 15:55 data-20170413.tar.gz

4. 模拟备份后原库生成了数据

最新的备份是4月13日下午15:55分备份的,那就拿这个备份来进行恢复。由于是测试库,原库没有数据生成,我们先在原库修改点数据

root@localhost[(none)]> use test
Database changed
root@localhost[test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| books          |
| checkpoint     |
| checkpoint_lox |
| tbx1           |
+----------------+
4 rows in set (0.00 sec)

root@localhost[test]> insert into books values(8,'ttt');
Query OK, 1 row affected (0.06 sec)

root@localhost[test]> insert into books values(101, 'ttt');
Query OK, 1 row affected (0.02 sec)

root@localhost[test]> select * from books;
+-----+------+
| id  | name |
+-----+------+
|   1 | aa   |
|   2 | bb   |
|   3 | cc   |
|   8 | ttt  |
|  10 | cc   |
|  11 | dd   |
|  12 | ee   |
|  13 | hh   |
|  14 | e    |
|  15 | f    |
| 101 | ttt  |
+-----+------+
11 rows in set (0.00 sec)

5. 恢复备份

在192.168.1.46异机上,恢复 data-20170413.tar.gz这个备份集

$ mkdir data
$ tar xvfz data-20170413.tar.gz -C ./data

准备/etc/my.cnf文件,可以使用原库的my.cnf文件,但是注意修改以下几项:

[mysqld]
socket                                = /data/backup/data/mysql.sock
datadir                               = /data/backup/data

去掉
log_bin                               = /data/logbin/master-bin
log-bin-index                         = /data/logbin/master-bin.index

[client]
socket                                = /data/backup/data/mysql.sock

在异机恢复库上就不要开log_bin和log-bin-index了,其它参数可以与原库一致
下一步就是apply-log

$ innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --use-memory=4G --apply-log /data/backup/data
170414 08:33:56 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

innobackupex version 2.4.5 based on MySQL server 5.7.13 Linux (x86_64) (revision id: e41c0be)
xtrabackup: cd to /data/backup/data
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2760424)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 8388608
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 4294967296 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 4G, instances = 1, chunk size = 128M
InnoDB: Completed initialization of buffer pool
InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 2760424
InnoDB: Doing recovery: scanned up to log sequence number 2760433 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 2760433 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 718, file name master-bin.000017
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.13 started; log sequence number 2760433
InnoDB: xtrabackup: Last MySQL binlog file position 718, file name master-bin.000017

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2760461
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 4
xtrabackup:   innodb_log_file_size = 104857600
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 4G, instances = 1, chunk size = 128M
InnoDB: Completed initialization of buffer pool
InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
InnoDB: Setting log file ./ib_logfile101 size to 100 MB
InnoDB: Progress in MB:
 100
InnoDB: Setting log file ./ib_logfile1 size to 100 MB
InnoDB: Progress in MB:
 100
InnoDB: Setting log file ./ib_logfile2 size to 100 MB
InnoDB: Progress in MB:
 100
InnoDB: Setting log file ./ib_logfile3 size to 100 MB
InnoDB: Progress in MB:
 100
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=2760461
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 2760716
InnoDB: Doing recovery: scanned up to log sequence number 2760725 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 2760725 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 718, file name master-bin.000017
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.13 started; log sequence number 2760725
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2760744
170414 08:34:04 completed OK!

由于是异机恢复,就可以不用copy-back数据文件了,直接打开数据库即可

# service mysql.server start
Starting MySQL.Logging to '/data/backup/data/test2.err'.
.                                                          [  OK  ]

root@localhost[(none)]> use test
Database changed
root@localhost[test]> select * from books;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
| 10 | cc   |
| 11 | dd   |
| 12 | ee   |
| 13 | hh   |
| 14 | e    |
| 15 | f    |
+----+------+
9 rows in set (0.02 sec)

6. 利用binlog增量恢复

检查xtrabackup备份完成时,binlog日志位置

# cat xtrabackup_binlog_info
master-bin.000018       194     05d54147-c68e-11e6-9c5a-001a4a1ee9ff:1-117

从这里看出,备份完成时,binlog位置为:master-bin.000018 194,我们只需要从这个位置往后增量应用日志就可以了。
将这个binlog和后面的日志全部复制到异机,检查binary logs,发现最后一个日志就是18,后面没有其它日志了,所以这里只需要把master-bin.000018复制到异机

root@localhost[(none)]> show binary logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000003 |         0 |
| master-bin.000004 |         0 |
| master-bin.000005 |         0 |
| master-bin.000006 |         0 |
| master-bin.000007 |         0 |
| master-bin.000008 |         0 |
| master-bin.000009 |         0 |
| master-bin.000010 |      1278 |
| master-bin.000011 |       242 |
| master-bin.000012 |       242 |
| master-bin.000013 |       242 |
| master-bin.000014 |     17505 |
| master-bin.000015 |       479 |
| master-bin.000016 |     37422 |
| master-bin.000017 |       741 |
| master-bin.000018 |       720 |
+-------------------+-----------+
16 rows in set (0.00 sec)

复制binlog

[mysql@test1 logbin]$ scp master-bin.000018 192.168.1.46:/data/backup/data

在异机上增量恢复

[root@test2 data]# mysqlbinlog master-bin.000018 --start-position=194|mysql -u root -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.

检查数据

root@localhost[(none)]> use test
Database changed
root@localhost[test]> select * from books;
+-----+------+
| id  | name |
+-----+------+
|   1 | aa   |
|   2 | bb   |
|   3 | cc   |
|   8 | ttt  |
|  10 | cc   |
|  11 | dd   |
|  12 | ee   |
|  13 | hh   |
|  14 | e    |
|  15 | f    |
| 101 | ttt  |
+-----+------+
11 rows in set (0.00 sec)

与主库相比,完全一致了

关于紫砂壶

感悟技术人生
此条目发表在MySQL分类目录,贴了标签。将固定链接加入收藏夹。