Linux MySQL 学习笔记-备份与恢复
1 简介
下面是备份非常重要的几个理由:
-
灾难恢复
灾难恢复是下列场景下需要做的事情:硬件故障、一个不经意的Bug导致数据损坏或者服务器及其数据由于某些原因不可获取或无法使用等。
你需要准备好应付很多问题: 某人偶然连错服务器执行了一个 ALTER TABLE的操作; 机房大楼被烧毁, 恶意的黑客攻击或 MySQL的Bug等。
尽管遭受任何一个特殊的灾难的几率都非常低, 但所有的风险叠加在一起就很有可能会碰到. -
人们改变想法
不必惊讶,很多人经常会在删除某些数据后又想要恢复这些数据。 -
审计
有时候需要知道数据或 Schema在过去的某个时间点是什么样的。
例如,你也许被卷入一场法律官司,或发现了应用的一个Bug,想知道这段代码之前干了什么(有时候,仅仅依靠代码的版本控制还不够)。 -
测试
一个最简单的基于实际数据来测试的方法是, 定期用最新的生产环境数据更新测试服务器。
如果使用备份的方案就非常简单: 只要把备份文件还原到测试服务器上即可.
备份是为了保证在灾难发生的时候，保证数据不丢失或者最小程度的丢失
备份策略要求：
损失最小、对数据库的影响最小
备份可以通过计划任务 + 脚本来实现自动化
1.2 备份的分类
-
根据服务是否在线继续分为热备份、温备份和冷备份
热备份：读写均不受影响
温备份：仅可以执行读操作
冷备份：也称为离线备份，读写操作均终止。 -
根据备份时是否直接复制数据文件分为物理备份和逻辑备份。
物理备份：直接复制数据文件，速度快。
逻辑备份：通常是将数据对象(如用户,表,存储过程等等)导出至文本文件中。速度慢、丢失浮点精度；方便使用文本根据直接进行处理、可移植能力强 -
根据备份数据内容分为完全备份、增量备份和差异备份。
完全备份：备份全部数据。
增量备份：仅备份上次完全备份或增量备份以后变化的数据。
差异备份：仅备份上次完全备份以来变化的数据
备份时间的选择：数据库访问量较小的时候做备份。
常用备份工具：
mysqldump: mysql官方提供的逻辑备份工具
lv：逻辑卷快照
xtrabackup: 开源的免费数据库热备份软件,可实现逻辑备份和物理备份
Innodb：商业工具
mysqlbackup：Mysql企业版的备份工具
备份建议：
- 备份并不是时时刻刻总是可靠的，所以定期做恢复测试非常重要。
- 对于大数据库，物理备份是必需的，逻辑备份太慢并受到资源限制，基于快照的备份是一个很好的选择。对于较小的数据库，逻辑备份可以很好的胜任。
- 备份binlog日志。可以每次备份后刷新binlog日志，这样只需要备份最新的binlog日志。
- 逻辑备份与物理备份混合。物理备份虽快，但也有一些显著的缺点，如不能跨平台，OS，版本等。另外，物理备份所占用的空间通常比逻辑备份大很多。
- 如果可以，保留多份备份（本机、跨机器备份、跨地域备份等）。
2 备份和恢复
备份可以通过计划任务 + 脚本来实现自动化
一、备份的分类
-
根据服务是否在线继续分为热备份、温备份和冷备份
热备份：读写均不受影响
温备份：仅可以执行读操作
冷备份：也称为离线备份，读写操作均终止。 -
根据备份时是否直接复制数据文件分为物理备份和逻辑备份。
物理备份：直接复制数据文件，速度快。
逻辑备份：通常是将数据对象(如用户,表,存储过程等等)导出至文本文件中。速度慢、丢失浮点精度；方便使用文本根据直接进行处理、可移植能力强 -
根据备份数据内容分为完全备份、增量备份和差异备份。
完全备份：备份全部数据。
增量备份：仅备份上次完全备份或增量备份以后变化的数据。
差异备份：仅备份上次完全备份以来变化的数据
备份时间的选择：数据库访问量较小的时候做备份。
常用备份工具：
mysqldump: mysql官方提供的逻辑备份工具
lv：逻辑卷快照
xtrabackup: 开源的免费数据库热备份软件,可实现逻辑备份和物理备份
Innodb：商业工具
mysqlbackup：Mysql企业版的备份工具
备份建议：
- 备份并不是时时刻刻总是可靠的，所以定期做恢复测试非常重要。
- 对于大数据库，物理备份是必需的，逻辑备份太慢并受到资源限制，基于快照的备份是一个很好的选择。对于较小的数据库，逻辑备份可以很好的胜任。
- 备份binlog日志。可以每次备份后刷新binlog日志，这样只需要备份最新的binlog日志。
- 逻辑备份与物理备份混合。物理备份虽快，但也有一些显著的缺点，如不能跨平台，OS，版本等。另外，物理备份所占用的空间通常比逻辑备份大很多。
- 如果可以，保留多份备份（本机、跨机器备份、跨地域备份等）。
2.1 数据的导入与导出
一、导入数据
把存在系统文件中的数据，导入到数据库中
以默认分隔符导入
- 创建源文件
导入数据时默认的字段分隔符是\t，默认行分隔符是\n
[root@ULA ~]# vim /opt/test.txt
1 root #以tab做分隔符
2 bin
3 adm
1000 user
- 创建表
MariaDB [test]> create table test(
-> uid int,
->username char(20));
Query OK, 0 rows affected (0.04 sec)
- 导入数据 记录
语法：load data infile 文件名 into table 表名;
MariaDB [test]> load data infile "/opt/test.txt" into table test;
MariaDB [test]> select * from test;
+------+----------+
| uid | username |
+------+----------+
| 1 | root |
| 2 | bin |
| 3 | adm |
| 1000 | user |
+------+----------+
总结：
1）文件不要放在mysql读不到的地方(注意目录权限)
2）mysql用户对要导入的文件至少要有读的权限(文件权限)
3）文件名用双引号或单引号引起来
手动指定分隔符
- 创建源文件
# head /etc/passwd > /opt/pass.txt
- 创建表
MariaDB [test]> create table pass (
-> username char(20),
-> password char(1),
-> uid int(10),
-> gid int(10),
-> msinfo char(50),
-> home char(30),
-> shell char(30));
- 导入数据 记录
语法：load data infile '文件名' into table 表名 fields terminated by '分隔符'；
MariaDB [test]> load data infile '/opt/pass.txt' into table pass fields terminated by ':';
MariaDB [test]> select * from pass;
二、导出数据
将数据库中的数据导出到系统文件中
MariaDB [test]> select * from pass into outfile "/opt/passout";
ERROR 1 (HY000): Can't create/write to file '/opt/passout' (Errcode: 13)
[root@ULA tmp]# chmod 777 /opt
MariaDB [test]> select * from pass into outfile "/opt/passout";
[root@ULA tmp]# cat /opt/passout
总结：
1）导出的文件一定是不存在的
2）mysql用户对目录要有读写权限
3）导出的文件默认以\t为字段分隔符，\n为行分隔符
2.2 Mysqldump
mysqldump是mysql官方提供的逻辑备份工具
表
备份
# mysqldump -u用户 -p密码 库名 表1 [表2 ...] > /path/tables.sql
恢复
# mysql -u用户 -p密码 库名 < /path/tables.sql
库
备份
# mysqldump -u用户 -p密码 -B 库1 [库2 ...] > /path/databases.sql #-B, --databases
恢复
# mysql -u用户 -p密码 < /path/databases.sql
全备份
备份
# mysqldump -u用户 -p密码 -A > /path/all.sql # -A, --all-databases
恢复
# mysql -u用户 -p密码 < /path/all.sql
- mysqldump常用选项:
-B, --databases 做库备份时指定所要备份的库
-A, --all-databases 全库备份
-x, --lock-all-tables 给所有的表加锁
-F, --flush-logs 备份后刷新日志(启动新的日志记录)
--master-data
2.3 Xtrabackup
Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份(备份时不影响数据读写)，是商业备份工具InnoDB Hotbackup的一个很好的替代品。
mysqldump备份方式是采用的逻辑备份，其最大的缺陷是备份和恢复速度较慢，如果数据库大于50G，mysqldump备份就不太适合
2.3.1 Xtrabackup原理
备份开始时首先会开启一个后台检测进程，实时检测mysq redo的变化，一旦发现有新的日志写入，立刻将日志记入后台日志文件xtrabackup_log中，之后复制innodb的数据文件,系统表空间文件ibdatax，复制结束后，执行flush tables with readlock,然后复制.frm MYI MYD等文件，完成后执行unlock tables,最后停止xtrabackup_log,备份完成
-
在InnoDB内部会维护一个redo/undo日志文件，也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时，InnoDB会检查数据文件和事务日志，并执行两个步骤：它应用（前滚）已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。
-
Xtrabackup在启动时会记住log sequence number（LSN），并且复制所有的数据文件。复制过程需要一些时间，所以这期间如果数据文件有改动，那么将会使数据库处于一个不同的时间点。这时，xtrabackup会运行一个后台进程，用于监视事务日志，并从事务日志复制最新的修改。Xtrabackup必须持续的做这个操作，是因为事务日志是会轮转重复的写入，并且事务日志可以被重用。所以xtrabackup自启动开始，就不停的将事务日志中每个数据文件的修改都记录下来。
-
上面就是xtrabackup的备份过程。接下来是准备（prepare）过程，在这个过程中，xtrabackup使用之前复制的事务日志，对各个数据文件执行灾难恢复（就像mysql刚启动时要做的一样）。当这个过程结束后，数据库就可以做恢复还原了，这个过程在xtrabackup的编译二进制程序中实现。
-
在准备（prepare）过程结束后，InnoDB表数据已经前滚到整个备份结束的点，而不是回滚到xtrabackup刚开始时的点。这个时间点与执行FLUSH TABLES WITH READ LOCK的时间点相同，所以myisam表数据与InnoDB表数据是同步的。类似oracle的，InnoDB的prepare过程可以称为recover（恢复），myisam的数据复制过程可以称为restore（还原）。
2.3.2 Xtrabackup工具及应用
-
Xtrabackup有两个主要的工具：xtrabackup、innobackupex
1)、xtrabackup只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表
2)、 innobackupex是参考了InnoDB Hotbackup的innoback脚本修改而来的.
innobackupex是一个perl脚本封装，封装了xtrabackup。主要是为了方便的同时备份InnoDB和MyISAM引擎的表，但在处理myisam时需要加一个读锁。
还加入了一些使用的选项。如slave-info可以记录备份恢复后，作为slave需要的一些信息，根据这些信息，可以很方便的利用备份来重做slave。 -
Xtrabackup可以做什么 :
在线(热)备份整个库的InnoDB、 XtraDB表
在xtrabackup的上一次整库备份基础上做增量备份(innodb only)
以流的形式产生备份，可以直接保存到远程机器上(本机硬盘空间不足时很有用)
MySQL数据库本身提供的工具并不支持真正的增量备份，二进制日志恢复是point-in-time(时间点)的恢复而不是增量备份。
Xtrabackup工具支持对InnoDB存储引擎的增量备份，工作原理如下：
1).首先完成一个完全备份，并记录下此时检查点的LSN(Log Sequence Number)。
2).在进行增量备份时，比较表空间中每个页的LSN是否大于上次备份的LSN，如果是则备份该页，同时记录当前检查点的LSN。
首先，在logfile中找到并记录最后一个checkpoint(“last checkpoint LSN”)，然后开始从LSN的位置开始拷贝InnoDB的logfile到xtrabackup_logfile；
接着，开始拷贝全部的数据文件.ibd；拷贝结束之后，才停止拷贝logfile。
因为logfile里面记录全部的数据修改情况，所以，即时在备份过程中数据文件被修改过了，恢复时仍然能够通过解析xtrabackup_logfile保持数据的一致。
2.3.3 Xtrabackupan安装
安装 xtrabackup
-
方法1: 自己下载安装
下载地址: http://www.percona.com/downloads/XtraBackup/ -
方法2: yum安装 – 版本较老，对较新的MySQL版本不支持
1.配置epel源
2. # yum install -y xtrabackup
注意版本的兼容性
• MySQL 5.6及之前的版本需要安装 Percona XtraBackup 2.3及以上，安装指导请参见官方文档Percona XtraBackup 2.3。
• MySQL 5.7版本需要安装 Percona XtraBackup 2.4及以上，安装指导请参见官方文档Percona XtraBackup 2.4。
• MySQL 8.0版本需要安装 Percona XtraBackup 8.0，安装指导请参见官方文档Percona XtraBackup 8.0。
如: xtrabackup 2.2.4不支持mysql 5.1.73, 否则会报以下错误：
innobackupex: Error: Unsupported server version: ‘5.1.73’
2.3.4 数据库备份与还原
一、全备与还原
- 全备
[root@node1 ~]# innobackupex --user=root --password=123 /backup/ # 全备
180308 10:54:36 innobackupex: Starting the backup operation
...
xtrabackup: Transaction log of lsn (1607884) to (1607884) was copied.
180308 10:54:40 completed OK!
[root@node1 ~]# ls /backup/ # 查看备份文件
2018-03-08_10-54-36
常用选项:
–no-timestamp 不创建时间戳目录, 直接备份到指定的目录中去
–defaults-file=/path/of/file 指定配置文件.默认为/etc/my.cnf
如果报以下错误：
InnoDB: Error: log file ./ib_logfile0 is of different size 5242880 bytes
InnoDB: than specified in the .cnf file 50331648 bytes!
innobackupex: Error: The xtrabackup child process has died at /usr/bin/innobackupex line 2679.
解决办法：在my.cnf的mysqld中加入以下内容：
[mysqld]
innodb_log_file_size = 5M
- 全备还原
2.1 还原准备, 以保证数据一致性
After creating a backup, the data is not ready to be restored.
There might be uncommitted transactions to be undone or transactions in the logs to be replayed.
Doing those pending operations will make the data files consistent and it is the purpose of the prepare stage.
Once this has been done, the data is ready to be used.
[root@node1 ~]# innobackupex --apply-log /backup/2018-03-08_10-54-36/
180308 11:08:31 innobackupex: Starting the apply-log operation
...
190308 11:38:34 completed OK!
常用选项:
–use-memory=size 指定使用内存，默认为100M，增加内存可加快速度
2.2 全备还原
步骤:
1).停掉数据库
2).删除数据目录中所有数据
3).执行还原操作
[root@node1 ~]# /etc/init.d/mysqld stop
[root@node1 ~]# rm -rf /data/mysql/* #删除数据目录中所有数据
[root@node1 ~]# innobackupex --copy-back /backup/2018-03-08_10-54-36/
二、增量备份与还原
示例：创建一个全备，两个增量备份，并以此备份进行还原
- 备份
1.1 首先做全备（过程略，如已做过请跳过此步）
创建好全备后，在全备目录中的checkpoints文件中可以看到类似于如下内容:
backup_type = full-backuped
from_lsn = 0
to_lsn = 1608224
last_lsn = 1608224
compact = 0
recover_binlog_info = 0
- 服务器在有负载的情况下，to_lsn 与 last_lsn 的值可能不一样，这是正常的。
1.2 创建增量备份，使用–incremental选项，并提供BASEDIR（以哪个备份为基础进行增量备份）
[root@node1 ~]# innobackupex --user=root --password=123 --incremental /backup/ --incremental-basedir=/backup/2018-03-08_10-54-36/
#以上命令执行后会在/backup目录下生成一个新的目录,我们把它称为增量目录1
[root@node1 backup]# ls
2018-03-08_10-54-36 2018-03-08_14-59-22
#在增量目录1中我们再看checkpoints文件:
[root@node1 backup]# cat 2018-03-08_14-59-22/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1608224
to_lsn = 1609280
last_lsn = 1609280
compact = 0
recover_binlog_info = 0
1.3 创建第二个增量备份,此备份是在第二个备份的基础上备份,此时BASEDIR变成了增量目录1
[root@node1 ~]# innobackupex --user=root --password=123 --incremental /backup/ --incremental-basedir=/backup/2018-03-08_14-59-22
#此时又会生成另一个增量备份目录,我们可以看到LSN是接着上次备份的
[root@node1 backup]# cat 2019-03-08_15-54-30/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1609280
to_lsn = 1609280
last_lsn = 1610302
compact = 0
recover_binlog_info = 0
- 还原
现在主备份目录下有三个备份目录:
[root@node1 backup]# ls
2018-03-08_10-54-36 2018-03-08_14-59-22 2018-03-08_15-54-30
2018-03-08_10-54-36: 全备目录
2018-03-08_14-59-22: 第一次增量备份目录
2018-03-08_15-54-30: 第二次增量备份目录
2.1 合并，还原准备
[root@node1 ~]# innobackupex --apply-log --redo-only /backup/2019-03-08_10-54-36/
[root@node1 ~]# innobackupex --apply-log --redo-only /backup/2019-03-08_10-54-36/ --incremental-dir=/backup/2019-03-08_14-59-22/
[root@node1 ~]# innobackupex --apply-log --redo-only /backup/2019-03-08_10-54-36/ --incremental-dir=/backup/2019-03-08_15-54-30/
到这里完成，还没有结束，还有最重要一步，就是要进行全量还原。
2.2 还原
停止数据库，清空数据目录，再还原:
[root@node1 ~]# /etc/init.d/mysqld stop
[root@node1 ~]# rm -rf /data/mysql/*
[root@node1 ~]# innobackupex --copy-back /backup/2019-03-08_10-54-36/
增量备份的原理就是，把增量目录下的数据，整合到全量目录下，然后在进行，全数据量的还原。
总体来说，innobackupex速度快，支持innodb,myisam,使用起来还是很方便.
innobackup 常用参数说明
-u, --user=name
-H, --host=name
-P, --port=#
-p, --password=name
-S, --socket=name
--defaults-file
同xtrabackup的--defaults-file参数
--apply-log
对xtrabackup的--prepare参数的封装
--copy-back
做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir ；
--remote-host=HOSTNAME
通过ssh将备份数据存储到进程服务器上；
--stream=[tar]
备 份文件输出格式, tar时使用tar4ibd , 该文件可在XtarBackup binary文件中获得.如果备份时有指定--stream=tar, 则tar4ibd文件所处目录一定要在$PATH中(因为使用的是tar4ibd去压缩, 在XtraBackup的binary包中可获得该文件)。
在 使用参数stream=tar备份的时候，你的xtrabackup_logfile可能会临时放在/tmp目录下，如果你备份的时候并发写入较大的话 xtrabackup_logfile可能会很大(5G+)，很可能会撑满你的/tmp目录，可以通过参数--tmpdir指定目录来解决这个问题。
--tmpdir=DIRECTORY
当有指定--remote-host or --stream时, 事务日志临时存储的目录, 默认采用MySQL配置文件中所指定的临时目录tmpdir
--redo-only --apply-log组,
强制备份日志时只redo ,跳过rollback。这在做增量备份时非常必要。
--use-memory=#
该参数在prepare的时候使用，控制prepare时innodb实例使用的内存量
--throttle=IOS
同xtrabackup的--throttle参数
--sleep=是给ibbackup使用的，指定每备份1M数据，过程停止拷贝多少毫秒，也是为了在备份时尽量减小对正常业务的影响，具体可以查看ibbackup的手册 ；
--compress[=LEVEL]
对备份数据迚行压缩，仅支持ibbackup，xtrabackup还没有实现；
--include=REGEXP
对 xtrabackup参数--tables的封装，也支持ibbackup。备份包含的库表，例如：--include="test.*"，意思是要备份 test库中所有的表。如果需要全备份，则省略这个参数；如果需要备份test库下的2个表：test1和test2,则写 成：--include="test.test1|test.test2"。也可以使用通配符，如：--include="test.test*"。
--databases=LIST
列出需要备份的databases，如果没有指定该参数，所有包含MyISAM和InnoDB表的database都会被备份；
--uncompress
解压备份的数据文件，支持ibbackup，xtrabackup还没有实现该功能；
--slave-info,
备份从库, 加上--slave-info备份目录下会多生成一个xtrabackup_slave_info 文件, 这里会保存主日志文件以及偏移, 文件内容类似于：CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=0
作业：
公司要求对MySQL数据库每周日做全备，周一到周六做增备，为了减小对业务的影响，备份应在凌晨3：00进行。请实现此需求。
2.4 备份的误区
备份误区1: 复制就是备份
这是我们经常碰到的一个误区。复制不是备份, 当然使用RAID阵列也不是备份。
为什么这么说? 可以考虑一下, 如果意外地在生产库上执行了 DROP DATABASE, 它们是否可以帮你恢复所有的数据 ?
RAID和复制连这个简单的测试都没法通过。它们不是备份,也不是备份的替代品。只有备份才能满足备份的要求。
备份误区2: 快照就是备份
一个快照, 不论是LVM快照、ZFS快照, 还是SAN快照, 都不是实际的备份, 因为它不包含数据的完整副本。
因为快照是写时复制的, 所以它只包含实际数据和快照发生的时间点的数据之间的差异数据。
如果一个没有被修改的块在备份副本时被损坏, 那就没有该块的正常副本可以用来恢复, 并且备份副本时每个快照看到的都是相同的损坏的块。
可以使用快照来“冻结”备份时的数据, 但不要把快照当作一个备份。
134
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
