权限类 本页为权限类常见问题。 MySQL需要具备哪些权限才能完成数据传输? 在使用DTS执行MySQL的数据传输时,需要区分是迁移还是同步任务,MySQL是源库还是目标库,根据不同的情况,来准备相关的权限。 数据迁移 数据库 所需权限 参考赋权语句 源库 源库为MySQL5.7时: 对MySQL库的SELECT权限。 对待迁移库的SELECT,EVENT,TRIGGER权限。 部分全局权限: REPLICATION CLIENT REPLICATION SLAVE SHOW VIEW PROCESS 如果是整实例迁移,需要对所有数据库的查询权限。 源库为MySQL8.0时: 对MySQL库的SELECT权限。 对待迁移库的SELECT, EXECUTE, EVENT,TRIGGER权限。 部分全局权限: PROCESS LOCK TABLES REPLICATION CLIENT REPLICATION SLAVE SHOW VIEW SHOWROUTINE 如果是整实例迁移,需要对所有数据库的查询权限。 源库为MySQL5.7时: GRANT SELECT ON mysql. TO '迁移账号'@'%'; GRANT SELECT, EVENT,TRIGGER ON 待迁移的库. TO '迁移账号'@'%'; GRANT REPLICATION CLIENT, REPLICATION SLAVE, SHOW VIEW, PROCESS ON . TO '迁移账号'@'%'; 源库为MySQL8.0时: GRANT SELECT ON mysql. TO '迁移账号'@'%'; GRANT SELECT, EXECUTE, EVENT, TRIGGER ON 待迁移的库. TO '迁移账号'@'%'; GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW, SHOWROUTINE ON . TO '迁移账号'@'%'; 目标库 以下全局权限: ALTER ALTER ROUTINE CREATE CREATE ROUTINE CREATE TEMPORARY TABLES CREATE USER CREATE VIEW DELETE DROP EVENT EXECUTE INDEX INSERT PROCESS REFERENCES SELECT SHOW DATABASES SHOW VIEW TRIGGER UPDATE GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, PROCESS, REFERENCES, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER, UPDATE ON . TO '迁移账号'@'%'; 数据同步 数据库 所需权限 参考赋权语句 源库 源库为MySQL5.7时: 对MySQL库的SELECT权限。 对待同步库的SELECT,EVENT,TRIGGER权限。 部分全局权限: REPLICATION CLIENT REPLICATION SLAVE SHOW VIEW PROCESS 如果是整库同步,需要对所有数据库的查询权限。 源库为MySQL8.0时: 对MySQL库的SELECT权限。 对待同步库的SELECT, EXECUTE, EVENT,TRIGGER权限。 部分全局权限: PROCESS REPLICATION CLIENT REPLICATION SLAVE SHOW VIEW SHOWROUTINE 如果是整库同步,需要对所有数据库的查询权限。 源库为MySQL5.7时: GRANT SELECT ON mysql. TO '同步账号'@'%'; GRANT SELECT , EVENT,TRIGGER ON 待同步的库. TO '同步账号'@'%'; GRANT REPLICATION CLIENT, REPLICATION SLAVE, SHOW VIEW, PROCESS ON . TO '同步账号'@'%'; 源库为MySQL8.0时: GRANT SELECT ON mysql. TO '同步账号'@'%'; GRANT SELECT, EXECUTE, EVENT, TRIGGER ON 待同步的库. TO '同步账号'@'%'; GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW, SHOWROUTINE ON . TO '同步账号'@'%'; 目标库 以下全局权限: ALTER ALTER ROUTINE CREATE CREATE ROUTINE CREATE TEMPORARY TABLES CREATE USER CREATE VIEW DELETEDROP EVENTEXECUTE INDEX INSERT PROCESS REFERENCES SELECT SHOW DATABASES SHOW VIEW TRIGGER UPDATE GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, PROCESS, REFERENCES, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER, UPDATE ON . TO '同步账号'@'%'; 注意 如果待迁移/同步对象包含用户自定义function,则在满足上述权限的基础上,目标端数据库同时需将logbintrustfunctioncreators设置为1,参考语句如下: set global logbintrustfunctioncreators 1;