Mysql数据库数据库服务备份与恢复

1. 数据库服务备份数据方式

在企业中实现数据库服务数据备份的方式主要有两种方式：

1.1物理方式

采用拷贝物理文件数据进行备份的方式，数据库服务物理数据文件存放路径是：/var/lib/mysql

1）实现方式：

• 可以在某个特定时间点停机或停止业务访问，然后利用cp和tar命令将物理数据文件备份或打包；

• 可以在任意时间节点在不停机不停止业务时，然后利用专业的xtrabackup(Percona Xtrabackup)热备工具进行数据库数据备份；

2）应用场景：

当企业数据库服务产生的需要备份的数据量在50G以上，可以选择物理备份（xtrabackup）；

1.2 逻辑方式

可以采用以SQL语句形式把数据库的数据导出保存备份为数据库文件（xxx.sql），文件中会含有大量SQL语句信息；

1）实现方式：

• 可以在任意时间节点在不停机不停止业务时，然后利用专业的mysqldump(MDP)逻辑备份工具进行数据备份；

• 可以在任意时间节点在不停机不停止业务时，然后利用二进制日志binlog文件实现逻辑备份数据操作；

• 可以在任意时间节点在不停机不停止业务时，然后利用主从数据库架构实现备份数据信息；

2）应用场景：

当企业数据库服务产生的需要备份的数据量在50G以内，可以选择逻辑备份（mysqldump）；

2. 数据库服务备份恢复职责

• 设计数据库备份策略：备份数据周期、选择的备份工具、应用的备份方式（全备 增量..）；
• 定期数据库备份检查：核实是否存在、确认备份文件大小；
• 安排数据库恢复演练：真实确认备份的数据，是否能够准确的做数据恢复；
• 真实数据库恢复能力：在数据库服务出现异常情况时，可以将数据库服务修复，并恢复丢失的数据信息；
• 关于数据库迁移升级：可以采用Mergeing方式（主从架构）、可以单独备份数据信息到新的数据库节点做恢复（逻辑导出）；

3. 数据库服务逻辑备份实践

• 在进行数据库数据逻辑备份操作过程中，主要会运用mysqldump逻辑备份工具，可以实现本地或远程的数据备份；
• 利用mysqldump进行逻辑备份数据时，主要的备份逻辑是将建库、建表、数据插入语句信息导出，实现数据的备份操作；
• 基于mysqldump备份数据的逻辑原理，对于数据量比较小的场景（单表数据行百万以内），mysqldump备份工具做备份会更适合些；
• 在跨平台或跨版本进行数据库数据信息迁移时，mysqldump备份工具做备份也会比较适合，可以避免物理备份的兼容性问题；

说明：在一般情况下，对数据库进行数据恢复的时间耗费，大约是数据库进行数据备份的时间耗费的3~5倍。

mysqldump备份命令
mysqldump -u数据库用户 -p数据库密码 [备份参数] > /路径信息/数据库备份文件.sql

mysqldump常用参数总结
-A, --all-databases Dump all the databases. This will be same as --databases; 所有数据库进行全备
-B, --databases Dump several databases. Note the difference in usage; 实现分库备份操作

3.1数据库备份案例

3.1.1数据库数据全备

本地备份：
mysqldump -uroot -p123456 -S /tmp/mysql.sock -A >/tmp/all.sql;

恢复数据：
方式一：通过mysql直接恢复数据
mysql -uroot -p123456 -S /tmp/mysql.sock </tmp/all.sql;

方式二：先进入mysql命令界面，再通过source命令恢复数据
source </tmp/all.sql;

3.1.2数据库指定数据库备份

数据库数据分库备份:
mysqldump -uroot -S /tmp/mysql.sock -B school --set-gtid-purged=OFF>/tmp/database.sql

恢复数据：
mysql -uroot -p123456 -S /tmp/mysql.sock </tmp/database.sql;
source </tmp/database.sql;

4. 进阶备份方式（主从同步）

--master-data（--source-data）
可以记录数据备份后，最后保存结束的位置点信息（binlog文件 和 position 位置点信息）
加上以上参数进行备份，在备份文件中就会出现以上内容信息
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000022', MASTER_LOG_POS=2248; 从库恢复了数据全备数据（all02.sql）-- mysql-bin.000022 2248

--single-transaction
早期版本数据库进行mysqldump备份数据时，需要将数据库进行锁定备份，从而确保一致性（--lock-all-tables）
新版数据库中，利用以上参数信息，可以实现基于快照数据进行备份

mysqldump -uroot -pxxx -S /tmp/mysql.sock --source-data=2 --single-transaction -A >/tmp/all.sql -- 本地备份
mysqldump -uroot -pxxx -h10.0.0.51 -P3306 --source-data=2 --single-transaction -A >/10.0.0.51/all.sql -- 远程备份

-R, --routines Dump stored routines (functions and procedures)
将数据库服务存储过程进行备份，存储过程==在数据库中已经写好的功能脚本
-E, --events Dump events
将数据库的事件信息进行备份；数据库事件信息=类似定时任务
--triggers Dump triggers for each dumped table
将数据库触发器信息做备份，根据设置触发器信息可以完成一些操作动作
PS：以上参数信息，可以用于数据库迁移备份数据
mysqldump -uroot -pxxx -h10.0.0.51 -P3306 --source-data=2 --single-transaction -A -R -E --triggers>/10.0.0.51/all.sql

4.1利用第三方工具实现物理备份（xbk--物理热备 增量备份）

备份工具兼容性问题：
- 对于数据库8.0.20+版本 需要使用PXB 8.0.12+以上版本
- 对于数据库8.0.11~8.0.19 需要使用PXB 8.0版本
- 对于数据库5.x版本 需要使用PXB 2.4版本

安装xbk物理备份工具：
yum install -y percona-xtrabackup-80-8.0.32-26.1.el7.x86_64.rpm

物理备份（全量备份）
- 备份操作：
mkdir /data/backup/full -p （用于物理备份的目录一定是空目录）
xtrabackup --defaults-file=/data/3306/my.cnf --host=10.0.0.51 --port=3306 --user=root --password=123456 --backup --target-dir=/data/backup/full

- 恢复操作：
systemctl stop mysql80
mv /data/3306/data /backup
rm -rf /data/3306/data/* --确认数据目录是空目录

xtrabackup --datadir=/data/3306/data --prepare --target-dir=/data/backup/full -- 恢复数据前的准备工作 恢复内存数据
xtrabackup --datadir=/data/3306/data --copy-back --target-dir=/data/backup/full -- 将备份数据复制回数据目录 恢复磁盘数据（恢复到数据目录）
chown -R mysql. /data/3306/data/
systemctl start mysql80

补充说明：参数--prepare有什么作用
严谨解释：prepare和事务提交的过程--两阶段提交
通俗解释：xbk备份工具，可以备份数据库所有数据（之前的历史数据备份-磁盘数据 还有备份过程中的增量数据也会备份-内存中数据）

物理备份（增量备份）
备份操作：
第一次备份：（全量备份）
xtrabackup --defaults-file=/data/3306/my.cnf --host=10.0.0.51 --user=root --password=123456 --port=3306 --backup --parallel=4 --target-dir=/data/backup/full

4.2Xtrabackup实现增量备份

xtrabackup物理备份数据时，实现增量备份原理分析：

• 增量备份的实质是，基于上一次备份LSN变化过的数据页，进行相应的备份操作，从而可以不断实现增量备份操作；

• 在备份同时产生的新的变更，会将redo日志信息备份；

• 第一次增量备份时依赖于全量备份的，将来的恢复操作也要合并到全备中，再进行统一恢复；

第二次备份：
create database xiaoO; -- 模拟生成增量数据
mkdir /data/backup/add01/ -p
xtrabackup --defaults-file=/data/3306/my.cnf --host=10.0.0.51 --user=root --password=123456 --port=3306 --backup --parallel=4 --target-dir=/data/backup/add01 --incremental-basedir=/data/backup/full

第三次备份：
create database xiaoOO;
mkdir /data/backup/add02/ -p
xtrabackup --defaults-file=/data/3306/my.cnf --host=10.0.0.51 --user=root --password=123456 --port=3306 --backup --parallel=4 --target-dir=/data/backup/add02 --incremental-basedir=/data/backup/add01

恢复操作：
systemctl stop mysql80
rm -rf /data/3306/data/*

xtrabackup --datadir=/data/3306/data --prepare --apply-log-only --target-dir=/data/backup/full -- 加载全量备份过程内存中数据（磁盘数据）
xtrabackup --datadir=/data/3306/data --prepare --apply-log-only --target-dir=/data/backup/full --incremental-dir=/data/backup/add01 -- 将第一次增量备份过程的内存数据和全量的内存数据做整合（磁盘数据）
xtrabackup --datadir=/data/3306/data --prepare --apply-log-only --target-dir=/data/backup/full --incremental-dir=/data/backup/add02 -- 将第二次增量备份过程的内存数据和全量的内存数据做整合（磁盘数据）
xtrabackup --datadir=/data/3306/data --prepare --target-dir=/data/backup/full -- 将所有内存部分数据做恢复
xtrabackup --datadir=/data/3306/data --copy-back --target-dir=/data/backup/full
chown -R mysql. /data/3306/data/
systemctl start mysql80