Перенос пользователей и их привилегий в MySQL

[makhomed]

Для этого уже есть готовая утилита pt-show-grants из набора утилит Percona Toolkit.

pt-show-grants работает как с Percona Server, так и с MariaDB или MySQL.

[Erelecano]

В Ubuntu pt-show-grants входит в пакет percona-toolkit(вдруг кто будет искать).
Вам спасибо за указание на pt-show-grants, пригодится. Сейчас разглядываю, что там еще в тулкит входит и что может в хозяйстве пригодится.

[makhomed]

Еще полезная утилита 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]

Спасибо, сижу изучаю. Жаль, что раньше не знал.

[zzuz]

bash это решает так

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]

Так, но не совсем. Был, бы еще вывод

CREATE USER 'your_user'@'localhost' IDENTIFIED BY '*MD5-HASH';

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

[redfs]

А зачем вам вообще этот вывод? show grants первой строкой выдает что-то типа

GRANT USAGE ON *.* TO 'user'@'host' IDENTIFIED BY PASSWORD '*MD5-HASH'

Если такого пользователя в новой базе нет — он создается автоматически.

[proctoleha]

Спасибо. Век живи, век учись, как бы это банально не звучало

[zzuz]

По мне create user это излишне. Grant достаточно.

[Nokse]

Недавно тоже довелось решать проблему сохранения пользователей и их привилегий на базы данных. В итоге был рожден такой монструозный однострочник:

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-скрипта, добавив чтение имени базы и пароля рута.

[proctoleha]

Спасибо, потестирую как руки дойдут

[petropavel]

А в чем вообще проблема переноса пользователей? Чем она отличается от переноса данных?

Кроме другого имени хоста и айпишника, ничего в голову не приходит. Но в примере везде localhost, значит проблема в другом. В чем же?