How to Export & Import Existing User (with its Privileges!)

Question

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?

Answer 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" 

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