记一次 MySQL 数据备份/迁移报错的解决方案

45 阅读 0 评论 30 点赞

我是靠谱客的博主洁净月饼，最近开发中收集的这篇文章主要介绍记一次 MySQL 数据备份/迁移报错的解决方案，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一、问题描述

1.1 GTID 错误

从腾讯云数据库（生产环境 mysql8.0）上 dump 数据到线下环境进行相关测试，云数据库使用的是双节点架构（异步复制方式），备份用户权限（select）。当在线下环境 source 备份的数据库脚本时，报如下错误：

ERROR 3546 (HY000): @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED

原始备份语句：

# 备份表结构：
/usr/bin/mysqldump --single-transaction -C -q -d -B <DBname> > /data/mysql/<Bname>.sql

# 备份表数据：
/usr/bin/mysqldump --single-transaction -C -q -n -t -B <DBname> > /data/mysql/<Bname>.sql

# 导入语句：
use DBname
source /root/<Bname>.sql

1.2 权限错误

这里的备份是在物理服务器上进行的数据备份，mysql 采用编译安装，备份用户权限（select）。报如下错误：

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this opera

原始备份语句：

# 备份表结构：
/home/mysql/bin/mysqldump --single-transaction -C -q -d -B <DBname> > /data/mysql/<Bname>.sql

# 备份表数据：
/home/mysql/bin/mysqldump --single-transaction -C -q -n -t -B <DBname> > /data/mysql/<Bname>.sql

# 导入语句：
use DBname
source /root/<Bname>.sql

二、解决方案

2.1 GTID 错误解决方案

我们都知道，MySQL 可基于 GTID 做主从复制，但无论是否使用了 GTID 的方式做了主从同步，导出导入时都需要注意数据库中的 GTID 信息。报错的原因是：当我 mysqldump 后，默认带了 GTID 相关信息（双节点架构），而带有 GTID 信息的 dump 文件，要求目标数据库实例必须开启 GTID 功能，否则就会导致上述错误，错误也很明显。其解决方案有两种：

方案1：将当前库的GTID_EXECUTED值置空

reset master;

# 执行完成后再导入sql脚本

方案2：导出不带有GTID信息的库

添加参数：–set-gtid-purged=off

# 备份表结构：
/usr/bin/mysqldump --single-transaction --set-gtid-purged=off -C -q -d -B <DBname> > /data/mysql/<Bname>.sql

# 备份表数据：
/usr/bin/mysqldump --single-transaction --set-gtid-purged=off -C -q -n -t -B <DBname> > /data/mysql/<Bname>.sql

2.2 权限错误解决方案

上述权限报错其实很明显，就是备份用户缺少PROCESS权限，按照提示添加权限即可：

grant process on *.* to '备份用户'@'host';
flush privileges;

# PROCESS权限是一个全局权限，给用户授权时需指定所有库所有表（*.*）。
# 默认情况下show processlist是可以查看当前用户的线程/连接的。
# 如果不给普通用户授予PROCESS权限，show processlist命令只能看到当前用户的线程，而授予了PROCESS权限后，使用 show processlist 就能看到所有用户的线程。

扩展1：LOCK TABLES权限

如果备份时加入没有添加 --single-transaction 选项，会报LOCK TABLES权限错误，解决方案同样是给备份用户添加LOCK TABLES权限。

扩展2：SHOW VIEW权限

当数据库中存在view（视图）的时候，使用mysqldump备份数据库，需要有SHOW VIEW权限，同样是给备份用户添加LOCK TABLES权限。

扩展3：RELOAD权限

加入 --master-data 选项后，备份需要RELOAD权限，同样是给备份用户添加LOCK TABLES权限。

扩展4：REPLICATION CLIENT和REPLICATION SLAVE权限

REPLICATION CLIENT 和 REPLICATION SLAVE 为mysql复制相关权限，一般复制账号需要这两个权限。同样是给备份用户添加对应权限。

扩展5：EVENT权限

备份是若要备份事件，备份选项 --events，则需要EVENT权限。

扩展6：TRIGGER权限

同样是给备份用户添加对应权限。

因此：在备份时可以一次性给备份用户赋予以下权限，免得每次都需要赋予权限

GRANT SELECT,PROCESS,LOCK TABLES,SHOW VIEW,RELOAD,REPLICATION CLIENT,REPLICATION SLAVE,EVENT,TRIGGER ON *.* TO '备份用户'@'host' IDENTIFIED BY 'your passwd';