XtraBackup is a great piece of software from Percona, which allows creating (nearly) lock-less MySQL/InnoDB backups. The tool has been around for quite some time and recently even received a major version bump. I have relied on it many times over the years. As it turns out, using it in some configurations may lead to heavy swapping or prevent MySQL from running queries.

So far I only kept complaining about the wrapper script XtraBackup has been distributed with and which was taken from Oracle’s InnoDB Hot Backup. The infamous innobackupex-1.5.1 was neither well written, nor was it even fully compatible with the XtraBackup’s feature set. This sometimes led to weird problems where there should not be any.

This time the problem can appear elsewhere. Mostly when one using the tool does not understand how it works in certain circumstances.

How does XtraBackup work?

In a few short words. XtraBackup performs two major tasks while it is running. The primary task is of course copying tablespaces, which constitutes the core of each backup. Because the process isn’t anything like making a point-in-time snapshot, it takes a significant amount of time as InnoDB files are sequentially read and written into another location one after another. There will always be transactions changing data in tables between when a backup started and when it ended. So in order to provide a fully consistent, point-in-time backup, XtraBackup has to work on another task. It follows and archives InnoDB transaction logs contents as new transactions commit while the process is running. The end result is an inconsistent backup of tablespaces and a transaction log that enables XtraBackup to fix the inconsistencies during prepare phase.

Streaming mode.

In many cases XtraBackup is used in streaming mode. It enables sending a backup straight over a network link into another system without storing any of the files on a database server itself. This is very useful for larger databases which may simply not fit two complete copies of data on available storage. It may be also useful for busy databases as it removes many disk I/Os that would otherwise be used for writing an archive to local disks.

However it is not entirely true that a streaming backup does not use any disk space on the source server:

–tmpdir=DIRECTORY
This option specifies the location where a temporary file will be stored. The option accepts a string argument. It should be used when –remote-host or –stream is specified. For these options, the transaction log will first be stored to a temporary file, before streaming or copying to a remote host. This option specifies the location where that temporary file will be stored. If the option is not specifed, the default is to use the value of tmpdir read from the server configuration.

A transaction log backup will always be written to the local storage first, even if it has to be shipped out elsewhere later.

The problem.

What if you want to run XtraBackup, but also have configured database to use memory-based storage for temporary space, e.g. tmpdir in my.cnf was set to /dev/shm or other location that mounts a tmpfs volume? Such configuration is often used to help performance when a lot of temporary tables are being created on disk. Assuming that you fail to notice the tmpdir option comment for XtraBackup, which is not actually very unlikely as I have seen very few people use it, you will be running a backup that by default (temporarily) archives the database transaction log into RAM.

If the option is not specifed, the default is to use the value of tmpdir read from the (MySQL) server configuration.

Given your database is sufficiently large and busy, or perhaps because you throttle XtraBackup, the backup process may run many hours. In several hours time the transaction logs can grow a lot, even by several gigabytes or more. By default a tmpfs volume that is these days automatically mounted by any Linux distribution can use up to 50% of RAM. As in practice no one really changes the limit from its default value, the transaction log archive can possibly also eat up to 50% of system’s memory. If RAM utilization on a server was high, and usually a mature database system uses even more than 90% of the available memory, it could eventually end up like this:

Swapping. When it happens a database server that is under any real load essentially stops responding. In this case it may not even matter whether it is MySQL instance memory being paged out or perhaps tmpfs volume contents.

At the same time the memory volume contents would look like this:

garfield ~ # ls -lh /dev/shm/
-rw-r--r--  1 root root   75 05-22 09:08 mysql-stderr
-rw-r--r--  1 root root  226 05-22 09:08 mysql-stdout
-rw-r--r--  1 root root   2G 05-22 09:08 xtrabackup_logfile

More problems.

The above does not have to be the only problem. Regardless of what MySQL uses for its temporary directory, if the storage is too small (e.g. some use a few gigabytes large /tmp partition), more bad things can happen.

If it gets full during backup, MySQL will not be able to run any queries that rely on temporary files, e.g. some that use GROUP BY or ORDER BY:

mysql> SELECT * FROM sbtest ORDER BY pad DESC LIMIT 5;
ERROR 3 (HY000): Error writing file '/dev/shm/MYJee0PR' (Errcode: 28)
mysql> Bye
garfield ~ # perror 28
OS error code  28:  No space left on device

Another problem that I spotted was that XtraBackup continued performing backup even after it noticed and reported that one of its writes failed. This is clearly a bug, which permits creation of a broken backup.

innobackupex-1.5.1: Backing up file '/var/lib/mysql/sbtest/sbtest.ibd'
>> log scanned up to (1 4052543506)
>> log scanned up to (1 4052543506)
>> log scanned up to (1 4052543506)
write: 65536 > 2048
xtrabackup: Error: write to stdout
xtrabackup: Error: xtrabackup_copy_logfile() failed.
innobackupex-1.5.1: Backing up file '/var/lib/mysql/sbtest/sbtest2.ibd'
[..]
xtrabackup: The latest check point (for incremental): '1:4115657100'
xtrabackup: Error: log_copying_thread failed.
120522 13:16:19  innobackupex-1.5.1: All tables unlocked
120522 13:16:19  innobackupex-1.5.1: Connection to database server closed

innobackupex-1.5.1: Backup created in directory '/root/backup'
innobackupex-1.5.1: MySQL binlog position: filename 'mysqld-bin.000067', position 107090
120522 13:16:19  innobackupex-1.5.1: completed OK!
innobackupex-1.5.1: You must use -i (--ignore-zeros) option for extraction of the tar stream.
garfield backup # echo $?
0

Conclusions.

Be careful when implementing streaming backup with XtraBackup. Make sure to check where it will store its temporary files as they may grow relatively large or even better always explicitly set tmpdir in the command line.

This problem also raises another important matter. If you configure a MySQL server to use a RAM based volume for a database temporary storage, make sure to tune tmpfs volume configuration rather than leave it with the default limit of 50%.

WordPress on S3: no more backups

How much trouble will it be if your webserver failed?  No trouble at all, if your website keeps its content on reliable Amazon S3 storage.

There are a lot of nuances in ensuring proper backups and restores of websites. When was the last backup taken? How much data might have been lost? How long will it take to recover it? When was the last time you tested restore? Do you even have an offsite backup?

Now that you can run dynamic websites off Amazon S3 storage, we’ll demonstrate why you no longer need to worry about backing up and restoring your website data. Losing the webserver is no longer a disaster. Cloud storage offers almost unsurpassable reliability a lot of website owners (small & large) would benefit from. In a way you get an "instantaneous backup" to the cloud. Your data is always safe with the cloud storage. The webserver can simply be replaced in minutes.

Contents

OblakSoft has released the 1^st ever dynamic WordPress site running on top of Amazon S3: Yapixx.  Yapixx is ready-to-run WordPress on S3, it stores all its data (content and media) in Amazon S3 storage.  Yapixx stands for Yet Another Picture Sharing Site.

Yapixx taps into power of Amazon S3.  Amazon S3 is inexpensive, highly reliable, available and scalable storage service.  Using Amazon S3 to store Yapixx data has the following benefits:

• No backup and recovery of the site data is needed
• Storage is extremelyreliable and durable by Amazon S3 design
• Pictures are served by Amazon S3 directly, which makes Yapixx highly scalable
• Storage cost scales with usage, no upfront reservation is needed
• Storage consumption scales up and down with the amount of data stored

The crash test is going to illustrate the first point: no backup and recovery is needed.  The data for the web site is safely stored in Amazon S3, so the machine that Yapixx runs on can be replaced in minutes.

Crash test. Start the Webserver.

To get started with the crash test, follow the five steps to deploy Yapixx:

1. Sign up for an AWS account.
2. Create an S3 bucket.
3. Start EC2 instance using read-only Yapixx AMI.
4. Connect to the web application from a web browser.
5. Enter the S3 data location and authentication information.

Refer to the complete step-by-step guide for extra pointers in setting up Yapixx.

Now Yapixx is up and running and you can upload pictures.  Upload some pictures to Yapixx.  Make sure the pictures are uploaded successfully.

Crash the Webserver – it is a crash test after all.

Then terminate the EC2 instance that is running Yapixx.  As a result the server should get completely destroyed.

Is all lost?  Not at all! 

Launch the replacement Webserver.

To get a new server running, repeat the steps 3-5 of the deployment instructions:

3. Start EC2 instance using read-only Yapixx AMI.
4. Connect to the web application from a web browser.
5. Enter the S3 data location and authentication information.

The full step-by-step guide for Yapixx crash test is available at here.

Make sure that you enter the same S3 data location information!   Yapixx is going to ask for a confirmation that this is the only instance that accesses the S3 data location: running multiple instances accessing the same S3 data location may lead to data corruption and data loss.

Feeling adventurous? Change the Webserver machine.

To add extra thrill, try using a different instance size: if you originally deployed a micro instance, try using a small instance, or vice versa.  This will get you a feeling of how easy it is to scale servers up and down.  Now, that’s truly elastic!

It’ll be even more fun if it was your WordPress site, will not it be? You can rely on Yapixx as the starting point to take your site to Amazon S3. Launch your own WordPress site to the cloud today!  It’s easy and FREE, no writing code is required.  Start at http://www.oblaksoft.com/downloads.

We hope you enjoyed the crash test!  Are you interested in making your site highly available with the help of cloud storage?

See also

WordPress on S3: run a beautiful website on Amazon cloud storage.

WordPress on S3: how it works.

WordPress on S3: the beauty of simplicity (blog).

How To Back Up MySQL Databases With mylvmbackup On Debian Squeeze

mylvmbackup is a Perl script for quickly creating MySQL backups. It uses LVM's snapshot feature to do so. To perform a backup, mylvmbackup obtains a read lock on all tables and flushes all server caches to disk, creates a snapshot of the volume containing the MySQL data directory, and unlocks the tables again. This article shows how to use it on a Debian Squeeze server.

Last month, Ronald Bradford, Giuseppe Maxia and Mark Leith spoke about how to simulate a mysqldump –ignore-database.
This mysqldump option doesn’t exist and these three guys have given us various helpful solutions.

But for those of us who use ZRM community to make backup with mysqldump, the –exclude-pattern seems to do the job :

--exclude-pattern "pattern" 
All databases or tables that match the pattern are not backed up. 
If --all-databases or --databases are specified, the exclude pattern applies
to database names. If --database is specified, the exclude pattern applies
to table names. Wildcard characters * (match one or more characters),
? (match one character), [] (match one of the characters specified
within parenthesis) and | (match one of the patterns) are supported.
For example: Exclude pattern "*_db|dbase[123]|abcd" will match names
tom_db, dbase1, dbase2, abcd. All tables or databases that match this pattern
will be excluded. Character # is not permitted in the exclude pattern.

But this option seems to not work properly and I remember that I’ve used a hack to force ZRM to exclude a database from the database list since I used this tools.
I’m not talented enough in perl to really hack the code of this tool, so, I made it simple.

I made ​​a simple change to this file : /usr/bin/mysql-zrm-backup
By adding this new line after the row number 1597 :  $params = “–databases @pdbs”

To obtain that :

} else {
 $params = "--all-databases ";
 @pdbs = &enumAllDatabases();
 if( defined $inputs{"exclude-pattern"} ){
 @pdbs = &filterPattern( @pdbs );
 my $l = @pdbs;
 if( $l == 0 ){
 &printAndDie( "Nothing to backup after exclude-pattern is applied\n" );
 }
 $params = "--databases @pdbs"
 }
 if( $verbose ){
 &printLog( "backup of the following databases will be done @pdbs\n" );
 }

I can now exclude a database from my dump (but not a table).

But I’m sure a talented person could try to review the code to improve it in a right way.
If you are this person, you know what needs to be done…

If you are a ZRM Community edition practicer, share your thoughts about that.

Database backups are typically critical to organizations, and are an important part of an overall disaster recovery strategy.

MySQL Enterprise Backup performs online "Hot", non-blocking backups of your MySQL databases, and interfaces with media management software such as Symantec NetBackup, Oracle Secure Backup and IBM Tivoli Storage Manager to execute backup and restore operations.

Two new white papers are available to help you better understand:

• How to Connect MySQL Enterprise Backup with Media Management systems through System Backup to Tape (SBT) interface.
• How to specifically use MySQL Enterprise Backup with Symantec NetBackup.

Enjoy the white papers.

A few days ago I showed a quick way to extract one table form a mysqldump output. Here is a more complete version which supports extracting either a full schema or a table from the specified schema. If full schema is being extracted, the script also looks for any associated views and routines.

Usage is simple:
garfield ~ # ./extract.sh -f dump.sql -d redmine > redmine.sql
garfield ~ # ./extract.sh -f dump.sql -d redmine -t workflows > redmine-workflows.sql

extract.sh

#!/bin/bash

#
# Extracts a schema or a table from a text dump generated by mysqldump or phpMyAdmin
# (c) 2012 Maciej Dobrzanski http://www.dbasquare.com/
# Released under GNU General Public License, version 2
#

function help()
{
  cat <<EOF
  Usage: [-h] -f <dump file> -d <database name> [-t <table>]
EOF
}

while getopts "hf:d:t:" flag ; do
  case $flag in
    h)
      help
      exit 255
      ;;
    f)
      EX_DFILE=${OPTARG}
      ;;
    d)
      EX_SCHEMA=${OPTARG}
      ;;
    t)
      EX_TABLE=${OPTARG}
      ;;
    \?)
      echo "Invalid option: -$OPTARG"
      ;;
  esac
done

: ${EX_DFILE?"Missing argument: -f must be specified; try '$0 -h' for details"}
: ${EX_SCHEMA?"Missing argument: -d must be specified; try '$0 -h' for details"}

if [ ! ${EX_TABLE} ] ; then
  sed -n \
      -e '1,/^-- \(Current \)\?Database/{/^\(--.*\)*\?$/d ; p } ; /^-- \(Current \)\?Database: `'"${EX_SCHEMA}"'`/,/^-- \(Current \)\?Database/ { /^-- \(Current \)\?Database/b ; /^\/\*\![0-9]\+.*=@OLD/be ; /^--$/d ; p }' \
      -e ':e /^\/\*\![0-9]\+.*=@OLD/,${ p }' "${EX_DFILE}"
else
  sed -n \
      -e '1,/^-- \(Current \)\?Database/{/^\(--.*\)*\?$/d ; p } ; /^-- \(Current \)\?Database: `'"${EX_SCHEMA}"'`/,/^-- \(Current \)\?Database/{ /^-- Table.*`'"${EX_TABLE}"'`/,/^-- \(Table\|Temporary\|\(Current \)\?Database\)/ { /^-- \(Table\|Temporary\|\(Current \)\?Database\)/b ; /^\/\*\![0-9]\+.*=@OLD/be ; /^--$/d ; p } }' \
      -e ':e /^\/\*\![0-9]\+.*=@OLD/,${ p }' "${EX_DFILE}"
fi

Ronald Bradford and Giuseppe Maxia (hey guys!) wrote about different ways to ignore a database when using mysqldump –all-databases over the past couple of days.

Whilst the solutions are interesting, I wondered why not attack it from the proper approach, and add the option to mysqldump itself? Honestly, the patch is trivial, and doing anything against INFORMATION_SCHEMA with lots of databases and tables … well let’s just say … group_concat_max_len is the least of your worries..

15 minutes later I had a working solution:

To my surprise, I also found Bug#3228, created a little over 8 years ago.. I’ve posted the patch to the bug. It acts in exactly the same way as the –ignore-table option (specify it multiple times to ignore multiple databases, passing in only the database name for each).

Here’s my little test:

$ mysql -u root -pmysql -h 127.0.0.1 -P 3307 -e "show databases";
+--------------------+
| DATABASE           |
+--------------------+
| information_schema |
| admin              |
| mem__inventory   |
| mysql              |
| performance_schema |
| test               |
+--------------------+
$ ./mysqldump -u root -pmysql -h 127.0.0.1 -P 3307 --all-databases --ignore-database=mysql --ignore-database=mem__inventory
Warning: USING a password ON the command line interface can be insecure.
-- MySQL dump 10.13  Distrib 5.6.6-m9, for Win32 (x86)
--
-- Host: 127.0.0.1    Database:
-- ------------------------------------------------------
-- Server version       5.6.5-m8
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
 
--
-- Current Database: `admin`
--
 
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `admin` /*!40100 DEFAULT CHARACTER SET latin1 */;
 
USE `admin`;
 
--
-- Current Database: `test`
--
 
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
 
USE `test`;
 
--
-- Table structure for table `t1`
--
 
DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
  `i` BINARY(16) NOT NULL,
  `k` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Dumping data for table `t1`
--
 
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
-- Dump completed on 2012-04-19 22:55:02

Using mysqldump is a quick way to do backups, although usually limited to only smaller databases – perhaps up to a few gigabytes large. It is still a fairly popular solution as majority of databases aren’t even that big. Also phpMyAdmin provides a variant of mysqldump format through its Export function. Everything works well for plain dump and restore, but in certain situations it is necessary to restore only a single table. With all data being in a single text file, it may not be a trivial task. Here is how I deal with the problem.

Rather than editing out parts of the file that I don’t need, I wrote simple one-liners that do that for me. These are not perfect as for example they can’t deal with multiple tables by the same name existing in several different schemas, but that so far never turned out to be a problem. I encourage you to suggest improved ways of doing this, just let me know in the post comments! :-)

But now, sed to the rescue!

# Extract table structure for table 'customer'
sed -ne '1,/^-- \(Current\|Database\)/{/^\(--.*\)*\?$/d;p};/^-- Table.*`customer`/,/^-- \(Dumping\|Table\|Temporary\)/{/^\(--.*\)*\?$/d;/^DROP/d;p}' dump.sql

# Extract data dump for table 'customer'
sed -ne '1,/^-- \(Current\|Database\)/{/^\(--.*\)*\?$/d;p};/^-- Dumping.*`customer`/,/^-- \(Dumping\|Table\|Temporary\)/{/^\(--.*\)*\?$/d;p}' dump.sql

These should work with both mysqldump text dumps as well as phpMyAdmin exports, although in the second case only when the UI language is set to English.

A few days ago, Ronald Bradford asked for a mysqldump –ignore-database option.

mysqldump --databases `mysql --skip-column-names \
   -e "SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') \
   FROM information_schema.schemata WHERE schema_name \
   NOT IN ('mysql','performance_schema','information_schema');" \
   >` >/mysql/backup/rds2.sql

It's a clever solution, but unfortunately it only works if you have a handful of schemas. If your databases happens to have several dozens (or hundreds or thousands) of schemas (which is where you need this option more), then the output will be truncated to the length of group_concat_max_len (by default, 1024.)

There are two alternative methods.

The all-shell method

DATABASE_LIST=$(mysql -NBe 'show schemas' | grep -wv 'mysql\|personnel\|buildings')
mysqldump --all-databases $DATABASE_LIST

Notice that, when you use --all-databases, information_schema and performance_schema are filtered off by default.

This method works, because the default length of the command line in Unix is much longer than group_concat_max_len:

$ getconf ARG_MAX
131072  # Linux

$ getconf ARG_MAX
262144  # Mac OSX

(in Windows it's much shorter: 8191, but since I haven't used Windows for ages, I don't really care).

A more elaborate method would require a scripting wrapper around the above instructions, but I think that as it is, it's simple enough to be remembered.

The options file method

If the length of all your database names combined is more than the maximum allowed by the shell, and all you want is filtering a few databases off a huge list, there is still hope.

Let's assume that you want to ignore mysql, personnel, and buildings from your backup.

echo '[mysqldump]' > mydump.cnf
mysql -NBe "select concat('ignore-table=', table_schema, '.', table_name) \
  from information_schema.tables \
  where table_schema in ('mysql', 'personnel', 'buildings')" \
  >> mydump.cnf

Now the options file looks like this:

[mysqldump]
ignore-table=mysql.db
ignore-table=mysql.host
ignore-table=mysql.user
[...]

mysqldump --defaults-file=./mydump.cnf  -u $DBUSER -p$DBPWD --all-databases

• There will be a DROP DATABASE IF EXISTS and CREATE DATABASE for each of the excluded schemas, although no tables will be dumped.
• This method only works with --default-options-file. Theoretically, it should also work with --default-extra-file, but it doesn't. Therefore, if you are relying on an options file for connection parameters, they should be added to this file or listed in the command line.