MySQL8恢复备份报ERROR 3552 如何导出含用户权限的单份备份

MySQL 8.0新增了系统库保护机制，禁止删除mysql、information_schema等内置系统库。你原有备份命令中--add-drop-database搭配--all-databases参数时，会为所有库（包括mysql系统库）生成DROP DATABASE IF EXISTS语句，恢复时执行到mysql库的删除语句就会触发ERROR 3552 (HY000) Access to system schema 'mysql' is rejected报错。

以下两个方案均可实现单份备份文件同时包含业务库数据和用户权限信息，恢复时不会触发系统库报错。

方案1：最小改动，过滤删除mysql库的语句 #

仅需在原有备份命令中增加sed过滤逻辑，删除掉备份内容里的mysql库DROP语句即可，修改后的备份命令如下：

mysqldump --add-drop-database --flush-logs --single-transaction --ignore-table=mysql.innodb_index_stats --ignore-table=mysql.innodb_table_stats --quick  --all-databases --triggers --routines --events -u root --password='senha' -P 3306 -h 1.1.1.1 | sed '/DROP DATABASE IF EXISTS `mysql`;/d' | bzip2 > /tmp/backup.sql.bz2

恢复方式 #

直接正常导入备份文件即可：

bunzip2 < /tmp/backup.sql.bz2 | mysql -u root -p

方案2：更稳妥的权限导出方案（推荐） #

直接备份mysql系统表存在版本兼容风险，推荐拆分备份逻辑：先导出所有业务库，再单独导出用户权限的SQL语句追加到备份文件末尾，完全不触及系统库结构，兼容性更好。

备份命令 #

# 1. 导出所有非系统业务库的结构、数据、触发器、存储过程、事件
mysqldump --add-drop-database --flush-logs --single-transaction --quick --databases $(mysql -u root --password='senha' -h 1.1.1.1 -P 3306 -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql|sys)" | tr '\n' ' ') --triggers --routines --events -u root --password='senha' -P 3306 -h 1.1.1.1 > /tmp/temp_backup.sql

# 2. 导出用户权限语句追加到备份文件（无需安装第三方工具，原生SQL实现）
mysql -u root --password='senha' -h 1.1.1.1 -P 3306 -sse "SELECT CONCAT('CREATE USER IF NOT EXISTS \'', user, '\'@\'', host, '\' IDENTIFIED WITH mysql_native_password AS \'', authentication_string, '\' PASSWORD EXPIRE NEVER;') FROM mysql.user WHERE user NOT IN ('mysql.sys', 'mysql.infoschema', 'mysql.session');" >> /tmp/temp_backup.sql
mysql -u root --password='senha' -h 1.1.1.1 -P 3306 -sse "SELECT CONCAT('GRANT ', PRIVILEGE_TYPE, ' ON ', TABLE_SCHEMA, '.* TO \'', GRANTEE, '\';') FROM INFORMATION_SCHEMA.USER_PRIVILEGES WHERE GRANTEE NOT IN ('\'mysql.sys\'@\'localhost\'', '\'mysql.infoschema\'@\'localhost\'', '\'mysql.session\'@\'localhost\'');" >> /tmp/temp_backup.sql

# 3. 压缩备份文件
bzip2 /tmp/temp_backup.sql && mv /tmp/temp_backup.sql.bz2 /tmp/backup.sql.bz2

恢复方式 #

和原有恢复逻辑完全一致，直接导入即可自动恢复业务库和用户权限。

内容的提问来源于stack exchange，提问作者Ribas