Loading...Contents
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 conventional ways of creating a MySQL database backup without any 3rd party MySQL backup solutions.
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 first five digits of the command are representing the time and date (minutes, hours, days of month, months, days of week, respectively), and putting “*” instead of one of those numbers means that any number works.
2. 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);
}
3. 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’;
?>
4. 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
5. 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.
6. 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.
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 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
