mysql - una - Cómo exportar e importar un usuario existente(¡con sus privilegios!)

importar exportar base de datos mysql consola

Question

Tengo una instancia de MySQL (prueba) existente, que contiene 2 bases de datos y unos pocos usuarios, cada uno con diferentes privilegios de acceso a cada base de datos.

Ahora necesito duplicar una de las bases de datos (en producción) y los usuarios asociados con ella.

Duplicar la base de datos fue easy :

Exportar:

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

Importar:

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

Sin embargo, no encontré una forma sencilla de exportar e importar usuarios , desde la línea de comandos (ya sea dentro o fuera de mysql).

¿Cómo puedo exportar e importar un user , desde la línea de comandos?

Actualización : Hasta ahora, he encontrado pasos manuales (y, por lo tanto, propensos a errores) para lograr esto:

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

Luego encuentra sus becas:

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

Luego, cree manualmente el usuario con las concesiones enumeradas en el resultado del comando ''mostrar concesiones'' anterior.

Prefiero una forma más segura, más automatizada. Hay uno

Answer 1

Abordé esto con un pequeño programa de C #. Aquí hay un código para generar un script o aplicar las subvenciones directamente desde el origen al destino. Si realiza la migración desde un entorno Windows -> * nix, es posible que deba tener en cuenta los problemas de sensibilidad de los casos.

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;
}
}
}

con un app.config que contiene ...

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

Answer 2

Sin embargo, otro bash one-liner para uso de linux en lugar de la herramienta Percona:

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

Answer 3

Una de las formas más fáciles que he encontrado para exportar usuarios es usando la herramienta pt-show-grants de Percona. El kit de herramientas de Percona es gratuito, fácil de instalar y fácil de usar, con una gran cantidad de documentación. Es una forma fácil de mostrar a todos los usuarios, o usuarios específicos. Enumera todas sus subvenciones y salidas en formato SQL. Daré un ejemplo de cómo mostraría todas las subvenciones para test_user:

shell> pt-show-grants --only test_user

Ejemplo de salida de ese comando:

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

Por lo general, vuelvo a escribir la salida en un archivo para poder editar lo que necesito, o cargarlo en mysql.

Alternativamente, si no desea usar la herramienta Percona y desea hacer un volcado de todos los usuarios, podría usar mysqldump de esta manera:

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

Nota: --flush-privileges no funcionará con esto, ya que la db completa no se está volcando. esto significa que necesitas ejecutarlo manualmente.

shell> mysql -e "FLUSH PRIVILEGES"

Answer 4

Una secuencia de comandos de PHP para recorrer sus usuarios y obtener los comandos de concesión sería como tal:

// 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);

Gist: https://gist.github.com/zaiddabaeen/e88a2d10528e31cd6692

Answer 5

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

El script anterior se ejecutará en el entorno de Linux y la salida será user_privileges_final.sql que puede importar en el nuevo servidor mysql donde desea copiar los privilegios de usuario.