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 Replies to “How to backup MySQL users except root on Ubuntu”

  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

Leave a Reply