Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Northern VA

    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.
    WebsiteMaven - Web Hosting Reviews, Guides, and Advice to Build and Promote your Web Site

  2. #2
    Join Date
    May 2005
    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

  3. #3
    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.
    * Rock solid shared & reseller UK webhosting. No downtime™ *
    Now offering fully managed VPS servers

  4. #4
    Join Date
    May 2005
    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 shows how to do for a phpBB message board) and then importing with BigDump from

    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.

  5. #5
    Here is a thought -- if you didn't have ssh access, why not create a shell script and upload that, and then cron it?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts