mysql: Backup your user privileges

How to backup your mysql privileges to a CSV file

Written by Benjamin Cane on 2011-09-15

While I am sure there are multiple ways to do this and some probably easier here is a way to backup your mysql user privileges to a CSV file.

First you will need to get to the mysql cli.

# mysql -uroot -p  
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.  
Your MySQL connection id is 35083  
Server version: 5.0.51a-24+lenny3 (Debian)
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

After you are logged in you will need to select which database to use. In this case its the db named mysql.

mysql> use mysql;
Reading table information for completion of table and column names

Now you are going to need to backup the db table. We do not care about the test database so this statement is excluding it.

mysql> select * from db where db not like "test%" INTO outfile "/var/tmp/mysql.db.dump" FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n';  
Query OK, 5 rows affected (0.11 sec)

And finally the user table. In this case I don't care about the root user or anything that matches Debian. So we will exclude these as well.

mysql> select * from user where user != "root" && user not like "debian%" INTO outfile "/var/tmp/mysql.users.dump" FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n';  
Query OK, 5 rows affected (0.00 sec)

Now we can see our CSV files.

# ls -la /var/tmp/mysql.*  
-rw-rw-rw- 1 mysql mysql 531 2011-09-14 20:49 /var/tmp/mysql.db.dump  
-rw-rw-rw- 1 mysql mysql 990 2011-09-14 20:49 /var/tmp/mysql.users.dump

Picture of Benjamin Cane

Benjamin's specialty is keeping the lights on for mission critical systems. He is currently building applications that enable high concurrency financial transactions.

Recently Benjamin published his first book; Red Hat Enterprise Linux Troubleshooting Guide. In addition to writing, he has several Open Source projects focused on making Ops easier. These projects include Automatron, a project enabling auto-healing infrastructure for the masses.


Identify, capture and resolve common issues faced by Red Hat Enterprise Linux administrators using best practices and advanced troubleshooting techniques

What people are saying:
Excellent, excellent resource for practical guidance on how to troubleshoot a wide variety of problems on Red Hat Linux. I particularly enjoyed how the author made sure to provide solid background and practical examples. I have a lot of experience on Red Hat but still came away with some great practical tools to add to my toolkit. - Amazon Review