MySQL backups are a near-daily part of every MySQL DBAs life. MySQL does provide MySQL DBAs and developers that deal with MySQL a bunch of backup-related statements - in this blog post we will go through them and also tell you how Backup Ninja might make your life easier in that regard too.
Backing Up MySQL - Backup Utilities
As already mentioned, MySQL (and other vendors) does provide you with quite a few backup utilities that can be used. Among those are:
- mysqldump - perhaps one of the most popular choices in the MySQL world. This utility has quite a lot of options associated with it and is one of the most widely used backup commands.
- Percona XtraBackup - also a popular choice amongst developers that work with MySQL frequently. A hot backup utility that suits all versions of MySQL and Percona Server for MySQL. A free tool provided by Percona.
- MySQL Enterprise Backup - another popular choice amongst MySQL DBAs. Provided by MySQL and primarily a paid tool.
Mysqldump, as already previously mentioned, produces logical backups. The utility has a few options to be configured with it. Some of them include:
- The --all-databases option which dumps all of the tables in all databases.
- The --databases option which interprets all of the name arguments as databases.
- The --force option which makes the utility continue backing up data even if an error occurs.
- The --tab option produces tab-separated backup files.
- The --add-drop-database option adds a DROP DATABASE statement before each CREATE DATABASE statement.
Mysqldump is really easy to use, a simple command will do:
Mysqldump -u[username] -p[password] [db_name] > backup.sql
Keep in mind that it’s probably a good idea to avoid putting your password inside of the console and instead put it inside of my.cnf, otherwise it might get observed by system status programs.
Percona XtraBackup is a tool provided by Percona and it’s a hot backup utility that suits all versions of MySQL, also Percona Server. Percona XtraBackup works a bit differently than, say, mysqldump does: it copies the InnoDB data files, then performs crash recovery on them. When it starts, it remembers the LSN which is an integer that acts as a version to the REDO log entries. At the same time, Percona XtraBackup continually monitors the transaction log files and copies changes from them.
Creating a backup using Percona’s XtraBackup utility is simple. To create a backup using Percona’s XtraBackup, run:
$ xtrabackup --backup --target-dir=/backups/
This command will store the backup in the backups directory. That’s it!
To restore the backup, prepare it first:
$ xtrabackup --prepare --target-dir=/backups/
Then copy it back to the data directory on the server:
$ xtrabackup --copy-back --target-dir=/backups/
MySQL Enterprise Backup
MySQL also offers an Enterprise edition of its backup tools. The flagship product in this space would be the MySQL Enterprise Backup solution. The tool has many unique features including hot backups, partial backups and incremental backups, but do note that the tool is enterprise edition by default meaning that it’s not free: a standard version of the license will cost $5,000.
MySQL Enterprise Backup, in a nutshell, is optimized to be used with InnoDB tables, enables users to choose between multiple backup scenarios, offers backup encryption and executes functions using the mysqlbackup client.
MySQL Enterprise Backup is optimized to be used together with InnoDB tables which means that if you backup data that is running, say, the MyISAM engine, it will be taken using a warm backup technique.
MySQL Enterprise Backup can be used like so:
$ mysqlbackup --user=root --password --port=3306 [OPTIONS] backup
If you want to perform, say, a full backup, you could use the tool like so:
$ ./mysqlbackup --user=root --password --host=127.0.0.1 --backup-image=/home/directory/backup.mbi --backup-dir=/home/backup-tmp backup-to-image
The --backup-image specifies the backup file, --backup-dir - the backup directory and the backup-to-image statement means that you want the tool to create a single-file backup. For more information about how MySQL Enterprise Backup works, refer to a blog post at MySQLBackups.
Now we get to Backup Ninja - how is the tool different from all of those mentioned above and why should you use it? We will answer that question now.
The first difference would be that while all of the other technologies focus exclusively on MySQL, Backup Ninja does not: it can be used together with MySQL, MariaDB, Percona Server, PostgreSQL, MongoDB or TimescaleDB. The second difference would be that while many DBAs could consider using backup scripts to achieve their backup goals, in most cases it’s not a scalable solution. Backup Ninja is able to provide you with peace of mind in that regard too.
Here’s how to take a backup using Backup Ninja:
- Set up your backup target:
- Create a database user:
- Then install the agent and discover your servers:
That’s it - it’s as easy as that! Isn’t that convenient?
MySQL and other database vendors do provide you with quite a few backup utilities. Most of those utilities do indeed help, but most of them aren’t easily scalable, the majority of them do need some time to get used to and some of them even cost thousands of dollars - Backup Ninja can relieve you of those issues. If you want to take advantage of what Backup Ninja has to offer, give it a try today!