I have a mySQL database on a Site5 server that I use for click activity and other stuff. I want to back it up locally in case the remote server crashes and my data is lost.
How do I do a complete backup in phpMyAdmin? I clicked on my database and then clicked export but wasn't sure if the default options clicked in Cpanel were correct or if I should have highlighted some of the files I saw in an export window (looked like log files).
How would I restore the exported dB if I ever needed to?
Is there a better way to export/import the dB other than phpMyAdmin?
Thanks in advance for any help any might be able to offer.
Rich WebsiteMaven - Web Hosting Reviews, Guides, and Advice to Build and Promote your Web Site
If you have SSH access, it can be done quite easily no matter the size of your database. If your database is quite large, phpmysqladmin may have issues exporting the file. I dont have 5 posts here yet so i can't post links to other sites or do PM's, if you contact me at the email in my sig, I will send you the links i reference here
I found a guide from the phpBB community on their knowledge base (the article id is 59) which should be useful for you through phpmysqladmin. As they note there, restoring a large database may cause you headaches through phpmysqladmin. I suggest (and have used multiple times) BigDump (search google for bigdump) which takes the sql file and automatically restores it into the database in managable chunks.
The alternative method would be to use ssh which would be probably a bit more daunting for a newer user.
To backup all your databases from SSH, you would do
Originally posted by coryd
If your database is quite large, phpmysqladmin may have issues exporting the file.
It almost indefinately won't have issues exporting the database from server to locally, however importing is another question.
You can either split the tables if its a reasonably large database, which would take forever, you can use SSH as "coryd" suggested, or if you want the quick and easy way of importing the database you can use a php script with timeout=0
If you want this script, I can type it out quickly, let me know if I can be of assitance in the future.
BeeServe * Rock solid shared & reseller UK webhosting. No downtime™ * Now offering fully managed VPS servers
Staggered import of large and very large MySQL Dumps (like phpMyAdmin 2.x Dumps) even through the web-servers with hard runtime limit and those in safe mode. The script executes only a small part of the huge dump and restarts itself. The next session starts where the last was stopped.
I have used this and recommended it's use successfully for multiple large databases.