How to backup MySQL users except root on Ubuntu

When you want to backup your mysql databases, you usually do mysqldump … –all-databases or mysqldump … –databases mysql … but you end up with the whole mysql table which is a pain to insert back when you need it because it can mess up the root password or the debian-sys-maint user…

If you wish to just backup all the users and privileges other than root and debian-sys-maint, you can use this command:

mysqldump -nt -uroot -p -w"User NOT LIKE 'root' AND User NOT LIKE 'debian%'" mysql user db > users_privs.sql

Here’s an explanation of each of the options:

  • -nt: Do not add “drop table” and “create table”.
  • -uroot -p: Connect as root and ask for a password
  • -w…: Add a “WHERE” condition to each query. We exclude everything related to root and debian-sys-maint.
  • mysql user db: Dump the user and db tables from the mysql database.
  • > users_privs.sql: Store the sql dump into the users_privs.sql file.

2 Comments so far »

  1. spirit said

    February 23 2012 @ 4:58 AM

    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

  2. Arnaud Soyez said

    February 23 2012 @ 7:27 AM

    Very useful, thanks spirit!

Comment RSS · TrackBack URI

Leave a comment

Name: (Required)

eMail: (Required)

Website:

Comment: