CODEWITHSUNDEEP
mysqldatabase
hasanghaforian
hasanghaforian

Reputation: 14022

Backup all privileges related to specific user of MySQL

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?

Upvotes: 0

Views: 361

Answers (2)

Michael - sqlbot
Michael - sqlbot

Reputation: 179134

Try these options (line breaks for clarity):

$ mysqldump -u root -p 
  --where="user='u_1'"
  --complete-insert
  --extended-insert
  --no-create-info
  mysql 
  user db tables_priv columns_priv procs_priv proxies_priv 
  > mysql.sql

Or... let's call the above solution "the hard way."

This should be the easy way:

$ mysql -u root -p
  --skip-column-names
  -e "SHOW GRANTS FOR 'u_1';"
  > grants.sql

Upvotes: 3

Bill Karwin
Bill Karwin

Reputation: 562428

I would use:

pt-show-grants --only u_1

pt-show-grants is a tool in the free Percona Toolkit.

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

Upvotes: 0

Related Questions