Вопрос: Как я могу экспортировать привилегии из MySQL, а затем импортировать их на новый сервер?


Я знаю, как экспортировать / импортировать базы данных с помощью mysqldump, и это нормально, но как я могу получить привилегии на новый сервер.

Для дополнительных точек уже существует несколько существующих баз данных на новом, как мне импортировать старые привилегии серверов без использования пары существующих.

Старый сервер: 5.0.67-сообщество

Новый сервер: 5.0.51a-24 + lenny1

EDIT: у меня есть дамп db 'mysql' от старого сервера и теперь хочу знать правильный способ слияния с db mysql на новом сервере.

Я попробовал прямое «Импорт» с помощью phpMyAdmin и получил ошибку в отношении дубликата (тот, который я уже перенес вручную).

Кто-нибудь получил элегантный способ слияния двух баз данных «mysql»?


80
2018-05-16 06:08


Источник


1. Требуется ли вам использовать PHPMyAdmin? Если это так, я напишу вам некоторые конкретные инструкции PHPMyAdmin. 2. Из PHPMyAdmin, если вы попытаетесь «выбрать * из mysql.user limit 1;» вы получаете результаты или ошибку. - Bruno Bronosky
Как я упоминал ниже, я считаю, что сценарий mygrants Ричарда - хороший способ получить информацию о гранте. Тем не менее, вы также можете попробовать отредактировать файл дампа, чтобы прокомментировать INSERTs в таблице пользователей для пользователей, которые уже существуют. Затем будут скопированы привилегии для dbs, восстановленных со старого сервера. Если вы уже назначили привилегии вручную для некоторых из dbs, которые вы восстановили в новом окне, найдите эти имена таблиц в файлах привилегий и прокомментируйте их. Не забудьте потом flush_privileges. Хорошее описание mysql db: grahamwideman.com/gw/tech/mysql/perms/index.htm - nedm
Огромная информация в этой ссылке, спасибо. Отмеченный. - Bruno Bronosky


Ответы:


Не связывайтесь с mysql db. Там гораздо больше, чем просто таблица пользователей. Ваш лучший выбор - это "ПОКАЖИТЕ ГРАНТЫ FOR ". У меня есть много псевдонимов и функций обслуживания CLI в моем .bashrc (на самом деле это мои .bash_aliases, которые я использую в моем .bashrc). Эта функция:

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

Первая команда mysql использует SQL для генерации действительного SQL, который передается во вторую команду mysql. Затем вывод передается через sed для добавления замечаний.

Команда $ @ позволяет вам называть ее следующим: mygrants --host = prod-db1 --user = admin --password = secret

Вы можете использовать свой полный набор инструментов Unix, например:

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

Это правильный способ перемещения пользователей. Ваш MySQL ACL модифицирован с помощью чистого SQL.


163
2018-05-27 15:51



Это действительно хорошая вспомогательная функция bash, которая отлично работает. Возьмите вывод из этого и сможете работать на новом сервере, и привилегии будут введены правильно и точно. - Jeremy Bouse
Я люблю твою функцию, сегодня это сэкономило мне много работы. Спасибо Спасибо спасибо... - Fabio
Это здорово, но у него есть один большой недостаток. Дополнительный «\» добавляется к подчеркиваниям в именах баз данных, поэтому, если у вас есть, например, пользователь с определенными привилегиями в базе данных с именем foo_bar, он будет отображаться как foo \ _bar вместо foo_bar, поэтому он не будет импортирован правильно , По крайней мере, это произойдет, если вы сохраните вывод из сценария в файл SQL и затем импортируете его на новый сервер. Я не пробовал прямые трубопроводы экспорта и импорта в одной строке. - matteo
Будьте осторожны с этой командой. Если у вас есть user таблица пользователя с хостом %, но затем в db в таблице есть записи, где host является явным значением, эти записи в db таблица не извлекается с использованием этого метода. - Mitar
@matteo Добавить -r ко второй команде mysql в функции, а двойные escape-последовательности не будут выводиться mysql, например: mysql -r $@ - lifo


Существует два метода для извлечения SQL-грантов из экземпляра MySQL

СПОСОБ № 1

Вы можете использовать пт-шоу-гранты из Percona Toolkit

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

СПОСОБ № 2

Вы можете эмулировать pt-show-grants со следующими

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

Любой из этих методов даст чистый SQL-дамп MySQL-грантов. Осталось только выполнить скрипт на новом сервере:

mysql -uroot -p -A < MySQLUserGrants.sql

Попробуйте !!!


39
2018-06-18 18:24



pt-show-grant - это именно то, что вы хотите для этого, оно отлично работает. - Glenn Plas
Percona - просто чистая удивительность. - sjas
Но ответ №2 так же хорош и будет работать без дополнительного программного обеспечения! - sjas


Ответ Ричарда Броноски был очень полезен для меня. Большое спасибо!!!

Вот небольшая вариация, которая мне полезна. Это полезно для переноса пользователей, например. между двумя установками Ubuntu, работающими с phpmyadmin. Просто дамп привилегий для всех пользователей, кроме root, phpmyadmin и debian-sys-maint. Затем код

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



спасибо за это «без проблем». :) - ThorstenS
Если вы посмотрите на мой пример, где я grep rails_admin вы можете сделать вывод, как это сделать, не делая специальной функции для каждого случая кромки. Используйте параметр «инвертировать-матч» grep следующим образом: 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


Или, используйте percona-toolkit (бывший маакит) и используйте pt-show-grants (или mk-show-grants) для этой цели. Нет необходимости в громоздких сценариях и / или хранимых процедурах.


6
2018-05-01 12:08





Вы можете mysqldump базу данных mysql и импортировать ее в новую; потребуются flush_privileges или перезагрузка, и вы обязательно захотите создать резервную копию существующего mysq db.

Чтобы избежать удаления существующих привилегий, обязательно добавьте, а не заменять строки в таблицах привилегий (db, columns_priv, host, func и т. Д.).


5
2018-05-16 06:24



Спасибо @nedm. Похоже, что раздражающий сервер cPanel, который я пытаюсь вытащить dbs, не показывает db 'mysql'. В противном случае я бы протестировал и подтвердил ваш ответ. Как только я это выясню, я вернусь. Благодарю. - Gareth
Это несчастливо, но понятно, вам, вероятно, не разрешено обращаться к какой-либо базе данных, кроме тех, которые принадлежат вашему пользователю на общий db. - Dave Cheney
Ой, да, без доступа к «mysql» будет сложно делать что-либо, связанное с пользователями или разрешениями. У вас есть доступ к командной строке? Можете ли вы запустить mysqldump из терминала или командной строки (НЕ из оболочки mysql)? mysqldump -u username -ppassword mysql> mysqldump.sql - nedm
Db 'mysql' был доступен из Cpanel WHM, если я вошел в систему как «root». Оттуда я могу получить доступ к версии phpmyadmin, которая имеет базу данных «mysql», содержащую разрешения - Gareth
Слияние с существующей схемой mysql, данные, извлеченные из схемы mysql через mysqldump, почти наверняка будут проблематичными. Вы манипулируете сложной схемой с принудительными отношениями и т. Д. Эта схема настолько сложна, что они создали специальный синтаксис SQL (GRANT, REVOKE, DROP USER и т. Д.) Для решения этой проблемы. Однако ваш отрывок состоит только из инструкций INSERT. У меня заканчиваются персонажи. Нужно продолжать? - Bruno Bronosky


Вы также можете сделать это как хранимую процедуру:

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

и назовите его

$ mysql -p -e "CALL spShowGrants" mysql

затем проведите вывод через команду Richards sed, чтобы получить резервную копию привилегий.


4
2018-05-09 22:05





Хотя ответ @Richard Bronosky правильный, я столкнулся с этим вопросом после попытки переноса набора баз данных с одного сервера на другой, и решение было намного проще:

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

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

На этом этапе все мои данные были видны, если я вошел в систему как root, mysql.user в таблице было все, что я ожидал, но я не мог войти в систему как любой из других пользователей и нашел этот вопрос, предполагая, что мне придется повторно выпустить GRANT заявления.

Тем не менее, оказывается, что сервер mysql просто необходимо перезапустить для обновленных привилегий в mysql.* таблицы для вступления в силу:

server2$ sudo restart mysql

Надеюсь, это поможет кому-то еще достичь того, что должно быть простой задачей!


3
2017-09-03 01:16



О, и моя ситуация немного отличалась от OP, поскольку я не пытался объединить дамп на сервер с существующими базами данных / пользователями. - Tom
На самом деле вам не нужно перезапускать MySQLd для «обновления привилегий». Это можно сделать с помощью команды MySQL `flush privileges; ' - CloudWeavers
Проблема с этим подходом заключается в том, что он работает только тогда, когда исходная и конечная MySQL-версия одинакова. В противном случае могут возникнуть проблемы, поскольку в исходной таблице mysql.user есть разные столбцы, чем таблица назначения mysql.user, например. - Mitar


Как насчет скрипта PHP? :)

Посмотрите источник на этот скрипт, и у вас будут все перечисленные привилегии:

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