intro
Every developer working with databases has taken a MySQL backup. In this guide, we’re walking you through a couple of best practices related to backup and recovery of this RDBMS.
Are you a database guru? If so, you’ve probably heard of DB-Engines. DB-Engines is a portal ranking database management systems by their popularity, and MySQL is ranked second. As such, MySQL backup procedures are of crucial importance to all DBAs and software engineers.
There are numerous backup appliances and strategies in MySQL you can follow. The strategy chosen has a direct impact on the future of both your application and your database. Let’s explore them all!
How to Backup MySQL?
For many, backing up MySQL is an easy task. What is there to think about? Just open phpMyAdmin, select the necessary options, and click the “Export” button. Here you go—you’ve downloaded a MySQL backup file. Easy, right?
That’s our exported file:
So many options, right? We know:
And we didn’t even have to scroll! Awesome. Frightening.
Judging from the screenshots above, you’d think that issuing a MySQL backup is very easy. We have bad news—what you saw is a logical backup taken by one of the tools that can be used. There are multiple types of backups in MySQL, and consequentially, many tools to help you take a backup.
MySQL Backup Methods
We’re starting from methods to help you take a MySQL backup. In the MySQL world, backup methods are as follows:
| Backup Method | About |
|---|---|
| Logical Backups | These MySQL backups are backups that backup statements (SQL queries) recreating the data in our database. |
| Physical Backups | This backup method involves backing up physical files related to MySQL. Such a method is usually faster to restore, but usually much more costly in terms of disk space. |
| Online and Offline—”Hot” and “Cold”—Backups | Online—or “hot”—backups are performed once the server that is “receiving” the backup is online and accessible, frequently used, by users. |
| Offline—or “cold”—backups are performed when the server is down and inaccessible to users. | |
| Full Backups | Full backups mean “comprehensive backups of everything a MySQL instance contains.” They include everything in the entire MySQL Server instance and can sometimes be very time-consuming and tedious to maintain. |
| Incremental Backups | Incremental backups record changes between two points in time. Incremental backups are recovered using point-in-time recovery methods. |
| Snapshots | A snapshot backup is a logical backup of the files in the file system at any given point in time. These kinds of backup methods don’t require a physical copy of the files existing on the database server. |
See how many options we have to choose from? Our next steps are as follows:
Before you ask—no, backup methods are not only unique to MySQL Server or its counterparts like MariaDB or Percona Server. These backup methods are applicable to various kinds of database management systems from PostgreSQL and TimescaleDB to MongoDB.
These kinds of backup methods are like “golden rules” of database management systems, but for them to work in a golden—secure and fast—manner, we need to follow a couple of best practices, too.
MySQL Backup Best Practices
Before embarking on your MySQL backup journey, decide what tool are you going to use to back up your MySQL instances:
| Backup Appliance | About |
|---|---|
mysqldump | A CLI-based tool used to back up databases or tables within them for a transfer into another database server. Only takes logical backups. |
| Percona XtraBackup | A fully-fledged backup appliance built by Percona. This backup appliance can backup data from InnoDB, XtraDB, MyISAM, and MyRocks-based tables. Only performs hot—online—backups. |
| MySQL Enterprise Backup | An enterprise-grade tool providing enterprise-grade backup and recovery for MySQL Server-based databases. Comes with ways to take all kinds of backups—full, incremental, partial, comes with encryption, backup validation, and compression capabilities, and much more. Learn more about MySQL Enterprise Backup on its official webpage. |
We’ve listed the top three choices for MySQL engineers across the globe—now we’ll walk you through each of those tools and provide you with some information on what SQL clients like the one provided by DbVisualizer are capable of, too.
Follow the information outlined below:
| Backup Appliance | Best Practices |
|---|---|
mysqldump | An ordinary use case of mysqldump looks like so: |
mysqldump [options] > file.sql
Options include, but are not limited to (all options are preceded by “--”):
• all-databases that dumps all tables in all databases.
• add-locks ”wraps” each dumped table around LOCK TABLES and UNLOCK TABLES SQL statements that prevent actions on that specific database.
• allow-keywords allows the creation of tables that are keywords (that’s usually prohibited.)
• default-character-set lets us specify a default character set for the database dump.
All options are preceded by “--”. The full list of options can be found here.
• To restore such a backup, either re-import it through phpMyAdmin, or back it up via the CLI:
mysql -u[user] database_name < backup.sql |
| Percona XtraBackup | • To create a backup, run Percona XtraBackup after specifying the --backup option like so:
xtrabackup --backup --target-dir=/backups/ where --backup invokes Percona XtraBackup and --target-dir specifies the directory which will “receive” (store) the backup.
• Once backups are made, they need to be prepared to be able to be restored.
• To prepare a backup, run the following statement:
xtrabackup --prepare --target-dir=/backups/
after replacing target-dir with your backup directory.
• Finally, to restore a backup, run --copy-back :
xtrabackup --copy-back --target-dir=/backups/
Learn more in Percona’s documentation of XtraBackup. |
| MySQL Enterprise Backup | • Create a backup like so (we don’t specify a password here for MySQL Enterprise Backup to grab it from my.cnf instead):
mysqlbackup --user=dbvisualizer --password --port=3306 [options] [backup|backup-to-image]
• Available options include backup-to-image (creates an “image” of a backup), copy-back (restores — “copies back” — a backup), copy-back-and-apply-log (the same as copy-back just with updates to log files), and validate (validates a backup.)
• Aside from the available options, we also have abilities to take incremental backups, exclude certain tables from being backed up, encrypt our backups, and more.
• To take a full backup, specify a --backup-image and a --backup-dir. Tell the Enterprise Backup solution to --backup-to-image too.
`• To take an incremental backup, specify a--defaults-file(location towards my.cnf), the--incremental-start-lsn(Log Sequence Number), add--with-timestamp, and provide a--incremental-backup-dir`.
• Other ways to take backups using MySQL Enterprise Backup are available in the documentation. |
Aside from best practices within MySQL backup and recovery tools, you must glance at SQL clients, too. Most of them aren’t exclusive to MySQL and provide support for various database management systems such as PostgreSQL, MsSQL, and SQLite, as well as database appliances like ClickHouse, Cassandra, and others.
DbVisualizer is a top-notch SQL client that also offers a free 21-day trial so you can try their various features for yourself. DbVisualizer provides you with the ability to enhance your workflow, a nice overview of your data and everything related to your database, whatever database you elect to use (hence the name), and it is also built to withstand security breaches.
Select a table, and DbVisualizer will also provide you with the DDL necessary to recreate the table structure on any database infrastructure, too:
DbVisualizer is also known for its ability to extensively document the references of your tables:
Grab a free trial of DbVisualizer, set up the SQL commander permissions, then have fun with your database. Make sure to visit our blog to learn more about database management systems and SQL clients!
Summary
The MySQL backup process isn’t a piece of cake, but it isn’t exactly rocket science either. We hope that this blog post has opened your eyes to what’s possible with the backup procedures in MySQL, and until next time!
FAQ
What tool should I use to backup MySQL?
It all depends on what kind of backups you want to take. The easiest option for logical backups would be mysqldump, but you can always use Percona XtraBackup or MySQL Enterprise Backup.
Why are there so many backup methods in MySQL?
It makes sense to have many MySQL backup methods because no two infrastructures are exactly the same. Plus, different backup methods back up different things inside of your database. So, having different backup methods at hand helps those dealing with bigger data sets or in other tricky situations.
What MySQL flavor should I use when performing a backup in MySQL?
It doesn’t matter much if you use MySQL Server, Percona Server, or MariaDB Server as the backup MySQL process is pretty much the same for all of them. However, bear in mind that Percona Server and MariaDB do offer features MySQL does not, such as XtraDB (unique to Percona Server) or other storage engines.
