Easy-to-use Perl scripts to backup your MySQL database with mysqldump and FTP the files to a remote server

Most users of MySQL utilize the mysqldump utility to backup their database. While mysqldump is handy and easy-to-use (and free), if you have data which is important to your business, then you should take a look at the MySQL Enterprise Edition – and use MySQL Enterprise Backup instead. The MySQL Enterprise Backup allows you to backup your database without the table locking you get with mysqldump. And, it is extremely fast – especially when you have to restore your database. Here is a sample speed comparison between MySQL Enterprise Backup and using mysqldump:

49x Better Performance: Backup

80x Better Performance: Backup

From the image, you can see it takes a long time to either dump or restore a fairly large (73 gigabyte) database compared to mysqldump. Even if your database isn’t this large (and most people don’t care how long their backups take to complete), when it comes time to restore your database in a production environment, the quicker you can restore your database, the better.

If your only option is to use mysqldump, here are two Perl scripts to make the task easier. The first script will backup your database(s) and send a copy of your backup to a remote server via FTP. The second script will connect to your FTP server and delete your old backup files – in case you have a storage limit on your FTP server. You can put these scripts in cron or Windows Task Scheduler, or run them manually. You can have the backup script run as often as possible (maybe once an hour) – but keep in mind there will be table-level locking. The script to delete the old backups only needs to be run once a day.

THE BACKUP SCRIPT

For the backup script, you will have to enter a few variables to match your system. You will also need to create a configuration file of all of the databases you want to backup. I could have connected to the MySQL database and ran a query (SHOW DATABASES;) to retrieve all of the databases, but I prefer to manually manage the list of databases to backup. With this method, you can skip an hourly backup of static or read-only databases and only backup the databases which are being changed. This configuration file is a text file with a list of the databases to be backed up, and you can use a # (pound sign) to comment out databases you want to skip.

NOTE:You don’t want to backup the following databases: PERFORMANCE_SCHEMA, INFORMATION_SCHEMA or SYS SCHEMA.

# set the directory where you will keep the backup files
$backup_folder = '/Users/tonydarnell/cron/mysqlbackups';

# 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_backup.config";

# Here is where you will put your FTP server name (or IP address)
# and your FTP username and password
my $host = "server_name.com";
my $user = "username";
my $password = "password";

You can also modify your mysqldump command which will be use to backup your databases, or use what I have in the script. You will need to add your mysql password where I have the word “PassWord“.

`/usr/local/mysql/bin/mysqldump -R -h192.168.1.2 --events --triggers -u mysqlbackup --password=PassWord --routines --add-drop-database --set-gtid-purged=OFF --add-drop-table $database $table | compress > $folder/$file.Z`;

I created a separate FTP user which has its own home directory, so I don’t have to change the directory once I login via FTP. If you can’t do this, you will need to uncomment the last three lines of this and navigate your FTP user to the correct directory:

# uncomment the last three lines if you can't set the home directory of the FTP user to a specific directory
# the directory on the FTP server where you want to save the backup files
# my $dir = "mysqlbackups";
# print "Accessing FTP - changing to $dir folder\n";
# $f->cwd($dir) or die "Can't cwd to $dir\n";

That is all you need to modify in the first script for it to work. If you run this script on the command line, or if you run it in cron, you should see something like this:

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

It isn’t a good idea to have your password in plain text anywhere, so you should create a user which only has the limited read-only permissions needed to run mysqldump. You will need to change the value of “database_name” in the GRANT statement to match each database you want to backup. You will need to run the GRANT statement for every database you want to backup, or you can use an asterisk “*” in place of the database name.

CREATE USER 'mysqlbackup'@'192.168.1.2' IDENTIFIED WITH sha256_password BY '';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON `database_name`.* TO 'mysqlbackup'@'192.168.1.2';

Or, to grant permissions to the mysqlbackup user on all of the tables:

GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'mysqlbackup'@'192.168.1.2';

Here is a sample output from the script:

# perl mysql_backup.pl
Beginning 2017-01-06-16:35:57
Backing up database_01 - 2017-01-06-1635 ... mysqldump: [Warning] Using a password on the command line interface can be insecure.
Finished exporting database_01 - as 2017-01-06-1635/database_01.sql.Z
Backing up database_02 - 2017-01-06-1635 ... mysqldump: [Warning] Using a password on the command line interface can be insecure.
Finished exporting database_02 - as 2017-01-06-1635/database_02.sql.Z
Backing up database_03 - 2017-01-06-1635 ... mysqldump: [Warning] Using a password on the command line interface can be insecure.
Finished exporting database_03 - as 2017-01-06-1635/database_03.sql.Z
....
Backing up database_04 - 2017-01-06-1635 ... mysqldump: [Warning] Using a password on the command line interface can be insecure.
Finished exporting database_04 - as 2017-01-06-1635/database_04.sql.Z
------------------------------------------------------------------
 Compressing backup as:  2017-01-06-1635.tar.gz 
------------------------------------------------------------------
Creating a tar file from the backup...
tar -czf 2017-01-06-1635.tar.gz 2017-01-06-1635
FTP'ing the file - 2017-01-06-1635.tar.gz 
Deleting the original backup directory and files.
Finished 2017-01-06-16:36:49

The backup script creates a new directory with a timestamp as the name of the directory. In this example, the directory was named “2017-01-06-1635“. After the backup is finished, it creates a single tar file and then deletes the backup directory and files. I delete the backup directory and files as the backup is now contained in the single tar file. It is easier to copy and delete a single tar file via FTP than to do the same with a directory containing multiple files.

DELETING OLD BACKUPS

The script to delete the old backups is fairly simple to configure. You will need to enter the host, username and password of your FTP user, and specify how many backups you want to keep on the server. Since I do a backup once an hour, I keep a week’s worth (168 copies) of backups on my server. I could have checked the date/time on the files and deleted the older files which were X number of days old, but I decided to just go with a certain number of files to keep. I also included a “print_output” variable if you want to suppress any output – simply change this value to anything but “yes” and the script won’t print any output.

# Here is where you will put your FTP server name (or IP address)
# and your username and password
my $host = "server_name.com";
my $user = "username";
my $password = "password";

# how many copies of the backup do you want to keep?
$total_files_to_keep = 168;

$print_output = "yes";

Uncomment this line if you want to see a list of the other files which will not be deleted.

# optional output - remove # to have it print remaining files
# if ($print_output eq "yes") { print "| $count of $total_files_available |   Keeping: $filename\n"; }

Here is a sample output from running the script:

root# perl mysql_delete_backup.pl 
--------------------------------------------
           Total files: 194
    Total backup files: 192
   Total files to keep: 168
 Total files to delete: 24
----------------------------------------------
| x of 192 |  Skipping: .
| x of 192 |  Skipping: ..
| 1 of 192 |  Deleting: 2017-12-29-1352.tar.gz
| 2 of 192 |  Deleting: 2017-12-29-1452.tar.gz
| 3 of 192 |  Deleting: 2017-12-29-1552.tar.gz
| 4 of 192 |  Deleting: 2017-12-29-1652.tar.gz
....
| 24 of 192 |  Deleting: 2017-12-30-1252.tar.gz
----------------------------------------------
Finished 2017-01-06-15:21:58

When I run this script on my FTP server, and I do a listing of the backup directory, it shows the single-period (.) or current directory and the double-period (..) or the parent directory. I take this into account by skipping all files which do not have “20” in the name (as in the first two letters of the year).

If you login to your FTP server and you don’t see the “.” and “..“, then you will need to remove the “-2” in this part of the script, and simply have $total_files_available = $total_files:

# subtract two because of the . and ..
$total_files_available = $total_files - 2;

Here is what I see when I FTP to my server: (notice the “.” and “..“)

root# ftp backups@scripts.com
Trying 1234:f1c0:4738:5088:cb9a:dksi:ebfa:3829...
Connected to scripts.com.
220 FTP Server ready.
331 Password required for backups
Password: 
230 User backups logged in
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> dir
229 Entering Extended Passive Mode (|||58906|)
150 Opening ASCII mode data connection for file list
drwx---r-x   2 u63541528-backups ftpusers     4096 Jan  6 16:52 .
drwx---r-x   2 u63541528-backups ftpusers     4096 Jan  6 16:52 ..
-rw----r--   1 u63541528-backups ftpusers 45522630 Jan  5 22:52 2017-01-05-2252.tar.gz
-rw----r--   1 u63541528-backups ftpusers 45539118 Jan  5 23:52 2017-01-05-2352.tar.gz
-rw----r--   1 u63541528-backups ftpusers 45558328 Jan  6 01:52 2017-01-06-0152.tar.gz
-rw----r--   1 u63541528-backups ftpusers 45560794 Jan  6 07:52 2017-01-06-0752.tar.gz
....

The scripts are available on GitHub – https://github.com/ScriptingMySQL/PerlFiles. The scripts are named: mysql_backup.pl and mysql_delete_old_backups.pl.

I am not the best Perl programmer, so there may be a better/easier way to do this. Good luck with the scripts and let me know how they work for you. And follow me on Twitter at ScriptingMySQL.

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
Tony is the author of Twenty Forty-Four: The League of Patriots

 

Visit http://2044thebook.com for more information.

MySQL Enterprise Transparent Data Encryption (TDE) – provides at-rest encryption for physical InnoDB tablespace data files

With MySQL version 5.7.11 and up, Oracle continues to improve MySQL’s security features by adding MySQL Enterprise Transparent Data Encryption (TDE) for InnoDB tables stored in innodb_file_per_table tablespaces. This feature provides at-rest encryption for physical tablespace data files.

MySQL Enterprise TDE uses a two-tier encryption key architecture, consisting of a master encryption key and tablespace keys. When an InnoDB table is encrypted, a tablespace key is encrypted with the master key and the encrypted value of the tablespace key is stored in the tablespace header. When encrypting tablespace data, InnoDB transparently uses the master encryption key to decrypt the tablespace key and then uses it to encrypt (on write) and decrypt (on read).

The master encryption key is stored in memory on the MySQL keyring. Using a key vault, this key ring is both persisted and protected. The master key nor any of the tablespace keys are ever written to disk in plain text. When the the user first enables TDE, and when the server starts up or when the master key is rotated – the master key is requested and updated from the key vault. Databases and their tablespaces can get large, and had we chosen a single-tiered method, then key rotation would require re-encrypting entire tablespaces. This process is slow, costly and risky. With a two-tiered scheme, the decrypted version of a tablespace key never changes, but the master encryption key may be changed as required. This action is referred to as master-key rotation and it re-encrypts all of the tablespace keys in one atomic operation very rapidly (milliseconds).

There are various key vaults in the market and there’s also a common industry protocol supported by most vaults. The protocol is KMIP and it comes from OASIS. Losing keys (whether by accident, mismanagement, or getting hacked) means you lose your data. To ensure your keys are available, secure, auditable, etc., you will want to use a key-vault management software application such as Oracle Key Vault. Oracle Key Vault enables you to quickly deploy encryption and other security solutions by centrally managing encryption keys not only for MySQL, but across many things, including (but not limited to) Oracle Wallets, Java Keystores, and credential files.

Using the label for TDE requires:
1 – Applications aren’t aware of encryption – it is provided seamlessly and doesn’t require any changes
2 – The data is encrypted at-rest – so the operating system users or other access to the files on the media or the file system can’t read the data (without a key)
3 – The keys are secured, available and protected – you can’t leave the key in the door (or under the mat)
4 – The keys are never written to the filesystem (in plain text)

MySQL does include tablespace encryption with the community version but that is not TDE as it doesn’t meet requirements #3 and #4 – the keys aren’t necessarily secured.

MySQL Enterprise TDE supports the Advanced Encryption Standard (AES) block-based encryption algorithm. It uses Electronic Codebook (ECB) block encryption mode for tablespace key encryption and Cipher Block Chaining (CBC) block encryption mode for data encryption. (from https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html)

In this post, I will show you how easy it is to use this new feature to encrypt your InnoDB tables (requires a licensed version of the MySQL Enterprise Edition MySQL 5.7.11 or higher).

Setting up MySQL TDE

Get Oracle Key Vault Credentials

First you will need to set up an account in Oracle Key Vault and create an endpoint (the key storage that keyring_okv plugin will talk to). Once you are done with that, you will have to create a configuration folder for keyring_okv. This folder will contain credentials to log into Oracle Key Vault. This, of course, needs to be stored securely. Go to this link to find information on how to accomplish this:
https://dev.mysql.com/doc/mysql-security-excerpt/5.7/en/keyring-okv-plugin.html
As the result you should have a configured endpoint in Oracle Key Vault and the configuration folder which should look like this:

# ls -l
total 8
-rw-rw-r-- 1 rob rob 427 lip 7 18:49 okvclient.ora
drwxrwxr-x 2 rob rob 4096 lip 7 18:49 ssl
# ls -l ssl
10:47 $ ls -l ssl
total 16
-rw-rw-r-- 1 rob rob 1200 lip 7 18:49 CA.pem
-rw-rw-r-- 1 rob rob 1209 lip 7 18:49 cert.pem
-rw-rw-r-- 1 rob rob 1027 lip 7 18:49 cert_req.pem
-rw-rw-r-- 1 rob rob 1675 lip 7 18:49 key.pem

From now on we will call this folder okv_enrollment.

Note: If you want to trial Oracle Key Vault it can be downloaded from the Oracle Software Delivery Cloud at https://edelivery.oracle.com/.

Install MySQL Server

Next you will need to install MySQL 5.7.11, and follow the post-installation steps, which include initializing the data directory.

Load keyring_okv plugin

To use the tablespace encryption feature, you will also need to load the keyring plugin (keyring_okv) by adding the following to your configuration file (under the [mysqld] section, let us assume that your path to the Oracle Key Vault folder is /home/user/okv_enrollment):

[mysqld]
early-plugin-load=keyring_okv.so
keyring_okv_conf_dir=/home/user/okv_enrollment

Before you attempt to create an encrypted table, you can check and make sure the plugin is active via this command:

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS
-> WHERE plugin_name='keyring_okv';
    
+--------------+---------------+
| PLUGIN_NAME  | PLUGIN_STATUS |
+--------------+---------------+
| keyring_okv  | ACTIVE        |
+--------------+---------------+

Creating encrypted tables

To create a table with encryption, you only need to add ENCRYPTION = ‘Y’ to the CREATE TABLE statement. This example will create a new database and an encrypted table:

CREATE DATABASE scripting;
USE scripting;
CREATE TABLE `test_encryption` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `server_name` varchar(15) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ENCRYPTION = 'Y';
Technical side note (can be skipped :)
As this is the first encrypted table we are creating - InnoDB will first ask 
keyring_okv to generate master key in Oracle Key Vault. From now on this key will 
be used to encrypt tablespace keys. Next InnoDB will ask Oracle Key Vault to 
generate random key for encrypting table test_encryption. keyring_okv will use 
Oracle Key Vault to ensure that this key will get generated with high entropy. 
This random key will then get encrypted using the master key and stored alongside
the encrypted test_encryption table. The next time encrypted table gets created - 
only the tablespace key will get generated and the master key will get reused 
(to encrypt new tablespace key).

This encryption feature only works with InnoDB tables. If you try this with a MyISAM table, you will get an error:

CREATE TABLE `test_encryption2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `server_name` varchar(15) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ENCRYPTION = 'Y';
ERROR 1031 (HY000): Table storage engine for 'test_encryption2' doesn't have this option

You may view a list of all of the encrypted tables via this command:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
-> WHERE CREATE_OPTIONS like 'ENCRYPTION="Y"';
+--------------+-----------------+----------------+
| TABLE_SCHEMA | TABLE_NAME      | CREATE_OPTIONS |
+--------------+-----------------+----------------+
| scripting    | test_encryption | ENCRYPTION="Y" |
+--------------+-----------------+----------------+
1 row in set (0.20 sec)
Encrypting tables with different endpoints

It is possible to use different endpoints with one instance of MySQL. You can start server with (say) endpoint1 and encrypt table test_encryption with it. Then you can restart the server with some other endpoint and use it to encrypt table (for instance) test_more_encryption. However test_encryption will not be accessible. You will see the following error:

mysql> select * from test_encryption; ERROR 3185 (HY000): Can't find master key from keyring, please check keyring plugin is loaded.

Also please note that, although keyring_okv_conf_dir is settable in runtime, it should be changed with care. Please see Appendix 1, at the bottom of this article, for more information.
Overall using multiple endpoints with one server is not encourage and should be done with extra care.

Encrypting existing tables

To enable encryption for an existing InnoDB table, specify the ENCRYPTION option in an ALTER TABLE statement.

mysql> ALTER TABLE t1 ENCRYPTION='Y';

To disable encryption for an InnoDB table, set ENCRYPTION=’N’ using ALTER TABLE.

mysql> ALTER TABLE t1 ENCRYPTION='N';

One warning – “Plan appropriately when altering an existing table with the ENCRYPTION option. ALTER TABLE … ENCRYPTION operations rebuild the table using ALGORITHM=COPY. ALGORITM=INPLACE is not supported.” (from https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html)

In other words, it may take some time to encrypt (or decrypt) an already-existing InnoDB table.

Rotating InnoDB master key

For security reasons InnoDB master key should be rotated periodically. This operation creates a new master key in Oracle Key Vault, re-encrypts all the tablespace keys with the new master key and deactivates the old master key. In case when rotation gets interrupted by the server crash/restart it will be continued on the next server startup. To start innoDB key rotation use this command:

mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY;

For more information go this link (paragraph InnoDB Tablespace Encryption and Master Key Rotation):
https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html

Moving encrypted tables between servers

You can also move a copy of an encrypted table to another server, following these steps. There are some limitations you will need to review.

“When exporting a tablespace that is encrypted using the InnoDB tablespace encryption feature, InnoDB generates a .cfp file in addition to a .cfg metadata file. The .cfp file must be copied to the destination server together with the .cfg file and tablespace file before performing the ALTER TABLE … IMPORT TABLESPACE operation on the destination server. The .cfp file contains a transfer key and an encrypted tablespace key. On import, InnoDB uses the transfer key to decrypt the tablespace key. For related information, see Section 14.4.10, InnoDB Tablespace Encryption.” (from: https://dev.mysql.com/doc/refman/5.7/en/tablespace-copying.html#innodb-transportable-tablespace-limitations

You now know how to use encryption for your InnoDB tables.

Troubleshooting

  • In case keyring_okv can not be accessed after restarting mysqld, you will see an error like this in the error log:
[ERROR] Plugin keyring_okv reported: 'Could not connect to the OKV server'
[ERROR] Plugin keyring_okv reported: 'keyring_okv initialization failure. Please check that the keyring_okv_conf_dir points to a readable directory and that the directory contains Oracle Key Vault configuration file and ssl materials. Please also check that Oracle Key Vault is up and running.

As error indicates it seems that Oracle Key Vault server is either down or there is something wrong with the credential directory. Make sure MySQL Server has privileges to access the configuration directory and that keyring_okv_conf_dir variable is set correctly (you can change it in runtime). Check also that structure of the configuration file is correct (see point Get Oracle Key Vault Credentials).

  • In case you forget to set keyring_okv_conf_dir variable during server startup or you install the plugin in runtime you will see the following error in the log file:
[Warning] Plugin keyring_okv reported: 'For keyring_okv to be initialized, please point keyring_okv_conf_dir variable to a directory with Oracle Key Vault configuration file and ssl materials

As you can see you have to set keyring_okv_conf_dir variable.


Appendix 1. Things to consider when changing keyring_okv_conf_dir in runtime

When server is loaded with keyring_okv – InnoDB fetches Master Key from Oracle Key Vault and uses it to decrypt each tablespace key – then it caches the decrypted tablespace keys. Thus the following code works:

CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB; 
INSERT INTO t1 VALUES(0, "aaaaa"); 
SELECT * FROM t1; 
# Changing endpoint 
SET @@global.keyring_okv_conf_dir= /home/user/okv_enrollment2';
# Table should accessible,keys in memory. 
SELECT * FROM t1;

Although, we have changed the endpoint, table t1 is accessible because its decrypted tablespace key was cached. This seems nice, but you have to be aware that changing endpoint in runtime does not implicitly mean blocking access to tables encrypted with the previously loaded endpoints. Also, we can get into troubles when endpoint we are changing to does not contain master key created by the server instance we are using. Then the following is not possible:

mysql> create table `test_encryption_4` (`id` int(10) unsigned) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ENCRYPTION = 'Y';
ERROR 3185 (HY000): Can't find master key from keyring, please check keyring plugin is loaded.

This is because InnoDB tries to fetch Master Key from the endpoint and it fails. This is different from starting the server with keyring_okv – then, when InnoDB cannot find Master Key in the keyring it asks keyring to generate a new one.


Thanks to Robert Golebiowski for help with this blog.


Here are some various links regarding MySQL Enterprise TDE:

https://www.mysql.com/products/enterprise/tde.html
https://dev.mysql.com/doc/refman/5.7/en/faqs-tablespace-encryption.html
https://dev.mysql.com/doc/refman/5.7/en/keyring-okv-plugin.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html
https://dev.mysql.com/doc/refman/5.7/en/keyring-installation.html
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_keyring_okv_conf_dir
http://www.mysql.com/news-and-events/web-seminars/mysql-security-transparent-data-encryption/

Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
Tony is the author of Twenty Forty-Four: The League of Patriots

Visit http://2044thebook.com for more information.

Upgrade MySQL to a new version with a fresh installation & use shell scripts and mysqldump to reload your data

There are several ways to upgrade MySQL. In this post, we will use a combination of shell scripts and the mysqldump application to export our MySQL data, and then re-import it back into the upgraded version of MySQL.

In this example, we will be doing a minor version upgrade. We will be going from 5.6.17 to 5.6.19. This method may not work if you are upgrading from one major release to another – from 5.1 to 5.5, or 5.5 to 5.6. You will want to check each version and review the new features/functions and also what features/functions have been deprecated. We are also assuming that no one will be using the database during the time it takes for us to do the upgrade.

If you want to upgrade from a version that is more than one major release apart from your current version, then you will want to upgrade to each successive version. For example, if you want to upgrade from 5.0 to 5.6, you will want to upgrade from 5.0 to 5.1, then 5.1 to 5.5, and then 5.5 to 5.6.

You don’t have to export all of your data when you upgrade MySQL. There are ways of upgrading without doing anything to your data. But in this post, I will be exporting the data and re-importing it, for a fresh installation. I don’t have that much data, so I don’t mind doing the export and import. If you have a lot of data, you might want to consider other options.

To get an idea of the size of your database(s), here is a quick script that you can use:

SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" 
FROM information_schema.TABLES GROUP BY table_schema ; 

When I perform an export/import, I like to export each database as a separate mysqldump file, and then also export all of the databases together in one large file. By exporting/importing the individual databases, if you have an error importing one of the database dump files, you can isolate the error to a single database. It is much easier to fix the error in one smaller data dump file than with a larger all-inclusive dump file.

I am also going to create some simple shell scripts to help me create the commands that I need to make this task much easier. First, you will want to create a directory to store all of the scripts and dump files. Do all of your work inside that directory.

Next, I want to get a list of all of my databases. I will log into mysql, and then issue the show databases; command: (which is the same command as: select schema_name from information_schema.schemata;)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| 12thmedia          |
| cbgc               |
| comicbookdb        |
| coupons            |
| healthcheck        |
| innodb_memcache    |
| landwatch          |
| laurelsprings      |
| ls_directory       |
| mem                |
| mysql              |
| performance_schema |
| protech            |
| scripts            |
| stacy              |
| storelist          |
| test               |
| testcert           |
| tony               |
| twtr               |
| watchdb            |
+--------------------+
22 rows in set (1.08 sec)

I can then just highlight and copy the list of databases, and put that list into a text file named “list.txt“. I do not want to include these databases in my export:

information_schema
mysql
performance_schema
test

However, I will export the mysql table later. You need to check with the MySQL manual to make sure that there haven’t been any changes to the MySQL table from one version to the next.

I will need to manually remove those databases from my list.txt file. I then want to remove all of the spaces and pipe symbols from the text file – assuming that you do not have any spaces in your database names. Instead of using spaces in a database name, I prefer to use an underline character “_“. These scripts assume that you don’t have any spaces in your database names.

If you know how to use the vi editor, you can so a substitution for the pipes and spaces with these commands:

:%s/ //g
:%s/|//g

Otherwise, you will want to use another text editor and manually edit the list to remove the spaces and pipe symbols. Your finished list.txt file should look like this:

12thmedia cbgc
comicbookdb
coupons
healthcheck
innodb_memcache
landwatch
laurelsprings
ls_directory
mem
protech
scripts
stacy
storelist
testcert
tony
twtr
watchdb

You can then create a simple shell script to help create your mysqldump commands – one command for each database. You will want to create this script and the other scripts in the directory you created earlier. Name the script export.sh. You can also change the mysqldump options to meet your needs. I am using GTID’s for replication, so I want to use this option –set-gtid-purged=OFF. You will also want to change the value of my password my_pass to your mysql password. You can also skip including the password by using the -p option, and just enter the password each time you run the mysqldump command.

# export.sh
# script to create the database export commands
k=""
for i in `cat list.txt`
do

echo "mysqldump -uroot --password=my_pass --set-gtid-purged=OFF --triggers --quick --skip-opt --add-drop-database --create-options --databases $i > "$i"_backup.sql"

k="$k $i"

done

# Optional - export the entire database
# use the file extention of .txt so that your script won't import it later
echo "mysqldump -uroot --password=my_pass --set-gtid-purged=OFF --triggers --quick --skip-opt --add-drop-database --create-options --databases $k > all_db_backup.txt"

For the individual databases, I am using the suffix of .sql. For the dump file that contains all of the databases, I am using the prefix .txt – as I use a wildcard search later to get a list of the dump files, and I don’t want to import the one dump file that contains all of the databases.

Now you can run the export.sh script to create a list of your mysqldump commands, and you are going to direct the output into another shell script named export_list.sh.

# sh export.sh > export_list.sh

We can now take a look at what is in the export_list.sh file

# cat export_list.sh
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases 12thmedia > 12thmedia_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases cbgc > cbgc_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases comicbookdb > comicbookdb_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases coupons > coupons_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases healthcheck > healthcheck_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases innodb_memcache > innodb_memcache_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases landwatch > landwatch_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases laurelsprings > laurelsprings_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases ls_directory > ls_directory_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases mem > mem_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases protech > protech_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases scripts > scripts_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases stacy > stacy_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases storelist > storelist_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases testcert > testcert_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases tony > tony_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases twtr > twtr_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases watchdb > watchdb_backup.sql

mysqldump -uroot -p --set-gtid-purged=OFF --password=my_psss --triggers --quick --skip-opt --add-drop-database --create-options --databases  12thmedia cbgc comicbookdb coupons healthcheck innodb_memcache landwatch laurelsprings ls_directory mem protech scripts stacy storelist testcert tony twtr watchdb > all_db_backup.txt

Now you have created a list of mysqldump commands that you can execute to dump all of your databases. You can now go ahead and execute your mysqldump commands by running the export_list.sh script:

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

The message “Warning: Using a password on the command line interface can be insecure.” is shown because you included the value for “–password“. If you don’t want to put your password on the command line, just change that option to “-p“, and you will have to manually enter your MySQL root user’s password after each mysqldump command.

Here is a list of the dump files that was produced:

# ls -l
total 21424
-rw-r--r--  1 root  staff    26690 Aug  1 16:25 12thmedia_backup.sql
-rw-r--r--  1 root  staff  5455275 Aug  1 16:26 all_db_backup.txt
-rw-r--r--  1 root  staff  1746820 Aug  1 16:25 cbgc_backup.sql
-rw-r--r--  1 root  staff   492943 Aug  1 16:25 comicbookdb_backup.sql
-rw-r--r--  1 root  staff     1057 Aug  1 16:25 coupons_backup.sql
-rw-r--r--  1 root  staff     3366 Aug  1 16:25 export_list.sh
-rw-r--r--  1 root  staff     1077 Aug  1 16:25 healthcheck_backup.sql
-rw-r--r--  1 root  staff     3429 Aug  1 16:25 innodb_memcache_backup.sql
-rw-r--r--  1 root  staff  1815839 Aug  1 16:25 landwatch_backup.sql
-rw-r--r--  1 root  staff   642965 Aug  1 16:25 laurelsprings_backup.sql
-rw-r--r--  1 root  staff   660254 Aug  1 16:25 ls_directory_backup.sql
-rw-r--r--  1 root  staff     1037 Aug  1 16:25 mem_backup.sql
-rw-r--r--  1 root  staff     1057 Aug  1 16:25 protech_backup.sql
-rw-r--r--  1 root  staff     2889 Aug  1 16:25 scripts_backup.sql
-rw-r--r--  1 root  staff    11107 Aug  1 16:25 stacy_backup.sql
-rw-r--r--  1 root  staff     4002 Aug  1 16:25 storelist_backup.sql
-rw-r--r--  1 root  staff     1062 Aug  1 16:25 testcert_backup.sql
-rw-r--r--  1 root  staff     4467 Aug  1 16:25 tony_backup.sql
-rw-r--r--  1 root  staff     1042 Aug  1 16:25 twtr_backup.sql
-rw-r--r--  1 root  staff    52209 Aug  1 16:25 watchdb_backup.sql

You will now want to dump your MySQL table, so you don’t have to recreate all of the MySQL information, including the users, passwords and privileges after the new install.

mysqldump -uroot --password=my_pass --set-gtid-purged=OFF mysql > mysql_user_backup.txt

I am once again using the .txt prefix for this file.

After you execute the above command, make sure that the dump file was created:

# ls -l mysql_user_backup.txt
-rw-r--r--  1 root  staff  9672 Aug  1 16:32 mysql_user_backup.txt

We have now finished exporting all of our data, including our MySQL table data. You will need to shutdown MySQL. You may use mysqladmin to shutdown your database, or here is a link on ways to shutdown MySQL.

# mysqladmin -uroot --password=my_pass shutdown
Warning: Using a password on the command line interface can be insecure.

Before continuing, you might want to check to make sure that the mysqld process isn’t still active.

# ps -ef|grep mysqld
    0 18380 17762   0   0:00.00 ttys002    0:00.00 grep mysqld

You are now going to want to change the name of your mysql directory. This will give you access to the old directory in case the upgrade fails. For my OS (Mac OS 10.9), my MySQL home directory is a symbolic link to another directory that contains the actual MySQL data. All I have to do is to remove the symbolic link. A new symbolic link will be created with the new install. Otherwise, just use the mv command to rename your old MySQL directory.

# cd /usr/local/
# ls -ld mysql* 
lrwxr-xr-x   1 root  wheel   36 Aug  9  2013 mysql -> mysql-advanced-5.6.17-osx10.6-x86_64
drwxr-xr-x  18 root  wheel  612 Jan 16  2014 mysql-advanced-5.6.17-osx10.6-x86_64

All I have to do is to remove the link, and the MySQL directory will still be there:

# rm mysql
# ls -ld mysql* 
drwxr-xr-x  18 root  wheel  612 Jan 16  2014 mysql-advanced-5.6.17-osx10.6-x86_64

Now I am ready to install the new version of MySQL. I won’t cover the installation process, but here is the link to the installation page.

Tip: After you have installed MySQL, don’t forget to run this script from your MySQL home directory. This will install your mysql database tables. Otherwise, you will get an error when you try to start the mysqld process.

# ./scripts/mysql_install_db

Now you can start the mysqld process. See this page if you don’t know how to start MySQL.

You can test to see if the new installation of MySQL is running by either checking the process table, or logging into mysql. With a fresh install of 5.6, you should not have to include a user name or password.

Note: (Future versions of MySQL may automatically create a random root password and put it in your data directory. You will then need to use that password to login to MySQL for the first time. Check the user’s manual for any MySQL versions beyond 5.6.)

# mysql
Welcome to the mysql monitor.  Commands end with ; or \g.
Your mysql connection id is 3
....

mysql>

Now that MySQL is up and running, leave the mysql terminal window open, and open another terminal window so you can import your mysql table information from your dump file:

# mysql < /users/tonydarnell/mysql_2014_0731/2014_0731_mysql_backup.sql

You won't be able to login with your old user names and passwords until you execute the flush privileges command. So, in your other terminal window with the mysql prompt:

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Open another terminal window and see if you can login with your old mysql user name and password:

# mysql -uroot -p
Enter password: 
Welcome to the mysql monitor.  Commands end with ; or \g.
Your mysql connection id is 3
....

mysql>

You can then look at your the user names and passwords in the mysql.user table:

mysql> select user, host, password from mysql.user order by user, host;
+----------------+---------------+-------------------------------------------+
| user           | host          | password                                  |
+----------------+---------------+-------------------------------------------+
| root           | 127.0.0.1     | *BF6F71512345332CAB67E7608EBE63005BEB705C |
| root           | 192.168.1.2   | *BF6F71512345332CAB67E7608EBE63005BEB705C |
| root           | 192.168.1.5   | *BF6F71512345332CAB67E7608EBE63005BEB705C |
| root           | 192.168.1.50  | *BF6F71512345332CAB67E7608EBE63005BEB705C |
| root           | localhost     | *BF6F71512345332CAB67E7608EBE63005BEB705C |
+----------------+---------------+-------------------------------------------+
5 rows in set (0.00 sec)


OPTIONAL:
Since I am using GTID’s for replication, I can check to see how many transactions have been completed, by issuing the show master status command:

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000005
         Position: 644455
     Binlog_Do_DB: 
 Binlog_Ignore_DB: coupons,usta,ls_directory,landwatch
Executed_Gtid_Set: e1eb3f38-18da-11e4-aa44-0a1a64a61679:1-124
1 row in set (0.00 sec)

We are now ready to import the database dump files. We can use this script to create the import commands. Copy this into a text file named import.sh:

# import.sh
# script to import all of the export files
# run this script in the same directory as the exported dump files
#
> import_files.sh
directory=`pwd`
for file in `ls *sql`
do

if [[ $(grep -c '.txt' $file) != 0 ]];then

echo "# found mysql - do nothing"

else

echo "mysql -uroot -p"my_pass"  < $directory/$file"
echo "mysql -uroot -p"my_pass"  > import_files.sh

fi

done

Then run the import.sh script. The script will print the output to the terminal window as well as into a new script file named import_files.sh.

# sh import.sh
mysql -uroot -pmy_pass < 12thmedia_backup.sql
mysql -uroot -pmy_pass < cbgc_backup.sql
mysql -uroot -pmy_pass < comicbookdb_backup.sql
mysql -uroot -pmy_pass < coupons_backup.sql
mysql -uroot -pmy_pass < healthcheck_backup.sql
mysql -uroot -pmy_pass < innodb_memcache_backup.sql
mysql -uroot -pmy_pass < landwatch_backup.sql
mysql -uroot -pmy_pass < laurelsprings_backup.sql
mysql -uroot -pmy_pass < ls_directory_backup.sql
mysql -uroot -pmy_pass < mem_backup.sql
mysql -uroot -pmy_pass < protech_backup.sql
mysql -uroot -pmy_pass < scripts_backup.sql
mysql -uroot -pmy_pass < stacy_backup.sql
mysql -uroot -pmy_pass < storelist_backup.sql
mysql -uroot -pmy_pass < testcert_backup.sql
mysql -uroot -pmy_pass < tony_backup.sql
mysql -uroot -pmy_pass < twtr_backup.sql
mysql -uroot -pmy_pass < watchdb_backup.sql

Look at the contents of the new script file – import_files.sh – to make sure that it contains all of the database files. You will use this file to help you import your dump files.

# cat import_files.sh
mysql -uroot -pmy_pass < 12thmedia_backup.sql
mysql -uroot -pmy_pass < cbgc_backup.sql
mysql -uroot -pmy_pass < comicbookdb_backup.sql
mysql -uroot -pmy_pass < coupons_backup.sql
mysql -uroot -pmy_pass < healthcheck_backup.sql
mysql -uroot -pmy_pass < innodb_memcache_backup.sql
mysql -uroot -pmy_pass < landwatch_backup.sql
mysql -uroot -pmy_pass < laurelsprings_backup.sql
mysql -uroot -pmy_pass < ls_directory_backup.sql
mysql -uroot -pmy_pass < mem_backup.sql
mysql -uroot -pmy_pass < protech_backup.sql
mysql -uroot -pmy_pass < scripts_backup.sql
mysql -uroot -pmy_pass < stacy_backup.sql
mysql -uroot -pmy_pass < storelist_backup.sql
mysql -uroot -pmy_pass < testcert_backup.sql
mysql -uroot -pmy_pass < tony_backup.sql
mysql -uroot -pmy_pass < twtr_backup.sql
mysql -uroot -pmy_pass < watchdb_backup.sql

WARNING: Be sure that this script file does not contain the main dump file or the mysql user’s file that we created.


I was exporting and importing eighteen (18) database files, so I can also check the line count of the import_files.sh script to make sure it matches:

# wc -l import_files.sh
      18 import_files.sh

I am now ready to import my files.


Optional: add the -v for verbose mode – sh -v import_files.sh


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

You databases should now be imported into your new instance of MySQL. You can always re-run the script to make sure that the databases are the same size.


OPTIONAL:
Since I am using GTID’s for replication, I can check to see how many transactions have been completed after importing the dump files, by issuing the show master status command:

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 16884001
     Binlog_Do_DB: 
 Binlog_Ignore_DB: coupons,usta,ls_directory,landwatch
Executed_Gtid_Set: cc68d008-18f3-11e4-aae6-470d6cf89709:1-43160
1 row in set (0.00 sec)

Your new and fresh installation of MySQL should be ready to use.

NOTE:A thank-you to Daniel Van Eeden for pointing out a mistake that I had made.

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
Tony is the author of Twenty Forty-Four: The League of Patriots

 

Visit http://2044thebook.com for more information.

Using mysqldump and the MySQL binary log – a quick guide on how to backup and restore MySQL databases

Be sure to check out my other posts on mysqldump:
Scripting Backups of MySQL with Perl via mysqldump
Splitting a MySQL Dump File Into Smaller Files Via Perl
Creating and restoring database backups with mysqldump and MySQL Enterprise Backup – Part 1 of 2
Creating and restoring database backups with mysqldump and MySQL Enterprise Backup – Part 2 of 2


I have already written several posts on using mysqldump. This post will be a quick guide to using mysqldump to backup and restore your databases. And we will look at using the MySQL binary log (binlog) along with mysqldump for point-in-time recovery. If your database is extremely large, and for databases in a production environment, you might want to consider using MySQL Enterprise Backup (mysqlbackup), which is part of the MySQL Enterprise Edition.

For those of you that aren’t familiar 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.

The best feature about mysqldump is that it is easy to use. The main problem with using mysqldump occurs when you need to restore a database. When you execute mysqldump, the database backup (output) is an SQL file that contains all of the necessary SQL statements to restore the database – but restoring requires that you execute these SQL statements to essentially rebuild the database. Since you are recreating your database, the tables and all of your data from this file, the restoration procedure can take a long time to execute if you have a very large database.


NOTE: If you are using GTID’s (global transaction identifiers) in your database, you will need to include the –set-gtid-purged=OFF option, otherwise you will receive 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. 

For these examples, I will not include the –set-gtid-purged=OFF option.


Dumping and making a copy of a single database

To dump/backup a single database:

mysqldump -uroot -p database_name > db_dump.sql

To load the dump file back into mysql, you will need to create the new database first. If you use the –databases option before the database name, mysqldump will also dump the CREATE DATABASE and USE statements that you need prior to inserting the data from the dump.

You can either use mysqladmin to create the new database, or create it from a MySQL prompt:

# mysqladmin create new_database_name

mysql> CREATE DATABASE new_database_name;

Next, you can simply import the dump file into mysql.

# mysql new_database_name < db_dump.sql

You can also use the dump file to move the database to another server. If you did not use the –databases option, then you will need to create the database first.

Dumping events, routines, triggers

Here are the options for mysqldump to also dump event scheduler events, stored procedures or functions. If you want to include these, use these options:

–routines – dumps stored procedures and functions
–events – dumps Event Scheduler events
–triggers – dumps triggers

When you use mysqldump, triggers are dumped by default. If you want to disable any of these functions, use the “skip” versions: https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_events–skip-events, –skip-routines, or –skip-triggers.

Only dump table definitions

If you want to just dump the CREATE TABLE statements without any data, use the –no-data option.

# mysqldump --no-data database_name > db_definitions_dump.sql

You might want to add the –routines and –events options to include stored routines and event definitions.

# mysqldump --no-data --routines --events database_name > db_definitions_dump.sql

Only dump the data

If you want to just dump the data without any table definitions, you can use the –no-create-info option:

# mysqldump --no-create-info database_name > db_data_dump.sql

Using mysqldump to test a new version of mysql

Let’s say that you need to upgrade to a new version of mysql. In order to test to see if your database objects are handled properly by the new version, you can simply dump the data definitions and import them into the new version of MySQL (preferably on a separate computer).

On the computer with the old version of MySQL:

mysqldump --all-databases --no-data --routines --events > db_definitions.sql

Then, on the upgraded server, you can just import this dump file:

mysql -uroot -p < db_definitions.sql

This will help you spot any potential incompatibilities with the new version of MySQL. If you don’t receive any errors, you can then dump the data and load into the new server. Be sure to run some test queries on the new upgraded server as well.

Point-in-time recovery using mysqldump and the binary logs

The MySQL binary logs (binlogs) contains all of the SQL statements or “events” that could change the database (I say “could” because a delete statement that does not delete any rows would still be entered into the binary log – unless you are using row-based logging).

For more information about the binary log, see: http://dev.mysql.com/doc/refman/5.6/en/binary-log.html.

Since the binlog contains all of the events that happen to the database, you can use the binlog to apply these same changes to a different database. If you started your MySQL instance with the binlogs enabled, and you have never flushed the binlogs, then the binlogs contain all of the SQL statements for all of the data that is in your database. The binlog itself is like a backup of your database.

If you want to use the binary logs in addition to mysqldump to restore your database, you need to have the binary logs (binlogs) enabled. There are many options for the binlogs (see http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html, but the only two that you really need for this example are:

 --log-bin[=base_name]
 --log-bin-index[=file_name]

One other option is to use the –binlog-format. You can set this value to STATEMENT (default), ROW or MIXED. For more information about these options, see http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_binlog_format.

These variables need to go into your my.cnf or my.ini file under the [mysqld] section, and this will require a restart of mysqld.

Once you have the binary log enabled, you will need to do a few things differently when you use mysqldump. You will need to:

– flush the tables and place a READ lock on the tables
– check to see what binlog is being used
– check the position of the binlog
– dump your data with mysqldump
– release the lock

By placing a read lock on the tables, you are stopping anyone from modifying the data in the database. By having the binlog and binlog position, these will allow you use the binary logs to restore any statements that happened after the mysqldump. Open two terminal windows – one with a MySQL prompt, and another with a root prompt:

In the MySQL prompt, issue the READ lock and SHOW MASTER STATUS:

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.47 sec)

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-bin.000008
         Position: 191
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

Now you are ready to dump the database with whatever options you need:

# mysqldump --all-databases > db_000008_191_dump.sql

Once the dump has finished, you can release the lock:

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

It is extremely important that you write down the binlog file and position from the SHOW MASTER STATUS statement and somehow associate it with the dump file. One way to do this is to insert the binlog file name and position into the dump file name. In my example above, I did this by adding both to the dump file name db_000008_191_dump.sql.

When you have to restore the database, you will need to load the dump file first, and then apply the binlog(s) to the database.

Let’s assume that we need to restore the entire database. First, we will import the dump file:

# mysql -uroot -p < db_000008_191_dump.sql

Next, you will need to load the information from the binlog(s). To load information from the binlogs, you need to use the mysqlbinlog utility. You can check your MySQL data directory to see how many binlogs you have used since the one that was in the SHOW MASTER STATUS statement:

$ ls -l mysql-bin*
-rw-rw----  1 mysql  wheel     67110 Apr  4 16:22 mysql-bin.000001
-rw-rw----  1 mysql  wheel   1230893 Apr  4 16:24 mysql-bin.000002
-rw-rw----  1 mysql  wheel  13383907 Apr  4 17:03 mysql-bin.000003
-rw-rw----  1 mysql  wheel  13383907 Apr  4 19:03 mysql-bin.000004
-rw-rw----  1 mysql  wheel  13383907 Apr  4 19:07 mysql-bin.000005
-rw-rw----  1 mysql  wheel  13383907 Apr 18 16:48 mysql-bin.000006
-rw-rw----  1 mysql  wheel  13383907 Apr 21 13:37 mysql-bin.000007
-rw-rw----  1 mysql  wheel  13383907 Apr 21 13:37 mysql-bin.000008
-rw-rw----  1 mysql  wheel    154847 Apr 21 13:37 mysql-bin.000009
-rw-rw----  1 mysql  wheel       171 Apr 21 13:37 mysql-bin.index

You can also just look at the mysql-bin.index file (located in your MySQL data directory), which contains a list of all of the binary files in use:

 # cat mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006
./mysql-bin.000007
./mysql-bin.000008
./mysql-bin.000009

In this example, we will need to apply the changes from the binlog file mysql-bin.000008 after position 191, and then all of the mysql-bin.000009 binlog. You will need to add the correct data directory PATH to your mysqlbinlog statement.

mysqlbinlog --start-position=191 $DATA_DIR_PATH/mysql-bin.000008 | mysql -u root -p

After you have inserted all of mysql-bin.000008 after position 191, you can insert the entire mysql-bin.000009 binlog file:

mysqlbinlog $DATA_DIR_PATH/mysql-bin.000009 | mysql -u root -p

Note: During the restore process, you do not want anyone inserting any data into the database.

Your database should now be back to the state when the database crashed or was deleted. It isn’t mandatory, but it is also a good idea to copy the binlogs to a separate location as part of your backup routine. You can use mysqlbinlog to do this as well – see: http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog-backup.html.

For more information on using the binary logs for point-in-time recovery, see https://dev.mysql.com/doc/refman/5.6/en/point-in-time-recovery.html. There are a lot of other options for using binlogs. The best option for backing up and restoring your MySQL database is to use the MySQL Enterprise Backup (mysqlbackup), which is part of the MySQL Enterprise Edition subscription, which includes 24×7 MySQL Support and the other Enterprise features.

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
Tony is the author of Twenty Forty-Four: The League of Patriots

 

Visit http://2044thebook.com for more information.

Creating and restoring database backups with mysqldump and MySQL Enterprise Backup – Part 2 of 2

In part one of this post, I gave you a couple examples of how to backup your MySQL databases using mysqldump. In part two, I will show you how to use the MySQL Enterprise Backup (MEB) to create a full and partial backup.


MySQL Enterprise Backup provides enterprise-grade backup and recovery for MySQL. It delivers hot, online, non-blocking backups on multiple platforms including Linux, Windows, Mac & Solaris. To learn more, you may download a whitepaper on MEB.

MySQL Enterprise Backup delivers:

  • NEW! Continuous monitoring – Monitor the progress and disk space usage
  • “Hot” Online Backups – Backups take place entirely online, without interrupting MySQL transactions
  • High Performance – Save time with faster backup and recovery
  • Incremental Backup – Backup only data that has changed since the last backup
  • Partial Backup – Target particular tables or tablespaces
  • Compression – Cut costs by reducing storage requirements up to 90%
  • Backup to Tape – Stream backup to tape or other media management solutions
  • Fast Recovery – Get servers back online and create replicated servers
  • Point-in-Time Recovery (PITR) – Recover to a specific transaction
  • Partial restore – Recover targeted tables or tablespaces
  • Restore to a separate location – Rapidly create clones for fast replication setup
  • Reduce Failures – Use a proven high quality solution from the developers of MySQL
  • Multi-platform – Backup and Restore on Linux, Windows, Mac & Solaris(from http://www.mysql.com/products/enterprise/backup.html

    While mysqldump is free to use, MEB is part of MySQL’s Enterprise Edition (EE) – so you need a license to use it. But if you are using MySQL in a production environment, you might want to look at EE, as:

    MySQL Enterprise Edition includes the most comprehensive set of advanced features, management tools and technical support to achieve the highest levels of MySQL scalability, security, reliability, and uptime. It reduces the risk, cost, and complexity in developing, deploying, and managing business-critical MySQL applications.
    (from: http://www.mysql.com/products/enterprise/)

    Before using MEB and backing up your database for the first time, you will need some information:

    Information to gather – Where to Find It – How It Is Used

    • Path to MySQL configuration file – Default system locations, hardcoded application default locations, or from –defaults-file option in mysqld startup script. – This is the preferred way to convey database configuration information to the mysqlbackup command, using the –defaults-file option. When connection and data layout information is available from the configuration file, you can skip most of the other choices listed below.
    • MySQL port – MySQL configuration file or mysqld startup script. Used to connect to the database instance during backup operations. Specified via the –port option of mysqlbackup. –port is not needed if available from MySQL configuration file. Not needed when doing an offline (cold) backup, which works directly on the files using OS-level file permissions.
    • Path to MySQL data directory – MySQL configuration file or mysqld startup script. – Used to retrieve files from the database instance during backup operations, and to copy files back to the database instance during restore operations. Automatically retrieved from database connection for hot and warm backups. Taken from MySQL configuration file for cold backups.
    • ID and password of privileged MySQL user – You record this during installation of your own databases, or get it from the DBA when backing up databases you do not own. Not needed when doing an offline (cold) backup, which works directly on the files using OS-level file permissions. For cold backups, you log in as an administrative user. – Specified via the –password option of the mysqlbackup. Prompted from the terminal if the –password option is present without the password argument.
    • Path under which to store backup data – You choose this. See Section 3.1.3, of the MySQL online manual – “Designate a Location for Backup Data” for details. – By default, this directory must be empty for mysqlbackup to write data into it, to avoid overwriting old backups or mixing up data from different backups. Use the –with-timestamp option to automatically create a subdirectory with a unique name, when storing multiple sets of backup data under the same main directory.
    • Owner and permission information for backed-up files (for Linux, Unix, and OS X systems) – In the MySQL data directory. – If you do the backup using a different OS user ID or a different umask setting than applies to the original files, you might need to run commands such as chown and chmod on the backup data. See Section A.1, of the MySQL online manual – “Limitations of mysqlbackup Command” for details.
    • Size of InnoDB redo log files – Calculated from the values of the innodb_log_file_size and innodb_log_files_in_group configuration variables. Use the technique explained for the –incremental-with-redo-log-only option. – Only needed if you perform incremental backups using the –incremental-with-redo-log-only option rather than the –incremental option. The size of the InnoDB redo log and the rate of generation for redo data dictate how often you must perform incremental backups.
    • Rate at which redo data is generated – Calculated from the values of the InnoDB logical sequence number at different points in time. Use the technique explained for the –incremental-with-redo-log-only option. – Only needed if you perform incremental backups using the –incremental-with-redo-log-only option rather than the –incremental option. The size of the InnoDB redo log and the rate of generation for redo data dictate how often you must perform incremental backups.For most backup operations, the mysqlbackup command connects to the MySQL server through –user and –password options. If you aren’t going to use the root user, then you will need to create a separate user. Follow these instructions for setting the proper permissions.

      All backup-related operations either create new files or reference existing files underneath a specified directory that holds backup data. Choose this directory in advance, on a file system with sufficient storage. (It could even be remotely mounted from a different server.) You specify the path to this directory with the –backup-dir option for many invocations of the mysqlbackup command.

      Once you establish a regular backup schedule with automated jobs, it is preferable to keep each backup within a timestamped subdirectory underneath the main backup directory. To make the mysqlbackup command create these subdirectories automatically, specify the –with-timestamp option each time you run mysqlbackup.

      For one-time backup operations, for example when cloning a database to set up a replication slave, you might specify a new directory each time, or specify the –force option of mysqlbackup to overwrite older backup files.

      If you haven’t downloaded and installed mysqlbackup, you may download it from edelivery.oracle.com (registration is required). Install the MySQL Enterprise Backup product on each database server whose contents you intend to back up. You perform all backup and restore operations locally, by running the mysqlbackup command on the same server as the MySQL instance.

      Now that we have gathered all of the required information and installed mysqlbackup, let’s run a simple and easy backup of the entire database. I installed MEB in my /usr/local directory, so I am including the full path of mysqlbackup. I am using the backup-and-apply-log option, which combines the –backup and the –apply-log options into one. The –backup option performs the initial phase of a backup. The second phase is performed later by running mysqlbackup again with the –apply-log option, which brings the InnoDB tables in the backup up-to-date, including any changes made to the data while the backup was running.

      $ /usr/local/meb/bin/mysqlbackup --user=root --password --backup-dir=/Users/tonydarnell/hotbackups backup-and-apply-log
      MySQL Enterprise Backup version 3.12.0 [2014/11/12] 
      Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved.
      
       mysqlbackup: INFO: Starting with following command line ...
       /usr/local/meb/bin/mysqlbackup --user=root --password 
              --backup-dir=/Users/tonydarnell/hotbackups backup-and-apply-log 
      
      Enter password: 
       mysqlbackup: INFO: MySQL server version is '5.6.9-rc-log'.
       mysqlbackup: INFO: Got some server configuration information from running server.
      
      IMPORTANT: Please check that mysqlbackup run completes successfully.
                 At the end of a successful 'backup-and-apply-log' run mysqlbackup
                 prints "mysqlbackup completed OK!".
      
      --------------------------------------------------------------------
                             Server Repository Options:
      --------------------------------------------------------------------
        datadir = /usr/local/mysql/data/
        innodb_data_home_dir = /usr/local/mysql/data
        innodb_data_file_path = ibdata1:40M:autoextend
        innodb_log_group_home_dir = /usr/local/mysql/data
        innodb_log_files_in_group = 2
        innodb_log_file_size = 5242880
        innodb_page_size = 16384
        innodb_checksum_algorithm = innodb
        innodb_undo_directory = /usr/local/mysql/data/
        innodb_undo_tablespaces = 0
        innodb_undo_logs = 128
      
      --------------------------------------------------------------------
                             Backup Config Options:
      --------------------------------------------------------------------
        datadir = /Users/tonydarnell/hotbackups/datadir
        innodb_data_home_dir = /Users/tonydarnell/hotbackups/datadir
        innodb_data_file_path = ibdata1:40M:autoextend
        innodb_log_group_home_dir = /Users/tonydarnell/hotbackups/datadir
        innodb_log_files_in_group = 2
        innodb_log_file_size = 5242880
        innodb_page_size = 16384
        innodb_checksum_algorithm = innodb
        innodb_undo_directory = /Users/tonydarnell/hotbackups/datadir
        innodb_undo_tablespaces = 0
        innodb_undo_logs = 128
      
       mysqlbackup: INFO: Unique generated backup id for this is 13742482113579320
      
       mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
      130719 11:36:53 mysqlbackup: INFO: Full Backup operation starts with following threads
      		1 read-threads    6 process-threads    1 write-threads
      130719 11:36:53 mysqlbackup: INFO: System tablespace file format is Antelope.
      130719 11:36:53 mysqlbackup: INFO: Starting to copy all innodb files...
      130719 11:36:53 mysqlbackup: INFO: Copying /usr/local/mysql/data/ibdata1 (Antelope file format).
      130719 11:36:53 mysqlbackup: INFO: Found checkpoint at lsn 135380756.
      130719 11:36:53 mysqlbackup: INFO: Starting log scan from lsn 135380480.
      130719 11:36:53 mysqlbackup: INFO: Copying log...
      130719 11:36:54 mysqlbackup: INFO: Log copied, lsn 135380756.
      
      (I have truncated some of the database and table output to save space)
      .....
      130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/innodb_index_stats.ibd (Antelope file format).
      130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/innodb_table_stats.ibd (Antelope file format).
      130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/slave_master_info.ibd (Antelope file format).
      130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/slave_relay_log_info.ibd (Antelope file format).
      130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/slave_worker_info.ibd (Antelope file format).
      .....
      130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/testcert/t1.ibd (Antelope file format).
      130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/testcert/t3.ibd (Antelope file format).
      .....
      130719 11:36:57 mysqlbackup: INFO: Copying /usr/local/mysql/data/watchdb/watches.ibd (Antelope file format).
      .....
      130719 11:36:57 mysqlbackup: INFO: Completing the copy of innodb files.
      130719 11:36:58 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.
      130719 11:36:58 mysqlbackup: INFO: Starting to lock all the tables...
      130719 11:36:58 mysqlbackup: INFO: All tables are locked and flushed to disk
      130719 11:36:58 mysqlbackup: INFO: Opening backup source directory '/usr/local/mysql/data/'
      130719 11:36:58 mysqlbackup: INFO: Starting to backup all non-innodb files in 
      	subdirectories of '/usr/local/mysql/data/'
      .....
      130719 11:36:58 mysqlbackup: INFO: Copying the database directory 'comicbookdb'
      .....
      130719 11:36:59 mysqlbackup: INFO: Copying the database directory 'mysql'
      130719 11:36:59 mysqlbackup: INFO: Copying the database directory 'performance_schema'
      .....
      130719 11:36:59 mysqlbackup: INFO: Copying the database directory 'test'
      .....
      130719 11:36:59 mysqlbackup: INFO: Copying the database directory 'watchdb'
      130719 11:36:59 mysqlbackup: INFO: Completing the copy of all non-innodb files.
      130719 11:37:00 mysqlbackup: INFO: A copied database page was modified at 135380756.
                (This is the highest lsn found on page)
                Scanned log up to lsn 135384397.
                Was able to parse the log up to lsn 135384397.
                Maximum page number for a log record 375
      130719 11:37:00 mysqlbackup: INFO: All tables unlocked
      130719 11:37:00 mysqlbackup: INFO: All MySQL tables were locked for 1.589 seconds.
      130719 11:37:00 mysqlbackup: INFO: Full Backup operation completed successfully.
      130719 11:37:00 mysqlbackup: INFO: Backup created in directory '/Users/tonydarnell/hotbackups'
      130719 11:37:00 mysqlbackup: INFO: MySQL binlog position: filename mysql-bin.000013, position 85573
      
      -------------------------------------------------------------
         Parameters Summary         
      -------------------------------------------------------------
         Start LSN                  : 135380480
         End LSN                    : 135384397
      -------------------------------------------------------------
      
       mysqlbackup: INFO: Creating 14 buffers each of size 65536.
      130719 11:37:00 mysqlbackup: INFO: Apply-log operation starts with following threads
      		1 read-threads    1 process-threads
      130719 11:37:00 mysqlbackup: INFO: ibbackup_logfile's creation parameters:
                start lsn 135380480, end lsn 135384397,
                start checkpoint 135380756.
       mysqlbackup: INFO: InnoDB: Starting an apply batch of log records to the database...
      InnoDB: Progress in percent: 0 1 .... 99 Setting log file size to 5242880
      Setting log file size to 5242880
      130719 11:37:00 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to
                lsn 135384397.
       mysqlbackup: INFO: Last MySQL binlog file position 0 85573, file name mysql-bin.000013
      130719 11:37:00 mysqlbackup: INFO: The first data file is '/Users/tonydarnell/hotbackups/datadir/ibdata1'
                and the new created log files are at '/Users/tonydarnell/hotbackups/datadir'
      130719 11:37:01 mysqlbackup: INFO: Apply-log operation completed successfully.
      130719 11:37:01 mysqlbackup: INFO: Full backup prepared for recovery successfully.
      
      mysqlbackup completed OK!
      

      Now, I can take a look at the backup file that was created:

      root@macserver01: $ pwd
      /Users/tonydarnell/hotbackups
      root@macserver01: $ ls -l
      total 8
      -rw-r--r--   1 root  staff  351 Jul 19 11:36 backup-my.cnf
      drwx------  21 root  staff  714 Jul 19 11:37 datadir
      drwx------   6 root  staff  204 Jul 19 11:37 meta
      $ ls -l datadir
      total 102416
      drwx------   5 root  staff       170 Jul 19 11:36 comicbookdb
      -rw-r-----   1 root  staff   5242880 Jul 19 11:37 ib_logfile0
      -rw-r-----   1 root  staff   5242880 Jul 19 11:37 ib_logfile1
      -rw-r--r--   1 root  staff      4608 Jul 19 11:37 ibbackup_logfile
      -rw-r--r--   1 root  staff  41943040 Jul 19 11:37 ibdata1
      drwx------  88 root  staff      2992 Jul 19 11:36 mysql
      drwx------  55 root  staff      1870 Jul 19 11:36 performance_schema
      drwx------   3 root  staff       102 Jul 19 11:36 test
      drwx------  30 root  staff      1020 Jul 19 11:36 testcert
      drwx------  19 root  staff       646 Jul 19 11:36 watchdb
      
      root@macserver01: $ ls -l meta
      total 216
      -rw-r--r--  1 root  staff  90786 Jul 19 11:37 backup_content.xml
      -rw-r--r--  1 root  staff   5746 Jul 19 11:36 backup_create.xml
      -rw-r--r--  1 root  staff    265 Jul 19 11:37 backup_gtid_executed.sql
      -rw-r--r--  1 root  staff    321 Jul 19 11:37 backup_variables.txt
      

      As you can see, the backup was created in /Users/tonydarnell/hotbackups. If I wanted to have a unique folder for this backup, I can use the –with-timestamp.

      The –with-timestamp option places the backup in a subdirectory created under the directory you specified above. The name of the backup subdirectory is formed from the date and the clock time of the backup run.

      I will run the same backup command again, but with the –with-timestamp option:

      (I am not going to duplicate the entire output – but I will only show you the output where it creates the sub-directory under /Users/tonydarnell/hotbackups)

      $ /usr/local/meb/bin/mysqlbackup --user=root --password --backup-dir=/Users/tonydarnell/hotbackups backup-and-apply-log --with-timestamp
      ......
      130719 11:49:54 mysqlbackup: INFO: The first data file is '/Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/ibdata1'
                and the new created log files are at '/Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir'
      130719 11:49:54 mysqlbackup: INFO: Apply-log operation completed successfully.
      130719 11:49:54 mysqlbackup: INFO: Full backup prepared for recovery successfully.
      
      mysqlbackup completed OK!
      

      So, I ran the backup again to get a unique directory. Instead of the backup files/directories being placed in /Users/tonydarnell/hotbackups, it created a sub-directory with a timestamp for the directory name:

      $ pwd
      /Users/tonydarnell/hotbackups
      root@macserver01: $ ls -l
      total 0
      drwx------  5 root  staff  170 Jul 19 11:49 2015-05-19_11-49-48
      $ ls -l 2015-05-19_11-49-48
      total 8
      -rw-r--r--   1 root  staff  371 Jul 19 11:49 backup-my.cnf
      drwx------  21 root  staff  714 Jul 19 11:49 datadir
      drwx------   6 root  staff  204 Jul 19 11:49 meta
      

      Note: If you don’t use the backup-and-apply-log option you will need to read this: Immediately after the backup job completes, the backup files might not be in a consistent state, because data could be inserted, updated, or deleted while the backup is running. These initial backup files are known as the raw backup.

      You must update the backup files so that they reflect the state of the database corresponding to a specific InnoDB log sequence number. (The same kind of operation as crash recovery.) When this step is complete, these final files are known as the prepared backup.

      During the backup, mysqlbackup copies the accumulated InnoDB log to a file called ibbackup_logfile. This log file is used to “roll forward” the backed-up data files, so that every page in the data files corresponds to the same log sequence number of the InnoDB log. This phase also creates new ib_logfiles that correspond to the data files.

      The mysqlbackup option for turning a raw backup into a prepared backup is apply-log. You can run this step on the same database server where you did the backup, or transfer the raw backup files to a different system first, to limit the CPU and storage overhead on the database server.

      Note: Since the apply-log operation does not modify any of the original files in the backup, nothing is lost if the operation fails for some reason (for example, insufficient disk space). After fixing the problem, you can safely retry apply-log and by specifying the –force option, which allows the data and log files created by the failed apply-log operation to be overwritten.

      For simple backups (without compression or incremental backup), you can combine the initial backup and the apply-log step using the option backup-and-apply-log.

      MEB 3.9 and later creates two .cnf files based on the output of SHOW GLOBAL VARIABLES: server-my.cnf (non-default values) and server-all.cnf (all values).

      Now that we have a completed backup, we are going to copy the backup files and the my.cnf file over to a different server to restore the databases. We will be using a server that was setup as a slave server to the server where the backup occurred. If you need to restore the backup to the same server, you will need to refer to the mysqlbackup manual. I copied the backup files as well as the my.cnf file to the new server:

      # pwd
      /Users/tonydarnell/hotbackups
      # ls -l
      total 16
      drwxrwxrwx  5 tonydarnell  staff   170 Jul 19 15:38 2015-05-19_11-49-48
      

      On the new server (where I will restore the data), I shutdown the mysqld process (mysqladmin -uroot -p shutdown), copied the my.cnf file to the proper directory, and now I can restore the database to the new server, using the copy-back option. The copy-back option requires the database server to be already shut down, then copies the data files, logs, and other backed-up files from the backup directory back to their original locations, and performs any required postprocessing on them.

      # /usr/local/meb/bin/mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/Users/tonydarnell/hotbackups/2015-05-19_11-49-48 copy-back
      MySQL Enterprise Backup version 3.12.0 [2014/11/12] 
      Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved.
      
       mysqlbackup: INFO: Starting with following command line ...
       /usr/local/meb/bin/mysqlbackup --defaults-file=/etc/my.cnf 
              --backup-dir=/Users/tonydarnell/hotbackups/2015-05-19_11-49-48 
              copy-back 
      
      IMPORTANT: Please check that mysqlbackup run completes successfully.
                 At the end of a successful 'copy-back' run mysqlbackup
                 prints "mysqlbackup completed OK!".
      
      --------------------------------------------------------------------
                             Server Repository Options:
      --------------------------------------------------------------------
        datadir = /usr/local/mysql/data
        innodb_data_home_dir = /usr/local/mysql/data
        innodb_data_file_path = ibdata1:40M:autoextend
        innodb_log_group_home_dir = /usr/local/mysql/data
        innodb_log_files_in_group = 2
        innodb_log_file_size = 5M
        innodb_page_size = Null
        innodb_checksum_algorithm = innodb
      
      --------------------------------------------------------------------
                             Backup Config Options:
      --------------------------------------------------------------------
        datadir = /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir
        innodb_data_home_dir = /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir
        innodb_data_file_path = ibdata1:40M:autoextend
        innodb_log_group_home_dir = /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir
        innodb_log_files_in_group = 2
        innodb_log_file_size = 5242880
        innodb_page_size = 16384
        innodb_checksum_algorithm = innodb
        innodb_undo_directory = /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir
        innodb_undo_tablespaces = 0
        innodb_undo_logs = 128
      
       mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
      130719 15:54:41 mysqlbackup: INFO: Copy-back operation starts with following threads
      		1 read-threads    1 write-threads
      130719 15:54:41 mysqlbackup: INFO: Copying /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/ibdata1.
      .....
      130719 15:54:42 mysqlbackup: INFO: Copying /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/comicbookdb/comics.ibd.
      .....
      130719 15:54:42 mysqlbackup: INFO: Copying /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/mysql/innodb_index_stats.ibd.
      130719 15:54:42 mysqlbackup: INFO: Copying /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/mysql/innodb_table_stats.ibd.
      130719 15:54:42 mysqlbackup: INFO: Copying /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/mysql/slave_master_info.ibd.
      130719 15:54:42 mysqlbackup: INFO: Copying /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/mysql/slave_relay_log_info.ibd.
      130719 15:54:42 mysqlbackup: INFO: Copying /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/mysql/slave_worker_info.ibd.
      .....
      130719 15:54:43 mysqlbackup: INFO: Copying /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/watchdb/watches.ibd.
      .....
      130719 15:54:43 mysqlbackup: INFO: Copying the database directory 'comicbookdb'
      .....
      130719 15:54:43 mysqlbackup: INFO: Copying the database directory 'mysql'
      130719 15:54:43 mysqlbackup: INFO: Copying the database directory 'performance_schema'
      .....
      130719 15:54:43 mysqlbackup: INFO: Copying the database directory 'test'
      .....
      130719 15:54:43 mysqlbackup: INFO: Copying the database directory 'watchdb'
      130719 15:54:43 mysqlbackup: INFO: Completing the copy of all non-innodb files.
      130719 15:54:43 mysqlbackup: INFO: Copying the log file 'ib_logfile0'
      130719 15:54:43 mysqlbackup: INFO: Copying the log file 'ib_logfile1'
      130719 15:54:44 mysqlbackup: INFO: Copy-back operation completed successfully.
      130719 15:54:44 mysqlbackup: INFO: Finished copying backup files to '/usr/local/mysql/data'
      
      mysqlbackup completed OK!
      

      I can now restart MySQL. I have a very small database (less than 50 megabytes). But it took less than a minute to restore the database. If I had to rebuild my database using mysqldump, it would take a lot longer. If you have a very large database, the different in using mysqlbackup and mysqldump could be in hours. For example, a 32-gig database with 33 tables takes about eight minutes to restore with mysqlbackup. Restoring the same database with a mysqldump file takes over two hours.

      An easy way to check to see if the databases match (assuming that I haven’t added any new records in any of the original databases – which I haven’t), I can use one of the MySQL Utilities – mysqldbcompare. I wrote about how to do this in an earlier blog about using it to test two replicated databases, but it will work here as well – see Using MySQL Utilities Workbench Script mysqldbcompare To Compare Two Databases In Replication.

      The mysqldbcompare utility “compares the objects and data from two databases to find differences. It identifies objects having different definitions in the two databases and presents them in a diff-style format of choice. Differences in the data are shown using a similar diff-style format. Changed or missing rows are shown in a standard format of GRID, CSV, TAB, or VERTICAL.” (from: mysqldbcompare — Compare Two Databases and Identify Differences)

      Some of the syntax may have changed for mysqldbcompare since I wrote that blog, so you will need to reference the help notes for mysqldbcompare. You would need to run this for each of your databases.

      $ mysqldbcompare --server1=scripts:scripts999@192.168.1.2   --server2=scripts:scripts999@192.168.1.123 --run-all-tests --difftype=context comicbookdb:comicbookdb
      # server1 on 192.168.1.2: ... connected.
      # server2 on 192.168.1.123: ... connected.
      # Checking databases comicbookdb on server1 and comicbookdb on server2
      
                                                          Defn    Row     Data   
      Type      Object Name                               Diff    Count   Check  
      --------------------------------------------------------------------------- 
      TABLE     comics                                    pass    pass    pass   
      
      Databases are consistent.
      
      # ...done
      

      You can try and run this for the mysql database, but you may get a few errors regarding the mysql.backup_history and mysql.backup_progress tables:

      $ mysqldbcompare --server1=scripts:scripts999@192.168.1.2   --server2=scripts:scripts999@192.168.1.123 --run-all-tests --difftype=context mysql:mysql
      # server1 on 192.168.1.2: ... connected.
      # server2 on 192.168.1.123: ... connected.
      # Checking databases mysql on server1 and mysql on server2
      
                                                          Defn    Row     Data   
      Type      Object Name                               Diff    Count   Check  
      --------------------------------------------------------------------------- 
      TABLE     backup_history                            pass    FAIL    SKIP    
      
      Row counts are not the same among mysql.backup_history and mysql.backup_history.
      
      No primary key found.
      
      TABLE     backup_progress                           pass    FAIL    SKIP    
      
      Row counts are not the same among mysql.backup_progress and mysql.backup_progress.
      
      No primary key found.
      
      TABLE     columns_priv                              pass    pass    pass    
      TABLE     db                                        pass    pass    pass    
      TABLE     event                                     pass    pass    pass    
      TABLE     func                                      pass    pass    pass    
      TABLE     general_log                               pass    pass    SKIP    
      
      No primary key found.
      
      TABLE     help_category                             pass    pass    pass    
      TABLE     help_keyword                              pass    pass    pass    
      TABLE     help_relation                             pass    pass    pass    
      TABLE     help_topic                                pass    pass    pass    
      TABLE     innodb_index_stats                        pass    pass    pass    
      TABLE     innodb_table_stats                        pass    pass    pass    
      TABLE     inventory                                 pass    pass    pass    
      TABLE     ndb_binlog_index                          pass    pass    pass    
      TABLE     plugin                                    pass    pass    pass    
      TABLE     proc                                      pass    pass    pass    
      TABLE     procs_priv                                pass    pass    pass    
      TABLE     proxies_priv                              pass    pass    pass    
      TABLE     servers                                   pass    pass    pass    
      TABLE     slave_master_info                         pass    pass    pass    
      TABLE     slave_relay_log_info                      pass    pass    pass    
      TABLE     slave_worker_info                         pass    pass    pass    
      TABLE     slow_log                                  pass    pass    SKIP    
      
      No primary key found.
      
      TABLE     tables_priv                               pass    pass    pass    
      TABLE     time_zone                                 pass    pass    pass    
      TABLE     time_zone_leap_second                     pass    pass    pass    
      TABLE     time_zone_name                            pass    pass    pass    
      TABLE     time_zone_transition                      pass    pass    pass    
      TABLE     time_zone_transition_type                 pass    pass    pass    
      TABLE     user                                      pass    pass    pass   
      
      Database consistency check failed.
      
      # ...done
      

      For example, when you compare the mysql.backup_history tables, the original database will have two entries – as I ran mysqlbackup twice. But the second backup entry doesn’t get entered until after the backup has occurred, and it isn’t reflected in the backup files.

      Original Server

      mysql> select count(*) from mysql.backup_history;
      +----------+
      | count(*) |
      +----------+
      |        2 |
      +----------+
      1 row in set (0.00 sec)
      

      Restored Server

      mysql> select count(*) from mysql.backup_history;
      +----------+
      | count(*) |
      +----------+
      |        1 |
      +----------+
      1 row in set (0.00 sec)
      

      For the mysql.backup_progress tables, the original database has ten rows, while the restored database has seven.

      There are many options for using mysqlbackup, including (but not limited to) incremental backup, partial backup , compression, backup to tape, point-in-time recovery (PITR), partial restore, etc. If you are running MySQL in a production environment, then you should look at MySQL Enterprise Edition, which includes MySQL Enterprise Backup. Of course, you should always have a backup and recovery plan in place. Finally, if and when possible, practice restoring your backup on a regular basis, to make sure that if your server crashes, you can restore your database quickly.

       


      Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
      Tony is the author of Twenty Forty-Four: The League of Patriots 

      Visit http://2044thebook.com for more information.