Backing up permissions for individual databases

Sometimes, you want to backup individual databases in MySQL to move to a different server. This part is easy using mysqldump:

The problem is, what happens when you want to backup the permissions associated with these databases? Well, here are a few queries to help you out.

Then, re-loading the permissions onto the new server is simple:

All up, a few queries to account for everything, but pretty easy to include in your backup/restore process. For further development, you could put the database list in a variable, so that you only need to change it on one line, rather than 6.

3 Replies to “Backing up permissions for individual databases”

  1. Should anyone else run across this, when manipulating mysql.user directly, it is necessary to:

    FLUSH PRIVILEGES;

    afterward, or the changes won’t take effect until the service is restarted.

Comments are closed.