Vraag Hoe kan ik de rechten van MySQL exporteren en vervolgens importeren naar een nieuwe server?


Ik weet hoe ik de databases moet exporteren / importeren met mysqldump & dat is prima, maar hoe krijg ik de rechten in de nieuwe server.

Voor extra punten zijn er al een aantal bestaande databases over de nieuwe, hoe importeer ik de oude serverrechten zonder het bestaande paar te vernietigen.

Oude server: 5.0.67-community

Nieuwe server: 5.0.51a-24 + lenny1

EDIT: Ik heb een dump van de db 'mysql' van de oude server en wil nu weten wat de juiste manier is om samen te voegen met de 'mysql' db op de nieuwe server.

Ik probeerde een straight 'Import' met behulp van phpMyAdmin en eindigde met een fout met betrekking tot een duplicaat (een die ik al handmatig heb gemigreerd).

Iedereen heeft een elegante manier gekregen om de twee 'mysql'-databases samen te voegen?


80
2018-05-16 06:08


oorsprong


1. Is het een vereiste voor u om PHPMyAdmin te gebruiken? Als dit het geval is, zal ik een aantal PHPMyAdmin-specifieke instructies voor u schrijven. 2. Van PHPMyAdmin als u probeert "select * from mysql.user limit 1;" krijg je resultaten of een foutmelding. - Bruno Bronosky
Zoals ik hieronder vermeldde, denk ik dat Richard's mygrants-script een goede manier is om beursinformatie te krijgen. U kunt echter ook proberen het dumpbestand te bewerken om INSERT's aan de gebruikerstabel te geven voor gebruikers die al bestaan. Bevoegdheden voor de dbs die van de oude server zijn hersteld, worden vervolgens gekopieerd. Als u al handmatig rechten hebt toegewezen voor sommige van de dbs die u in het nieuwe vak hebt teruggezet, zoekt u naar deze tabelnamen in de privilege-bestanden en becommentarieert u deze ook. Vergeet naderhand geen flush_privileges. Goede beschrijving van de mysql db bij: grahamwideman.com/gw/tech/mysql/perms/index.htm - nedm
Geweldige info in die link, bedankt. Bladwijzer. - Bruno Bronosky


antwoorden:


Knoei niet met de mysql db. Er is veel meer aan de hand dan alleen de tabel met gebruikers. Je beste gok is de "TOON DEELNEMEN VOOR "opdracht. Ik heb veel CLI onderhoud aliassen en functies in mijn .bashrc (eigenlijk mijn .bash_aliases die ik bron in mijn .bashrc). Deze functie:

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

De eerste mysql-opdracht gebruikt SQL om geldige SQL te genereren die wordt doorgestuurd naar de tweede mysql-opdracht. De uitvoer wordt vervolgens via Sed geleid om mooie opmerkingen toe te voegen.

De $ @ in de opdracht laat je toe om het te noemen als: mygrants --host = prod-db1 --user = admin --password = geheim

Je kunt je volledige Unix-toolkit hier als volgt gebruiken:

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

Dat is de juiste manier om gebruikers te verplaatsen. Uw MySQL ACL is aangepast met pure SQL.


165
2018-05-27 15:51



Dit is eigenlijk een mooie bash-helperfunctie die geweldig werkt. Neem de uitvoer hiervan en voer deze uit op de nieuwe server en de rechten zouden correct en correct worden ingevoerd. - Jeremy Bouse
Ik hou van je functie, het heeft me vandaag veel werk bespaard. Dankje, dankje, dankje... - Fabio
Dit is geweldig, maar het heeft een grote tekortkoming. Een extra "\" is toegevoegd aan underscores in databasenamen, dus als u bijvoorbeeld een gebruiker hebt met specifieke rechten op een database met de naam foo_bar, wordt deze weergegeven als foo \ _bar in plaats van foo_bar, dus dat wordt niet correct geïmporteerd . Dit gebeurt in ieder geval als u de uitvoer van uw script in een SQL-bestand opslaat en vervolgens in de nieuwe server importeert. Ik heb niet de directe piping van export en import in een enkele regel geprobeerd. - matteo
Wees voorzichtig met dit commando. Als je in hebt user tabel een gebruiker met host %, maar dan in db tabel heb je vermeldingen waar host is een expliciete waarde, die vermeldingen in dbtabel worden niet opgehaald met behulp van deze methode. - Mitar
@matteo toevoegen -r naar de 2e mysql-opdracht in de functie en de dubbele escapes worden niet uitgevoerd door mysql. bijvoorbeeld: mysql -r $@ - lifo


Er zijn twee methoden om SQL Grants uit een MySQL-instantie te extraheren

METHODE # 1

Je kunt gebruiken PT-toon-subsidies van Percona Toolkit

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

METHODE # 2

Je kunt emuleren pt-show-grants met het volgende

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 produceren een pure SQL-dump van de MySQL-subsidies. Het enige dat u hoeft te doen, is het script op een nieuwe server uitvoeren:

mysql -uroot -p -A < MySQLUserGrants.sql

Probeer het eens !!!


40
2018-06-18 18:24



pt-show-grants is precies wat je hiervoor wilt, het werkt geweldig. - Glenn Plas
Percona is gewoon pure awesomeness. - sjas
Maar antwoord 2 is net zo goed en werkt zonder extra software! - sjas


Het antwoord van Richard Bronosky was zeer nuttig voor mij. Erg bedankt!!!

Hier is een kleine variatie die nuttig was voor mij. Het is nuttig voor het overbrengen van gebruikers, bijvoorbeeld tussen twee Ubuntu-installaties met phpmyadmin. Dump privileges voor alle gebruikers, behalve root, phpmyadmin en debian-sys-maint. De code is dan

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



bedankt voor deze "no brainer" verbetering. :) - ThorstenS
Als je naar mijn voorbeeld kijkt, waar ik grep rails_admin u kunt hieruit afleiden zonder een speciale functie te maken voor elke randbehuizing. Gebruik grep's "invert-match" optie zoals: 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


Of gebruik percona-toolkit (voorheen maatkit) en gebruik pt-show-grants (of mk-show-grants) met dat doel. Geen noodzaak voor omslachtige scripts en / of opgeslagen procedures.


6
2018-05-01 12:08





Je kunt mysqldump de 'mysql' database en importeren naar de nieuwe; een flush_privileges of opnieuw opstarten is vereist en je zult zeker eerst een back-up willen maken van de bestaande mysq db.

Om te voorkomen dat u uw bestaande privileges verwijdert, moet u ervoor zorgen dat u rijen toevoegt in plaats van vervangt in de privilege-tabellen (db, columns_priv, host, func, enz.).


5
2018-05-16 06:24



Bedankt @nedm. Het lijkt erop dat de vervelende cPanel-server die ik probeer de dbs uit te schakelen, de db 'mysql' niet laat zien. Anders zou ik je antwoord testen en bevestigen. Zodra ik dat uit heb kom ik terug. Bedankt. - Gareth
Dat is jammer, maar begrijpelijk, je hebt waarschijnlijk geen toegang tot een database maar de bestanden die direct eigendom zijn van je gebruiker op een gedeelde db. - Dave Cheney
Ouch, ja, zonder toegang tot 'mysql' zal het moeilijk zijn om iets te doen met betrekking tot gebruikers of toestemmingen. Heeft u toegang tot de opdrachtregel? Kun je mysqldump uitvoeren vanuit de terminal of opdrachtregel (NIET vanuit de mysql-shell)? mysqldump -u gebruikersnaam -ppassword mysql> mysqldump.sql - nedm
De db 'mysql' was toegankelijk via het Cpanel WHM als ik me aanmeldde als 'root'. Vanaf daar heb ik toegang tot een versie van phpmyadmin die de 'mysql'-database met rechten heeft - Gareth
Samengevoegd in een bestaand mysql-schema, is het vrijwel zeker dat de gegevens die via mysqldump zijn geëxtraheerd uit een mysql-schema problematisch zijn. U manipuleert een complex schema met afgedwongen relaties, enz. Dit schema is zo complex dat ze in feite toegewijde SQL-syntaxis creëerden (GRANT, REVOKE, DROP USER, enz.) Om ermee om te gaan. Uw uittreksel bestaat echter alleen uit INSERT-instructies. Ik heb hier bijna geen personages meer. Moet ik doorgaan? - Bruno Bronosky


Je zou het ook als opgeslagen procedure kunnen doen:

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

en noem het met

$ mysql -p -e "CALL spShowGrants" mysql

vervolgens pijp de output via Richards sed commando om een ​​back-up van de privileges te krijgen.


4
2018-05-09 22:05





Hoewel het antwoord van @Richard Bronosky de juiste is, kwam ik deze vraag tegen nadat ik probeerde een set databases van de ene naar de andere server te migreren en de oplossing veel eenvoudiger was:

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

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

Op dit moment waren al mijn gegevens zichtbaar als ik me had aangemeld als root, de mysql.user tafel had alles wat ik verwachtte, maar ik kon niet inloggen als een van de andere gebruikers en vond deze vraag in de veronderstelling dat ik het opnieuw zou moeten uitgeven GRANT statements.

Het bleek echter dat de mysql-server gewoon opnieuw moest worden opgestart voor de bijgewerkte bevoegdheden in de mysql.* tabellen die van kracht worden:

server2$ sudo restart mysql

Hopelijk helpt dat iemand anders te bereiken wat een eenvoudige taak zou moeten zijn!


3
2017-09-03 01:16



Oh, en mijn situatie was iets anders dan die van de OP's, in die zin dat ik niet probeerde een dump in een server samen te voegen met bestaande databases / gebruikers. - Tom
U hoeft MySQLd niet opnieuw te starten om de privileges bij te werken. Het kan worden gedaan met het MySQL-commando 'flush privileges'; - CloudWeavers
Het probleem met deze aanpak is dat het alleen werkt als de MySQL-versie van zowel de bron als de bestemming hetzelfde is. Anders hebt u mogelijk problemen omdat de bron mysql.user-tabel bijvoorbeeld andere kolommen heeft dan de mysql.user-tabel van bestemming. - Mitar


Wat dacht je van een PHP-script? :)

Bekijk de bron van dit script en u krijgt alle vermelde rechten:

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





maak een shell-scriptbestand met de volgende 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
#

**** voer het dan als volgt uit op de shell: u wordt gevraagd om tweemaal het root-wachtwoord in te voeren en vervolgens verschijnt de tekst GRANTS SQL op het scherm. ****


2
2017-09-06 23:03





One-liner doet eigenlijk hetzelfde als het geweldige 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'

Alleen op basis van Unix-tools is geen extra software nodig.

Uitvoeren vanuit een bash-shell, verondersteld heb je een werking .my.cnf waar het wachtwoord van uw mysql root gebruiker kan worden gelezen.


0
2018-04-13 18:33



ik heb de helft van het bericht van @rolandomysqldba gekopieerd na een half jaar later terug te komen, ik heb het me net gerealiseerd. - sjas