Pertanyaan Bagaimana saya bisa mengekspor hak istimewa dari MySQL dan kemudian mengimpor ke server baru?


Saya tahu cara mengekspor / mengimpor database menggunakan mysqldump & itu bagus tapi bagaimana cara mendapatkan hak istimewa ke server baru.

Untuk poin tambahan, ada beberapa database yang sudah ada di yang baru sudah, bagaimana cara mengimpor keistimewaan server lama tanpa nuking beberapa yang ada.

Server lama: 5.0.67-komunitas

Server baru: 5.0.51a-24 + lenny1

EDIT: Saya punya dump dari db 'mysql' dari Server Lama & sekarang ingin tahu cara yang tepat untuk bergabung dengan 'mysql' db di Server Baru.

Saya mencoba 'Impor' langsung menggunakan phpMyAdmin dan berakhir dengan kesalahan terkait duplikat (yang sudah saya pindahkan secara manual).

Adakah yang punya cara yang elegan untuk menggabungkan dua basis data 'mysql'?


80
2018-05-16 06:08




1. Apakah ini merupakan persyaratan bagi Anda untuk menggunakan PHPMyAdmin? Jika saya akan menulis beberapa petunjuk khusus PHPMyAdmin untuk Anda. 2. Dari PHPMyAdmin jika Anda mencoba "select * from mysql.user limit 1;" apakah Anda mendapatkan hasil atau kesalahan. - Bruno Bronosky
Seperti yang saya sebutkan di bawah ini, saya pikir tulisan Richard mygrants adalah cara yang baik untuk mendapatkan info hibah. Namun, Anda juga dapat mencoba mengedit file dump untuk mengomentari INSERT ke tabel pengguna untuk pengguna yang sudah ada. Hak istimewa untuk dbs yang dipulihkan dari server lama akan disalin. Jika Anda sudah menetapkan hak istimewa secara manual untuk beberapa dbs yang Anda pulihkan ke kotak baru, cari nama-nama tabel ini dalam file hak istimewa dan beri komentar ini juga. Jangan lupa flush_privileges sesudahnya. Deskripsi bagus tentang mysql db di: grahamwideman.com/gw/tech/mysql/perms/index.htm - nedm
Info hebat di tautan itu, terima kasih. Ditandai. - Bruno Bronosky


Jawaban:


Jangan main-main dengan mysql db. Ada lebih banyak yang terjadi di sana daripada hanya tabel pengguna. Taruhan terbaik Anda adalah "TAMPILKAN HIBAH UNTUK "perintah. Saya memiliki banyak alias dan fungsi pemeliharaan CLI di saya. Bashrc (sebenarnya saya .bash_aliases yang saya sumber di .bashrc saya.) Fungsi ini:

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

Perintah mysql pertama menggunakan SQL untuk menghasilkan SQL valid yang disalurkan ke perintah mysql kedua. Outputnya kemudian disalurkan melalui sed untuk menambahkan komentar cantik.

$ @ Dalam perintah akan memungkinkan Anda untuk memanggilnya sebagai: mygrants --host = prod-db1 --user = admin --password = rahasia

Anda dapat menggunakan tool tool unix lengkap Anda seperti ini:

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

Itu adalah cara yang benar untuk memindahkan pengguna. ACL MySQL Anda dimodifikasi dengan SQL murni.


165
2018-05-27 15:51



Ini sebenarnya adalah fungsi pembantu bash yang bagus dan berfungsi dengan baik. Ambil output dari itu dan dapat dijalankan di server baru dan hak istimewa akan dimasukkan dengan benar dan akurat. - Jeremy Bouse
Saya suka fungsi Anda, hari ini itu menyelamatkan saya banyak pekerjaan. Terima kasih terima kasih terima kasih... - Fabio
Ini bagus tetapi memiliki satu kelemahan besar. Tambahan "\" ditambahkan ke garis bawah dalam nama database, jadi jika Anda memiliki misalnya pengguna dengan hak khusus pada database yang disebut foo_bar, itu akan ditampilkan sebagai foo \ _bar bukan foo_bar, jadi itu tidak akan diimpor dengan benar . Setidaknya, ini terjadi jika Anda menyimpan output dari skrip Anda ke dalam file SQL dan kemudian mengimpornya ke server baru. Saya belum mencoba pemipaan langsung ekspor dan impor dalam satu baris. - matteo
Hati-hati dengan perintah ini. Jika Anda memilikinya user tabel pengguna dengan host %, tapi kemudian masuk db tabel Anda memiliki entri di mana host adalah nilai eksplisit, entri tersebut dalam dbtabel tidak diambil menggunakan metode ini. - Mitar
@matteo Tambah -r ke perintah mysql ke-2 dalam fungsi dan escapes ganda tidak akan dihasilkan oleh mysql. misalnya: mysql -r $@ - lifo


Ada dua metode untuk mengekstraksi Hibah SQL dari Instance MySQL

METODE # 1

Kamu bisa memakai pt-show-grants dari Percona Toolkit

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

METODE # 2

Anda dapat meniru pt-show-grants dengan yang berikut ini

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

Metode mana pun akan menghasilkan dump SQL murni dari hibah MySQL. Yang perlu dilakukan adalah menjalankan skrip pada server baru:

mysql -uroot -p -A < MySQLUserGrants.sql

Cobalah !!!


40
2018-06-18 18:24



pt-show-grants adalah apa yang Anda inginkan untuk ini, ini bekerja dengan baik. - Glenn Plas
Percona hanyalah kedahsyatan murni. - sjas
Tapi jawab # 2 sama bagusnya dan akan bekerja tanpa perangkat lunak tambahan! - sjas


Jawaban Richard Bronosky sangat berguna bagi saya. Terimakasih banyak!!!

Ini adalah variasi kecil yang berguna untuk saya. Ini berguna untuk mentransfer pengguna, mis. antara dua instalasi Ubuntu menjalankan phpmyadmin. Hanya membuang hak istimewa untuk semua pengguna selain dari root, phpmyadmin, dan debian-sys-maint. Kode itu kemudian

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



terima kasih atas perbaikan "tanpa otak" ini. :) - ThorstenS
Jika Anda melihat contoh saya di mana saya grep rails_admin Anda dapat menyimpulkan bagaimana melakukan ini tanpa membuat fungsi khusus untuk setiap kasus tepi. Gunakan opsi "invert-match" dari grep seperti: 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


Atau, gunakan perkona-toolkit (mantan maatkit) dan gunakan pt-show-grants (atau mk-show-grants) untuk alasan tersebut. Tidak perlu untuk skrip rumit dan / atau prosedur yang tersimpan.


6
2018-05-01 12:08





Anda dapat mysqldump database 'mysql' dan mengimpor ke yang baru; sebuah flush_privileges atau restart akan diperlukan dan Anda pasti ingin mem-backup mysq db yang ada terlebih dahulu.

Untuk menghindari penghapusan hak istimewa yang ada, pastikan untuk menambahkan alih-alih mengganti baris dalam tabel hak istimewa (db, columns_priv, host, func, dll.).


5
2018-05-16 06:24



Terima kasih, @nedm. Sepertinya server cPanel yang menjengkelkan saya mencoba untuk mendapatkan dbs off tidak menunjukkan db 'mysql'. Kalau tidak, saya akan menguji dan menegaskan jawaban Anda. Setelah saya mengetahui hal itu, saya akan memeriksa kembali. Terima kasih. - Gareth
Itu sangat disayangkan tetapi dapat dimengerti, Anda mungkin dicegah mengakses database apa pun tetapi yang langsung dimiliki oleh pengguna Anda pada db yang dibagikan. - Dave Cheney
Aduh, ya, tanpa akses ke 'mysql' itu akan sulit untuk melakukan sesuatu yang berhubungan dengan pengguna atau perizinan. Apakah Anda memiliki akses baris perintah? Dapatkah Anda menjalankan mysqldump dari terminal atau baris perintah (BUKAN dari shell mysql)? nama pengguna mysqldump -u -ppassword mysql> mysqldump.sql - nedm
The db 'mysql' dapat diakses dari Cpanel WHM jika saya login sebagai 'root'. Dari sana saya dapat mengakses versi phpmyadmin yang memiliki database 'mysql' yang berisi izin - Gareth
Menggabungkan ke skema mysql yang ada, data yang diekstrak dari skema mysql melalui mysqldump hampir pasti akan bermasalah. Anda memanipulasi skema kompleks dengan hubungan yang dipaksakan, dll. Skema ini sangat kompleks, pada kenyataannya, mereka membuat sintaks SQL khusus (GRANT, REVOKE, DROP USER, dll.) Untuk menghadapinya. Ekstrak Anda, bagaimanapun, hanya terdiri dari pernyataan INSERT. Saya kehabisan karakter di sini. Perlu saya lanjutkan? - Bruno Bronosky


Anda juga bisa melakukannya sebagai prosedur tersimpan:

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

dan menyebutnya dengan

$ mysql -p -e "CALL spShowGrants" mysql

kemudian pipa output melalui Richards sed perintah untuk mendapatkan cadangan dari hak istimewa.


4
2018-05-09 22:05





Meskipun tampaknya jawaban @Richard Bronosky adalah jawaban yang benar, saya menemukan pertanyaan ini setelah mencoba memigrasikan satu set basis data dari satu server ke yang lain dan solusinya jauh lebih sederhana:

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

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

Pada titik ini, semua data saya terlihat jika saya masuk sebagai root, yang mysql.user tabel memiliki semua yang saya harapkan, tapi saya tidak bisa login sebagai salah satu pengguna lain dan menemukan pertanyaan ini dengan asumsi saya harus kembali mengeluarkan GRANT pernyataan.

Namun, ternyata server mysql hanya perlu direstart untuk hak istimewa yang diperbarui di mysql.* tabel untuk diterapkan:

server2$ sudo restart mysql

Semoga itu membantu orang lain mencapai apa yang seharusnya menjadi tugas yang sederhana!


3
2017-09-03 01:16



Oh, dan situasi saya sedikit berbeda dengan OP, karena saya tidak mencoba untuk menggabungkan dump ke server dengan database / pengguna yang ada. - Tom
Anda tidak benar-benar harus me-restart MySQLd untuk 'memperbarui hak istimewa'. Ini bisa dilakukan dengan perintah MySQL 'hak istimewa siram;' - CloudWeavers
Masalah dengan pendekatan ini adalah bahwa ia bekerja hanya ketika kedua versi MySQL sumber dan tujuan adalah sama. Kalau tidak, Anda mungkin memiliki masalah karena sumber mysql.user tabel memiliki kolom yang berbeda dari meja tujuan mysql.user, misalnya. - Mitar


Bagaimana dengan skrip PHP? :)

Lihat sumber pada skrip ini dan Anda akan memiliki semua hak istimewa yang terdaftar:

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





buat file skrip shell dengan kode berikut:

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

**** lalu jalankan di shell seperti ini: Anda akan diminta untuk memasukkan kata sandi root dua kali dan kemudian GRANTS SQL akan ditampilkan di layar. ****


2
2017-09-06 23:03





Satu-kapal melakukan hampir sama dengan yang luar biasa 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'

Hanya berdasarkan perangkat unix, tidak diperlukan perangkat lunak tambahan.

Jalankan dari dalam shell bash, diasumsikan Anda memiliki kerja .my.cnf di mana kata sandi Anda mysql root pengguna dapat dibaca.


0
2018-04-13 18:33



Saya menduplikasi setengah dari posting @rolandomysqldba setelah kembali setengah tahun kemudian, saya baru sadar. - sjas