MySQL Enterprise Backup备份工具介绍及及使用
备份工具介绍：
Mysqlbackup是ORACLE公司也提供了针对企业的备份软件MySQL Enterprise Backup简称，是MySQL服务器的备份实用程序。它是一个多平台，高性能的工具，具有丰富的功能，例如 “热”（在线）备份，增量和差异备份，选择性备份和还原，备份加密和压缩以及许多其他有价值的功能特征。经过优化以用于InnoDB表，MySQL Enterprise Backup能够备份和还原MySQL支持的任何存储引擎创建的各种表。它的读取和写入过程（在独立的，多个线程中执行）的并行性及其块级并行性（不同的线程可以在单个文件中读取，处理或写入不同的块），从而可以快速完成备份和还原过程，并且与logical backup使用mysqldump之类的工具 相比，通常可以显着提高性能 。MySQL Enterprise Backup是用于维护和保护MySQL数据以及在发生事故或灾难时快速可靠地进行恢复的宝贵工具。它是MySQL企业版的一部分，可根据商业许可向订户提供。
Mysqlbackup工具版本建议与MySQL Server版本一致。
例如:
对于 MySQL 8.0.27, 使用 MySQL Enterprise Backup 8.0.27。
对于 MySQL 5.7，使用 MySQL Enterprise Backup 4.1。
对于 MySQL 5.6，使用 MySQL Enterprise Backup 3.12。
对于MyISAM引擎，在备份时存在锁表现象。需添加--no-locking参数。
备份方案
根据不同系统高可用架构及RTO/RPO，可在操作系统中配置crontab的全备及增量备份时间。
需在备份主机节点上挂载备份目录。具体大小根据系统数据量进行分配。
例:
00 4 \* \* \* /app/bakcup/mysql\_fullback.sh
30 \*/2 \* \* \* /app/bakcup/mysql\_incremental.sh
00 7 \* \* \* /app/bakcup/mysql\_delete.sh
备份配置
4.1版本:
CREATE USER 'mysqlbackup'@'localhost' IDENTIFIED BY 'PASSWD'; << 此处替换密码
GRANT RELOAD, SUPER, PROCESS ON \*.\* TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup\_progress TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, SELECT, DROP, UPDATE, ALTER ON mysql.backup\_history
TO 'mysqlbackup'@'localhost';
GRANT REPLICATION CLIENT ON \*.\* TO 'mysqlbackup'@'localhost';
GRANT SELECT ON performance\_schema.replication\_group\_members TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP ON mysql.backup\_history\_old TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, ALTER ON mysql.backup\_history\_new TO 'mysqlbackup'@'localhost';
GRANT LOCK TABLES, SELECT, CREATE, DROP, FILE ON \*.\* TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup\_sbt\_history TO 'mysqlbackup'@'localhost';
8.0版本:
CREATE USER 'mysqlbackup'@'localhost' IDENTIFIED BY 'PASSWD'; << 此处替换密码
CREATE USER 'mysqlbackup'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, BACKUP\_ADMIN, RELOAD, PROCESS, SUPER, REPLICATION CLIENT ON \*.\*
TO \`mysqlbackup\`@\`localhost\`;
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup\_progress TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, UPDATE, SELECT, ALTER ON mysql.backup\_history
TO 'mysqlbackup'@'localhost';
GRANT LOCK TABLES, CREATE, DROP, FILE, INSERT, ALTER ON \*.\* TO 'mysqlbackup'@'localhost';
GRANT CREATE, DROP, UPDATE ON mysql.backup\_sbt\_history TO 'mysqlbackup'@'localhost';
GRANT ENCRYPTION\_KEY\_ADMIN ON \*.\* TO 'mysqlbackup'@'localhost';
GRANT INNODB\_REDO\_LOG\_ARCHIVE ON \*.\* TO 'mysqlbackup'@'localhost';
GRANT ALTER ON mysql.backup\_progress TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP ON mysql.backup\_progress\_old TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, ALTER ON mysql.backup\_progress\_new TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP ON mysql.backup\_history\_old TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, ALTER ON mysql.backup\_history\_new TO 'mysqlbackup'@'localhost';
GRANT ALTER ON mysql.backup\_sbt\_history TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP ON mysql.backup\_sbt\_history\_old TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, ALTER ON mysql.backup\_sbt\_history\_new TO 'mysqlbackup'@'localhost';
mysql\_config\_editor set --login-path=backup --user=mysqlbackup --password --socket=/tmp/mysql\_3306.sock --host=localhost
$ mysql\_config\_editor set --login-path=backup --user=mysqlbackup --password --socket=/tmp/mysql\_3306.sock --host=localhost
Enter password: \*\*\*\*\*\* <<此处根据提示输入mysqlbackup备份用户密码
$ mysql\_config\_editor print --all
\[backup\]
user = "mysqlbackup"
password = \*\*\*\*\*
host = "localhost"
socket = "/tmp/mysql\_3306.sock"
mkdir -p /app/bakcup/log
mkdir -p /app/bakcup/back\_images/{full,incremental}
chown -R mysql.mysql /app/backup
#!/bin/bash
# User specific environment and startup programs
source /etc/profile
################### Declare environment variables #########
record\_log=/app/bakcup/log
log\_name=fullback\_record\_\`date '+%Y-%m-%d-%H-%M-%S'\`.log
defaults\_file=/app/mysql/mysql3306/etc/my.cnf
backup\_dir=/app/bakcup/back\_images/full
login\_path=backup
echo "--------------------Full Backup Starting------------------" >> $record\_log/$log\_name
date >> $record\_log/$log\_name
mysqlbackup --defaults-file=$defaults\_file \\
--login-path=$login\_path \\
--with-timestamp \\
--backup-dir=$backup\_dir \\
--backup-image=fullback\_\`date +%Y%m%d\`.mbi \\
--compress-level=1 \\
backup-to-image >>$record\_log/$log\_name 2>&1
pkill mysqlbackup
date >> $record\_log/$log\_name
echo "--------------------Full Backup Ended------------------" >> $record\_log/$log\_name
#!/bin/bash
# User specific environment and startup programs
source /etc/profile
################### Declare environment variables #########
record\_log=/app/bakcup/log
log\_name=incremental\_record\_\`date '+%Y-%m-%d-%H-%M-%S'\`.log
defaults\_file=/app/mysql/mysql3306/etc/my.cnf
backup\_dir=/app/bakcup/back\_images/incremental
full\_dir=/app/bakcup/back\_images/full
login\_path=backup
echo "--------------------incremental Backup Starting------------------" >> $record\_log/$log\_name
date >> $record\_log/$log\_name
mysqlbackup --login-path=$login\_path \\
--defaults-file=$defaults\_file \\
--with-timestamp \\
--incremental \\
--incremental-base=history:last\_backup \\
--backup-dir=$backup\_dir \\
--backup-image=incremental\_\`date +%Y%m%d%H%M%S\`.mbi \\
--compress-level=1 \\
backup-to-image >> $record\_log/$log\_name 2>&1
pkill mysqlbackup
date >> $record\_log/$log\_name
echo "--------------------incremental Backup Ended------------------" >> $record\_log/$log\_name.
########## delete the images && metadata\_infor && log from 7 days ago #############
fullimages\_dir=/app/bakcup/back\_images/full
/bin/find $fullimages\_dir -type d -name "202\*" -mtime +7 -exec rm -rf {} \\;
incimages\_dir=/app/bakcup/back\_images/incremental
/bin/find $incimages\_dir -type d -name "202\*" -mtime +8 -exec rm -rf {} \\;
log\_dir=/app/bakcup/log
find $log\_dir -type f -name "\*\_record\_\*" -mtime +7 -exec rm -rf {} \\;
备份除 “ mysql ”和“ performance\_schema ” 数据库中的表之外的所有表
mysqlbackup --defaults-file=/app/mysql/mysql3306/etc/my.cnf \\
--login-path=backup \\
--with-timestamp \\
--backup-dir=/app/bakcup/back\_images/table \\
--backup-image=test2.mbi \\
--exclude-tables="^(mysql|performance\_schema)\\." \\
backup-to-image >> /app/bakcup/log/table\_record\_\`date '+%Y-%m-%d-%H-%M-%S'\`.log 2>&1
备份“ fact\_sale ”的表
mysqlbackup --defaults-file=/app/mysql/mysql3306/etc/my.cnf \\
--login-path=backup \\
--with-timestamp \\
--backup-dir=/app/bakcup/back\_images/table \\
--backup-image=test1.mbi \\
--include-tables="^test\\fact\_sale" \\
backup-to-image >> /app/bakcup/log/table\_record\_\`date '+%Y-%m-%d-%H-%M-%S'\`.log 2>&1
备份“ sales ” 数据库中的所有表，但排除名称为 “ hardware ”的表
mysqlbackup --defaults-file=/app/mysql/mysql3306/etc/my.cnf \\
--login-path=backup \\
--with-timestamp \\
--backup-dir=/app/bakcup/back\_images/table \\
--backup-image=test3.mbi \\
--include-tables="^sales\\."
--exclude-tables="^sales\\.hardware$" \\
backup-to-image >> /app/bakcup/log/table\_record\_\`date '+%Y-%m-%d-%H-%M-%S'\`.log 2>&1
Mysqlbackup参数:
–backup-image:生成的备份image名称
–backup-dir:生成的备份image所在目录
–show-progress:显示备份进度\[可选项\]
–compress:对备份image进行压缩节省空间\[可选项\]
–with-timestamp:在backup-dir目录下生成’年-月-日-时-分-秒’的目录存储备份image
backup-to-image：声明这是备份为image的备份
--no-locking 参数 ---数据库中存储在MyISAM 存储引擎时添加该参数，只有InnoDB引擎时不建议添加该参数
–datadir: mysql server的datadir目录，就是要还原到这里
–backup-dir:生成的备份datafile所在目录
–show-progress:显示还原进度\[可选项\]
–uncompress\[可选项\]
–apply-log: 把备份期间的redo log贴回datafile
–copy-back：把datafile复制回datadir目录
–copy-back-and-apply-log:把备份期间的redolog贴回datafile再把datafile复制回datadir目录
mysqlbackup 8.0.21版本开始可以识别备份是否为compress，如果是，那么它在还原时会自动uncompress，不需要显示告诉它，低于8.0.21版本备份时指定compress参数，恢复时需指定uncompress。
- 备份验证及查看命令
mysqlbackup --backup-image= /app/bakcup/back\_images/full/2023-01-11\_16-51-07/ fullback\_20230111.mbi list-image ---查看备份列表
mysqlbackup --backup-image=/app/bakcup/back\_images/full/2023-01-11\_16-51-07/ fullback\_20230111.mbi validate ---验证备份状态
备份恢复
- 全备恢复:
恢复前关闭mysql,并清空mysql数据目录。
mysqlbackup --defaults-file=
--socket=< .sock > --backup-dir=
- 增量备份恢复:
在执行增量恢复之前，要先恢复完整全量备份。
mysqlbackup --defaults-file=
--socket=< .sock > --backup-dir=
--uncompress copy-back-and-apply-log
- 部分表恢复:
恢复pets用户中的cats表
mysqlbackup --socket=< .sock > --include-tables="^pets\\.cats" --backup-dir=
--backup-image=
恢复“sales”数据库中的所有表，但排除名为“hardware”的表：
mysqlbackup --socket=< .sock > --include-tables="^sales\\." \\
--exclude-tables="^sales\\.hardware$" --backup-dir=
