mysql 拷贝权限_mysql用户及权限复制

前言

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

原理

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

用户迁移

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

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

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

FLUSH PRIVILEGES

然后新的密码才能生效

权限迁移

利用SQL语句

SHOW GRANTS FOR 'user'@'host';

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

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

完整示例脚本

# 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()