How to backup/restore your MySQL database

If you run a database driven website, it is important to backup your databases, periodically. You can generate a full backup to do this, but you may decide it is unnecessary to backup everything. So, here is how to backup just your databases.

To backup the databases please do the following:

  1. Login to your control panel and click Backups.
  2. Under Download a MySQL Database Backup, click the name of the database.
  3. Click Save As.
  4. Select a destination for where you would like the back up to be saved, locally.

Once you have a backup, if you ever inadvertently lose your data, then you can restore the database easily and quickly.

To restore a database, please do the following:
  1. Login to your control panel and click Backups.
  2. Click the Browse button next to the option which says Restore a MySQL Database.
  3. Select the .sql.gz file which you want restored.
  4. Click the Open button and click the Upload button.

Your database driven website should be working again.


Article Comments

Jaque
How do you make partial or incremental backups?

HostGator
A database only backup is a partial backup (as opposed to a full backup which gets databases, web content, emails, everything.)

There are no incremental backup tools provided.

JuanJo
Is it possible to have it "automatically" and daily?

HostGator
You cannot create daily automatic backups with our tools, but you can create a Cron Job if you have a script which does that.

Unfortunately, we cannot provide such a script.

Cyrus
Hi there,

Is there a size limit for the backup? I noticed that there's a 4GB for the full backup. I'm currently looking into the Baby Plan from your shared hosting service.

Cyrus

HostGator
The size limit for any type of backup is 4GB.

Todd
What is the file upload size limit for restore?

HostGator
There is no upload limit, but I recommend files under 4GB.

Files which are up to 6GB or 8GB tend to not work, as they sometimes become corrupt.

Dave
Can you elaborate at all on the script/cron comment? I tried making a PHP script that utilizes MySQL "SELECT * INTO OUTFILE" but I don't have sufficient permissions to run this query. I suppose I could write more code to run a bunch of separate SELECTs, parse the data into a SQL string, and write the string to file, but if there's an easier way, I'm all ears.

HostGator
A cron will not help if you cannot get the script to work properly.

First, make sure you are connecting to the correct database. If you are connecting to a remote database, then you must setup your connecting IP address as an Access Host.

http://support.hostgator.com/articles/cpanel/how-to-connect-to-the-mysql-database

Next, be sure you are using a database user and password which has the SELECT privileges granted for the database.

http://support.hostgator.com/articles/cpanel/how-do-i-create-a-mysql-database-user-and-then-delete-if-needed

If you need help to check these, please contact HostGator support.

Patt
If I've extracted the .sql.gz to .sql accidentally, is there a way to recompress it to a .sql.gz? I need to restore a database backed up from 4 days ago, but didn't realize that the file needed to remain compressed.

HostGator
Normally, when you extract a file, the original compressed format remains. Please check because both versions should still exist.

sws4420
When I go to MySQL Databases, it says my main database is 818mb, yet when I download a database as described above, the size shows as 20.0 bytes. Why is this?

HostGator
The file will be compressed, but that size does not seem correct.

My 10.69 MB database compresses to a 1.59 MB .sql.gz file.

Please contact HostGator via phone or live chat if you cannot figure out what happened.

Asif
Can we also keep MSsql databases on your servers ?
If yes, do we have to make separate payment for this?

HostGator
Only our Windows dedicated hosting will support MSSQL.

Andy
If i remember correctly i could only do full backups, so thank you for allowing me this option. I also used to backup sql via phpmyadmin but you installed a timeout on it. Has this been removed, can i backup a database via phpmyadmin again (baby plan)

HostGator
You can backup databases via phpMyAdmin if you prefer. Large databases may timeout.

If you get a timeout, you may email support@hostgator.com and request that we export the database (be specific with a name) to a file (specify .sql or other type), and we will upload it to your account.

cheeseandrice
Database name must be shorter then 13 characters or it will error

GBHostmaster
I am a Hostgator customer and wondered if you could answer this question / query.

When I back-up my database via cPanel the database is 20kb's (tiny) and takes about 3 seconds to complete. When I do the same task using phpMyAdmin the file takes about 10 minutes to complete. My concern is that (in my case, for some unknown reason) the back-up doesn't look like it's working via the cPanel method but does using the phpMyadmin method.

HostGator
Sounds like the cPanel backup is not working. Please contact us via email, phone or live chat for assistance.

Sofie
Can hostgator retrieve / restore a database for me if it was deleted by mistake?

HostGator
Yes, but only if you qualify for our weekly backups, and then we would only have a copy from the most recent Sunday.

It is much better if you create backups on a regular basis. (Be sure to always make backups before making major/critical changes to your account.)


Your comments help us keep the knowledge base updated. This is not a medium for support. If you have questions or need help, please contact us via email, phone or live chat for fast assistance.

Post Comment