Question Comment puis-je exporter les privilèges de MySQL, puis les importer sur un nouveau serveur?


Je sais comment exporter / importer les bases de données en utilisant mysqldump & c'est bien, mais comment puis-je obtenir les privilèges sur le nouveau serveur.

Pour des points supplémentaires, il y a déjà quelques bases de données sur la nouvelle, comment puis-je importer les privilèges d'anciens serveurs sans modifier le couple existant.

Ancien serveur: 5.0.67-community

Nouveau serveur: 5.0.51a-24 + lenny1

EDIT: J'ai une copie de la base de données 'mysql' de l'ancien serveur et je veux maintenant savoir comment fusionner avec la base de données 'mysql' sur le nouveau serveur.

J'ai essayé une "importation" directe en utilisant phpMyAdmin et une erreur concernant un duplicata (une migraine que j'ai déjà migrée manuellement) s'est terminée.

Quelqu'un a-t-il un moyen élégant de fusionner les deux bases de données 'mysql'?


80
2018-05-16 06:08


origine


1. Est-il obligatoire pour vous d'utiliser PHPMyAdmin? Si c'est le cas, j'écrirai pour vous certaines instructions spécifiques à PHPMyAdmin. 2. Depuis PHPMyAdmin si vous essayez de "sélectionner * dans mysql.user limit 1;" obtenez-vous des résultats ou une erreur. - Bruno Bronosky
Comme je l'ai mentionné ci-dessous, je pense que le script mygrants de Richard est un bon moyen d'obtenir des informations sur les subventions. Cependant, vous pouvez également essayer de modifier le fichier de vidage pour mettre en commentaire les INSERT dans la table user pour les utilisateurs existants. Les privilèges des dbs restaurés à partir de l'ancien serveur seront alors copiés. Si vous avez déjà attribué manuellement les privilèges pour certaines des dbs que vous avez restaurées dans la nouvelle boîte, recherchez ces noms de table dans les fichiers de privilèges et commentez-les également. N'oubliez pas un flush_privileges après. Bonne description de la base de données mysql sur: grahamwideman.com/gw/tech/mysql/perms/index.htm - nedm
Excellente information dans ce lien, merci. Signé. - Bruno Bronosky


Réponses:


Ne plaisante pas avec la base de données mysql. Il y a beaucoup plus d'activités que la table des utilisateurs. Votre meilleur pari est le "SHOW SUBVENTIONS FOR ". J'ai beaucoup d'alias et de fonctions de maintenance CLI dans mon .bashrc (en fait, mes alias .bash que je source dans mon .bashrc). Cette fonction:

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

La première commande mysql utilise SQL pour générer un SQL valide qui est dirigé vers la deuxième commande mysql. La sortie est ensuite transmise par sed pour ajouter de jolis commentaires.

Le $ @ dans la commande vous permettra de l'appeler comme suit: mygrants --host = prod-db1 --user = admin --password = secret

Vous pouvez utiliser votre trousse à outils Unix complète sur ceci comme ceci:

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

C’est LA bonne façon de déplacer les utilisateurs. Votre ACL MySQL est modifiée avec du SQL pur.


165
2018-05-27 15:51



C’est en fait une fonction d’aide bash qui fonctionne très bien. Prendre la sortie de cela et pouvoir fonctionner sur le nouveau serveur et les privilèges seraient entrés correctement et avec précision. - Jeremy Bouse
J'adore ta fonction, aujourd'hui, cela m'a épargné beaucoup de travail. Merci merci merci... - Fabio
C'est génial mais il y a un gros défaut. Un "\" supplémentaire est ajouté aux traits de soulignement dans les noms de base de données. Ainsi, si vous avez par exemple un utilisateur avec des privilèges spécifiques sur une base de données appelée foo_bar, il sera affiché sous la forme foo \ _bar au lieu de foo_bar, de sorte qu'il ne soit pas importé correctement. . Cela se produit au moins si vous enregistrez la sortie de votre script dans un fichier SQL, puis l’importez dans le nouveau serveur. Je n'ai pas essayé la canalisation directe d'exportation et d'importation en une seule ligne. - matteo
Soyez prudent avec cette commande. Si vous avez dans user table un utilisateur avec hôte %, mais ensuite dans db table vous avez des entrées où host est une valeur explicite, ces entrées dans dbtable ne sont pas récupérés en utilisant cette méthode. - Mitar
@matteo Ajouter -r à la 2ème commande mysql dans la fonction et les doubles échappements ne seront pas générés par mysql. par exemple: mysql -r $@ - lifo


Il existe deux méthodes pour extraire des subventions SQL à partir d'une instance MySQL

METHODE N ° 1

Vous pouvez utiliser pt-show-grants de Percona Toolkit

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

MÉTHODE N ° 2

Vous pouvez imiter pt-show-grants avec ce qui suit

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

L'une ou l'autre méthode produira un dump SQL pur des octrois MySQL. Il ne reste plus qu'à exécuter le script sur un nouveau serveur:

mysql -uroot -p -A < MySQLUserGrants.sql

Essaie !!!


40
2018-06-18 18:24



pt-show-grants est exactement ce que vous voulez pour cela, cela fonctionne très bien. - Glenn Plas
Percona est tout simplement génial. - sjas
Mais la réponse n ° 2 est tout aussi bonne et fonctionnera sans logiciel supplémentaire! - sjas


La réponse de Richard Bronosky m'a été extrêmement utile. Merci beaucoup!!!

Voici une petite variation qui m'a été utile. Il est utile pour transférer des utilisateurs, par exemple. entre deux installations Ubuntu exécutant phpmyadmin. Il suffit de supprimer les privilèges de tous les utilisateurs, à l'exception de root, phpmyadmin et debian-sys-maint. Le code est alors

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



merci pour cette amélioration «pas de réflexion». :) - ThorstenS
Si vous regardez mon exemple où je grep rails_admin vous pouvez en déduire comment faire cela sans créer une fonction spéciale pour chaque cas de bord. Utilisez l'option "invert-match" de grep comme ceci: 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


Ou utilisez percona-toolkit (ancien maatkit) et utilisez pt-show-grants (ou mk-show-grants) Dans ce but. Pas besoin de scripts fastidieux et / ou de procédures stockées.


6
2018-05-01 12:08





Vous pouvez mysqldump la base de données 'mysql' et l'importer dans la nouvelle. un flush_privileges ou un redémarrage sera nécessaire et vous voudrez certainement sauvegarder la base de données mysq existante.

Pour éviter de supprimer vos privilèges existants, veillez à ajouter plutôt qu'à remplacer les lignes dans les tables de privilèges (db, columns_priv, hôte, func, etc.).


5
2018-05-16 06:24



Merci @nedm. On dirait que le serveur cPanel ennuyeux que j'essaie de supprimer de la base de données ne montre pas la base de données 'mysql'. Sinon, je testerais et confirmerais votre réponse. Une fois que j'ai compris ça, je vais vérifier. Merci. - Gareth
C'est malheureux mais compréhensible, vous ne pourrez probablement pas accéder à une base de données mais à celles qui appartiennent directement à votre utilisateur sur une base de données partagée. - Dave Cheney
Ouais, oui, sans accès à 'mysql', il sera difficile de faire quoi que ce soit en rapport avec les utilisateurs ou les permissions. Avez-vous un accès en ligne de commande? Pouvez-vous exécuter mysqldump à partir du terminal ou de la ligne de commande (PAS à partir du shell mysql)? mysqldump -u nom d'utilisateur -ppassword mysql> mysqldump.sql - nedm
La base de données 'mysql' était accessible à partir du WHM Cpanel si je me connectais en tant que 'root'. De là, je peux accéder à une version de phpmyadmin qui a la base de données 'mysql' contenant les permissions - Gareth
En fusionnant dans un schéma mysql existant, les données extraites d'un schéma mysql via mysqldump sont presque sûrement problématiques. Vous manipulez un schéma complexe avec des relations imposées, etc. Ce schéma est si complexe, en fait, qu'ils ont créé une syntaxe SQL dédiée (GRANT, REVOKE, DROP USER, etc.) pour le gérer. Cependant, votre extrait ne contient que des instructions INSERT. Je suis à court de caractères ici. Ai-je besoin de continuer? - Bruno Bronosky


Vous pouvez également le faire en tant que procédure stockée:

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

et appelez avec

$ mysql -p -e "CALL spShowGrants" mysql

puis dirigez la sortie via la commande Richards sed pour obtenir une sauvegarde des privilèges.


4
2018-05-09 22:05





Bien qu'il semble que la réponse de @Richard Bronosky soit la bonne, je suis tombé sur cette question après avoir tenté de migrer un ensemble de bases de données d'un serveur à un autre et la solution était beaucoup plus simple:

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

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

À ce stade, toutes mes données étaient visibles si je me connectais en tant que root, la mysql.user La table contenait tout ce à quoi je m'attendais, mais je ne pouvais pas me connecter en tant qu'autre des utilisateurs et j'ai trouvé cette question en supposant que je devais réémettre le message. GRANT déclarations.

Cependant, il s'avère que le serveur mysql doit simplement être redémarré pour les privilèges mis à jour dans mysql.* tableaux à prendre en compte:

server2$ sudo restart mysql

J'espère que cela aidera quelqu'un d'autre à accomplir ce qui devrait être une tâche simple!


3
2017-09-03 01:16



Oh, et ma situation était légèrement différente de celle des PO, en ce sens que je n'essayais pas de fusionner une sauvegarde dans un serveur avec des bases de données / utilisateurs existants. - Tom
Il n'est pas nécessaire de redémarrer MySQLd pour "mettre à jour les privilèges". Cela peut être fait avec la commande MySQL 'flush privileges'; - CloudWeavers
Le problème avec cette approche est qu’elle ne fonctionne que lorsque les versions source et cible de MySQL sont identiques. Sinon, vous pourriez avoir des problèmes parce que la table mysql.user source a des colonnes différentes de la table de destination mysql.user, par exemple. - Mitar


Que diriez-vous d'un script PHP? :)

Voir la source sur ce script et vous aurez tous les privilèges énumérés:

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





créez un fichier de script shell avec le code suivant:

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

**** puis l'exécuter sur le shell comme ceci: il vous sera demandé de saisir le mot de passe root deux fois, puis le code SQL de Subventions s'affichera à l'écran. ****


2
2017-09-06 23:03





One-Liner fait à peu près la même chose que le génial 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'

Basé uniquement sur les outils Unix, aucun logiciel supplémentaire n'est nécessaire.

Exécuter depuis un shell bash, en supposant que vous avez un .my.cnf où le mot de passe de votre mysql root l'utilisateur peut être lu.


0
2018-04-13 18:33



J'ai dupliqué la moitié du message de @rolandomysqldba après être revenu six mois plus tard, je viens de me rendre compte. - sjas