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

В настоящее время тема переноса баз сервера MySql достаточно широко освещена в сети Интернет. Можно использовать как различные СУБД, так и встроенные средства, например mysqldump.
Но, к сожалению, сервер MySql не имеет встроенных возможностей для переноса пользователей MySql и их привилегий.

Важное замечание 1: мы подразумеваем, что у вас есть root доступ к исходному серверу MySql. Т.е. вы переезжаете на Vscale.io не с shared хостинга!

Важное замечание 2: говоря о переносе пользователей и их привилегий, применительно к серверу MySql, автор статьи подразумевает использование нативных средств Линукс.

Важное замечание 3: если вы используете панель управления сервером, например VestaCP, то созданные описанным в статье способом пользователи MySql, в вашей панели управления, будут не видны.

Обращаем ваше внимание на выражение "не видны". На самом деле сервер MySql будет работать в штатном режиме, но VestaCP пользователей MySql видеть не будет. Автор сталкивался с данным фактом в двух панелях управления сервером: ISP Manager и VestaCP.
При этом PhpMyAdmin работает в штатном режиме и всё видит.

Как перенести пользователей сервера MySql

Шаг 1: сохраняем пользователей сервера MySql на исходном сервере

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

1. Создаем на любом вашем сайте, на исходном сервере, скрипт php, например db.php, и прописываем ваш пароль для пользователя root сервера MySql в $pass.
Также нужно закомментировать/раскомментировать строки:

//$statement = $link->prepare("select `user`, `host`, `authentication_string` FROM `user`");
$statement = $link->prepare("select `user`, `host`, `password` FROM `user`");

Об этом ниже.

Скрипт:

<?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);

Важное замечание 4: Будьте предельно внимательным при редактировании данного скрипта! Важно не только правильно прописать пароль пользователя root, но и знать вашу версию MySql!

Узнать версию MySql можно командой:

mysql --version

Если ваша версия MySql больше 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!

2. Запустите созданный скрипт, перейдя по адресу http://your-site.com/db.php. Если всё сделано правильно, вы получите примерно такой вывод данных в браузере:

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

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

Важное замечание 5: будьте предельно внимательны при редактировании данных! Убедитесь что выведены все пользователи с хешированными паролями, и все их привилегии!

Обязательно удалите системных пользователей, включая данные пользователя root! Например, в приведенном выводе нужно удалить:

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;

Шаг 2: Переносим данные на новый сервер

1. Переходим на ваш сервер с помощью ssh, например:

ssh user_name@your_server

2. Создаем файл users.sql:

nano users.sql

3. Копируем отредактированные данные с домашнего компьютера, и вставляем, используя горячие клавиши ctrl + shift + v.

4. Сохраняем данные и выходим из редактора nano: ctrl + o -> enter -> crtl + x.

В приведенном примере необходимо сохранить:

CREATE USER 'your_user'@'localhost' IDENTIFIED BY '*MD5-HASH';
GRANT ALL PRIVILEGES ON `your_DB`.* TO 'your_user'@'localhost' WITH GRANT OPTION;

5. Запускаем команду:

mysql -uroot -pYour_passwd < users.sql

6. Enjoy!

Заключение

Возможно покажется, что метод, приведенный в статье, для переноса пользователей сервера MySql - сложный.

На самом деле всё просто:

• На исходном сервере создаем и запускаем php скрипт.
• Редактируем вывод.
• Сохраняем данные в файл users.sql на конечном сервере.
• Запускаем файл users.sql с помощью mysql.