xtrabackup的初使用

最新推荐文章于 2024-06-07 12:42:29 发布

Silencecxq

最新推荐文章于 2024-06-07 12:42:29 发布

阅读量4.1k

点赞数 2

分类专栏： MySQL 文章标签：数据备份 MySQL

本文链接：https://blog.csdn.net/chen1511XIQIANG/article/details/69945098

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

这里参考了jeanron大师的文章：http://chuansong.me/n/1696438151334
经过前边恶心的安装以后，接下来尝试一番xtrabackup的备份功能。
xtrabackup主要是用于热备份innodb,或者是 xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；
innobackupex是将xtrabackup进行封装的perl脚本，可以备份和恢复MyISAM表以及数据表结构。
**

1、备份的解析

**
从innobackupex –help命令可以看到innobackupex 的参数有很多，下面尝试做一个全备。

[root@cxqtest ~]# cd /data/backup/0408/
[root@cxqtest 0408]# innobackupex -S /data/3306/mysql.sock /data/backup/0408/ --no-timestamp --no-lock --throttle=100
170304 11:00:41 innobackupex: Starting the backup operation

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

170304 11:00:42  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/data/3306/mysql.sock' (using password: NO).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/data/3306/mysql.sock','',...) failed: Access denied for user 'root'@'localhost' (using password: NO) at - line 1314
170304 11:00:43 Connecting to MySQL server host: localhost, user: not set, password: not set, port: 3306, socket: /data/3306/mysql.sock
Failed to connect to MySQL server: Access denied for user 'root'@'localhost' (using password: NO).
[root@cxqtest 0408]# innobackupex -S /data/3306/mysql.sock /data/backup/0408/ --no-timestamp --no-lock --throttle=100 -uroot -p
innobackupex: [ERROR] innobackupex: option '-p' requires an argument

报错了，原因是没有写上用户名密码

[root@cxqtest 0408]# innobackupex -S /data/3306/mysql.sock /data/backup/0408/ --no-timestamp --no-lock --throttle=100 -uroot -pmysql123
170304 11:01:24 innobackupex: Starting the backup operation

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

170304 11:01:24  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/data/3306/mysql.sock' as 'root'  (using password: YES).
170304 11:01:24  version_check Connected to MySQL server
170304 11:01:24  version_check Executing a version check against the server...
170304 11:01:24  version_check Done.
170304 11:01:24 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /data/3306/mysql.sock
Using server version 5.6.30-log
innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/3306/data
xtrabackup: open files limit requested 65535, set to 65535
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:1G:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 524288000
xtrabackup: using O_DIRECT
InnoDB: Number of pools: 1
170304 11:01:25 >> log scanned up to (1626008)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 1 for mysql/innodb_table_stats, old maximum was 0
170304 11:01:25 [01] Copying ./ibdata1 to /data/backup/0408/ibdata1
170304 11:01:26 >> log scanned up to (1626008)
170304 11:01:27 >> log scanned up to (1626008)
.....
xtrabackup: The latest check point (for incremental): '1626008'
xtrabackup: Stopping log copying thread.
.170304 11:02:00 >> log scanned up to (1626008)

170304 11:02:01 Backup created in directory '/data/backup/0408/'
MySQL binlog position: filename 'mybinlog.000006', position '191', GTID of the last change '82f3c6ed-007a-11e7-9b50-000c298ee31c:1-7'
170304 11:02:01 [00] Writing backup-my.cnf
170304 11:02:01 [00]        ...done
170304 11:02:01 [00] Writing xtrabackup_info
170304 11:02:01 [00]        ...done
xtrabackup: Transaction log of lsn (1626008) to (1626008) was copied.
170304 11:02:01 completed OK!

经过一段时间的备份，可以看到备份完成了
让我们查看一下备份文件如下：

[root@cxqtest 0408]# du -sh 
1.1G    .
[root@cxqtest 0408]# du -sh ./*
4.0K    ./backup-my.cnf
1.1G    ./ibdata1
1.7M    ./mysql
636K    ./performance_schema
4.0K    ./test
4.0K    ./xtrabackup_binlog_info
4.0K    ./xtrabackup_checkpoints
4.0K    ./xtrabackup_info
4.0K    ./xtrabackup_logfile

可以看到备份了my.cnf，ibdata1，各种数据库，这时候还多出来几个xtrabackup打头的文件
查看这几个文件的内容如下：
–xtrabackup_binlog_info文件记录了对应当下binlog的信息

[root@cxqtest 0408]# more xtrabackup_binlog_info
more xtrabackup_binlog_info
mybinlog.000006 191 82f3c6ed-007a-11e7-9b50-000c298ee31c:1-7

–xtrabackup_checkpoints记录了备份的类型是全备，是从0到1626008的最新的全备

[root@cxqtest 0408]# more xtrabackup_checkpoints
backup_type = full-backupedf
rom_lsn = 0
to_lsn = 1626008
last_lsn = 1626008
compact = 0
recover_binlog_info = 0

–xtrabackup_info记录的是命令的信息

[root@cxqtest 0408]# more xtrabackup_info
uuid = f0083067-0086-11e7-8435-000c298ee31c
name = innobackupextool
command = -S /data/3306/mysql.sock /data/backup/0408/ --no-timestamp --no-lock --throttle=100 -uroot -pmysql123
version = 2.4.6
ibbackup_version = 2.4.6
server_version = 5.6.30
logstart_time = 2017-03-04 11:01:24
end_time = 2017-03-04 11:02:01
lock_time = 1488596521
binlog_pos = filename 'mybinlog.000006', position '191', 
GTID of the last change '82f3c6ed-007a-11e7-9b50-000c298ee31c:1-7'
innodb_from_lsn = 0
innodb_to_lsn = 1626008
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N

–xtrabackup_logfile 看到logfile无法用more查看，是一个二进制日志文件格式，所以使用strings进行查看如下：
记录的是备份的时间

[root@cxqtest 0408]# more xtrabackup_logfile
--More--(88%)

?庰掟栶氿烉︷
[root@cxqtest 0408]# XshellXshellXshellXshellXshellXshellXshellXshellXshellXshell -bash: XshellXshellXshellXshellXshellXshellXshellXshellXshellXshell: command not found
[root@cxqtest 0408]#
 [root@cxqtest 0408]# strings xtrabackup_log
 filextrabkup 170304 11:01:25

2、全备恢复测试

数据的恢复还是使用innobackupex这个工具
这里的数据恢复分为两个步骤，prepare和还原恢复，prepare意义是如果我们备份数据的时候，存在未提交的事务，但是数据却存在于备份中，这样就是一个数据不一致的状态，在启动数据库的时候需要走一个前滚，然后是一个回滚的操作。这个体现主要就在于logfile和ibdata。是使用apply-log这个选项实现的。
前提需要先将之前的目录删除，停掉mysql服务。

[root@cxqtest 0408]# /etc/init.d/mysqld stop
Shutting down MySQL...                                     [  OK  ]
[root@cxqtest 0408]# 
[root@cxqtest 0408]# cd 
[root@cxqtest ~]# 
[root@cxqtest 3306]# ls
data
[root@cxqtest 3306]# mv data data.bak
[root@cxqtest 3306]# ls
data.bak
[root@cxqtest 3306]# mkdir data

可以使用如下命令进行恢复操作：
第一步：执行innobackupex –defaults-file=/data/backup/0408/backup-my.cnf -uroot -pmysql123 –apply-log /data/backup/0408/

[root@cxqtest 0408]# innobackupex --defaults-file=/data/backup/0408/backup-my.cnf -uroot -pmysql123 --apply-log /data/backup/0408/
170304 12:31:01 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.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
xtrabackup: cd to /data/backup/0408/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(1626008)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:1G: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:1G: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 104857600 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 = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence number 1625998 in the system tablespace does not match the log sequence number 1626008 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Doing recovery: scanned up to log sequence number 1626008 (0%)
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: Waiting for purge to start
InnoDB: 5.7.13 started; log sequence number 1626008

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1626027
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:1G:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 524288000
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 = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Setting log file ./ib_logfile101 size to 500 MB
InnoDB: Progress in MB:
 100 200 300 400 500
InnoDB: Setting log file ./ib_logfile1 size to 500 MB
InnoDB: Progress in MB:
 100 200 300 400 500
InnoDB: Setting log file ./ib_logfile2 size to 500 MB
InnoDB: Progress in MB:
 100 200 300 400 500
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=1626027
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 1626124
InnoDB: Doing recovery: scanned up to log sequence number 1626133 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 1626133 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
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: Waiting for purge to start
InnoDB: page_cleaner: 1000ms intended loop took 54592ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
InnoDB: 5.7.13 started; log sequence number 1626133
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1626152
170304 12:32:00 completed OK!

然后执行如下：

[root@cxqtest 0408]# innobackupex --defaults-file=/data/backup/0408/backup-my.cnf -uroot -pmysql123 --copy-back /data/backup/0408/
170304 12:32:42 innobackupex: Starting the copy-back operation

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

innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
Error: datadir must be specified.

报错要执行datadir，原因是我指定的参数文件是备份的backup-my.cnf内容如下：

[root@cxqtest 0408]# cat backup-my.cnf 

[mysqld]
innodb_checksum_algorithm=innodb
innodb_log_checksum_algorithm=innodb
innodb_data_file_path=ibdata1:1G:autoextend
innodb_log_files_in_group=3
innodb_log_file_size=524288000
innodb_fast_checksum=false
innodb_page_size=16384
innodb_log_block_size=512
innodb_undo_directory=.
innodb_undo_tablespaces=0
server_id=3306

redo_log_version=0

由于没有指定datadir导致报错，所以报错，这里直接指定原来的/etc/my.cnf

[root@cxqtest 3306]# innobackupex --defaults-file=/etc/my.cnf  -uroot -pmysql123 --copy-back /data/backup/0408/
170304 12:47:01 innobackupex: Starting the copy-back operation

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

innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
170304 12:47:01 [01] Copying ib_logfile0 to /data/3306/data/ib_logfile0
170304 12:47:22 [01]        ...done
170304 12:47:22 [01] Copying ib_logfile1 to /data/3306/data/ib_logfile1
170304 12:47:54 [01]        ...done
170304 12:47:55 [01] Copying ib_logfile2 to /data/3306/data/ib_logfile2
innobackupex: Error writing file '/data/3306/data/ib_logfile2' (Errcode: 28 - No space left on device)
[01] Error: copy_file() failed.

再一次报错：不过这次报错很明显
innobackupex: Error writing file ‘/data/3306/data/ib_logfile2’ (Errcode: 28 - No space left on device)
然后查看df -h:空间满了，无奈删除了一些其他的文件

[root@cxqtest 3306]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda2        36G   36G     0 100% /
tmpfs           1.2G   68K  1.2G   1% /dev/shm
/dev/sda1       194M   34M  150M  19% /boot
[root@cxqtest 0408]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda2        36G   28G  5.6G  84% /
tmpfs           1.2G   68K  1.2G   1% /dev/shm
/dev/sda1       194M   34M  150M  19% /boot

然后继续如上的步骤：

[root@cxqtest 3306]# innobackupex --defaults-file=/etc/my.cnf  -uroot -pmysql123 --copy-back /data/backup/0408/
170304 12:50:52 innobackupex: Starting the copy-back operation


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


innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
Original data directory /data/3306/data is not empty!

又报错！！！原因是第一次copyback回去了部分数据，目录不为空导致无法进行，然后将对应目录清空，继续进行如上copy-back操作

[root@cxqtest data]# innobackupex --defaults-file=/etc/my.cnf  -uroot -pmysql123 --copy-back /data/backup/0408/
170304 12:51:57 innobackupex: Starting the copy-back operation

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

innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
170304 12:51:57 [01] Copying ib_logfile0 to /data/3306/data/ib_logfile0
170304 12:52:18 [01]        ...done
170304 12:52:18 [01] Copying ib_logfile1 to /data/3306/data/ib_logfile1
170304 12:52:42 [01]        ...done
170304 12:52:42 [01] Copying ib_logfile2 to /data/3306/data/ib_logfile2
170304 12:53:06 [01]        ...done
170304 12:53:07 [01] Copying ibdata1 to /data/3306/data/ibdata1
170304 12:53:54 [01]        ...done
.......省略一堆的copy输出日志
170304 12:53:56 [01] Copying ./xtrabackup_info to /data/3306/data/xtrabackup_info
170304 12:53:56 [01]        ...done
170304 12:53:56 completed OK!
[root@cxqtest data]# 
[root@cxqtest data]# 
[root@cxqtest data]# ls
ibdata1  ib_logfile0  ib_logfile1  ib_logfile2  ibtmp1  mysql  performance_schema  test  xtrabackup_info
[root@cxqtest data]# pwd
/data/3306/data

终于将3306恢复回来了
然后将对应的属组修改回MySQL，默认是root。启动MySQL服务登陆正常

[root@cxqtest data]# ll
total 2596892
-rw-r----- 1 root root 1073741824 Mar  4 12:53 ibdata1
-rw-r----- 1 root root  524288000 Mar  4 12:52 ib_logfile0
-rw-r----- 1 root root  524288000 Mar  4 12:52 ib_logfile1
-rw-r----- 1 root root  524288000 Mar  4 12:53 ib_logfile2
-rw-r----- 1 root root   12582912 Mar  4 12:53 ibtmp1
drwxr-x--- 2 root root       4096 Mar  4 12:53 mysql
drwxr-x--- 2 root root       4096 Mar  4 12:53 performance_schema
drwxr-x--- 2 root root       4096 Mar  4 12:53 test
-rw-r----- 1 root root        606 Mar  4 12:53 xtrabackup_info
[root@cxqtest data]# chown -R mysql.mysql /data/3306/data
[root@cxqtest data]# ll
total 2596892
-rw-r----- 1 mysql mysql 1073741824 Mar  4 12:53 ibdata1
-rw-r----- 1 mysql mysql  524288000 Mar  4 12:52 ib_logfile0
-rw-r----- 1 mysql mysql  524288000 Mar  4 12:52 ib_logfile1
-rw-r----- 1 mysql mysql  524288000 Mar  4 12:53 ib_logfile2
-rw-r----- 1 mysql mysql   12582912 Mar  4 12:53 ibtmp1
drwxr-x--- 2 mysql mysql       4096 Mar  4 12:53 mysql
drwxr-x--- 2 mysql mysql       4096 Mar  4 12:53 performance_schema
drwxr-x--- 2 mysql mysql       4096 Mar  4 12:53 test
-rw-r----- 1 mysql mysql        606 Mar  4 12:53 xtrabackup_info
[root@cxqtest data]# /etc/init.d/mysqld start
Starting MySQL................                             [  OK  ]
[root@cxqtest data]# 
[root@cxqtest data]# 
[root@cxqtest data]# mysql -uroot -p
mysEnter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@cxqtest data]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.30-log Source distribution

Copyright (c) 2000, 2013, 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.

root@localhost:mysql.sock  12:56:04 [(none)]>
root@localhost:mysql.sock  12:56:05 [(none)]>
root@localhost:mysql.sock  12:56:05 [(none)]>
root@localhost:mysql.sock  12:56:05 [(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.14 sec)

root@localhost:mysql.sock  12:56:08 [(none)]>
root@localhost:mysql.sock  12:56:09 [(none)]>
root@localhost:mysql.sock  12:56:09 [(none)]>exit

3、增量的备份恢复操作

然后进行一下增量备份恢复的测试工作：
首先在test数据库下创建一张测试表：

root@localhost:mysql.sock  13:15:47 [(none)]>use test;
Database changed
root@localhost:mysql.sock  13:15:49 [test]>
root@localhost:mysql.sock  13:15:49 [test]>
root@localhost:mysql.sock  13:15:49 [test]>
root@localhost:mysql.sock  13:15:49 [test]>show tables;
Empty set (0.05 sec)

root@localhost:mysql.sock  13:15:56 [test]>
root@localhost:mysql.sock  13:15:57 [test]>
root@localhost:mysql.sock  13:15:57 [test]>create table t(id int ,name varchar(10));
Query OK, 0 rows affected (0.80 sec)

root@localhost:mysql.sock  13:16:28 [test]>insert into t values(1,'a'),(2,'b'),(3,'c'),(4,'d');
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0

root@localhost:mysql.sock  13:17:52 [test]>select * from t;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.00 sec)

由于之前已经做过了全备了，所以这里就直接可以做增量备份。
使用如下命令进行增量备份：

innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --incremental-basedir=/data/backup/0408/incr --incremental /data/backup/0408/incr

很不幸失败了：

[root@cxqtest incr]# innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --incremental-basedir=/data/backup/0408/incr --incremental /data/backup/0408/incr
170304 13:21:17 innobackupex: Starting the backup operation

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

xtrabackup: Error: cannot open /data/backup/0408/incr//xtrabackup_checkpoints
xtrabackup: error: failed to read metadata from /data/backup/0408/incr//xtrabackup_checkpoints

原因嘛，也给出来了

error: failed to read metadata from /data/backup/0408/incr//xtrabackup_checkpoints

当然，这个错误很明显，就是要找到之前全备的一个基点，也就是增量备的起始点lsn，这样才能进行增量备份，但是这里我直接指定了增量备份的basedir目录/data/backup/0408/incr，所以导致报了错，然后修改回来就没有问题了。这里引用jeanron大师的总结，很精辟：
原因就在于里面的一个关键文件 _checkpoints
使用增备得有一个参考点，从哪里开始，即从哪个LSN开始，这个LSN在指定的参数–incremental-basedir=/data/backup/0408/incr下不存在，因为这个是一个新目录，所以需要指向全库备份的目录。
然后修复后备份就没问题了，英为有了这个参考点LSN，所以需要要说明的是这个备份其实有累计增量和差异增量了。
这个怎么理解呢，比如周日做一个全备，周一做一个增备，周二做一个周日全备到周二的一个增备，这就是一个累计增量备份，而周三的时候做一个周二至周三数据变化的备份，就是一个差异增量备份。

[root@cxqtest incr]# innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --incremental-basedir=/data/backup/0408/ --incremental /data/backup/0408/incr
170304 13:21:52 innobackupex: Starting the backup operation

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

170304 13:21:53  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/data/3306/mysql.sock' as 'root'  (using password: YES).
170304 13:21:53  version_check Connected to MySQL server
170304 13:21:53  version_check Executing a version check against the server...
170304 13:21:53  version_check Done.
170304 13:21:53 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /data/3306/mysql.sock
Using server version 5.6.30-log
innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
incremental backup from 1626008 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/3306/data
xtrabackup: open files limit requested 65535, set to 65535
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:1G:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 524288000
xtrabackup: using O_DIRECT
InnoDB: Number of pools: 1
170304 13:21:53 >> log scanned up to (1634925)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 1 for mysql/innodb_table_stats, old maximum was 0
xtrabackup: using the full scan for incremental backup
170304 13:21:54 >> log scanned up to (1634925)
170304 13:21:55 [01] Copying ./ibdata1 to /data/backup/0408/incr/2017-03-04_13-21-52/ibdata1.delta
170304 13:21:55 >> log scanned up to (1634925)
.....
170304 13:22:14 [01]        ...done
......
170304 13:22:18 Finished backing up non-InnoDB tables and files
170304 13:22:18 [00] Writing xtrabackup_binlog_info
170304 13:22:18 [00]        ...done
170304 13:22:18 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1634925'
xtrabackup: Stopping log copying thread.
.170304 13:22:18 >> log scanned up to (1634925)

170304 13:22:18 Executing UNLOCK TABLES
170304 13:22:18 All tables unlocked
170304 13:22:18 Backup created in directory '/data/backup/0408/incr/2017-03-04_13-21-52/'
MySQL binlog position: filename 'mybinlog.000001', position '574', GTID of the last change 'd26bc1be-0096-11e7-9c08-000c298ee31c:1-2'
170304 13:22:18 [00] Writing backup-my.cnf
170304 13:22:18 [00]        ...done
170304 13:22:18 [00] Writing xtrabackup_info
170304 13:22:18 [00]        ...done
xtrabackup: Transaction log of lsn (1634925) to (1634925) was copied.
170304 13:22:18 completed OK!
[root@cxqtest incr]# ls
2017-03-04_13-21-17  2017-03-04_13-21-52

为了区别两次增量的不同，继续插入

root@localhost:mysql.sock  13:31:16 [test]>insert into t values(5,'e'),(6,'f');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@localhost:mysql.sock  13:31:36 [test]>select * from t;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
+------+------+
6 rows in set (0.00 sec)

再次进行基于上一次增量备份的增量备份：

[root@cxqtest incr]# innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --incremental-basedir=/data/backup/0408/incr/2017-03-04_13-21-52 --incremental /data/backup/0408/incr
170304 13:35:29 innobackupex: Starting the backup operation

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

170304 13:35:29  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/data/3306/mysql.sock' as 'root'  (using password: YES).
170304 13:35:29  version_check Connected to MySQL server
170304 13:35:29  version_check Executing a version check against the server...
170304 13:35:29  version_check Done.
170304 13:35:29 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /data/3306/mysql.sock
Using server version 5.6.30-log
innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
incremental backup from 1634925 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/3306/data
xtrabackup: open files limit requested 65535, set to 65535
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:1G:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 524288000
xtrabackup: using O_DIRECT
InnoDB: Number of pools: 1
170304 13:35:29 >> log scanned up to (1639553)
......
170304 13:35:49 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1639553'
xtrabackup: Stopping log copying thread.
.170304 13:35:49 >> log scanned up to (1639553)

170304 13:35:49 Executing UNLOCK TABLES
170304 13:35:49 All tables unlocked
170304 13:35:49 Backup created in directory '/data/backup/0408/incr/2017-03-04_13-35-29/'
MySQL binlog position: filename 'mybinlog.000001', position '821', GTID of the last change 'd26bc1be-0096-11e7-9c08-000c298ee31c:1-3'
170304 13:35:49 [00] Writing backup-my.cnf
170304 13:35:49 [00]        ...done
170304 13:35:49 [00] Writing xtrabackup_info
170304 13:35:49 [00]        ...done
xtrabackup: Transaction log of lsn (1639553) to (1639553) was copied.
170304 13:35:50 completed OK!
[root@cxqtest incr]# 
[root@cxqtest incr]# ls
2017-03-04_13-21-17  2017-03-04_13-21-52  2017-03-04_13-35-29

然后删除test数据库中的表t

root@localhost:mysql.sock  13:37:48 [(none)]>use test;
Database changed
root@localhost:mysql.sock  13:37:50 [test]>
root@localhost:mysql.sock  13:37:50 [test]>
root@localhost:mysql.sock  13:37:50 [test]>show tables;
+----------------+
| Tables_in_test |
+----------------+
| t              |
+----------------+
1 row in set (0.00 sec)

root@localhost:mysql.sock  13:37:53 [test]>drop table t;
Query OK, 0 rows affected (0.13 sec)

然后我们对比一下全备，第一次增备，第二次增备的checkpoints文件内容：

[root@cxqtest incr]# cat ../xtrabackup_checkpoints  ---全备
backup_type = full-prepared
from_lsn = 0
to_lsn = 1626008
last_lsn = 1626008
compact = 0
recover_binlog_info = 0
[root@cxqtest incr]# cat 2017-03-04_13-21-52/xtrabackup_checkpoints  --第一次增备
backup_type = incremental
from_lsn = 1626008
to_lsn = 1634925
last_lsn = 1634925
compact = 0
recover_binlog_info = 0
[root@cxqtest incr]# cat 2017-03-04_13-35-29/xtrabackup_checkpoints  --第二次增备
backup_type = incremental
from_lsn = 1634925
to_lsn = 1639553
last_lsn = 1639553
compact = 0
recover_binlog_info = 0

lsn值逐渐递增。
增量还原分为两个步骤
a.prepare

innobackupex –apply-log /path/to/BACKUP-DIR

此时数据可以被程序访问使用；可使用—use-memory选项指定所用内存以加快进度，默认100M；
b.recover

innobackupex –copy-back /path/to/BACKUP-DIR

从my.cnf读取datadir/innodb_data_home_dir/innodb_data_file_path等变量

先复制MyISAM表，然后是innodb表，最后为logfile；

开始合并操做：
第一次是全备的redo apply

[root@cxqtest incr]# innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --apply-log --redo-only /data/backup/0408/
170304 13:46:27 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.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
xtrabackup: cd to /data/backup/0408/
xtrabackup: This target seems to be already prepared.
InnoDB: Number of pools: 1
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:1G:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 524288000
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:1G:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 524288000
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 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 = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1626237
InnoDB: Number of pools: 1
170304 13:46:28 completed OK!

第二次是第一次增备的redo apply

[root@cxqtest incr]# innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --apply-log --redo-only /data/backup/0408/ --incremental-dir=/data/backup/0408/incr/2017-03-04_13-21-52
170304 13:51:13 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.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
incremental backup from 1626008 is enabled.
xtrabackup: cd to /data/backup/0408/
xtrabackup: This target seems to be already prepared with --apply-log-only.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(1634925)
.....
xtrabackup: Using 104857600 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 = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence number 1626228 in the system tablespace does not match the log sequence number 1634925 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Doing recovery: scanned up to log sequence number 1634925 (0%)
InnoDB: xtrabackup: Last MySQL binlog file position 574, file name mybinlog.000001
InnoDB: xtrabackup: Last MySQL binlog file position 574, file name mybinlog.000001
170304 13:51:17 completed OK!

其实整个过程就是一个merge的过程，可以看到全备的checkpoint中的信息已经发生了变化。

[root@cxqtest 0408]# cat xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 1634925
last_lsn = 1634925
compact = 0
recover_binlog_info = 0
[root@cxqtest 2017-03-04_13-21-52]# cat xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 1626008
to_lsn = 1634925
last_lsn = 1634925
compact = 0
recover_binlog_info = 0

然后进行第一次还原操作：

[root@cxqtest incr]# innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --copy-back /data/backup/0408/incr/2017-03-04_13-21-52
170304 13:58:13 innobackupex: Starting the copy-back operation

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

innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
Original data directory /data/3306/data is not empty!

报错了，需要清空对应的数据文件目录，清空目录继续进行操作：

[root@cxqtest incr]# innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --copy-back /data/backup/0408/incr/2017-03-04_13-21-52
170304 14:00:31 innobackupex: Starting the copy-back operation

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

innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
innobackupex: File 'ibdata1' not found (Errcode: 2 - No such file or directory)
InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
[01] error: cannot open file ibdata1
[01] Error: copy_file() failed.

又报错，说找不到ibdata1，其实这时候也应该明白了，其实merge合并之后到的是全备的checkpoint文件，所以要恢复的指定的应该是全备的目录才对。
再次修改目录进行恢复，成功：

[root@cxqtest incr]# innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --copy-back /data/backup/0408/
170304 14:02:30 innobackupex: Starting the copy-back operation

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

innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
.....
170304 14:03:59 completed OK!

然后赋予相应的mysql权限
注意：此次没有关闭mysql服务
登陆：

root@localhost:mysql.sock  14:14:27 [(none)]>use test;
Database changed
root@localhost:mysql.sock  14:14:32 [test]>show tables;
+----------------+
| Tables_in_test |
+----------------+
| t              |
+----------------+
1 row in set (0.00 sec)

root@localhost:mysql.sock  14:14:35 [test]>select * from t;
ERROR 1146 (42S02): Table 'test.t' doesn't exist

竟然报错了，明明显示有表啊，为什么会报表不存在？
重新启动mysql失败

[root@cxqtest incr]# /etc/init.d/mysqld restart
MySQL server PID file could not be found!                  [FAILED]
Starting MySQL......
...................The server quit without updating PID fil[FAILED]/3306/data/mysql.pid).

报错的原因就是找不到pid，幸好，清空目录的时候只是进行了mv操作，所以讲原pid再拷贝到data目录下，启动MySQL服务

[root@cxqtest incr]# /etc/init.d/mysqld start
Starting MySQL                                             [  OK  ]
[root@cxqtest incr]# 
[root@cxqtest incr]# 
[root@cxqtest incr]# mysql -uroot -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/data/3306/mysql.sock' (2)

但是很遗憾，虽然启动成功了，但是找不到sock，登陆不了MySQL数据库，这就尴尬了
然后想着能不能再关闭重启一下是否可以

[root@cxqtest incr]# /etc/init.d/mysqld stop
Shutting down MySQL..                                      [  OK  ]
[root@cxqtest incr]# /etc/init.d/mysqld start
Starting MySQL                                             [  OK  ]
[root@cxqtest incr]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.30-log Source distribution

Copyright (c) 2000, 2013, 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.

root@localhost:mysql.sock  14:20:35 [(none)]>
root@localhost:mysql.sock  14:20:36 [(none)]>
root@localhost:mysql.sock  14:20:36 [(none)]>use test;
Database changed
root@localhost:mysql.sock  14:20:39 [test]>show tables;
+----------------+
| Tables_in_test |
+----------------+
| t              |
+----------------+
1 row in set (0.00 sec)

root@localhost:mysql.sock  14:20:41 [test]>select * from t;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.11 sec)

终于恢复回来了，好不容易！！
TIPS：在进行恢复的过程当中还是需要关闭服务再恢复，再启动服务，不然的话，还是会出现上述的报错，还挺麻烦。
这个过程我们相当于完成了一个全备+一个增备的数据恢复过程。
而我们在一个增备之后又插入了一些数据，这个怎么继续恢复呢，还是prepare的过程。这个路径需要注意，还是merge到全备中。
tips：在prepare阶段，不能关闭MySQL的服务，在copy的阶段再进行关闭MySQL服务的操作

innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --apply-log --redo-only /data/backup/0408/ --incremental-dir=/data/backup/0408/incr/2017-03-04_13-35-29

查看xtrabackup_checkpoints

[root@cxqtest 0408]# cat xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 1634925
last_lsn = 1634925
compact = 0
recover_binlog_info = 0
[root@cxqtest 0408]# 
[root@cxqtest 0408]# 
[root@cxqtest 0408]# cat xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 1639553
last_lsn = 1639553
compact = 0
recover_binlog_info = 0

已经更新到了最新的lsn号
如下，进行恢复–ps：这次进行MySQL服务停止尝试一下
同样，清空data目录

[root@cxqtest 3306]# cd data/
[root@cxqtest data]# ls
auto.cnf     ib_logfile1  innodb_status.12062  mysql               test
error.log    ib_logfile2  mybinlog.000001      mysql.pid           xtrabackup_binlog_pos_innodb
ibdata1      ibtmp1       mybinlog.000002      performance_schema  xtrabackup_info
ib_logfile0  incr         mybinlog.index       slow.log
[root@cxqtest data]# /etc/init.d/mysqld stop
Shutting down MySQL..                                      [  OK  ]
[root@cxqtest data]# 
[root@cxqtest data]# mv * ../bak/
[root@cxqtest incr]# innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --copy-back /data/backup/0408/
170304 14:35:05 innobackupex: Starting the copy-back operation

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

innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
170304 14:35:05 [01] Copying ib_logfile0 to /data/3306/data/ib_logfile0
170304 14:37:00 completed OK!

恢复完成，重复赋予恢复文件的MySQL属组权限，启动MySQL服务：
如果没有服务权限就会报如下的错：

[root@cxqtest data]# /etc/init.d/mysqld start
Starting MySQL..The server quit without updating PID file ([FAILED]06/data/mysql.pid).
[root@cxqtest data]# /etc/init.d/mysqld start
Starting MySQL                                             [  OK  ]
[root@cxqtest data]# 
[root@cxqtest data]# /etc/init.d/mysqld stop
Shutting down MySQL...The server quit without updating PID [FAILED]ata/3306/data/mysql.pid).
[root@cxqtest data]# /etc/init.d/mysqld start
Starting MySQL                                             [  OK  ]
[root@cxqtest data]# /etc/init.d/mysqld stop
MySQL server PID file could not be found!                  [FAILED]
[root@cxqtest data]# 
[root@cxqtest data]# ls
2017-03-04_14-29-25  ib_logfile0  ibtmp1               innodb_status.17127  performance_schema  xtrabackup_binlog_pos_innodb
error.log            ib_logfile1  incr                 mybinlog.index       slow.log            xtrabackup_info
ibdata1              ib_logfile2  innodb_status.14679  mysql                test
[root@cxqtest data]# ll
total 2596920
drwxr-x--- 2 root  root        4096 Mar  4 14:36 2017-03-04_14-29-25
-rw-r----- 1 mysql root       14301 Mar  4 14:43 error.log
-rw-r----- 1 root  root  1073741824 Mar  4 14:36 ibdata1
-rw-r----- 1 root  root   524288000 Mar  4 14:35 ib_logfile0
-rw-r----- 1 root  root   524288000 Mar  4 14:35 ib_logfile1
-rw-r----- 1 root  root   524288000 Mar  4 14:36 ib_logfile2
-rw-r----- 1 root  root    12582912 Mar  4 14:37 ibtmp1
drwxr-x--- 2 root  root        4096 Mar  4 14:36 incr
-rw-rw---- 1 mysql mysql          0 Mar  4 14:39 mybinlog.index
drwxr-x--- 2 root  root        4096 Mar  4 14:36 mysql
drwxr-x--- 2 root  root        4096 Mar  4 14:37 performance_schema
-rw-rw---- 1 mysql mysql        543 Mar  4 14:43 slow.log
drwxr-x--- 2 root  root        4096 Mar  4 14:36 test
-rw-r----- 1 root  root          20 Mar  4 14:37 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root  root         625 Mar  4 14:37 xtrabackup_info
[root@cxqtest data]# pwd
/data/3306/data
[root@cxqtest data]# chown -R mysql.mysql *
[root@cxqtest data]# 
[root@cxqtest data]# 
[root@cxqtest data]# /etc/init.d/mysqld start
Starting MySQL..                                           [  OK  ]
[root@cxqtest data]# 
[root@cxqtest data]# ls
2017-03-04_14-29-25  ib_logfile0  incr                 mysql               test
auto.cnf             ib_logfile1  innodb_status.19581  mysql.pid           xtrabackup_binlog_pos_innodb
error.log            ib_logfile2  mybinlog.000001      performance_schema  xtrabackup_info
ibdata1              ibtmp1       mybinlog.index       slow.log

然后进行还原恢复：

[root@cxqtest incr]# innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --copy-back /data/backup/0408/
170304 15:02:39 innobackupex: Starting the copy-back operation

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

innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
170304 15:02:39 [01] Copying ib_logfile0 to /data/3306/data/ib_logfile0
170304 15:04:03 [01] Copying ./xtrabackup_info to /data/3306/data/xtrabackup_info
170304 15:04:03 [01]        ...done
170304 15:04:03 completed OK!

恢复完成，然后在data目录下赋予MySQL属组权限，启动MySQL服务：

[root@cxqtest bak]# /etc/init.d/mysqld start
Starting MySQL                                             [  OK  ]
[root@cxqtest bak]# 
[root@cxqtest bak]# 
[root@cxqtest bak]# /etc/init.d/mysqld stop
Shutting down MySQL..                                      [  OK  ]
[root@cxqtest bak]# /etc/init.d/mysqld start
Starting MySQL..                                           [  OK  ]

然后登陆数据库进行查询操作：

root@localhost:mysql.sock  15:11:42 [(none)]>use test;
Database changed
root@localhost:mysql.sock  15:11:44 [test]>
root@localhost:mysql.sock  15:11:44 [test]>
root@localhost:mysql.sock  15:11:45 [test]>
root@localhost:mysql.sock  15:11:45 [test]>show tables;
+----------------+
| Tables_in_test |
+----------------+
| t              |
+----------------+
1 row in set (0.00 sec)

root@localhost:mysql.sock  15:11:48 [test]>select * from t;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
+------+------+
6 rows in set (0.03 sec)

可以看到第二次增量备添加的数据被恢复的回来。

4、备份中的常用选项和场景

innobackupex中的选项很多，常用的比如stream选项，–slave-info选项能够方便搭建从库，生成偏移量的信息，比如并行–parallel等，还可以根据LSN来备份，选项是–incremental-lsn
对于stream选项，默认是打包，可以结合管道来实现压缩，比如：
innobackupex –defaults-file=/etc/my.cnf –user=root –stream=tar /data/backup/0408/ | gzip > /data/backup/0408/0408.tar.gz
很多时候其实我不想备份整个库，我只想备份一个表，那么这个操作如何来实现呢。
innobackupex –defaults-file=/etc/my.cnf –user=root -pn–include=’test.t’ /data/backup/0408
这里有几点需要注意，工具还是会逐个去扫描，只是那些不符合的会被忽略掉，也就意味着备份出来的情况和全备的目录结构是一样的，但是指定的表会备份出ibd，frm文件。

[root@cxqtest bak]# ll
total 1036
-rw-r--r-- 1 mysql mysql    8556 Mar 22 18:34 t.frm
-rw-r--r-- 1 root  root  1048576 Mar 22 19:26 t.ibd
[root@cxqtest bak]# cd ..

而这种情况下，ibdata也会完整备份出来，如果这个文件很大，那就相当不给力了。
不过有一个场景还是很实用的。那就是迁移表。

5、迁移表实验

如果我们还有一个实例3307的数据库，想把3306库中的test.t表导入到3307的test数据库中我们可以使用Innobackupex来做物理备份，然后还原导入，达到迁移的目的。

[root@cxqtest incr]# innobackupex --defaults-file=/etc/my.cnf --user=root -pmysql123 --include='test.t'  /data/backup/0408
170304 15:21:16 innobackupex: Starting the backup operation

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

170304 15:21:16  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/data/3306/mysql.sock' as 'root'  (using password: YES).
170304 15:21:16  version_check Connected to MySQL server
xtrabackup: Transaction log of lsn (1626267) to (1626267) was copied.
170304 15:21:51 completed OK!
[root@cxqtest 0408]# cd 2017-03-04_15-21-16
[root@cxqtest 2017-03-04_15-21-16]# ls
2017-03-04_14-29-25  2017-03-04_14-56-20  ibdata1  test                    xtrabackup_checkpoints  xtrabackup_logfile
2017-03-04_14-53-36  backup-my.cnf        incr     xtrabackup_binlog_info  xtrabackup_info
[root@cxqtest 2017-03-04_15-21-16]# cat xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 1626267
last_lsn = 1626267
compact = 0
recover_binlog_info = 0

下面的命令会声明指定目录下的备份需要导出对象。

[root@cxqtest 2017-03-04_15-21-16]# ls
2017-03-04_14-29-25  2017-03-04_14-56-20  ibdata1  test                    xtrabackup_checkpoints  xtrabackup_logfile
2017-03-04_14-53-36  backup-my.cnf        incr     xtrabackup_binlog_info  xtrabackup_info
[root@cxqtest 2017-03-04_15-21-16]# cd test/
[root@cxqtest test]# ll
total 108
-rw-r----- 1 root root  8586 Mar  4 15:21 t.frm
-rw-r----- 1 root root 98304 Mar  4 15:21 t.ibd
[root@cxqtest incr]# innobackupex --apply-log --export /data/backup/0408/2017-03-04_15-21-16
170304 15:31:06 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.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
xtrabackup: auto-enabling --innodb-file-per-table due to the --export option
xtrabackup: cd to /data/backup/0408/2017-03-04_15-21-16/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(1626267)
.....
InnoDB: Shutdown completed; log sequence number 1626664
170304 15:31:52 completed OK!

直接结果就是多了如下的文件：

[root@cxqtest test]# ll
total 128
-rw-r--r-- 1 root root   420 Mar  4 15:31 t.cfg
-rw-r----- 1 root root 16384 Mar  4 15:31 t.exp
-rw-r----- 1 root root  8586 Mar  4 15:21 t.frm
-rw-r----- 1 root root 98304 Mar  4 15:21 t.ibd

然后在新的3307的test数据库中创建表t，并且对表t信息做截断：

root@localhost:mysql.sock  15:35:24 [(none)]>use test;
Database changed
root@localhost:mysql.sock  15:35:26 [test]>
root@localhost:mysql.sock  15:35:26 [test]>
root@localhost:mysql.sock  15:51:16 [test]>create table t(id int ,name varchar(10));
Query OK, 0 rows affected (0.34 sec)
root@localhost:mysql.sock  15:35:26 [test]>alter table t discard tablespace;
Query OK, 0 rows affected (0.26 sec)

然后将exp和ibd文件拷贝到目标目录然后修改属组导入即可（如是导入到mysql5.6拷贝.cfg，而不是.exp）：

[root@cxqtest test]# cp t.exp /data/3307/data/test/
[root@cxqtest test]# cp t.ibd /data/3307/data/test/
[root@cxqtest test]# cp t.cfg /data/3307/data/test/
[root@cxqtest test]# cd /data/3307/data/test/
[root@cxqtest test]# ll
total 128
-rw-r--r-- 1 root root   420 Mar  4 15:47 t.cfg
-rw-r----- 1 root root 16384 Mar 4 15:38 t.exp
-rw-r----- 1 root root 98304 Mar 4 15:39 t.ibd
[root@cxqtest test]# chown mysql.mysql t.*
[root@cxqtest test]# lltotal 128[root@cxqtest test]# ll
total 116
-rw-r--r-- 1 mysql mysql   420 Mar  4 15:47 t.cfg
-rw-r----- 1 mysql mysql 16384 Mar  4 15:38 t.exp
-rw-r----- 1 mysql mysql 98304 Mar  4 15:39 t.ibd

然后再在test数据库中对表t进行导入操作：

root@localhost:mysql.sock  15:53:10 [test]>alter table t import tablespace;
Query OK, 0 rows affected (0.13 sec)
root@localhost:mysql.sock  15:53:41 [test]>show tables;
+----------------+
| Tables_in_test |
+----------------+
| t              |
+----------------+
1 row in set (0.00 sec)

root@localhost:mysql.sock  15:53:46 [test]>select * from t;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
+------+------+
6 rows in set (0.00 sec)

可以看到数据从3306的test数据库中导入到了3307的数据库当中。
如果不是按如上步骤进行操作会报如下的错误：
原因就是，1、在没有对表进行创建和discard之前就将文件拷贝到了test目录下；2、表创建完成以后，没有将对应的文件拷贝到test目录下，导致数据库无法找到对应的文件

root@localhost:mysql.sock  15:49:44 [test]>alter table t import tablespace;
ERROR 1146 (42S02): Table 'test.t' doesn't exist
root@localhost:mysql.sock  15:49:47 [test]>alter table test.t import tablespace;
ERROR 1146 (42S02): Table 'test.t' doesn't exist
root@localhost:mysql.sock  15:49:53 [test]>
root@localhost:mysql.sock  15:49:54 [test]>
root@localhost:mysql.sock  15:49:54 [test]>
root@localhost:mysql.sock  15:50:31 [test]>
root@localhost:mysql.sock  15:50:32 [test]>create table t(id int ,name varchar(10));
ERROR 1813 (HY000): Tablespace for table '`test`.`t`' exists. Please DISCARD the tablespace before IMPORT.
root@localhost:mysql.sock  15:50:44 [test]>alter table test2 discard tablespace;
ERROR 1146 (42S02): Table 'test.test2' doesn't exist
root@localhost:mysql.sock  15:51:07 [test]>alter table t discard tablespace;
ERROR 1146 (42S02): Table 'test.t' doesn't exist

有另外一点值得说的是，这个.exp文件是不是必须的，其实也不是。
我们只拷贝.ibd文件也照样可以。可能在新版本中会有一些警告提示，我们重新来做一下。

root@localhost:mysql.sock  15:50:32 [test]>alter table t discard tablespace;
Query OK, 0 rows affected (0.03 sec)

同时删除刚刚拷贝过来的.exp文件。
然后拷贝ibd文件到指定目录，赋权限
导入表空间信息。

root@localhost:mysql.sock  15:50:32 [test]> alter table t import tablespace; 
Query OK, 0 rows affected (0.00 sec)

Silencecxq

关注

2
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
xtrabackup的初使用

这里参考了jeanron大师的文章：http://chuansong.me/n/1696438151334 经过前边恶心的安装以后，接下来尝试一番xtrabackup的备份功能。 xtrabackup主要是用于热备份innodb,或者是 xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构； innobackupex是将xtrabackup进行封装的perl脚本，可以备份和恢
复制链接

扫一扫

专栏目录