You have already used mysqldump to backup your database if you have been using MySQL for a while. I am going to show you how to build a quick complete and partial backup using mysqldump in part one of this blog. I’ll show you how to use MySQL Enterprise Backup (the successor of the InnoDB Hot Backup product) in part two. MySQL Enterprise  Backup helps you to back up the database when it is online and during backup operations, it holds the database open to users (you don’t have to take the database offline or lock any databases/tables, but you need to use the option-no-locking to do this).

Mysqldump will deal with this article. For those of you not acquainted with mysqldump:

The mysqldump client is a utility that performs logical backups, producing a set of SQL statements that can be run to reproduce the original schema objects, table data, or both. It dumps one or more MySQL database for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format

Mysqldump’s best attribute is that it’s easy to use. When you need to rebuild a database, the primary issue of using mysqldump exists. The database backup (output) is a SQL file that contains all the required SQL statements to restore the database when you run mysqldump, but the restoration allows you to execute these SQL statements to essentially restore the database. Since you are recreating from this file your database, If you have a very large database, the restoration process will take a long time to conduct.

With mysqldump, there are a lot of features and options. I’m not going to analyze all of the features, but I’m going to clarify some of the ones I use. 

Here is the mysqldump command to clearly backup all of your databases (assuming you have tables with InnoDB). A dump (backup) file called all databases.sql is created by this command.

mysqldump --all-databases --single-transaction --user=root --password > all_databases.sql

You’ll have to enter your password after you hit Return. After the password option, you can provide the password (example:-password = my password), but this is less secure and you will get the following error:

Warning: Using a password on the command line interface can be insecure.

Here is some information about the options that were used:

--all-databases - this dumps all of the tables in all of the databases
--user - The MySQL user name you want to use for the backup
--password - The password for this user.  You can leave this blank or include the password value (which is less secure)
--single-transaction - for InnoDB tables

If you are using Global Transaction Identifier’s (GTID’s) with InnoDB (GTID’s aren’t available with MyISAM), you will want to use the –set-gtid-purged=OFF option. Then you would issue this command:

mysqldump --all-databases --single-transaction --set-gtid-purged=OFF --user=root --password > all_databases.sql

Otherwise you will see this error:

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

A partial backup of all the databases may also be performed. This example will be a partial backup because the default MySQL databases (created during installation)-mysql, test, PERFORMANCE SCHEMA and INFORMATION SCHEMA will not be backed up by me.

Note: mysqldump does not dump the INFORMATION_SCHEMA database by default. To dump INFORMATION_SCHEMA, name it explicitly on the command line and also use the –skip-lock-tables option.

mysqldump never dumps the performance_schema database.

mysqldump also does not dump the MySQL Cluster ndbinfo information database.

Mysqldump does not dump the general log or sluggish query log tables for MySQL database dumps prior to MySQL 5.6.6. As of 5.6.6, the dump contains statements to restore certain tables so that after reloading the dump file, they are not missing. The contents of a log table are not discarded.

You’ll need a list of the databases that you want to backup in order to do a partial backup. A list of all databases can be accessed by simply executing the command SHOW DATABASES from the mysql prompt:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| comicbookdb        |
| coupons            |
| mysql              |
| performance_schema |
| scripts            |
| test               |
| watchdb            |
+--------------------+
8 rows in set (0.00 sec)

In this example, since I don’t want to backup the default mysql databases, I am only going to backup the comicbookdb, coupons, scripts and watchdb databases. I am going to use the following options:

--databases - This allows you to specify the databases that you want to backup.  You can also <a href="http://dev.mysql.com/doc/mysql-enterprise-backup/3.6/en/partial.html">specify certain tables</a> that you want to backup.  If you want to do a full backup of all of the databases, then leave out this option
--add-drop-database - This will insert a DROP DATABASE statement before each CREATE DATABASE statement.  This is useful if you need to import the data to an existing MySQL instance where you want to overwrite the existing data.  You can also use this to import your backup onto a new MySQL instance, and it will create the databases and tables for you.
--triggers - this will include the triggers for each dumped table
--routines - this will include the stored routines (procedures and functions) from the dumped databases
--events - this will include any events from the dumped databases
--set-gtid-purged=OFF - since I am using replication on this database (it is the master), I like to include this in case I want to create a new slave using the data that I have dumped.  This option enables control over global transaction identifiers (GTID) information written to the dump file, by indicating whether to add a SET @@global.gtid_purged statement to the output.
--user - The MySQL user name you want to use
--password - Again, you can add the actual value of the password (ex. --password=mypassword), but it is less secure than typing in the password manually.  This is useful for when you want to put the backup in a script, in cron or in Windows Task Scheduler.
--single-transaction - Since I am using InnoDB tables, I will want to use this option.

Here is the command that I will run from a prompt:

mysqldump --databases comicbookdb coupons scripts watchdb --single-transaction --set-gtid-purged=OFF --add-drop-database --triggers --routines --events --user=root --password > partial_database_backup.sql

I will need to enter my password on the command line. After the backup has completed, if your backup file isn’t too large, you can open it and see the actual SQL statements that will be used if you decide that you need to recreate the database(s). If you accidentally dump all of the databases into one file, and you want to separate the dump file into smaller files, see my post on using Perl to split the dump file.

For example, here is the section of the dump file (partial_database_backup.db) for the comicbookdb database (without the table definitions). (I omitted the headers from the dump file.)

--
-- Current Database: `comicbookdb`
--
 
/*!40000 DROP DATABASE IF EXISTS `comicbookdb`*/;
 
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `comicbookdb` /*!40100 DEFAULT CHARACTER SET latin1 */;
 
USE `comicbookdb`;
 
--
-- Table structure for table `comics`
--
 
DROP TABLE IF EXISTS `comics`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `comics` (
  `serial_id` int(7) NOT NULL AUTO_INCREMENT,
  `date_time_added` datetime NOT NULL,
  `publisher_id` int(6) NOT NULL,
....

If you are using the dump file to create a slave server, you can use the –master-data option, which includes the CHANGE MASTER information, which looks like this:

--
-- Position to start replication or point-in-time recovery from
--
 
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=79338;

If you used the –set-gtid-purged=ON option, you would see the value of the Global Transaction Identifier’s (GTID’s):

--
--GTID state at the beginning of the backup 
--
 
SET @@GLOBAL.GTID_PURGED='82F20158-5A16-11E2-88F9-C4A801092ABB:1-168523';

By using the no-data option, you can also test your backup without exporting any data. This will show you all of the database and table development information, but it will not export any data. This is also useful on the same or another server for recreating a blank database.

Mysqldump can build INSERT INTO statements to import the data into the tables when you export your data. By default, however, INSERT INTO statements contain multiple-row INSERT syntax which includes multiple lists of VALUES. This allows for the data to be imported faster.But, if you think your information could be corrupt and you want to be able to isolate a certain data row, or if you only want to have one INSERT INTO statement per data row, then you can use the option of skip-extended-insert. If you use the option of skip-extended-insert, it will take much longer to complete the data import, and the backup file size will be larger.

It is simple to import and restore the data. You may simply use the mysql command to import data: to import the backup file into a new, blank instance of MySQL.

mysql -uroot -p < partial_database_backup.sql

Again, after the -p option (less secure), you will need to enter your password or you can include the value. 

With href= “http:/dev.mysql.com/doc/refman/5.6/en/mysqldump.html”>mysqldump, there are several more choices you may use. The key thing to note is that, on a regular basis, you can backup your data and transfer a copy of the backup file away from the MySQL server.

Finally, here’s a Perl script that I’m using to backup my databases with cron. This script allows you to use the mysql bak.config file to define which databases you want to backup. This configuration file is simply a list of the databases that you want to backup, with the ability to disregard any database that is # -commented. As you have to embed the MySQL user password into the script, this is not a stable script.

#!/usr/bin/perl
# Perform a mysqldump on all the databases specified in the dbbackup.config file
 
use warnings;
use File::Basename;
 
# set the directory where you will keep the backup files
$backup_folder = '/Users/tonydarnell/mysqlbak';
 
# the config file is a text file with a list of the databases to backup
# this should be in the same location as this script, but you can modify this
# if you want to put the file somewhere else
my $config_file = dirname($0) . "/mysql_bak.config";
 
# example config file
# You may use a comment to bypass any database that you don't want to backup
# # Unwanted_DB    (commented - will not be backed up)
# twtr
# cbgc
 
# retrieve a list of the databases from the config file
my @databases = removeComments(getFileContents($config_file));
 
# change to the directory of the backup files.
chdir($backup_folder) or die("Cannot go to folder '$backup_folder'");
 
# grab the local time variables
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$year += 1900;
$mon++;
#Zero padding
$mday = '0'.$mday if ($mday<10);
$mon = '0'.$mon if ($mon<10);
 
$hour = "0$hour" if $hour < 10;
$min = "0$min" if $min  $folder/$file.Z`;
 
	print "Done\n";
}
print "Done\n\n";
 
# this subroutine simply creates an array of the list of the databases
 
sub getFileContents {
	my $file = shift;
	open (FILE,$file) || die("Can't open '$file': $!");
	my @lines=;
	close(FILE);
 
	return @lines;
}
 
# remove any commented tables from the @lines array
 
sub removeComments {
	my @lines = @_;
 
	@cleaned = grep(!/^\s*#/, @lines); #Remove Comments
	@cleaned = grep(!/^\s*$/, @cleaned); #Remove Empty lines
 
	return @cleaned;
}

By kwame

Leave a Reply

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

//