This week we are talking about size, which is a subject that should matter to any system administrator in charge of the backup system of any project, and in particular database backups.

I sometimes get questions about what should be the best compression tool to apply during a particular backup system: gzip? bzip2? any other?

The testing environment

In order to test several formats and tools, I created a .csv file (comma-separated values) that was 3,700,635,579 bytes in size by transforming a recent dump of all the OpenStreetMap nodes of the European portion of Spain. It had a total of 46,741,126 rows and looked like this:

171773  38.6048402      -0.0489871      4       2012-08-25 00:37:46     12850816        472193  rubensd
171774  38.6061981      -0.0496867      2       2008-01-19 10:23:21     666916  9250 …

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 …

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 …

TL;DR version: The backup locks feature introduced in Percona Server 5.6.16-64.0 is a lightweight alternative to FLUSH TABLES WITH READ LOCK and can be used to take both physical and logical backups with less downtime on busy servers. To employ the feature with mysqldump, use mysqldump --lock-for-backup --single-transaction. The next release of Percona XtraBackup will also be using backup locks automatically if the target server supports the feature.

Now on to the gory details, but let’s start with some history.

In the beginning…

In the beginning there was FLUSH TABLES, and users messed with their MyISAM tables under a live server and were not ashamed. Users could do nice things like:

mysql> FLUSH TABLES;
# execute myisamchk, myisampack, backup / restore some tables, etc.

And users were happy until someone realized that tables must be …

More than once I have wished that mysqldump was never created. In many backup scenarios it is better to use other tools such as xtrabackup. However, there is still times when I end up using mysqldump. For one thing it is included with the mysql packages. It's "always there". Secondly, it sometimes is really a good fit for the tasks. And occassionally, with a bit of work, you can make it do neat tricks. 

I'm going to cover a few of the tricks I have used over the years. Hopefully you will find them helpful. In some cases one of these might even save your bacon.

Trick One:

One of the annoying issues with mysqldump is its lack of flexibility. It's very easy to dump all databases (--all-databases option), or one or more databases (--databases). Even dumping a single table isn't too difficult. But what about all the tables that start with "a"? You could specify each table on the command line, but I have ADD and I can't …

Recently, I was working with a client that asked about using Percona Xtrabackup to take a snapshot of a particular MySQL schema and then reload it with a different schema name on the same server.  It caught me off guard because I’d never really thought about it – typically, I’ve used Xtrabackup simply to clone a server (for replication or migration) or migrate an existing schema to a new server.  However, given the import/export functionality of Xtrabackup combined with Percona Server (Exporting and Importing Tables), it did seem …

It's now safe to backup InnoDB with mysqldumpBefore MySQL 5.6, running mysqldump to make backup of InnoDB tables could cause your backup to 'loose' some data. The problem is described in our manual  here.

In latest MySQL 5.6 this is no longer a problem, this means you no longer risk 'loosing' data when using mysqlbackup together with DDL statements on your InnoDB tables. If you are interested in metadata-locking (MDL) you can read more about MDL  here.

To test this we need to create a few tables and also look at which order mysqldump processes tables.

mysql> CREATE DATABASE ted;
mysql> USE ted;
mysql> CREATE TABLE `a` (`i` int(11) DEFAULT NULL) ENGINE=InnoDB
mysql> CREATE …

Percona Server for MySQL version 5.1.68-14.6

Percona is glad to announce the release of Percona Server  5.1.68 -14.6 on April 19, 2013 (downloads are available here and from the Percona Software Repositories). Based on MySQL 5.1.68, including all the bug fixes in it, Percona Server 5.1.68-14.6, a MySQL alternative, is now the current stable release in the 5.1 series. All …

With MySQL 5.5 the default storage engine was changed to InnoDB. This was a good step as most users expected MySQL to support transactions, row level locking and all the other InnoDB features, but with 5.1 they sometimes forgot to choose the right storage engine. As most databases don't have multiple storage engines and many MySQL users changed the default storage engine to InnoDB this made the switch to MySQL easier and the default behaviour more in line with what people expect from a relational database.

Changing the storage engine can of course be done on a per table or per instance basis (default-storage-engine in my.cnf). The temporary tables created with 'CREATE TEMPORARY TABLE ...' should not be forgotten. The performance of InnoDB or MyISAM for temporary tables can have quite some impact, especially with slow storage, a buffer pool which is too small to hold the complete dataset or very small temporary tables. In MySQL 5.6 …

Some time ago, I had to convert all tables of a database from MyISAM to InnoDB on a new server. The plan was to take a logical dump on the master, exporting separately the schema and the data, then edit the CREATE TABLE statements to ensure all tables are created with InnoDB, and reload everything on the new server.

Quite easy, isn’t it? Of course I wanted to run a test first before performing the actions on a live system.

So let’s play with the sakila database.

mysqldump has options to export schema and data separately, let’s use them:

# Export schema
$ mysqldump --no-data sakila > schema.sql

# Export data
$ mysqldump --no-create-info sakila > data.sql

Just to check that everything is fine, let’s reimport the data in a new database:

mysql> CREATE DATABASE sakila2;
Query OK, 1 row affected (0.00 sec)

$ mysql sakila2 < schema.sql 
$ mysql sakila2 < data.sql …