mysql5.7/8.0备份工具

一、MySQL备份介绍

按照备份后产生的副本文件是否可以编辑，可分为逻辑备份、物理备份。
1、物理备份：物理备份产生的数据副本都是二进制文件，常常不可编辑，例如数据库的二进制日志。
适用： 物理备份适用于大数据量的备份，比如你有百G级、TB级的数据，那你就适合使用物理备份；
备份方法： 
1.文件系统命令：cp,scp,tar,rsync; 
2.MySQL相关备份软件；系统快照snapshot；

2.逻辑备份（将数据导出至文本文件中）：
逻辑备份就是把数据库的结构定义语句，数据内容的插入语句，全部存储下来。然后恢复的时候，在另一个mysql服务器执行这些语句，就可以创建另一个与之前一样的数据库了。
备份后，数据库管理员通常可以直接查看和编辑副本文件中的内容。

适用：这种比较适合数据量少的数据库。
备份方法： 
1.如，使用mysqldump等程序可以把对远程/本地数据库进行逻辑备份. 
2.SELECT ..... INTO OUTFILE可以把数据进行逻辑备份，但备份文件只能存储在mysql-server的机器上；

二、mysqldump(单线程)与mysqlpump(多线程)备份工具简介

mysql服务自带的备份工具，是一种逻辑备份工具，它支持一下方式备份： 
完全、部分备份； 
InnoDB：热备； 
MyISAM：温备；
2）mydumper开源，是mysqldump的一个衍生，速度在两者之间。
3）cp/tar 

物理拷贝
4）innobackup[收费] / xtrabackup[免费]
由Percona提供，开源工具，支持对InnoDB做热备，物理备份工具； 
完全备份、部分备份； 完全备份、增量备份； 完全备份、差异备份；
5）官方mysql enterprise backup备份软件，收费。

MySQL备份方案选型：

MySQL一般情况下有几种策略。
策略一：
直接拷贝数据库文件（文件系统备份工具 cp）（适合小型数据库，是最可靠的）
策略二：
mysqldump备份数据库（完全备份+增加备份，速度相对较慢，适合中小型数据库）
策略三：
Xtrabackup&&lvs快照从物理角度实现几乎热备的完全备份，配合二进制日志备份
实现增量备份，速度快适合比较烦忙的数据库

MySQL恢复数据验证

备份文件有了之后还需要对其定期的进行恢复测试，不然可能是白忙一场。
1 恢复时间及地点
每周进行一次恢复测试，主要在测试机上进行
2 恢复方式
模拟某个时间点主机数据全部丢失，要求恢复到丢失时间点的所有数据，先进行全备
恢复，然后根据binlog恢复到最近时间点

 MySQL备份命令

# mysql 5.7
mysqldump -u root -proot --single-transaction --master-data=2 --routines --flush-logs --database webgame > webgame.sql

--single-transaction
该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。
本选项和–lock-tables 选项是互斥的，因为LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用–quick 选项

--master-data
该选项将binlog的位置和文件名追加到输出文件中。如果为1，将会输出CHANGE MASTER 命令；如果为2，输出的CHANGE MASTER命令前添加注释信息。
该选项将打开–lock-all-tables 选项，除非–single-transaction也被指定（在这种情况下，全局读锁在开始导出时获得很短的时间；其他内容参考下面的–single-transaction选项）。
该选项自动关闭–lock-tables选项

# 备份整个库
mysqlpump -u root -proot --single-transaction --default-parallelism=2 --default-character-set=utf8mb4 --all-database webgame > webgame.sql

--default-parallelism=N
设置并行导出的并发度，与 single-transaction 冲突

--single-transaction
创建一个单独的事务来导出所有的表

当这两个参数同时启用的时候，mysqlpump 实际上还是在一个一个表的导出。single-transaction的优先级会高于default-parallelism

优缺点
优点：
并行备份数据库和数据库中的对象，比 mysqldump 更高效。
更好的控制数据库和数据库对象（表，存储过程，用户帐户）的备份。
备份进度可视化。

缺点：   
只能并行到表级别，如果有一个表数据量特别大那么会存在非常严重的短板效应。
导出的数据保存在一个文件中，导入仍旧是单线程，效率较低。
无法获取当前备份对应的binlog位置。

MySQLdumper备份

# mysql 8.0 使用mysqldumper
下载地址：https://launchpad.net/mydumper

依赖包：
yum install -y glib2-devel mysql-devel zlib-devel pcre-devel openssl-devel cmake make

tar -xzvf mysqldumper-0.9.1.tar.gz
cd mysqldumper-0.9.1
cmake . && make && make install
# 备份
mysqldumper -u root -proot -h 192.168.10.10 -P 3306 -B webgame -o /data/backup/webgame
# 恢复
myloader -u root -proot -o -d /data/backup/webgame

xtrabackup mysql8.0中备份

# 备份
xtrabackup --defaults-file=/etc/my.cnf --datadir=/usr/local/mysql/data -S /var/lib/mysql/mysql.sock --user=root --password=root --backup --target-dir=/data/backup/webgame/fullbacup20250113 --parallel=2

--parallel=2  并行

# 恢复步骤
备份(--backup)==>应用日志(--apply-log)==>恢复(--copy-back)
1.停止数据库，并将原来的data改名为data_bak
2.完整的备份：--prepare(/data/backup/webgame/fullbacup20250113)
执行之后，xtrabackup_checkpoints中的backup_type=full-backuped变为backup_type=full-prepared
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/backup/webgame/fullbacup20250113 --parallel=2
# 如果有内存限制，添加限制内存参数：--user-memory=1G
3.copy执行恢复数据操作
rsync -avrP /data/backup/webgame/fullbacup20250113/* --exclude=xtrabackup_* /usr/local/mysql/data
4.修改目录权限
chown -Rmysql.mysql /usr/local/mysql/data
5.启动数据库

 MySQLbackup备份MySQL 8.0，本工具需要收费，在企业版中包含

tar -xzvf mysql-commercial-backup-8.0.30-el7-x86_64.tar.gz
mv mysql-commercial-backup-8.0.30-el7-x86_64 /usr/local/mysql/mysqlbackup
echo "export PATH=$PATH:/usr/local/mysql/mysqlbackup" >>/etc/profile
source /etc/profile

#创建备份目录：
mkdir /data/backup/webgame/{mysql-db,mysql-tmp,restore-tmp,mysql-binlog} && chown -R mysql.mysql /data/backup/webgame && chmod -R 777 /data/backup/webgame
#创建备份用户权限，根据情况自选
mysql -uroot -prootroot
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'backup';
GRANT RELOAD ON *.* TO 'backup'@'localhost';GRANT CREATE, INSERT, DROP,UPDATE ON mysql.backup_progress TO 'backup'@'localhost';
GRANT CREATE, INSERT, SELECT, DROP, UPDATE ON mysql.backup_history TO 'backup'@'localhost;
GRANT REPLICATION CLIENT ON *.*TO 'backup'@'localhost';
GRANT SUPER ON*.*TO 'backup'@'localhost';
GRANT PROCESS ON*.*TO 'backup'@'localhost;
GRANT ALTER ON mysql.backup_history TO 'backup'@'localhost';
FLUSH PRIVILEGES;
#transportable tablespaces
#GRANT LOCK TABLES, SELECT, CREATE, DROP, FILE ON *.*TO 'backup'@'Iocalhost';
#GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_sbt history TO 'backup'@'localhost;

# 备份数据库，备份出来的文件为.mbi文件
mysgllbackup --defaults-file=/etc/my.cnf --user=backup --password=backup-backup-image=/data/backup/webgame/mysql-db/fullbackup date +%Y%m%d.mbi--with-timestamp backup-to-image --backup-dir=/data/backup/webgame/mysql-tmp

# 恢复数据库
mysqlbackup --defaults-file=/etc/my.cnf --user=backup --password=backup--datadir-/usr/local/mysql/data --backup-image-/data/backup/webgame/mysql-db/fullbackup20250113.mbi--backup-dir=/data/backup/webgame/restore-tmp copy-back-and-apply-log

copy-back-and-apply-log:拷然后再恢复增备的log:

如果报错:
180504 10:07:05 MAIN ERROR: 0is not a valid innodb_checksum_algorithm.
180504 10:07:05 MAIN ERROR: Invalid innodb_checksum_algorithm name found.
mysqlbackup failed with errors!

解决方法:
my.cnf文件中:innodb_checksum_algorithm=0改为:innodb_checksum_algorithm = crc32

# 权限恢复chown -R mysql.mysql /usr/lcoal/mysql/data