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.