停止旧wins的mysql服务（也可以不停止服务直接备份）

1
2
3
# systemctl stop mariadb  
OR  
# systemctl stop mysql  

备份旧mysql服务上所有的数据库

1
mysqldump -u root -p --all-databases > all_databases.sql 

  1. 将备份的文件拷贝到新服务器上

  1. 在新服务上还原
1
mysql -u root -p123456 < all_databases.sql 

5.迁移用户权限

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- 执行下面sql语句查询数据库用户
 SELECT CONCAT('SHOW GRANTS FOR\'', user,'\'@\'', host, '\';') FROM mysql.user ;
 -- 筛选查询结果，查询需要授权的用户
SHOW GRANTS FOR'COMMON_IMPORT_IMAS'@'%';
SHOW GRANTS FOR'EAST2'@'%';
SHOW GRANTS FOR'RATE_FILING'@'%';
SHOW GRANTS FOR'STREAM'@'%';
SHOW GRANTS FOR'USERS_CORE'@'%';
SHOW GRANTS FOR'USERS_CORE_IMAS'@'%';
SHOW GRANTS FOR'USERS_CORE_PAYSETTLE'@'%';
SHOW GRANTS FOR'USERS_CORE_RCPMIS'@'%';
SHOW GRANTS FOR'jira666_root'@'%';
SHOW GRANTS FOR'rcpmis'@'%';
-- 将结果拷贝到目标服务器上执行
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'COMMON_IMPORT_IMAS'@'%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'EAST2'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'RATE_FILING'@'%' WITH GRANT OPTION;
GRANT USAGE ON *.* TO 'STREAM'@'%';
GRANT ALL PRIVILEGES ON `stream`.* TO 'STREAM'@'%';
GRANT ALL PRIVILEGES ON `users_core`.* TO 'STREAM'@'%';
GRANT USAGE ON *.* TO 'USERS_CORE'@'%';
GRANT ALL PRIVILEGES ON `users_core`.* TO 'USERS_CORE'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'USERS_CORE_IMAS'@'%' WITH GRANT OPTION;
GRANT USAGE ON *.* TO 'USERS_CORE_PAYSETTLE'@'%';
GRANT ALL PRIVILEGES ON `users_core_paysettle`.* TO 'USERS_CORE_PAYSETTLE'@'%';
GRANT USAGE ON *.* TO 'USERS_CORE_RCPMIS'@'%';
GRANT ALL PRIVILEGES ON `users_core_rcpmis`.* TO 'USERS_CORE_RCPMIS'@'%';
GRANT USAGE ON *.* TO 'jira666_root'@'%';
GRANT ALL PRIVILEGES ON `jiradb`.* TO 'jira666_root'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'rcpmis'@'%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON `users_core_rcpmis`.* TO 'rcpmis'@'%';
flush privileges;
-- 用旧服务器上的用户登录测试 是否可以正常登录

原服务器：

目标服务器：

问题：

1.目标服务器上执行 grant all privileges on stream.* to ‘STREAM‘@’%’;

提示1133-Can’t find any matching row in the user table错误。

解决办法：执行flush privilegs即可。

2.如果是linux服务器上的迁移，用户权限迁移可参考博客：

https://developer.aliyun.com/article/423208

参考博客：

https://www.51cto.com/article/593531.html

https://blog.51cto.com/linuxtech/1732303