xtrabackup的初使用分分快三全天计划网站

作者:分分快三全天计划网站

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

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)

能够看见第二回增量备增多的数目被还原的归来。

160321 10:56:06 [01]        ...done

3.xtrabackup备份原理表明

随就是采纳innobackupex依然xtrabackup工具举办备份和恢复生机,都有3个步骤:备份(backup)、准备(prepare)、恢复(copy back)。

细心,xtrabackup备份进程中,先备份innodb表,再备份非innodb表。

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

1.6.4 innobackupex帮忙--rsync选项 在datadir目录实行两阶段rsync(首先未有写锁,之后有写锁,)收缩写锁具有的时光

5.5 innobackupex达成定期点苏醒

xtrabackup本人不可能落到实处按期点苏醒,只可以通过苏醒备份后通过二进制日志完毕。达成情势和平常准期点苏醒是生机勃勃致的。见:二进制日志确定地点还原数据库。

此间参照他事他说加以侦查了jeanron大师的篇章:
经过前边恶心的装置今后,接下去尝试生龙活虎番xtrabackup的备份效率。
xtrabackup首就算用于热备份innodb,恐怕是 xtradb表中多少的工具,无法备份别的项目标表,也不可能备份数据表结构;
innobackupex是将xtrabackup进行包装的perl脚本,能够备份和复苏MyISAM表以至数据表结构。
**

InnoDB: Highest supported file format is Barracuda.

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

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也会完全备份出来,假若这一个文件超大,那就一定不给力了。
只是有三个现象依然很实用的。那正是迁移表。

xtrabackup: Using 1073741824 bytes for buffer pool (set by --use-memory parameter)

3.3 苏醒进程(copy back阶段)

xtrabackup的出山小草进程实质是将备份的数据文件和结构定义等文件拷贝回MySQL的datadir。相像能够拷贝到自便机器上。

务求复苏早前MySQL必得是终止运作情形,且datadir是空目录,除非恢复生机的操作是导入表的操作。具体见后文对应的剧情。

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.1.1 扶助紧密备份,加密备份。不在援助5.0内置Innodb和5.1停放Innoddb。移除--remote-host选项

6.xtrabackup工具

xtrabackup工具是C语言编写的工具,在innobackupex使用C重写之后,innobackupex是该工具的多少个软链接。不过它不能够完毕innobackupex的有所机能,举例xtrabackup工具未有恢复功能,而innobackupex有"--copy-back"选项来过来。

xtrabackup工具有三种常用运营形式:"--backup"和"--prepare"。还会有八个非常少用的形式:"--stats"和"--print-param"。

由于前文对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)

160321 10:56:07 [00] Writing xtrabackup_binlog_info

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).进行第叁次增备

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的主人和属组。

执行:

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). 部分备份的准备和卷土重来进程

有些备份的希图和大张旗鼓进度分别是导出表和导入表的进度。见上文。 

change master to master_host='10.75.22.67', master_user='replica', master_password='eHnNCaQE3ND',MASTER_PORT=5711,MASTER_AUTO_POSITION = 1;

5.innobackupex工具

InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.

MariaDB也能够应用percona xtrabackup举办备份,不过MariaDB基于percona xtrabackup开拓了它协调的备份工具:MariaDB Backup。它遵照xtrabackup开辟,所以所用方法基本和xtrabackup相同,只是有一点点自个儿的风味。详细内容见MariaDB Backup官方手册:https://mariadb.com/kb/en/library/mariadb-backup/

--log-copy-interval 可陈设log拷贝线程检查的间隔时间

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 |
 ---------- 

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//abc/object_info.ibd.delta is 16384 bytes

5.7 加快备份

当备份到地头的时候,能够接收"--rsync"选项,该接受用于在flush tables with read lock后调用rsync代替cp进程复制非Innodb数据和.frm文件,加速复制速度。

但要注意,因为支撑备份锁的本子在获得到backup locks的时候会自行复制非Innodb数据和.frm文件,所以"--rsync"选项是于事无补的。

其它,该选项不可能和"--stream"选项和"--remote-host"选项同一时间使用。 

2.3.1 innobackupex脚本用c重写,而且只是xtrabackup的标记连接。innobackupex援助2.2版本全数的特色,可是当前已降级在下个Major版本中移除,innobackupex将不支持具备新特征的语法,同一时间xtrabackup未来支撑MyISAM的正片况且支持innobakcupex的具有个性。innobackupex先前特点的语法xtrabackup肖似支撑

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的备份恢复措施进行详细的求证,还可能会在认证经过中尽量的解说它们是怎么着专门的学问的,此外还有或者会介绍它们的有些例外效果的选项,如流备份选项。

160321 10:56:07 [00] Writing xtrabackup_slave_info

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操作。

二进制日志锁在大局范围内锁定二进制日志,所以会阻塞别的会话订正二进制日志。那样能够确认保障能够拿走到二进制日志中生机勃勃致性的岗位坐标。 

InnoDB: Allocated tablespace ID 30 for slow_query_log/global_query_review_history, old maximum was 0

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

160321 12:02:04 [01] ...done

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数据库中的数据是还是不是完好。

xtrabackup: innodb_log_group_home_dir = .

MariaDB/MySQL备份复苏连串:
备份和回复(后生可畏):mysqldump工具用法详述
备份和还原(二):导入、导出表数据
备份和苏醒(三):xtrabackup用法和法规详述

InnoDB: Starting shutdown...

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/*

Applying /data1/dbatemp/2016-03-21_12-19-21//mysql/time_zone_transition.ibd.delta to ./mysql/time_zone_transition.ibd...

3.2 计划进度(prepare阶段)

是因为备份的时候拷贝走的数据文件恐怕是不相通的,比方监控着MySQL的redo log中在拷贝进程做到后又新的工作提交了,而拷贝走的数目是未提交状态的,那么就必要对该事情前滚;若是监察和控制到的日志中有职业未提交,那么该业务就供给回滚。

只是只要只备份了myisam表或任何非事务表数据,因为备份阶段直接锁定了那么些表,所以不会有不等同的事态。

xtrabackup有一个"打算"的阶段。这些阶段的精气神儿正是对备份的innodb数据利用redo log,该回滚的回滚,该前滚的前滚,最终确定保障xtrabackup_logfile中记录的redo log已经全体行使到备份数据页上,並且达成了生龙活虎致性。当使用甘休后,会重写"xtrabackup_logfile"再度保障该redo log和备份的数据是呼应的。

计划进度无需一而再数据库,该进程能够在放肆装了xtrabackup软件的机器上海展览中心开,之所能实现是因为xtrabackup软件的内部嵌入了一个简化的innodb存款和储蓄引擎,能够通过它成功日志的运用。

InnoDB: Completed initialization of buffer pool

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);

xtrabackup --backup --target-dir=/data1/dbatemp/testx/

xtrabackup是percona共青团和少先队研究开发的备份工具,比MySQL官方的ibbackup的效应还要多。扶助myisam温全备、innodb热全备和温增备,还能够达成innodb的准时点复苏,并且备份和还原的速度都十分的快。在这里时此刻MySQL的备份达成上,考虑价格、速度、安全、风流倜傥致性等角度,xtrabackup是极其稳当的工具。

GTID援救情形

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;

InnoDB: Starting shutdown...


创设本地Full Backup(创立,prepare,还原)

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。

Applying /data1/dbatemp/2016-03-21_12-19-21//mysql/help_category.ibd.delta to ./mysql/help_category.ibd...

160321 11:29:42 [01] ...done

160321 10:56:07 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...

xtrabackup:   innodb_data_home_dir = .

支撑xtrabackup --export,这么些选项生成5.6样式的元数据文件。能够透过alter table import tablespace导入

InnoDB: Number of pools: 1

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//mysql/help_category.ibd.delta is 16384 bytes

开创单表的Backup

InnoDB: Number of pools: 1

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//mysql/time_zone.ibd.delta is 16384 bytes

prepare

mkdir mysql5711

xtrabackup: cd to /data1/mysql5711

xtrabackup: Transaction log of lsn (4151107) to (4151116) was copied.

创立增量

* 钦赐--rsync选项,加快备份过程(为了加快备份进程,同一时候减小FLUSH TBALES WITH READ LOCAK阻塞写的时间,当该采取指依期innobackupex使用rsync拷贝全体的非InnoDB文件替换cp。越发适用于有恢宏的库和表的时候会越来越快。innobackup会调用rsync两遍。1、施行flush tables with read lock前后 ;2、收缩读锁被有着的光阴内。因为第豆蔻梢头调用在刷新读锁早先,所以它仅仅一同那个非事务的数目标调换)

InnoDB: Highest supported file format is Barracuda.

/usr/local/xtrabackup/bin/innobackupex version 2.4.1 based on MySQL server 5.7.10 Linux (x86_64) (revision id: a2dc9d4)

xtrabackup: innodb_log_group_home_dir = .

xtrabackup: cd to /data1/dbatemp/2016-03-21_12-02-03

160321 12:21:48 [00]        ...done

xtrabackup: cd to /data1/mysql5711

InnoDB: Mutexes and rw_locks use GCC atomic builtins

 

160321 10:56:02 [01]        ...done

xtrabackup: open files limit requested 0, set to 204800

xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend

InnoDB:  Are you sure you are using the right ib_logfiles to start up the database? Log sequence number in the ib_logfiles is 4151355, less than the log sequence number in the first system tablespace file header, 4151373.

xtrabackup:   innodb_log_files_in_group = 1

160321 11:29:31 [01] ...done

xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend

160321 12:15:37 completed OK!

160321 10:56:00  version_check Connected to MySQL server

innobackupex /data1/dbatemp/5711back

InnoDB: Database was not shutdown normally!

innobackupex --include='abc.weibo_asset_info' /data1/dbatemp/

160321 11:04:16 innobackupex: Starting the apply-log operation

Applying /data1/dbatemp/2016-03-21_12-19-21//mysql/time_zone_leap_second.ibd.delta to ./mysql/time_zone_leap_second.ibd...

160321 11:29:42 [01] Copying ./ib_buffer_pool to /data1/mysql5711/ib_buffer_pool

Applying /data1/dbatemp/2016-03-21_12-19-21//abc/object_info.ibd.delta to ./abc/object_info.ibd...

  • 接二连三和权力 连接server,databases user通过--user和--password选项,要是不点名,xtrabackup以为是系统客商施行

xtrabackup: innodb_log_file_size = 1363148800

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//mysql/help_keyword.ibd.delta is 16384 bytes

InnoDB: Database was not shutdown normally!

InnoDB: page_cleaner: 1000ms intended loop took 13284ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//slow_query_log/global_query_review_history.ibd.delta is 16384 bytes

160321 12:21:44 [01]        ...done

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//mysql/time_zone_leap_second.ibd.delta is 16384 bytes

2.2.8 基于5.6.22 (解决当总redo log当先4G,prepare会失利的主题材料)

InnoDB: Shutdown completed; log sequence number 4151336

160321 12:02:05 >> log scanned up to (4151364)

160321 12:21:44 [01] Copying /data1/dbatemp/2016-03-21_12-19-21/slow_query_log/db.opt to ./slow_query_log/db.opt

160321 10:56:07 [01]        ...done

xtrabackup: innodb_log_files_in_group = 3

InnoDB: Starting crash recovery.

xtrabackup: The latest check point (for incremental): '4151107'

160321 10:56:03 Starting to backup non-InnoDB tables and files

160321 12:02:04 [01] Copying ./slow_query_log/global_query_review_history.ibd to /data1/dbatemp/2016-03-21_12-02-03/slow_query_log/global_query_review_history.ibd

xtrabackup: using O_DIRECT

增量backup

* 钦点--safe-slave-backup,扩展备份的生龙活虎致性。(这几个选项结束SQL线程并且等到show status中的slave_open_temp_tables为0的时候伊始备份,若无张开一时表,bakcup会立即初叶,否则SQL线程运维大概关闭知道未有张开的有时表。假若slave_open_temp_tables在--safe-slave-backup-timeount(暗中同意300秒)秒未来不为0,从库sql线程会在备份达成的时候重启)

InnoDB: Starting shutdown...

InnoDB: xtrabackup: Last MySQL binlog file position 179018, file name mysql-bin.000001

xtrabackup: using the following InnoDB configuration for recovery:

InnoDB: Using CPU crc32 instructions

xtrabackup: innodb_data_file_path = ibdata1:100M:autoextend

InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M

xtrabackup2.4.1文档

xtrabackup: innodb_data_home_dir = .

160321 10:56:06 [01] Copying ./sys/schema_auto_increment_columns.frm to /data1/dbatemp/5711back/sys/schema_auto_increment_columns.frm

Applying /data1/dbatemp/2016-03-21_12-19-21//mysql/innodb_table_stats.ibd.delta to ./mysql/innodb_table_stats.ibd...

160321 10:56:07 [01]        ...done

160321 12:21:44 [01] Copying /data1/dbatemp/2016-03-21_12-19-21/slow_query_log/global_query_review.frm to ./slow_query_log/global_query_review.frm

InnoDB: page_cleaner coordinator priority: -20

160321 12:02:04 [01] ...done

160321 12:02:10 Finished backing up non-InnoDB tables and files

160321 11:29:39 [01] ...done

$ innobackupex --user=LUKE  --password=US3TH3F0RC3 --stream=tar ./ |

创办全备

100 200 300 400 500 600 700 800 900 1000 1100 1200 1300

xtrabackup:   innodb_log_group_home_dir = /data1/dbatemp/2016-03-21_12-19-21/

InnoDB: PUNCH HOLE support not available

InnoDB: Doing recovery: scanned up to log sequence number 4151317 (0%)

from_lsn = 0

创设基于GTID的SLAVE

InnoDB: File './ibtmp1' size is now 12 MB.

160321 10:56:04 >> log scanned up to (4151116)

160321 10:56:07 [01] Copying ./sys/sys_config_update_set_user.TRN to /data1/dbatemp/5711back/sys/sys_config_update_set_user.TRN

 

创建full backup

InnoDB: Doing recovery: scanned up to log sequence number 4151364 (0%)

InnoDB: Compressed tables use zlib 1.2.3

cp my5711.cnf /data1/mysql5711

innobackupex --compress --compress-threads=8 --stream=xbstream --parallel=4 ./ > backup.xbstream

对MySQL5.5,MySQL5.6本子支持

InnoDB: FTS optimize thread exiting.

Applying /data1/dbatemp/2016-03-21_12-19-21//mysql/time_zone_name.ibd.delta to ./mysql/time_zone_name.ibd...

 

Applying /data1/dbatemp/2016-03-21_12-19-21//mysql/innodb_index_stats.ibd.delta to ./mysql/innodb_index_stats.ibd...

160321 12:21:44 [01]        ...done

万生龙活虎翻开gtid,xtrabackup_binlog_info储存gtid的值

 

rsync -rvt --exclude 'xtrabackup_checkpoints' --exclude 'xtrabackup_logfile' ./ /data1/mysql5711

Applying /data1/dbatemp/2016-03-21_12-19-21//slow_query_log/global_query_review_history.ibd.delta to ./slow_query_log/global_query_review_history.ibd...

160321 12:21:44 [01] Copying /data1/dbatemp/2016-03-21_12-19-21/abc/db.opt to ./abc/db.opt

160321 10:56:07 [00] Copying ib_buffer_pool to /data1/dbatemp/5711back/ib_buffer_pool

160321 10:56:02 [01] Copying ./slow_query_log/global_query_review_history.ibd to /data1/dbatemp/5711back/slow_query_log/global_query_review_history.ibd

InnoDB: Number of pools: 1

xtrabackup: innodb_log_files_in_group = 3

xtrabackup --prepare --target-dir=/data1/dbatemp/testb/

recover_binlog_info = 0

to_lsn = 4151355

160321 10:56:02 [01] Copying ./slow_query_log/global_query_review.ibd to /data1/dbatemp/5711back/slow_query_log/global_query_review.ibd

## stream bakcup

160321 12:21:44 [01]        ...done

InnoDB: Doing recovery: scanned up to log sequence number 4151116 (0%)

xtrabackup:   innodb_log_file_size = 1363148800

InnoDB: Setting log file ./ib_logfile2 size to 1300 MB

### 还原全备:

## 增量备份

160321 10:56:07 [00]        ...done

160321 10:56:02 [01]        ...done

160321 10:56:03 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...

/usr/local/xtrabackup/bin/innobackupex version 2.4.1 based on MySQL server 5.7.10 Linux (x86_64) (revision id: a2dc9d4)

centos5 gcc g 须求升高gcc至4.4以上上 ,消除:安装4.4.7测试通过

  • innobackupex innobackupex只是xtrabackup的贰个标记链接,innobackupex仍旧支撑像2.2本子相符援救全体的特点及语法,在将来的版本中会被降职也许移除
  • xtrabackup 备份整个MySQL实例的MyISAM,InnoDB表,XtraDB表
  • xbcrpt 加密和平解决密备份文件
  • xbstream 允许streaming和经过xbstream格式中提取文件
  • xbcloud 从云中上传或许下载全体照旧某些xbstream归档文件
  • 2.3自此的本子推荐通过xtrabackup脚本备份

对现成版本结合新本性的建议

xtrabackup: using the following InnoDB configuration for recovery:

InnoDB: Progress in MB:

xtrabackup: innodb_log_file_size = 1363148800

160321 10:56:04 [01] Copying ./mysql/time_zone_transition_type.frm to /data1/dbatemp/5711back/mysql/time_zone_transition_type.frm

xtrabackup: using the following InnoDB configuration for recovery:

 

160321 10:56:00 Connecting to MySQL server host: localhost, user: mysqlha, password: set, port: 5711, socket: /tmp/mysql5711.sock

xtrabackup: innodb_log_group_home_dir = .

160321 11:29:34 [01] ...done

160321 10:56:00 innobackupex: Starting the backup operation

InnoDB: 5.7.10 started; log sequence number 4151116

/usr/local/xtrabackup/bin/innobackupex version 2.4.1 based on MySQL server 5.7.10 Linux (x86_64) (revision id: a2dc9d4)

160321 10:56:04 [01] Copying ./mysql/func.MYD to /data1/dbatemp/5711back/mysql/func.MYD

160321 10:56:02 [01] Copying ./mysql/time_zone_name.ibd to /data1/dbatemp/5711back/mysql/time_zone_name.ibd

InnoDB: Progress in MB:

支撑5.6引入的innodb buffer pool预载。buffer pool dumps可以改换依然导入加快开动。在备份时buffer pool dump拷贝到备份目录,在平复星等拷贝回data目录,

InnoDB: Initializing buffer pool, total size = 1G, instances = 1, chunk size = 128M

 

160321 10:56:03 [01] Copying ./slow_query_log/global_query_review_history.frm to /data1/dbatemp/5711back/slow_query_log/global_query_review_history.frm

Failed to get master binlog coordinates from SHOW SLAVE STATUS

xtrabackup: open files limit requested 0, set to 204800

5.6部分表导出还原测量试验平常

 

Applying /data1/dbatemp/2016-03-21_12-19-21//slow_query_log/global_query_review.ibd.delta to ./slow_query_log/global_query_review.ibd...

InnoDB: Starting crash recovery.

xtrabackup: innodb_log_file_size = 8388608

160321 11:29:42 completed OK!

InnoDB: Uses event mutexes

160321 10:56:04 [01]        ...done

xtrabackup: uses posix_fadvise().

160321 12:02:10 Backup created in directory '/data1/dbatemp/2016-03-21_12-02-03'

last_lsn = 4151364

xtrabackup: This target seems to be already prepared with --apply-log-only.

160321 11:29:38 [01] ...done

InnoDB: Number of pools: 1

prepare base

### base backup:

 

160321 11:29:38 [01] ...done

InnoDB: Number of pools: 1

160321 10:56:02 >> log scanned up to (4151116)

160321 12:02:06 Executing FLUSH TABLES WITH READ LOCK...

InnoDB: Using CPU crc32 instructions

 

5.5得以健康备份还原

160321 12:02:10 [00] Copying ib_buffer_pool to /data1/dbatemp/2016-03-21_12-02-03/ib_buffer_pool

InnoDB: Setting log file ./ib_logfile1 size to 1300 MB

xtrabackup: innodb_data_file_path = ibdata1:100M:autoextend

InnoDB: xtrabackup: Last MySQL binlog file position 179018, file name mysql-bin.000001

[root@hebe211 dbatemp]# cat 2016-03-21_12-19-21/xtrabackup_checkpoints 

InnoDB: page_cleaner coordinator priority: -20

 

xtrabackup: This target seems to be not prepared yet.

xtrabackup: Generating a list of tablespaces

160321 11:29:38 [01] ...done

备份还原单表

InnoDB: Allocated tablespace ID 30 for slow_query_log/global_query_review_history, old maximum was 0

alter table weibo_asset_info import tablespace;

xtrabackup: innodb_log_file_size = 8388608

alter table weibo_asset_info discard tablespace;

set global gtid_purged=“a34b5a32-e04e-11e5-a5bf-782b22675711:1”;

InnoDB: Highest supported file format is Barracuda.

InnoDB: FTS optimize thread exiting.

InnoDB: Doing recovery: scanned up to log sequence number 4151364 (0%)

xtrabackup: innodb_log_group_home_dir = .

InnoDB: New log files created, LSN=4151291

160321 12:02:03 Connecting to MySQL server host: localhost, user: mysqlha, password: set, port: 5711, socket: /tmp/mysql5711.sock

At the end of a successful apply-log run innobackupex

xtrabackup:   innodb_log_group_home_dir = .

InnoDB: Starting crash recovery.

Applying /data1/dbatemp/2016-03-21_12-19-21//mysql/servers.ibd.delta to ./mysql/servers.ibd...

别的xtrabackcup其余Boost版本要求1.59.0版本或以上,近来centos5,6暗中认可是1.41.0。杀绝:升级至1.59.0

innobackupex --apply-log --export /data1/dbatemp/2016-03-21_12-46-24/

* 流式备份通过--stream钦定格式为xbtream而代表tar,扶持streaming格式的竞相备份和削减

prepare backup

160321 12:02:03 version_check Connected to MySQL server

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//mysql/plugin.ibd.delta is 16384 bytes

InnoDB: Compressed tables use zlib 1.2.3

160321 10:56:00  version_check Done.

InnoDB: Compressed tables use zlib 1.2.3

160321 10:56:03 [01]        ...done

xtrabackup --backup --target-dir=/data1/dbatemp/testb/

xtrabackup: using the following InnoDB configuration for recovery:

xtrabackup: innodb_data_home_dir = .

innobackupex --incremental --apply-log --redo-only /data1/dbatemp/2016-03-21_12-02-03/ --use-memory=1G 

2.1.6 innobackupex --force-non-empty-directories

160321 12:02:10 [00] ...done

160321 12:21:44 [01]        ...done

160321 11:29:27 [01] Copying ib_logfile0 to /data1/mysql5711/ib_logfile0

2.3.2 命令行语法跟随MySQL5.6的转换而生成。其它命令行帮忙--datadir

2.2.1 移除xtrabackup_56 xtrabakcup_55,只保留xtrabakcup.移除Build脚本,支持cmake编译。基于5.6.16

160321 11:29:38 [01] ...done

InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0

160321 12:02:03 version_check Done.

光复备份(需手动)

compact = 0

InnoDB: Shutdown completed; log sequence number 4151373

xtrabackup: innodb_data_file_path = ibdata1:100M:autoextend

LRU DUMP

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//mysql/engine_cost.ibd.delta is 16384 bytes

100 200 300 400 500 600 700 800 900 1000 1100 1200 1300

InnoDB: Doing recovery: scanned up to log sequence number 4151364 (0%)

InnoDB: Using CPU crc32 instructions

innobackupex --defaults-file=/data1/mysql5711_bak/my5711.cnf.bakuse --copy-back /data1/dbatemp/5711back/

xtrabackup:   innodb_log_files_in_group = 3

MySQL binlog position: filename 'mysql-bin.000002', position '1099', GTID of the last change 'a34b5a32-e04e-11e5-a5bf-782b22675711:1'
MySQL slave binlog position: master host '10.75.22.67', purge list 'a34b5a32-e04e-11e5-a5bf-782b22675711:1'

 

......

innobackupex --decrypt and innobackupex --decompress,

xtrabackup: The latest check point (for incremental): '4151355'

InnoDB: 32 non-redo rollback segment(s) are active.

xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(4151355)

xtrabackup --backup --target-dir=/data1/dbatemp/testa_incremental --incremental-basedir=/data1/dbatemp/testa

Using server version 5.7.11-log

160321 12:21:48 completed OK!

 

160321 11:29:42 [01] ...done

MySQL binlog position: filename 'mysql-bin.000001', position '154'

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//mysql/slave_worker_info.ibd.delta is 16384 bytes

xtrabackup: using O_DIRECT

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//mysql/slave_relay_log_info.ibd.delta is 16384 bytes

xtrabackup --prepare --apply-log-only --target-dir=/data1/dbatemp/testa/ --incremental-dir=/data1/dbatemp/testa_incremental

160321 12:02:03 >> log scanned up to (4151364)

.160321 10:56:07 >> log scanned up to (4151116)

160321 10:56:03 Executing FLUSH TABLES WITH READ LOCK...

xtrabackup: innodb_data_file_path = ibdata1:100M:autoextend

IMPORTANT: Please check that the backup run completes successfully.

InnoDB: Shutdown completed; log sequence number 4151291

160321 12:02:10 [00] ...done

xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(4151107)

prepare单表bakcup

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//mysql/time_zone_transition_type.ibd.delta is 16384 bytes

innobackupex

160321 10:56:01 >> log scanned up to (4151116)

160321 10:56:00  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=5711;mysql_socket=/tmp/mysql5711.sock' as 'mysqlha'  (using password: YES).

InnoDB: Database was not shutdown normally!

xtrabackup

 

160321 12:21:48 [01]        ...done

测量试验5.6,5.7展开GTID下能够健康备份,还原

 

xtrabackup: innodb_log_group_home_dir = .

Applying /data1/dbatemp/2016-03-21_12-19-21//mysql/time_zone_transition_type.ibd.delta to ./mysql/time_zone_transition_type.ibd...

xtrabackup: innodb_log_files_in_group = 1

Percona Xtrabackup 2.4.1

160321 10:56:03 [01]        ...done

160321 10:56:04 [01]        ...done

Applying /data1/dbatemp/2016-03-21_12-19-21//mysql/time_zone.ibd.delta to ./mysql/time_zone.ibd...

InnoDB: Number of pools: 1

Applying /data1/dbatemp/2016-03-21_12-19-21//mysql/help_keyword.ibd.delta to ./mysql/help_keyword.ibd...

xtrabackup: Generating a list of tablespaces

160321 11:29:42 [01] ...done

160321 10:56:02 [01] Copying ./abc/object_info.ibd to /data1/dbatemp/5711back/abc/object_info.ibd

 

2.2.6 通过show variables读取Mysql选项。在早先化表扫描的时候输出更详细消息

InnoDB: xtrabackup: Last MySQL binlog file position 179018, file name mysql-bin.000001

2.1.4 MySQL versions 5.1.70, 5.5.30, 5.6.11 

xtrabackup: Starting InnoDB instance for recovery.

InnoDB: xtrabackup: Last MySQL binlog file position 179018, file name mysql-bin.000001

亟待安装qpress

生成QP文件

xtrabackup: using the following InnoDB configuration for recovery:

prints “completed OK!”.

InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

xtrabackup: export metadata of table 'abc/weibo_asset_info' to file `./abc/weibo_asset_info.exp` (4 indexes)
xtrabackup: name=PRIMARY, id.low=68, page=3
xtrabackup: name=ip_in, id.low=69, page=4
xtrabackup: name=owner, id.low=70, page=5

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//mysql/gtid_executed.ibd.delta is 16384 bytes

xtrabackup:   innodb_data_home_dir = .

2.0.3 支持--move-back

InnoDB: Number of pools: 1

InnoDB: Setting log file ./ib_logfile101 size to 1300 MB

160321 10:56:02 [01] Copying ./mysql/slave_worker_info.ibd to /data1/dbatemp/5711back/mysql/slave_worker_info.ibd

xtrabackup: Using 1073741824 bytes for buffer pool (set by --use-memory parameter)

centos5,6 须求晋级cmake至2.8.2版本以上,消除:安装cmake版本3.4.3测量试验通过

160321 12:21:44 [01] Copying /data1/dbatemp/2016-03-21_12-19-21/abc/weibo_asset_info.frm to ./abc/weibo_asset_info.frm

compact = 0

160321 12:02:10 [00] Writing xtrabackup_info

......

160321 11:04:32 completed OK!

InnoDB: not started

InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.

160321 12:21:48 [00] Copying /data1/dbatemp/2016-03-21_12-19-21//xtrabackup_binlog_info to ./xtrabackup_binlog_info

xtrabackup --prepare --apply-log-only --target-dir=/data1/dbatemp/testa/

xtrabackup:   innodb_log_file_size = 8388608

InnoDB: xtrabackup: Last MySQL binlog file position 179018, file name mysql-bin.000001

Applying /data1/dbatemp/2016-03-21_12-19-21//mysql/slave_worker_info.ibd.delta to ./mysql/slave_worker_info.ibd...

InnoDB: Database was not shutdown normally!

to_lsn = 4151355

release历史

声称:本文由本人的同事@fiona514编辑,是笔者看过的最用心的普通话表达介绍,刚烈推荐大家学习运用。

支撑streaming增量备份

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//mysql/servers.ibd.delta is 16384 bytes

InnoDB: xtrabackup: Last MySQL binlog file position 179018, file name mysql-bin.000001

Applying /data1/dbatemp/2016-03-21_12-19-21//mysql/plugin.ibd.delta to ./mysql/plugin.ibd...

160321 12:21:48 [00] Copying /data1/dbatemp/2016-03-21_12-19-21//xtrabackup_info to ./xtrabackup_info

InnoDB: Mutexes and rw_locks use GCC atomic builtins

160321 12:02:10 [00] ...done

160321 12:02:10 [00] ...done

           prints "completed OK!".

Innobackupex

创设一个full backup

/usr/local/xtrabackup/bin/innobackupex version 2.4.1 based on MySQL server 5.7.10 Linux (x86_64) (revision id: a2dc9d4)

InnoDB: page_cleaner coordinator priority: -20

InnoDB: GCC builtin __sync_synchronize() is used for memory barrier

160321 12:02:10 [00] Writing backup-my.cnf

160321 11:29:38 [01] Copying ./abc/weibo_asset_info.frm to /data1/mysql5711/abc/weibo_asset_info.frm

Applying /data1/dbatemp/2016-03-21_12-19-21//sys/sys_config.ibd.delta to ./sys/sys_config.ibd...

160321 12:21:44 [01] Copying /data1/dbatemp/2016-03-21_12-19-21/slow_query_log/global_query_review_history.frm to ./slow_query_log/global_query_review_history.frm

160321 12:02:06 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...

xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend

backup_type = log-applied

InnoDB: GCC builtin __sync_synchronize() is used for memory barrier

InnoDB: Mutexes and rw_locks use GCC atomic builtins

xtrabackup: innodb_log_files_in_group = 1

### prepare增量备份

160321 12:21:44 [01]        ...done

160321 10:56:07 [00]        ...done

* 在此以前脚本使用第三方压缩工具pbzip2进行压缩。建议通过--compress 和--compress-threads选项举行交互压缩

incremental backup from 4151355 is enabled.

160321 11:29:38 [01] ...done

160321 12:21:48 [00]        ...done

innobackupex --compress --compress-threads=8 /data1/dbatemp/

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//mysql/slave_master_info.ibd.delta is 16384 bytes

Applying /data1/dbatemp/2016-03-21_12-19-21//mysql/help_topic.ibd.delta to ./mysql/help_topic.ibd...

InnoDB: Shutdown completed; log sequence number 4151373

创制生龙活虎份备份

prepare

160321 11:29:38 [01] Copying ./slow_query_log/global_query_review_history.ibd to /data1/mysql5711/slow_query_log/global_query_review_history.ibd

160321 12:02:10 completed OK!

一时线上版本一大半在1.6.3和1.5版本。超多须要是因此第三方工具援助。结合2.4.1的新特色和release历史和当下意况,提议几点如下:

160321 10:56:02 [01]        ...done

xtrabackup: using the following InnoDB configuration:

InnoDB: Uses event mutexes

xtrabackup: innodb_data_home_dir = .

编写翻译及软件信赖

* 针对紧凑备份和增量备份在即使某个场景下极其有用,与刘伟同志研商过暂且继续先不做陈设成功统意气风发版本中去

160321 11:29:34 [01] Copying ib_logfile2 to /data1/mysql5711/ib_logfile2

感兴趣的可今后后看。。。。。。

160321 10:56:07 All tables unlocked

160321 10:56:02 [01]        ...done

160321 10:56:07 completed OK!

InnoDB: Completed initialization of buffer pool

xtrabackup:   innodb_log_file_size = 8388608

Applying /data1/dbatemp/2016-03-21_12-19-21//mysql/server_cost.ibd.delta to ./mysql/server_cost.ibd...

InnoDB: Uses event mutexes

160321 10:56:07 [00]        ...done

InnoDB: Progress in MB:

160321 12:02:03 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=5711;mysql_socket=/tmp/mysql5711.sock' as 'mysqlha' (using password: YES).

InnoDB: PUNCH HOLE support not available

Applying /data1/dbatemp/2016-03-21_12-19-21//ibdata1.delta to ./ibdata1...

 

160321 10:56:07 Executing UNLOCK TABLES

160321 10:56:00  version_check Executing a version check against the server...

xtrabackup: using the following InnoDB configuration:

innobackupex --apply-log /data1/dbatemp/5711back

160321 12:02:04 [01] Copying ./slow_query_log/global_query_review.ibd to /data1/dbatemp/2016-03-21_12-02-03/slow_query_log/global_query_review.ibd

160321 10:56:01 [01] Copying ./ibdata1 to /data1/dbatemp/5711back/ibdata1

InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

160321 11:29:38 [01] Copying ./slow_query_log/global_query_review.frm to /data1/mysql5711/slow_query_log/global_query_review.frm

100 200 300 400 500 600 700 800 900 1000 1100 1200 1300

Applying /data1/dbatemp/2016-03-21_12-19-21//mysql/slave_master_info.ibd.delta to ./mysql/slave_master_info.ibd...

chown -R my5711:mysql mysql5711

InnoDB: File './ibtmp1' size is now 12 MB.

2.2.5 基于5.6.21

xtrabackup: using the following InnoDB configuration for recovery:

160321 11:29:38 [01] Copying ./slow_query_log/db.opt to /data1/mysql5711/slow_query_log/db.opt

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//ibdata1.delta is 16384 bytes

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

.160321 12:02:10 >> log scanned up to (4151364)

1.9.1 帮助压缩备份,在此之前能能streaming备份之后通过外界工具压缩

xtrabackup --prepare --target-dir=/data1/dbatemp/testa/

xtrabackup: Transaction log of lsn (4151355) to (4151364) was copied.

           At the end of a successful backup run innobackupex

InnoDB: Waiting for purge to start

xtrabackup: Generating a list of tablespaces

160321 10:56:03 [01] Copying ./slow_query_log/db.opt to /data1/dbatemp/5711back/slow_query_log/db.opt

2.1.0 援助mysql5.6的兼具个性(GTID, 可活动表空间,独立undo表空间,5.6样式的buffer pool导出文件)

MySQL binlog position: filename 'mysql-bin.000002', position '154'

from_lsn = 4151355

prepare incremental

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//mysql/innodb_index_stats.ibd.delta is 16384 bytes

Percona Xtrabackup Features

xtrabackup: Stopping log copying thread.

将weibo_asset_info.exp和weibo_asset_ibd文件传到目的机目的目录中

xtrabackup: innodb_data_file_path = ibdata1:100M:autoextend

InnoDB: PUNCH HOLE support not available

160321 12:02:10 Executing UNLOCK TABLES

```

160321 11:29:38 [01] Copying ./xtrabackup_info to /data1/mysql5711/xtrabackup_info

InnoDB: Initializing buffer pool, total size = 1G, instances = 1, chunk size = 128M

xtrabackup --backup --target-dir=/data1/dbatemp/testa/

prepare增量:

InnoDB: Doing recovery: scanned up to log sequence number 4151317 (0%)

InnoDB: not started

InnoDB: Completed initialization of buffer pool

xtrabackup: innodb_log_group_home_dir = .

160321 12:02:10 [00] Writing xtrabackup_binlog_info

prepare base

Applying /data1/dbatemp/2016-03-21_12-19-21//abc/weibo_asset_info.ibd.delta to ./abc/weibo_asset_info.ibd...

  • 热备
  • 增量复制
  • 将减削后的备份stream到其余server
  • 在线的在mysql server实例之间移动表
  • 更随便的搭建新的从库
  • 备份不扩展server的下压力

InnoDB: Uses event mutexes

bzip2

$ xtrabackup --user=DVADER --password=14MY0URF4TH3R --backup --target-dir=/data/bkps/

  • 任何总是选项
  • Option
  • Description
  • --port
  • 通过TCP/IP连接数据库的端口号
  • --socket
  • 本地连接sockect
  • --host
  • 通过TCP/IP连接的数据库的IP
  • 批准和权力
    接连几天来数据库之后,要求server文件系统层面datadir目录的读写和实行权限
    关于数据库层面,供给如下权限:
    1,RELOAD和LOCK TABLES权限
    要求在拷贝文件此前FLUSH TABLES WITH READLOCKS和FLUSH ENGINE LOGS。此外当展开Backup Locks,实践LOCK TABLES FOMurano BACKUP和LOCK BINLOG FO逍客 BACKUP供给这两样权限
    2,REPLICATION CLIENT权限 获得binlog的点位
    3,CREATE TABLESPACE权限 目的是为着导入tables
    4,PROCESS权限 show processlist
    5, SUPE大切诺基权限 复制情形下start slave 和stop slave
    6,CREATE权限 目标是开创PERCONA_SECHEMA.xtrabackup_history数据库和表
    7,INSERT权限 在PERCONA_SCHEAM.xtrabackup_history表中加进历史记录
    8,SELECT权限 使用innobackupex --incremental-history-name 或许innobackupex --incremental-history-uuid目标是为了查询PERCONA_SCHEMA.xtrabackup表中innodb_tolsn的值
    创设叁个可以看到full backup最小权限的数据库顾客:
    mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cret';
  • mysql> GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
  • mysql> FLUSH PRIVILEGES;
  • 全量备份生命周期-FULL Backups
    **
    通过Innobackupex创建八个备份**

innobackupex是二个通过xtrabackup结合了xbstream和xbcrypt等来备份一整个数据库实例的工具

蒋健三个生龙活虎体化备份,除了连接数据库的选项还只必要贰个参数,备份存储目录的路子

$ innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/

后来检查确认消息输出的尾声风姿罗曼蒂克行:

innobackupex: Backup created in directory ’/path/to/BACKUP-DIR/2013-03-25_00-00-09’

innobackupex: MySQL binlog position: filename ’mysql-bin.000003’, position 1946

111225 00:00:53  innobackupex: completed OK!

备份会最后存款和储蓄在以时间戳命名的目录内 

在尾巴部分,在后台,innobackupex被称作二进制xtrabackup来备份Innodb全数表的多寡相同的时间拷贝全数的frm表定义文件,数据,和MyISAM,ME陆风X8GE,CSV表相关的文书,触发器,数据库配置文件到多少个时间戳的目录中去

任何急需思索的选择

--no-timestamp:告诉innobackupex不要创立三个年华戳目录来积存备份

--defaults-file:能够提供innobackupex别的的数据库配置文件。唯黄金年代的限量就是必得放在第三个参数的地点

innobackupex --defaults-file=/tmp/other-my.cnf --user=XXX --password=XXX /path/to/backup

PREPARE阶段,用innobackupex prepare Full Backup

在创设了一个backup之后,数据还不能够用来恢复,因为有未提交的事情未成功只怕职业日志需求重播,做这个待定的操作须求数据文件豆蔻梢头致.那么些皆以prepare阶段的目标,风度翩翩旦那个产生,数据就能够用了

供给内定选项apply-log:

innobakupex --apply-log /path/to/BACKUP-DIR

万十分之一功了,innobackupex操作之后,数据是即时可用的

在后台,innobackupex通过读取backup-my.cnf早先prepare进度,在后头,innobackupe重播已交由的政工并回滚未提交的职业,后生可畏旦这个操作完结,全体音信在表空间中(innodb文件),Log文件被重新建立.这几个验证了调用xtrabackup --prepare三回。

在乎这一个preparation不适合增量备份,假诺依照增量备份,将无法'add'增量部分

通过Innobackupex还原Full Backup

--copy-back选项,在server的datadir目录实行生龙活虎份备份的回复

innobakupex --copy-back /path/to/BACKUP-DIR

--copy-back:做数据恢复生机时将备份数据文件拷贝到MySQL服务器的datadir 

会跟实际my.cnf文件里的布署,拷贝全体数占领关的公文到datadir。

注意:

1.datadir目录必需为空。除非钦点innobackupex --force-non-empty-directorires选项钦定,不然--copy-backup选项不会覆盖

2.在restore早先,必得shutdown MySQL实例,你不能够将贰个周转中的实例restore到datadir目录中

3.出于文件属性会被封存,大部分情况下你须求在运转实例此前将文件的属主改为mysql,那几个文件将属于制造备份的客户

chown -R my5711:mysql /data1/dbrestore

以上亟待在客户调用Innobackupex在此之前到位

其他项目备份

经过Innobackupex进行增量备份

在各种备份之间并不是负有的新闻都有浮动,增量备份的目标是减掉需求的存储容积和成立风姿罗曼蒂克份备份的年华

那些足以做到是因为每种InnoDB的页都有二个LSN,那一个LSN也正是一整个数据库的本子号码,每一回数据库改革,那么些Number就能依次增加

增量备份就是拷贝某一个钦赐LSN早前的装有page

借使这个pages以她们各自的种种被安放一齐,应用那个日记将再度创制影响数据库的进度,在开创backup时刻产生多少

透过innobakupex创建风流浪漫份增量备份

率先,创立大器晚成份全量备份作为基础用于之后的增量备份

innobackupex /data1/dbbackup 

将会在/data1/dbbackup目录生成贰个满含时间戳的目录,比方假如备份是在前三个月月首最后一天制造.BASEDIXC60是/data1/dbbackup/二零一四-02-29_23-01-18

能够由此innobackupex --no-timestamp选项覆盖这种表现,备份将会被创立在内定的目录中

假若检查BASE-DIPRADO目录中的xtrabackup-checkpoints文件,你能观察如下:

backup_type = full-backuped

from_lsn = 0

to_lsn = 1291135

其次天创造风流倜傥份增量备份,通过--incremental选项并提供BASEDI汉兰达:

innobackupex --incremental /data1/backups --incremental-basedir=BASEDIR

会在/data1/backups目录里生成另风流罗曼蒂克份包括时间戳的目录,正是/data1/backups/2015-03-01_23-01-18,该目录包罗了增量备份,大家称之为INCREMENTAL-DIRAV4-1,假如检查该目录的xtrabackup-checkpoints文件。会见到如下:

backup_type = incremental

from_len = 1291135

to_lsn = 1352113

看似的,第四日创制另生龙活虎份增量备份。可是第二天的增量备份产生了BASEDI中华V

innobackupex --incremental /data/backups --incremental-basedir=INCRENMENTAL-DIR-1

结果生成/data1/backups/二零一五-03-02_23-01-18,用INCREMENTAL-DIR-2来表示:

backup_type = incremental

from_lsn = 1352113

to_lsn  = 1358967

像大家事先所说,增量备份只拷贝LSN大于七个钦命值的pages,提供LSN会产生相似数量的目录:

innobackupex --incremental /data/backups --incremental-lsn=1291135

innobackupex --incremental /data/bakcups --incremental-lsn=1358967

因为全备或上贰个增备并非总在系统中(如备份后传输到长途),用这种办法做增量备份特别常有效。这种经过只能影响XtraDB和InnoDB表,别的斯特林发动机的表如MyISAM.在做增量备份的长河中时候会拷贝全体。

通过innobakupex prepare生龙活虎份增量备份

prepare增量备份与全量备份差异。这里必要额外注意:

  • 第一头有付出过的政工须要在每份备份中个重播(apply-log BASEDIRAV4)
  • 这么些将增量的一些联合到全量备份的base中去()
  • 下一场,未提交的政工必须回滚,为了有三个随就可以用的备份

若是在依附base backup中重播提交业务回滚未提交业务,是不能add增量的。假设对于增量的这么做,是不可能add从那刻起的数量和任何的增量。

--redo-only --apply-log:

强制备份日志时只redo ,跳过rollback。那在做增量备份时特别要求

innobackupex --apply-log --redo-only BASE-DIR

下一场,第二个增量Backup能apply给base backup:

innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCRENMENTAL-DIR-1

 

若果未有--incremental-dir棉被服装置了,innobackupex会接纳在basedir里近些日子创立的一个子目录

那会儿,BASE-DI中华V满含了平素到第叁遍增量备份的数码,注意全备永恒都在base backup目录里

下一场在第2个增量备份上再也那么些进度:

innobackupex --apply-log BASE-DIR --incremental-dir=INCREMENTAL-DIR-2

假使现身'complete ok',最后的数据会都merge到base backup目录中去(BASE-DIQashqai)

留神:--redo-only用于merging全部的增量除了末了三个

您能够透过以上的进度给base扩展更加的多的增量,只要服从备份的到位的大运顺序依次就能够。假设用错误的意气风发大器晚成Merge增量,备份就全盘无用了。如若对apply的各种有疑问,能够检查各类目录的xtrabackup_checkpoints文件

比方您对base backup目录merge完全体的增量,接下去prepare,回滚未提交的作业:

innobackupex --apply-log BASE-DIR  (innobackupex回滚未提交的事情)

这儿的backup可以至时用于还原了,此步preparation是可选的,然和,就算您回复未有那步,database server会伊始rollback未提交的事务。借使爆发crash时,database server会做同样的做事。那会延迟db server的开发银行时间,如若此步prepare则会幸免

稳重innobacupex不会创立iblog*那一个文件,假若想要创立,用xtrabackup -prepare成效于该目录,不然,那些文件在server运维的时候被创立

因而innobackupex还原增量备份

preparing增量备份之后,base dir正是一个full backup,还原情势:

innobackupex --copy-back BASE-DIR

经过xbstream和tar进行增量流式备份

运用xbstream streaming选项,备份能够被打包成自定义的xbstream格式,雷同要求二个Base backup

innobakcupex /data/bakcups

本地备份:

innobackupex --incremental --incremental-lsn=LSN-number --stream=xbstream ./ > incremental.xbstream

解压备份:

xbstream -x < incremental.xbstream

做风流罗曼蒂克份本地备份并streaming到长途服务器然后解压:

innobackupex  --incremental --incremental-lsn=LSN-number --stream=xbstream ./ | /

ssh user@hostname " cat - | xbstream -x -C > /backup-dir/"

--stream=[tar]

备 份文件输出格式, tar时利用tar4ibd , 该公文可在XtarBackup binary文件中得到.假诺备份时有钦赐--stream=tar, 则tar4ibd文件所处目录必须要在$PATH中(因为运用的是tar4ibd去减弱, 在XtraBackup的binary包中可收获该文件)。

在 使用参数stream=tar备份的时候,你的xtrabackup_logfile恐怕会一时放在/tmp目录下,倘令你备份的时候现身写入相当的大的话 xtrabackup_logfile大概会非常大(5G ),很或者会撑满你的/tmp目录,能够经过参数--tmpdir钦点目录来解决那么些标题

有的备份

只备份部分的表恐怕db,前提是开启innodb_file_per_table选项,每张表有独立的表空间。你无法透过轻松地将prepared的意气风发对备份使用--copy-back选项直接复制回数据目录,而是要通过导入表的可行性来得以完成复苏。当然,有个别情形下,部分备份也足以直接通过--copy-back举行理并答复原,但这种方法还原而来的数码非常多会产生多少不等同的标题,由此,无论如何不推荐应用这种措施。

开创部分备份

有二种方法内定备份这部分数量

  • --include选项 使用正则表明式形式时,必要为其钦定相称要备份的表的完全名称,即databasename.tablename

innobackupex --include='^mydatabase[.]mytable' /path/to/backup

上边的一声令下只备份表名相相配的数据。--include选项传递给xtrabackup --tables,对各类库中的各样表逐大器晚成相称,由此会创制全数的库,然则是空的目录。

  • --tables-file选项 此选项的参数需假诺三个文书名,此文件中每行李包裹罗二个要备份的表的完整名称,格式为databasename.tablename。

echo "mydatabase.mytable" > /tmp/tables.txt

innobackupex --tables-file=/tmp/tables.txt /path/to/backup

该选项传递给xtrabackup --tables-file,与--table选项差异,独有要备份的表的库才会被创造

  • --databases选项 此选项采用的参数为多少名,要是要内定八个数据库,相互间需求以空格隔开分离;同一时候,在内定某数据库时,也能够只钦点此中的某张表。别的,此选项也得以担负叁个文本为参数,文件中每豆蔻年华行为叁个要备份的对象

innobackupex --databases="mydatabase.mytable mysql" /path/to/backup

该选择对innodb引擎表无效,还是会备份的

prepare部分备份

prepare部分备份的进度看似于导出表的进度,要使用--export选项进行:

innobackupex --apply-log --export /path/to/partial/backup

此命令施行进程中,innobackupex会调用xtrabackup命令从数量字典中移除缺失的表,因而,会显得出无尽有关“表荒诞不经”类的告诫音讯。同时,也博览会示出为备份文件中存在的表创造.exp文件的相干信息。

回复部分备份

过来部分备份的经过跟导入表的历程同样。当然,也足以由此一贯复制prepared状态的备份直接至数据目录中落到实处苏醒,不要那个时候必要数据目录处于相同状态。

减掉备份

备份innodb表的时候可能会忽视辅助索引,会使备份更紧密并且节约磁盘空间。瑕疵是协助索引重新建立导致backup prepare的进度会须求更加长的岁月。备份大小分化在于帮助索引大小的区分。。比如未有加--compat选项的full backup.

留意:压缩备份不帮忙系统表空间,,所以需求开垦innodb-file-per-table选项

创建压缩备份

innobackupex --compact /data/backups

会在/data/backups目录下创办个日子戳目录

豆蔻梢头经济检察查BASE-DIWrangler里面包车型大巴xtrabackup_checkpoints文件,能来看如下:

backup_type = full-backuped
from_lsn = 0
to_lsn = 2888984349
last_lsn = 2888984349
compact = 1

还未--compact选项compact的值为0,这种方法方便的自己争论备份是还是不是带有帮助索引

preparing压缩备份

preparing压缩备份须求重新建构索引,prepare backup必要--rebuild-index跟随--apply-logs

innobackupex --apply-log --rebuild-indexes /data/backups/2016-03-14_10-29-48

命令输出,除了职业innobackupex输出,还包含了目录重新建立的新闻

复原压缩备份

innnobackupex有贰个--copy-back选项。功用是讲生龙活虎份backup还原到server的datadir目录中去

innobackupex --copy-back /path/to/backup-dir

会将具备数占有关的保有文件拷贝到datadir目录,由my.cnf配置文件定义

加密备份

2.1版本引进,加密还是解密本地备份恐怕由xbstream选项备份的流式备份,加密由libgcrypt库达成

创办加密备份

加密须求钦命以下选项(--encrypt-key和--encrypt-key-file只需点名在这之中之后生可畏就能够)

  • --encryption=ALGO奥德赛ITHM 前段时间帮衬的算法有ASE128,AES192,AES256
  • --encrypt-key=ENCRYPTION_KEY 使用方便长度加密key,因为会记录到命令行,所以不引入应用
  • --encrypt-key-file=KEYFILE 文件必需是二个大致二进制或许文本文件 加密key可透过以下命令行命令生成,生成的值可用于Key  openssl rand -base64 24 

--encrypt-key选项使用栗子:

innobackupex --encrypt=AES256 --encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK Yzfs" /data/backups

优化加密进程

引进多个新的选项增长速度加密进程,--encrypt-threads和--encrypt-chunk-size

--encrypt-threads选项并行加密,--encrypt-chunk-size内定各类加密线程buffer的朗朗上口(字节,暗中认可64K)

解密加密备份

可由此xbcrypt二进制解密,以下生机勃勃行解密全体目录:

for i in ‘find . -iname "*.xbcrypt"‘; do xbcrypt -d --encrypt-key-file=/root/secret_key --encrypt-

prepare加密备份

在备份解密之后,能够通过full backup同样的措施经过--apply-logs prepare

innobackupex --apply-log /data/backups/2016-03-14_08-31-35/

留心,xtrabackup不会活动移除加密文件,为了清理Backup目录,需求客商手动rm *.xbcrypt文件

大张旗鼓加密备份

--copy-back选项,通过拷贝文件到datadir目录还原备份

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

高端个性

流式和减削备份

streaming形式,发送backup以tar也许xbstream格式到标准输出,取代拷贝文件到backup目录,那样允许你选用别的程序过滤bakcup的输出提供越来越灵敏的备份,比方,通过管道将backup的输出压缩工具举行压缩,流式备份此中的黄金时代项好处是是由此unix管道备份能够自行加密

选用streaming天性,须求动用--stream选项并提供stream的格式(tar大概stream),和在何地存有时文件

innobackpex --stream=tar /tmp

innobackup以--log-stream形式在子进程中运营xtrabackup,並且重定向日志到有的时候文件,之后接纳xbstream以出色的xbstream格式将有着数据文件stream到职业输出。

开启压缩时,xtrabakup以钦赐的压缩算法,压缩全数数据,除了元数据和非innodb文件等不被压缩文件,如今算法只支持quicklz。结果文件为qpress归档文件

动用xbstream作为stream选项,备份能够并行拷贝和减削,大大的加快了备份进程,以幸免备份同不时间减弱和加密。必要首先先解密之后再解压缩

使用xbstream栗子

积累完整备份到单一文件

innobackupex --stream=xbstream /root/backup/ > /root/backup/backup.xbtream

stream并且压缩这么些备份:

innobackupex --stream=xbstream --compress /root/backup/ > /root/backup/backup.xbstream

解包到/root/backup/目录:

xbstream -x < backup.xbstream -C /root/backup/

将压缩backup发送到别的host并解压缩:

innobackupex --compress --stream=xbstream /root/backup/ | ssh user@otherhost "xbstream -x -C /root/

使用tar的栗子

将意气风发体化的backup存到三个tar归档

innobackupex --stream=tar /root/backup/ > /root/backup/out.tar

将tar归档发送到别的host

innobackupex --stream=tar ./ | ssh user@destination "cat - > /data/backups/backup.tar"

只顾提取percona xtrabackup归档须求内定-i选项

tar -xizf backup.tar.gz

可钦定喜欢的压缩工具:

innobackupex --stream=tar ./ | gzip - > backup.tar.gz
innobackupex --stream=tar ./ | bzip2 - > backup.tar.bz2

亟待注意的是,流式备份供给在还原此前Prepare,流形式无需prepare

复制碰着中备份

从库备份须要钦点以下选项:

  • --slave-info选项
    从库备份,它会打字与印刷binlog的点位,还也是有主库的名字,雷同会将那些音信座位change master语句写入xtrabckup_slave_info文件
    应用境况,可以透过那些备份搭建一个创办那几个主库的从库。
  • --safe-slave-backup
    为保障生机勃勃致性复制状态,这一个选项停止SQL线程而且等到show status中的slave_open_temp_tables为0的时候开始备份,若无打开不常表,bakcup会马上开首,否则SQL线程运转也许关闭知道未有张开的临时表。倘若slave_open_temp_tables在--safe-slave-backup-timeount(暗中认可300秒)秒今后不为0,从库sql线程会在备份达成的时候重启
    刚毅推荐

加速备份进度

  • 通过--parallel拷贝和-compress-threads加快当进行地面备份恐怕经过xbstream选项流式备份时,能够透过--parallel选项多少个文本现身拷贝,那几个选项钦定xtrabackup创建生成的线程数来拷贝数据文件 前提须求展开innodb_file_per_table和分享表空间存在四个ibdata文件中,此天性施行等级为文件等第,在高碎片化的数据文件做并发布公文件转换会追加IO负载,因为十分大的自由读诉求重叠,你能够思索对文件系统调优以获得最大的性质 若是数量存到单一文件中,这几个选项未有其余影响。使用格局: 本地:  innobackupex --parallel=4 /path/to/backup  假设接收xbstream在流式备份中得以因而--compress-threads选项增长速度收缩进程。那么些选项钦定了由并行数据压缩中xtrabackup创设的线程数,暗许值为1 应用那个特点,只需加上该选项 innobackupex --stream=xbstream --compress --compress-threads=4 ./ > backup.xbstream 

在applying log早先,压缩文件需求被解压缩

  • 透过--rsync选项增长速度

为了加紧备份进程,相同的时间减小FLUSH TBALES WITH READ LOCAK阻塞写的光阴,当该选拔指准期,innobackupex使用rsync拷贝全数的非InnoDB文件替换cp。尤其适用于有恢宏的库和表的时候会越来越快。innobackup会调用rsync一次。1、执行flush tables with read lock以前;2、减弱读锁被有着的岁月内。因为第黄金时代调用在刷新读锁以前,所以它独自一齐那么些非事务的数据的扭转

(它不可能和--remote-host、--stream一同行使)

节流(Throttling)备份

就算innobackupex不会阻塞任何数据库的操作,不过备份那个进度会给系统增加Load,借使系统并未有更加的多的IO手艺,那么能够界定innoabckupex读写Innodb数据的功能,通过--throttle选项决定

该接收直接传给xtrabackup二进制造进度序,并仅约束了innodb表的日志和文件操作的

iostat命令能够检查体系上IO操作,

在意innobackup --throttle选项只在备份阶段专业,innobackupex --apply-log and innobackupex --copy-back并不做事

--throttle选项很像mysqlbackup中的--sleep选项

大张旗鼓单表

早于5.6b版本,是不容许在server中间拷贝文件来拷贝表。但是通过Xtrabackup,你能够其它innodb数据库中间导出单个表,并且把它们导入到MySQL5.6中(source不分明是MySQL5.6,可是destination必需是),况且只对独立ibd文件有效

导出表

exporting不是在创制backup的时候,而是在prepare阶段完结,意气风发旦full backup建好,用--export选项prepare它

innobackupex --apply-log --export /path/to/backup

会为各种有独立表空间的Innodb创立一个.exp扩大的文书。

举个栗子:

find /data/backups/mysql/ -name export_test.*
/data/backups/mysql/test/export_test.exp
/data/backups/mysql/test/export_test.ibd
/data/backups/mysql/test/export_test.cfg

有多个公文须求导入线上5.6的表中

MySQL通过dump成大器晚成种特别格式的的Innodb字典的.cfg文件,这种格式不一致于.exp。严峻来讲,.cfg文件没有必要导入mysql5.6表空间。表空间固然从差异的server上也会导入成功。如若有关的.cfg文件在同生龙活虎的目录中,innodb会做schema验证

种种.exp(只怕.cfg)用来导入表

专一:innodb 慢停实例(full purge change buffer merge)通过on --export,不然表空间会不均等而且不会被导入。全数科学普及的个性难点会被思考:丰裕的buffer pool(比如--use-memory,默许100M)和充分的积攒,不然将会费用比较久完毕导出

导入表

将一张表导入到任何服务器上,首先以平等的表结构创立一张新表用于导入:

OTHERSERVER|mysql> CREATE TABLE mytable (...) ENGINE=InnoDB;

然后甩掉表空间:

OTHERSERVER|mysql> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;

然后,拷贝mytable.ibd和mytable.exp(如酚酞入到5.6则是mytable.cfg)到数据库家目录,然后导入表空间:

OTHERSERVER|mysql> ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

意气风发经实践到位。导入表的书库是马上可用的

凭仗时间点过来

因此innobackupex和二进制日志可过来指准时间点的数目

小心二进制日志包罗从过去的三个岁月点起来数据库的转移操作,你必要四个full datadir作为base,然后能够从二进制日志中apply大器晚成层层的操作使数据相称你想要的特别时间点的操作

做生机勃勃份snapshot,大家能够通过innobackupex做豆蔻年华份全备full backup

innobackupex /path/to/backup --no-timestamp

是因为方便使用--no-timestamp选项。之后大家开端prepare,为了未来做好恢复生机的预备

innobackupex --apply-log /path/to/bakcup

这段时间,假若过去了后生可畏段时间,你希望复苏数据库到千古的某部特定点,想下制作快速照相时点的限制

想要搜索二进制日志处境,试行show binary logs和show master status

下一场寻找快速照相生成时候的pos点。找到backup目录下的xtrabackup_binlog_info

cat /path/to/backup/xtrabackup_binlog_info
mysql-bin.000003 57

那会告知备份的时间点的binlog日志及Pos点,那一个pos点在恢复生机备份时候极度管用

innobackupex --copy-back /path/to/backup

下一步正是从二进制日志中用mysqlbinlog从快照的pos点开头提前查询语句一视同仁定向到三个文书中

mysqlbinlog /path/to/datadir/mysql-bin.000003 /path/to/datadir/mysql-bin.000004 --start-position=57 > mybinlog.sql

注意假诺有四个binlog,供给列出具备

全日阅览来调控哪些POS点大概时间点是您要还原的不行点。风流洒脱旦决定好了。管道传向server,如果时间点是11-12-25 01:00:00:

mysqlbinlog /path/to/datadir/mysql-bin.000003 /path/to/datadir/mysql-bin.000004
--start-position=57 --stop-datetime="11-12-25 01:00:00" | mysql -u root -p

提升FLUSH TABLES WITH READ LOCKING handling

备份时,FLUSH TABLES WITH READ LOCK会在备份非innodb文件从前使用来确定保证备份的大器晚成致性,FLUSH TABLES WITH READ LOCK甚至有询问实行了多少个钟头的时候仍可以够运转,这种景况下,任何都会锁在Waiting for table flush or Waiting for master to send event那三种处境下,kill掉也不解决其余难点。唯有kill掉导致FLUSH锁住的慢查才干让server上除朝常运作。那就意味着借使长日子运作的查询时,FLUSH TABLES WITH READ LOCK会被打断,

留意,上述情状在backup locks是不行的,Percona Server 5.6 中特性,XtraBackup会自动拷贝非Innodb数据制止阻塞校订InnoDB表的DML查询

两件事足以避免上述难点:

innobackupex 等待好时机

innobackupex 能够Kill阻止得到全局锁的询问(全部或许仅select查询)

等候查询完毕

获得全局锁的好机缘是指具有长查询都推行完成,为防御innobackupex等待执行LUSH太短期。新选项被引进:

innobakcupex --ftwrl-wait-timeout选项,节制等待时间,风姿浪漫旦过期就能够报错退出。私下认可值为0,关闭此性情

另二个是安装等待查询的种类,innobackupex --ftwrl-sait-query-type,可选址为all和update,设置为all时,innobackupex会在实施FLUSH TABLES WITH READ LOCK以前等待全院长时查询达成(实行时间超越innobackupex--ftwrl-wait-threshold),当班值日为update时会等待UPDATE/ALTEOdyssey/REPLACE/INSERT查询完毕

--lock-wait-threshold用来定义 --locl-wait-query-type中的长运转查询,倘诺凌驾--lock-wait-threshold都算长运行查询。

kill掉阻塞查询

能够透过点名--kill-long-queries-timeout用来钦点施行FLUSH TABLES WITH READ LOCK后还足以推行的时光,0为不kill,--kill-long-query-type用来钦命超时今后,kill的询问类型,可以是all或然select

innobackupex --lock-wait-threshold=40 --lock-wait-query-type=all --lock-wait-timeout=180 --kill-long-queries-timeout=20 --kill-long-query-type=all /data/backups/

innobacupex费用不超过3分钟时间等待当先40秒的询问实现,FLUSH之后,innobackupex会等待20秒时间获得全局锁,借使胜过20秒照旧未有到手,会kill全数的运转时刻超越FLUSH命令的查询

innobackupex是怎样行事的

2.3起innobackupex用c重写而且作为xtrabackup的暗记连接,innobackupex扶持2.2版本的装有个性和语法。可是现在已降级而且在下叁个major版本少校被移除,。新的特征语法将加在xtrabakup中,实际不是innobackupex中

making a backup

设若未有方式钦定,innobakucpex默以为backup情势

暗中同意的,innobackupex通过--suspend-at-end选项运维xtrabackup,况且让它拷贝innodb数据文件,当xtrabackup完结,innobackupex望着它创设xtarbackup_suspended_2文件同期奉行FLUSH TABLES WITH READ LOCK.

innoabackupex之后检查MySQL变量来决定server援助什么特色,非常是backup locks,change page bitmaps,GTID mode等等,若是一切顺遂,二进制作为贰个子经过运维

innobackupex在装置--safe-slave-backup选项后翘首以待同步,何况flush全数表with read lock.阻止全数myisam表写入(除非钦定--mo-lock)。

假若形成,起先备份全体非Innodb文件,.frm,.MLANDG,.MYD,.MYI,.CSV,.OPT文件等等

当有着文件备份后,实践ibbackup何况等待直到落成备份落成拷贝事务达成。之后,表被解锁,开启联合(借使钦命--safe-slave-backup)何况与server的接连关闭。之后,移除xtrabackup_suspended_2文本并允许xtrabackup退出

再便是在备份的目录创造如下文件:

xtrabackcup_checkpoints 包涵备份类型和LSN

xtrabackcup_binlog_info 包含开启备份时刻的binlog和POS

xtrabackcup_binlog_pos_innodb 包罗初叶备份InnoDB事务相关的binlog的POS

xtrabackup_slave_info 包含master的binlog和POS(需指定--slave-info)

backup-my.cnf 备份须要的my.cnf中的选项

xtrabackup_binary 包罗backup须求的二进制文件

mysql-stderr

mysql-stdout

末尾binlog pos打字与印刷在正式错误输出並且Innobackupex退出码为0退出

因而备份还原

还原备份通过--copy-back选项

innobackupex读取my.cnf中的变量并检讨有关目录是或不是存在

日后拷贝MyISAM表,索引等等。首先是innodb表其次索引,最终是log文件。拷贝时保留文件属性。

用作替换,--move-back选项用来还原备份。那么些选项与--copy-back相符。唯生龙活虎的区分是它不拷贝文件,而是移动文件到指标地。那一个选项移除backup文件,用时候必得小心。使用境况:没有充分的磁盘空间同事保留数据文件和Backup副本

innobackupex 命令行选项

选项

  • --apply-log 
    通过apply名叫xtrabackup_logfile的事务日志来在BACKUP-DIOdyssey目录中Prepare贰个backup,相仿,创建新的事情日志。innodb配置从生成bakcup进度中innobakcupex创设的backup-my.cnf文件读取,innobackupex --apply-log 私下认可使用bakcup-my.cnf中的innodb配置.这里说的Innodb配置指的是震慑多少格式的种类变量,譬喻:innodb_page_size,innodb_log_block_size等等,本地相关变量举个例子innodb_log_group_home_dir或者innodb_data_file_path.
    貌似景观下,在备份完毕后,数据尚且不能够用于复苏操作,因为备份的多寡中或然会含有尚未提交的政工或早已交给但绝非同步至数据文件中的事务。因而,那时候数据文件仍管理不平等状态。“计划”的显要功用就是经过回滚未提交的业务及协同已经提交的事务至数据文件也使得数据文件处于风姿洒脱致性状态。
    对xtrabackup的--prepare参数的卷入
  • --backup-locks
    仅扶持percona server5.6,若是server不援救,开启不读私人和发生影响
  • --close-files
    2.2.5引进的新特征
    闭馆不再访谈的文书句柄,这几个选项直接传送给xtrabackup,当xtrabackup展开表空间常常并不关门文件句柄指标是不错的管理DDL操作。假若表空间数据宏大,那是风流洒脱种能够关闭不再访谈的文书句柄的办法。使用该选项有风险,会有发生不均等备份的或是
  • --compact
    成立黄金时代份未有助于索引的黄金年代体的备份,该选用直接传送给xtrabackup
  • --compress
    该选拔教导xtrabackup压缩innodb数据文件的backup的正片,直接传送给xtrabackup的子进度
  • --compress-threads = #
    该接纳钦命并行压缩的worker线程的多寡,间接传送给xtrabackup的子进度
  • --compress-chunk-size = #
    这一个选项钦赐每一种压缩线程的里边worker buffer的深浅。单位是字节,默许是64K。直接传送给xtrabackup子进程
  • --copy-back
    实施还原操作,从备份目录中方今的后生可畏份备份中拷贝全体文件到datadir,innobackupex --copy-back选项除非钦命innobackupex --force-non-empty-directories选项,不然不会拷贝覆盖全数的文本
  • --databases=LIST
    点名innoabckupex备份的DB列表,该选择选取叁个三个字符串参数可能隐含DB列表的文本的全路线。若无一点名该选择,全体包蕴innodb和myam表的DB会被备份,请确认--databases包含全体的innodb数据库和表,,以便全数的innodb.frm文件也雷同备份,假如列表相当短的话。能够以文件替代
  • --decompress
    解压全体值钱通过--compress选项压缩成的.qp文件。innodbakcupex --parallel选项允许多少个文本同一时候解压。为领会压,qpress工具必须有安装还要访问这一个文件的权杖。那几个进程将在同贰个职位移除原本的削减/加密文件
  • --decrypt=ENCRYPTION-ALGORITHM
    解密全体以前经过--encrypt选项加密的.xbcrypt文件。--innobackup --parallel选项允许同一时间四个文件解密
  • --defaults-file=[MY.CNF]
    该接纳钦命了从哪个文件读取MySQL配置,必须放在命令行第一个选项的职分
  • --defaults-extra-file=[MY.CNF]
    点名了在行业内部defaults-file在此之前从哪些额外的公文读取MySQL配置,必需在命令行的率先个选用的位置
  • --default-group=GROUP-NAME
    这些选项选择了贰个字符串参数钦命读取配置文件的group,在一机多实例的时候供给钦点
  • --encrypt=ENCRYPTION_ALGORITHM
    该选拔钦命了xtrabackup通过ENCHighlanderYPTION_ALGOEnclaveITHM的算法加密innodb数据文件的备份拷贝,该选择直接传送给xtrabackup子进度
  • --encrypt-key=ENCRYPTION_KEY
    指点xtrabackup使用了--encrypt选项时候使用ENCEscortYPTION_KEY这么些KEY,直接传送给xtrabackup子进程
  • --encrypt-key-file=ENCRYPTION_KEY_FILE
    其意气风发选项告诉xtrabackup使用--encrypt的时候。Key存在了ENCKugaYPTION_KEY_FILE这一个文件中
  • --encrypt-chunk-size=#
    本条选项钦点了各类加密线程内部worker buffer的高低,单位字节,间接传送给xtrabackup子进程
  • --export=DIRECTORY
    这些选项间接传送给 xtrabackup --export选项。开启可导出单身的表之后再导入其他Mysql中
  • --extra-lsndir=DIRECTORY
    那些选项接收三个字符串参数钦定保存额外风流倜傥份xtrabackup_checkpoints文件的目录,直接传送给xtrabackup --extra-lsndir选项
  • --force-non-empty-directories
    点名该参数时候,使得innobackupex --copy-back或innobackupex --move-back选项转移文件到非空目录,已存在的文书不会被掩瞒,假诺--copy-back和--move-back文件要求从备份目录拷贝几个在datadir已经存在的公文,会报错战败
  • --galera-info
    该选项生成了含有创造备份时候本地节点状态的文件xtrabackup_galera_info文件,该接收只适用于备份PXC。
  • --history=NAME
    percona server5.6的备份历史记录在percona_schema.xtrabackup_history表
  • --host=HOST
    分选内定了TCP/IP连接的数据库实例IP
  • --ibbackup=IBBACKUP-BINARY
    本条选项钦命了使用哪个xtrabackup二进制造进度序。IBBACKUP-BINA福睿斯Y是运营percona xtrabackup的命令,。这几个选项适用于xtrbackup二进制不在你是寻找和办事目录,假如钦点了该选项,innoabackupex自动决定用的二进制造进度序
  • --include=REGEXP
    正则表明式相配表的名字[db.tb],直接传送给xtrabackup --tables选项。
  • --incremental
    本条选项告诉xtrabackup成立三个增量备份,直接传送给xtrabakcup子进程,当以此选项内定,必要同期钦命--incremental-lisn只怕--incremental-basedir。若无一点点名,私下认可传给xtrabackup --incremental-basedir,值为Backup BASE目录中的第多个小时戳目录
  • --incremental-basedir=DIRECTORY
    本条选项选取了三个字符串参数钦命含有full backup的目录为增量备份的base目录,与--incremental同期利用
  • --incremental-dir=DIRECTORY
    钦赐了增量备份的目录,结合full backup生成生成后生可畏份新的full bakcup
  • --incremettal-history-name=NAME
    本条选项钦命了蕴藏在PERCONA_SCHEMA.xtrabackup_history基于增量备份的历史记录的名字。Percona Xtrabackup搜索历史表查找近期(innodb_to_lsn)成功备份况兼将to_lsn值作为增量备份运维出事lsn.与innobackupex--incremental-history-uuid互斥。若无检测到有效的lsn,xtrabackup会重回error
  • --incremetal-history-uuid=UUID
    那个选项钦赐了仓库储存在percona_schema.xtrabackup_history基于增量备份的特定历史记录的UUID
  • --incremental-lsn=LSN
    以此选项钦赐增量备份的LSN,与--incremental选项联合利用
  • --kill-long-queries-timeout=SECONDS
    以此选项钦命innobackupex从上马推行FLUSH TABLES WITH READ LOCK到kill掉阻塞它的那一个查询之间等待的秒数,暗中同意值为0.认为着Innobakcupex不会kill任何查询,使用这一个选项xtrabackup要求有Process和super权限。
  • --kill-long-query-type=all|select
    指定kill的类型,默认是all
  • --ftwrl-wait-timeout=SECONDS
    实行FLUSH TABLES WITH READ LOCK在此以前,innobackupex等待绿灯查询实行到位等待秒数,超时的时候借使查询还是未有试行完,innobackupex会终止并报错,默认为0,innobakcupex不等待查询实现马上FLUSH
  • --ftwrl-wait-threshold=SECONDS
    点名innoabckupex检查评定到长查询和 innobackupex --ftwrl-wait-timeount不为0,这些长查询能够运作的阈值,
  • --ftwrl-wait-query-type=all|update
    点名innobakcupex得到全局锁在此之前允许这种查询完毕,暗中认可是ALL
  • --log-copy-interval=#
    其生机勃勃选项钦命了每一次拷贝log线程实现检查时期的间隔(阿秒)
  • --move-back
    从备份目录少校近日后生可畏份备份中的全数文件移动到datadir目录中
  • --no-lock
    闭馆FTW福特ExplorerL的表锁,只有在您富有表都以Innodb表並且你不关切backup的binlog pos点
    假使有其余DDL语句正在实施或然非InnoDB正在更新时(包蕴mysql库下的表),都不应当使用那几个选项,后果是引致备份数据不相像
    举个例子虚构备份因为拿到锁退步,,能够考虑--safe-slave-backup马上停下复制线程
  • --no-timestamp
    那么些选项阻止在BACKUP-ROOT-DI卡宴里创建多少个年华戳子目录,钦赐了该选取的话,备份在BACKUP-ROOT-DI讴歌RDX完结
  • --no-version-check
    其黄金年代选项禁止使用由--version-check打开的version check
  • --parallel=NUMBER-OF-THREADS
    内定xtrabackup并行复制的子进程数。注意是文件等级并行,假如有八个ibd文件,他们会互相拷贝,假设具备的表存在三个表空间文件中,未有此外效果。。直接传送给xtrabakcup --parallel选项
  • --password=PASSWORD
  • --port=PORT
  • --rebuild-indexes
    与--apply-log一齐用时候才使得。而且直接传送给xtrabackup,在apply log之后重新建立全体助于索引,该采用用于Prepare紧密备份。
  • --rebuild-threads=NUMBER-OF-THREADS
    与--apply-log和--rebuild-index选项一同用时候才生效,重新建设构造索引的时候,xtrabacup以内定的线程数并行的管理表空间文件
  • --redo-only
    其生龙活虎选项在prepare base full backup,往里面merge增量备份(但不包罗最终贰个)时候使用。传递给xtrabackup --apply-log-only的选项。这一个强制xtrabackup跳过rollback况兼只重做redo
  • --rsync 
    由此rsync工具优化地面传输,当内定这一个选项,innobackupex使用rsync拷贝非Innodb文件而替换cp,当有繁多DB和表的时候会快比比较多。无法--stream一齐利用
  • --safe-slave-backup
    钦点的时候innobackupex会在施行FLUSH TABLES WITH READ LOCK结束sql线程,並且直到show status里slave_open_temp_tables的值为0的时候start backup,。若无张开的一时表,就起来备份,否则sql线程start大概stop直到未有展开的一时表,假如在innobackupex --safe-slave-backup-timeout之后slave_open_temp_tables的值仍还未有变成0备份就能够退步。SQL线程会在backup实现之后重启。
  • --safe-slave-backup-timeout=SECONDS
    innobackupex --safe-slave-backup应该等多少秒等slave_open_temp_tables变成0,默认是300秒
  • --scpopt=SCP-OPTIONS
    当--remost-host钦赐的时候,钦点传给scp的命令行选项。若无一些名,默以为-Cp -c arcfour
  • --slave-info
    对slave进行备份的时候使用,打印出master的名字和binlog pos,同样将那么些新闻以change master的下令写入xtrabackup_slave_info文件。能够由此依照那份备份运行三个从库况且保留在xtrabackup_slave_info文件中的binlog pos点创造五个新的从库
  • --socket
    三翻五次本地实例的时候利用
  • --sshopt=SSH-OPTIONS
    在钦点了--remost-host的时候,钦命传给ssh的吩咐行选项
  • --stream=STREAMNAME
    流式备份的格式,backup完毕以后以内定格式到STDOUT,近些日子只扶助tar和xbstream。使用xbstream为percona xtrabakcup发型版本,倘若在此个选项之后钦定了路子。会精通值为tmpdir
  • --tables-file=FILE
    点名含有表列表的文本,格式为database.table,该接收间接传给xtrabackup's --tables-file
  • --throttle=IOS
    点名每秒IO操作的次数,直接传送给xtrabackup --throttle选项。只遵从于bakcup阶段有效。apply-log和--copy-back不见到成效不要一同用
  • --tmpdir=DIRECTORY
    点名--stream的时候,钦命一时文件存在哪个地方,在streaming和拷贝到远程server在此之前,事务日志首先存在有的时候文件里。
  • --use-memory=#
    唯其如此和--apply-log选项一齐利用,prepare a backup的时候,xtrabackup做crash recovery分配的内部存款和储蓄器大小,单位字节。也可(1MB,1M,1G,1GB),间接传给xtrabackup --use-memory选项
  • --version
    突显Innobackupex版本和版权音讯后退出
  • --version-check
    innobackupex在与server创设连接之后的备份阶段进行版本检查

Xtrabackup 二进制

get started

配置xtrabackup

xtrabackup读取配置文件中的[mysqld]和[xtrabackup]有些,读取datardir和innodb选项,能够因此将这么些都钦命在[xtrabackup]部分。越靠后,越优先。

最简便易行的在[xtrabackup]一些只内定target_dir,该接受钦定backup默许放的目录,举例:

[xtrabackup]

target_dir = /data/backups/mysql

Xtrabackup-FULL BAKCUPS

创立二个备份

运行xtrabackup指定--backup ,--target_dir,--datadir.

假设不钦赐target_dir,xtrabacup会创设它。若是目录存在且为空,xtrabackup会成功,xtrabackup不会覆盖原来就有文件。会报“file exist”的荒诞

本条工具将职业目录转形成datadir,而且实践两项首要的天职:(后台运转的log扫描线程扫描redo log,ibdata1上的文本拷贝线程)

  • 后台运维四个拷贝日志的线程,那些线程关怀innodb日志文件,当redo log有调换,这么些线程拷贝变化的块到到target目录成为xtrabackup_logfile的文件
  • 拷贝Innodb数据文件到目的目录,那不是个轻松拷贝文件那么粗略,它像Innodb那样张开读取文件,读取数据字典而且逐意气风发的正片几个page
    当拷贝完数据文件,xtrabackup结束log-copying线程,并在target目录生成富含了备份类型和始发和尾声的lsn号的xtarabckup_checkpoints文件,
    举个例子命令如下:

    xtrabakcup --backup --datadir=/data1/mysql/ --target-dir=/data/backups/

    备份/data1/mysql目录并蕴藏在/data/backups/mysql/。假诺你钦命一个相对路线,target目录会涉嫌到当下路径
    在备份过程里面,你能收看第一次全国代表大会堆输出展示了文本正在拷贝中,同时log file线程重复的扫描redo log,而且文件拷贝线程将innodb数据文件拷贝到target目录
    最后风姿罗曼蒂克件要求关心的事情是,LSN的值在哪儿成为二个数字垄断(monopoly)于您的类别
    小心:log拷贝线程每秒检查作业日志去看是否写入了新的日志记录要求拷贝,也可以有神蹟的log拷贝线程赶不上大量的写入事务日志的快慢,redo log在被读取早先就被遮住了,就能够报错!!!!
    当Backup甘休,target目录富含了:

    /data/backups/ibdata11
    /data/backups/test
    /data/backups/test/tbl1.ibd
    /data/backups/xtrabackup_checkpoints
    /data/backups/xtrabackup_logfile

    备份会持续时间决计于数据库大小,在其它时间cancel都以平安的,因为它并不纠正数据库
    下一步要让backup变为可复原:prepare backup

preparing the backup

用--backup生成备份后,下一步正是prepare。数据文件不是时刻点相近的停止他们被prepare,因为他俩在程序运维时不一样一时间间拷贝,何况爆发时后生可畏度改成了,假如你尝试用这个数据文件运行innodb,之后会检查实验到崩溃,并且crash本人来防御在破坏的数码上持续运维。--prepare阶段让这个文件在随机时间都大器晚成致性,所以你能够在上边运维Innodb

瞩目:innobakcupex --apply-log 自动从bakcup-my.cnf读取innodb配置,恐怕--defaults-file=bakcup-my.cnf选项传递给xtrabackup。不然会产生不得法还原因为xtrabackup已经用了错误的布署选项

你能够在别的机器上运转Prepare操作,没有必要在备份机上或然复苏机上操作,你能够拷贝backup到风华正茂台特意的中央调节机并且在prepare

留心:你能够用新本子prepare多个较老版本创设的backup,但反过来不行。在生机勃勃台不辅助的Mysql server版本上prepare一个bakcup应该用援救该server的最新版本,举个例子,假若通过1.6版本xtrabackup备份mysql5.0,用2.2prepare是不补助的。因为2.1版本中移除了5.0

在prepare阶段,xtarbackup嵌入了更正过的innodb,禁绝了innodb的自己研商,比方日志文件大小是不是准确。

prepare阶段正是运用那一个放手的innodb来做通过日记文件对数据文件进行crash苏醒

xtrabackup --prepare --target-dir=/data/backups/mysql

完了之后,能够看见innodb shutdown的音信和lsn

您的备份未来是干净何况肖似的了,并且希图好还原,然则,你大概希望额外的步骤让还原越来越快。那亟需第三遍Prepare。第一遍prepare让数据文件完美的风度翩翩致性。可是不多如牛毛新鲜的Innodb日志文件,假若当时还原备份况兼运转Mysql,它供给创制新的日志文件,那必要一段时间。你可能不想等待。若是您第三遍运营--prepare,xtrabackup会创设日志文件,redo log的朗朗上口决意于mysql的布局文件

xtrabackup --prepare --target-dir=/data/backups/mysql/

xtrabackup: This target seems to be already prepared.

xtrabackup: notice: xtrabackup_logfile was already used to ’--prepare’.

101107 16:54:10 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to <SIZE> MB

InnoDB: Database physically writes the file full: wait...

101107 16:54:10 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to <SIZE> MB

InnoDB: Database physically writes the file full: wait...

101107 16:54:15 InnoDB: Shutdown completed; log sequence number 1284108

其次次依旧第叁回prepare不会更改风流倜傥度Prepare的数据文件,你可以看输出:

xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to ’--prepare’.

不推荐prepare的时候抛锚xtrabackup进程,会招致数据文件损坏何况backup不可用,中断prepare进程不保障backup可用性

设若之后会拿这份那份backup作为基础而开展增量备份,prepare的时候要接纳--apply-log-only选项,不然你无法apply增量备份到那份basic全备。

复原全备

xtrabackup未有过来备份的意义。由客户来做,你能够透过rsync或许cp来还原,你应当检查还原著件有准确的属主和权杖

当心:datadir在恢复生机备份此前必得为空,相近至关心注重要的是Mysql server须要还原此前shutdown,你不能够在Mysql运营时还原到datadir目录(除非是导入部分备份)

rsync命令还原:

rsync -avrP /data1/backup/ /data1/mysql

文件属性保留,大许多景象下须求在运营实例此前改变文件的属主

chown -R mysql5711:mysql /data1/mysql5711

注意,xtrabackup只备份Innodb数据,你必得独立还原mysql系统库,myisam数据,表定义文件。也许innobakcupex

其余品种备份

增量备份

xtrabackup和Innobacupex都接济增量备份,意味着能够只拷贝自从上次全备之后变化的数据,你可以在各类full backup之间做过多份增量备份,那样您能够做这么贰个备份程序七日做一回full backup每一天一回增量,也许一天做一回full backup每小时做三次增量

增量备份原理,每种Innodb页皆有贰个LSN号,生龙活虎份增量备份拷贝每一个比上次增量备份或然全备LSN更新的page。

有七个算法能够找到这么的急需拷贝的Page的聚众

  • 对于有所server和版本可用,通过读取全体的数额页检查page的LSN
  • 仅对Percona Server适用,忽略

增量备份不会不会实际的和上次的备份相比数据文件。你即使知道LSN的话以致在一向不从前备份的气象下利用通过--incremental-lsn实行一回增量备份。增量备份轻松的读取page何况比较他们的LSN与上次备份的LSN。但是你依旧要求三个full bakcup来苏醒增量的生成。若无多少个full bakcup作为贰个base,增量备份是不曾用的

始建豆蔻梢头份增量备份

成立黄金时代份增量备份,须要从二个full backup初阶,xtrabakcup写三个叫xtrabackup_checkporints的文书到备份指标目录,那个文件包蕴黄金时代行显示to_lsn(backup截止时候的lsn)

xtrabackup --backup --target-dir=/data/backups/base --datadir=/data1/mysql5711

xtrabakcup_checkpoints文件包涵

backup_type = full-bakcuped
from_lsn =0
to_lsn =1291135

依照这些full backup创立大器晚成份增量:

xtrabackup --backup --target-dir=/data/backups/inc1 --incremental-basedir=/data/backups/base --datadir=/data1/mysql5711

/data/backup/inc1/目录包罗了增量的文件,举个例子ibdata1.delta和test/table1.idb.delta ,代表了从LSN1291135的话的转移,若是检查那个目录的xtrabacup_checkpoints文件,会映珍惜帘如下:

bacup_type = incremental
from_lsn = 1291135
to_lsn = 1291340

于今能够拿inc1当做接下去增量备份的base目录:

xtrabackup --backup --target-dir=/data/backups/inc2 --incremental-basedir=/data/backups/inc1 --datadir=/data1/mysql5711/

prepare增量备份

增量备份的--prepare阶段与平常备份分裂,在常规备份中,实践二种档期的顺序操作来确认保证数据库意气风发致性,已交给的事务会在数据文件中重播,未提交的作业回滚,prepare备份的时候你必要跳过未提交业务的回滚,因为在备份的十三分时刻未提交的事情正在张开中,并且显著的它们会在下一次增量备份的交由。你需求动用--apply-log-only选项来阻止回滚阶段

要是您不用--apply-log-only选项阻止回滚,那么以往的增量备份就不算了。事务假诺被回滚,那之后的增量备份就不可能被回放

从您从头成立的full backup,你能够Prepare它,之后重播增量的界别,回想您早就有如下备份base/,inc1/,inc2/

prepare base备份,然后阻止回滚:

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base

输出显示lsn为1291135.

备份假使现在还原是特别安全的,以至回滚的步调被跳过了,要是您今后苏醒并运行MySQL,InnoDB会检查实验到回滚未有实践,之后再在后台举行开始crash恢复生机,通告你多少未有被不荒谬关闭

回看第一个增量备份给full backup:

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base --incremental-dir=/data/backups/inc1

如此那般给/data/backups/base目录重播delta文件。将备份的流年发展到增量备份的时间点,之后照常重放事务日志,最后数额是在/data/backups/base并不在增量目录里.

展现如下:

incremental backup from 1291135 is enabled.
xtrabackup: cd to /data/backups/base/
xtrabackup: This target seems to be already prepared.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1291340)
Applying /data/backups/inc1/ibdata1.delta ...
Applying /data/backups/inc1/test/table1.ibd.delta ...
.... snip
101107 20:56:30 InnoDB: Shutdown completed; log sequence number 1291340

借使由此/data/backups/base目录还原,你可知数据库的情景是首先次备份时的场所

prepare第一回增量备份以同等的步骤,apply增量到上步的base,将数据的岁月点同步到第二遍增量备份的时辰点

xtrabackup --prepare --target-dir=/data/backups/base --incremental-dir=/data/backups/inc2

--apply-log-only用在merging全数增量除最终三个。最终一步仍然是能够用,备份最终也是间接的只是server不会推行回滚步骤

有个别备份

必要敞开innodb_file_per_table,有二种艺术援助部分备份

留意:假诺别的匹配的依旧列入的表在备份时期删除,xtrabackup会失利

  • 行使--tables选项相称databasename.tablename  xtrabackup --backup --datadir=/data1/mysql5711 --target-dir=/data/backups/ --tables="^test[.].*"  备份整个test库
  • 使用--tables-file选项
  • 使用 --databases 和 --databases-file选项 #### prepare 部分备份 会用--prepare选项进行部分备份的时候,你会看出相关表海市蜃楼的报告急察方,原因是那些表存在于InnoDB的数目字典中不过相应的.ibd文件空中楼阁,他们未尝被拷贝到备份目录中去,那个表将会从数额字典中移除,不过当你苏醒备份况兼运维InnoDB的时候,他们讲不会设有并在日记文件中报错

裁减备份

不含有扶植索引页,占用更加少磁盘空间,劣势是prepare的小运更加长因为急需重新创立扶助索引

专一:压缩备份不扶助系统表空间,所以应该张开innodb-file-per-table选项

成立压缩备份

通过--compact选项

xtrabackup --bakcup --compact --target-dir=/data/backups

反省target-dir目录下的xtrabackup-checkpoints文件。如下:

backup_type = full-backuped
from_lsn = 0
to_lsn = 2888984349
last_lsn = 2888984349
compact = 1

要是不应用--compact的话--value的值为0,这些办法能够高速检验备份是不是包含补助索引页

prepare 压缩备份

因而--rebuild-indexes和--apply-logs一齐行使

xtrabackup --prepare --rebuild-indexes /data/backups

经过--rebuild-threads选项四线程重新建立索引

xtrabackup --prepare --rebuild-indexes --rebuild-threads=16 /data/backups/

平复压缩备份

未曾现有工具,能够因而rsync也许cp达成,须求检查还原来的文章件是不是有正确权限

高等天性:

throttling备份

xtrabackup不会阻塞数据库读写,backup扩充系统压力,能够透过--throttle选项,这些选项限定IO操作的数量为每秒每MB

在--backup形式,那么些选项限定了xtrabackup每秒推行的对(read和write)。假若您创设多少个增量备份。然后限定每秒读IO的数量

默许,no throttling,xtrabackup最快的读写数据,假设您IO节制过于严谨,备份会超级慢况且追不上innodb写作业日志的快慢,备份将生生世世无法到位

编纂备份脚本

suspending after copying

在备份格局中,xtarbackup在后台拷贝日志文件,前端线程拷贝数据文件,,之后截止日志线程并成功。若是您利用--suspend-at-end选项并非结束日志线程并造成。xtrabacup会继续拷贝日志文件,并在target目录生成一个xtrabackup_suspended文件,之后要以此文件存在,xtrabackup会继续观察事务日志并把他们拷贝到target目录中xtrabackup_logfile文件。当这几个文件移除的时候,xtrabackup会结束拷贝事务日志并退出

该功效和睦备份Innodb数据和任何动作时那些有用,最显著的是拷贝表定义文件(.frm)以便备份能被还原,你能够往台运维xtrabakcup,等待xtrabackup_suspended文件被创设,然后拷贝全数你供给达成那个备份的其余公文。。这么些正是innobakcupex工具做的做事

生成元数据

备份富含其余你需求还原备份时候需求的新闻是个好主意,xtarbackup能打印my.cnf文件中必要还原的数码和日志文件的剧情,假诺扩充--print-param选项。会打字与印刷如下内容:

```

This MySQL options file was generated by XtraBackup.

[mysqld]

datadir = /data/mysql/

innodb_data_home_dir = /data/innodb/

innodb_data_file_path = ibdata1:10M:autoextend

innodb_log_group_home_dir = /data/innodb-logs/

```

能够重定向输出到backup的target目录

协商source目录

陈设文件大概别的因素会促成xtrabackup从分歧的职问责份数据。为了防范那几个,你能够用--print-param来搜索从哪个地方copy数据。你能够用输出来保障xtrabackup和您的脚本管理相仿的数据集

log streaming

可以透过--log-stream 让xtrabackup将redo log文件streaming实际不是拷贝,那样会自动增加--suspend-at-end选项。你的本子能够推行

stream远程备份并由此管道将log文件传给ssh而且经过rsync或许xbstream等工具将数据拷贝到远程服务器上

浅析表总括消息

xtrabackup以只读情势深入分析InnoDB数据文件并提供给总括。能够因而--stats选项。能够同--ables选项一同使用约束检查的的文件。还应该有--use-memory选项。

您能够在大器晚成台运营实例的机器上施行解析,在深入深入分析期间数据变动会有出错的可能率。恐怕能够剖判备份的正片。假使急需利用总结的特征,你必要三个干净的正片包罗准确的Logfile大小,,所以您要求在一遍备份上实行--prepare三回

使用binlog

xtrabakcup提取了innodb事务日志关于对应已交给业务的binlog pos,那个能够打字与印刷出backup对应的binlog pos,你可以因此搭建一些列从库大概进行基于时间点的回涨

找到binlog pos

大器晚成旦backup prepare之后你能够找到binlog pos,通过运维xtrabakcup --prepare大概innobackupex --apply-log都能够产生。借使bakcup是来自展开binlog的实例。xtrabackup会在target目录创建三个xtrabakcup_binglog_info的公文。那几个文件包罗了与prepare对应的binlog名字和pos点位

输出的音信在xtrabakcup_binlog_pos_info文件中找到,独有采纳innodb引擎才会标准

其余斯特林发动机,举个例子myisam,你应有使用xtrabackup_binlog_info文件得到地方

依据时间点恢复生机

从黄金年代份xtrabackup的备份里基于时间点的大张旗鼓,你供给prepare并且复苏备份,之后重放xtrabackup_binlog_info中记录的点起来的binlog

搭建七个新的从库

亟需先prepare,然后在新从库的data目录中恢复生机,之后选取change master to命令,使用xtrabackup_binlog_info文件的binlog和pos运营复制

光复单独表

在5.6本子从前,即便张开innodb_file_per_table选项,还是不容许通过在实例之间通过拷贝文件来拷贝表。可是,通过Xtrabackup你能够从其余InnoDB数据库中程导弹出单表,并且导入到5.6中去(source不必是5.6只是destination必得是!!!!!)只在独立.ibd文件生效,若无独立ibd文件是不可见导出单表的!!!!!

导出单表

以此表必需在开垦innodb_file_per_table格局下创办。所以在在--bakcup创设风度翩翩份备份之后,.ibd文件应该已经存在于target目录中

当您prepare的时候,须求额外加--export命令:

xtrabacup --prepare --export --target-dir=/data/backups/mysql5711

现行反革命你能够在target目录找到.exp文件

$ find /data/backups/mysql5711/ -name export_test.*
/data/backups/mysql5711/test/export_test.exp
/data/backups/mysql5711/test/export_test.ibd
/data/backups/mysql711/test/export_test.cfg

那多个公文是您将表导入5.6的兼具文件

表明:mysql使用cfg文件,那些文件包涵了Innodb字典dump。这种格式不一样于xtrabDB的.exp文件。严峻来说,.cfg文件在5.6以至后来是不在须要的。假诺存在cfg文件,那么Innodb会通过cfg文件做schema验证

导入表

在5.6,以平等表结构创建一张表,然后实行以下步骤:

  • 执行alter table test.export_test discard tablespace;必要展开innodb_file_per_table
  • 拷贝以前导出到文件目标服务器的数码目录test/子目录
  • 执行alter table test.export_test import tablespace; 那张表未来早已导入,你能够经过select命令查看导入数据 ### LRU dump备份 这些特点收缩了通过在实例重启之后从ib_lru_dump文件还原buffer pool状态的预热时间。xtrabakcup自动发掘ib_lru_dump何况自动备份 假设my.cnf中拉开buffer还原挑选,buffer pool会在从备份还原之后自动预热。只在Percona server中有其生机勃勃选项。mysql未有

实施

xtrabakcup的限制

有以下供给注意:

* 如果xtrabakcup_logfile当先4G,三12个人系统上的xtrabakcup在--prepare阶段会失利

* xtrabackup在首先次--prepare的不会变动新的redo log文件,必得--prepare三遍,在第贰次的时候才会变卦

* 不扶持my.cnf里有--set-variable这种格式设置

推行细节

文本权限

xtrabacup以读写情势展开源数据文件,但不退换那一个文件,意味着你不得不以有写这一个文件权限的客户来运维xtrabackup。以读写方式张开文件的由来是xtrabakcup使用内置的Innodb库来张开读写文件,而且Innodb以读写方式展开因为健康假如是内需写这几个文件了

调整os buffers

因为xtrabackup读取文件系统的豁达数据,大概它选择posix_fadvise()指引操作系统不要尝试缓存从磁盘读取的block。未有那么些提醒。如果xtrabackup十分的快再一次索要这个块,操作系统更乐于缓存那一个块。缓存如此大的文本会给操作系统的虚构内部存款和储蓄器增加压力并到底其余进程,比方数据库swap out。xtrabakcup工具通过source和destination如下提醒来防止这种情形发生:

posix_fadvise(file,0,0,POSIX_FADV_DONTNEED)

除此以外,xtrabackup让操作系统在源文件上来实践更挑衅性的read-ahead优化

posix_fadvise(file, 0, 0, POSIX_FADV_SEQUENTIAL)

拷贝数据文件

当向target目录拷贝数据文件的时候,xtrabakcup三次读写1MB数据。那是不足配置的。当拷贝事务日志,xtrabackup一遍读写512字节。着平等不能够安顿。是顺应Innodb的(percona server的消除办法是有至极的参数innodb_log_block_size)

读取文件之后,xtrabackup二遍1MBbuffer遍历page。并经过innodb buf_page_is corrupted()函数检查各种Page的corruption。假诺page损坏,便会重读並且各类page重试11次,叁回写buffer会跳过这些检查

手册

xtrabackup选项

选项

  • --apply-log-only 
    prepare备份的时候只实行redo阶段,对增量备份相当的重大
  • --backup
    创制备份并且放入--target-dir目录中
  • --close-files
    不保险文件展开状态,xtrabackup张开表空间的时候平时不会停业文件句柄目标是为了正确管理DDL操作。然则,假如表空间数据拾贰分了不起而且不切合任何约束,黄金时代旦文件不在被访谈的时候那么些选项可以关闭文件句柄.张开这几个选项会发出不生龙活虎致的备份。本身评估危机。。
  • --compact
    创办风度翩翩份没有利于索引的后生可畏体备份
  • --compress
    减去全体出口数据,满含专门的学问日志文件和元数据文件,通过点名的压缩算法,目前唯风流浪漫帮忙的算法是quicklz.结果文件是qpress归档格式,各样xtrabackup创设的*.qp文件都足以透过qpress程序领取恐怕解压缩
  • --compress-chunk-size=#
    压缩线程工作buffer的字节大小,私下认可是64K
  • --compress-threads=#
    xtrabackup举办相互数据压缩时的worker线程的数量,该选项暗许值是1,并行压缩('compress-threads')能够和相互文件拷贝('parallel')一同行使。比如:'--parallel=4 --compress --compress-threads=2'会创造4个IO线程读取数据并通过管道传送给2个削减线程
  • --create-ib-logfile
    那一个选项如今尚未曾贯彻,近来开创Innodb事务日志,你依然须求prepare四回bakcup
  • --datadir=DIRECTORY
    backup的源目录,mysql实例的多寡目录。从my.cnf中读取,只怕命令行钦赐
  • --defaults-extra-file=[MY.CNF]
    在global files文件今后读取,必得在命令行的率先精选地点钦定
  • --defaults-file=[MY.CNF]
    唯风度翩翩从给定文件读取私下认可选项,必需是个真正文件,必需在命令行第二个筛选地点钦点
  • --defaults-group=GROUP-NAME
    从安顿文件读取的组,innobakcupex七个实例布置时采取
  • --export
    为导出的表创制须要的文件
  • --extra-lsndir=DIRECTORY
    (for --bakcup):在钦赐目录创立后生可畏份xtrabakcup_checkpoints文件的附加的备份
  • --incremental-basedir=DIRECTORY
    创造意气风发份增量备份时,这些目录是增量别分的风姿浪漫份蕴含了full bakcup的Base数据集
  • --incremental-dir=DIRECTORY
    prepare增量备份的时候,增量备份在DIRECTOCR-VY结合full backup创造出大器晚成份新的full backup
  • --incremental-force-scan
    开创风流潇洒份增量备份时,强制扫描全部增在备份中的数据页即便完全改观的page bitmap数据可用
  • --incremetal-lsn=LSN
    创立增量备份的时候内定lsn。
  • --innodb-log-arch-dir
    钦点包涵归档日志的目录。只可以和xtrabackup --prepare选项联合使用
  • --innodb-miscellaneous
    从My.cnf文件读取的后生可畏组Innodb选项。以便xtrabackup以平等的配备运行松手的Innodb。日常无需出示钦赐
  • --log-copy-interval=#
    那些选项钦赐了log拷贝线程check的大运间距(暗许1秒)
  • --log-stream
    xtrabakcup不拷贝数据文件,将工作日志内容重定向到正式输出直到--suspend-at-end文件被剔除。那一个选项自动开启--suspend-at-end
  • --no-defaults
    不从此外取舍文件中读取任何默许选项,必需在命令行第三个筛选
  • --databases=#
    点名了急需备份的数据库和表
  • --database-file=#
    内定包蕴数据库和表的文件格式为databasename1.tablename1为贰个因素,八个因素生龙活虎行
  • --parallel=#
    钦命备份时拷贝多少个数据文件并发的历程数,暗中同意值为1
  • --prepare
    xtrabackup在大器晚成份通过--backup生成的备份实践还原操作,以便计划利用
  • --print-default
    打字与印刷程序参数列表并脱离,必得放在命令行第三人
  • --print-param
    使xtrabackup打字与印刷参数用来将数据文件拷贝到datadir并回复它们
  • --rebuild_indexes
    在apply事务日志之后重新创设innodb协理索引,独有和--prepare一同才生效
  • --rebuild_threads=#
    在严密备份重新建立支持索引的线程数,唯有和--prepare和rebuild-index一齐才生效
  • --stats
    xtrabakcup扫描钦赐数据文件并打字与印刷出索引总括
  • --stream=name
    将具备备份文件以钦定格式流向标准输出,近日扶助的格式有xbstream和tar
  • --suspend-at-end
    使xtrabackup在--target-dir目录中生成xtrabakcup_suspended文件。在拷贝数据文件之后xtrabackup不是脱离而是继续拷贝日志文件相同的时候等待知道xtrabakcup_suspended文件被去除。那项能够使xtrabackup和此外程序合营专门的学问
  • --tables=name
    正则表达式相称database.tablename。备份相配的表
  • --tables-file=name
    钦点文件,贰个表名风姿罗曼蒂克行
  • --target-dir=DIRECTORY
    点名backup的指标地,假设目录不设有,xtrabakcup会创立。假使目录存在且为空则成功。不会覆盖已存在的文书
  • --throttle=#
    钦定每秒操作读写对的数目
  • --tmpdir=name
    当使用--print-param钦赐的时候打字与印刷出不错的tmpdir参数,除却未有别的用。。
  • --to-archived-lsn=LSN
    点名prepare备份时apply事务日志的LSN,只好和xtarbackup --prepare选项联合用
  • --user-memory = #
    经过--prepare prepare备份时候分配多大内存,目标像innodb_buffer_pool_size。暗许值100M万大器晚成您有丰硕大的内部存款和储蓄器。1-2G是推荐值,帮助各个单位(1MB,1M,1GB,1G)
  • --version
    打字与印刷xtrabackup版本并脱离

xbstream

扶助同一时间减少和流式化。要求客性格很顽强在艰苦劳顿或巨大压力面前不屈古板归档tar,cpio和任何不允许动态streaming生成的公文的节制,比方动态压缩文件,xbstream超过其余守旧流式/归档格式的的长处是,并发stream三个文件同一时候更严俊的数据存款和储蓄(所以能够和--parallel选项选项一齐行使xbstream格式实行streaming)

像tar同样使用:

* -x 选项 从规范输入stream read中领取文件到当前目录,除非钦定其余-C选项

* -c 选项 stream命令行钦定的公文到专门的工作输出

目的,通过posix_fadvise()调用减少对OS page cache的熏陶

xtrabackup开启压缩的时候根本数据被减少,满含业务日志和元数据文件,通过点名的压缩算法,唯生龙活虎当前支撑度呃算法是quicklz,结果文件是qpress归档个事。每间隔xtrabakcup生成的.qp文件能够透过qpress文件归档提取或然解压缩。那就意味着不要求经过tar.gz解压缩整个bakcup来还原贰个单表

文件能够通过qpress解压缩,qpress帮忙八线程解压缩

xtrabakcup专门的工作原理

xtrabackup基于InnoDB crash-recovery效率,拷贝innodb数据文件,那会促成数据之中不均等,,不过随后它在文件上进行crash recovery使数据文件一致

innodb维护redo log又称事情日志。redo log日志中包括innodb数据的历次更改。当innodb运转时会去检查数据文件和职业日志,然后又多少个步骤,1,apply应用已交由的政工日志到数据文件,2,已改造但未提交的工作实行undo操作

percona xtrabackup在开发银行的时候记录LSN,然后拷贝数据文件,那会供给部分光阴,,所以倘若文件退换了,它反映出不一样有的时候候间点数据库的动静,。同一时间,xtrabakcup运行四个后台实行监察职业日志,并拷贝变动(复制改正).xtrabakcup须要不停止运输维以上因为事情日志是循环写的,过段时间之后会被复用,xtrabackup必要每便数据文件变化对应的事情日志记录

上述是备份的进程,下一步是prepare的经过,在此个进度中,xtarabakcup通过已拷贝的事体日志在已拷贝的数据文件上实行crash recovery。之后,数据库已经足以进行恢复并能够使用了

以上通过编写翻译好的二进制xtrabakcup施行了。

Innobackup在那基础上扩展了越来越多职能,比方备份Myisam表和.frm文件。它运营xtrabakcup而且等待它做到拷贝文件,之后执行FLUSH TABLES WITH READ LOCK防止mysql数据变动,获得表全局锁,然后flush全部的myisam表到磁盘,之后再释放表全局锁

备份的myisam和innodb表最后会相仿,因为在prepare(recovery)之后,innodb数据会前滚到备份完结时候的时间点,并非回滚到备份发轫时候的时间点。那几个时刻点与FLUSH TABLES WITH READ LOCK发生时相符,全体myisam与已prepare过的Innodb数据是同台的

percona xtrabakcup是有的列如下的工具:

innobackupex:xtrabackup的灯号连接,innobakcupex扶助2.2版本的持有特性和语法,可是今后曾经降级而且在以二个要害版本中remove

xtrabackup:编写翻译的C程序提供备份一整个数据库实例myisam和Innodb表

xbstream:以xbstream格式streaming和领取文件

160321 10:56:07 [00] Writing backup-my.cnf

InnoDB: page_cleaner coordinator priority: -20

5.6在开启和关闭gtid方式下都能够平常备份还原

160321 10:56:07 >> log scanned up to (4151116)

160321 10:56:02 [01] Copying ./mysql/time_zone_transition.ibd to /data1/dbatemp/5711back/mysql/time_zone_transition.ibd

innobackupex   --incremental /data/dbatemp --incremental-basedir=/data1/dbatemp/2016-03-21_12-02-03

restore,将backup移动到目标目录也许服务器

减掉备份

xtrabackup紧要意义测量试验

2.4.1 支持MySQL5.7(5.7.10)

160321 12:02:05 [01] Copying ./sys/sys_config.ibd to /data1/dbatemp/2016-03-21_12-02-03/sys/sys_config.ibd

160321 11:29:38 [01] ...done

InnoDB: Number of pools: 1

InnoDB: Log scan progressed past the checkpoint lsn 4151355

160321 10:56:07 Finished backing up non-InnoDB tables and files

IMPORTANT: Please check that the apply-log run completes successfully.

160321 12:02:06 [01] Copying ./slow_query_log/db.opt to /data1/dbatemp/2016-03-21_12-02-03/slow_query_log/db.opt

-rw-r--r-- 1 root root 1309 Mar 21 12:49 weibo_asset_info.cfg
-rw-r----- 1 root root 16384 Mar 21 12:49 weibo_asset_info.exp
-rw-r----- 1 root root 8980 Mar 21 12:46 weibo_asset_info.frm
-rw-r----- 1 root root 589824 Mar 21 12:46 weibo_asset_info.ibd

160321 11:29:42 [01] Copying ./xtrabackup_slave_info to /data1/mysql5711/xtrabackup_slave_info

innobackupex  /data1/dbatemp/

160321 10:56:02 [01]        ...done

xtrabackup: innodb_data_file_path = ibdata1:100M:autoextend

160321 10:56:02 [01]        ...done

xtrabackup: Starting InnoDB instance for recovery.

xtrabackup:   innodb_data_home_dir = .

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//mysql/help_relation.ibd.delta is 16384 bytes

160321 10:56:02 [01]        ...done

InnoDB: 5.7.10 started; log sequence number 4151317

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//mysql/help_topic.ibd.delta is 16384 bytes

InnoDB: xtrabackup: Last MySQL binlog file position 179018, file name mysql-bin.000001

160321 12:02:05 [01] ...done

InnoDB: Doing recovery: scanned up to log sequence number 4151364 (0%)

 

160321 11:29:39 [01] Copying ./mysql/user.frm to /data1/mysql5711/mysql/user.frm

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

160321 11:29:38 [01] Copying ./abc/object_info.ibd to /data1/mysql5711/abc/object_info.ibd

InnoDB: Log scan progressed past the checkpoint lsn 4151308

160321 12:02:04 [01] Copying ./ibdata1 to /data1/dbatemp/2016-03-21_12-02-03/ibdata1

xtrabackup: innodb_log_files_in_group = 1

 

### 基于base bakcup的增量备份:

160321 12:02:04 >> log scanned up to (4151364)

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(4151355)

xtrabackup: innodb_log_file_size = 8388608

160321 11:29:38 [01] ...done

InnoDB: Creating shared tablespace for temporary tables

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//slow_query_log/global_query_review.ibd.delta is 16384 bytes

InnoDB: Starting shutdown...

160321 10:56:07 Backup created in directory '/data1/dbatemp/5711back'

InnoDB: Number of pools: 1

backup_type = incremental

InnoDB: FTS optimize thread exiting.

160321 12:02:03 version_check Executing a version check against the server...

————————————————————————————————————————————————————————————————————————————————————————————————————

InnoDB: Creating shared tablespace for temporary tables

160321 11:29:38 [01] Copying ibdata1 to /data1/mysql5711/ibdata1

160321 11:29:31 [01] Copying ib_logfile1 to /data1/mysql5711/ib_logfile1

平复单表

xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)

xtrabackup: innodb_log_file_size = 8388608

160321 10:56:07 [00]        ...done

/usr/local/xtrabackup/bin/innobackupex version 2.4.1 based on MySQL server 5.7.10 Linux (x86_64) (revision id: a2dc9d4)

InnoDB: Number of pools: 1

160321 12:02:06 [01] ...done

160321 12:02:06 Starting to backup non-InnoDB tables and files

InnoDB: PUNCH HOLE support not available

xtrabackup: Starting InnoDB instance for recovery.

xtrabackup: Stopping log copying thread.

InnoDB: Using CPU crc32 instructions

prepare base incremental

160321 11:29:37 [01] ...done

xtrabackup: innodb_log_files_in_group = 1

InnoDB: Mutexes and rw_locks use GCC atomic builtins

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//mysql/server_cost.ibd.delta is 16384 bytes

160321 11:29:38 [01] Copying ./slow_query_log/global_query_review_history.frm to /data1/mysql5711/slow_query_log/global_query_review_history.frm

160321 10:56:04 [01] Copying ./mysql/proc.MYI to /data1/dbatemp/5711back/mysql/proc.MYI

2.0.5 --defaults-extra-file 存备份客商的客商名和密码的布局文件

xtrabackup --prepare --target-dir=/data1/dbatemp/testx/

prepare(base incremental) 可选

Using server version 5.7.11-log

160321 10:56:07 [00]        ...done

160321 10:56:03 >> log scanned up to (4151116)

innobackupex --incremental --apply-log --redo-only /data1/dbatemp/2016-03-21_12-02-03/ --use-memory=1G --incremental-dir=/data1/dbatemp/2016-03-21_12-19-21/

InnoDB: not started

xtrabackup: innodb_data_home_dir = .

配置开启gtid的slave

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//sys/sys_config.ibd.delta is 16384 bytes

 

[root@hebe211 dbatemp]# cat 2016-03-21_12-02-03/xtrabackup_checkpoints 

 

xtrabackup: This target seems to be not prepared yet.

/usr/local/xtrabackup/bin/innobackupex version 2.4.1 based on MySQL server 5.7.10 Linux (x86_64) (revision id: a2dc9d4)

This means that the server is not a replication slave. Ignoring the --slave-info option

InnoDB: GCC builtin __sync_synchronize() is used for memory barrier

innobackupex --decompress /data1/dbatemp/2016-03-21_12-32-46/

recover_binlog_info = 0

160321 12:21:48 [01] Copying /data1/dbatemp/2016-03-21_12-19-21/sys/session.frm to ./sys/session.frm

 

160321 11:29:38 [01] Copying ./slow_query_log/global_query_review.ibd to /data1/mysql5711/slow_query_log/global_query_review.ibd

fullbackup

* xtrabackup支持非Innodb表备份,並且Innobackupex在下一版本中移除,建议通过xtrabackup替换innobackupex

innobackupex --stream=tar ./ |gzip - > backup.tar.gz

Applying /data1/dbatemp/2016-03-21_12-19-21//mysql/help_relation.ibd.delta to ./mysql/help_relation.ibd...

160321 12:02:10 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...

```

InnoDB: Removed temporary tablespace data file: “ibtmp1”

Applying /data1/dbatemp/2016-03-21_12-19-21//mysql/slave_relay_log_info.ibd.delta to ./mysql/slave_relay_log_info.ibd...

InnoDB: Log scan progressed past the checkpoint lsn 4151107

innobackupex --no-lock ,拷贝非Innodb数据时不消声匿迹复制线程,不过规格是备份时期非事务型表上不能够有DDL或许DML操作

xtrabackup: cd to /data1/dbatemp/5711back

160321 11:29:38 [01] ...done

160321 12:02:06 [01] Copying ./slow_query_log/global_query_review_history.frm to /data1/dbatemp/2016-03-21_12-02-03/slow_query_log/global_query_review_history.frm

前提

xtrabackup:   innodb_log_files_in_group = 1

解压缩:

cd /data1/dbatemp/testa

2.2.21 支持5.6(基于5.6.24版本)

160321 12:02:10 All tables unlocked

xtrabackup: using the following InnoDB configuration for recovery:

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//abc/weibo_asset_info.ibd.delta is 16384 bytes

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//mysql/innodb_table_stats.ibd.delta is 16384 bytes

InnoDB: GCC builtin __sync_synchronize() is used for memory barrier

xtrabackup: cd to /data1/dbatemp/2016-03-21_12-02-03

InnoDB: Doing recovery: scanned up to log sequence number 4151116 (0%)

InnoDB: Completed initialization of buffer pool

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//mysql/time_zone_name.ibd.delta is 16384 bytes

InnoDB: Number of pools: 1

InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

xtrabackup: page size for /data1/dbatemp/2016-03-21_12-19-21//mysql/time_zone_transition.ibd.delta is 16384 bytes

InnoDB: Highest supported file format is Barracuda.

$ innobackupex --user=DBUSER --password=SECRET /path/to/backup/dir/

Applying /data1/dbatemp/2016-03-21_12-19-21//mysql/gtid_executed.ibd.delta to ./mysql/gtid_executed.ibd...

InnoDB: Log scan progressed past the checkpoint lsn 4151355

MySQL slave binlog position: master host '10.75.22.67', filename 'mysql-bin.000007', position '154

后来隐去--defaults-file=/data1/mysql5711/my5711.cnf.bakuse --no-timestamp --slave-info --socket=/tmp/mysql5711.sock --user=mysqlha --password=xxx 等选择

————————————————————————————————————————————————————————————————————————————————————————————————————

InnoDB: 32 non-redo rollback segment(s) are active.

xtrabackup: innodb_data_home_dir = .

160321 10:56:07 [00] Writing xtrabackup_info

InnoDB: Compressed tables use zlib 1.2.3

xtrabackup:   innodb_log_group_home_dir = /data1/dbatemp/2016-03-21_12-19-21/

InnoDB: Starting crash recovery.

xtrabackup: innodb_data_home_dir = .

复苏机上试行:

last_lsn = 4151364

Applying /data1/dbatemp/2016-03-21_12-19-21//mysql/engine_cost.ibd.delta to ./mysql/engine_cost.ibd...

InnoDB: Number of pools: 1

xtrabackup: uses posix_fadvise().

InnoDB: Allocated tablespace ID 30 for slow_query_log/global_query_review_history, old maximum was 0

160321 10:56:06 >> log scanned up to (4151116)

 分分快三全天计划网站 4

本文由分分快三计划发布,转载请注明来源

关键词: 分分快三计划 mysql 数据 备份 xtrabackup