Home > Backup and Recovery Strategies Blog > How to backup MySQL database? MySQL database backup guide.
1 Star2 Stars3 Stars4 Stars5 Stars
(7 votes, average: 4.90 out of 5)
Loading...

How to backup MySQL database? MySQL database backup guide.

  • June 24, 2020, Rob Morrison

The need for MySQL database backups are not as universal as is as is the need for regular end-user backups, but when required, it becomes an especially important part of a company’s infrastructure. Bacula Enterprise’s module for MySQL backups allows users to perform efficient and user-friendly MySQL database backups - and more importantly, restores -  in a very short amount of time. This of course, is critical in meeting any RTO’s (recovery time objectives). This article delves deeper into the very nature of the backup process that makes Bacula Enterprise so much better than its competitors, including different approaches to backup and recovery, and different customization options.

Different ways to create a MySQL database backup

Before we move on to Bacula Enterprise's solution, let’s go over some of the different, more unconventional ways of creating a MySQL database backup.

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.

The purpose of MySQL backup module with Bacula Enterprise

The general purpose of the MySQL backup module from Bacula Enterprise is to make both backup and restore processes easier and faster at the same time. 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.

CapabilitiesBinaryDump
Single object restoreNoYes*
Backup sizeBigSmall
Backup speedFastSlow
Restore speedFastVery slow
Capable of restoring at any timeYesYes
Support for differential and incremental backup typesYesYes
ConsistencyYesYes
Online backupYesYes
Restoration to previous major MySQL versionNoYes**
Restoration to newer major MySQL versionNoYes

* 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 nameApplianceDescription
global-grants.sqlglobalUnified list that specifies all of the users, their passwords and other specific data
createdb.sqldatabaseScript about creating the database
grants.sqldatabaseA list that specifies every user that is associated with this database
data.sqldatabaseYour database’s data in dump format
settings.txtglobalPresent variables of the MySQL server
schema.sqldatabaseScript about creating the database schema
my.cnfglobalServer 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

New technology across all aII environments is developed constantly. For that reason most businesses benefit from a backup solution that innovates regularly, and ensures that it can cover all kinds of technologies in a IT department or data center - whether those technologies be new or old.  The result of using a modern, innovative backup solution is typically one of shorter RTOs and RPOs, lower maintenance, lower costs, and much higher security. Bacula Enterprise is a good example of such a backup solution provider.

About the author

Rob Morrison Rob on LinkedIn

Rob Morrison is the marketing director at Bacula Systems. He 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, and completed an Overseas Studies Program.

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>