Frage Wie kann ich die Privilegien von MySQL exportieren und dann auf einen neuen Server importieren?


Ich weiß, wie man die Datenbanken mit mysqldump exportiert / importiert, und das ist in Ordnung, aber wie bekomme ich die Berechtigungen in den neuen Server.

Für zusätzliche Punkte gibt es bereits einige Datenbanken auf der neuen, wie importiere ich die alten Server-Privilegien, ohne die bestehenden Paare zu vernichten.

Alter Server: 5.0.67-Community

Neuer Server: 5.0.51a-24 + lenny1

EDIT: Ich habe einen Dump der db 'mysql' von der alten Server und jetzt wollen Sie wissen, die richtige Weise mit der 'mysql' db auf dem neuen Server zu verschmelzen.

Ich habe versucht, mit phpMyAdmin einen direkten 'Import' auszuführen und habe einen Fehler in Bezug auf ein Duplikat (eines, das ich bereits manuell migriert habe) angezeigt.

Jeder hat eine elegante Möglichkeit, die beiden MySQL-Datenbanken zusammenzuführen.


80
2018-05-16 06:08


Ursprung


1. Ist es eine Voraussetzung für Sie, PHPMyAdmin zu verwenden? Wenn es ist, werde ich einige PHPMyAdmin spezifische Anweisungen für Sie schreiben. 2. Von PHPMyAdmin, wenn Sie versuchen, "aus mysql.user limit 1;" auszuwählen Erhalten Sie Ergebnisse oder einen Fehler? - Bruno Bronosky
Wie ich unten erwähnt habe, denke ich, Richards Mygrants-Skript ist ein guter Weg, um Grant-Informationen zu erhalten. Sie können jedoch auch versuchen, die Speicherauszugsdatei zu bearbeiten, um INSERT-Einträge für Benutzer, die bereits vorhanden sind, aus der Benutzertabelle auszukommentieren. Berechtigungen für die dbs, die vom alten Server wiederhergestellt wurden, werden dann kopiert. Wenn Sie für einige der wiederhergestellten DBs bereits Berechtigungen manuell zugewiesen haben, suchen Sie in den Berechtigungsdateien nach diesen Tabellennamen und kommentieren Sie diese auch. Vergiss danach keine flush_privileges. Gute Beschreibung der mysql db unter: grahamwiddeman.com/gw/tech/mysql/perms/index.htm - nedm
Tolle Infos in diesem Link, danke. Markiert - Bruno Bronosky


Antworten:


Verwechseln Sie nicht die mysql db. Dort ist viel mehr los als nur die Benutzertabelle. Ihre beste Wette ist die "GRANTS ZEIGEN FOR "Befehl. Ich habe viele CLI Wartung Aliase und Funktionen in meinem. Bashrc (eigentlich meine. Bash_aliases, die ich in meinem. Bashrc). Diese Funktion:

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

Der erste mysql-Befehl verwendet SQL zum Generieren von gültigem SQL, das an den zweiten mysql-Befehl übergeben wird. Die Ausgabe wird dann durch sed geleitet, um hübsche Kommentare hinzuzufügen.

Mit dem $ @ im Befehl können Sie es wie folgt aufrufen: mygrants --host = prod-db1 --benutzer = admin --password = geheim

Sie können Ihr komplettes Unix-Tool-Kit wie folgt verwenden:

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

Das ist der richtige Weg, um Nutzer zu bewegen. Ihre MySQL-ACL ist mit reinem SQL modifiziert.


165
2018-05-27 15:51



Dies ist eigentlich eine nette Bash-Hilfsfunktion, die großartig funktioniert. Nehmen Sie die Ausgabe davon und in der Lage sein, auf dem neuen Server zu laufen, und die Berechtigungen würden richtig und genau eingegeben werden. - Jeremy Bouse
Ich liebe deine Funktion, heute hat es mir viel Arbeit erspart. Danke danke danke... - Fabio
Das ist großartig, aber es hat einen großen Fehler. Ein zusätzliches "\" wird zu Unterstrichen in Datenbanknamen hinzugefügt. Wenn Sie also beispielsweise einen Benutzer mit bestimmten Berechtigungen für eine Datenbank namens foo_bar haben, wird es als foo \ _bar anstelle von foo_bar gerendert, sodass es nicht korrekt importiert wird . Zumindest passiert dies, wenn Sie die Ausgabe von Ihrem Skript in eine SQL-Datei speichern und dann auf den neuen Server importieren. Ich habe die direkte Verrohrung von Export und Import nicht in einer einzigen Zeile ausprobiert. - matteo
Sei vorsichtig mit diesem Befehl. Wenn du drin bist user Tabelle einen Benutzer mit Host %, aber dann db Tabelle haben Sie Einträge wo host ist ein expliziter Wert, diese Einträge in dbTabelle wird nicht mit dieser Methode abgerufen. - Mitar
@matteo Hinzufügen -r zum 2. mysql-Befehl in der Funktion und die Doppel-Escapes werden nicht von ausgegeben mysql. z.B: mysql -r $@ - lifo


Es gibt zwei Methoden zum Extrahieren von SQL Grants aus einer MySQL-Instanz

METHODE 1

Sie können verwenden pt-show-Zuschüsse von Percona Toolkit

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

Methode # 2

Sie können emulieren pt-show-grants mit den folgenden

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

Beide Methoden erzeugen einen reinen SQL-Dump der MySQL-Grants. Alles, was noch zu tun ist, ist, das Skript auf einem neuen Server auszuführen:

mysql -uroot -p -A < MySQLUserGrants.sql

Versuche es !!!


40
2018-06-18 18:24



pt-show-grants ist genau das, was du dafür willst, es funktioniert super. - Glenn Plas
Percona ist einfach pure Großartigkeit. - sjas
Aber Antwort # 2 ist genauso gut und funktioniert ohne zusätzliche Software! - sjas


Richard Bronoskys Antwort war äußerst nützlich für mich. Danke vielmals!!!

Hier ist eine kleine Variation, die für mich nützlich war. Es ist hilfreich für die Übertragung von Benutzern z.B. zwischen zwei Ubuntu-Installationen, auf denen phpmyadmin läuft. Dump Privilegien für alle Benutzer abgesehen von root, phpmyadmin und debian-sys-maint. Der Code ist dann

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



Danke für diese "no brainer" Verbesserung. :) - ThorstenS
Wenn du dir mein Beispiel ansiehst, wo ich grep rails_admin Sie können ableiten, wie Sie dies tun, ohne für jeden Randfall eine spezielle Funktion zu definieren. Verwenden Sie greps "invert-match" -Option wie folgt: 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


Oder verwenden Sie percona-toolkit (früheres maatkit) und benutzen Sie pt-show-grants (oder mk-show-grants) zu diesem Zweck. Keine lästigen Skripte und / oder gespeicherten Prozeduren erforderlich.


6
2018-05-01 12:08





Sie können die 'mysql' Datenbank mysqldump und in den neuen importieren; Ein flush_privileges oder Neustart wird benötigt und Sie werden definitiv die vorhandene mysq db zuerst sichern wollen.

Stellen Sie sicher, dass Sie keine Zeilen in den Berechtigungstabellen (db, columns_priv, host, func usw.) anfügen, um zu vermeiden, dass Ihre vorhandenen Berechtigungen entfernt werden.


5
2018-05-16 06:24



Danke @nedm. Scheint so, als ob der nervende cPanel-Server, den ich versuche, die dbs auszuschalten, nicht die db 'mysql' anzeigt. Sonst würde ich deine Antwort testen und bestätigen. Sobald ich das herausgefunden habe, werde ich es überprüfen. Vielen Dank. - Gareth
Das ist bedauerlich, aber verständlich. Sie werden wahrscheinlich daran gehindert, auf irgendeine Datenbank zuzugreifen, außer auf diejenigen, die direkt Ihrem Benutzer in einer gemeinsam genutzten Datenbank gehören. - Dave Cheney
Autsch, ja, ohne Zugriff auf 'mysql' wird es schwierig sein, irgendetwas in Bezug auf Benutzer oder Berechtigungen zu tun. Haben Sie Zugriff auf die Befehlszeile? Können Sie mysqldump vom Terminal oder der Befehlszeile (NICHT von der mysql Shell) ausführen? mysqldump -u Benutzername -ppassword mysql> mysqldump.sql - nedm
Die db 'mysql' war über das Cpanel WHM zugänglich, wenn ich mich als 'root' angemeldet habe. Von dort kann ich auf eine Version von phpmyadmin zugreifen, die die Datenbank 'mysql' enthält, die Berechtigungen enthält - Gareth
Beim Zusammenführen in ein bestehendes MySQL-Schema sind Daten, die aus einem MySQL-Schema über mysqldump extrahiert werden, fast sicher problematisch. Sie manipulieren ein komplexes Schema mit erzwungenen Beziehungen usw. Dieses Schema ist in der Tat so komplex, dass es dedizierte SQL-Syntax (GRANT, REVOKE, DROP USER usw.) für den Umgang damit erstellt hat. Ihr Extrakt besteht jedoch nur aus INSERT-Anweisungen. Mir gehen hier die Charaktere aus. Brauche ich weiter? - Bruno Bronosky


Sie können es auch als gespeicherte Prozedur ausführen:

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

und rufe es mit

$ mysql -p -e "CALL spShowGrants" mysql

Verteilen Sie dann die Ausgabe über Richards sed-Befehl, um eine Sicherung der Berechtigungen zu erhalten.


4
2018-05-09 22:05





Während @Richard Bronoskys Antwort die richtige ist, bin ich auf diese Frage gestoßen, nachdem ich versucht hatte, eine Reihe von Datenbanken von einem Server auf einen anderen zu migrieren, und die Lösung war viel einfacher:

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

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

Zu diesem Zeitpunkt waren alle meine Daten sichtbar, wenn ich mich angemeldet habe root, das mysql.user Die Tabelle enthielt alles, was ich erwartet hatte, aber ich konnte mich als keiner der anderen Benutzer einloggen und fand diese Frage, wenn ich annahm, dass ich das Problem erneut stellen musste GRANT Aussagen.

Es stellt sich jedoch heraus, dass der mysql - Server lediglich für die aktualisierten Berechtigungen in der Datenbank neu gestartet werden musste mysql.* Tabellen wirksam werden:

server2$ sudo restart mysql

Hoffentlich hilft das jemand anderem, was eine einfache Aufgabe sein sollte!


3
2017-09-03 01:16



Oh, und meine Situation war etwas anders als bei den OPs, da ich nicht versuchte, einen Dump in einen Server mit vorhandenen Datenbanken / Benutzern zusammenzuführen. - Tom
Sie müssen MySQLd nicht neu starten, um die Berechtigungen zu aktualisieren. Dies kann mit dem MySQL-Befehl 'flush privileges' geschehen. - CloudWeavers
Das Problem bei diesem Ansatz besteht darin, dass er nur funktioniert, wenn die Quell- und die Ziel-MySQL-Version identisch sind. Andernfalls könnten Probleme auftreten, da die mysql.user-Quellentabelle beispielsweise andere Spalten als die mysql.user-Zieltabelle hat. - Mitar


Wie wäre es mit einem PHP-Skript? :)

Zeigen Sie die Quelle für dieses Skript an und Sie haben alle aufgeführten Berechtigungen:

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





Erstellen Sie eine Shell-Skriptdatei mit dem folgenden Code:

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

**** dann führe es auf der Shell so aus: Sie werden aufgefordert, das Root-Passwort zweimal einzugeben und dann wird das GRANTS SQL auf dem Bildschirm angezeigt. ****


2
2017-09-06 23:03





One-Liner macht ziemlich genau das gleiche wie das Awesome 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'

Nur auf Unix-Tools basierend, wird keine zusätzliche Software benötigt.

Führe aus einer Bash-Shell aus, angenommen, du hast eine Arbeit .my.cnf wo das Passwort deines mysql root Benutzer kann gelesen werden.


0
2018-04-13 18:33



Ich habe die Hälfte von @rolandomysqldbas Post dupliziert, nachdem ich ein halbes Jahr später zurückgekommen bin. - sjas