backing up MySQL users & privs

[guysmiley]

I was reading through Falko's tutorial here when I noted he makes reference to using phpmyadmin to backup all dbs. I've looked in vain all over the web for the answer to this (and obviously not in the right places) so here it is:

I would like to use phpmyadmin to perform a full and complete backup of all dbs, users & their privs so that I can restore the entire MySQL structure quickly (ideally from a single file using a single command). Can this be done through phpmyadmin, and, if so, how? Our current mySQL structure contains more than 90 dbs with 30+ tables in each...

All help is greatly appreciated! (and thanks to Till and Falko for the great work they do!)

gs

[till]

A backup of all tables to a single file can not be done with phpmyadmin. Better use the commandline program "mysqldump".

[guysmiley]

Thanks for the pointer, Till.

Is there a how-to on how to dump the an entire mysql db (users and their privs included)?

I found this http://www.howtoforge.com/faq/6_3_en.html from falko but it only does 1 db at a time and I don't believe the users are backed-up.

[jnsc]

Users and privs are stored in a special database called mysql. As long as you backup everything, you will also have this base.

[guysmiley]

Thanks Jnsc,

Yep, I discovered this one later on and was about to post my learning when I saw your response...

I've been told to upgrade from mysql 4 to 5 and am looking for a how-to because I'm unfamiliar with the differences in structures and am nervous at the prospect.

If I dump all dbs out of 4 and into 5, should I expect major problems on import? Is there a how-to kicking around?

[martinfst]

MySQL 4 to 5 migration might be easy, might be some challenges.

1. Do you use international charsets in your tables? If yes, you have to be careful. Chars may nog get converted and you need to do it manually. Check the settings of your dbs & tables after creation.
2. Does your application use 'left join' on multiple tables? If yes, you may need to update your application code. MySQL has implemented a more strict adherance to SQL syntax per 5.xx.

That are the two major issues I had during upgrade.

[jnsc]

Yes, but as you also want to dump the mysql table, it migth be necessary to update the shema.

A good read would be http://dev.mysql.com/doc/refman/4.1/...-from-4-0.html and http://dev.mysql.com/doc/refman/5.0/...-from-4-1.html