Summary: in this tutorial, you will learn different types of MySQL backups, strategies to implement, and the necessary tools for each backup type.
MySQL backup types
In MySQL, you can make a backup of the entire database server, including database and configuration files; this is referred to as a physical backup.
Alternatively, you can create a backup of specific databases or tables, which is known as a logical backup.
Logical backups
A logical backup allows you to recreate table structures and data without copying the actual data files. Logical backups export the database structure and data to SQL statements.
A popular tool for performing logical backups is mysqldump, which comes with the default MySQL installation.
Both tools export the backup files that contain CREATE TABLE and INSERT statements, allowing for the recreation of the tables and data.
For example, here’s the backup command that uses the mysqldump tool to export a database and its data into a backup.sql file:
mysqldump -u [username] -p[password] [database_name] > backup.sqlCode language: CSS (css)A logical backup provides you with flexibility but can take longer to restore than a physical backup.
Physical backups
Physical backups capture the binary data files, which provide an exact copy of the database at a specific point in time. Therefore, a physical backup is restored faster than a logical backup.
The tool for physical backups is Percona XtraBackup. It is a free, open-source, and powerful backup tool for MySQL.
Here’s the backup command of the Xtrabackup:
xtrabackup --backup --user=[username] --password=[password] --target-dir=/path/to/backupCode language: JavaScript (javascript)Hot & cold backups
Besides logical and physical backups, we can classify the backups based on the database’s operational state during the backup process.
Hot backups and cold backups refer to how the database is handled while the backup is being performed.
Hot backups
Hot backups are performed while the database is actively running and serving requests. This method ensures continuous availability and minimal disruption to users during the backup process.
Percona XtraBackup can perform hot backups, capturing the database state without locking tables and keeping the database available during the backup process.
Here’s the backup command using Xtrabackup:
xtrabackup --backup --user=[username] --password=[password] --target-dir=/path/to/backupCode language: JavaScript (javascript)Cold backups
Cold backups are performed when the database is offline and no write operation occurs. This ensures a consistent snapshot of the entire database at the point of backup.
To perform a cold backup, you can use the mysqldump command-line tool.
Here are the steps for carrying a cold backup:
First, stop the MySQL server:
sudo service [mysql_service] stopCode language: CSS (css)Second, back up a database to a file:
mysqldump -u [username] -p[password] [database_name] > backup.sqlCode language: CSS (css)Third, start the MySQL server:
sudo service [mysql_service] startCode language: CSS (css)Summary
- A logical backup exports database structure and data to files that include SQL statements.
- A physical backup captures an exact binary image of the entire database system including configuration and data files.
- A hot backup creates a copy of a database while it is actively running, whereas a cold backup creates a copy of a database while it is shut down
