如何导出和导入现有用户(及其特权!)

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

现在我需要复制与之相关的一个数据库(进入生产环境) 还有用户。

复制数据库的是 放松:

出口:

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”命令的结果中列出的授权创建用户。

我更喜欢更安全，更自动化的方式，有吗？

126562 次浏览
小开
最佳答案

我发现导出用户最简单的方法之一是使用 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

注意: ——杂凑特权对此不起作用，因为没有转储整个数据库。这意味着您需要手动运行它。

shell> mysql -e "FLUSH PRIVILEGES"
小开 
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 $0";"}'  user_privileges.txt >user_privileges_final.sql
rm user.txt user_list_with_header.txt user_grant.txt user_privileges.txt

上面的脚本将在 Linux 环境中运行，输出将是 user _ rights _ final.sql，您可以将其导入到新的 mysql 服务器中，以便在其中复制用户权限。

更新: 第2个 mysql 语句的用户缺少 -。

小开

我用一个小的 C # 程序解决了这个问题。这里有生成脚本或直接从源到目标应用授权的代码。如果从 Windows-> * nix 环境移植，可能需要考虑大小写敏感性问题。

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;


}
}
}

使用 app.config 包含..。

    <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>
小开

循环访问用户以获得 Grant 命令的 PHP 脚本如下:

// 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();
while ($row2 = $statement2->fetch())
{
$export[] = $row2[0];
}
}


$link = null;
echo implode(";\n", $export);

要点: https://gist.github.com/zaiddabaeen/e88a2d10528e31cd6692

小开

另一个 linux 的 bash 一行程序，你可以用它来代替 Percona 工具:

mysql -u<user> -p<password> -h<host> -N mysql -e "select concat(\"'\", user, \"'@'\", host, \"'\"), coalesce(password, 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\)$/\1;/' ; done
小开

你可以使用 phpMyAdmin。

登录并转到用户可以访问的数据库或表。

选择特权

所有有权限的用户都在那里。

选择“导出”。一个小窗口中的所有拨款都已经准备好可以复制和粘贴了。

小开

我也有同样的问题。解决方案是，在导入备份之后，您需要做一个“刷新特权;”。然后，用户的特权将像在原始数据库中一样处于活动状态。

因此，执行:

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

mysql -u root

然后在 mysql 中: flush privileges;

小开

作为@Sergey-Podushkin 回答的补充，这个 shell 脚本代码对我来说很有用:

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\)$/\1;/'; done
小开

下面是我最近在日常备份脚本中使用的内容(需要 root shell 和 MySQL 访问、 linux shell，并使用 MySQL 内置模式:

首先，我创建了一个包含 root 密码的文件/var/back/mysqlroot.cnf，这样我就可以自动执行我的脚本，而不必在其中硬编码任何密码:

[client]
password=(put your password here)

然后我创建一个导出脚本，它转储创建用户命令和授权，如下所示:

touch /var/backup/backup_sql.sh
chmod 700 /var/backup/backup_sql.sh
vi /var/backup/backup_sql.sh

然后写下以下内容:

#!/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

然后我只需要这样运行它: /var/back/back _ sql. sh >/tmp/exportusers.sql

小开 
pass=your_password_here; \
MYSQL_PWD=$pass mysql -B -N -uroot -e "SELECT CONCAT('\'', user,'\' ','\'', host,'\' ','\'', authentication_string,'\' ','\'', plugin,'\'') FROM mysql.user WHERE user != 'debian-sys-maint' AND user != 'root' AND user != 'mysql.sys' AND user != 'mysql.session' AND user != ''" > mysql_all_users.txt; \
while read line; do linearray=(${line}); \
MYSQL_PWD=$pass mysql -B -N -uroot -e "SELECT CONCAT('CREATE USER \'',${linearray[0]},'\'@\'',${linearray[1]},'\' IDENTIFIED WITH \'',${linearray[3]},'\' AS \'',${linearray[2]},'\'')"; \
done < mysql_all_users.txt > mysql_all_users_sql.sql; \
while read line; do linearray=(${line}); \
MYSQL_PWD=$pass mysql -B -N -uroot -e "SHOW GRANTS FOR ${linearray[0]}@${linearray[1]}"; \
done < mysql_all_users.txt >> mysql_all_users_sql.sql; \
sed -e 's/$/;/' -i mysql_all_users_sql.sql; \
echo 'FLUSH PRIVILEGES;' >> mysql_all_users_sql.sql; \
unset pass

第一个 mysql 命令: 将所有用户导出到文件中并排除一些。
第二个 mysql 命令: 循环用户从文件将 sql 命令“ create user”写入导出文件(使用身份验证凭据)。
第三个 mysql 命令: 从文件循环用户将他们的特权附加到导出的文件。
Sed 命令将一个“ ;”附加到行的末尾，并刷新要完成的特权。
导入: MYSQL _ PWD = $pass MYSQL-u root < MYSQL _ all _ users _ sql. sql

小开

在 mysql 5.7和更高版本中，您可以使用这个。

mysqlpump -uroot -p${yourpasswd} --exclude-databases=% --users

这将生成一个 sql 格式的输出，您可以将其重定向到 mysql _ users.sql。

注意，它是 Mysqlump而不是 mysqldump。

小开 
SELECT CONCAT('\create user ', user,'\'@\'', host, '\' identified by ', "'", authentication_string, "'"'\;') FROM user WHERE user != 'mysql.session' AND user !='mysql.sys'  AND user != 'root' AND user != '';

提交答案