mysqldump and database users...

Question

Alright,

Let me preface this with the fact that I'm assuming I am lacking a fundamental understanding of mysql. I'm attempting to assemble all the options that I need for mysqldump to create everything used by my applications database into a single script. This includes the database itself and all the database users/passwords/privileges.

I've got it all figured out with the exception of the user piece... here's what I'm currently using:

mysqldump -h host -u root -p --add-drop-database --routines -B database_name > backup.sql

So, what am I missing?

Thanks in advance!

BB

Answer 1

The database users/passwords/privileges are kept in the mysql database, and won't get dumped with your dump command. You'll have to it to the list of DBs to dump:

mysqldump ... --routines --databases database_name mysql > backup.sql

or just dump everything:

mysqldump ... --routines --all-databases > backup.sql

Answer 2

So, I had a fundamental misunderstanding. Users are not specific to a database, but are rather created at the server level.

You can view all existing users with the following query:

SELECT * FROM mysql.user;

Knowing this, it's pretty obvious that mysqldump shouldn't do anything with users. However, if you need an answer to exporting/importing users and perms I suggest you check the following article - it helped me out.

http://pento.net/2009/03/12/backing-up-permissions-for-individual-databases/

My apologies for the noise on the board, but I figured I'd leave the post incase anyone else has the same misunderstanding.