phpMyAdmin: How to Backup Large Databases
In this article, we will talk about how to backup large MySQL databases. If you simply want to export a copy of your small database and do not want to use PHPMyAdmin, you can do so using a few click method from within the backup functionality in SiteAdmin or cPanel.
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.
Debbie Naugle November 19, 2014 at 3:57 pm
Is this database backup similar to the one I create with backup buddy? Or is this something different that needs to be done regularly?
James Davey November 20, 2014 at 6:25 am
BackupBuddy should back up your databases as well, but you would need to confirm that yourself. I cannot guarantee that it is, so I would recommend manually backing up your database periodically, as well.