Приветствую уважаемое Хабросообщество! Как известно у сервера MySql нет встроенных механизмов для переноса пользователей MySql и их привилегий на другой сервер. В сети готовых решений крайне мало, и в этой небольшой статье мы перенесем наших пользователей MySql и их привилегии, с одного сервера на другой, за несколько минут.
Актуальность данной задачи описывать особо смысла нет. Если сами базы данных можно перенести кучей способов, то с пользователями, если их много, возникает проблема.
Вообще задача тривиальна: на любом языке программирования, работающим с MySql, запустить скрипт, который вытащит из базы mysql пользователей и их привилегии. Мне проще работать с php.
За основу взят gist от Zaid Daba'een
Если версия MySql
Больше 5.6, например 5.7
То раскоментируйте строку:
И закомментируйте:
Иначе оставьте всё как есть.
Начиная с версии MySql 5.7 пароль пользователя хранится в поле authentication_string, до этого в поле password.
Запустим скрипт на исполнение и скопируем вывод, получится что-то вроде:
От греха подальше удалим скрипт и изучим полученный вывод. Удалим все записи с системными пользователями, включая пользователя root.
Тем или иным способом внесем данные на новый сервер MySQL.
Всё! Отписываемся в комментариях о ваших способах решения данной задачи. Приветствуется любая конструктивная критика.
Актуальность данной задачи описывать особо смысла нет. Если сами базы данных можно перенести кучей способов, то с пользователями, если их много, возникает проблема.
Вообще задача тривиальна: на любом языке программирования, работающим с MySql, запустить скрипт, который вытащит из базы mysql пользователей и их привилегии. Мне проще работать с php.
За основу взят gist от Zaid Daba'een
Код php скрипта
<?php
/** Feel free to improve it.
* Original by Janich: https://gist.github.com/janich/6121771
*
* @requires PHP 5.3+
* @package ExportMySQLUsers
* @author Zaid Daba'een
* @license http://www.dbad-license.org/ DBAD license
*/
// Set up database root credentials
$host = 'localhost';
$user = 'root';
$pass = 'your_passwd';
// ---- Do not edit below this ----
// Misc settings
header('Content-type: text/plain; Charset=UTF-8');
// Final import queries goes here
$export = array();
// Connect to database
try {
$link = new PDO("mysql:host=$host;dbname=mysql", $user, $pass);
} catch (PDOException $e) {
printf('Connect failed: %s', $e->getMessage());
die();
}
// Get users from database
//$statement = $link->prepare("select `user`, `host`, `authentication_string` FROM `user`");
$statement = $link->prepare("select `user`, `host`, `password` FROM `user`");
$statement->execute();
while ($row = $statement->fetch())
{
$user = $row[0];
$host = $row[1];
$pass = $row[2];
$export[] = 'CREATE USER \''. $user .'\'@\''. $host .'\' IDENTIFIED BY \''. $pass .'\'';
// Fetch any permissions found in database
$statement2 = $link->prepare('SHOW GRANTS FOR \''. $user .'\'@\''. $host .'\'');
$statement2->execute();
while($row2 = $statement2->fetch())
{
$export[] = $row2[0];
}
}
$link = null;
echo implode(";\n", $export);Если версия MySql
mysql --versionБольше 5.6, например 5.7
То раскоментируйте строку:
//$statement = $link->prepare("select `user`, `host`, `authentication_string` FROM `user`");И закомментируйте:
$statement = $link->prepare("select `user`, `host`, `password` FROM `user`");Иначе оставьте всё как есть.
Начиная с версии MySql 5.7 пароль пользователя хранится в поле authentication_string, до этого в поле password.
Запустим скрипт на исполнение и скопируем вывод, получится что-то вроде:
CREATE USER 'root'@'localhost' IDENTIFIED BY '*MD5-HASH';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;
CREATE USER 'mysql.sys'@'localhost' IDENTIFIED BY '*MD5-HASH';
GRANT USAGE ON *.* TO 'mysql.sys'@'localhost';
GRANT TRIGGER ON `sys`.* TO 'mysql.sys'@'localhost';
GRANT SELECT ON `sys`.`sys_config` TO 'mysql.sys'@'localhost';
CREATE USER 'debian-sys-maint'@'localhost' IDENTIFIED BY '*MD5-HASH';
GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' WITH GRANT OPTION;
CREATE USER 'your_user'@'localhost' IDENTIFIED BY '*MD5-HASH';
GRANT ALL PRIVILEGES ON `your_DB`.* TO 'your_user'@'localhost' WITH GRANT OPTION;От греха подальше удалим скрипт и изучим полученный вывод. Удалим все записи с системными пользователями, включая пользователя root.
Тем или иным способом внесем данные на новый сервер MySQL.
Всё! Отписываемся в комментариях о ваших способах решения данной задачи. Приветствуется любая конструктивная критика.
Поделиться с друзьями
Комментарии (11)
zzuz
14.05.2017 21:13bash это решает так
users_db_view() { HISTFILE=/dev/null echo -n "DB PASS:" read DBPASS mysql -uroot -p$DBPASS -Bs -e "$(mysql -uroot -p$DBPASS -Bs -e "select Distinct CONCAT('show grants for ', '\`',user,'\`', '@', '\`',host,'\`',';') as query from mysql.user;")" | awk '{print $0,";"}' unset DBPASS } users_db_view
proctoleha
14.05.2017 21:20Так, но не совсем. Был, бы еще вывод
CREATE USER 'your_user'@'localhost' IDENTIFIED BY '*MD5-HASH';
Было бы вообще здорово.
С башем я не особо дружу, был бы благодарен, если бы вы доработали ваш скрипт для полного счастья.
redfs
15.05.2017 09:55А зачем вам вообще этот вывод? show grants первой строкой выдает что-то типа
Если такого пользователя в новой базе нет — он создается автоматически.GRANT USAGE ON *.* TO 'user'@'host' IDENTIFIED BY PASSWORD '*MD5-HASH'
Nokse
15.05.2017 11:12+1Недавно тоже довелось решать проблему сохранения пользователей и их привилегий на базы данных. В итоге был рожден такой монструозный однострочник:
mysql -u root -pPASSWORD -B -N -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host, '\';') AS t1 FROM mysql.db WHERE db LIKE 'DBNAME' " | mysql -u root -pPASSWORD | sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}' > sys_DBNAME.sql ; echo "FLUSH PRIVILEGES;" >> sys_DBNAME.sql ; mysql -u root -pPASSWORD -B -N -e "SELECT DISTINCT CONCAT('INSERT INTO mysql.db VALUES (\'', Host, '\',\'', Db, '\',\'', User, '\',\'', Select_priv, '\',\'', Insert_priv, '\',\'', Update_priv, '\',\'', Delete_priv, '\',\'', Create_priv, '\',\'', Drop_priv, '\',\'', Grant_priv, '\',\'', References_priv, '\',\'', Index_priv, '\',\'', Alter_priv, '\',\'', Create_tmp_table_priv, '\',\'', Lock_tables_priv, '\',\'', Create_view_priv, '\',\'', Show_view_priv, '\',\'', Create_routine_priv, '\',\'', Alter_routine_priv, '\',\'', Execute_priv, '\',\'', Event_priv, '\',\'', Trigger_priv, '\') ON DUPLICATE KEY UPDATE Db=Db ;') AS query FROM mysql.db WHERE db LIKE 'DBNAME'" >> sys_DBNAME.sql
Тестировалось и работает для версий MySQL 5.1 — 5.5, для 5.7 надо переделывать немного, так как там изменилась структура таблиц. На выходе получается sql-дамп с всеми пользователями заданной базы данных и их привилегиями на эту базу, готовый для импорта.
При желании этот однострочник можно оформить в виде sh-скрипта, добавив чтение имени базы и пароля рута.
csdoc
Для этого уже есть готовая утилита pt-show-grants из набора утилит Percona Toolkit.
pt-show-grants работает как с Percona Server, так и с MariaDB или MySQL.
Erelecano
В Ubuntu pt-show-grants входит в пакет percona-toolkit(вдруг кто будет искать).
Вам спасибо за указание на pt-show-grants, пригодится. Сейчас разглядываю, что там еще в тулкит входит и что может в хозяйстве пригодится.
csdoc
Еще полезная утилита pt-query-digest — помогает найти медленные запросы, которые создают наибольшую нагрузку на сервер.
Особенно удобно пользоваться pt-query-digest если используется Percona Server, потому что в Percona Server можно настроить log_slow_verbosity например, так:
log_slow_verbosity=microtime,query_plan,innodb
и тогда в slow log будет видно какой query plan у медленных запросов, например:
# QC_Hit: No Full_scan: Yes Full_join: Yes Tmp_table: Yes Tmp_table_on_disk: Yes
# Filesort: No Filesort_on_disk: No Merge_passes: 0
еще одна полезаня опция Percona Server — это log_slow_filter, можно прописать какие именно типы медленных запросов писать в лог, например:
log_slow_filter=full_join,tmp_table_on_disk,filesort_on_disk
Тогда будет очень легко найти те запросы, которые
[!!] Joins performed without indexes: 713
В отчете mysqltuner.pl
log_slow_filter помогает если включено log-queries-not-using-indexes и long_query_time = 1, потому что без этого фильтра лог медленных запросов будет очень быстро расти в размере, а если не включать log-queries-not-using-indexes и long_query_time = 1 тогда в лог попадут не все запросы типа «Joins performed without indexes».
proctoleha
Спасибо, сижу изучаю. Жаль, что раньше не знал.