A database can be considered one of the most important parts of any application, since it represents the place where an app’s data is stored and managed. Most applications can be rendered useless just by removing or breaking their databases. This is the main reason why correct and regular backup of your databases is important, and this is just as true if your business or server applications are working on MySQL.
The importance of MySQL databases and reliable backup systems
MySQL in its core is a great data repository for a variety of applications (accounting, ERP, web server apps, etc) because of its low-cost open-source licensing rules as well as its efficient and flexible SQL (Structured Query Language) architecture. According to various reports, MySQL is used in one way or another in about ¼ of all medium-sized and larger companies. Of course, such a large technology adoption sees with it a lot of different premium MySQL backup services. At the same time, there’s also a number of MySQL backup open source tools available to try for free, as well.
You can save a lot of hard work - and revenue - in case of a data-loss disaster if you’ve got a good MySQL backup set up and working. The same goes for problems that might happen during the process of system migration events.
Both software and hardware changes are inevitable in any business environment, including software upgrades, server hardware changes and the addition of more systems is just a fraction of the reasons why most IT departments are continually dealing with migrations, installs and reinstallations of all kinds. At the same time, if you know something about this process as a whole, you probably know that one of the main challenges is data migration, rather than the software or hardware update itself.
It’s common for freshly installed software to create new databases for the program, and there’s a lot of cases when there’s no import and export tools included in that same program to allow you to transfer your previous data. That’s why manual MySQL database transfer is a common thing when it comes to fresh systems or software. Subsequently, this leads to just one more reason why it’s important to have a reliable MySQL backup system in place to begin with.
That said, there are a few important steps worth taking before starting up the actual database backup process. First, you’ll likely need to stop the database itself from running at the moment of backup, to prevent data corruption and backup inconsistency. There are two main steps that are required here: closing all of the apps that work with MySQL database and shutting down the MySQL server application itself.
Of course, a lot of businesses would be unhappy - or simply unable - to shut down databases if the business itself uses them directly and constantly (enterprise software, corporate website backend, etc). In many cases, downtime means revenue losses. In some cases of large, high transaction databases, downtown can be quantified in lost dollars-per-minute (or even second). That’s why it may be important to keep database downtime as short as possible.
Creating a MySQL database backup
There’s a number of different ways to create a MySQL database backup for different systems. We’ll go over six of the most common ones:
1. Automatic MySQL database backups on Linux.
It’s possible via a Linux/Unix-based utility called cron. It is a utility that can start up with the system and reads a specific configure file to know what to do. The usual location for the cron configuration is /etc/crontab. Adding the following line allows you to set up a daily backup in 2:30 am:
30 2 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip >
/mnt/disk2/database_`data ' %m-%d-%Y'`.sql.gz
First five numbers are supposed to be minutes, hours, days of month, months, days of week respectively, and “*” means “any”.
2. Creating an XML file using PHP
This one is not the most reliable format when it comes to data restoration, but it is easier to interact with for some people. Here’s a PHP snippet that exports the specific database in the XML file 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. Backup using PHP
Here’s another example of using PHP for backup purposes - to take a database backup query from PHP file and use it. Here’s an example of creating table backup by using a specific query (SELECT INTO OUTFILE query, to be specific):
<?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 here’s a second query that is used for backup restoration purposes (LOAD DATA INFILE query):
<?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. SSH backup
You can also use SSH to backup particularly large MySQL databases, as well. You’ll start with enabling shell access in the settings of your Plesk control panel, then you’ll have to use PuTTY or a similar utility to gain access to your server via SSH. Next you’ll have to specify the directory for saving your backup files:
CD wwwroot/dbbackup
The last part is to use mysqldump command for exporting your data into a specific file on the server (it is possible for a password prompt to appear after entering this command):
mysqldump --add-drop-table -u db_username -p db_name > mybackup.sql
5. Ubuntu’s MySQL backup server Shell script
If there’s a dedicated VPS server in your possession that runs Ubuntu Linux, you can also use MySQL server Shell script for backup purposes to backup all of your MySQL server databases to your FTP server. Here’s the contents of this specific Shell script (login and password should be specified before launching the script):
#!/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 saving this as a .sh file (for example, /home/your-name/mysql.backup.sh), you’ll have to setup the executable permissions for this file:
$ chmod +x /home/your-name/mysql.backup.sh
And here’s two commands that should launch your MySQL backup process:
/home/your-name/mysql.backup.sh
Or:
sudo /home/your-name/mysql.backup.sh
It is also possible to run this script as another cron job (cron was discussed earlier in the article). For example, if we want the script to run each day at midnight, we’ll need two commands:
$ sudo crontab -e
And:
@midnight /home/you/mysql.backup.sh >/dev/null 2>&1
These scripts can also work with different Linux or UNIX distributives, as well.
6. MySQL database backup using phpMyAdmin
phpMyAdmin is a free open source tool for administration of MySQL and MariaDB databases. Here’s the correct process:
- Open phpMyAdmin;
- Select the database you want to back up;
- Click the “export” link in the navigation bar;
- Choose “Custom – display all possible options”;
- Select the specific tables you want to back up, the default is when all of them are selected;
- Compression type gzipped is recommended;
- Click the “Go” button to initiate the backup process and wait for your backup file to be downloaded automatically.
These were some of the most common options for basic MySQL database backups.
Bacula’s free MySQL backup software
There are, of course, many other options for MySQL backup and recovery, not least of which includes third-party backup solutions that are capable of managing the entire backup and recovery process without requiring the knowledge of command lines and shell scripts. One such example is Bacula’s MySQL backup open source solution.
Bacula’s free MySQL backup software is created to both optimize the MySQL database backup speed and make the entire process simpler, easier and more reliable than it may otherwise be. For example, using Bacula means your system administrator does not have to know the various different MySQL backup techniques to create even the most complex backup jobs. There’s also a range of options to automatically back up the most valuable or critical information, such as user definition or configuration files. It also provides control over the entire process via a choice of command line interfaces, or GUI tools.
Bacula’s free MySQL backup tool can help you with keeping your MySQL databases safe and ready for any disaster that might occur.
If you’re also interested in Linux Backup, check out the Bacula’s solution.