Pregunta ¿Cómo puedo exportar los privilegios de MySQL y luego importarlos a un nuevo servidor?


Sé cómo exportar / importar las bases de datos usando mysqldump y eso está bien, pero ¿cómo puedo obtener los privilegios en el nuevo servidor?

Para obtener puntos adicionales, ya hay un par de bases de datos existentes en la nueva, ¿cómo importo los privilegios de los servidores antiguos sin dañar la pareja existente?

Servidor antiguo: 5.0.67-comunidad

Nuevo servidor: 5.0.51a-24 + lenny1

EDITAR: Tengo un volcado de la base de datos 'mysql' del servidor antiguo y ahora quiero saber la forma correcta de fusionarse con la base de datos 'mysql' en el nuevo servidor.

Probé una 'Importación' directa usando phpMyAdmin y terminé con un error relacionado con un duplicado (uno que ya he migrado manualmente).

¿Alguien tiene una forma elegante de fusionar las dos bases de datos 'mysql'?


80
2018-05-16 06:08


origen


1. ¿Es un requisito para usted usar PHPMyAdmin? Si es así, escribiré algunas instrucciones específicas de PHPMyAdmin para usted. 2. Desde PHPMyAdmin si intenta "seleccionar * de mysql.user limit 1;" ¿Obtienes resultados o un error? - Bruno Bronosky
Como mencioné a continuación, creo que el script mygrants de Richard es una buena manera de obtener información de la subvención. Sin embargo, también puede intentar editar el archivo de volcado para comentar INSERT a la tabla de usuarios para los usuarios que ya existen. Los privilegios para los dbs restaurados desde el servidor antiguo se copiarán. Si ya ha asignado privilegios manualmente para algunos de los dbs que restauró en el nuevo cuadro, busque estos nombres de tabla en los archivos de privilegios y comente estos también. No olvides un flush_privileges después. Buena descripción de la db mysql en: grahamwideman.com/gw/tech/mysql/perms/index.htm - nedm
Gran información en ese enlace, gracias. Marcado - Bruno Bronosky


Respuestas:


No te metas con la db mysql. Hay mucho más en juego que solo la tabla de usuarios. Tu mejor apuesta es el "Mostrar subvenciones Para "comando. Tengo muchos alias de mantenimiento de la CLI y funciones en mi .bashrc (en realidad mis .bash_aliases que obtengo en mi .bashrc). Esta función:

mygrants()
{
  mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR \'', user, '\'@\'', host, '\';'
    ) AS query FROM mysql.user" | \
  mysql $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}

El primer comando mysql usa SQL para generar un SQL válido que se canaliza al segundo comando mysql. La salida se canaliza a través de sed para agregar comentarios bonitos.

El $ @ en el comando te permitirá llamarlo como: mygrants --host = prod-db1 --user = admin --password = secret

Puede usar su kit completo de herramientas de Unix en esto así:

mygrants --host=prod-db1 --user=admin --password=secret | grep rails_admin | mysql --host=staging-db1 --user=admin --password=secret

Esa es la forma correcta de mover a los usuarios. Su MySQL ACL se modifica con SQL puro.


165
2018-05-27 15:51



Esta es en realidad una buena función de ayuda de bash que funciona muy bien. Tome la salida de eso y sea capaz de ejecutarse en el nuevo servidor y los privilegios se ingresarán de manera adecuada y precisa. - Jeremy Bouse
Me encanta tu función, hoy me ahorró mucho trabajo. Gracias, gracias, gracias... - Fabio
Esto es genial pero tiene un gran defecto. Se agrega un "\" adicional a los guiones bajos en los nombres de la base de datos, por lo que si tiene, por ejemplo, un usuario con privilegios específicos en una base de datos llamada foo_bar, se representará como foo \ _bar en lugar de foo_bar, por lo que no se importará correctamente . Al menos, esto sucede si guarda la salida de su script en un archivo SQL y luego lo importa al nuevo servidor. No he probado la canalización directa de exportación e importación en una sola línea. - matteo
Ten cuidado con este comando. Si tienes en user tabla un usuario con host %, pero luego en db tabla tienes entradas donde host es un valor explícito, esas entradas en dbtabla no se recupera utilizando este método. - Mitar
@matteo Añadir -r al segundo comando mysql en la función y los escapes dobles no serán enviados por mysql. p.ej: mysql -r $@ - lifo


Hay dos métodos para extraer las subvenciones de SQL de una instancia de MySQL

MÉTODO 1

Puedes usar pt-show-subvenciones de Percona Toolkit

MYSQL_CONN="-uroot -ppassword"
pt-show-grants ${MYSQL_CONN} > MySQLUserGrants.sql

MÉTODO # 2

Puedes emular pt-show-grants con lo siguiente

MYSQL_CONN="-uroot -ppassword"
mysql ${MYSQL_CONN} --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql ${MYSQL_CONN} --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

Cualquiera de los dos métodos producirá un volcado de SQL puro de las concesiones de MySQL. Todo lo que queda por hacer es ejecutar el script en un nuevo servidor:

mysql -uroot -p -A < MySQLUserGrants.sql

Darle una oportunidad !!!


40
2018-06-18 18:24



pt-show-grants es exactamente lo que quieres para esto, funciona muy bien. - Glenn Plas
Percona es pura maravilla. - sjas
¡Pero la respuesta # 2 es igual de buena y funcionará sin software adicional! - sjas


La respuesta de Richard Bronosky fue extremadamente útil para mí. ¡¡¡Muchas gracias!!!

Aquí hay una pequeña variación que fue útil para mí. Es útil para transferir usuarios, por ejemplo. entre dos instalaciones de Ubuntu ejecutando phpmyadmin. Simplemente descargue los privilegios para todos los usuarios aparte de root, phpmyadmin y debian-sys-maint. El código es entonces

mygrants()
{
mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
'SHOW GRANTS FOR ''', user, '''@''', host, ''';'
) AS query FROM mysql.user WHERE user NOT IN ('root','phpmyadmin','debian-sys-maint')"  | \
mysql $@ | \
sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}

14
2017-09-02 20:27



Gracias por esta mejora "sin complicaciones". :) - ThorstenS
Si miras mi ejemplo donde yo grep rails_admin puede inferir cómo hacer esto sin realizar una función especial para cada caso de borde. Use la opción "invertir-coincidencia" de grep así: mygrants --host=prod-db1 --user=admin --password=secret | grep -Ev 'root|phpmyadmin|debian-sys-maint' | mysql --host=staging-db1 --user=admin --password=secret - Bruno Bronosky


O bien, utilice percona-toolkit (anteriormente maatkit) y use pt-show-grants (o mk-show-grants) para ese propósito. No hay necesidad de scripts engorrosos y / o procedimientos almacenados.


6
2018-05-01 12:08





Puede mysqldump la base de datos 'mysql' e importar a la nueva; se requerirá un flush_privileges o reinicio y definitivamente querrá hacer una copia de seguridad de la base de datos mysq existente primero.

Para evitar eliminar sus privilegios existentes, asegúrese de agregar en lugar de reemplazar filas en las tablas de privilegios (db, columns_priv, host, func, etc.).


5
2018-05-16 06:24



Gracias @nedm. Parece que el molesto servidor cPanel que estoy tratando de quitar las dbs no muestra la db 'mysql'. De lo contrario probaría y afirmaría tu respuesta. Una vez que lo averigüe lo comprobaré de nuevo. Gracias. - Gareth
Esto es desafortunado pero comprensible. Probablemente se le impida acceder a cualquier base de datos que no sea la propiedad directa de su usuario en una base de datos compartida. - Dave Cheney
Ouch, sí, sin acceso a 'mysql' será difícil hacer algo relacionado con los usuarios o permisos. ¿Tienes acceso a la línea de comandos? ¿Se puede ejecutar mysqldump desde el terminal o la línea de comandos (NO desde el shell mysql)? mysqldump -u nombre de usuario -ppassword mysql> mysqldump.sql - nedm
Se pudo acceder a la base de datos "mysql" desde el WHM de Cpanel si inicié sesión como "raíz". Desde allí puedo acceder a una versión de phpmyadmin que tiene la base de datos 'mysql' que contiene permisos - Gareth
Al fusionarse con un esquema mysql existente, es casi seguro que los datos extraídos de un esquema mysql a través de mysqldump serán problemáticos. Está manipulando un esquema complejo con relaciones forzadas, etc. Este esquema es tan complejo, de hecho, que crearon una sintaxis SQL dedicada (GRANT, REVOKE, DROP USER, etc.) para tratarlo. Su extracto, sin embargo, consiste solo en sentencias INSERT. Me estoy quedando sin personajes aquí. ¿Necesito continuar? - Bruno Bronosky


También puedes hacerlo como un procedimiento almacenado:

CREATE PROCEDURE spShowGrants()
    READS SQL DATA
    COMMENT 'Show GRANT statements for users'
BEGIN
    DECLARE v VARCHAR(64) CHARACTER SET utf8;
    DECLARE c CURSOR FOR
    SELECT DISTINCT CONCAT(
        'SHOW GRANTS FOR ', user, '@', host, ';'
    ) AS query FROM mysql.user;
    DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;  
    OPEN c;
    WHILE TRUE DO
        FETCH c INTO v;
        SET @v = v;
        PREPARE stmt FROM @v;
        EXECUTE stmt;
    END WHILE;
    CLOSE c;
END

y llámalo con

$ mysql -p -e "CALL spShowGrants" mysql

luego canalice la salida a través del comando sed de Richards para obtener una copia de seguridad de los privilegios.


4
2018-05-09 22:05





Si bien parece que la respuesta de @Richard Bronosky es la correcta, encontré esta pregunta después de intentar migrar un conjunto de bases de datos de un servidor a otro y la solución fue mucho más simple:

server1$ mysqldump -u root -p --all-databases > dbdump.sql

server2$ mysql -u root -p < dbdump.sql

En este punto, todos mis datos estaban visibles si inicié sesión como root, la mysql.user La tabla tenía todo lo que esperaba, pero no pude iniciar sesión como ninguno de los otros usuarios y encontré esta pregunta asumiendo que tendría que volver a emitir el GRANT declaraciones

Sin embargo, resulta que el servidor mysql simplemente necesitaba reiniciarse para los privilegios actualizados en el mysql.* Tablas a surtir efecto:

server2$ sudo restart mysql

¡Esperemos que eso ayude a alguien más a lograr lo que debería ser una tarea simple!


3
2017-09-03 01:16



Ah, y mi situación era ligeramente diferente a la de los OP, ya que no estaba intentando fusionar un volcado en un servidor con bases de datos / usuarios existentes. - Tom
No es necesario reiniciar MySQLd para 'actualizar los privilegios'. Se puede hacer con el comando MySQL 'flush privileges'; - CloudWeavers
El problema con este enfoque es que funciona solo cuando la versión de MySQL de origen y de destino es la misma. De lo contrario, es posible que tenga problemas porque la tabla mysql.user de origen tiene columnas diferentes a la tabla mysql.user de destino, por ejemplo. - Mitar


¿Qué tal un script PHP? :)

Vea la fuente en este script y tendrá todos los privilegios en la lista:

//connect
mysql_select_db("mysql", mysql_connect("localhost","root",""));

//create grants select statements
$rs = mysql_query("SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') AS query FROM user");

//iterate through grants
while ($row=mysql_fetch_array($rs)) {
    //run grant query
    $rs2 = mysql_query($row['query']);
    //iterate through results
    while($row2 = mysql_fetch_array($rs2)){
        //print results
        echo $row2[0] . ";\n\n";
    }
}

3
2018-05-03 18:11





crear un archivo de script de shell con el siguiente código:

############################################## 3
echo "SELECT DISTINCT CONCAT (\"show grants for '\", user, \"'@'\", host, \"';\") AS query FROM mysql.user; " >   script.sql    
echo "*** You will be asked to enter the root password twice ******"    
mysql -u root -p  < script.sql > output.sql ;    
cat output.sql | grep show > output1.sql  ; rm output.sql -f ; 
mysql -u root -p  < output1.sql > output.sql ;
clear
echo "-----Exported Grants-----"    
cat  output.sql ; rm  output.sql   output1.sql -f    
echo "-------------------------"
rm  script.sql -f
#

**** entonces ejecútalo en la cáscara así: se le pedirá que ingrese la contraseña de root dos veces y luego se mostrará GRANTS SQL en la pantalla. ****


2
2017-09-06 23:03





One-liner haciendo casi lo mismo que el impresionante pt-show-grants:

mysql --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql --skip-column-names -A | sed 's/$/;/g'

Basado solo en herramientas de Unix, no se necesita software adicional.

Ejecutar desde dentro de un shell bash, asumiendo que tienes un trabajo .my.cnf donde la contraseña de tu mysql root El usuario puede ser leído.


0
2018-04-13 18:33



Dupliqué la mitad del post de @rolandomysqldba después de regresar medio año después, me di cuenta. - sjas