MySQL 如何导出和导入现有用户（及其权限！）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23519797/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Export & Import Existing User (with its Privileges!)
提问by Withheld
I have an existing MySQL instance (test), containing 2 databases and a few users each having different access privileges to each database.
我有一个现有的 MySQL 实例（测试），包含 2 个数据库和几个用户，每个用户对每个数据库都有不同的访问权限。
I now need to duplicate one of the databases (into production) and the usersassociated with it.
我现在需要复制其中一个数据库（进入生产环境）和与之关联的用户。
Duplicating the database was easy:
复制数据库很容易：
Export:
出口：
mysqldump --no-data --tables -u root -p secondb >> secondb_schema.sql
Import:
进口：
mysql -u root -p -h localhost secondb < secondb_schema.sql
I didn't find, however, a straightforward way to export and import users, from the command line(either inside or outside mysql).
我没有找到，但是，一个简单的方法来导出和导入用户，在命令行（内部或外部的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;
Then find its grants:
然后找到它的补助金：
-- find privilege granted to a particular user
show grants for 'root'@'localhost';
Then manuallycreate user with the grants listed in the result of the 'show grants' command above.
然后使用上面“show grants”命令的结果中列出的授权手动创建用户。
I prefer a safer, more automated way. Is there one?
我更喜欢更安全、更自动化的方式。有吗？
采纳答案by Matthew Carey
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:
我发现导出用户的最简单方法之一是使用 Percona 的工具 pt-show-grants。Percona 工具包是免费的、易于安装且易于使用，并带有大量文档。这是显示所有用户或特定用户的简单方法。它以 SQL 格式列出了他们的所有授权和输出。我将举例说明如何显示 test_user 的所有授权：
shell> pt-show-grants --only test_user
Example output of that command:
该命令的示例输出：
GRANT USAGE ON *.* TO 'test_user'@'%' IDENTIFIED BY PASSWORD '*06406C868B12689643D7E55E8EB2FE82B4A6F5F4';
GRANT ALTER, INSERT, LOCK TABLES, SELECT, UPDATE ON `test`.* TO 'test_user'@'%';
I usually rederict the output into a file so I can edit what I need, or load it into mysql.
我通常将输出重新定义为一个文件，以便我可以编辑我需要的内容，或将其加载到 mysql 中。
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:
或者，如果您不想使用 Percona 工具并希望对所有用户进行转储，您可以以这种方式使用 mysqldump：
shell> mysqldump mysql --tables user db > users.sql
Note: --flush-privileges won't work with this, as the entire db isn't being dumped. this means you need to run it manually.
注意：--flush-privileges 不适用于此，因为整个数据库不会被转储。这意味着您需要手动运行它。
shell> mysql -e "FLUSH PRIVILEGES"
回答by Vardan Gupta
mysql -u<user> -p<password> -h<host> -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<user> -p<password> -h<host> -e"$user" > user_grant.txt; sed '1d' user_grant.txt >> user_privileges.txt; echo "flush privileges" >> user_privileges.txt; done < user.txt
awk '{print using System;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.IO;
using System.Collections.Generic;
namespace GenerateUsersScript
{
class Program
{
static void Main(string[] args)
{
List<string> grantsQueries = new List<string>();
// Get A Show Grants query for each user
using (MySqlConnection sourceConn = OpenConnection("sourceDatabase"))
{
using (MySqlDataReader usersReader = GetUsersReader(sourceConn))
{
while (usersReader.Read())
{
grantsQueries.Add(String.Format("SHOW GRANTS FOR '{0}'@'{1}'", usersReader[0], usersReader[1]));
}
}
Console.WriteLine("Exporting Grants For {0} Users", grantsQueries.Count);
using (StreamWriter writer = File.CreateText(@".\UserPermissions.Sql"))
{
// Then Execute each in turn
foreach (string grantsSql in grantsQueries)
{
WritePermissionsScript(sourceConn, grantsSql, writer);
}
//using (MySqlConnection destConn = OpenConnection("targetDatabase"))
//{
// MySqlCommand command = destConn.CreateCommand();
// foreach (string grantsSql in grantsQueries)
// {
// WritePermissionsDirect(sourceConn, grantsSql, command);
// }
//}
}
}
Console.WriteLine("Done - Press A Key to Continue");
Console.ReadKey();
}
private static void WritePermissionsDirect(MySqlConnection sourceConn, string grantsSql, MySqlCommand writeCommand)
{
MySqlCommand cmd = new MySqlCommand(grantsSql, sourceConn);
using (MySqlDataReader grantsReader = cmd.ExecuteReader())
{
while (grantsReader.Read())
{
try
{
writeCommand.CommandText = grantsReader[0].ToString();
writeCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(grantsReader[0].ToString());
Console.WriteLine(ex.Message);
}
}
}
}
private static void WritePermissionsScript(MySqlConnection conn, string grantsSql, StreamWriter writer)
{
MySqlCommand command = new MySqlCommand(grantsSql, conn);
using (MySqlDataReader grantsReader = command.ExecuteReader())
{
while (grantsReader.Read())
{
writer.WriteLine(grantsReader[0] + ";");
}
}
writer.WriteLine();
}
private static MySqlDataReader GetUsersReader(MySqlConnection conn)
{
string queryString = String.Format("SELECT User, Host FROM USER");
MySqlCommand command = new MySqlCommand(queryString, conn);
MySqlDataReader reader = command.ExecuteReader();
return reader;
}
private static MySqlConnection OpenConnection(string connName)
{
string connectionString = ConfigurationManager.ConnectionStrings[connName].ConnectionString;
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();
return connection;
}
}
}
";"}' user_privileges.txt >user_privileges_final.sql
rm user.txt user_list_with_header.txt user_grant.txt user_privileges.txt
Above script will run in linux environment and output will be user_privileges_final.sql that you can import in new mysql server where you want to copy user privileges.
以上脚本将在 linux 环境中运行，输出将是 user_privileges_final.sql，您可以将其导入到要复制用户权限的新 mysql 服务器中。
UPDATE: There was a missing -for the user of the 2nd mysql statement.
更新：-第二个 mysql 语句的用户缺少一个。
回答by Hugh Jones
I tackled this with a small C# program. There is code here to generate a script or apply the grants directly from source to destination. If porting from a Windows -> *nix environment you may have to consider case sensitivity issues.
我用一个小的 C# 程序解决了这个问题。这里有代码可以生成脚本或直接从源到目标应用授权。如果从 Windows -> *nix 环境移植，您可能必须考虑区分大小写的问题。
<connectionStrings>
<add name="sourceDatabase" connectionString="server=localhost;user id=hugh;password=xxxxxxxx;persistsecurityinfo=True;database=MySql" />
<add name="targetDatabase" connectionString="server=queeg;user id=hugh;password=xxxxxxxx;persistsecurityinfo=True;database=MySql" />
</connectionStrings>
with an app.config containing ...
带有包含...的 app.config
// Set up database root credentials
$host = 'localhost';
$user = 'root';
$pass = 'YOUR PASSWORD';
// ---- Do not edit below this ----
// Misc settings
header('Content-type: text/plain; Charset=UTF-8');
// Final import queries goes here
$export = array();
// Connect to database
try {
$link = new PDO("mysql:host=$host;dbname=mysql", $user, $pass);
} catch (PDOException $e) {
printf('Connect failed: %s', $e->getMessage());
die();
}
// Get users from database
$statement = $link->prepare("select `user`, `host`, `password` FROM `user`");
$statement->execute();
while ($row = $statement->fetch())
{
$user = $row[0];
$host = $row[1];
$pass = $row[2];
$export[] = 'CREATE USER \''. $user .'\'@\''. $host .'\' IDENTIFIED BY \''. $pass .'\'';
// Fetch any permissions found in database
$statement2 = $link->prepare('SHOW GRANTS FOR \''. $user .'\'@\''. $host .'\'');
$statement2->execute();
if ($row2 = $statement2->fetch())
{
$export[] = $row2[0];
}
}
$link = null;
echo implode(";\n", $export);
回答by zed
A PHP script to loop over your users to get the grant commands would be as such:
一个循环遍历用户以获取授权命令的 PHP 脚本如下：
mysql -u<user> -p<password> -h<host> -N mysql -e "select concat(\"'\", user, \"'@'\", host, \"'\"), authentication_string from user where not user like 'mysql.%'" | while read usr pw ; do echo "GRANT USAGE ON *.* TO $usr IDENTIFIED BY PASSWORD '$pw';" ; mysql -u<user> -p<password> -h<host> -N -e "SHOW GRANTS FOR $usr" | grep -v 'GRANT USAGE' | sed 's/\(\S\)$/;/' ; done
Gist: https://gist.github.com/zaiddabaeen/e88a2d10528e31cd6692
要点：https: //gist.github.com/zaiddabaeen/e88a2d10528e31cd6692
回答by Sergey Podushkin
Yet another bash one-liner for linux to use instead of Percona tool:
另一个用于 Linux 的 bash one-liner 代替 Percona 工具：
mysql -u<user> -p<password> -N mysql -e "select concat(\"'\", user, \"'@'\", host, \"'\"), authentication_string from user where not user like 'root'" | while read usr pw ; do mysql -u<user> -p<password> -N -e "SHOW GRANTS FOR $usr" | sed 's/\(\S\)$/;/'; done
回答by Mysql MariaDB
PhpMyAdminYou can use phpMyAdmin.
PhpMyAdmin您可以使用 phpMyAdmin。
Login and Go to your database or a table where the user has access.
登录并转到您的数据库或用户有权访问的表。
Select privileges
选择权限
All users with access are there.
所有具有访问权限的用户都在那里。
Select Export. And a little window with all the GRANTS are there ready to copy and paste.
选择导出。一个包含所有 GRANTS 的小窗口可供复制和粘贴。
回答by HadTheSameProblem
I had the same problem. The solution is that after the import of the backup you need to do a "flush privileges;". Then the privileges of the users will be active as in the original database.
我有同样的问题。解决办法是，在导入备份后需要做一个“刷新权限；”。然后用户的权限将像在原始数据库中一样处于活动状态。
SO
所以
mysql -u root -p -h localhost secondb < secondb_schema.sql
mysql -u root; then in mysql: "flush privileges;"
mysql -u root -p -h localhost secondb < secondb_schema.sql
mysql -u 根；然后在 mysql 中：“刷新权限；”
回答by shgnInc
In complement of @Sergey-Podushkin 's answer, this shell script code is workin for me:
作为@Sergey-Podushkin 回答的补充，这个 shell 脚本代码对我有用：
[client]
password=(put your password here)
回答by RedScourge
Here's what I'm using these days as part of my daily backup scripts (requires root shell and MySQL access, linux shell, and uses the mysql built-in schema:
这是我这些天用作日常备份脚本的一部分的内容（需要 root shell 和 MySQL 访问、linux shell，并使用 mysql 内置架构：
First, I create a file /var/backup/mysqlroot.cnf containing the root password so I can automate my scripts and not hardcode any passwords in them:
首先，我创建了一个包含 root 密码的文件 /var/backup/mysqlroot.cnf 以便我可以自动化我的脚本而不是硬编码其中的任何密码：
touch /var/backup/backup_sql.sh
chmod 700 /var/backup/backup_sql.sh
vi /var/backup/backup_sql.sh
Then I create an export script which dumps create user commands and grants like this:
然后我创建一个导出脚本，它转储创建用户命令和授权，如下所示：
#!/bin/bash
mysql --defaults-extra-file=/var/backup/mysqlroot.cnf -sNe " \
SELECT \
CONCAT( 'CREATE USER \'', User, '\'@\'', Host, '\' IDENTIFIED BY ', authentication_string, '\;' ) AS User \
FROM mysql.user \
WHERE \
User NOT LIKE 'mysql.%' AND CONCAT( User, Host ) <> 'rootlocalhost' AND User <> 'debian-sys-maint' \
"
mysql --defaults-extra-file=/var/backup/mysqlroot.cnf -sNe " \
SELECT \
CONCAT( '\'', User, '\'@\'', Host, '\'' ) as User FROM mysql.user \
WHERE \
User NOT LIKE 'mysql.%' \
AND CONCAT( User, Host ) <> 'rootlocalhost' \
AND User <> 'debian-sys-maint' \
" | sort | while read u ;
do echo "-- $u"; mysql --defaults-extra-file=/var/backup/mysqlroot.cnf -sNe "show grants for $u" | sed 's/$/;/'
done
And then write the following contents:
然后写出如下内容：
##代码##Then I just have to run it like this: /var/backup/backup_sql.sh > /tmp/exportusers.sql
然后我只需要像这样运行它：/var/backup/backup_sql.sh > /tmp/exportusers.sql
