讲解MySQL8.0备份与还原工具(mysqlbackup)

一、安装mysqlbackup

1. 下载

登录oracle edelivery，进入下载连接选择适合你系统的版本下载，在这里我使用的是银河麒麟Kylin OS Server V10 SP2，因此我选择一个通用的预编译二进制的tar包，如下图：
没有Oracle edelivery账号的朋友可以到私信我索取软件安装包。

2. 安装

[root@light] tar xvf mysql-commercial-backup-8.0.28-linux-glibc2.17-x86_64-minimal.tar.xz
[root@light] cd mysql-commercial-backup-8.0.28-linux-glibc2.17-x86_64-minimal
[root@light] mv bin/mysqlbackup /usr/local/mysql/bin
[root@light] chown mysql.mysql /usr/local/mysql/bin/mysqlbackup 
[root@light] mysqlbackup --version
MySQL Enterprise Backup  Ver 8.0.28-commercial for Linux on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2022, Oracle and/or its affiliates.

安装完成。

二、使用mysqlbackup

mysqlbackup支持将数据库全部文件备份为一个独立的image，也支持将数据库按目录结构备份datafile。话不多说，分开演示。

0. 创建mysqlbackup专用账户

创建一个immeb@localhost账户，专门用于备份数据库

CREATE USER 'immeb'@'localhost' IDENTIFIED BY 'LaoGeDB123!';
GRANT SELECT, BACKUP_ADMIN, RELOAD, PROCESS, SUPER, REPLICATION CLIENT ON *.* TO `immeb`@`localhost`;
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'immeb'@'localhost'; 
GRANT CREATE, INSERT, DROP, UPDATE, SELECT, ALTER ON mysql.backup_history TO 'immeb'@'localhost';
GRANT LOCK TABLES, CREATE, DROP, FILE, INSERT, ALTER ON *.* TO 'immeb'@'localhost';
GRANT CREATE, DROP, UPDATE ON mysql.backup_sbt_history TO 'immeb'@'localhost';
GRANT ENCRYPTION_KEY_ADMIN ON *.* TO 'immeb'@'localhost';
GRANT INNODB_REDO_LOG_ARCHIVE ON *.* TO 'immeb'@'localhost';
GRANT ALTER ON mysql.backup_progress TO 'immeb'@'localhost';
GRANT CREATE, INSERT, DROP ON mysql.backup_progress_old TO 'immeb'@'localhost';
GRANT CREATE, INSERT, DROP, ALTER ON mysql.backup_progress_new TO 'immeb'@'localhost';
GRANT CREATE, INSERT, DROP ON mysql.backup_history_old TO 'immeb'@'localhost';
GRANT CREATE, INSERT, DROP, ALTER ON mysql.backup_history_new TO 'immeb'@'localhost';
GRANT ALTER ON mysql.backup_sbt_history TO 'immeb'@'localhost';
GRANT CREATE, INSERT, DROP ON mysql.backup_sbt_history_old TO 'immeb'@'localhost';
GRANT CREATE, INSERT, DROP, ALTER ON mysql.backup_sbt_history_new TO 'immeb'@'localhost';

1. backup-to-image方式的全量备份与还原

1.1 备份image

备份命令：

mysqlbackup \
--user=immeb --password=LaoGeDB123!  \
--socket=/data/mysql/data/mysqld.sock  \
--backup-image=my_full_bak.mbi  \
--backu