首页 > MySQL > mysql qpress压缩备份恢复
2016
05-04

mysql qpress压缩备份恢复

1.估计大家都会gzip压缩方法,备份正常,但后来恢复的时候,发现命中bug,报错如下:

InnoDB: Page [page id: space=9494, page number=27743946] log sequence number 2332453520996 is in the future! Current system log sequence number 2332453312855.
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
InnoDB: Page [page id: space=9494, page number=27735123] log sequence number 2332456427612 is in the future! Current system log sequence number 2332453312855.
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.

InnoDB: Apply batch completed
InnoDB: xtrabackup: Last MySQL binlog file position 513900963, file name mysql-bin.000436
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Starting in background the rollback of uncommitted transactions
InnoDB: Rolling back trx with id 661140886, 1 rows to undo
InnoDB: Setting file ‘./ibtmp1’ size to 12 MB. Physically writing the file full; Please wait …
InnoDB: Rollback of trx with id 661140886 completed
InnoDB: Rolling back trx with id 661140885, 1 rows to undo
InnoDB: Rollback of trx with id 661140885 completed
InnoDB: Rolling back trx with id 661140884, 1 rows to undo
InnoDB: Rollback of trx with id 661140884 completed
InnoDB: Rollback of non-prepared transactions completed
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.10 started; log sequence number 2332453312097
InnoDB: page_cleaner: 1000ms intended loop took 341221ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
InnoDB: not started
InnoDB: xtrabackup: Last MySQL binlog file position 513900963, file name mysql-bin.000436

xtrabackup: error: The transaction log file is corrupted.
xtrabackup: error: The log was not applied to the intended LSN!

2.本文采用了qpress的压缩方式,并发备份,速度比原来要快5倍,压缩比率比gzip稍微低一些,gzip约为117/775,qpress 约为157/775

一、备份机制
一周全备份一次
每天做一次增备(全备份当天除外)

二、备份
2.1全备:
innobackupex –defaults-file=/etc/my.cnf –use-memory=10G –user=root –password=root –compress –compress-threads=8 –parallel=4 –stream=xbstream /mysql/full –extra-lsndir=/tmp  > /mysql/full/backup.xbstream

若需要传到其他主机,可使用下述命令:
innobackupex –defaults-file=/etc/my.cnf –use-memory=10G –user=root –password=root –compress –compress-threads=8 –parallel=4 –stream=xbstream /mysql/full –extra-lsndir=/tmp |ssh  root@180.153.62.136″ xbstream -x -C /mysql/full”

2.2增量备份:–必须是在全备的基础上进行
innobackupex –defaults-file=/etc/my.cnf –use-memory=10G –user=root –password=root –compress –compress-threads=8 –parallel=4 –stream=xbstream  –incremental –incremental-basedir=/tmp –extra-lsndir=/tmp  /mysql/incre1> /mysql/incre1/backup_incre1.xbstream

三、备份恢复举例
当前环境:
mysql> use testdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+———————+
| Tables_in_testdb    |
+———————+
| fq_1                |
| milestat            |
| miletest            |
| runningrecord       |
| runningrecordlatest |
| t1                  |
| t2                  |
| t3                  |
| t4                  |
| t_timetest          |
| testrun             |
| testrun2            |
| v_1                 |
+———————+
13 rows in set (0.01 sec)

mysql> select * from t1;
+——+
| id   |
+——+
|    1 |
|    2 |
|    3 |
|    4 |
+——+
4 rows in set (0.04 sec)

1)全备:–并发数可自定义
innobackupex –defaults-file=/etc/my.cnf –use-memory=500m –user=root –password=root –compress –compress-threads=2 –parallel=2 –stream=xbstream /mysql/full –extra-lsndir=/tmp  > /mysql/full/backup.xbstream

[root@kafka1 full]# ll
总用量 9492
-rw-r–r–. 1 root root 9716168 5月   3 11:55 backup.xbstream

[root@kafka1 full]# cat /tmp/xtrabackup_checkpoints   –记录位置点的文件
backup_type = full-backuped
from_lsn = 0                      –开始点
to_lsn = 50779787                 –结束点
last_lsn = 50779796
compact = 0
recover_binlog_info = 0
[root@kafka1 full]#

说明:因为全备要拷贝走,因此需要额外的一份lsn保留下来,以便下次读取位置做增备

2)做一些操作:
mysql> insert into t1 values (5),(6),(7),(8);
Query OK, 4 rows affected (0.33 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> select * from t1;
+——+
| id   |
+——+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
+——+
8 rows in set (0.00 sec)
mysql> drop table t4;
Query OK, 0 rows affected (0.10 sec)

3)第一次增量备份
innobackupex –defaults-file=/etc/my.cnf –use-memory=400m –user=root –password=root –compress –compress-threads=2 –parallel=2 –stream=xbstream  –incremental –incremental-basedir=/tmp –extra-lsndir=/tmp  /mysql/incre1> /mysql/incre1/backup_incre1.xbstream

说明:
–incremental-basedir 指向的是上一次备份的lsn所在目录;
–extra-lsndir 指向的是这一次备份的额外xtrabackup_checkpoints的存放目录;
/mysql/incre1 指向的是这次备份的存放目录。

[root@kafka1 incre1]# ll
总用量 488
-rw-r–r–. 1 root root 496194 5月   3 12:03 backup_incre1.xbstream

[root@kafka1 tmp]# cat xtrabackup_checkpoints    –名字跟之前一个相同,内容覆盖了
backup_type = incremental
from_lsn = 50779787           –开始点(上一次的结束点)
to_lsn = 50804287             –结束点
last_lsn = 50804296
compact = 0
recover_binlog_info = 0

4)再做一些操作
mysql> truncate table t1;
Query OK, 0 rows affected (0.14 sec)
5)第二次增量备份
innobackupex –defaults-file=/etc/my.cnf –use-memory=400m –user=root –password=root –compress –compress-threads=2 –parallel=2 –stream=xbstream  –incremental –incremental-basedir=/tmp –extra-lsndir=/tmp  /mysql/incre2> /mysql/incre2/backup_incre2.xbstream

[root@kafka1 mysql]# cd incre2
[root@kafka1 incre2]# ll
总用量 456
-rw-r–r–. 1 root root 465003 5月   3 13:38 backup_incre2.xbstream

[root@kafka1 incre2]# cat /tmp/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 50804287              –开始点
to_lsn = 50811553                –结束点
last_lsn = 50811562
compact = 0
recover_binlog_info = 0

[root@kafka1 incre2]# cd /mysql
[root@kafka1 mysql]# du -sh *
9.3M full
488K incre1
456K incre2
[root@kafka1 mysql]# ll
总用量 0
drwxrwxrwx. 2 root root 28 5月   3 11:55 full
drwxrwxrwx. 2 root root 35 5月   3 12:02 incre1
drwxrwxrwx. 2 root root 35 5月   3 13:38 incre2

6)模拟灾难
磁盘故障,整个数据丢失,在新机器上装好mysql后(目录相同,进行初始化)

[root@kafka1 incre1]# service mysql57 stop
Shutting down MySQL… SUCCESS!
[root@kafka1 incre1]# cd /usr/local/mysql
[root@kafka1 mysql]# ll
总用量 304
drwxr-xr-x.  2 mysql mysql   4096 11月 30 03:54 bin
-rw-r–r–.  1 mysql mysql  17987 11月 30 03:16 COPYING
drwxr-xr-x.  7 mysql mysql   4096 5月   3 13:43 data
drwxr-xr-x.  6 mysql mysql   4096 4月  11 14:26 data_old
drwxr-xr-x.  2 mysql mysql     52 11月 30 03:54 docs
drwxr-xr-x.  3 mysql mysql   4096 11月 30 03:54 include
-rw-r–r–.  1 mysql mysql 108090 11月 30 03:26 INSTALL-BINARY
drwxr-xr-x.  5 mysql mysql   4096 11月 30 03:54 lib
drwxr-xr-x.  4 mysql mysql     28 11月 30 03:54 man
-rw-r—–.  1 mysql mysql 141349 5月   3 13:43 mysqlerr.log
-rw-r–r–.  1 mysql mysql   2478 11月 30 03:16 README
drwxr-xr-x. 28 mysql mysql   4096 11月 30 03:54 share
drwxr-xr-x.  2 mysql mysql   4096 11月 30 03:54 support-files
[root@kafka1 mysql]# mv data data_bak     —整个data目录移走
[root@kafka1 mysql]# mkdir data
[root@kafka1 mysql]# chown mysql:mysql data  –新建一个空的,准备恢复

7)解压全备包
[root@kafka1 full]# xbstream -x <backup.xbstream  -C /tmp/test
[root@kafka1 full]# cd /tmp/test
[root@kafka1 test]# ll
总用量 3568
-rw-r—–. 1 root root     407 5月   3 14:58 backup-my.cnf.qp
-rw-r—–. 1 root root     308 5月   3 14:58 ib_buffer_pool.qp
-rw-r—–. 1 root root 3594072 5月   3 14:58 ibdata1.qp
drwxr-x—. 2 root root    4096 5月   3 14:58 mysql
drwxr-x—. 2 root root    8192 5月   3 14:58 performance_schema
drwxr-x—. 2 root root    4096 5月   3 14:58 sakila
drwxr-x—. 2 root root    8192 5月   3 14:58 sys
drwxr-x—. 2 root root    4096 5月   3 14:58 testdb
-rw-r—–. 1 root root     115 5月   3 14:58 xtrabackup_checkpoints
-rw-r—–. 1 root root     540 5月   3 14:58 xtrabackup_info.qp
-rw-r—–. 1 root root     369 5月   3 14:58 xtrabackup_logfile.qp

8)解压增量包
[root@kafka1 incre1]# xbstream -x <backup_incre1.xbstream
[root@kafka1 incre1]# ll
total 640
-rw-r–r–. 1 root root 496194 May  3 12:03 backup_incre1.xbstream
-rw-r—–. 1 root root    407 May  3 15:02 backup-my.cnf.qp
-rw-r—–. 1 root root    308 May  3 15:02 ib_buffer_pool.qp
-rw-r—–. 1 root root  79915 May  3 15:02 ibdata1.delta.qp
-rw-r—–. 1 root root     44 May  3 15:02 ibdata1.meta
drwxr-x—. 2 root root   4096 May  3 15:02 mysql
drwxr-x—. 2 root root   8192 May  3 15:02 performance_schema
drwxr-x—. 2 root root   4096 May  3 15:02 sakila
drwxr-x—. 2 root root   8192 May  3 15:02 sys
drwxr-x—. 2 root root   4096 May  3 15:02 testdb
-rw-r—–. 1 root root    120 May  3 15:02 xtrabackup_checkpoints
-rw-r—–. 1 root root    565 May  3 15:02 xtrabackup_info.qp
-rw-r—–. 1 root root    329 May  3 15:02 xtrabackup_logfile.qp

[root@kafka1 incre2]# xbstream -x <backup_incre2.xbstream
[root@kafka1 incre2]# ll
total 584
-rw-r–r–. 1 root root 465003 May  3 13:38 backup_incre2.xbstream
-rw-r—–. 1 root root    407 May  3 15:02 backup-my.cnf.qp
-rw-r—–. 1 root root    308 May  3 15:02 ib_buffer_pool.qp
-rw-r—–. 1 root root  56172 May  3 15:02 ibdata1.delta.qp
-rw-r—–. 1 root root     44 May  3 15:02 ibdata1.meta
drwxr-x—. 2 root root   4096 May  3 15:02 mysql
drwxr-x—. 2 root root   8192 May  3 15:02 performance_schema
drwxr-x—. 2 root root   4096 May  3 15:02 sakila
drwxr-x—. 2 root root   8192 May  3 15:02 sys
drwxr-x—. 2 root root   4096 May  3 15:02 testdb
-rw-r—–. 1 root root    120 May  3 15:02 xtrabackup_checkpoints
-rw-r—–. 1 root root    567 May  3 15:02 xtrabackup_info.qp
-rw-r—–. 1 root root    400 May  3 15:02 xtrabackup_logfile.qp

9)解压所有qp文件
9.1 安装
tar –xvf qpress-11-linux-x64.tar
cp qpress /usr/bin
chmod 777 qpress

9.2解压全备
[root@kafka1 test]# for f in `find ./ -iname “*\.qp”`; do qpress -dT2 $f  $(dirname $f) && rm -f $f; done
[root@kafka1 test]# ll
总用量 77880
-rw-r–r–. 1 root root      412 5月   3 15:14 backup-my.cnf
-rw-r–r–. 1 root root      300 5月   3 15:14 ib_buffer_pool
-rw-r–r–. 1 root root 79691776 5月   3 15:14 ibdata1
drwxr-x—. 2 root root     4096 5月   3 15:14 mysql
drwxr-x—. 2 root root     8192 5月   3 15:14 performance_schema
drwxr-x—. 2 root root     4096 5月   3 15:14 sakila
drwxr-x—. 2 root root     8192 5月   3 15:14 sys
drwxr-x—. 2 root root     4096 5月   3 15:14 testdb
-rw-r—–. 1 root root      115 5月   3 14:58 xtrabackup_checkpoints
-rw-r–r–. 1 root root      563 5月   3 15:14 xtrabackup_info
-rw-r–r–. 1 root root     2560 5月   3 15:14 xtrabackup_logfile
9.3解压增备1
[root@kafka1 incre1]# for f in `find ./ -iname “*\.qp”`; do qpress -dT2 $f  $(dirname $f) && rm -f $f; done
[root@kafka1 incre1]# ll
总用量 1220
-rw-r–r–. 1 root root 496194 5月   3 12:03 backup_incre1.xbstream
-rw-r–r–. 1 root root    412 5月   3 15:26 backup-my.cnf
-rw-r–r–. 1 root root    300 5月   3 15:26 ib_buffer_pool
-rw-r–r–. 1 root root 671744 5月   3 15:26 ibdata1.delta
-rw-r—–. 1 root root     44 5月   3 15:02 ibdata1.meta
drwxr-x—. 2 root root   4096 5月   3 15:26 mysql
drwxr-x—. 2 root root   8192 5月   3 15:26 performance_schema
drwxr-x—. 2 root root   8192 5月   3 15:26 sakila
drwxr-x—. 2 root root   8192 5月   3 15:26 sys
drwxr-x—. 2 root root   4096 5月   3 15:26 testdb
-rw-r—–. 1 root root    120 5月   3 15:02 xtrabackup_checkpoints
-rw-r–r–. 1 root root    612 5月   3 15:26 xtrabackup_info
-rw-r–r–. 1 root root   2560 5月   3 15:26 xtrabackup_logfile

9.4解压增备2
[root@kafka1 incre2]# for f in `find ./ -iname “*\.qp”`; do qpress -dT2 $f  $(dirname $f) && rm -f $f; done
[root@kafka1 incre2]# ll
总用量 1072
-rw-r–r–. 1 root root 465003 5月   3 13:38 backup_incre2.xbstream
-rw-r–r–. 1 root root    412 5月   3 15:10 backup-my.cnf
-rw-r–r–. 1 root root    300 5月   3 15:10 ib_buffer_pool
-rw-r–r–. 1 root root 557056 5月   3 15:10 ibdata1.delta
-rw-r—–. 1 root root     44 5月   3 15:02 ibdata1.meta
drwxr-x—. 2 root root   4096 5月   3 15:10 mysql
drwxr-x—. 2 root root   8192 5月   3 15:10 performance_schema
drwxr-x—. 2 root root   4096 5月   3 15:10 sakila
drwxr-x—. 2 root root   8192 5月   3 15:10 sys
drwxr-x—. 2 root root   4096 5月   3 15:10 testdb
-rw-r—–. 1 root root    120 5月   3 15:02 xtrabackup_checkpoints
-rw-r–r–. 1 root root    613 5月   3 15:10 xtrabackup_info
-rw-r–r–. 1 root root   2560 5月   3 15:10 xtrabackup_logfile

10)应用所有日志
10.1 应用全备日志
innobackupex –use-memory=400m –apply-log  –redo-only /tmp/test

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown…
InnoDB: Shutdown completed; log sequence number 50779805
InnoDB: Number of pools: 1
160503 15:23:35 completed OK!

10.2 应用增备日志1
innobackupex   –use-memory=400m –apply-log –redo-only  /tmp/test
–incremental-dir=/mysql/incre1

………………
160503 15:27:41 [01] Copying /mysql/incre1/performance_schema/db.opt to ./performance_schema/db.opt
160503 15:27:41 [01]        …done
160503 15:27:41 [01] Copying /mysql/incre1/performance_schema/cond_instances.frm to ./performance_schema/cond_instances.frm
160503 15:27:41 [01]        …done
160503 15:27:41 [00] Copying /mysql/incre1/xtrabackup_info to ./xtrabackup_info
160503 15:27:41 [00]        …done
160503 15:27:41 completed OK!

10.3 应用增备日志2
innobackupex   –use-memory=400m –apply-log  /tmp/test
–incremental-dir=/mysql/incre2

说明:其实就是将增备1和增备2中的所有操作日志应用到全备目录,那么此时的全备目录就是增备2的最新数据,直接使用这个全备目录还原即可。
最后一次的增备不带redo-only参数

11)将数据目录拷贝回data
11.1 可使用copy-back
innobackupex –defaults-file=/etc/my.cnf  –user=’root’ –password=’root’ –copy-back  /tmp/test

11.2 也可直接修改名称,mv过去
[root@kafka1 mysql]# cd  /tmp/test
[root@kafka1 test]# cp -r * ../data/
[root@kafka1 mysql]# chown -R mysql:mysql data
12)启动mysql
[root@kafka1 mysql]# service mysql57 start
Starting MySQL… SUCCESS!
[root@kafka1 mysql]# /usr/local/mysql/bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show databases;
+——————–+
| Database           |
+——————–+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| testdb             |
+——————–+
6 rows in set (0.08 sec)

mysql> use testdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+———————+
| Tables_in_testdb    |
+———————+
| fq_1                |
| milestat            |
| miletest            |
| runningrecord       |
| runningrecordlatest |
| t1                  |
| t2                  |
| t3                  |
| t_timetest          |
| testrun             |
| testrun2            |
| v_1                 |
+———————+
12 rows in set (0.00 sec)

mysql> select * from t1;
Empty set (0.00 sec)

验证可发现,所有备份的操作都在,恢复成功!

13)利用binlog恢复到当天最新数据(由于我测试环境没开binlog,因此后续没测试)
–获取最后一次增量的log position
cd /mysql/incre2
more xtrabackup_binlog_info
mysql-binlog.000453     774

–使用start-position 和stop-datetime  将日志追加到最新
mysqlbinlog /mysql/mysql-binlog/mysql-binlog.000453
–start-position=774 –stop-datetime=”2016-05-03 10:00:00” \
> |mysql -uroot -proot –P2021


留下一个回复

你的email不会被公开。