Before I upgrade MySQL to the latest and greatest version, one of the first things that I do is export the user and grant information. In the past, I would keep all of my user information (user name, password, grants) in a text file, with the SQL for each user/grant ready to be executed on the upgraded server. I did use my own form of “mental encryption” for my passwords, so the passwords weren’t in plain English. But then I would have to decode my passwords each time before I executed the SQL statements.
When I upgrade, I usually like to dump all of the data and import it into the new version, so I have a fresh copy of the database. The MySQL server that I have is for my personal use and the data size is relatively small, so for my case it doesn’t take long to import the data.
But there were times when I would add a user in the MySQL database and forget to add it to my text file. Then, when it came time to upgrade and I would refer to my text file, these new users would not get recreated. For me it wasn’t that big of a deal, as I am only dealing with my own home-office server, and not a production server. I would eventually figure out that these users weren’t available, and I would simply recreate them. But I often add temporary users for testing purposes. Every once in a while I would want to keep some of the temporary users, and some users would be deleted after the testing was completed. So my text file was rarely up to date.
I am in the process of upgrading my servers to MySQL 5.6.9, and I decided to write a quick Perl script to export all of the users, passwords and grants. Since I will be doing a fresh install of MySQL, I can then just use the output from this script to recreate my users. Of course, you need to run this script before the upgrade.
There are two SQL statements that we will execute with this script. The first, is simply retrieving a list of the user and host names from the mysql.user table:
mysql> SELECT user, host FROM user order by user, host; +-------------+---------------+ | user | host | +-------------+---------------+ | replicate | 192.168.1.121 | | replicate | 192.168.1.2 | | replicate | 192.168.1.4 | | replicate | localhost | | root | 127.0.0.1 | | root | 192.168.1.2 | | root | localhost | | root | macserver01 | | scripting | 192.168.1.121 | | scripting | 192.168.1.122 | | scripting | 192.168.1.2 | +-----------+---------------+ 11 rows in set (0.00 sec) |
Then, we will loop through each of the user and host names to retrieve their grants and passwords. Here is a sample of retrieving this data for just one user and one host name:
mysql> SHOW GRANTS FOR 'replicate'@'192.168.1.121'; +-------------------------------------------------------------------------------------------------------------------+ | Grants for replicate@192.168.1.121 | +-------------------------------------------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'192.168.1.121' IDENTIFIED BY PASSWORD '*BF6F715A6EBFE63005BEB705C' | +-------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
The script will create the necessary SQL statements to recreate the users and grants, with the output displayed in the terminal window. You will just need to copy and save the output from the terminal window into a text file. Or you can change the print statement to output the information directly to a file. Here are the changes to output to a file:
Add this to the top of the script after the “my $Database” line.
open(OUTFILE, ">mysql_usernames.txt") || die "Can't redirect stdout"; |
Add OUTFILE after each of your print commands:
print OUTFILE "CREATE user \'$user\'\@'$host\'\ identified by '';\n"; print OUTFILE "$privileges;\n\n"; |
And close the OUTFILE after the last right curly bracket “}” and before the subroutine:
close OUTFILE; |
For the script, you will need to install the Perl::DBI module. You will also need to change the values for the $host, $userid and $passwd variables in the sub routine ConnectToMySql to match your system. Here is the Perl script.
#!/usr/bin/perl
use DBI;
my $Database = "mysql";
$dbh = ConnectToMySql($Database);
# retrieve a list of users and host names
$query = "SELECT user, host FROM user order by user, host";
$sth = $dbh->prepare($query);
$sth->execute();
while (@data = $sth->fetchrow_array()) {
my $user = $data[0];
my $host = $data[1];
print "CREATE user \'$user\'\@'$host\'\ identified by '';\n";
$dbh2 = ConnectToMySql($Database);
# retrieve the grants for each user and host combination
$query2 = "SHOW GRANTS FOR '$user'\@'$host'";
$sth2 = $dbh2->prepare($query2);
$sth2->execute();
while (@data2 = $sth2->fetchrow_array()) {
my $privileges = $data2[0];
print "$privileges;\n\n";
}
# end first while statement
}
#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------
my ($db) = @_;
my $host ="";
my $userid = "";
my $passwd = "";
my $connectionInfo = "dbi:mysql:$db;$host";
# make connection to database
my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
return $l_dbh;
}
|
And here is the output from running the script.
root@macserver01: $ perl get_mysql_users.pl CREATE user 'replicate'@'192.168.1.121' identified by ''; GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'192.168.1.121' IDENTIFIED BY PASSWORD '*BF6F715A6EBF367E76X705C'; CREATE user 'replicate'@'192.168.1.2' identified by ''; GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'192.168.1.2' IDENTIFIED BY PASSWORD '*BF6F715A6EBF367E76X705C'; CREATE user 'replicate'@'192.168.1.4' identified by ''; GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'192.168.1.4' IDENTIFIED BY PASSWORD '*2A9C19E10B309BF1BE40E4A9C'; CREATE user 'replicate'@'localhost' identified by ''; GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'localhost' IDENTIFIED BY PASSWORD '*2A9C19E10B309BF1BE40E4A9C'; CREATE user 'root'@'127.0.0.1' identified by ''; GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*BF6F715A6EBFKK367E76X705C' WITH GRANT OPTION; CREATE user 'root'@'192.168.1.2' identified by ''; GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.2' IDENTIFIED BY PASSWORD '*BF6F715A6EBFKK367E76X705C' WITH GRANT OPTION; CREATE user 'root'@'localhost' identified by ''; GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*BF6F715A6EBFKK367E76X705C' WITH GRANT OPTION; CREATE user 'root'@'macserver01' identified by ''; GRANT ALL PRIVILEGES ON *.* TO 'root'@'macserver01' WITH GRANT OPTION; CREATE user 'scripting'@'192.168.1.121' identified by ''; GRANT ALL PRIVILEGES ON *.* TO 'scripting'@'192.168.1.121' IDENTIFIED BY PASSWORD '*DEE6483B0XX23K3AD402E34F7' WITH GRANT OPTION; CREATE user 'scripting'@'192.168.1.122' identified by ''; GRANT ALL PRIVILEGES ON *.* TO 'scripting'@'192.168.1.122' IDENTIFIED BY PASSWORD '*DEE6483B0XX23K3AD402E34F7' WITH GRANT OPTION; CREATE user 'scripting'@'192.168.1.2' identified by ''; GRANT ALL PRIVILEGES ON *.* TO 'scripting'@'192.168.1.2' IDENTIFIED BY PASSWORD '*DEE6483B0XX23K3AD402E34F7' WITH GRANT OPTION; |
Now when I upgrade my server, I can simply run this script prior to my upgrade and save the information. I don’t have to worry about missing a user or keeping my user information in a text file.
| Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn. |