1. 备份恢复概述
1.1 为什么要备份
灾难恢复：硬件、软件故障、自然灾害、黑客攻击、误操作等数据丢失场景
数据迁移：搭建主从，更换新服务器等
1.2 备份类型
按数据的完整性可以分为完全备份和部分备份：
- 完全备份：备份整个数据库的所有内容。
- 部分备份：只备份其中一部分数据，例如某个库，某个表。
- 增量备份：仅备份上次备份以来变化的数据，优点是数据量小备份快，缺点是还原次数多比较麻烦。
- 差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单
按数据是否可以写入分为冷备，温备，热备：
- 冷备：数据备份期间读、写操作均不可进行，数据库停止服务。
- 温备：给要备份的数据范围加读锁，读操作可执行；但写操作不可执行。
- 热备：数据备份期间读、写操作均可执行。
注意：MyISAM引擎只支持冷备和温备，InnoDB都支持。
按数据的备份方式可以分为物理备份和逻辑备份：
- 物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快。
逻辑备份：从数据库中“导出”数据的SQL文件而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度。
1.3 常用备份方式
cp，tar，rsync等复制归档工具：物理备份方式，适用于所有引擎，只支持冷备，完全和部分备份。推荐使用rsync -a，可以保留权限和属性。
- LVM的快照：先加读锁，做快照后解锁，几乎热备；借助文件系统工具进行备份，备份完成后记得删除快照。
- mysqldump：逻辑备份工具，适用所有存储引擎，对MyISAM存储引擎进行温备；支持完全或部 分备份；对InnoDB存储引擎支持热备，结合binlog可以实现增量备份，优点是MySQL自带工具，数据量较小的时候使用起来轻量便捷，缺点是当数据量较大时非常占用机器资源，耗时也较长。
- mysqlbackup：只支持InnoDB的物理热备份工具，备份时占用资源小适用于数据量较大的场景。
xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，需要自行安装此工具，支持完全备份、增量备份，相比较mysqldump来说备份速度较快，适用于数据量较大的备份。
1.4 备份策略的制定
一般来说MySQL数据备份需要考虑以下几个因素：
哪些数据需要备份
- 什么时间进行备份
- 备份所需要的时长
- 多久备份一次
- 选择什么备份方式
- 备份数据恢复演练
2. 备份还原实战案例
2.1 使用 rsync 命令备份
此种方式支持冷备，因此需要提前停止数据库的服务。 ```bash选择需要备份的数据备份到远程服务器
一般有数据库目录，log目录和配置文件等
[root@centos81 ~]#rsync -a /var/lib/mysql 172.16.100.70:/data/backup/ [root@centos81 ~]#rsync -a /data/binlog 172.16.100.70:/data/backup/ [root@centos81 ~]#rsync -a /etc/my.cnf 172.16.100.70:/data/backup/
模拟数据库损坏，在新机器上进行数据还原
将备份数据拷贝到当前新机器上
[root@centos82 ~]#rsync -a 172.16.100.70:/data/backup/ .
移动数据到对应目录下
[root@centos82 ~]#mv /data/backup/mysql /var/lib/ [root@centos82 ~]#mv /data/backup/binlog /data/ [root@centos82 ~]#mv /data/backup/my.cnf /etc/
启动数据库查看还原情况
[root@centos82 ~]#systemctl enable —now mysqld
如果原mysql服务器器存放binlog的磁盘没有损坏，还可以将binlog复制过来进一步还原。
[root@centos81 ~]#scp /data/binlog/mysql-bin.000003 172.16.100.71:/root/ [root@centos82 ~]#mysqlbinlog mysql-bin.000003 > binlog003.sql
进入数据库先关闭二进制日志功能，导入binlog数据后重新再开启。
[root@centos82 ~]#mysql -uroot -p’123456’ mysql> set sql_log_bin=off; mysql> source /root/binlog003.sql mysql> set sql_log_bin=on;
<a name="MNWqc"></a>## 2.2 mysqldump 备份<a name="ZOmAY"></a>### 2.2.1 完全备份```bashmysqldump -uroot -p123456 -A -F --single-transaction --master-data=2 | gzip > /backup/all-`date +%F`.sql.gz
2.2.2 增量备份
# 查看二进制日志备份点grep '^-- CHANGE MASTER TO' /data/backup/all-data-2020-10-15.sql-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1285;mysqlbinlog /data/binlog/mysql-bin.000002 --start-position=1285 > inc.sqlmysqlbinlog /data/binlog/mysql-bin.000003 >> inc.sql
2.2.3 分库备份
# 方法1：使用for循环for db in `mysql -e 'show databases' | egrep -v '(Database|information_schema|performance_schema|sys)'`;do mysqldump -B $db | gzip > /data/$db.sql.gz; done# 方法2：使用sedmysql -e 'show databases' | sed -rn '/(Database|information_schema|performance_schema|sys)/!s#.*#mysqldump -B & | gzip > /data/&.sql.gz#p' | bash# 方法3：使用awkmysql -e 'show databases' | awk '!/(Database|information_schema|performance_schema|sys)/{print "mysqldump -B",$0,"| gzip > /data/"$0".sql.gz"}' | bash
2.2.4 单表备份
mysqldump [OPTIONS] database [tables]
#支持指定数据库和指定多表的备份，但数据库本身定 义不备份mysqldump -uroot -p'123.com' hellodb teachers > teachers.sql
2.3 Xtrabackup 备份
2.3.1 添加 yum 源安装
[root@mysql ~]# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm[root@mysql ~]# yum list | grep xtrabackuppercona-xtrabackup.x86_64 2.3.10-1.el7 percona-release-x86_64percona-xtrabackup-22.x86_64 2.2.13-1.el7 percona-release-x86_64percona-xtrabackup-22-debuginfo.x86_64 2.2.13-1.el7 percona-release-x86_64percona-xtrabackup-24.x86_64 2.4.22-1.el7 percona-release-x86_64percona-xtrabackup-24-debuginfo.x86_64 2.4.22-1.el7 percona-release-x86_64percona-xtrabackup-80.x86_64 8.0.23-16.1.el7 percona-release-x86_64percona-xtrabackup-80-debuginfo.x86_64 8.0.23-16.1.el7 percona-release-x86_64percona-xtrabackup-debuginfo.x86_64 2.3.10-1.el7 percona-release-x86_64percona-xtrabackup-test.x86_64 2.3.10-1.el7 percona-release-x86_64percona-xtrabackup-test-22.x86_64 2.2.13-1.el7 percona-release-x86_64percona-xtrabackup-test-24.x86_64 2.4.22-1.el7 percona-release-x86_64percona-xtrabackup-test-80.x86_64 8.0.23-16.1.el7 percona-release-x86_64...[root@mysql ~]# yum -y install percona-xtrabackup-24.x86_64
2.3.2 rpm 安装
注意：rpm 安装需要自行解决依赖问题
yum -y install libev.x86_64 0:4.15-7.el7yum -y install ./percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm
2.3.3 完全备份
# 数据库完全备份xtrabackup -uroot -p744123 --backup --target-dir=/backup/# 备份到远程服务器scp -r /backup 172.16.100.75:/data/# 将备份好的数据拷贝过来scp -r 172.16.100.75:/data/ /data/# 数据恢复预准备，解压并回滚数据中未完成的事务xtrabackup --prepare --target-dir=/data/backup# 将预准备好的数据拷贝回数据库目录## 注意 mysql 服务需要关闭且数据目录下不能有任何其他文件xtrabackup --copy-back --target-dir=/data/backup# 修改用户组chown -R mysql.mysql /data/mysql/# 启动mysql服务，查看是否还原完成service mysqld startStarting MySQL.Logging to '/data/mysql/mysql.log'. SUCCESS!# 启动mysql服务的时候可能会出现以下报错service mysqld startStarting MySQL...2021-05-08T07:14:26.772499Z mysqld_safe error: log-error set to '/data/log/mysqld.log', however file don't exists. Create writable for user 'mysql'.ERROR! The server quit without updating PID file (/data/mysql/172-16-203-117.pid).# 可以手动创建文件解决touch /data/log/mysqld.logchown mysql.mysql /data/log/mysqld.log
2.3.4 增量备份
# 数据库完全备份[root@centos7 ~]# xtrabackup -uroot -p744123 --backup --target-dir=/backup/base/# 插入两条数据，模拟数据库第一次发生变化[root@centos7 mysql]#mysql -uroot -p744123 -e "insert into hellodb.teachers (name,age,gender) values ('zhangsan',33,'F'),('lisi',52,'M')"# 查看确认插入数据成功root@localhost [hellodb]>select * from teachers;+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | Miejue Shitai | 77 | F || 4 | Lin Chaoying | 93 | F || 5 | wang | 22 | M || 6 | mage | 62 | F || 7 | zhangsan | 33 | F || 8 | lisi | 52 | M |+-----+---------------+-----+--------+8 rows in set (0.00 sec)# 第一次增量备份[root@centos7 mysql]# xtrabackup -uroot -p744123 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base[root@centos7 mysql]#tree -d -L 1 /backup//backup/├── base└── inc1[root@centos7 mysql]#du -sh /backup/*39M /backup/base3.2M /backup/inc1# 再次插入两条数据，模拟数据库第二次发生变化[root@centos7 mysql]#mysql -uroot -p744123 -e "insert into hellodb.teachers (name,age,gender) values ('xiaoming',35,'M'),('xiaohong',27,'F')"# 查看确认插入数据成功root@localhost [hellodb]>select * from teachers;+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | Miejue Shitai | 77 | F || 4 | Lin Chaoying | 93 | F || 5 | wang | 22 | M || 6 | mage | 62 | F || 7 | zhangsan | 33 | F || 8 | lisi | 52 | M || 9 | xiaoming | 35 | M || 10 | xiaohong | 27 | F |+-----+---------------+-----+--------+10 rows in set (0.00 sec)# 第二次增量备份[root@centos7 mysql]#xtrabackup -uroot -p744123 --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1[root@centos7 backup]#tree -d -L 1 /backup//backup/├── base├── inc1└── inc2[root@centos7 backup]#du -sh /backup/*39M /backup/base3.2M /backup/inc13.2M /backup/inc2# 备份到远程服务器[root@centos7 ~]# scp -r /backup/* 10.0.0.84:/data/# 还原数据库# 将远程服务器的数据拷贝回来[root@centos7 ~]# scp -r 10.0.0.84:/data/backup/ .# 数据恢复前预准备，最后一次合并备份前需要加--apply-log-only，阻止事务回滚[root@centos7 backup]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base# 合并第一次增量备份[root@centos7 backup]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1# 合并第二次增量备份，由于这里是最后一次合并备份，所以不需要加--apply-log-only，使最后一次备份数据中未完成的事务进行回滚，让数据统一。另外，如果有binlog日志的话，可以继续使用binlog日志将数据库还原到最新状态。[root@centos7 backup]#xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2[root@centos7 backup]#du -sh /backup/*155M /backup/base12M /backup/inc112M /backup/inc2# 将准备好的数据复制回数据库，复制前确保数据库目录为空[root@centos7 backup]#ll /data/mysql/total 0[root@centos7 mysql]#xtrabackup --copy-back --target-dir=/backup/base# 修改用户组[root@centos7 mysql]#chown -R mysql.mysql /data/mysql/# 启动mysql服务[root@centos7 mysql]#service mysqld startStarting MySQL.Logging to '/data/mysql/mysql.log'.SUCCESS!# 确认数据无误，还原完成root@localhost [hellodb]>select * from teachers;+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | Miejue Shitai | 77 | F || 4 | Lin Chaoying | 93 | F || 5 | wang | 22 | M || 6 | mage | 62 | F || 7 | zhangsan | 33 | F || 8 | lisi | 52 | M || 9 | xiaoming | 35 | M || 10 | xiaohong | 27 | F |+-----+---------------+-----+--------+10 rows in set (0.00 sec)
2.3.5 压缩备份
# --compress压缩备份xtrabackup -uroot -p744123 --backup --compress --target-dir=/backup/# --compress-threads 压缩备份多线程加速xtrabackup -uroot -p744123 --backup --compress --compress-threads=4 --target-dir=/backup/# 解压备份数据，需要安装qpress，可以从 Percona 软件仓库安装。xtrabackup --decompress --target-dir=/backup/# 预准备备份数据xtrabackup --prepare --target-dir=/backup/# 恢复数据xtrabackup --copy-back --target-dir=/backup/# 修改权限chown -R mysql:mysql /var/lib/mysql# 启动服务service mysqld start
2.3.6 从完全备份中恢复某单个表
总体思路：
- 预准备完全备份。
- 从预准备完全备份后的目录下，找到被删除的表的表结构
.frm文件和表空间.ibd文件。 - 使用
mysqlfrm命令查看表结构，并在数据库中重新创建表。 - 丢弃表空间。
- 从预准备完全备份后的目录下复制表空间
.ibd文件到数据库对应目录下。 - 改变文件属性。
- 重新导入表空间。
- 校验数据一致性。
# 先做一次完全备份[root@c7-01 ~]# xtrabackup -uroot -p744123 --backup --target-dir=/backup/# 模拟表被删除root@localhost [hellodb]>drop table coc;Query OK, 0 rows affected (0.01 sec)# 预准备完全备份[root@c7-01 ~]# xtrabackup --prepare --target-dir=/backup# 表结构 .frm 文件和表空间 .ibd 文件[root@c7-01 ~]# ll /backup/hellodb/coc*-rw-r----- 1 root root 8630 May 19 13:48 /backup/hellodb/coc.frm-rw-r----- 1 root root 98304 May 19 13:48 /backup/hellodb/coc.ibd# 安装 MySQL Utilities# MySQL Utilities安装依赖于MySQL Connector/Python包，centos7 yum源中自带的版本较低，需要去官方手动下载2.0以上版本[root@c7-01 ~]# rpm -ivh mysql-connector-python-2.1.7-1.el7.x86_64.rpm[root@c7-01 ~]# rpm -ivh mysql-utilities-1.6.5-1.el7.noarch.rpm# 查看表结构[root@c7-01 ~]# mysqlfrm --diagnostic /backup/hellodb/students.frm# WARNING: Cannot generate character set or collation names without the --server option.# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it maynot identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.# Reading .frm file for /backup/hellodb/coc.frm:# The .frm file is a TABLE.# CREATE TABLE Statement:CREATE TABLE `hellodb`.`coc` (`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,`ClassID` tinyint(3) unsigned NOT NULL,`CourseID` smallint(5) unsigned DEFAULT NULL,PRIMARY KEY `PRIMARY` (`ID`)) ENGINE=InnoDB;#...done.# 创建表root@localhost [hellodb]>CREATE TABLE `hellodb`.`coc` (-> `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,-> `ClassID` tinyint(3) unsigned NOT NULL,-> `CourseID` smallint(5) unsigned DEFAULT NULL,-> PRIMARY KEY `PRIMARY` (`ID`)-> ) ENGINE=InnoDB;Query OK, 0 rows affected (0.01 sec)# 删除表空间root@localhost [hellodb]>alter table coc discard tablespace;Query OK, 0 rows affected (0.00 sec)# 复制完全备份目录下的表空间文件到数据库目录下[root@c7-01 ~]# cp /backup/hellodb/coc.ibd /data/mysql/hellodb/# 修改权限[root@c7-01 ~]# chown mysql.mysql /data/mysql/hellodb/coc.ibd# 重新导入表空间root@localhost [hellodb]>alter table coc import tablespace;Query OK, 0 rows affected, 1 warning (0.01 sec)# 查看校验数据root@localhost [hellodb]>select * from coc;+----+---------+----------+| ID | ClassID | CourseID |+----+---------+----------+| 1 | 1 | 2 || 2 | 1 | 5 || 3 | 2 | 2 || 4 | 2 | 6 |+----+---------+----------+4 rows in set (0.00 sec)
若有收获，就点个赞吧
0 人点赞
