In this article, we will talk about how to backup large MySQL databases.

Let’s first talk about the different types of compression that are available to us:

None

None is as you would imagine, no compression. This is a raw sql dump of the database and because of this, can produce very large backup files (sometimes even larger than the database itself).

Zip

Zip takes the raw dump of the MySQL database and zips it in an archive. For most people, this will be the easiest compression type to use simply because all operating systems can natively open a zip file.

Gzipped & Bzipped

Because of a more complex algorithm used compared to the zip compression type, these formats produces a much smaller archive. It is natively supported on most Linux/Unix/Mac systems, however you will need special software to open the file on Windows based systems.

Why not just use Zip?

While zip is easier to use for most users (if you are attempting to open the file manually), using it can cause issues on larger databases. Because the zip compression is done using PHP functions, it is limited to using a certain amount of memory during the zip process. This will result in large databases not being archived correctly (it will only download a ~213 byte file).

If your database size is larger than ~75MBs to 80MBs, it is highly recommended that you do NOT use zip as the compression type. All other formats (None, Gzip, & Bzip) work as expected no matter the size of the database.

How do I check the size of my database?

In this section, we will show you how to check the size of your database so you can know if you can use Zip, or if you need to use another compression type.

1) Login to SiteAdmin or cPanel

2) From the Databases section, click phpMyAdmin

3) Click on the database you wish to check

4) Locate the total size at the bottom of the size column

 

How do I backup a MySQL database using compression?

In this section, we will show you how to make a backup (also known as exporting) of your database using compression.

1) Login to SiteAdmin or cPanel

2) From the Databases section, click phpMyAdmin

3) Click the database you wish to export

4) Click the Export tab

5) Click the Custom option under method

6) Under the output section, select the compression type. Please read our section above about using Zip

7) Click Go at the bottom of the page

That’s it! Depending on the size of your database, it could take some time. However, once it is done compressing the files (on the server), it will automatically start downloading to your computer.