4

I want to backup all privileges related to specific user (for example u_1) from a MySQL database and restore that in another server. As described here, The 'mysql' database contains users/privileges/passwords. So I have to backup all related data from all tables of mysql database (mysql.user,mysql.db, ...). I can run this command:

$ mysqldump -u root --flush-privileges -r mysql.sql -p mysql

But of course the mysql.sql contains all users and all privileges.

Also I tried this command:

$ mysqldump -u root -p --where="user.user='u_1'" mysql user> mysql.sql

But as expected, it only contains a row of mysql.user table.

Is there a way to strip out other users except of u_1?

3

Capture the output from

SHOW GRANTS FOR 'u_1'@localhost;
SHOW GRANTS FOR 'u_1'@'%';

(and any other 'hosts' involved.)

Then replay GRANTs to establish hist credentials elsewhere.

  • Do I have to restore them manually? – hasanghaforian Sep 25 '17 at 2:23
  • 2
    If you capture the output, you could send that to mysql -u root <grants.sql -- that's not quite "manually". – Rick James Sep 25 '17 at 3:06
2

By this script you can backup all of mysql's users except root:

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

So, if you just need to export a specific user like (u_1)

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

And you can restore it by:

mysql -u root <grants.sql

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.