Migrating users and their privileges to MySQL
Greetings dear Habroskoobschestvo! As you know, the MySql server does not have built-in mechanisms for transferring MySql users and their privileges to another server. There are very few ready-made solutions in the network, and in this small article we will transfer our users MySql and their privileges, from one server to another, in a few minutes.
The urgency of this task does not make much sense to describe. If the databases themselves can be transferred in a bunch of ways, then with users, if there are a lot of them, a problem arises.
In general, the task is trivial: in any programming language working with MySql, run a script that pulls users and their privileges from the mysql database. It's easier for me to work with php.
Based on gist from Zaid Daba'een
')
If the MySql version
Greater than 5.6, for example 5.7
Then uncomment the line:
And comment out:
Otherwise, leave everything as it is.
Starting with MySql 5.7, the user's password is stored in the authentication_string field, before that in the password field.
Run the script for execution and copy the output, you get something like:
From sin away, remove the script and examine the output. Delete all entries with system users, including the root user.
In one way or another, we will add data to the new MySQL server.
Everything! We unsubscribe in the comments about your ways to solve this problem. Any constructive criticism is welcome.
The urgency of this task does not make much sense to describe. If the databases themselves can be transferred in a bunch of ways, then with users, if there are a lot of them, a problem arises.
In general, the task is trivial: in any programming language working with MySql, run a script that pulls users and their privileges from the mysql database. It's easier for me to work with php.
Based on gist from Zaid Daba'een
')
Php script code
<?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); If the MySql version
mysql --version
Greater than 5.6, for example 5.7
Then uncomment the line:
//$statement = $link->prepare("select `user`, `host`, `authentication_string` FROM `user`");
And comment out:
$statement = $link->prepare("select `user`, `host`, `password` FROM `user`");
Otherwise, leave everything as it is.
Starting with MySql 5.7, the user's password is stored in the authentication_string field, before that in the password field.
Run the script for execution and copy the output, you get something like:
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;
From sin away, remove the script and examine the output. Delete all entries with system users, including the root user.
In one way or another, we will add data to the new MySQL server.
Everything! We unsubscribe in the comments about your ways to solve this problem. Any constructive criticism is welcome.
Source: https://habr.com/ru/post/328604/
All Articles