I’ll be giving a talk tonight at the Ignite MySQL! For those of you who don’t know about this format, I suggest that you check out Ignite!.  The basic premise is:

Fast-paced, fun, thought-provoking, social, local, global—Ignite is all of these and more. It’s a high-energy evening of 5-minute talks by people who have an idea—and the guts to get onstage and share it with their hometown crowd.

My talk will be called Backups Don’t Make Me Money

Backups aren’t exciting or glamorous. They don’t make you money and there’s no such thing as a valid backup. But, understanding your restores is exciting! Let me draw you a few pictures of database restores at their most ironic, entertaining, and even thrilling. Five minutes, 20 slides auto advancing every 15 seconds.  It will be …

Dear Community,

The notice is hereby given that next version 1.2 of XtraBackup software is released.

The list of changes in this version includes:
Changelog:

• XtraBackup supports now XtraDB 10
• tar4ibd supports variable page size and fast_checksum of XtraDB
• Supported 32bit platform for Centos 5, Centos 4, Debian lenny and etch, Ubuntu Karmic, Jaunty, Intrepid, Hardy, FreeBSD

Fixeb bugs in this release:

• Bug #550776: "--use-memory" is not used at InnoDB-Plugin based version

The binary packages for as well as source code of the XtraBackup is available on http://www.percona.com/percona-builds/XtraBackup/XtraBackup-1.2/.

…

I’ve been wanting to write a backup script for a while now that does the following: reads the partition information for the directory that you are backing up into and computes the used/available/percentage-available space. Then it reads the total data size from the MySQL tables and ensures that you have enough space on disk (for [...]

If you ever wondered how big is that or another index in InnoDB ... you had to calculate it yourself by multiplying size of row (which I should add is harder in the case of a VARCHAR - since you need to estimate average length) on count of records. And it still would be quite inaccurate as secondary indexes tend to take more space. So we added more detailed index statistics into our xtrabackup utility. The thanks for this feature goes to a well known Social Network who sponsored the development.

We chose to put this into xtrabackup for a couple of reasons - the first is that running statistics on your backup database does not need to hurt production servers, and the second reason is that running statistic on a stopped database is more accurate than with online (although online is also supported, but you may have inexact results).

Let's see how it works. I have one table with size 13Gb what was filled during about 2.5 years.
…

Recently while we were building a slave with a newer version of MySQL 5.1 from an InnoDB Hot backup, the following error occurred when we ran mysql_upgrade:

mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
ERROR 13 (HY000) at line 311: Can't get stat of './mysql/general_log.CSV' (Errcode: 2)
ERROR 13 (HY000) at line 316: Can't get stat of './mysql/slow_log.CSV' (Errcode: 2)
FATAL ERROR: Upgrade failed

The problem is that in MySQL 5.1, it is possible to log the slow query log and general log to tables in the mysql schema (source: Selecting General Query and Slow Query Log Output Destinations). These tables are …

We've written about replication slaves lagging behind masters before, but one of the other side effects of the binary log being serialized, is that it also limits the effectiveness of using it for incremental backup.  Let me make up some numbers for the purposes of this example:

• We have 2 Servers in a Master-Slave topology.
• The database size is 100 GB (same tables on each).
• The slave machine barely keeps up with the master (at 90% capacity during peak, 75% during offpeak)
• The peak window is 12 hours, the offpeak window is 12 hours.

Provided that the backup method was raw data files, it shouldn't take much more than 30 minutes to restore 100GB (50MB/s), but to replay one day of binary logs  it would take an additional 20 hours ((12 * …

Speed of InnoDB recovery is known and quite annoying problem. It was discussed many times, see:

http://bugs.mysql.com/bug.php?id=29847

http://dammit.lt/2008/10/26/innodb-crash-recovery/

This is problem when your InnoDB crashes, it may takes long time to start. Also it affects restoring from backup (both LVM and xtrabackup / innobackup)

In this is simple test, I do crash mysql during in-memory tpcc-mysql benchmark with 16 running threads.
MySQL params are:

PLAIN TEXT CODE:

1. innodb_buffer_pool_size=16G
2. innodb_log_files_in_group=3
3. innodb_log_file_size=512M

So let's take standard MySQL …

Dear community,

The release 0.8 of the opensource backup tool for InnoDB and XtraDB is available for download.

Key features:

• New mode of innobackupex --stream=tar4ibd; new command tar4ibd based on libtar-1.2.11
• Experimental option --export is added (see Vadim's post "Impossible - possible, moving InnoDB tables between servers"for details)

tar4ibd is made to be sure that read of InnoDB page is consistent. Before we had some complains what in stream mode some pages are getting corrupted, and we suspect tar can do read of pages in time when they changed. So we patches libtar to make read consistent.

Export is added to support moving .ibd tablespaces …

We announce next version of our xtrabackup tool and we consider it stable enough to put label RC on it.

Changelist includes:

- use O_DIRECT by default for handling InnoDB files
- use posix fadvise call to disable OS caching of copying files
- disable recovery of double buffer

Also we added binary builds for FreeBSD 7 64bit platform

You can download current binaries (64bit) RPM for RHEL4 and RHEL5 (compatible with CentOS also), DEB for Debian/Ubuntu, tar.gz for Mac OS / Intel and .tar.gz for FreeBSD 7:
http://www.percona.com/mysql/xtrabackup/0.7/.
By the same link you can find general .tar.gz with binaries which can be run on any modern Linux distribution.
By the same link you can download source code if you do not want to deal with bazaar and Launchpad.

The project lives on Launchpad : …

Using xtrabackup for copying files can really saturate your disks, and that why we made special option --throttle=rate to limit rate of IO per second. But it really works when you do local copy.
What about stream backup ? Even you copy just to remote box with
innobackupex --stream=tar | ssh remotebox "tar xfi -"
, read may be so intensive so your mysqld feels impact, slave getting behind, etc...

For this there is a nice small utility - pv .

With pv you run:
innobackupex --stream=tar | pv -q -L10m | ssh remotebox "tar xfi -"

and it will limit channel rate to 10 M per second.

Entry posted by Vadim | No comment

Add to: …