mysql用户及权限复制

Posted on Views:

前言

由于某些很坑的原因，需要将一台mysql里的全部数据进行迁移，并且需要迁移用户及权限。下面记录下用户及权限是如何迁移的。

原理

首先，我没找到现成的工具。。。因此只好自己搞了，好在也没多复杂。

用户迁移

mysql里的用户都存在于mysql.user这张表里。可以通过SQL查询这张表拿到host、user、authentication_string（加密后的密码）。

然后通过SQL在新库中创建用户，并更新新库中的mysql.user表里的authentication_string字段。这样就不需要知道账号的明文密码了。

需要注意的是，修改完mysql.user表中的加密密码字段后，需要执行

1
FLUSH PRIVILEGES

然后新的密码才能生效

权限迁移

利用SQL语句

1
SHOW GRANTS FOR 'user'@'host';

可查询指定用户被授权过的权限，得到的直接就是一条授权语句。将此授权语句在新库中执行即可

需要注意的是授权语句中如果有针对某个表的授权，那么当表不存在时会报错，因此授权的迁移只能放在数据的迁移之后

完整示例脚本

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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
# coding:utf8
import pymysql


source_mysql_client = pymysql.connect(
    host=None, user=None, password="", database=None, port=3306
)
source_mysql_client.autocommit(True)
source_mysql_cursor = source_mysql_client.cursor()

target_mysql_client = pymysql.connect(
    host=None, user=None, password="", database=None, port=3306
)
target_mysql_client.autocommit(True)
target_mysql_cursor = target_mysql_client.cursor()

# 需要忽略的用户
ignore_users = ["mysql.session", "root", "mysql.sys"]

#
target_sql_list = []

sql = "select host, user, authentication_string from mysql.user"
source_mysql_cursor.execute(sql)

for host, user, authentication_string in source_mysql_cursor.fetchall():
    if user in ignore_users:
        continue
    # 创建用户
    create_sql = "CREATE USER IF NOT EXISTS '{}'@'{}' IDENTIFIED BY 'skvnajnvr92jkfads'".format(
        user, host
    )
    # 修改密码
    change_password_sql = "UPDATE mysql.user SET authentication_string='{}' WHERE host='{}' AND user='{}'".format(
        authentication_string, host, user
    )

    target_sql_list.append(create_sql)
    target_sql_list.append(change_password_sql)

    # 授权语句获取
    grant_sql = "show grants for '{}'@'{}'".format(user, host)
    source_mysql_cursor.execute(grant_sql)
    grant_sql_result = source_mysql_cursor.fetchall()
    target_sql_list.extend([x[0] for x in grant_sql_result])

source_mysql_cursor.close()
source_mysql_client.close()


#
target_sql_list.append("FLUSH PRIVILEGES")
for sql in target_sql_list:
    r = target_mysql_cursor.execute(sql)
    print(sql)
    print(r)

target_mysql_cursor.close()
target_mysql_client.close()