How to Export & Import Existing User (with its Privileges!)
Solution 1
One of the easiest ways I've found to export users is using Percona's tool pt-show-grants. The Percona tool kit is free, easy to install, and easy to use, with lots of documentation. It's an easy way to show all users, or specific users. It lists all of their grants and outputs in SQL format. I'll give an example of how I would show all grants for test_user:
shell> pt-show-grants --only test_user
Example output of that command:
GRANT USAGE ON *.* TO 'test_user'@'%' IDENTIFIED BY PASSWORD '*06406C868B12689643D7E55E8EB2FE82B4A6F5F4';
GRANT ALTER, INSERT, LOCK TABLES, SELECT, UPDATE ON `test`.* TO 'test_user'@'%';
I usually rederict the output into a file so I can edit what I need, or load it into mysql.
Alternatively, if you don't want to use the Percona tool and want to do a dump of all users, you could use mysqldump in this fashion:
shell> mysqldump mysql --tables user db > users.sql
Note: --flush-privileges won't work with this, as the entire db isn't being dumped. this means you need to run it manually.
shell> mysql -e "FLUSH PRIVILEGES"
Solution 2
mysql -u<user> -p<password> -h<host> -e"select concat('show grants for ','\'',user,'\'@\'',host,'\'') from mysql.user" > user_list_with_header.txt
sed '1d' user_list_with_header.txt > ./user.txt
while read user; do mysql -u<user> -p<password> -h<host> -e"$user" > user_grant.txt; sed '1d' user_grant.txt >> user_privileges.txt; echo "flush privileges" >> user_privileges.txt; done < user.txt
awk '{print $0";"}' user_privileges.txt >user_privileges_final.sql
rm user.txt user_list_with_header.txt user_grant.txt user_privileges.txt
Above script will run in linux environment and output will be user_privileges_final.sql that you can import in new mysql server where you want to copy user privileges.
UPDATE: There was a missing -
for the user of the 2nd mysql statement.
Solution 3
Yet another bash one-liner for linux that you can use instead of the Percona tool:
mysql -u<user> -p<password> -h<host> -N mysql -e "select concat(\"'\", user, \"'@'\", host, \"'\"), coalesce(password, authentication_string) from user where not user like 'mysql.%'" | while read usr pw ; do echo "GRANT USAGE ON *.* TO $usr IDENTIFIED BY PASSWORD '$pw';" ; mysql -u<user> -p<password> -h<host> -N -e "SHOW GRANTS FOR $usr" | grep -v 'GRANT USAGE' | sed 's/\(\S\)$/\1;/' ; done
Solution 4
In complement of @Sergey-Podushkin 's answer, this shell script code is workin for me:
mysql -u<user> -p<password> -N mysql -e "select concat(\"'\", user, \"'@'\", host, \"'\"), authentication_string from user where not user like 'root'" | while read usr pw ; do mysql -u<user> -p<password> -N -e "SHOW GRANTS FOR $usr" | sed 's/\(\S\)$/\1;/'; done
Solution 5
In mysql 5.7 and later you can use this.
mysqlpump -uroot -p${yourpasswd} --exclude-databases=% --users
This will generate a sql format output that you can redirect to mysql_users.sql.
Note that it is mysqlpump not mysqldump.
Withheld
Updated on July 05, 2022Comments
-
Withheld almost 2 years
I have an existing MySQL instance (test), containing 2 databases and a few users each having different access privileges to each database.
I now need to duplicate one of the databases (into production) and the users associated with it.
Duplicating the database was easy:
Export:
mysqldump --no-data --tables -u root -p secondb >> secondb_schema.sql
Import:
mysql -u root -p -h localhost secondb < secondb_schema.sql
I didn't find, however, a straightforward way to export and import users, from the command line (either inside or outside mysql).
How do I export and import a user, from the command line?
Update: So far, I have found manual (and thus error prone) steps for accomplishing this:
-- lists all users select user,host from mysql.user;
Then find its grants:
-- find privilege granted to a particular user show grants for 'root'@'localhost';
Then manually create user with the grants listed in the result of the 'show grants' command above.
I prefer a safer, more automated way. Is there one?
-
Hugo H over 7 yearsThere is a lot of
flush privileges;
empty lines but it works. Thanks! -
robsch about 7 years
-
Admin almost 7 yearsi was really anxious to run this (hard to read script), but it worked, kudos!
-
trey-jones almost 6 yearsNote that for MySQL 5, you will need to use the 2.x version of this tool, rather than 3.x
-
danemacmillan about 5 years@threeve I assume you suggest that because
pt-show-grants
no longer provides the password with that combination? -
trey-jones about 5 years@danemacmillan My memory is hazy - I believe 3.x is compatible with mysql 8+. Probably my comment extends from trying the latest release with our server (5.x) and finding that "it didn't work".
-
CodingInTheUK over 4 yearsHow to add backticks? some columns use keywords so backticks are needed. GRANT SELECT, UPDATE (ssl_key, ssl, ssl_request, ssl_action, ssl_letsencrypt, ssl_cert) ON
database
.table
TO 'user'@'hostname'; ssl is a reserved work, I cant change the column its not my project but it breaks this script, as will any reserved word. -
Toothbrush over 4 years@Chris
GRANT SELECT, UPDATE (`ssl_key`, `ssl`, `ssl_request`, `ssl_action`, `ssl_letsencrypt`, `ssl_cert`) ON `database`.`table` TO 'user'@'hostname';
-
Novocaine over 3 yearsReally handy script, the comments in the gist for code tweaks are necessary, after which it does exactly what I need.
-
TommyPeanuts over 3 yearsYour
IDENTIFIED BY ', authentication_string,
doesn't work on the import as it's missing enclosing commas aroundauthentication_string
. -
RedScourge over 3 yearsauthentication_string is not a string, rather it is a column name of a field we want in the table mysql.user, so it should indeed work without additional quotation marks.
-
TommyPeanuts over 3 yearswhen I try running the SQL, the hashes don't turn up in that column but the literal string does unless I put quotes around it. Also, the resulting grants don't seem to work anyway. I have had to use the percona tool instead.
-
RedScourge over 3 yearsOh, I see what you mean now. Seems I do have quotes there actually in the code I'm running. I will update the answer. As for the grants not working, I have run into problems with certain versions of MySQL, particularly lower ones, or where your hashes were originally generated in a much older version. In the latter case I had to re-set all the passwords when migrating very old credentials, then the hashes output as expected. Other than that, it works fine for me in 5.7.32 as I use it as part of my automated backups for recreating everything.
-
TommyPeanuts over 3 yearsinteresting - I'm using 5.7.32 but some of the hashes were generated in an older version so perhaps that's it.
-
RedScourge over 3 yearsI think the hashes that gave me problems were from a pretty old version, like 5.5.30 or something.
-
TommyPeanuts over 3 yearsIt's possible they could have come from that early a version. BTW when I tried the SQL output from your script (once I'd fixed the quote thing) I didn't get any errors, just that the users couldn't log in.
-
amicoderozer almost 3 yearsThanks, this is perfect for MySql 8, Sergey's won't work in version 8 because there isn't field password in mysql.user table
-
Diggi55 over 2 yearsWhy would you force this into a one-line?? The answer is barely readable
-
Sergey Podushkin over 2 years@Diggi55 just because I can and want to and just because it's convenient when you work in command line and retrieve the whole command from the history as one line rather than set of commands. You can put this one-liner into file, break it to multiple line and save as script if one-liner doesn't fit your needs.
-
Dennis V over 2 yearsmysqldump --version mysqldump Ver 8.0.27-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu)) # mysqldump --users mysqldump: [ERROR] unknown option '--users'.
-
Pablo Luna over 2 yearsNOT mysqldump but mysqlpump.... pump not dump