backing up MySQL users & privs

Discussion in 'Installation/Configuration' started by guysmiley, Feb 28, 2007.

  1. guysmiley

    guysmiley New Member

    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
     
  2. till

    till Super Moderator Staff Member ISPConfig Developer

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

    guysmiley New Member

    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.
     
  4. jnsc

    jnsc rotaredoM Moderator

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

    guysmiley New Member

    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?
     
  6. martinfst

    martinfst ISPConfig Developer ISPConfig Developer

    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.
     
  7. jnsc

    jnsc rotaredoM Moderator

Share This Page