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

Приветствую уважаемое Хабросообщество!

Как известно у сервера MySql нет встроенных механизмов для переноса пользователей MySql и их привилегий на другой сервер. В сети готовых решений крайне мало, и в этой небольшой статье мы перенесем наших пользователей MySql и их привилегии, с одного сервера на другой, за несколько минут.

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

Вообще задача тривиальна: на любом языке программирования, работающим с MySql, запустить скрипт, который вытащит из базы mysql пользователей и их привилегии. Мне проще работать с php.

За основу взят gist ^[1]от Zaid Daba'een

<?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.

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

Автор: Алексей Кулагин

Источник ^[2]