Backup MySQL users

Question

How do I backup MySQL users and their privileges?

Anything like mysqldump?

I am looking for something like:

mysqldump -d -u root -p MyTable > Schema.sql

Solution 1

mysql -BNe "select concat('\'',user,'\'@\'',host,'\'') from mysql.user where user != 'root'" | \
while read uh; do mysql -BNe "show grants for $uh" | sed 's/$/;/; s/\\\\/\\/g'; done > grants.sql

Solution 2

You can backup mysql database using

mysqldump -u root -p mysql > mysql.sql

and restore mysql database by executing

 mysql -uroot -p mysql < mysql.sql

Dont forget to

FLUSH PRIVILEGES

after restoring dump.

Hope it helps...

Solution 3

So far my experience with MySQL i didn't see anything to backup user and their privileges through a command line.

But i can backup those critical data by backing up mysql

mysqldump -u root -p mysql > mysql.sql

Solution 4

Percona has a great tool for this. pt-show-grants will dump users and their permissions so you can easily reload them.

https://www.percona.com/doc/percona-toolkit/LATEST/pt-show-grants.html

Solution 5

The users and privileges are stored in the databased named 'mysql'. You can use mysqldump to backup the tables in the databased named 'mysql'.

View more solutions

Comments

[Rafael Herscovici]

can you add how to restore those?

[Ahmad Hajjar]

how should this be restored? is it just by executing the resulting file on the information_schema??

[Dzamir]

Thanks! After the FLUSH PRIVILEGES command, it all started working

[Ligemer]

This is the better answer because it includes the flush privileges command. Another alternative is to just restart the server process instead of flushing privileges.

[kostas]

This is only guranteed to work if restoring to the same MySQL version, the the 'mysql' database may differ accross versions and contains many more things than just users/privileges. If your intention is to migrate your users and privileges to another server, @spirit 's answer above is the way to go.

[Alastair Irvine]

This worked beautifully, and dumped hashed passwords unlike the other solutions. Use --ignore to skip grants for certain users, or --only to only include grants for certain users. Option syntax differs from mysqldump in that --ask-pass is its own option.