如何汇出与汇出导入现有用户(及其特权！) [英] How to Export & Import Existing User (with its Privileges!)

问题描述

我有一个现有的MySQL实例(测试)，包含2个数据库和几个用户，每个用户对每个数据库都有不同的访问权限.

I have an existing MySQL instance (test), containing 2 databases and a few users each having different access privileges to each database.

我现在需要复制一个数据库(进入生产环境)和与其关联的用户.

I now need to duplicate one of the databases (into production) and the users associated with it.

复制数据库容易:

导出:

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

导入:

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

但是，我找不到从命令行(在mysql内部还是外部)导出和导入用户的直接方法.

I didn't find, however, a straightforward way to export and import users, from the command line (either inside or outside mysql).

如何从命令行导出和导入用户?

How do I export and import a user, from the command line?

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

Update: So far, I have found manual (and thus error prone) steps for accomplishing this:

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

然后找到其赠款:

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

然后手动使用上面"show Grants"命令结果中列出的赠款创建用户.

Then manually create user with the grants listed in the result of the 'show grants' command above.

我更喜欢一种更安全，更自动化的方式.有一个吗?

I prefer a safer, more automated way. Is there one?

推荐答案

我发现导出用户最简单的方法之一是使用Percona的pt-show-grants工具. Percona工具套件是免费的，易于安装且易于使用，并带有大量文档. 这是显示所有用户或特定用户的简便方法.它以SQL格式列出其所有授予和输出.我将举一个示例，说明如何显示test_user的所有赠款:

One of the easiest ways I've found to export users is using Percona's tool pt-show-grants. The Percona tool kit is free, easy to install, and easy to use, with lots of documentation. It's an easy way to show all users, or specific users. It lists all of their grants and outputs in SQL format. I'll give an example of how I would show all grants for 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中.

I usually rederict the output into a file so I can edit what I need, or load it into mysql.

或者，如果您不想使用Percona工具并想对所有用户进行转储，则可以通过以下方式使用mysqldump:

Alternatively, if you don't want to use the Percona tool and want to do a dump of all users, you could use mysqldump in this fashion:

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

注意:--flush-privileges不适用于此功能，因为不会转储整个数据库.这意味着您需要手动运行它.

Note: --flush-privileges won't work with this, as the entire db isn't being dumped. this means you need to run it manually.

shell> mysql -e "FLUSH PRIVILEGES"

这篇关于如何汇出与汇出导入现有用户(及其特权！)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！