mysql xtrabackup 结构_MySQL中的xtrabackup的原理解析

最新推荐文章于 2024-04-10 21:53:02 发布

哞滞

最新推荐文章于 2024-04-10 21:53:02 发布

阅读量132

点赞数

文章标签： mysql xtrabackup 结构

本文链接：https://blog.csdn.net/weixin_35952362/article/details/113233425

版权

xtrabackup的官方下载地址为

http://www.percona.com/software/percona-xtrabackup。

xtrabackup包含两个主要的工具，即xtrabackup和innobackupex，二者区别如下：

1 xtrabackup只能备份innodb和xtradb两种引擎的表，而不能备份myisam引擎的表；

2 innobackupex是一个封装了xtrabackup的Perl脚本，支持同时备份innodb和myisam，但在对myisam备份时需要加一个全局的读锁。还有就是myisam不支持增量备份。

innobackupex工具的备份过程原理图

如图，备份开始的时候

1 首先会启动一个xtrabackup_log后台检测的进程，实时检测mysql redo的变化，一旦发现redo有新的日志写入，立刻将日志写入到日志文件xtrabackup_log中

2 复制innodb的数据文件和系统表空间文件idbdata1到对应的以默认时间戳为备份目录的地方

3 复制结束后，执行flush table with read lock操作

4 复制.frm .myd .myi文件

5 并且在这一时刻获得binary log 的位置

6 将表进行解锁unlock tables

7 停止xtrabackup_log进程

全库恢复的过程

这一阶段会启动xtrabackup内嵌的innodb实例，将xtrabackup日志xtrabackup_Log进行回放，将提交的事务信息变更应用到innodb数据或表空间，同时回滚未提交的事务

增量备份

增量备份主要是通过拷贝innodb中有变更的页(指的是LSN大于xtrabackup_checkpoints中的LSN号)。增量备份是基于全备的，第一次增量备份的数据是基于上一次全备，之后的每一次增倍都是基于上一次的增倍，最终达到一致性的增倍，增倍的过程中，和全备很类似，区别在于第二步

增量备份的恢复

和全库恢复类似，也需要两步

1 数据文件的恢复分3部分全备增量备份和xtrabackup_log

2 对未提交事务的回滚

=================================================================================

innobackupex的使用案例

rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

yum -y install percona-xtrabackup

1 创建备份用户

mysql> grant reload,lock tables,replication client on *.* to 'dbbak'@'localhost' identified by 'bk2016';

mysql> flush privileges;

进行数据库全备

mkdir -pv /data/dbbak

cd/data/dbbak

使用以下参数进行全库备份

[root@MASTER_03 dbbak]# innobackupex --defaults-file=/etc/my.cnf --user=dbbak --password=bk2016 --socket=/data/3306/tmp/mysql.sock /data/dbbak/

xtrabackup: Stopping log copying thread.

.160204 00:36:20 >> log scanned up to (1095197210)

160204 00:36:20 Executing UNLOCK TABLES

160204 00:36:20 All tables unlocked

160204 00:36:20 Backup created in directory '/data/dbbak//2016-02-04_00-35-36'

MySQL binlog position: filename 'mysql-bin.000011', position '1338619'

160204 00:36:20 [00] Writing backup-my.cnf

160204 00:36:20 [00] ...done

160204 00:36:20 [00] Writing xtrabackup_info

160204 00:36:20 [00] ...done

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

160204 00:36:20 completed OK!说明备份成功

查看对应生成的文件

[root@MASTER_03 dbbak]# ll 2016-02-04_00-35-36/

total 1048648

-rw-r-----. 1 root root 387 Feb 4 00:36 backup-my.cnf

-rw-r-----. 1 root root 1073741824 Feb 4 00:35 ibdata1

drwx------. 2 root root 4096 Feb 4 00:36 iot

drwx------. 2 root root 12288 Feb 4 00:36 iot2

drwx------. 2 root root 4096 Feb 4 00:36 iot3

drwx------. 2 root root 4096 Feb 4 00:36 lsn

drwx------. 2 root root 4096 Feb 4 00:36 mysql

drwx------. 2 root root 4096 Feb 4 00:36 performance_schema

drwx------. 2 root root 4096 Feb 4 00:36 sakila

drwx------. 2 root root 4096 Feb 4 00:36 sbtest

drwx------. 2 root root 4096 Feb 4 00:36 test

drwx------. 2 root root 4096 Feb 4 00:36 xtrabackup0219

-rw-r-----. 1 root root 25 Feb 4 00:36 xtrabackup_binlog_info

-rw-r-----. 1 root root 119 Feb 4 00:36 xtrabackup_checkpoints

-rw-r-----. 1 root root 539 Feb 4 00:36 xtrabackup_info

-rw-r-----. 1 root root 2560 Feb 4 00:36 xtrabackup_logfile

需要注意的几个文件

[root@MASTER_03 dbbak]# cat 2016-02-04_00-35-36/xtrabackup_checkpoints

backup_type = full-backuped ###全备

from_lsn = 0

to_lsn = 1095197210

last_lsn = 1095197210 #####LSN号

compact = 0

recover_binlog_info = 0

[root@MASTER_03 dbbak]# cat 2016-02-04_00-35-36/xtrabackup_binlog_info

mysql-bin.000011 1338619

删掉某个数据库，进行全库恢复

mysql> drop databaseiot2;

Query OK,49 rows affected (7.93 sec)

关闭数据库

[root@MASTER_03 dbbak]# /etc/init.d/mysqld stop

Shutting down MySQL.......... SUCCESS!

[root@MASTER_03 dbbak]# mv /data/3306/data /data/3306/data_bak

[root@MASTER_03 dbbak]# mkdir /data/3306/data

恢复

[root@MASTER_03 dbbak]# innobackupex --apply-log /data/dbbak/2016-02-04_00-35-36/

160204 00:56:47 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.3.3 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 525ca7d)

xtrabackup: cd to /data/dbbak/2016-02-04_00-35-36/

xtrabackup: This target seems to be not prepared yet.

xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1095197210)

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

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

xtrabackup: Starting InnoDB instance for recovery.

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

InnoDB: Using atomics to ref count buffer pool pages

InnoDB: The InnoDB memory heap is disabled

InnoDB: Mutexes and rw_locks use GCC atomic builtins

InnoDB: Memory barrier is not used

InnoDB: Compressed tables use zlib 1.2.3

InnoDB: Using CPU crc32 instructions

InnoDB: Initializing buffer pool, size = 100.0M

InnoDB: Completed initialization of buffer pool

InnoDB: Highest supported file format is Barracuda.

InnoDB: The log sequence numbers 532847032 and 532847032 in ibdata files do not match the log sequence number 1095197210 in the ib_logfiles!

InnoDB: Database was not shutdown normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Restoring possible half-written data pages

InnoDB: from the doublewrite buffer...

InnoDB: 128 rollback segment(s) are active.

InnoDB: Waiting for purge to start

InnoDB: 5.6.24 started; log sequence number 1095197210

xtrabackup: Last MySQL binlog file position 1337268, file name mysql-bin.000011

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 1095198530

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

InnoDB: Using atomics to ref count buffer pool pages

InnoDB: The InnoDB memory heap is disabled

InnoDB: Mutexes and rw_locks use GCC atomic builtins

InnoDB: Memory barrier is not used

InnoDB: Compressed tables use zlib 1.2.3

InnoDB: Using CPU crc32 instructions

InnoDB: Initializing buffer pool, size = 100.0M

InnoDB: Completed initialization of buffer pool

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

InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000

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

InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000

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

InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000

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

InnoDB: New log files created, LSN=1095198530

InnoDB: Highest supported file format is Barracuda.

InnoDB: 128 rollback segment(s) are active.

InnoDB: Waiting for purge to start

InnoDB: 5.6.24 started; log sequence number 1095198732

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 1095202631

160204 00:57:31 completed OK!

以上对应的目录就是innobackupex全备份自己创建的目录

[root@MASTER_03 dbbak]# innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /data/dbbak/2016-02-04_00-35-36/

160204 01:08:39 [01] ...done

160204 01:08:39 [01] Copying ./iot2/t_hash1#P#p3.ibd to /data/3306/data/iot2/t_hash1#P#p3.ibd

160204 01:08:39 [01] ...done

160204 01:08:39 [01] Copying ./iot2/db.opt to /data/3306/data/iot2/db.opt

160204 01:08:39 [01] ...done

160204 01:08:39 [01] Copying ./xtrabackup0219/db.opt to /data/3306/data/xtrabackup0219/db.opt

160204 01:08:39 [01] ...done

160204 01:08:39 completed OK!

更改权限

[root@MASTER_03 tmp]# chown -R mysql.mysql /data/3306/data/

启动mysqld

[root@MASTER_03 tmp]# /etc/init.d/mysqld start

[root@MASTER_03 tmp]# mysql -uroot -p

Enter password:

Welcometo the MySQL monitor. Commands end with ; or\g.

Your MySQL connection idis 1Server version:5.6.28-logMySQL Community Server (GPL)

Oracleis a registered trademark of Oracle Corporation and/orits

affiliates. Other names may be trademarksoftheir respective

owners.

Type'help;' or '\h' for help. Type '\c' to clear the currentinput statement.

mysql>show databases;+--------------------+

| Database |

+--------------------+

| information_schema |

| iot |

| iot2 | ###被删除的库

| iot3 |

| lsn |

| mysql |

| performance_schema |

| sakila |

| sbtest |

| test |

| xtrabackup0219 |

+--------------------+

11 rows in set (0.00 sec)

发现数据是已经成功恢复

先全备

mysql> usextrabackup0219;

mysql> create table t1(id int(5) primary key auto_increment,name varchar(20));

innobackupex --defaults-file=/etc/my.cnf --user=dbbak --password=bk2016 --socket=/data/3306/tmp/mysql.sock /data/dbbak/

增量备份

#####往表里插入数据

mysql> insert into t1 select 1,'love sql';

Query OK, 1 row affected (0.01 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into t1 select 2,'love sql';

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into t1 select 3,'love sql';

Query OK, 1 row affected (0.01 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from t1;

+----+----------+

| id | name |

+----+----------+

| 1 | love sql |

| 2 | love sql |

| 3 | love sql |

+----+----------+

3 rows in set (0.00 sec)

[root@MASTER_03 dbbak]# innobackupex --defaults-file=/etc/my.cnf --user=dbbak --password=bk2016 --socket=/data/3306/tmp/mysql.sock --incremental /data/dbbak/ --incremental-basedir=/data/dbbak/2016-02-04_01-44-24/ --parallel=2

[root@MASTER_03 dbbak]# du -sh *

1.5G2016-02-04_01-44-24

6.0M2016-02-04_01-46-48

[root@MASTER_03 dbbak]# cat 2016-02-04_01-46-48/xtrabackup_checkpoints

backup_type = incremental ###说明是增量的

from_lsn = 1095215215

to_lsn = 1095217565

last_lsn = 1095217565

compact = 0

recover_binlog_info = 0

此时再插入数据

mysql> insert into t1 select 4,'mysql dba';

Query OK, 1 row affected (0.01 sec)

Records: 1 Duplicates: 0 Warnings: 0

增量备份2

[root@MASTER_03 dbbak]# du -sh *

1.5G2016-02-04_01-44-24

6.0M2016-02-04_01-46-48

5.9M2016-02-04_01-49-21

增量备份的恢复

增量备份的恢复需要有3个步骤

1 恢复完全备份

2 恢复增量备份到完全备份(开始恢复的增量备份要添加--redo-only参数,到最后一次增量备份要去掉--redo-only)

3 对整体的完全备份进行恢复，回滚未提交的数据

[root@MASTER_03 dbbak]# innobackupex --apply-log --redo-only /data/dbbak/2016-02-04_01-44-24/

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 1095209828

160204 01:27:09 completed OK!

将增量1应用到完全备份

[root@MASTER_03 dbbak]# innobackupex --apply-log --redo-only /data/dbbak/2016-02-04_01-44-24/ --incremental-dir=/data/dbbak/2016-02-04_01-46-48/

[root@MASTER_03 dbbak]# innobackupex --apply-log /data/dbbak/2016-02-04_01-44-24/ --incremental-dir=/data/dbbak/2016-02-04_01-49-21/

把所有合在一起的完全备份整体进行一次apply操作，回滚未提交的数据

[root@MASTER_03 dbbak]# innobackupex --apply-log /data/dbbak/2016-02-04_01-44-24/

模拟测试

mysql> drop table t1;

Query OK, 0 rows affected (0.05 sec)

[root@MASTER_03 dbbak]# rm -rf /data/3306/data/

[root@MASTER_03 dbbak]# mkdir /data/3306/data

[root@MASTER_03 dbbak]# innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /data/dbbak/2016-02-04_01-44-24/

[root@MASTER_03 dbbak]# chown -R mysql.mysql /data/3306/data/

登录查看

mysql> select * from t1;

+----+-----------+

| id | name |

+----+-----------+

| 1 | love sql |

| 2 | love sql |

| 3 | love sql |

| 4 | mysql dba |

+----+-----------+

4 rows in set (0.05 sec)

发现数据是已经正确

测试完毕，恢复还是比较简单的。

哞滞

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql xtrabackup 结构_MySQL中的xtrabackup的原理解析

xtrabackup的官方下载地址为http://www.percona.com/software/percona-xtrabackup。xtrabackup包含两个主要的工具，即xtrabackup和innobackupex，二者区别如下：1 xtrabackup只能备份innodb和xtradb两种引擎的表，而不能备份myisam引擎的表；2 innobackupex是一个封装了xtrabac...
复制链接

扫一扫