前言
由于某些很坑的原因,需要将一台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()