Skip to content

How to back up a MySQL database

Databases house one of the most important assets an organization has — their data. Keeping data safe and uncorrupted is mission-critical. For most applications, downtime is less painful than loss or corruption of data. Customers will generally forgive a bit of downtime, but they will not forgive you losing or corrupting their data. Database backup is therefore non-negotiable for any serious organization that cares about disaster recovery.

Risks to your database will inevitably come up. If you migrate your MySQL database from one platform to another, upgrade to MySQL versions, or switch from PostgreSQL to MySQL, something could go wrong. A bug could potentially corrupt data in an irreparable way. Or hardware could fail and cause permanent data loss.

Backing up your MySQL database regularly can help mitigate all these potential risks when done correctly. In this article, we’ll review MySQL database backup methods and available replication tools to help you navigate your options for keeping your data safe. These tips can help you restore MySQL databases in case disaster strikes.

Different types of MySQL backup methods

MySQL backups come in a variety of formats, so we’ll need to define some terminology first. It’s important to understand all these formats, because not every backup process style is suitable for every project.

Choosing the right backup and restore method that you can rely on is crucial. The backup is your plan B if things go wrong. The last thing you want is for your plan B to also fail, and you’re left without another plan to restore your data.

The main MySQL backup methods are:

  • Logical and physical
  • Online and offline
  • Local and remote
  • Full and incremental
  • Snapshots

Let's go through each of these.

Logical and physical

Logical backups save data by maintaining a backup file with a script of SQL operations to aid the replication of the entire database. This includes both schema and table creation operations and all data insertions. The script is then executed to restore the backup. This method is better suited for small databases or if you only want to restore part of a database.

Physical backups store copies of all your database’s directories and files. This method is faster to restore than the logical method, but requires a lot more storage space. This style is preferable for large databases.

Online and offline

Online backups are created while the MySQL server is online and fulfilling client requests. They’re also known as “hot” backups. The disadvantage is it can be complex to simultaneously back up a database that is actively being updated by clients. So you must implement logic to lock some parts of the database during the backup process.

Offline backups, also known as “cold” backups, are performed while the MySQL server is shut down. This means clients cannot make requests to the database while the database backup is being created. The upside of blocking clients from making changes is you can skip any complicated locking logic.

Local and remote

Local MySQL backups are backups created on the same machine running the MySQL server. These are generally easier to manage, but introduce risk of losing both the all databases and database backups if the hardware fails. Additionally, you’re more likely to encounter disk full errors storing both on the same disk.

Remote backups use another MySQL server for the backup. This eliminates the single point of failure risk of a local backup, but introduces extra overhead in managing more hardware and network configurations keeping each machine in communication.

Full and incremental

Full backups save a comprehensive backup of the entire database. These are useful for ensuring everything is backed up and this method tends to be simpler to do a restore. The downsides are it requires more storage space and can be intensive to maintain.

Incremental backups save changes between two points in time. This method is lighter to maintain, but is not as comprehensive and can be difficult to restore. Incremental backups are best paired with a full backup, where first the full backup is restored, and then the incremental backup is restored to bring the restored database more up to date.

Snapshots

Snapshot backups create a “snapshot” copy of the database’s file system at a given point in time, but does it without needing to store an entire physical copy. It does this by making a logical copy of the file system.

How to back up a MySQL database using mysqldump

The native MySQL backup solution is called mysqldump. It’s a command line tool that generates a logical backup of SQL statements reproducing the entire database in a dump file.

Creating a backup with the mysqldump utility can be done with a single, simple command. If you wanted to create a backup where the name of the database is test_db and save it in a backup file name of backup.sql, you run the following command:

SQL
mysqldump test_db > backup.sql

You can also backup multiple databases in a single operation and dump the backup in one file. You do this by adding the --databases flag and listing each database to back up.

SQL
mysqldump --databases test_db test_db2 test_db3 > backup.sql

Or, if you wanted to back up all of our databases, you could use the --all-databases flag:

SQL
mysqldump --all-databases > backup.sql

Pros and cons of using mysqldump to back up a MySQL database

The mysqldump command has a lot of advantages as a backup tool, but is not perfect for every project. The main pros of it are:

  1. Natively supported by MySQL and therefore easy to use and free.
  2. Lightweight as it only creates a SQL backup file and not a huge directory of files.
  3. Easy to read and modify by humans because it’s standard SQL syntax.
  4. Compatible with all MySQL providers and most other SQL-based databases.

Before committing to using mysqldump, you should consider the following cons:

  1. It’s very slow to generate and restore with.
  2. It requires locking tables from write operations white the backup is generating.
  3. If locking isn’t done, the backup may not contain updates made during the backup generation.
  4. Does not support incremental backups.

Using PlanetScale to back up a MySQL database

PlanetScale offers automated database backups every 12 hours on the Scaler Pro plan, managed through a simple user interface. Additional backups can be created manually or at scheduled times at a small monthly cost.

You can even use the PlanetScale CLI and a cron job to automate offsite database backups using a MySQL dump with the pscale db dump command, as shown in the following video:

Restoring a database involves restoring the backup to a new branch, like pushing code changes to a git branch. You can test if everything works well with the backup, and then promote that branch to production through PlanetScale’s UI, command line utility, or API (beta).

Conclusion

Designing a solid backup and restore strategy is paramount for any serious application and requires a lot of consideration and careful planning. It doesn’t have to be overly complex if you have the right tooling though. With PlanetScale, you can trust your MySQL database is being reliably and regularly backed up without you being up all night worrying about it.