xtrabackup的初使用,MySQL备份和恢复

图片 3

MariaDB/MySQL备份复苏种类:
备份和东山复起(豆蔻梢头):mysqldump工具用法详述
备份和回复(二):导入、导出表数据
备份和还原(三):xtrabackup用法和公理详述

此处参谋了jeanron大师的作品:
由在此在此之前边恶乙酰胆碱心得安装以往,接下去尝试蒸蒸日上番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

xtrabackup是percona团队研究开发的备份工具,比MySQL官方的ibbackup的作用还要多。扶植myisam温全备、innodb热全备和温增备,还可以完结innodb的定期点恢复生机,并且备份和还原的快慢都异常的快。在时下MySQL的备份达成上,思索价格、速度、安全、少年老成致性等角度,xtrabackup是非常妥贴的工具。

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

MariaDB也得以选拔percona xtrabackup进行备份,可是MariaDB基于percona
xtrabackup开荒了它协调的备份工具:MariaDB
Backup。它依据xtrabackup开拓,所以所用方法基本和xtrabackup一样,只是多少自身的风味。详细内容见MariaDB
Backup官方手册:https://mariadb.com/kb/en/library/mariadb-backup/

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)

能够见到第三遍增量备增加的多少被恢复生机的回来。

xtrabackup官方手册:https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html

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也会完好备份出来,假若那几个文件异常的大,那就非常不给力了。
不过有二个情状依然很实用的。那便是迁移表。

1.安装xtrabackup

下载地址:https://www.percona.com/downloads/XtraBackup/LATEST/

rpm仓库(实际上是percona的旅社):http://repo.percona.com/release/

南开东军政大学学percona源:https://mirrors.tuna.tsinghua.edu.cn/percona/

因为只是二个备份工具,所以没要求编写翻译安装,直接下载它的rpm包就能够。不过该rpm包信赖于libev.so.4,该信赖包能够在epel源中找到。

此间安装的是当下风靡版的xtrabackup-24-2.4.11。

cat <<eof>>/etc/yum.repos.d/percona.repo
[percona]
name = Percona
baseurl = https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch
enabled = 1
gpgcheck = 0

[epel]
name=epelrepo
baseurl=https://mirrors.aliyun.com/epel/$releasever/$basearch
gpgcheck=0
enable=1
eof

[root@node1 ~]# yum list all| grep xtraback -i
Repository epel is listed more than once in the configuration
holland-xtrabackup.noarch                      1.0.14-3.el6                 epel
percona-xtrabackup.x86_64                      2.3.10-1.el6                 percona
percona-xtrabackup-20.x86_64                   2.0.8-587.rhel6              percona
percona-xtrabackup-20-debuginfo.x86_64         2.0.8-587.rhel6              percona
percona-xtrabackup-20-test.x86_64              2.0.8-587.rhel6              percona
percona-xtrabackup-21.x86_64                   2.1.9-746.rhel6              percona
percona-xtrabackup-21-debuginfo.x86_64         2.1.9-746.rhel6              percona
percona-xtrabackup-22.x86_64                   2.2.13-1.el6                 percona
percona-xtrabackup-22-debuginfo.x86_64         2.2.13-1.el6                 percona
percona-xtrabackup-24.x86_64                   2.4.11-1.el6                 percona
percona-xtrabackup-24-debuginfo.x86_64         2.4.11-1.el6                 percona
percona-xtrabackup-debuginfo.x86_64            2.3.10-1.el6                 percona
percona-xtrabackup-test.x86_64                 2.3.10-1.el6                 percona
percona-xtrabackup-test-21.x86_64              2.1.9-746.rhel6              percona
percona-xtrabackup-test-22.x86_64              2.2.13-1.el6                 percona
percona-xtrabackup-test-24.x86_64              2.4.11-1.el6                 percona

[root@node1 ~]# yum -y install percona-xtrabackup-24

装完xtrabackup后,生成以下多少个工具。

[root@node1 ~]# rpm -ql percona-xtrabackup-24 | grep bin | xargs ls -hl
lrwxrwxrwx 1 root root   10 May  8 19:19 /usr/bin/innobackupex -> xtrabackup
-rwxr-xr-x 1 root root 3.5M Apr 19 01:11 /usr/bin/xbcloud
-rwxr-xr-x 1 root root 3.0K Apr 19 01:04 /usr/bin/xbcloud_osenv
-rwxr-xr-x 1 root root 3.5M Apr 19 01:11 /usr/bin/xbcrypt
-rwxr-xr-x 1 root root 3.5M Apr 19 01:11 /usr/bin/xbstream
-rwxr-xr-x 1 root root  21M Apr 19 01:11 /usr/bin/xtrabackup
  • xbcloud和xbcloud_osenv是xtrabackup新的尖端性子:云备份;
  • xbcrypt也是新的风味,加密备份集;
  • xbstream是xtrabackup的流数据功用,通过流数据成效,可将备份内容打包并传给管道后的压缩工具进行削减;
  • xtrabackup是主程序;
  • innobackupex在那前是二个perl脚本,会调用xtrabackup这么些二进制工具,从xtrabackup
    2.3从头,该工具使用C语言举行了重写,当前它是xtabackup二进制工具的贰个软连接,然而实际的运用形式却今是昨非,并且在今后的本子中会删除该工具。

在本文中,会独家对七个主程序innobackupex和xtrabackup的备份复苏措施开展详尽的印证,还恐怕会在表明经过中尽量的表达它们是怎么着专门的学业的,别的还有只怕会介绍它们的部分新鲜成效的选项,如流备份选项。

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)

2.备份锁

后生可畏篇不错的牵线xtrabackup锁的小说:https://www.percona.com/blog/2014/03/11/introducing-backup-locks-percona-server-2/。

percona Server 5.6+ 支撑风姿罗曼蒂克种新锁——backup
lock(备份锁)
,这种锁是percona对MySQL的补给,特地为备份而规划。这种锁在percona
Server 5.6+ 有,MariaDB中也会有,不过Oracle的MySQL中从未,最少MySQL
5.7中并未有。

这种锁用在备份的时候代替 flush tables with
read
lock 获取全局锁,是风度翩翩种轻量级的大局锁。它有两种类型的锁:备份表锁和二进制日志锁。为此新扩展了3种语法:

lock tables for backup   # 申请备份表锁
lock binlog for backup   # 申请二进制日志锁
unlock binlog            # 释放二进制日志锁

备份表锁在大局范围内只对非innodb表加锁,所以具有该锁后不能够修改非innodb表,但却不影响innodb表的DML。当然,因为是全局锁,所以也会阻塞DDL操作。

二进制日志锁在全局范围内锁定二进制日志,所以会阻塞其余会话修改二进制日志。那样能够保障能够收获到二进制日志中意气风发致性的岗位坐标。 

3.xtrabackup备份规律表明

无论是是接纳innobackupex照旧xtrabackup工具进行备份和还原,都有3个步骤:备份(backup)、准备(prepare)、恢复(copy
back)。

留意,xtrabackup备份进程中,先备份innodb表,再备份非innodb表。

3.1 备份进度(backup阶段)

(1).在运转xtrabackup时记下LSN并将redo
log拷贝到备份目的目录下的xtrabackup_logfile文件中。由于拷贝需求断定时期,固然在拷贝时间段内有日记写入,将导致拷贝的日记和MySQL的redo
log不一样等,所以xtrabackup还应该有八个后台进程监控着mysql的redo
log,每秒监察和控制贰次,当MySQL的redo
log有变化,该监察和控制进度会登时将转移的开始和结果写入到xtrabackup_logfile文件,那样就能够确定保障拷贝走的redo
log中记录了全方位变化。然则那也许有危机的,因为redo是轮流培训式循环写入的,若是某不平日刻有充足大气的日志写到redo
log中,使得还没起来复制的日志就被新日志覆盖了,那样会日志遗失,并报错。

(2).拷贝完初步版的redo
log后,xtrabackup初始拷贝innodb表的数据文件(即表空间文件.ibd文件和ibdata1)。注意,此时不拷贝innodb的frm文件。

(3).当innodb相关表的数据文件拷贝达成后,xtrabackup开端筹划拷贝非innodb的公文。但在拷贝它们以前,要先对非innodb表进行加锁幸免拷贝时有语句修改那个类别的表数据。

对于不扶植backup
lock的版本,只好通过flush tables with read
lock来赢得全局读锁,但如此也同等会锁住innodb表,杀伤力太大。所以选拔xtrabackup备份Oracle的MySQL,实质上不得不促成innodb表的某个时间热备、部分时刻温备。

对于援救backup
lock的本子,xtrabackup通过lock tables for backup获取轻量级的backup
locks来代表flush tables with read
lock,因为它只锁定非innodb表,所以通过达成了innodb表的真的热备。

(4).当获取到非innodb表的锁将来,开端拷贝非innodb表的数码和.frm文件。当那几个拷贝实现今后,继续拷贝其余存款和储蓄引擎类型的公文。(实际上,拷贝非innodb表的数据是在获得backup
locks(借使协理)后自行举办的,它们属于同叁个历程)

(5).当拷贝阶段完结后,就到了备份的收尾阶段。蕴含获取二进制日志中新生事物正在蒸蒸日上致性地点的坐标点、甘休redo
log的监督检查和拷贝、释放锁等。

对于不辅助backup
lock的本子,收尾阶段的进度是这样的:获取二进制日志的黄金时代致性坐标点、停止redo
log的监察和拷贝、释放锁。

对此支撑backup lock的本子,收尾阶段的历程是这么的:先经过lock binlog for
bakcup来获取二进制日志锁,然后甘休redo log的监督检查和拷贝,再unlock
tables释放表锁,随后获得二进制日志的黄金年代致性地方坐标点,最终unlock
binlog释放二进制日志锁。

(6).假使全部都OK,xtrabackup将以状态码0退出。

故而,对是不是帮忙backup lock的版本,xtrabackup备份的时的表现是差别等的。

图片 1

backup阶段的长河具体如下图所示:

图片 2

FTWRL:flush table with read lock;

图片 3

3.2 筹算进程(prepare阶段)

由于备份的时候拷贝走的数据文件恐怕是区别样的,举例监察和控制着MySQL的redo
log中在拷贝进程一鼓作气后又新的业务提交了,而拷贝走的数额是未提交状态的,那么就须要对该事情前滚;假使监察和控制到的日记中有业务未提交,那么该专门的学业就须要回滚。

可是借使只备份了myisam表或此外非事务表数据,因为备份阶段间接锁定了那几个表,所以不会有不风华正茂致的气象。

xtrabackup有三个”计划”的品级。那几个级其余真相便是对备份的innodb数据利用redo
log,该回滚的回滚,该前滚的前滚,最后确认保证xtrabackup_logfile中著录的redo
log已经整整运用到备份数据页上,况且达成了风流罗曼蒂克致性。当使用截至后,会重写”xtrabackup_logfile”再度保障该redo
log和备份的多少是对应的。

兵马不动有备无患进度无需连接数据库,该进程能够在任性装了xtrabackup软件的机器上海展览中心开,之所能达成是因为xtrabackup软件的里边嵌入了二个简化的innodb存款和储蓄引擎,能够通过它成功日志的运用。

3.3 恢复生机进度(copy back阶段)

xtrabackup的复苏进度实质是将备份的数据文件和布局定义等文件拷贝回MySQL的datadir。同样能够拷贝到任意机器上。

渴求苏醒以前MySQL必得是终止运作状态,且datadir是空目录,除非恢复的操作是导入表的操作。具体见后文对应的源委。

4.图谋实验情状

创办测验数据库backuptest,并创建myisam表和innodb表,此处轻松的应用数值扶助表并分别插入1亿条数据。

DROP DATABASE IF EXISTS backuptest;
CREATE DATABASE backuptest;
USE backuptest;

# 创建myisam类型的数值辅助表和对应插入数据的存储过程
CREATE TABLE num_isam(n INT NOT NULL PRIMARY KEY)ENGINE=MYISAM;
DELIMITER $$
DROP PROCEDURE IF EXISTS proc_num1$$
CREATE PROCEDURE proc_num1(num INT) 
BEGIN
    DECLARE rn INT DEFAULT 1;
    TRUNCATE TABLE backuptest.num_isam;
    INSERT INTO backuptest.num_isam VALUES(1);
    dd: WHILE rn*2 < num DO
        BEGIN
            INSERT INTO backuptest.num_isam SELECT rn+n FROM backuptest.num_isam;
            SET rn = rn*2;
        END;
    END WHILE dd;
    INSERT INTO backuptest.num_isam SELECT n+rn FROM num_isam WHERE n+rn <=num;
END;$$
DELIMITER ;

# 创建innodb类型的数值辅助表和对应插入数据的存储过程
CREATE TABLE num_innodb(n INT NOT NULL PRIMARY KEY)ENGINE=INNODB;
DELIMITER $$
DROP PROCEDURE IF EXISTS proc_num2$$
CREATE PROCEDURE proc_num2(num INT) 
BEGIN
    DECLARE rn INT DEFAULT 1;
    TRUNCATE TABLE backuptest.num_innodb;
    INSERT INTO backuptest.num_innodb VALUES(1);
    dd: WHILE rn*2 < num DO
        BEGIN
            INSERT INTO backuptest.num_innodb SELECT rn+n FROM backuptest.num_innodb;
            SET rn = rn*2;
        END;
    END WHILE dd;
    INSERT INTO backuptest.num_innodb SELECT n+rn FROM backuptest.num_innodb WHERE n+rn <=num;
END;$$
DELIMITER ;

# 分别向两个数值辅助表中插入1亿条数据,
CALL proc_num1(100000000);
CALL proc_num2(100000000);

5.innobackupex工具

5.1 innobackupex达成全备份和出山小草的长河

(1). 全备

除却给定连接MySQL服务器的接连几日参数,只需再给定贰个目录就可以,该目录是备份的对象地点。暗中同意xtrabackup连接数据库的时候从布局文件中去读取和备份相关的配置,能够采取采取–defaluts-file内定连接时的参数配置文件,但倘使钦命该选取,该接纳只可以放在第叁个选项地方。

innobackupex --user=root --password=123456 /bakdir/

暗中认可备份的门路是钦命路线/bakdir下的四个以时间为时间戳的目录。

[root@xuexi bakdir]# du -sh /bakdir/2017-04-02_07-09-47/*
4.0K    /bakdir/2017-04-02_07-09-47/backup-my.cnf
4.0G    /bakdir/2017-04-02_07-09-47/backuptest
589M    /bakdir/2017-04-02_07-09-47/ibdata1
1.8M    /bakdir/2017-04-02_07-09-47/mysql
8.0K    /bakdir/2017-04-02_07-09-47/Performance
636K    /bakdir/2017-04-02_07-09-47/performance_schema
1008K   /bakdir/2017-04-02_07-09-47/world
4.0K    /bakdir/2017-04-02_07-09-47/xtrabackup_binlog_info
4.0K    /bakdir/2017-04-02_07-09-47/xtrabackup_checkpoints
4.0K    /bakdir/2017-04-02_07-09-47/xtrabackup_info
4.0K    /bakdir/2017-04-02_07-09-47/xtrabackup_logfile

查看该文件目录中文件和大小,能够见见xtrabackup的表现便是复制了对象数据库的连锁文书,并新建了多少个文件。

其中:

  • backup-my.cnf是拷贝过来的安顿文件。里面只包蕴[mysqld]配备部分和备份有关的选项。
  • xtrabackup_binlog_info中记录的是如今应用的二进制日志文件。

    [root@xuexi bakdir]# cat 2017-04-02_07-09-47/xtrabackup_binlog_info 
    mysql-bin.000001        120
    
  • xtrabackup_checkpoints中著录了备份的门类是全备依然增备,还大概有备份的开局、终止LSN号。

    [root@xuexi bakdir]# cat 2017-04-02_07-09-47/xtrabackup_checkpoints 
    backup_type = full-backuped
    from_lsn = 0
    to_lsn = 7533359841
    last_lsn = 7533359841
    compact = 0
    recover_binlog_info = 0
    
  • xtrabackup_info中记录的是备份进度中的一些信息。

    [root@xuexi bakdir]# cat 2017-04-02_07-09-47/xtrabackup_info 
    uuid = 66f34974-1730-11e7-9d09-000c299af3f3
    name = 
    tool_name = innobackupex
    tool_command = --user=root --password=...  /bakdir/
    tool_version = 2.4.6
    ibbackup_version = 2.4.6
    server_version = 5.6.35-log
    start_time = 2017-04-02 07:09:47
    end_time = 2017-04-02 07:10:31
    lock_time = 0
    binlog_pos = filename 'mysql-bin.000001', position '120'
    innodb_from_lsn = 0
    innodb_to_lsn = 7533359841
    partial = N          # N表示未启用该方面的功能,如此处表示不是备份部分数据库或表
    incremental = N
    format = file
    compact = N
    compressed = N
    encrypted = N
    
  • xtrabackup_logfile是复制和督察后写的redo日志。该日记是备份后下叁个操作”盘算”的最重要。唯有经过它本领实现数据风流罗曼蒂克致性。

(2). 全备的备选过程

在全备份实现未来,备份的数码中风度翩翩经有innodb数据,则还不可能用来过来。因为从xtrabackup先导备份的时候就监控着MySQL的redo
log,在拷贝的innodb数据文件中很只怕还应该有未提交的作业,並且拷贝完innodb数据未来还恐怕付出了作业可能开启了新的事体等等。同理可得,全备之后的图景不自然是同后生可畏的。不过只要只备份了myisam表或其余非事务表数据,因为备份阶段直接锁定了那几个表,所以不会有不平等的情状。

xtrabackup有三个”计划”的阶段。那么些阶段的实质便是对备份的innodb数据选用redo
log,该回滚的回滚,该前滚的前滚,最后确定保证xtrabackup_logfile中著录的redo
log已经全体施用到备份数据页上,况兼完结了如火如荼致性。当使用结束后,会重写”xtrabackup_logfile”再度保证该redo
log和备份的数量是相应的。

举例,备份的innodb数据文件中存在未提交的业务,不过在监督检查到的日记中打开了提交,那么就必要对该业务前滚;假诺监控到的日志中有事情未提交,那么该职业就供给回滚。

有备无患阶段选择的方式选取是”–apply-log”。希图阶段不会三番一次MySQL,所以不用钦定连接选项如–user等。

[root@xuexi bakdir]# innobackupex --apply-log /bakdir/2017-04-02_07-09-47/

在备选成功时,会在频幕上输出如下提示内容:

InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 7533367063
170402 12:11:23 completed OK!

在预备阶段,有三个内存使用量选项”–use-memory”,该选项私下认可值为100M,值越大盘算的经过越快。当然,将该值加大的前提是服务器内部存款和储蓄器够用。

(3). 全备份的余烬复起进度

光复的级差正是向MySQL的datadir拷贝。全备份的东山复起需要MySQL必得处于stop状态,並且datadir必得为空哪怕是和MySQL非亲非故的文本也不能够存在,它不会去蒙蔽datadir中已存在的开始和结果。不然会唤起如下错误:

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

停止mysql并清空datadir。

service mysqld stop
rm -rf /mydata/data/*

光复时利用的格局是”–copy-back”,选项后钦命要过来的源备份目录。恢复时因为无需延续数据库,所以不要钦点连接选项,如–user等。

[root@xuexi bakdir]# innobackupex --copy-back /bakdir/2017-04-02_07-09-47/
170402 12:36:09 completed OK!

拷贝实现后,MySQL的datadir的文书的全体者和属组是innobackupex的调用者,所以供给改回mysql.mysql。

[root@xuexi bakdir]# ll /mydata/data/
total 712736
drwxr-x--- 2 root root      4096 Apr  2 12:36 backuptest
-rw-r----- 1 root root 616562688 Apr  2 12:35 ibdata1
-rw-r----- 1 root root  50331648 Apr  2 12:35 ib_logfile0
-rw-r----- 1 root root  50331648 Apr  2 12:35 ib_logfile1
-rw-r----- 1 root root  12582912 Apr  2 12:36 ibtmp1
drwxr-x--- 2 root root      4096 Apr  2 12:36 mysql
drwxr-x--- 2 root root      4096 Apr  2 12:35 Performance
drwxr-x--- 2 root root      4096 Apr  2 12:36 performance_schema
drwxr-x--- 2 root root      4096 Apr  2 12:35 world
-rw-r----- 1 root root        23 Apr  2 12:35 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root       494 Apr  2 12:35 xtrabackup_info

[root@xuexi bakdir]# chown -R mysql.mysql /mydata/data/*

姣好这几个之后,就可以运行MySQL服务器了。能够进来mysql测验backuptest数据库中的数据是或不是完好。

5.2 innobackupex实现增量备份和恢复生机的长河

增量备份重视于全备份。xtrabackup完结增量备份的准绳是透过比较全备份的极端LSN和脚下的LSN,增备时将从终端LSN开端一贯备份到眼下的LSN。在备份时也是有redo
log的督察线程,对于增备进程中程导弹致LSN增加的操作也会写入到日志中。

增备的完结依据于LSN,所以只对innodb有效,对myisam表使用增备时,背后实行的是全备。

(1). 要开展增备,首先要有全备文件。这里再度展开贰遍全备。

innobackupex --user=root --password=123456 /bakdir/

全备完结后,在/bakdir目录下转移的全备目录是2017-04-02_13-26-35。

[root@xuexi ~]# ls /bakdir/2017-04-02_13-26-35/
backup-my.cnf  ibdata1  Performance         secure_dir  xtrabackup_binlog_info  xtrabackup_info
backuptest     mysql    performance_schema  world       xtrabackup_checkpoints  xtrabackup_logfile

查看xtrabackup_checkpoints能够摸清有关的LSN。

[root@xuexi ~]# cat /bakdir/2017-04-02_13-26-35/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 7533367093
last_lsn = 7533367093
compact = 0
recover_binlog_info = 0

介怀:要落实增备,这一回的全备一定不能够举行”策动”操作,原因稍后给出。

(2). 实行第三回增备。

若是对示范数据可backuptest中的num_innodb表进行了truncate操作。

mysql> truncate backuptest.num_innodb;

下一场再增备。增备时选取”–incremental”选项表示增量备份,增量备份时索要经过”–incremental-basedir=fullback_PATH”钦点基于哪个备份集备份,因为是率先次增备,所以要基于完全备份增量集。

[root@xuexi ~]# innobackupex --user=root --password=123456 --incremental /bakdir/ --incremental-basedir=/bakdir/2017-04-02_13-26-35/

增备实现后,生成的增备集为/bakdir/2017-04-02_13-39-05/,查看里面包车型客车xtrabackup_checkpoints,可以见到备份的起头LSN是上次全备完结后的LSN。

[root@xuexi ~]# cat /bakdir/2017-04-02_13-39-05/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 7533367093
to_lsn = 7533372535
last_lsn = 7533372535
compact = 0
recover_binlog_info = 0

暗中认可景况下,增备的初步LSN是自行获得的,可是在某个情形下不或许赢得,还有个别情形下不恐怕获取到将要增备的basedir。xtrabackup提供的选项”–incremental-lsn=N”能够显式钦赐增备的开始LSN,显式钦命LSN时,能够不用提供增备的basedir。

诸如,假若得到到了上次全备的终止LSN为7533367093,能够如下情势增备:

innobackupex --user=root --password=123456 --incremental /bakdir/ --incremental-lsn=7533367093

诸有此类增备后也在/bakdir中生成多少个小时戳目录/bakdir/2017-04-02_13-50-33。查看LSN信息:

[root@xuexi ~]# cat /bakdir/2017-04-02_13-50-33/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 7533367093
to_lsn = 7533372535
last_lsn = 7533372535
compact = 0
recover_binlog_info = 0

想来和点名–incremental-basedir实行增备是如日中天致的。

(3). 实行首回增备。

借使在率先次增备后,向上次truncate的表backuptest.num_innodb表中插入的100W条记下。

mysql> call backuptest.proc_num2(1000000);      
mysql> select count(*) from backuptest.num_innodb;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+

下一场进行增备。此次增备是依附第二回增备的(当然也足以根据全备举办备份,那样达成的是出入备份)。

[root@xuexi ~]# innobackupex --user=root --password=123456 --incremental /bakdir/ --incremental-basedir=/bakdir/2017-04-02_13-39-05/

这一次增备完成后调换的备份集为/bakdir/2017-04-02_14-03-51/。查看LSN信息:

[root@xuexi ~]# cat /bakdir/2017-04-02_14-03-51/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 7533372535
to_lsn = 7585150275
last_lsn = 7585150275
compact = 0
recover_binlog_info = 0

(4). 增备的备选进程

增备的预备进度和全备的备选进度有一些不雷同,不到最终苏醒的时候不可能张开任何”希图”进程。

增备进程中的每一遍备份行为都会监督MySQL的redo
log,写入到xtrabackup_logfile的日记中也许会有未提交的工作,不过到前面增备的时候举办了交给,也正是说提交进程记录到了增备时监察和控制的日志xtrabackup_logfile中。借使在增备前进行了”盘算”,那么该事务就能被回滚,后边增备中的提交就不见了,由此会导致数据遗失。

要确定保障将兼具的备份集实行整合,需求选用在各类备份集的”筹算”过程中应用”–redo-only”选项,那样应用日志时会”直线向前”直到最后一个备份集。它的原形是向全备聚焦不断的加码应用增备中的日志。不过,最后三个增备集需求作为备份集整合的顶峰,所以它无法动用”–redo-only”选项。整合形成之后,原本的全备就早就全部了,那时再对扩张达成的全备集进行一次”图谋”就可以用于末端的苏醒。

故此,假设全备为A,3次增备分别为B/C/D,倘诺只想重理旧业到C,那么从A起初整合到C甘休就可以。

因为在每二个增备的”计划”进程中都亟需向整合的初叶备份聚焦增添应用日志,所以每二遍增备的”筹划”都要求钦定整合的开始备份集目录作为basedir。比如钦赐全备份作为整合的开首备份集。

从上述实验进度中,获得的全备集是2017-04-02_13-26-35,第二回和第三回增备集分别是2017-04-02_13-26-35、2017-04-02_14-03-51。下边是它们的”计划”进程。

# 对整合的开始备份集——全备集应用日志,并指定"--redo-only"表示开始进入日志追加
innobackupex --apply-log --redo-only /bakdir/2017-04-02_13-26-35

# 对第一个增备集进行"准备",将其追加到全备集中
innobackupex --apply-log --redo-only /bakdir/2017-04-02_13-26-35 --incremental-dir=/bakdir/2017-04-02_13-39-05

# 对第二个增备集进行"准备",将其追加到全备集中,但是不再应用"--redo-only",表示整合的结束点
innobackupex --apply-log /bakdir/2017-04-02_13-26-35 --incremental-dir=/bakdir/2017-04-02_14-03-51

# 对整合完成的全备集进行一次整体的"准备"
innobackupex --apply-log /bakdir/2017-04-02_13-26-35

当全部的备份集整合实现后,就如几个后生可畏体化的全备集,全备中的LSN会更新到组合的截至点。如下:

[root@xuexi data]# cat /bakdir/2017-04-02_13-26-35/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 7585150275  #整合完成后全备中的LSN
last_lsn = 7585150275
compact = 0
recover_binlog_info = 0

[root@xuexi data]# cat /bakdir/2017-04-02_14-03-51/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 7533372535
to_lsn = 7585150275       #整合的结束备份集中的LSN
last_lsn = 7585150275
compact = 0
recover_binlog_info = 0

假如比不小心整合的风流浪漫意气风发错误了,那么结合的备份集将是低效的,要求重新结合。

(5). 增备的过来进程

因为组成甘休后就等价于三个全备集,所以可以向来实行苏醒。

还原进度同样需求有限支撑MySQL的datadir是空的,且MySQL服务器是stop的。

service mysqld stop
rm -rf /mydata/data/*
innobackupex --copy-back /bakdir/2017-04-02_13-26-35
chown -R mysql.mysql /mydata/data/*

接下来重启MySQL,步向查看可见num_innodb的数据为100W行记录,即恢复生机成功。

mysql> select count(*) from backuptest.num_innodb;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+

5.3 innobackupex完结导出和导入单张表的经过

默许景况下,InnoDB表不能够通过一贯复制表文件的法子在mysql服务器之间实行移植,纵然使用了innodb_file_per_table选项。而选拔Xtrabackup工具贯彻此种作用,然则只好”导出”具备.ibd文件的表,约等于说导出表的mysql服务器启用了innodb_file_per_table选项,何况要导出的表还是在启用该选取之后才创建的。

导入表的是,供给导入表的服务器版本是MySQL
5.6+,且启用了innodb_file_per_table选项。

(1). 导出表

导出表是在”盘算”的进程中张开的,不是在备份的时候导出。对于三个已经备份好的备份集,使用”–apply-log”和”–export”选项就能够导出备份集中的表。

万一以全备份集/bakdir/2017-04-02_17-41-38为例,要导出在那之中的表。

innobackupex --apply-log --export /bakdir/2017-04-02_17-41-38

在导出进程中,会看出如下消息:

xtrabackup: export metadata of table 'backuptest/num_innodb' to file `./backuptest/num_innodb.exp` (1 indexes)
xtrabackup:     name=PRIMARY, id.low=144, page=3

它表明了创办了八个.exp文书。

翻开备份集目录下的backuptest目录,会意识多出了2个公文:.cfg和.exp,再加上.ibd文件,那3个文件是三番两遍导入表时所需的文书。

-rw-r--r-- 1 root root  349 Apr  2 18:15 num_innodb.cfg
-rw-r----- 1 root root  16K Apr  2 18:15 num_innodb.exp
-rw-r----- 1 root root 8.4K Apr  2 17:41 num_innodb.frm
-rw-r----- 1 root root  31M Apr  2 17:41 num_innodb.ibd

当中.cfg文件是意气风发种特有的innodb数据字典文件,它和exp文件的效能是大半的,只然而前者还协理在xtradb中程导弹入,严酷地讲,要将导出的表导入到MySQL5.6照旧percona
server
5.6中,”.cfg”文件完全能够无需,可是纵然有该文件的话,会进展架构验证。

(2). 导入表

要在mysql服务器上导入来自于别的服务器的某innodb表,必要先在当下服务器上创办五个跟原表表结构同样的表,而后才干贯彻将表导入:

mysql> CREATE TABLE tabletest (...)  ENGINE=InnoDB;

下一场将此表的表空间:

mysql> ALTER TABLE mydatabase.tabletest  DISCARD TABLESPACE;

接下去,未来自于”导出”表的的.ibd和.exp文件复制到当前服务器的数量目录,如果导入目的服务器是MySQL
5.6+,也足以复制.cfg文件。然后采纳如下命令将其“导入”:

mysql> ALTER TABLE mydatabase.tabletest IMPORT TABLESPACE;

5.4 innobackupex实现部分备份和余烬复起的长河

xtrabackup支持部分备份,意味着能够钦命备份哪个数据库只怕哪些表。

一些备份独有某个须求注意:在恢复生机的时候不要通过”–copy-back”的法子拷贝回datadir,而是应该运用导入表的办法。就算采纳拷贝的点子不常候是立见成效的,但是众多气象下会产出数据库不一致等的处境。

(1). 备份

开创部分备份有二种办法:

  1.  通过”–include”选项能够钦赐正则来同盟要备份的表,这种办法要利用完全对象援用格式,即db_name.tab_name的方式。
  2. 快要备份的表分行枚举到三个文本中,通过”–tables-file”钦赐该文件。
  3. 抑或使用”–databases”钦赐要备份的数据库或表,钦定备份的表时要采纳完全对象援用格式,多个因素选取空格分开。

运用前两种部分备份格局,只好备份innodb表,不会备份任何myisam,就算内定了也不会备份。何况要备份的表必需有独立的表空间文件,也正是说必得拉开了innodb_file_per_table,纠正确的说,要备份的表是在拉开了innodb_file_per_table选项之后才创立的。第三种备份方式能够备份myisam表。

例如 –include=’^back.*[.]num_*’ ,将备份back字母起头的数据库中num开端的表,当中”[.]”的中括号不能够少,因为正则中”.”有卓越意义,所以选取中括号来枚举以落到实处指标的完好援引。

innobackupex --user=root --password=123456 --include='^back*[.]num_*' /bakdir/

动用”–include”和”–tables-file”备份后,会变动七个光阴戳目录,目录中独有和要备份的表有关的文书。

[root@xuexi data]# ls /bakdir/2017-04-02_17-35-46/
backup-my.cnf  ibdata1  xtrabackup_binlog_info  xtrabackup_checkpoints  xtrabackup_info  xtrabackup_logfile

假使利用的是–databases选项,则会扭转二个日子戳目录,里面有备份的数据库代表的目录,要是只备份了某些表,则该数据库目录中唯有该表相关的文本。

innobackupex --user=root --password=123456 --databases='mysql.user backuptest' /bakdir/

地点只备份mysql.user表和backuptest数据库,在转移的光阴戳目录准将有五个mysql目录和backuptest目录。

[root@xuexi data]# ls /bakdir/2017-04-02_17-41-38/
backup-my.cnf  backuptest  ibdata1  mysql  xtrabackup_binlog_info  xtrabackup_checkpoints  xtrabackup_info  xtrabackup_logfile

[root@xuexi data]# ls /bakdir/2017-04-02_17-41-38/backuptest/
db.opt  num_innodb.frm  num_innodb.ibd  num_isam.frm  num_isam.MYD  num_isam.MYI

[root@xuexi data]# ls /bakdir/2017-04-02_17-41-38/mysql/
user.frm  user.MYD  user.MYI

(2). 部分备份的备选和回复进程

一些备份的备选和还原进程分别是导出表和导入表的进度。见上文。 

5.5 innobackupex达成按期点复苏

xtrabackup自身不能够实现定时点复苏,只好通过复苏备份后经过二进制日志实现。达成方式和平常定期点复苏是同豆蔻梢头的。见:二进制日志定点还原数据库。

5.6 流备份和长途备份

xtrabackup扶持备份流,当前可用的流类型唯有tar和xtrabackup自带的xbstream,通过流能够将它们传递给别的程序进行连锁的操作,如压缩。可是不建议在备份的还要实行压缩,因为压缩会占用十分的大的cpu财富,使得备份时间延长相当多,温备的进度也就延长了。

别的,MySQL的数据文件压缩比比相当大,所以建议备份后在悠闲的时候举办压缩。

xtrabackup还扶助远程备份,只需使用”–remote-host”钦赐远程的主机名就能够,钦赐方式和ssh钦定的主意同样。如–remote-host=root@192.168.100.18。

行使流备份的措施如下:

# 使用tar流
innobackupex --user=root --password=123456 --stream=tar /bakdir/ >/tmp/a.tar
# 使用tar流的同时交给gzip压缩
innobackupex --user=root --password=123456 --stream=tar /bakdir/ | gzip >/tmp/a.tar.gz
# 使用tar流备份到远程主机中并归档
innobackupex --user=root --password=123456 --stream=tar /bakdir/ | ssh root@192.168.100.10  "cat -  > /tmp/`date +%F_%H-%M-%S`.tar"
# 使用tar流备份到原远程主机中并解包
innobackupex --user=root --password=123456 --stream=tar /bakdir/ | ssh root@192.168.100.10  "cat -  | tar -x -C /tmp/"

# 使用xtrabackup自带的xbstream流
innobackupex --user=root --password=123456 --stream=xbstream /bakdir/ >/tmp/b.xbs
# 解压xbstream流
innobackupex --user=root --password=123456 --stream=xbstream /bakdir/ | ssh root@192.168.100.10  "cat -  | xbstream -x -C /tmp/"
# 使用xbstream流的同时进行压缩,使用"--compress"选项
innobackupex --user=root --password=123456 --stream=xbstream --compress /bakdir/ > /bakdir/backup.xbs

注意,如若在解压备份的.tar.gz时出错,恐怕在解压的时候须要利用-i选项。如tar
-xif /tmp/b.tar/gz。

5.7 加快备份

当备份到本地的时候,能够使用”–rsync”选项,该选取用于在flush tables with
read lock后调用rsync代替cp进程复制非Innodb数据和.frm文件,加快复制速度。

但要注意,因为帮衬备份锁的版本在得到到backup
locks的时候会自动复制非Innodb数据和.frm文件,所以”–rsync”选项是无用的。

别的,该选项不能够和”–stream”选项和”–remote-host”选项同期利用。 

6.xtrabackup工具

xtrabackup工具是C语言编写的工具,在innobackupex使用C重写之后,innobackupex是该工具的叁个软链接。但是它不能够促成innobackupex的享有成效,举例xtrabackup工具未有回复魔法,而innobackupex有”–copy-back”选项来平复。

xtrabackup工具备二种常用运维情势:”–backup”和”–prepare”。还应该有七个相当少用的形式:”–stats”和”–print-param”。

是因为前文对innobackupex的牵线非常详细,xtrabackup在效劳实现上和它是黄金时代律的。所以下边将简介。

6.1 xtrabackup实现全备 

(1).备份进度

和innobackupex备份进度分化的是,xtrabackup的备份路线是由”–target-dir”选项严厉内定的,假诺内定的目录不真实,它备份的时候不会在target-dir目录中再成立时间戳子目录。

[root@xuexi data]# xtrabackup --backup --user=root --password=123456 --datadir=/mydata/data --target-dir=/bakdir/fullback

[root@xuexi data]# ls /bakdir/fullback
backup-my.cnf  ibdata1  Performance         secure_dir  xtrabackup_binlog_info  xtrabackup_info
backuptest     mysql    performance_schema  world       xtrabackup_checkpoints  xtrabackup_logfile

(2).计划进程

xtrabackup --prepare --target-dir=/bakdir/fullback

(3).苏醒进程

xtrabackup自个儿无法余烬复起,只可以通过拷贝备份集的章程来苏醒。比方使用rsync大概cp等。

别的,复苏时也同等供给MySQL是stop状态,datadir是空目录。并且拷贝实现后要修改datadir普通话件的持有者和属组为mysql顾客和组。

service mysqld stop
rm -rf /mydata/data/*
rsync -azP /bakdir/fullback/* /mydata/data
chown -R mysql.mysql /mydata/data/*

6.2 xtrabackup落到实处增备

(1).首先举办全备

xtrabackup --backup --user=root --password=123456 --datadir=/mydata/data --target-dir=/bakdir/base_full

(2).举办第贰回增备

xtrabackup --backup --user=root --password=123456 --target-dir=/bakdir/incr_bak1 --incremental-basedir=/bakdir/base_full --datadir=/mydata/data/

一样也得以在增备时行使”–incremental-lsn”来钦命从哪个lsn初阶增量备份,那和innobackupex是千篇豆蔻梢头律的。

(3).进行第2回增备

xtrabackup --backup --user=root --password=123456 --target-dir=/bakdir/incr_bak2 --incremental-basedir=/bakdir/incr_bak1 --datadir=/mydata/data/

(4).准备进度

兵马不动未雨盘算进度和innobackupex是同等的,使用”–apply-log-only”来直线向前地采取redo
log,同样,在结尾多个增备集的备选进度中不能够接纳”–apply-log-only”选项。

xtrabackup --prepare --apply-log-only --target-dir=/bakdir/base_full
xtrabackup --prepare --apply-log-only --target-dir=/bakdir/base_full --incremental-dir=/bakdir/incr_bak1
xtrabackup --prepare --target-dir=/bakdir/base_full --incremental-dir=/bakdir/incr_bak2

(5).恢复阶段

平复阶段即拷贝阶段,和后边全备的过来阶段是旭日东升致的,需求MySQL结束运营,datadir是空目录,拷贝全备目录到datadir,修改datadir的全体者和属组。

6.3 xtrabackup实现部分备份

xtrabackup部分备份和innobackupex不太同样,innobackupex的旭日东升对备份实质上是在已经备份好的备份集上导出导入表,而xtrabackup直接在备份进度中筛选要备份的靶子,它不树立在已有的备份集上。

(1).备份进程

  • Ÿxtrabackup使用”–tables”选项对应innobackupex的”–include”选项,它们是同样的,都以正则匹配完整对象引用名称。
  • 动用”–tables-file”选项钦赐枚举要备份表的列表,每行贰个表,表名要求利用完全对象援用名称。和innobackupex一样的。
  • Ÿ使用”–databases”和”–databases-file”钦点要单独备份的数据库或表,后面一个能够枚举出要备份的列表。那多个选项不能选拔通配符和正则相称。

例如:

xtrabackup --backup --user=root --password=123456 --target-dir=/bakdir/part_bak1 --datadir=/mydata/data/ --tables="^back*[.]num_*"

(2).企图进程

xtrabackup的片段备份的希图要比innobackupex方便的多,直接对备份集进行”–prepare”就能够。

xtrabackup --prepare --target-dir=/bakdir/part_bak1

Leave a Comment.