A short time ago I posted how I was Using XtraDB Backup to backup InnoDB. Overall, the blog post was positive, but experiences that others have had (and commented to on that blog post) have made me want to put out another short article about using XtraDB backup.

The first few points remain the same — the backup process is stable, we were able to use the binaries without compiling, and using Innobackupex as the wrapper script, analogous to Innobackup.pl.

However, we did figure out why Xtrabackup had to be run as the mysql user:

Xtrabackup writes to the data dictionary file (ibdata1, for example). We have not examined if it also writes to the data and index files (still ibdata1 by default, or the .ibd files when using innodb_file_per_table). [EDIT: The authors of Xtrabackup have commented below as to why the write occurs: …

Shortly after I created the initial packages of embedded InnoDB on the OpenSUSE Build Service, Oracle/Innobase released an updated version (1.0.3.5325). In addition to many improvements and bug fixes, they slightly changed the versioning scheme to better indicate what version of the InnodDB plugin their code is based on (see Vasil's posting on the InnoDB Forums for more information).

I've now updated my InnoDB packages on the Build Service to this version as well - please note that the naming scheme of the shared library package has …

When you write your create table statements, always make sure that you make them non-ambiguous. That way even though other servers might have different configurations, you make sure your table will be created in the same way.
Imagine for instance you are developing an application on a development server, nicely storing all the scripts you need to create the same database on your production server. If the same script creates a table differently on both servers, that might cause you a lot of headache later on. At Open Query, we strive to minimise (or preferrably eliminate) headaches.

One of the parts of the create table statement that has the largest impact is the storage engine specification. When you omit the storage engine from the create table statement, your table is automatically created with the default storage engine type configured for the server. Since the storage engine is a very important choice when designing your tables, …

Oracle/InnoBase announced the availability of the embedded version of InnoDB at this year's MySQL Conference & Expo, but I have not seen a lot of comments or reviews about it so far. Which surprises me, because I think this is a very interesting piece of technology!

In my opinion it might actually hit the sweet spot for application developers seeking an alternative embedded database solution. SQLite is nice and popular, but it seems to have concurrency issues when used in multi-threaded applications. An embedded MySQL …

After a long hiatus, I am happy to announce that mylvmbackup version 0.12 has now been released. This release includes a large number of improvements, minor code cleanups, as well as some new functionality. In particular, I would like to thank Matthew Boehm, Tim Stoop, Baron Schwartz, Ville Skyttä and Ronald Bradford for their contributions.

Some notable highlights from the ChangeLog:

• Removed the absolute path names to external tools (make sure $PATH is correct)
• Added --log-err to the startup options of the recovery instance to avoid cluttering the server's error log
• Added support for hooks written as Perl Modules. (Matthew Boehm)
• Added support for date/time-formatted path names for backupdir and mountdir (Matthew Boehm)
• Backupdir and …

Seen in #maatkit on Freenode:

I never realized just how terrible recovering MyISAM from a crash can be

Sad but true — it can be pretty painful. This is one of the reasons I pretty much recommend InnoDB (okay, okay, XtraDB) for most data unless it’s read-only.

Related posts:

1. Hindsight on a scalable replacement for InnoDB A while ag
2. What is the scalable replacement for InnoDB? A while ba
3. Xtrabackup is for InnoDB tables too, not just XtraDB Just thoug

Related posts brought to you by …

I don’t remember having to do this before, but I had compiled MySQL 5.1.34 recently on my dev box (os x) and I saw a warning on a create table statement. It turns out InnoDB was not enabled (or even listed in the list of storage engines.

Before:

mysql> show engines;
+————+———+———————————————————–+————–+——+————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+————+———+———————————————————–+————–+——+————+
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
…

The ibdata file is too big 10GB, and actually we've only about 2GB (data+index) in innodb storage engine.How we can defragment this file and reduce it?How is this happened?By default the ibdata file created initially by (innodb_data_file_path = ibdata1:10M:autoextend) and auto extended by (innodb_autoextend_increment = 8MB) when it’s needed, and this file (tablespace) contain all innodb tables (

If you use innodb_file_per_table = 1 and innodb_open_files = X (whatever amount is suitable for your server) there’s no way internal to MySQL for finding out how many IBD files InnoDB actually has open. Neither SHOW GLOBAL STATUS LIKE ‘innodb%’ nor SHOW ENGINE INNODB STATUS provide this information.

Many sites do have a growing number of tables, so you’ll want to know when it’s time to up the number (and potentially also open-files-limit). Solution: sudo lsof | grep -c “\.ibd$”

XtraBackup is an Open Source online (non-blockable) backup solution for the InnoDB and XtraDB storage engines. It works with both MySQL 5.0 and 5.1 (and possibly 5.4 as well) and is distributed under the GPLv2.

Some weeks ago Vadim announced the availability of xtrabackup-0.7, stating that they consider it stable enough now to label this version a "Release Candidate". I've been maintaining RPM packages of xtrabackup on the fine openSUSE Build Service for quite some time now, RPMs of 0.7 for a number of distributions are now …