Results 1 to 5 of 5
Thread: mySQL dB Backup How to?
-
05-30-2005, 08:10 AM #1Web Hosting Master
- Join Date
- Feb 2005
- Location
- Northern VA
- Posts
- 1,582
mySQL dB Backup How to?
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
Husband, Father, Retired Marine, Geek
-
05-30-2005, 02:24 PM #2Newbie
- Join Date
- May 2005
- Location
- Canada
- Posts
- 15
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
mysqldump -uyourusername -pyourpassword –all-databases > /home/LOGIN/public_html/backups/xbackup_$date.sql
One database would be
mysqldump -uyourusername -pyourpassword –databases DB1 > /home/LOGIN/public_html/backups/xbackup_$date.sql
To restore a db it would be
mysqlimport -u username -p newdbname < /path/to/dump.sql
-
05-30-2005, 04:51 PM #3Web Hosting Master
- Join Date
- Mar 2005
- Posts
- 1,073
Originally posted by coryd
If your database is quite large, phpmysqladmin may have issues exporting the file.
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
-
05-30-2005, 05:06 PM #4Newbie
- Join Date
- May 2005
- Location
- Canada
- Posts
- 15
Actually, I suggested two different methods. The SSH method is a harder one but doesn't require external scripts and can be done in much the same manner for exporting or importing.
I would suggest exporting with phpmysql admin (like http://www.phpbb.com/kb/article.php?article_id=59 shows how to do for a phpBB message board) and then importing with BigDump from http://www.ozerov.de/bigdump.php
From their page:
BigDump: Staggered MySQL Dump Importer
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.
-
06-02-2005, 06:46 AM #5Newbie
- Join Date
- Jun 2005
- Posts
- 8
Here is a thought -- if you didn't have ssh access, why not create a shell script and upload that, and then cron it?