MySQL8.0_mysqldump_迁移手册_V1.0

1.整体规划1.1迁移涉及的对象源库源表目标库目标表192.168.1.12/192.168.1.55/56/57/58/59/source_dbxxxxxxtarget_dbxxxxxxxx1.2使用工具工具名称备份路径mysqldump软件/backup/mysql以上目录也可以根据实际情况去调整1.3导出导入专用用户创建用户mysqlselect host,user from mysql.user;create user dump_datalocalhostidentified by 密码;create user load_datalocalhost identified by 密码;select host,user from mysql.user;--导出用户GRANT SELECT,SHOW DATABASES,show view,RELOAD,LOCK TABLES,TRIGGER,EVENT,REPLICATION CLIENT,PROCESSON *.* TOdump_datalocalhost;如果只需备份特定数据库如 mydb将 *.* 改为 mydb.*-- 如果你使用的是 MySQL 8.0.14 或更高版本并且需要执行mysqldump --master-data2那么确实需要 BINLOG_ADMIN用于执行 SHOW MASTER STATUS。-- MySQL 8.0.14 不支持 BINLOG_ADMIN此时用 REPLICATION CLIENT 即可。-- BINLOG_ADMIN 是动态权限不能和其他权限写在同一句 GRANT 中。动态权限总是作用于 *.*全局不能限定到特定数据库。GRANT BINLOG_ADMIN ON *.* TO dump_datalocalhost;--导入用户GRANTSELECT, INSERT, UPDATE, DELETE,CREATE, DROP, ALTER, INDEX,REFERENCES,LOCK TABLES,REPLICATION CLIENTON *.* TO load_datalocalhost;-- 补充权限导入普通表不需要GRANTCREATE VIEW,SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE,EXECUTE,RELOADON *.* TO load_datalocalhost;grant SYSTEM_VARIABLES_ADMIN on *.* toload_datalocalhost;-- SYSTEM_VARIABLES_ADMIN 是 MySQL 8.0 引入的一个精细权限dynamic privilege用于控制用户是否可以动态修改全局系统变量即通过 SET GLOBAL 或 SET PERSIST 等语句。动态权限总是作用于 *.*全局不能限定到特定数据库。!!!注意在 MySQL 8.0 中SUPER 权限也包含此能力但官方推荐使用更细粒度的动态权限如 SYSTEM_VARIABLES_ADMIN替代 SUPER。--能力SUPERSYSTEM_VARIABLES_ADMIN修改全局变量✅✅终止任意会话✅❌需 CONNECTION_ADMIN切换复制角色✅❌忽略只读模式✅❌更细粒度控制❌✅推荐!!!最佳实践在 MySQL 8.0 中避免使用 SUPER改用组合动态权限principle of least privilege。-- 权限用途CONNECTION_ADMIN终止连接、查看所有会话ROLE_ADMIN管理角色BACKUP_ADMIN执行 LOCK INSTANCE FOR BACKUPSESSION_VARIABLES_ADMIN修改会话级变量通常不需要普通用户默认可改自己会话!!!安全建议不要随意授予 SYSTEM_VARIABLES_ADMIN因为恶意用户可通过修改变量破坏系统如 max_connections1 拒绝服务。对于只读账号、应用账号绝对不要授予此权限。运维账号可按需分配配合其他动态权限如 CONNECTION_ADMIN。SET GLOBAL variable_name value;SET PERSIST variable_name value;SET PERSIST_ONLY variable_name value;-- 验证SHOW GRANTS FOR dump_datalocalhost;SHOW GRANTS FOR load_datalocalhost;-- 刷新权限FLUSH PRIVILEGES;-- 权限是否必需用途SELECT✅ 必需读取表和视图数据也用于读取 routines 元数据LOCK TABLES✅ 必需MyISAM表锁保证一致性SHOW VIEW✅ 必需导出视图定义CREATE VIEWTRIGGER✅ 必需导出触发器EVENT✅ 必需导出事件调度器RELOAD✅ 必需执行 FLUSH TABLES用于一致性快照REPLICATION CLIENT!!!按需如果使用 --master-data 获取 binlog 位置PROCESS❌ 通常不需要查看线程列表mysqldump 不依赖SHOW DATABASES❌ 通常不需要当你明确指定数据库名时不需要--权限用途INSERT插入数据CREATE创建表恢复结构DROP删除旧表如果 dump 含 DROPALTER修改表结构INDEX创建索引REFERENCES外键约束通常不需要但安全起见可加!!!不要授予 SUPER、FILE除非用 LOAD DATA INFILE、SHUTDOWN 等危险权限。2.迁移过程2.1Mysqldump导出$(mysql)mysqldump -udump_data-p\--single-transaction\--flush-logs\--set-gtid-purgedOFF \mydb table1 table2 table3 multi_tables.sql--single-transactionInnoDB 一致性备份不锁表--lock-tablesfalse跳过锁表配合 --single-transaction 使用--routines同时导出存储过程和函数--triggers导出触发器默认已包含--set-gtid-purgedOFF避免 GTID 冲突主从环境注意--databases db1 db2备份多个指定数据库输出含 CREATE DATABASE 和 USE--all-databases, -A备份所有数据库等价于 --databases mysql sys ...--ignore-tabledb.table跳过指定表可多次使用--tables显式指定表列表默认行为--single-transactionInnoDB 推荐 启动一个事务保证一致性快照不锁表--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 语句便于调试--compatiblename生成兼容其他数据库的 SQL如 ansi, postgresql--set-gtid-purgedOFF主从环境必备 避免 GTID 冲突可选值ON, OFF, AUTO--set-gtid-purgedOFF 是 MySQL 5.6启用 GTID 模式后 使用 mysqldump 时的一个关键参数用于控制是否在备份文件中包含 GTID_PURGED 信息。什么是 GTIDGTIDGlobal Transaction Identifier 是 MySQL 主从复制中用于唯一标识事务的全局 ID。启用 GTID 后每个事务都有一个唯一的 source_id:transaction_id。从库通过 GTID 自动定位需要同步的位置无需手动指定 binlog 文件和位置。--set-gtid-purged 参数的作用该参数决定 mysqldump 是否在输出 SQL 文件中加入如下语句SET GLOBAL.GTID_PURGEDaaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:1-100;这个语句的作用是告诉目标 MySQL 实例“这些 GTID 已经被应用过”避免重复执行。--值行为适用场景AUTO默认如果启用了 GTID 且导出的是全库或部分库则自动添加 SET GTID_PURGED如果是单表则不加一般使用但可能引发问题ON强制添加 SET GTID_PURGED需要保留 GTID 信息如主从重建OFF完全不添加 SET GTID_PURGED 语句✅大多数备份/迁移场景推荐--场景推荐值日常备份、开发测试、跨环境迁移✅--set-gtid-purgedOFF重建从库、主从故障恢复--set-gtid-purgedON不确定✅默认用 OFF 更安全!!!最佳实践除非你明确需要 GTID 信息用于主从复制否则一律使用 --set-gtid-purgedOFF。注意修改multi_tables.sql里面的gtid和binlog点位2.2Rename源表Rename源表防止误操作mysqlshow tables;RENAMETABLE old_table_nameTOnew_table_name;show tables2.3Mysql导入$(mysql)mysql -uload_data-pmydb multi_tables.sql2.4创建业务用户$(mysql)select host,user from mysql.user;create user app_user192.168.126.% identified by 密码;create user app_user_read192.168.126.% identified by 密码;select host,user from mysql.user;-- 1045方案1修改用户使用旧插件兼容性更好ALTER USER app_user192.168.126.% IDENTIFIED WITH mysql_native_password BY 密码;-- 1045方案2升级应用的 MySQL 驱动到 8.0-- 1044报错排查-- 查当前用户SELECT USER(), CURRENT_USER();-- 用 root 执行CREATE DATABASE IF NOT EXISTSmydbCHARACTER SET utf8mb4COLLATE utf8mb4_unicode_ci;GRANT USAGE ON *.* TOapp_user192.168.126.%;GRANT select ON *.* TOapp_user192.168.126.%;GRANT ALL PRIVILEGES ON mydb.* TO app_user192.168.126.%;FLUSH PRIVILEGES;SHOW GRANTS FORapp_user192.168.126.%;-- 1044报错排查GRANT select ON *.* TOapp_user_read192.168.126.%;FLUSH PRIVILEGES;SHOW GRANTS FORapp_user_read192.168.126.%;3.回退3.1在源库rename表mysqlRENAMETABLE old_table_nameTOnew_table_name;3.2锁定目的库业务用户$(mysql)-- 锁定用户 app_user 从任何主机的连接ALTER USERapp_user192.168.126.%ACCOUNTLOCK;FLUSH PRIVILEGES;SELECT User, Host, account_locked FROM mysql.user WHERE User app_user;-- 查看连接SELECTUSER,Host, db, Command,COUNT(*) AS connectionsFROMinformation_schema.processlistGROUP BYUSER,Host, db, CommandORDER BYconnections DESC;-- 只想看真实客户端用户排除复制 I/O 或 SQL 线程排除系统用户如 system user用于复制SELECTUSER,Host, db, Command,COUNT(*) AS connectionsFROMinformation_schema.processlistWHEREUSER NOT IN (system user, event_scheduler)GROUP BYUSER,Host, db, CommandORDER BYconnections DESC;-- 解锁ALTER USER app_user192.168.126.% ACCOUNTUNLOCK;FLUSH PRIVILEGES;SELECT User, Host, account_locked FROM mysql.user WHERE User app_user;