如何导出和导入现有用户（包括其权限！）

12 浏览
0 Comments

如何导出和导入现有用户（包括其权限！）

我有一个现有的MySQL实例（test），其中包含2个数据库和一些用户，每个用户对每个数据库具有不同的访问权限。

现在我需要复制其中一个数据库（到production）和相关的用户。

复制数据库很简单：

导出：

mysqldump --no-data --tables -u root -p secondb >> secondb_schema.sql

导入：

mysql -u root -p -h localhost secondb < secondb_schema.sql

然而，我没有找到在命令行（内部或外部mysql）中导出和导入用户的直接方法。

如何在命令行中导出和导入用户？

更新：到目前为止，我已经找到了手动（因此容易出错）完成此操作的步骤：

-- lists all users
select user,host from mysql.user;

然后找到它的授权：

-- find privilege granted to a particular user
show grants for 'root'@'localhost';

然后手动创建用户，并在上述“show grants”命令的结果中列出授权。

我更喜欢一种更安全、更自动化的方式。是否有一种？

admin 更改状态以发布 2023年5月22日
0

2 答案

0 Comments 

mysql -u -p -h -e"select concat('show grants for ','\'',user,'\'@\'',host,'\'') from mysql.user" > user_list_with_header.txt
sed '1d' user_list_with_header.txt > ./user.txt
while read user; do  mysql -u -p -h -e"$user" > user_grant.txt; sed '1d' user_grant.txt >> user_privileges.txt; echo "flush privileges" >> user_privileges.txt; done < user.txt
awk '{print $0";"}'  user_privileges.txt >user_privileges_final.sql
rm user.txt user_list_with_header.txt user_grant.txt user_privileges.txt

以上脚本将在Linux环境中运行，并输出user_privileges_final.sql文件，您可以将其导入到新的MySQL服务器中，以复制用户权限。

更新：第二个MySQL语句的用户缺少一个-符号。

0
0 Comments

我发现使用Percona的工具pt-show-grants是导出用户最简单的方法之一。Percona工具包是免费的，安装和使用都很容易，文档也很多。

这是一种简单的方法来显示所有用户或特定用户。它列出了他们的授权并以SQL格式输出。我将举一个例子来展示如何显示test_user的所有授权：

shell> pt-show-grants --only test_user

该命令的示例输出：

GRANT USAGE ON *.* TO 'test_user'@'%' IDENTIFIED BY PASSWORD '*06406C868B12689643D7E55E8EB2FE82B4A6F5F4';
GRANT ALTER, INSERT, LOCK TABLES, SELECT, UPDATE ON `test`.* TO 'test_user'@'%';

通常我会将输出重定向到一个文件中，这样我就可以编辑我需要的内容了，或者加载到mysql中。

或者，如果你不想使用Percona工具，并想要转储所有用户，你可以这样使用mysqldump：

shell> mysqldump mysql --tables user db > users.sql

注意：--flush-privileges不能与此一起使用，因为整个数据库没有被转储。这意味着你需要手动运行它。

shell> mysql -e "FLUSH PRIVILEGES"

0