Loading...Contents
- Different ways to create a MySQL database backup
- Top 12 MySQL backup software solutions and tools
- Percona XtraBackup
- SQLBackupAndFTP
- Vembu BDR Suite
- Iperius Backup
- Handy Backup
- N-able Cove Data Protection
- Ottomatik
- SqlBak
- Commvault Backup and Recovery
- Comet Backup
- Quest Rapid Recovery
- Backup Ninja
- SimpleBackups
- The purpose of MySQL backup with Bacula Enterprise
- Binary or Dump?
- How it works: Binary mode
- How it works: Dump mode
- MySQL restore modes
- Conclusion
- Why you can trust us
The need for MySQL database backups is not as universal as is the need for regular end-user backups, but when required, it becomes an especially important part of a company’s infrastructure. This article delves deeper into the very nature of an automatic MySQL backup process, including free and paid approaches to backup and recovery, and different customization options. The article features both built-in MySQL backup possibilities as well as 3rd party MySQL backup software tools.
Different ways to create a MySQL database backup
Let’s go over some popular and unconventional ways of creating a MySQL database backup without any 3rd party MySQL backup solutions. The backup in question can be performed either manually or automatically. Here are a few examples of manual MySQL backups:
1. Using PHP to create an XML file
It’s also possible to recreate your database as an XML file, with the help of the PHP. It’s not the safest format in the world when it comes to restoring your data, but some people prefer it over anything else. This snippet of code below should allow you to export a specific database in the file with an .XML format:
//connect
$link = mysql_connect($host,$user,$pass);
mysql_select_db($name,$link);
//get all the tables
$query = ‘SHOW TABLES FROM ‘.$name;
$result = mysql_query($query,$link) or die(‘cannot show tables’);
if(mysql_num_rows($result))
{
//prep output
$tab = “\t”;
$br = “\n”;
$xml = ‘<?xml version=”1.0″ encoding=”UTF-8″?>’.$br;
$xml.= ‘<database name=”‘.$name.'”>’.$br;
//for every table…
while($table = mysql_fetch_row($result))
{
//prep table out
$xml.= $tab.'<table name=”‘.$table[0].'”>’.$br;
//get the rows
$query3 = ‘SELECT * FROM ‘.$table[0];
$records = mysql_query($query3,$link) or die(‘cannot select from table: ‘.$table[0]);
//table attributes
$attributes = array(‘name’,’blob’,’maxlength’,’multiple_key’,’not_null’,’numeric’,’primary_key’,’table’,’type’,’default’,’unique_key’,’unsigned’,’zerofill’);
$xml.= $tab.$tab.'<columns>’.$br;
$x = 0;
while($x < mysql_num_fields($records))
{
$meta = mysql_fetch_field($records,$x);
$xml.= $tab.$tab.$tab.'<column ‘;
foreach($attributes as $attribute)
{
$xml.= $attribute.’=”‘.$meta->$attribute.'” ‘;
}
$xml.= ‘/>’.$br;
$x++;
}
$xml.= $tab.$tab.'</columns>’.$br;
//stick the records
$xml.= $tab.$tab.'<records>’.$br;
while($record = mysql_fetch_assoc($records))
{
$xml.= $tab.$tab.$tab.'<record>’.$br;
foreach($record as $key=>$value)
{
$xml.= $tab.$tab.$tab.$tab.'<‘.$key.’>’.htmlspecialchars(stripslashes($value)).'</’.$key.’>’.$br;
}
$xml.= $tab.$tab.$tab.'</record>’.$br;
}
$xml.= $tab.$tab.'</records>’.$br;
$xml.= $tab.'</table>’.$br;
}
$xml.= ‘</database>’;
//save file
$handle = fopen($name.’-backup-‘.time().’.xml’,’w+’);
fwrite($handle,$xml);
fclose($handle);
}
2. Using PHP as a backup method in the first place
One more way of utilizing PHP for your backup needs is to take a database backup query from a PHP file and use that. The query in the following example is SELECT INTO OUTFILE.
<?php
include ‘config.php’;
include ‘opendb.php’;
$tableName = ‘mypet’;
$backupFile = ‘backup/mypet.sql’;
$query = “SELECT * INTO OUTFILE ‘$backupFile’ FROM $tableName”;
$result = mysql_query($query);
include ‘closedb.php’;
?>
And of course, you’ll need a way to restore that backup type, as well. Here’s the query for that, it’s LOAD DATA INFILE one:
<?php
include ‘config.php’;
include ‘opendb.php’;
$tableName = ‘mypet’;
$backupFile = ‘mypet.sql’;
$query = “LOAD DATA INFILE ‘backupFile’ INTO TABLE $tableName”;
$result = mysql_query($query);
include ‘closedb.php’;
?>
3. Backup using SSH
Backing up via SSH is doable even for a particularly large MySQL database (s). In your Plesk panel, start by enabling shell access, and then use any tool that can gain access to the server in question via SSH (PuTTY or similar tool). First though, you’ll have to specify a target directory for your future backup:
CD wwwroot/dbbackup
And then you’ll use mysqldump to export your database in a specific file within a server:
mysqldump –add-drop-table -u db_username -p db_name > mybackup.sql
At the same time, plenty of scripts and methods exist that allow for at least some degree of MySQL backup automation, such as:
1. Linux-based automatic MySQL database backup
A certain Linux/Unix-based utility under the name cron can perform MySQL backups on those systems. It’s a relatively simple utility that starts up with the system and reads its instructions from a specific file. This config file is usually stored in the /etc/crontab. Here’s an example of a daily backup set at 2:30 AM:
30 2 * * * root mysqldump -u root -pPASSWORD –all-databases | gzip >
/mnt/disk2/database_`data ‘ %m-%d-%Y’`.sql.gz
The main command used here is mysqldump – a simple dump utility that comes with every MySQL deployment. A lot of different MySQL backup methods rely on this tool to obtain database dump files in the first place.
Cron jobs can also be automated and customized using cPanel – a web hosting control panel for websites that can also handle MySQL database management tasks.
2. Server Shell script to backup a MySQL database via Ubuntu
If you have a dedicated VPS server with Ubuntu Linux on board, you have one more option. You can use a specific shell script to backup all of your databases to your FTP server. Here’s an example:
#!/bin/bash
### MySQL Server Login Info ###
MUSER=”root”
MPASS=”MYSQL-ROOT-PASSWORD”
MHOST=”localhost”
MYSQL=”$(which mysql)”
MYSQLDUMP=”$(which mysqldump)”
BAK=”/backup/mysql”
GZIP=”$(which gzip)”
### FTP SERVER Login info ###
FTPU=”FTP-SERVER-USER-NAME”
FTPP=”FTP-SERVER-PASSWORD”
FTPS=”FTP-SERVER-IP-ADDRESS”
NOW=$(date +”%d-%m-%Y”)
### See comments below ###
### [ ! -d $BAK ] && mkdir -p $BAK || /bin/rm -f $BAK/* ###
[ ! -d “$BAK” ] && mkdir -p “$BAK”
DBS=”$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse ‘show databases’)”
for db in $DBS
do
FILE=$BAK/$db.$NOW-$(date +”%T”).gz
$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
done
lftp -u $FTPU,$FTPP -e “mkdir /mysql/$NOW;cd /mysql/$NOW; mput /backup/mysql/*; quit” $FTPS
After you’re done saving this segment as an .sh file, the next step is to set up the correct permission level for this specific file:
$ chmod +x /home/your-name/mysql.backup.sh
From now on you can input one of the two commands to launch your backup process:
/home/your-name/mysql.backup.sh
Or:
sudo /home/your-name/mysql.backup.sh
You can also set this script to run as a cron job. Here’s an example of the script running daily at midnight, it consists of two different command prompts:
$ sudo crontab -e
And:
@midnight /home/you/mysql.backup.sh >/dev/null 2>&1
It is possible for these scripts to work with different UNIX or Linux distributives, too.
3. Using phpMyAdmin
phpMyAdmin is an open source tool that’s also free, and works with both MySQL and MariaDB databases. The correct order of inputs is as follows:
- Open the tool in question;
- Select your database that you want to create a backup of;
- Choose the “export” option on the navbar;
- Click the “display all possible options” line;
- Choose the specific tables that you want to see backed up;
- Change the compression type (or leave it at gzipped, it’s recommended to do that);
- Initiate the backup by clicking the “go” button.
This concludes our list of the most relatively popular backup options for your basic MySQL database backup needs.
4. AutoMySQLBackup
AutoMySQLBackup is also regarded as a convenient way to dump databases at any time – it is a customizable script that expands upon the mysqldump command to create and compress backups at fixed intervals. It can be found at Sourceforge and installed with relative ease by using the following command:
$ tar zxvf automysqlbackup-v3.0_rc6.tar.gz
The script’s README file provides extensive instructions on how to create backups or customize various settings. The script can be installed by running install.sh or by exporting the necessary files by hand.
Once the installation process is complete – it is time to configure the solution properly in order for it to be able to create backups. This process is relatively simple – you need a MySQL user with at least a SELECT level of privileges and a dedicated folder for your future MySQL backups.
The username and password of a MySQL user are specified using the following command:
# Username to access the MySQL server e.g. dbuser
CONFIG_mysql_dump_username=’db_user’
# Password to access the MySQL server e.g. password
CONFIG_mysql_dump_password=’dbpassword’
The host name and the backup directory can also be easily modified with the following command (the server/host name should always be localhost):
# Host name (or IP address) of MySQL server e.g localhost
CONFIG_mysql_dump_host=’localhost’
# Backup directory location e.g /backups
CONFIG_backup_dir=’/home/mysqlbackups’
This script allows for quite a lot of customization when it comes to backup target databases – it is possible to manually specify which databases need to be backed up, or you can just back up all of them at once (the “CONFIG_db_names=()” command):
# List of databases for Daily/Weekly Backup e.g. ( ‘DB1’ ‘DB2’ ‘DB3’ … )
# set to (), i.e. empty, if you want to backup all databases
CONFIG_db_names=()
# List of databases for Monthly Backups.
# set to (), i.e. empty, if you want to backup all databases
CONFIG_db_month_names=()
# List of DBNAMES to EXLUCDE if DBNAMES is empty, i.e. ().
CONFIG_db_exclude=(‘information_schema’ ‘test_db’ ‘demo_db’ )
It is also possible to use similar commands to customize rotation settings, notifications, and so on. Once the configuration process is complete, you can initiate the backup for a specific database (or all of them) with the following command:
# automysqlbackup /etc/automysqlbackup/servername.conf
There is also a simple backup script that is presented as an example in the aforementioned README file:
#!/bin/sh
/usr/local/bin/automysqlbackup /etc/automysqlbackup/myserver.conf
chown root.root /var/backup/db* -R
find /var/backup/db* -type f -exec chmod 400 {} ;
find /var/backup/db* -type d -exec chmod 700 {} ;
This particular backup script ensures that the backup owner is root, and the files are read-only (to prevent accidental changes). The end user would have to change myserver.conf to match the name of their configuration file, and all of the /var/backup/ mentions would also have to be replaced with the user’s backup directory path.
It should also be possible to schedule these backups (with Cron) and send them offsite (using Horcrux), but both of these options are a bit more case-specific, so we are not going into them here.
5. Amazon S3 automatic backup script
There is also a somewhat old django script that was published back in 2009. It allows for MySQL backups to be transferred directly to Amazon S3 storage after their creation. The script in question is no longer accessible in the original location, but it can be found using archive.org – the link in question has both the download link for the script we mentioned, as well as detailed instructions on how it can be used, what needs to be configured beforehand, and so on.
6. MySQL backup automation with Task Scheduler
Basic Windows features such as the Task Scheduler can be used to set up and perform MySQL backup tasks automatically with little effort. The script in question is as follows:
cd “C:\Program Files\MySQL\MySQL Server\bin”
mysqldump -h 127.0.0.1 -u root -p –all-databases > “D:\MySQL\allmysql.sql”
It needs to be copied into a blank text file. That way, it is easier to manipulate the full name of the script to our advantage. The name of the file should be modified to have a .bat at the end of it, so that it becomes an executable command file instead of a text file.
Next, the Task Scheduler has to be opened – it is a rather interesting tool that allows for extensive task automation and customization when necessary. The process itself is simple:
- A task is created
- A trigger for the task in question is generated (“on schedule”)
- The action for the trigger is specified (“start a program” and a full file path to the aforementioned .bat file)
Once the task’s customization is complete, it should be automatically enabled, allowing you to perform MySQL backups using standard Windows feature set and practically nothing else.
Of course, neither one of these lists are complete, there are probably plenty of other script examples that are even more difficult to find. As we have mentioned before, it should also be possible to use full-fledged backup solutions to create backups of MySQL databases. There are plenty of those solutions out there, ranging from enterprise-grade comprehensive backup solutions to small-scale software that targets small businesses as their main audience.
Top 12 MySQL backup software solutions and tools
We have picked 12 different examples of backup solutions available, in the field of MySQL backups – providing information about their specialties and unique traits:
Percona XtraBackup
Percona XtraBackup is a good starting point for this list – a MySQL backup solution that is absolutely free to use, but has a lot of limitations. It is an open-source backup software, but that only works with Percona servers. It has a moderately large list of features, including the choice between on-premise and cloud backup target location, backup automation capabilities, solution scalability, point-in-time recovery capability, and so on. While it does have the restricting requirement of only being capable of working with a Percona server, both the backup solution and the server itself are completely free of charge, so it may be a good choice for smaller companies with limited budgets.
Customer ratings:
- Capterra – 4.8/5 stars based on 14 customer reviews
- TrustRadius – 8.8/10 stars based on 4 customer reviews
- G2 – 4.8/5 stars based on 6 customer reviews
Advantages:
- Cloud storage can be used as a backup location, saving storage costs
- Higher overall efficiency since there’s no need to lock databases in order to perform backups
- Multithreading, incremental backups, hot backups, parallel backups, and many other features
Shortcomings:
- Lack of a dedicated GUI
- Very limited range of supported database types
- First-time configuration is somewhat lengthy and complicated
Pricing:
- Percona XtraBackup is a solution that is both completely free and open-source.
SQLBackupAndFTP
Another example of a free MySQL backup solution is SQLBackupAndFTP. As the name suggests, it is an FTP-based backup solution with a rather basic set of features. It can offer cloud integration, backup encryption, a variety of notifications, as well as general SaaS availability. However, what it does not have is detailed scheduling or monitoring functionality, which makes it less than optimal for various enterprises and other large companies with heavier requirements in data protection.
Customer ratings:
- G2 – 4.3/5 stars based on 20 customer reviews
Advantages:
- Simple and effective automation capabilities
- Email notifications for different events, including backup completion
- A centralized account management console helps with general information management
Shortcomings:
- Restoring a backup in a location that is different from the original is not as easy and clear-cut as the regular restoration process
- Data transfer between backup servers is not completely stable
- Getting an answer from the customer support team is neither fast nor easy
Pricing:
- SQLBackupAndFTP has three different subscription-based pricing plans, and an additional plan for a lifetime license.
- “Lite” – $39 per machine as a one-time fee, as well as a $28 per year per machine for a full-service subscription, can offer basic backup destinations, only one basic backup type, and support for up to 5 databases at once
- “Standard” – $89 per machine as a one-time fee, as well as a $48 per year per machine for a full-service subscription, does not include AES encryption support, OneDrive for Business, Azure Storage, or Amazon S3 support
- “Professional” – $129 per machine as a one-time fee, as well as a $68 per year per machine for a full-service subscription, a complete set of SQLBackupAndFTP’s features, includes everything from all previous versions, as well as the aforementioned encryption, S3 support, etc.
- “Professional Lifetime” – $499 as a one-time fee for a single machine, includes free full-service subscription, offers a complete feature set of the solution identical to the “Professional” pricing plan, with the only difference being the fact that this is a perpetual license
- A 15% discount is applied to all orders that pay for 2 or more machines at the same time, and customers that wish to pay for over 50 devices at once can contact the solution in question in order to receive a personalized quote.
Vembu BDR Suite
Vembu BDR Suite is one of several examples on this list that represent some of the best solutions on the backup and recovery market as a whole. These kinds of solutions offer a variety of different modules or features to accommodate as many different backup types as possible. Vembu’s Apps and DB Backup edition is the one that has the capability to perform MS Exchange Server backups, SQL Server backups, and MySQL instance backups. It has an impressive set of features, including AES-256 data encryption, backup automation, granular recovery capabilities, remote backups, and more. This solution has several different tiers of pricing and the only way to try it out is to order a free 30-day trial for your company beforehand.
Customer ratings:
- Capterra – 4.5/5 stars based on 17 customer reviews
- TrustRadius – 8.4/10 stars based on 88 customer reviews
- G2 – 4.2/5 stars based on 130 customer reviews
Advantages:
- User interface is helpful and easy to work with
- Customer support team receives a lot of praise from many customers
- Support for a number of different operating systems, including a fully-functional Linux version of the software
Shortcomings:
- Vembu BDR Suite is a sophisticated solution that may take some time getting used to all of its features and unique qualities
- Customer support’s knowledge is mostly based on Windows version of the solution, which may not be the same when attempting to help a Linux user
- Some IT technologies and database are not supported
- The overall price of the solution is significantly higher than market average, especially after last year’s sudden price increase for all pricing plans
Pricing:
- Vembu BDR Suite’s pricing varies quite a lot depending on the target data source:
- VM Backup – from $18 per year per VM, with support for both Hyper-V and VMware deployments
- Server Backup – from $60 per year per server, both Linux and Windows servers are supported
- Application/DB Backup – from $60 per year per application/database, can work with SQL, MySQL, and Exchange
- SaaS Backup – from $12 per year per user, works for Google Workspace and Microsoft 365
- Cloud VM Backup – from $30 per year per AWS instance
- Endpoint Backup – from $15 per year per endpoint, supports Windows and Mac devices
- It is also worth noting that most of the BDR Suite’s backup solutions are also available for free with most of the features and a strict limitation on the number of workstations/VMs/servers it can work with.
- A lot more details about Vembu BDR Suite’s pricing can be obtained at the official pricing page.
Iperius Backup
Iperius Backup is a solution that may be suitable (like many discussed here) for smaller organizations. It may not be as popular as some of its competitors, but it is still a viable solution in its own right. It can offer backup and recovery options for several different database types – Oracle, SQL Server, PostgreSQL, and MariaDB. It is deployed on-premise using Windows Server, and there are multiple useful features that are included in the package. For example, there are features such as data compression, data encryption, hot backup capability, no limitations for the number of database instances covered per license, and so on. While Iperius’ backup plans do not include cloud storage by default, it is still a viable option that can be purchased separately – with an option to choose another cloud provider for backup storage purposes, if necessary.
Customer ratings:
- Capterra – 4.1/5 stars based on 47 customer reviews
- TrustRadius – 6.5/10 stars based on 6 customer reviews
- G2 – 4.5/5 stars based on 49 customer reviews
Advantages:
- Vast backup automation capabilities
- Reasonable pricing
- Support for cloud storage providers as backup locations
Shortcomings:
- User interface as a whole is not particularly simple or easy to use
- Tutorials in different languages may be lackluster
- Lack of Linux support
- Limited scalability
Pricing:
- Iperius Backup offers five different pricing plans for its users – although, only four of them can perform MySQL backup/recovery operations. All Iperius Backup licenses are perpetual
- “Basic” – €29 per PC or €69 per server, a basic set of backup features such as disk cloning, backup to cloud, incremental backups, backup to different target locations, disaster recovery, VSS support, etc.
- “Adv. DB” – €199 per single PC or server, a pricing plan tailored for database-related backups specifically, supports MySQL, MariaDB, PostgreSQL, Oracle, SQL Server, and does not have limitations on the number of databases covered
- “Adv. Exchange” – €199 per single PC or server, a M365/MS Exchange-oriented pricing plan, supports Exchange Online, Microsoft 365, Exchange On-Premise, Backup to PST with granular restore, and no limitations on the number of mailboxes that could be backed up or restored
- “Adv. VM” – €219 per single PC or server, a slightly different offering from Iperius with the aim of covering VM-related tasks, including support for both Hyper-V and VMware, as well as incremental backups with CBT, host-to-host replication, RCT, and so on
- “FULL” – €299 per single PC or server, a complete set of Iperius’s backup and recovery features, including Exchange, databases, VMs, and more
- Iperius also applies quantity-related discounts to all of the purchases
- 2-5 PCs/servers get a 10% discount
- 6-10 PCs/servers get a 16% discount
- 11-24 PCs/servers get a 22% discount
- 25-49 PCs/servers get a 28% discount
Handy Backup
Handy Backup might not be the most popular solution in the backup and recovery field, but it is still a great multifunctional service that can cover a variety of backup targets, from regular data and disk images to VMs and databases, including MySQL. As a competitive MySQL backup tool, Handy Backup offers a user-friendly interface, as well as features such as backup verification, backup automation, data compression, backup encryption, task scheduling, a multitude of backup storage options (from local HDDs to OneDrive, Amazon S3, Dropbox, NAS or even FTP/SFTP/FTPS), and more. It also has a portfolio of many prominent companies all over the world that use the solution, combined with lifetime licensing without additional fees.
Customer ratings:
Advantages:
- General versatility of the backup feature, supports multiple formats and locations, if necessary
- Server version’s backup capabilities are even wider, supporting practically every data format or system type that exists
- A centralized dashboard greatly simplifies backup management across the board
Shortcomings:
- A failed backup process is not easy to get back up and running right away, requires additional actions before that can be done
- The price of the solution is often regarded as somewhat high and not easily affordable for its target audience – be it regular users or small businesses
- Unlikely tom meet medium or large businesses’ needs
Pricing:
- Handy Backup has four different pricing tiers, but only two of them are capable of MySQL backup/recovery operations
- “Standard” – $39 per one device, a basic offering that supports a few cloud storage providers as backup targets, and is mostly capable of backing up emails, pictures, documents, and other personal data types
- “Professional” – $89 per one device, an advanced offering that is still more suitable for specific devices, with disk imaging, disaster recovery capabilities, system backups, cloud backup support, and so on
- “Small Business” – $249 per one device, a business-related offering that can create backups of Exchange data, as well as databases (MySQL, Oracle, MS SQL, DB2, etc.) and other storage types
- “Server Network” – $299 for a single management panel (Handy Backup Server Network), also requires both Server Agents ($149 per one) and Workstation Agents ($39 per one) for every workstation within the system that has to be backed up; offers a complete Handy Backup feature set capable of covering files, folders, databases, disk images, network servers, and so on
N-able Cove Data Protection
N-able Cove Data Protection is a MySQL backup solution with a singular purpose – to create backups that are both easy to manage and created to be as safe as possible. It is a web-based SaaS platform that offers a variety of useful features, such as backup monitoring, M365 integration, data archiving, and a granular file restoration tool. It has both virtual and physical backup support, while also being capable of future-proofing your backups with recovery testing tools. It can create backups of entire servers or specific VMs, and its centralized console is great for managing all of your backup operations in one place.
Customer ratings:
- Capterra – 4.7/5 stars based on 35 customer reviews
- TrustRadius – 8.8/10 stars based on 48 customer reviews
- G2 – 4.3/5 stars based on 228 customer reviews
Advantages:
- Generally high performance of backup/recovery operations across the board
- Local storage is not mandatory but it could be added if it is necessary for the client
- Single centralized source of information in the form of a dashboard with clearly traceable patterns and problems
Shortcomings:
- Some integrations are very limited in their capabilities, including M365 integration that can only create very basic backups
- Multiple issues with backup report emails, including aforementioned emails being sent out before the backup window closes
- Reporting features are lackluster in some specific cases
Pricing:
- Cove Data Protection’s pricing information is not publicly available on their official website and the only way to obtain such information is by contacting the company directly for a consultation or after initiating a free trial.
Ottomatik
Ottomatik is another great MySQL backup solution for smaller businesses that puts simplicity and minimalism above everything else. It is a SaaS-based MySQL backup tool that offers a short list of fundamental features in terms of MySQL backups as a whole. It has a one-click recovery feature, data encryption, a quick setup process, can modify permissions in accordance with specified roles, and is great for fast backup and recovery operations in general. There are four distinctive pricing plans that Ottomatik offers, each with its own limits in terms of hosted storage capacity from 1 to 15 gigabytes of storage included in the package.
Key features:
- MySQL backup automation capabilities
- Simple and easy initial setup process
- A lot of customization in terms of backup targets or frequency
- Fast disaster recovery when necessary, as well as testing and migration using backups
Pricing:
- Ottomatik has four different pricing plans
- “Free” plan costs $0, offers 1 backup job, 1 GB of storage, and daily backups
- “Lite” – $14 per month, has 3 backup jobs, 5 GB of storage, daily backups, and unlimited users
- “Professional” – $79 per month, offers access to automation and API, as well as 100 GB of storage, backups every 5 minutes, and up to 50 backup jobs
- “Agency” – $199 per month, the biggest offering of Ottomatik with 250 backup jobs, 200 GB of storage, API/automation, and capability to perform backups every single minute if necessary
SqlBak
Continuing the trend of providing MySQL backup software that is created specifically for MySQL backup purposes – SqlBak is one of the bigger players in this regard, offering extensive support for PostgreSQL, MS SQL, Azure SQL, and MySQL databases. It offers basic backup and recovery capabilities that you can find in any of the MySQL backup tools – but there are also features such as scheduling, encryption, compression, monitoring, and notifications management. It supports multiple backup locations, including Amazon S3, Google Drive, Dropbox, OneDrive, and regular FTP. It is also extremely convenient since it is a web-based service, meaning that backup and restore operations could be launched whenever you want, which is extremely useful when it comes to sudden restore operations, for example.
Customer ratings:
- G2 – 5/5 stars based on 10 customer reviews
Advantages:
- User-friendly interface
- Fast and resourceful customer support
- Relatively cheap, affordable for most potential users
Shortcomings:
- Backup error messages could be shorter and less detailed
- Any additional storage failing marks the entire backup process as failed
- Impossible to export backup reports en masse
Pricing:
- SqlBak offers three different pricing plans for its customers
- “Free” costs $0 to use, includes 1 server limit and 2 databases limit, can keep the backup history for 1 month and inform about server down errors as often as every hour
- “Standard” – $12 per server per month, expands upon the free version’s features with FTP support, Google Drive/OneDrive support, as well as 6 months of backup history and server notifications every 10 minutes
- “Professional” – $16 per server per month, can work with Azure Storage, Amazon S3, and S3 Compatible, as well as 5 minutes of notification cooldown, 12 months of backup history, data encryption, white labeling, extended support, and more
Commvault Backup and Recovery
Another example of a competent multifunctional backup solution that can also act as a MySQL backup software is Commvault – a comprehensive enterprise software package that is deployed on-premise with the capability to cover cloud accounts, applications, and entire servers. All-in-all it is a rather comprehensive solution that covers a lot of ground when it comes to backups in general. As for MySQL specifically – it offers regular backup and recovery capabilities, as well as incremental backups, ransomware protection, integrations with other services, data encryption, and more. All of that is also controlled using a single unified command center that is easy to work with, making it a great solution for all kinds of companies – from smaller businesses to large enterprises with multiple sites/MSPs.
Customer ratings:
- Capterra – 4.8/5 stars based on 9 customer reviews
- TrustRadius – 7.8/10 stars based on 207 customer reviews
- G2 – 4.2/5 stars based on 78 customer reviews
Advantages:
- Seamless integration into some of the most complex IT infrastructures imaginable
- Capability to integrate with a lot of other services when necessary
- Simple and quick backup configuration
Shortcomings:
- The solution itself can be relatively complex and not intuitive in some elements
- Commvault can be especially expensive
- Logging and reporting features are very basic and are not as detailed as most solutions on the market can offer
- Initial setup and the following configuration is somewhat long and time-consuming
Pricing:
- Commvault’s pricing information is not publicly available on their official website and the only way to obtain such information is by contacting the company directly for a demo showcase or a free 30-day trial.
- The unofficial information suggests that Commvault’s hardware appliances’ price ranges from $3,400 to $8,781 per month.
Comet Backup
Comet Backup is a relatively simple but effective backup solution that also has the capability to act as a MySQL backup software. It performs quick incremental backups and uses its own technology of “chunking” – turning data pieces into “chunks” that are both encrypted and compressed to increase security and lower the storage space required for backups. It also has client-side deduplication, low recovery time, backup endpoint customization, and an API that allows the solution to integrate with various data sources. The solution itself is sold based on specific categories of data that need to be covered, and the capability to perform MySQL backups falls under the “File & Folders Backup” service, which is as low as $2 per month.
Customer ratings:
Advantages:
- High degree of customizability
- A choice between many different cloud storage providers so that the customer always has the best price available for them
- Quick and helpful customer support
Shortcomings:
- Setting up the self-hosted option can be tough for someone unfamiliar with the usual industry processes
- User interface seems somewhat dated in many cases
- No mobile app available
Pricing:
- Comet Backup’s offering is comprised of the base subscription fee and a number of additional optional features that also cost extra
- “Base Charge” – $2 per device per month, includes backup capabilities for files/folders, Windows System backup, MySQL support, Program Output, etc.
- Synology – $1 per device
- MongoDB – $1 per device
- Disk Image Backup – $3 per device
- Application-Aware Writer – $1 per device
- Microsoft Office 365 – $1.50 per user
- Microsoft SQL Server – $1 per device
- Microsoft Hyper-V – $1 per host
- Microsoft Exchange Server – $1 per device
- Comet Storage – $5.99 per TB per month
- CloudView CRM – $49 flat
- Comet-Hosted Server – $49 for first 500 devices + boosters, and $39 for each additional case of 500 more devices + boosters
Quest Rapid Recovery
Quest Rapid Recovery is another notable MySQL backup tool with a telling name – since it focuses a lot on making the data recovery process as quick as possible. It uses database snapshots that are then deduplicated and compressed to make the overall capacity demands lower than usual, while also being able to quickly restore all of the data to its original state if necessary. It also has file prioritization, integrity checks, and support for most of the popular storage solutions. All of the solution’s capabilities are combined in an easy-to-use interface with a dashboard and other convenient features.
Customer ratings:
- Capterra – 4.7/5 stars based on 6 customer reviews
- TrustRadius – 7.4/10 stars based on 32 customer reviews
- G2 – 4.0/5 stars based on 41 customer reviews
Advantages:
- User-friendly interface
- Capable of minimizing the amount of data created for backup purposes
- Impressive customer support response time
Shortcomings:
- VM restore process takes a lot of time
- File restoration from a Linux box is difficult
- Existing training materials are insufficient
- Perhaps not advantageous to medium and large businesses
Pricing:
- There is no public pricing information available on the official Quest Rapid Recovery website, the only way to obtain such information is to contact the company directly and request a personalized quote.
Backup Ninja
Finishing off this list with another free solution, we have Backup Ninja – an economical backup solution that specializes in working as a MySQL backup tool while also supporting other database types. It can offer partial backups, incremental backups, backup automatization, data encryption, and hosting backups either in the cloud or locally, while also offering extensive backup statistics. It also has a centralized dashboard for all of its features to make the overall control over the backup process that much easier. There are two versions of Backup Ninja available – the first one is free and more suitable for smaller companies, while the second one is paid and has no limitations in terms of storage capacity or encryption for backups.
Key features:
- Extensive data security
- Variety of storage options, including both local and cloud storage providers
- The interface is user-friendly and fairly simple
Pricing:
- Backup Ninja’s pricing model is relatively simple and only includes two pricing plans
- “Free” is the basic version of the solution that costs $0, it is limited to a single backup and single restore operations per day, it can only save backups to local storage, and does not have encryption or on-demand backups
- “Business” – $40 per month per agent, no limitations on the number of backup/restore operations, works with both cloud and local storage, can encrypt backups, does not have limitations in terms of backup sizes, and many other features
SimpleBackups
SimpleBackups is a relatively new backup solution that works with all kinds of environments – servers, databases, applications, cloud storage, etc. It can offer AES-256 encryption to all of its backups, a versatile but user-friendly interface, and even the ability to extend its own functionality with a dedicated system of plugins and add-ons. MySQL databases are one of many storage types SimpleBackups can work with, providing simple yet effective backup automation, custom flags, serverless incremental backups, no need to lock tables for the backup process, and also support for larger databases, as well.
Customer ratings:
Advantages:
- A user-friendly and convenient interface that is accessible enough to be operated even by someone with little to no technical background
- A surprisingly low price, considering the number of features SimpleBackups offers to its users
- Speaking of features, SimpleBackups has plenty to offer, from data encryption to different backup types, different storage types, and more
Shortcomings:
- A very limited free plan that can barely act as a demo version of the solution
- Due to the solution being relatively new, there can still be some issues here and there with the interface or the features
- Security may be an issue
Pricing:
- SimpleBackups offers four different pricing plans, as well as a free plan and a free trial
- A free plan is very limited, it can only work with one customizable backup job, can only work with the most basic combination of target locations (MongoDB, MySQL, Redis, PostgreSQL), and cannot perform backups more often than once a day
- All of the remaining pricing plans are subscription-based, but also far more impressive feature-wise:
- “Startup” – $34 per month, is limited to 20 backup jobs, can perform backups every 6 hours, includes 30 Gb of storage (as well as unlimited BYO storage), and up to 2 team members
- “Agency” – $99 per month, limited to 50 backup jobs, can perform backups every hour, has no team member limitations, customizable post-backup and pre-backup scripts, automation, and more features
- “Business” – $199 per month, up to 200 backup jobs, backups performed every single minute, no limitation on the project number or the number of team members, in addition to API access, advanced automation, etc.
- “Enterprise” is also technically a pricing tier of SimpleBackups, the highest one you can have – it supports SSO, custom contracts, priority support, custom integrations, and also a “custom” price that can only be acquired by requesting a personalized quote and/or schedule a demo from the company
It is clear to see that there is quite a lot of competition when it comes to MySQL backup operations. But there is one solution that we have yet to go over – Bacula Enterprise. This is a broad, comprehensive backup and recovery solution that comes with especially high levels of security. It is popular with military, defense, government and supercomputing users, but is also used by medium sized enterprises due to its non capacity-based licensing and open source roots.
The purpose of MySQL backup with Bacula Enterprise
The general purpose of the MySQL backup module from Bacula Enterprise is to automate backup and restore processes. While using this module, the backup administrator doesn’t have to know all of the ins and outs of MySQL backup techniques and there’s no requirement for any script writing skills, either. The module itself is capable of performing a variety of different functions, including automatic backup for config files and other important system data. There are two main backup methods: dump and binary.
Binary or Dump?
Since there are two main backup techniques, it’s better to present a direct comparison of some of their features in a table below.
| Capabilities | Binary | Dump |
| Single object restore | No | Yes* |
| Backup size | Big | Small |
| Backup speed | Fast | Slow |
| Restore speed | Fast | Very slow |
| Capable of restoring at any time | Yes | Yes |
| Support for differential and incremental backup types | Yes | Yes |
| Consistency | Yes | Yes |
| Online backup | Yes | Yes |
| Restoration to previous major MySQL version | No | Yes** |
| Restoration to newer major MySQL version | No | Yes |
* Manual editing of a dump file is required to perform a single object restore.
** Manual editing of the SQL file might be required to restore an SQL dump to an older MySQL version if you’re using features that are not available on the older version. Generally speaking, restoration to an older MySQL version is not guaranteed and not supported.
How it works: Binary mode
Binary mode’s main working principle is its interaction with Percona XtraBackup that allows it to perform the backup process without stopping it to begin with. It’s capable of working with InnoDB, XtraDB, MyISAM and other tables with unmodified MySQL, or with a Percona server with XtraDB.
Previously there were two main ways of performing a database copy. The first one implied copying the active database, and this method had a quite high chance of having all sorts of discrepancies in the copy, or might be just missing all of the changes that happened in the database for the length of time that the copy was being created. The second one implied fully stopping the database to make a consistent copy. But the problem with the second way is that potentially (and usually) every second of a business’ database not working represents a loss of revenue, no matter how small that stopping period is.
For this exact purpose, the MySQL Percona module from Bacula and Percona XtraBackup tool offer an effective solution to this problem by having the ability to create full or incremental backups of any kind of running and active MySQL databases. One more advantage of the Percona-connected mode is that it doesn’t need to use large temp files that are the duplicates of the data that you’re backing up or restoring.
The restoration process is relatively simple, with data being restored to a temporary location and then changed to be consistent with the help of the “prepare” function. This function would, with other solutions, be very resource-heavy, and not something that you would want to perform during an urgent recovery situation. Therefore, to prevent adding a lot of time to the restoration process, Bacula’s “prepare” function is performed automatically and in advance, right after the backup is created. This adds a slight time to the backup process but makes the restore process far faster. In case of emergencies, the restore time is far more critical than the backup time. And in a situation where the database in question is an extremely large, high-transaction database, this time difference can even be the difference between an organization’s ability to stay in business at all.
The need for this combination of Bacula Enterprise MySQL Percona module and Percona tools comes from the problematic existence of the traditional MySQL. The main trouble of creating a “mysqldump” is that it’s not a true binary backup of the data, the entire process is about converting the entire database in the large amount of SQL statements and processing them afterwards as part of the restore process. This exact process as it is gets extremely slow the larger your database is.
Percona tools come in handy when there’s a situation that puts a heavy load on the MySQL in general and both performance and stability are necessary for the system to operate properly.
Any company that needs to restore their failed server safely and quickly to protect their business from severe failure, or even going bankrupt can take advantage of this special combination of Percona tools with Bacula’s capabilities to make the restore process as quick as possible in an Enterprise environment.
How it works: Dump mode
Dump mode is more or less the “legacy” backup option that is still relevant in specific cases. It relies on the log files that are created in the process of MySQL database’s existence, and those logs are utilized by the point-in-time recovery system to perform various feats with your data, like replication, protection, and so on.
The default backup method is to dump each of your databases separately, meaning that your data won’t be completely consistent if you’ll restore several databases that were backed up at different time periods and have no consistency between each other. This is a significant issue if you’re working with multiple databases at once, and Bacula’s solution to that is to save the log files that are generated during each of the backup processes and then play back those files to make sure that all of the databases are globally consistent with each other at a time.
There’s also another, more global, solution to the problem as well – the all_databases option. It allows all of the databases to be dumped at once, making it so that you’re sure that your databases are consistent and you can perform multiple incremental backups after this full one with no fear of global inconsistency.
There’s a specific list of files that MySQL agent generates in the Bacula catalog for a single “test” of your database. You can see those file entries and what they are needed for in the table below.
| File name | Appliance | Description |
| global-grants.sql | global | Unified list that specifies all of the users, their passwords and other specific data |
| createdb.sql | database | Script about creating the database |
| grants.sql | database | A list that specifies every user that is associated with this database |
| data.sql | database | Your database’s data in dump format |
| settings.txt | global | Present variables of the MySQL server |
| schema.sql | database | Script about creating the database schema |
| my.cnf | global | Server configuration data for MySQL |
MySQL restore modes
Bacula’s MySQL agent is capable of providing several different restore modes, such as:
- Single database restore;
- Dump or binary logs restore;
- Point-in-time restore;
- Users and roles restore.
As mentioned, Bacula Enterprise utilizes Percona tools with its MySQL agent to restore any information about a binary mode backup.
Conclusion
As with almost any type of database, performing backups of MySQL is essential for all organizations and businesses, ultimately to ensure their business continuity and legal obligations. When one considers the pervasiveness of the relational database in industry and commerce, it is only natural that there are a vast range of MySQL backup solutions for all kinds of companies.
There is a backup solution for pretty much any type of user: smaller companies with limited budgets can use Backup Ninja or SQLBackupAndFTP for their basic backup tasks with zero additional cost, while large enterprises are likely to get more value out of comprehensive solutions such as Vembu BDR Suite or Bacula Enterprise. Both of them are enterprise-level backup platforms with a variety of different features aside from MySQL backup support. Vembu is more focused on a more narrow set of features and being somewhat expensive in the long run, while Bacula Enterprise has especially high security, huge scalability, uses a completely different (arguably, more affordable) pricing model and also offers one of the widest feature sets on the entire market with its module-like infrastructure.
Finding a solution that can create a backup of your MySQL databases should not be an overly difficult task. We hope that this article was helpful to you in terms of identifying which solution would work best in your specific use case.
Why you can trust us
Bacula Systems is all about accuracy and consistency, our materials always try to provide the most objective point of view on different technologies, products, and companies. In our reviews, we use many different methods such as product info and expert insights to generate the most informative content possible.
Our materials offer all kinds of factors about every single solution presented, be it feature sets, pricing, customer reviews, etc. Bacula’s product strategy is overlooked and controlled by Jorge Gea – the CTO at Bacula Systems of Bacula Systems, and Rob Morrison – the Marketing Director of Bacula Systems.
Before joining Bacula Systems, Jorge was for many years the CTO of Whitebearsolutions SL, where he led the Backup and Storage area and the WBSAirback solution. Jorge now provides leadership and guidance in current technological trends, technical skills, processes, methodologies and tools for the rapid and exciting development of Bacula products. Responsible for the product roadmap, Jorge is actively involved in the architecture, engineering and development process of Bacula components. Jorge holds a Bachelor degree in computer science engineering from the University of Alicante, a Doctorate in computation technologies and a Master Degree in network administration.
Rob started his IT marketing career with Silicon Graphics in Switzerland, performing strongly in various marketing management roles for almost 10 years. In the next 10 years, Rob also held various marketing management positions in JBoss, Red Hat, and Pentaho ensuring market share growth for these well-known companies. He is a graduate of Plymouth University and holds an Honours Digital Media and Communications degree.
