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

    Приветствую уважаемое Хабросообщество! Как известно у сервера MySql нет встроенных механизмов для переноса пользователей MySql и их привилегий на другой сервер. В сети готовых решений крайне мало, и в этой небольшой статье мы перенесем наших пользователей 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.

    Всё! Отписываемся в комментариях о ваших способах решения данной задачи. Приветствуется любая конструктивная критика.
    Поделиться публикацией
    Похожие публикации
    Ой, у вас баннер убежал!

    Ну, и что?
    Реклама
    Комментарии 12
    • +2
      Для этого уже есть готовая утилита pt-show-grants из набора утилит Percona Toolkit.

      pt-show-grants работает как с Percona Server, так и с MariaDB или MySQL.
      • +1
        В Ubuntu pt-show-grants входит в пакет percona-toolkit(вдруг кто будет искать).
        Вам спасибо за указание на pt-show-grants, пригодится. Сейчас разглядываю, что там еще в тулкит входит и что может в хозяйстве пригодится.
        • 0
          Еще полезная утилита 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».
        • 0
          Спасибо, сижу изучаю. Жаль, что раньше не знал.
        • 0
          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
          
          • 0
            Так, но не совсем. Был, бы еще вывод
            CREATE USER 'your_user'@'localhost' IDENTIFIED BY '*MD5-HASH';
            

            Было бы вообще здорово.
            С башем я не особо дружу, был бы благодарен, если бы вы доработали ваш скрипт для полного счастья.
            • 0
              А зачем вам вообще этот вывод? show grants первой строкой выдает что-то типа
              GRANT USAGE ON *.* TO 'user'@'host' IDENTIFIED BY PASSWORD '*MD5-HASH'
              
              Если такого пользователя в новой базе нет — он создается автоматически.
              • 0
                Спасибо. Век живи, век учись, как бы это банально не звучало
              • 0
                По мне create user это излишне. Grant достаточно.
            • +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-скрипта, добавив чтение имени базы и пароля рута.
              • 0
                Спасибо, потестирую как руки дойдут
              • 0

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


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

                Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                Самое читаемое