×
×

PhpMyAdmin: How to Backup Large Databases

Back

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

db_size_1

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

db_size_2

 

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

mysql_export_large_1

4) Click the Export tab

mysql_export_large_2

5) Click the Custom option under method

mysql_export_large_3

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

mysql_export_large_4

7) Click Go at the bottom of the page

mysql_export_large_5

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.