MySQL数据库备份还原(第一讲-xtrabackup)
1.基本概念
1.1 备份类型
备份类型 | 说明 |
热备份 | 当数据库进行备份时,数据库的读写操作不受影响 |
冷备份 | 当数据库进行备份时,数据库不能进行读写操作,也就是脱机备份,数据库要下线 |
温备份 | 当数据库进行备份时,数据库可以进行读操作,但是不能执行写操作。 ①在进行备份之前,设置MySQL数据库为只读模式。防止普通用户进行数据修改的操作,也就是说普通用户在进行insert、update、delete操作时,都会报出数据库处于只读模式不能发生数据变化的错误提示。但是,无法限定具有超级权限的root用户。 命令:set global read_only = 1; ②备份完成之后,再将MySQL从只读模式设置为读写状态。 命令:set global read_only = 0; ps:这里设置的是全局的。 |
1.2 备份方式
备份种类 | 说明 | 特点 |
全量备份 | 指对某一个时间点上的所有数据或应用进行一个完全拷贝。 | ① 备份的数据全面,且最完整; ② 数据量大的时候备份时间长; ③ 备份数据会存在大量重复信息。 |
增量备份 | 1).概念:指在一次全量备份或上一次增量备份后,以后每次的备份都只备份与前一次相比增加或者被修改的文件。 2).解释:第一次增量备份的对象时进行全备后所产生的增加或修改的文件;第二次增量备份的对象是第一次增量备份后所产生的增加或修改的文件,以此类推。 | ① 与全量备份相比,没有重复的备份数据; ② 备份数据量不大,备份恢复的时间短; ③ 数据恢复麻烦,必须具有上一次全量备份和所有增量备份的文件,并且必须按照全量备份到增量备份的顺序进行反推恢复。 |
差异备份 | 1).概念:指在一次全备后到进行差异备份的这段时间内,对那些增加或修改的文件进行备份。 2).解释:与增量备份的区别在于,差异备份每次都是在上次的全备基础上进行备份的。 | ①备份时间短,节省磁盘空间; ②恢复所需时间短。 |
比较 数据备份的数据量(大>>小):全量备份 > 差异备份 > 增量备份 数据的恢复时间(快>>慢):全量备份 > 差异备份 > 增量备份 |
1.3 备份种类
备份方式 | 说明 |
逻辑备份 | 备份sql语句。把数据库的结构定义语句,数据内容的插入语句等全部存储下来。在恢复的时候执行备份的sql语句就可以实现数据库数据的重现。 |
物理备份 | 简单理解:把MySQL存储好的所有文件直接进行复制保存下来(冷备的时候是这样的,热备的时候,对于innodb来说存在事务(redo log)的问题)。 1).举栗子:创建一个数据库base后,MySQL就会在datadir/目录下会创建一个base目录,那么把base目录下的所有文件保存下来就是物理备份,说白了就是Ctrl + c 和 Ctrl + V。 2).文件存储结构(datadir目录下的数据库文件) ①.如果存储引擎是innodb,那么base目录下有两类文件:*.frm(表结构文件)和*.ibd(表数据和索引文件,独享表空间存储方式使用.ibd文件)。 ②.如果存储引擎是myisam,那么base目录下有三类文件:*.frm(表结构文件)、*.MYD(表数据文件)和*.MYI(表索引文件)。 |
基于快照的备份 |
|
增量备份 | 刷新二进制日志 |
2.数据库备份还原要求
1).清算流程开始执行之前进行全量备份。
2).清算流程在执行的过程中,一旦某个节点出现问题,或者系统的其他需要,数据库的数据可以还原到任何之前的一个节点或做过备份的时间点。也就是对每个节点都需要进行增量备份,而不是差异备份。
3.数据库备份还原技术
3.1 Xtrabackup备份
3.1.1介绍
Xtrabackup是一款基于MySQL的热备份工具。
主要有两个工具:xtrabackup和innobackupex。
1).Xtrabackup:C/C++编译的二进制程序,用来备份InnoDB,不能备份InnoDB表,在内部实现了对InnoDB的热备份,和MySQL没有交互。
2).Innobackupex:由Perl脚本编写,是对xtrabackup的封装。
① 通过调用xtrabackup命令来备份innodb表;
② 通过调用mysqldump等命令来实现对非innodb表的备份;
③ 还会和mysqld server发送命令进行交互,如,加读锁(FTWRL,flush table with read lock)等。
由于两个工具是用perl和C二进制两个进程实现的,没有较好的通信方式,也没有啥协议。 所以在2.3版本之前是通过判断备份控制文件是否存在来协调两个进程之间的工作,但是这种方式很不靠谱,因为很容易被外部干扰(比如文件被误删)。 然后,在2.3版本开始,用C重写了innobackupex并集成到了xtrabackup中,只有一个二进制文件,另外为了使用上的兼容考虑,将innobackupex作为xtrabackup的一个软链接(类似Windows系统的快捷方式)①。 A.通过命令查看可知,2.4版本的还是两个二进制文件,并不是一个。 B.软链接倒是真的有。 |
3.1.2安装
3.1.2.1下载
下载网址:https://www.percona.com/downloads , 这里下载【通用Linux】版本
3.1.2.2安装
【备注】:在安装前先查看一下是否已经安装,因为Linux安装软件方式较多,查看方式如下。
① rpm包安装:rpm -qa | grep xtrabackup
② yum安装:yum list installed | grep xtrabackup
3.1.2.2.1 yum安装
如果下载的是tar.gz,那么直接解压后是不能使用的,xtrabackup无法执行。
1).安装percona yum存储库,通过以root用户或sudo运行
命令:yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
2).搜索xtrabackup的版本
命令:yum list | grep xtrabackup
这里就安装最新版本24吧
3).安装percona-xtrabackup-24
命令:yum -y install percona-xtrabackup-24.i686
yum -y install percona-xtrabackup-24-debuginfo.i686
yum -y install percona-xtrabackup-test-24.i686
4).查看是否安装成功
① 查看是否已安装
命令:rpm -qa | grep xtrabackup
② 查看安装信息
命令:rpm -ql 上一步的查询结果(percona-xtrabackup-24-2.4.21-1.el6.i686)
3.1.2.2.2 问题
在安装的过程中存在着需要依赖的其他软件,我们可以去 http://rpmfind.net/linux/rpm2html/search.php 搜索相对应的rpm,下载的时候注意版本。
问题1:缺少libev.so.4
1).错误信息描述如下
2).解决
①.搜索,下载
②.安装
命令:rpm -ivh libev-4.04-2.el6.i686.rpm
问题2:缺失perl
1).安装perl
命令:yum -y install perl-DBD-MySQL
报错:和现有MySQL冲突 conflicts with file from package mysql-community-server-5.6.42-2.el6.i686
2).查看需要安装哪些 libs-compat 软件
命令:yum list | grep mysql | grep libs-compat
安装,命令: yum -y install mysql-community-libs-compat.i686
3).再使用命令 yum -y install perl-DBD-MySQL 安装perl
3.1.2.3 配置环境变量
配置环境变量,输入命令:vi /etx/profile 编辑,在最底部添加如下配置
export PATH=$PATH:xtrabackup安装目录 |
3.1.3MySQL配置
1).先使用命令 mysql --help | grep "my.cnf" 查看MySQL配置文件的位置
这里只介绍在备份还原过程中比较重要的配置。
配置 | 说明 |
basedir=/usr/local/mysql | 数据库安装目录 |
datadir=/usr/local/mysql/data | 数据库文件存放位置 |
log-error=/usr/local/mysql/logs/err.log | 记录MySQL启动、运行、停止时产生的日志文件。 配置完后需要手动创建目录,并设置目录的写入权限。 在重启报错的时候可用来排查问题。 |
innodb_file_per_table=1 | 1).该配置的目的是为了将每个表都以独立文件方式进行存储(也就是独立表空间)。每个表都有一个 *.frm 文件(存储表结构) 和 *.ibd文件(存放表数据和索引)。如果不开启的话,那么数据库的所有表数据和索引文件都将放在一个文件中(也就是共享表空间),默认在data目录下(默认文件名为ibdata1,初始化10M) 2).xtrabackup物理备份 ①可以备份指定数据库的指定表; ②并发拷贝需要MySQL开启该配置。 |
二进制日志binlog配置:下面①和②都需要配置,才会真正开启二进制日志功能。 | |
sql_log_bin=1 | 开启二进制日志 |
log_bin=/usr/local/mysql/logs/bin_log_file | 存放二进制日志的目录位置,如果不指定的话,默认是和data在同一个目录下。而备份的时候是不需要的,为了避免浪费时间和空间,还是建议开启。 |
3.1.4备份
3.1.4.1备份流程(原理,待完善)
1.备份流程
2.备份过程①
1).innobackupex在启动后,会先fork一个进程,启动xtrabackup进程,然后等到xtrabackup备份完ibd数据文件;
2).xtrabackup在备份innodb相关数据的时候,存在两种线程。
① 一种是redo日志②拷贝线程,负责拷贝redo log文件;一种是*.idb(表数据和索引文件)拷贝线程,负责拷贝ibd文件。
② redo拷贝线程只有一个,ibd拷贝线程可以通过参数来设定。
③ redo拷贝线程在ibd拷贝线程之前启动,在idb线程结束后结束。
Xtrabackup进行开始执行后,先启动redo拷贝线程,从最新的checkpoint点开始顺序拷贝redo日志;然后再启动ibd拷贝线程,在xtrabackup拷贝*.ibd文件的过程中,innobackupex进行一直处于等待状态。
3).xtrabackup拷贝完*.ibd文件后,通知innobackupex,同时自己进入等待状态(redo线程仍然继续拷贝);
4).innobackupex收到xtrabackup通知后,执行 flush no_write_to_binlog tables和 flush tables with read lock,然后开始备份非innodb文件(包括 *.frm、*.MYD、*.MYI等)。
① flush no_write_to_binlog tables:停止写入binlog日志(MySQL的逻辑日志[简单说就是sql语句],二进制文件,记录数据库执行的写入性操作[不包括查询]信息);
② flush tables with read lock:关闭所有打开的表,同时对所有数据库的表加上全局只读锁。此时,DML语句会夯住,直到显示的使用unlock tables释放锁。
flush tables with read lock举栗子
--会话① mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.04 sec)
--会话②,DML操作会hang住 mysql> insert into test values(10,'LEADER','Beijing');
--会话① mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.01 sec)
--会话②,DML操作成功 mysql> insert into test values(10,'LEADER','Beijing'); Query OK, 1 row affected (18 min 44.54 sec) |
【注意】:在拷贝非innodb文件的过程中,由于数据库处于全局只读状态,所以,要考虑对性能的影响。
5).当innobackupex拷贝完所有非innodb表文件后,通知xtrabackup,同时自己进入等待状态;
在5)和6)之间还有两步工作,暂时不清楚是由xtrabackup完成的还是由innobackupex完成的。 I.读取binlog信息,并写入xtrabackup_binlog_info,该文件记录了“最新的binlog日志文件名称以及所处的位置”; II.执行 flush no_write_to_binlog engine logs 这里执行 flush no_write_to_binlog engine logs的目的是为了 刷新 redo log buffer中的日志数据到redo log file磁盘文件中,确保redo日志拷贝线程可以拷贝到最后的redo log日志数据,确保事务是完整的。 假设存在以下情况。 ① 开启事务A,执行insert into语句,再commit。这些操作都已经加载到了redo log buffer中; ② 此时,在redo log buffer刷到磁盘之前,innobackupex刚好执行了flush tables with read lock加上了全局只读锁,那么这部分redo日志数据就还在内存中; 这种情况下,flush no_write_to_binlog engine logs就起作用了,这样做的目的就是为了防止在恢复数据时丢失最后一组事务。 |
6).xtrabackup收到innobackupex备份完非innodb通知后,就停止redo拷贝线程,然后通知innobackupex redo 日志拷贝完成;
7).innobackupex收到redo log备份完成通知后,就开始释放锁,执行 unlock tables;
8).innobackupex和xtrabackup进程各自完成收尾工作,生成backup-my.cnf和xtrabackup_info等文件,备份完成。
【综述】:从备份流程中可以看出。
xtrabackup负责拷贝innodb相关数据,包含redo log file 和 *.ibd文件(表数据和索引文件);
Innobackupex负责拷贝非innodb相关数据,包含 *.frm(表结构文件)、*.MYD(表数据文件)和*.MYI(表索引文件)等文件。
3.1.4.2全量备份(全库)
3.1.4.2.1命令
命令:将innobackupex命令的日志打印到 sh003_backup.out文件中。 | |
innobackupex --defaults-file=/etc/my.cnf --socket=/var/lib/mysql/mysql.sock --host=localhost --port=3308 --user=root --password=* --no-timestamp /backup/mysql/data/xtrabackup/sh003>/backup/mysql/data/xtrabackup/logs/sh003_backup.out 2>&1 | |
参数 | 说明 |
--defaults-file | 该参数必须放在第一个位置,不然会报错。 指定MySQL的配置文件,xtrabackup需要从配置文件中找到datadir的路径 |
--socket | Xtrabackup在进行备份的时候,默认会去/var/lib/mysql/mysql.sock文件中获取数据库的socket信息,如果修改了数据库的socket配置,则需要使用--socket参数进行重新指定,否则会抛出找不到连接的异常。 |
--host | 指定ip |
--port | 指定端口号 |
--user | 指定用户 |
--password | 指定用户密码 |
--no-timestamp | 如果不加的话,那么在备份目录下会自动生成一个以当前日期、时间为名字的目录。所以,一般情况下,还是由用户自己来定义吧。 |
执行命令和结果如下
3.1.4.2.2日志概览
通过日志分析查看innobackupex的基本流程。
I.拷贝大致过程
xtrabackup: recognized server arguments: --parallel=2 xtrabackup: recognized client arguments: 210225 14:20:14 innobackupex: Starting the backup operation 210225 14:20:14 version_check Connecting to MySQL server with DSN 1、数据库连通性和版本检测 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=127.0.0.1' as 'test' (using password: YES). 210225 14:20:14 version_check Connected to MySQL server 210225 14:20:14 version_check Executing a version check against the server... 210225 14:20:14 version_check Done. 210225 14:20:14 Connecting to MySQL server host: 127.0.0.1, user: test, password: set, port: not set, socket: not set Using server version 5.7.27-log /usr/bin/innobackupex version 2.4.19 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c2d69da) xtrabackup: uses posix_fadvise(). 2、读取数据库相关的配置信息(数据和日志) xtrabackup: cd to /data/mysql/data/ xtrabackup: open files limit requested 0, set to 655350 xtrabackup: using the following InnoDB configuration: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = /data/mysql/redolog xtrabackup: innodb_log_files_in_group = 4 xtrabackup: innodb_log_file_size = 2147483648 InnoDB: Number of pools: 1 3、开启redo日志拷贝线程,从最新的checkpoint开始顺序拷贝redolog 210225 14:20:14 >> log scanned up to (28198520580) InnoDB: Opened 3 undo tablespaces InnoDB: 0 undo tablespaces made active xtrabackup: Generating a list of tablespaces InnoDB: Allocated tablespace ID 5 for mysql/plugin, old maximum was 3 xtrabackup: Starting 2 threads for parallel data files transfer 4、开始拷贝 .ibd ibdata1 undo 等innodb文件 210225 14:20:15 [02] Copying ./ibdata1 to /data/backup/2021-02-25_14-20-14/ibdata1 210225 14:20:15 [01] Copying /data/mysql/undolog/undo001 to /data/backup/2021-02-25_14-20-14/undo001 210225 14:20:15 [02] ...done 210225 14:20:15 [01] ...done 210225 14:20:15 >> log scanned up to (28198520580) 210225 14:20:16 [02] Copying /data/mysql/undolog/undo002 to /data/backup/2021-02-25_14-20-14/undo002 210225 14:20:23 >> log scanned up to (28198520580) 210225 14:20:25 [02] ...done 210225 14:20:25 [01] Copying ./mysql/plugin.ibd to /data/backup/2021-02-25_14-20-14/mysql/plugin.ibd 210225 14:20:25 [01] ...done 5、执行FLUSH NO_WRITE_TO_BINLOG TABLES 和 FLUSH TABLES WITH READ LOCK 210225 14:20:28 >> log scanned up to (28198520580) 210225 14:20:29 Executing FLUSH NO_WRITE_TO_BINLOG TABLES... 210225 14:20:29 Executing FLUSH TABLES WITH READ LOCK... 6、拷贝非innodb表相关文件信息 210225 14:20:29 Starting to backup non-InnoDB tables and files 210225 14:20:29 [01] Copying ./mysql/db.opt to /data/backup/2021-02-25_14-20-14/mysql/db.opt 210225 14:20:29 [01] ...done 210225 14:20:34 [01] Copying ./sys/statements_with_temp_tables.frm to /data/backup/2021-02-25_14-20-14/sys/statements_with_temp_tables.frm 210225 14:20:34 [01] ...done 210225 14:20:40 Finished backing up non-InnoDB tables and files 7、读取binlog pos信息并写入xtrabackup_binlog_info文件 210225 14:20:40 [00] Writing /data/backup/2021-02-25_14-20-14/xtrabackup_slave_info 210225 14:20:40 [00] ...done 210225 14:20:40 [00] Writing /data/backup/2021-02-25_14-20-14/xtrabackup_binlog_info 210225 14:20:40 [00] ...done 8、执行 FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS 210225 14:20:40 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... 9、停止redolog日志拷贝线程 xtrabackup: The latest check point (for incremental): '28198520571' xtrabackup: Stopping log copying thread. .210225 14:20:40 >> log scanned up to (28198520580) 10、执行UNLOCK TABLES释放表锁 210225 14:20:41 Executing UNLOCK TABLES 210225 14:20:41 All tables unlocked 11、收尾生成backup-my.cnf和xtrabackup_info等文件,备份完成 210225 14:20:41 [00] Copying ib_buffer_pool to /data/backup/2021-02-25_14-20-14/ib_buffer_pool 210225 14:20:41 [00] ...done 210225 14:20:41 Backup created in directory '/data/backup/2021-02-25_14-20-14/' MySQL binlog position: filename 'bin.000007', position '2549986', GTID of the last change 'dafc13c9-7eff-11ea-addc-5254010ec9c8:1-14, dd1cffa1-7eff-11ea-a289-5254010ec9c8:1-56, e0860904-7eff-11ea-989d-5254010ec9c8:1-3999' 210225 14:20:41 [00] Writing /data/backup/2021-02-25_14-20-14/backup-my.cnf 210225 14:20:41 [00] ...done 210225 14:20:41 [00] Writing /data/backup/2021-02-25_14-20-14/xtrabackup_info 210225 14:20:41 [00] ...done xtrabackup: Transaction log of lsn (28198520571) to (28198520580) was copied. 210225 14:20:42 completed OK! |
II.备份成功标志为“completed OK!”
1). 开始,日志一开始就提示了备份成功标志
2). 日志结束位置显示“completed OK!”,表示备份成功。
3.1.4.2.3问题
现象1:在xtrabackup拷贝innodb相关文件之前没有对数据库加锁
问题1:此时对表数据进行写入操作的话,有什么影响?
结论:没影响。
原因:因为xtrabackup在备份innodb相关数据时,开启了redo拷贝线程对redo log file也进行了备份。
问题2:由于没有对数据库加锁,那么此时的DDL语句是被允许的,有什么影响?
结论:xtrabackup备份报错。
原因:xtrabackup在扫描innodb日志的LSN(log sequence number)时存在多次扫描,当发现lsn发生改变时就会报错。
综述:在备份期间要禁止DDL操作。
3.1.4.2.4优化
1).参数
从上面可以看出,所耗时间为8s,耗时有点长了啊。使用man innobackupex搜索一番后发现有如下参数可以加速备份。
参数 | 说明 |
--parallel | 在xtrabackup开始拷贝*.ibd文件(表数据和索引文件)的时候可以并行拷贝的线程数量,默认是单线程拷贝。 【注意】:在MySQL的配置文件my.cnf中,必须配置innodb_file_per_table=1,开启独立表空间。否则,如果采用共享表空间的话(即所有表数据和索引都将存储到ibdata*文件),不会起到任何作用。 |
--compress-threads | 暂不考虑,因为如果使用了的话,那么在还原的时候就需要解压(--decompress)。 |
2).测试
① 执行命令和结果如下
② 从日志中可以看出在备份*.ibd文件(表数据和索引文件)的时候,开启了4个线程。
参数 | 所耗时间 |
| 8s |
--parallel=4 | 4s |
3.1.4.3全量备份(指定库和表)
3.1.4.3.1命令
innobackupex --defaults-file=/etc/my.cnf --socket=/var/lib/mysql/mysql.sock --host=localhost --port=3308 --user=root --password=* --parallel=2 --tables-file=/backup/mysql/data/xtrabackup/tables/tablename.txt --no-timestamp /backup/mysql/data/xtrabackup/sh003>/backup/mysql/data/xtrabackup/logs/sh003_backup.out 2>&1 | |
参数 | 说明 |
--tables-file | 在文件tablename.txt中指定需要备份哪些库和哪些表(格式:库名.表名,每行只能写一个) |
【注】:--databases的功能与--tables-file相同。
3.1.5还原
参考
- [MySQL · 物理备份 · Percona XtraBackup 备份原理](http://mysql.taobao.org/monthly/2016/03/07/)
- [必须了解的mysql三大日志-binlog、redo log和undo log](https://juejin.cn/post/6860252224930070536)