#1  
Old 28th February 2007, 03:39
guysmiley guysmiley is offline
Junior Member
 
Join Date: Sep 2006
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Question backing up MySQL users & privs

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
Reply With Quote
Sponsored Links
  #2  
Old 28th February 2007, 08:15
till till is online now
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 36,388
Thanks: 833
Thanked 5,485 Times in 4,317 Posts
Default

A backup of all tables to a single file can not be done with phpmyadmin. Better use the commandline program "mysqldump".
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
  #3  
Old 28th February 2007, 17:05
guysmiley guysmiley is offline
Junior Member
 
Join Date: Sep 2006
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
  #4  
Old 28th February 2007, 17:12
jnsc jnsc is offline
rotaredoM
 
Join Date: Mar 2006
Location: Lausanne, Switzerland
Posts: 525
Thanks: 10
Thanked 172 Times in 77 Posts
Default

Users and privs are stored in a special database called mysql. As long as you backup everything, you will also have this base.
Reply With Quote
  #5  
Old 28th February 2007, 18:56
guysmiley guysmiley is offline
Junior Member
 
Join Date: Sep 2006
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?
Reply With Quote
  #6  
Old 28th February 2007, 20:22
martinfst martinfst is offline
Senior Member
 
Join Date: Dec 2006
Location: Hilversum, The Netherlands
Posts: 880
Thanks: 1
Thanked 18 Times in 17 Posts
Send a message via MSN to martinfst Send a message via Skype™ to martinfst
Default

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.
Reply With Quote
  #7  
Old 28th February 2007, 21:55
jnsc jnsc is offline
rotaredoM
 
Join Date: Mar 2006
Location: Lausanne, Switzerland
Posts: 525
Thanks: 10
Thanked 172 Times in 77 Posts
 
Default

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
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Junk mail and spamassassin... sthompson Installation/Configuration 4 27th December 2006 16:11
Recovering unlinked mysql databases and users... Dave Lane Installation/Configuration 2 10th May 2006 19:45
Problem with Virtual Users And Domains With Postfix, Courier And MySQL Howto fenix Server Operation 4 11th March 2006 02:53
Another Problem: Virtual Users And Domains With Postfix, Courier And MySQL (+ SMTP bluegrass Installation/Configuration 24 8th March 2006 01:41
Mandriva 10.2 Perfect Setup Install Problems... ctroyp Installation/Configuration 12 30th December 2005 16:04


All times are GMT +2. The time now is 12:29.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.