1.整体规划

1.1迁移涉及的对象

源库	源表	目标库	目标表
192.168.1.12	/	192.168.1.55/56/57/58/59	/
source_db	xxxxxx	target_db	xxxxxxxx

1.2使用工具

工具名称	备份路径
mysqldump软件	/backup/mysql

以上目录也可以根据实际情况去调整

1.3导出导入专用用户

创建用户

mysql>

select host,user from mysql.user;

create user dump_data@localhost identified by '密码';

create user load_data@localhost identified by '密码';

select host,user from mysql.user;

--导出用户

GRANT SELECT,

SHOW DATABASES,

show view,

RELOAD,

LOCK TABLES,

TRIGGER,

EVENT,

REPLICATION CLIENT,

PROCESS

ON *.* TO dump_data@localhost;

如果只需备份特定数据库（如 mydb），将 *.* 改为 mydb.*：

-- 如果你使用的是 MySQL 8.0.14 或更高版本，并且需要执行mysqldump --master-data=2

那么确实需要 BINLOG_ADMIN（用于执行 SHOW MASTER STATUS）。

-- MySQL < 8.0.14 不支持 BINLOG_ADMIN，此时用 REPLICATION CLIENT 即可。

-- BINLOG_ADMIN 是动态权限，不能和其他权限写在同一句 GRANT 中。

动态权限总是作用于 *.*（全局），不能限定到特定数据库。

GRANT BINLOG_ADMIN ON *.* TO 'dump_data'@'localhost';

--导入用户

GRANT

  SELECT, INSERT, UPDATE, DELETE,

  CREATE, DROP, ALTER, INDEX,

  REFERENCES,

  LOCK TABLES,

  REPLICATION CLIENT        

ON *.* TO 'load_data'@'localhost';

-- 补充权限（导入普通表不需要）

GRANT

  CREATE VIEW,

  SHOW VIEW,

  CREATE ROUTINE,

  ALTER ROUTINE,

  EXECUTE,

  RELOAD

ON *.* TO 'load_data'@'localhost';

grant SYSTEM_VARIABLES_ADMIN  on *.* to 'load_data'@'localhost';

-- SYSTEM_VARIABLES_ADMIN 是 MySQL 8.0 引入的一个精细权限（dynamic privilege），用于控制用户是否可以动态修改全局系统变量（即通过 SET GLOBAL 或 SET PERSIST 等语句）。

动态权限总是作用于 *.*（全局），不能限定到特定数据库。

!!!注意：在 MySQL 8.0 中，SUPER 权限也包含此能力，但官方推荐使用更细粒度的动态权限（如 SYSTEM_VARIABLES_ADMIN）替代 SUPER。

-- 能力 SUPER SYSTEM_VARIABLES_ADMIN

修改全局变量 ✅ ✅

终止任意会话 ✅ ❌（需 CONNECTION_ADMIN）

切换复制角色 ✅ ❌

忽略只读模式 ✅ ❌

更细粒度控制 ❌ ✅（推荐）

!!!最佳实践：在 MySQL 8.0+ 中，避免使用 SUPER，改用组合动态权限（principle of least privilege）。

-- 权限 用途

CONNECTION_ADMIN 终止连接、查看所有会话

ROLE_ADMIN 管理角色

BACKUP_ADMIN 执行 LOCK INSTANCE FOR BACKUP

SESSION_VARIABLES_ADMIN 修改会话级变量（通常不需要，普通用户默认可改自己会话）

!!!安全建议

不要随意授予 SYSTEM_VARIABLES_ADMIN，因为恶意用户可通过修改变量破坏系统（如 max_connections=1 拒绝服务）。

对于只读账号、应用账号，绝对不要授予此权限。

运维账号可按需分配，配合其他动态权限（如 CONNECTION_ADMIN）。

SET GLOBAL variable_name = value;

SET PERSIST variable_name = value;

SET PERSIST_ONLY variable_name = value;

-- 验证

SHOW GRANTS FOR 'dump_data'@'localhost';

SHOW GRANTS FOR 'load_data'@'localhost';

-- 刷新权限

FLUSH PRIVILEGES;

-- 权限 是否必需 用途

SELECT ✅ 必需 读取表和视图数据，也用于读取 routines 元数据

LOCK TABLES ✅ 必需（MyISAM） 表锁保证一致性

SHOW VIEW ✅ 必需 导出视图定义（CREATE VIEW）

TRIGGER ✅ 必需 导出触发器

EVENT ✅ 必需 导出事件调度器

RELOAD ✅ 必需 执行 FLUSH TABLES（用于一致性快照）

REPLICATION CLIENT  !!!按需 如果使用 --master-data 获取 binlog 位置

PROCESS ❌ 通常不需要 查看线程列表（mysqldump 不依赖）

SHOW DATABASES ❌ 通常不需要 当你明确指定数据库名时不需要

-- 权限 用途

INSERT 插入数据

CREATE 创建表（恢复结构）

DROP 删除旧表（如果 dump 含 DROP）

ALTER 修改表结构

INDEX 创建索引

REFERENCES 外键约束（通常不需要，但安全起见可加）

!!!不要授予 SUPER、FILE（除非用 LOAD DATA INFILE）、SHUTDOWN 等危险权限。

2.迁移过程

2.1Mysqldump导出

$(mysql)

mysqldump -u dump_data -p \

--single-transaction \

--flush-logs \

--set-gtid-purged=OFF \

 mydb table1 table2 table3 > multi_tables.sql

--single-transaction InnoDB 一致性备份（不锁表）

--lock-tables=false 跳过锁表（配合 --single-transaction 使用）

--routines 同时导出存储过程和函数

--triggers 导出触发器（默认已包含）

--set-gtid-purged=OFF 避免 GTID 冲突（主从环境注意）

--databases db1 db2 备份多个指定数据库（输出含 CREATE DATABASE 和 USE）

--all-databases, -A 备份所有数据库（等价于 --databases mysql sys ...）

--ignore-table=db.table 跳过指定表（可多次使用）

--tables 显式指定表列表（默认行为）

--single-transaction InnoDB 推荐！ 启动一个事务，保证一致性快照，不锁表

--lock-tables, -l 备份前锁所有表（MyISAM 必需，默认开启）

--lock-all-tables, -x 全局读锁（所有库所有表），比 --lock-tables 更强

--flush-logs 备份前刷新 binlog（配合主从/ PITR）

--master-data[=1|2] 在输出中加入 CHANGE MASTER TO 语句

    • =1：可执行语句

    • =2：注释形式（推荐）

--no-create-info, -t 只导出数据，不导出 CREATE TABLE

--no-data, -d 只导出表结构，不导出数据

--skip-triggers 不导出触发器（默认导出）

--routines, -R 导出存储过程和函数

--events, -E 导出事件调度器

--skip-comments 去掉注释（如 -- Dump completed on ...）

--skip-add-drop-table 不生成 DROP TABLE IF EXISTS（默认生成）

--add-locks 在 INSERT 前后加 LOCK TABLES / UNLOCK TABLES（默认开启）

--complete-insert, -c 使用列名的完整 INSERT 语句（便于调试）

--compatible=name 生成兼容其他数据库的 SQL（如 ansi, postgresql）

--set-gtid-purged=OFF 主从环境必备！ 避免 GTID 冲突（可选值：ON, OFF, AUTO）

--set-gtid-purged=OFF 是 MySQL 5.6+（启用 GTID 模式后） 使用 mysqldump 时的一个关键参数，用于控制是否在备份文件中包含 GTID_PURGED 信息。

什么是 GTID？

GTID（Global Transaction Identifier） 是 MySQL 主从复制中用于唯一标识事务的全局 ID。

启用 GTID 后，每个事务都有一个唯一的 source_id:transaction_id。

从库通过 GTID 自动定位需要同步的位置，无需手动指定 binlog 文件和位置。

--set-gtid-purged 参数的作用

该参数决定 mysqldump 是否在输出 SQL 文件中加入如下语句：

SET @@GLOBAL.GTID_PURGED='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:1-100';

这个语句的作用是：告诉目标 MySQL 实例“这些 GTID 已经被应用过”，避免重复执行。

-- 值           行为                 适用场景

AUTO（默认） 如果启用了 GTID 且导出的是全库或部分库，则自动添加 SET GTID_PURGED；如果是单表，则不加                                   一般使用，但可能引发问题

ON 强制添加 SET GTID_PURGED 需要保留 GTID 信息（如主从重建）

OFF 完全不添加 SET GTID_PURGED 语句 ✅大多数备份/迁移场景推荐！

-- 场景                               推荐值

日常备份、开发测试、跨环境迁移      ✅--set-gtid-purged=OFF

重建从库、主从故障恢复              --set-gtid-purged=ON

不确定                          ✅默认用 OFF 更安全

!!!最佳实践：

除非你明确需要 GTID 信息用于主从复制，否则一律使用 --set-gtid-purged=OFF。

注意修改multi_tables.sql里面的gtid和binlog点位

2.2Rename源表

Rename源表，防止误操作

mysql>

show tables;

RENAME TABLE old_table_name TO new_table_name;

show tables

2.3Mysql导入

$(mysql)

mysql -u load_data -p mydb < multi_tables.sql

2.4创建业务用户

$(mysql)

select host,user from mysql.user;

create user 'app_user'@'192.168.126.%' identified by '密码';

create user 'app_user_read'@'192.168.126.%' identified by '密码';

select host,user from mysql.user;

-- 1045方案1：修改用户使用旧插件（兼容性更好）

ALTER USER 'app_user'@'192.168.126.%' IDENTIFIED WITH mysql_native_password BY '密码';

-- 1045方案2：升级应用的 MySQL 驱动到 8.0+

-- 1044报错排查：-- 查当前用户

SELECT USER(), CURRENT_USER();

-- 用 root 执行

CREATE DATABASE IF NOT EXISTS mydb

  CHARACTER SET utf8mb4

  COLLATE utf8mb4_unicode_ci;

GRANT USAGE ON *.* TO 'app_user'@'192.168.126.%';

GRANT select ON *.* TO 'app_user'@'192.168.126.%';

GRANT ALL PRIVILEGES ON mydb.* TO 'app_user'@'192.168.126.%';

FLUSH PRIVILEGES;

SHOW GRANTS FOR 'app_user'@'192.168.126.%';  -- 1044报错排查

GRANT select ON *.* TO 'app_user_read'@'192.168.126.%';

FLUSH PRIVILEGES;

SHOW GRANTS FOR 'app_user_read'@'192.168.126.%';

3.回退

3.1在源库rename表

mysql>

RENAME TABLE old_table_name TO new_table_name;

3.2锁定目的库业务用户

$(mysql)

-- 锁定用户 app_user 从任何主机的连接

ALTER USER 'app_user'@'192.168.126.%' ACCOUNT LOCK;

FLUSH PRIVILEGES;

SELECT User, Host, account_locked FROM mysql.user WHERE User = 'app_user';

-- 查看连接

SELECT

    USER, Host, db, Command,

    COUNT(*) AS connections

FROM

    information_schema.processlist

GROUP BY

    USER, Host, db, Command

ORDER BY

connections DESC;

-- 只想看真实客户端用户（排除复制 I/O 或 SQL 线程），排除系统用户（如 system user，用于复制）

SELECT

    USER, Host, db, Command,

    COUNT(*) AS connections

FROM

    information_schema.processlist

WHERE

    USER NOT IN ('system user', 'event_scheduler')

GROUP BY

    USER, Host, db, Command

ORDER BY

connections DESC;

-- 解锁

ALTER USER 'app_user'@'192.168.126.%' ACCOUNT UNLOCK;

FLUSH PRIVILEGES;

SELECT User, Host, account_locked FROM mysql.user WHERE User = 'app_user';