Xtrabackup Manager - License changed from GPLv3 to GPLv2

Hi Folks,

I changed the license on Xtrabackup Manager from GPLv3 to GPLv2 today. This should make it more compatible with the MySQL Ecosystem.

If anyone has any objections, advice, comments -- please let them fly :)

Meanwhile, testing and work continue...

Cheers,
Lachlan

Xtrabackup Manager - A new home and some more progress...

Hi Folks,

The Xtrabackup Manager project now has a new home on Google Code. Including tasks/to-dos, etc.

You can find the project here:

  http://code.google.com/p/xtrabackup-manager/

If you haven't heard about it yet, the project aims to provide a nice backup management wrapper to the popular "xtrabackup" tool from Percona.

At the moment you can set it up and it will automagically detect when to take a FULL backup (first) and then when to take incremental backups following that. It will even collapse your old incrementals into your FULL backup (seed) based on your snapshot retention policy.

So functionality is coming along nicely!...

I have also started work on some documentation -- it is still a work in progress, but shortly there will be enough there for the more experimental among you to try it out.

I'll make another post once that is ready.

Interesting in contributing? Please reach out to me and let me know. I'm interested in finding someone who may like to develop a Web Front-end for it in PHP.

Until then, I will keep plugging away as I find the time.

Cheers,
Lachlan

OSS Project hosting woes of a first-timer

Being a long time OSS Project user/supporter but not having setup or developed any of my own projects previously, I'm having to navigate unchartered territory with regards to how and where to setup my projects.

At first I looked at launchpad.net because I'd seen a bunch of other projects using it, however, when I discovered they didn't have many of the supporting features I wanted - mainly wiki, I decided to look elsewhere.

It was then I checked out good ol' sourceforge.net (apparently I'm still living in the past, because someone commented to me the other day something like "Oh.. you mean sourceforget?") So I guess even geeks have stuff that's "in" and stuff that's "out" ;-)

Anywho.. I liked what I saw on SF.net, it seemed fairly easy to setup and had a lot of features that seemed like they would be useful for a project. So I setup the project on sourceforge(t).net and continued my work.

About 2 revision commits in I started thinking the project was getting to a point that it could be used - so of course I needed to make some quick and dirty documentation about how to use and set it up.

It was then that I came to realise my mistake -- I was apparently so blinded by the various _other_ features on SF.net, that I neglected to notice that they don't have a wiki space provided. Sure, I could set one up in the web hosting space, but I want to spend my time on actually working on the project, not managing the infrastructure around it. (It's a nice way of saying I'm lazy..)

So now I'm thinking about Google Code and Github.

I went to Github and went to check out some of the project hosting pages. Honestly, I think I touched git a long time ago in a galaxy far far away and any information other than it's name has long since evacuated my brain. 

The first thing I did was try to browse through some of the existing projects -- How does it feel to browse? Can I find the things I would want to find easily?

The answer was "No." most of the time I couldn't tell if I was in a project or a branch or a fork or whatever and I struggled to find an easy link for wiki or documentation or even downloads. Github might be a great tool if I knew git and knew what I was doing, but if I imagine a DBA like myself coming to the page to find my tool and use it, they would likely feel an equal level of confusion. They don't necessarily want the source code or to contribute, they want to download and use it.

So for now Github is out.

Then came Google Code - It seemed like a breath of fresh air. I could see clear links to Source, Downloads, Wiki and Issues ( bugs ). Yes, this would do just fine. The problem then became that when I logged in with my gmail account, I saw that it was not my display name that came showing up against the project, but my gmail account ID. 

Ewww... 

Call me vain, but I like putting my real name against things that I do in the community. I don't want people to have to learn that "arzkl123@gmail.com" is actually Lachlan Mulcahy. (That's not my gmail by the way -- I'm attempting to avoid spam here). 

A quick google search and it seems this issue is something people have complained about since 2008 sometime. I guess it is not going to get changed anytime soon, so I'll just go any make myself a gmail account with my real name in it more visibly and use that.

It is a bit of a pain, but out of the available options, it seems like the best choice.

Now I'll hop off the soapbox and get back to work :)

Cheers,

Lachlan

Xtrabackup Manager - A toolset for making managing backups using xtrabackup simple!

Hi Folks,

Long time, no post -- I know, but I'll cut to the chase.

I have started work on a new GPL project entitled "Xtrabackup Manager". Just to be clear, the only relationship to Percona is that the toolset is designed to wrap around the Percona xtrabackup tool.

It is essentially a wrapper for xtrabackup that allows you to more easily manage and schedule your backups for multiple systems.

It is being written in PHP and the work-in-progress code is up on sourceforge. Currently it is not really near ready for general consumption as it is still under construction, but I'm trying to be a good OSS citizen and follow the "release early, release often" method.

A few things to note and disclaim:

#1. I am not the world's greatest developer, so please when you critique my code, be gentle ;-)
#2. It is written in PHP because it is the scripting language I am most familiar with. Not necessarily because it is the best tool for the job ( although I think it should do the job just fine ! )

Some info/features on what I have planned:

* Will run on Linux only to start with - so far have been testing on CentOS 5.5
* Setup any number of hosts and configure backup times using a cron expression (don't reinvent the wheel for scheduling)
* Give the tool a Linux user of it's own, it will hijack the crontab for scheduling
* Uses SSH trust as a means of running backups
* Uses tar stream and netcat for pulling backups over the network into the backup host
* Configure how many snapshots you wish to retain - utilizes full backup and incremental backup feature of the xtrabackup too for this. Automatically merges snapshots together as you roll forward with more snapshots.
* Support for multiple storage volumes -- all incrementals and seed must live on the same volume.
* Rich logging: Mutliple log levels, DEBUG/INFO/ERROR - Global system log and per host log files.
* Email alerting / reporting - Get alerted when backups fail. Get reports of what backups ran/when, etc.
* Requires a small MySQL instance for metadata, and management storage.
* Command-line and DB interface for configuration to start with.

Want to get involved? I'm looking for anyone who may be interested in developing a web front-end to the tool. I'm a decent hand with PHP, but I have not developed anything web related in quite some time.

Leave a blog comment or reach out to me on lmulcahy (at) marinsoftware (dot) com if you are interested!

Right now I am developing this for use in-house at the company I work for, so my focus is towards getting something working that we can begin using here.

I'm of course hoping that this will be a chance to give something back to the MySQL community and that others can benefit.

Please let me know your thoughts/feedback.

Lachlan

HowTo: xtrabackup directly to target host, no additional space for archive file needed

xtrabackup is a great tool for taking backups/snapshots, etc. and sometimes we have large amounts of data to deal with and not enough storage to mess around with.

The xtrabackup docs contain steps for how to stream your backup over the network to another host, which is fine if the end result you want is a tar/gzip type archive, however, in some cases you may want to just get the files to the other host unextracted in order to create a new slave DB.

In this case you just want to get that snapshot into the new host as easily as possible -- in many cases I don't have enough storage to first put it into a tar or tar.gz and then extract.

To work around that, here is a way you can stream your backup over the network straight onto disk on the other side, while avoiding the need for an archive file as a stepping stone in the process.

Note: My bash-fu is probably not as advanced as some, so perhaps there is a more elegant way to make this fly, but it seems to work just fine for me.

ssh root@target-host "cd /data/target-dir; nc -l 9210 | tar xvif - " & sleep 1; \
innobackupex-1.5.1 --stream=tar /datadir/path --user=root --password=XXXXX\
   --slave-info | nc target-host 9210

Once you are done, remember you still need to --apply-log before the snapshot can be used.

Hopefully this will save someone else a few minutes.

Lachlan

ON DUPLICATE KEY UPDATE Gotcha!

I know it has been a long time between drinks/posts, but I've been busy -- I promise! :)

Today I spent a considerable amount of time trying to figure out why an INSERT SELECT ON DUPLICATE KEY UPDATE was not behaving as I would expect.

Here is an example to illustrate:

CREATE TABLE t1 (
  id INT AUTO_INCREMENT,
  num INT NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

CREATE TABLE t2 (
  id INT NOT NULL,
  num INT
);

INSERT INTO t1 VALUES (1, 10);
INSERT INTO t2 VALUES (1, NULL);


INSERT INTO t1
  SELECT id, num
  FROM t2
ON DUPLICATE KEY UPDATE
  num=IFNULL(VALUES(num), t1.num);

To convert the above query into plain English -- I'm saying, INSERT into table t1 the id and num fields from the t2 table. If there are already row(s) for any UNIQUE key in the target table, t1, then we should instead UPDATE the existing row. Additionally, we should set the num field to the result of whatever this evaluates to:

IFNULL( VALUES(num), t1.num)

To explain: VALUES(num) means "The value that is to be placed into the "num" field when it is updated."

So we are saying, if a NULL is going to be put into the field "num" then we want to leave the value alone -- set it to "t1.num" -- eg. the value that is already there.

One might expect the result of my query to be as follows:


testDB:test> SELECT * FROM t1;
+----+-----+
| id | num |
+----+-----+
|  1 |  10 |
+----+-----+
1 row in set (0.00 sec)

However, that is not the case -- the actual result is:

testDB:test> SELECT * FROM t1;
+----+-----+
| id | num |
+----+-----+
|  1 |   0 |
+----+-----+
1 row in set (0.00 sec)


Why is this the case?

The hint lies in the result of the INSERT itself:


dbp16-int:test> INSERT INTO t1   SELECT id, num   FROM t2 ON DUPLICATE KEY UPDATE   num=IFNULL(VALUES(num), t1.num);
Query OK, 0 rows affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 1

Note: 1 warning...

testDB:test> SHOW WARNINGS;
+---------+------+-----------------------------+
| Level   | Code | Message                     |
+---------+------+-----------------------------+
| Warning | 1048 | Column 'num' cannot be null |
+---------+------+-----------------------------+
1 row in set (0.00 sec)

What is actually happening here is that because the column 'num' in the table t1 is defined as NOT NULL, MySQL is silently converting it to a valid value of 0.

So VALUES(num) actually equals 0, thus, it will not evaluate as NULL and the 0 will be INSERTed into the table.

This was not my intention and the solution in this case was to allow NULLs on the "num" field of the table t1. It may not always be possible to remove such restrictions if you rely on these automatic conversions by MySQL to "valid values".

Something to keep in mind - VALUES() will always evaluate as what would have ended up in the table, which may not necessarily be the same thing as the value that was attempted to be INSERTed.

replicate-do-db gotcha!

Last weekend we went live with a change where we split one of our central user databases off into a master-master replication pair, with HA and a virtual IP/hostname to point our apps to. It had been previously hosted on one of the shards, so it was time to give it a life of its own as well as some redundancy.

On Friday we did a test of the change in our staging environment.

After setting up the master-master pair, the first thing we verified was writes/table creates, etc. were flowing in both directions for the database we planned to replicate between the two. Lets just call it "db1".

To enable this I had used the option: replicate-do-db=db1 in the my.cnf on both DB machines.

After we verified this, I was told that we should probably also replicate "db2". I edited the config on both machines and changed the option to be replicate-db-db=db1,db2

We then proceeded to take a snapshot of our staging environment for these dbs so that we could test failover, app performance, etc.

We restored the snapshot, brought up the app and everything looked fine. We tested failovers. All looked good.

The change was pushed into production over the weekend and on Sunday night a network glitch caused the HA to failover to make the second DB in the pair active.  Then late Monday evening someone noticed that changes didn't actually seem to be replicating between the two, although SHOW SLAVE STATUS reported IO and SQL threads running and everything was caught up.

Enter the culprit - my replicate-do-db setting

The correct way to tell MySQL to enable replication for select DBs is to issue the parameter multiple times:

replicate-do-db=db1
replicate-do-db=db2

The configuration as I had it was actually telling MySQL to filter and only replicate a database called "db1,db2" which of course did not exist.

Despite the fact we thought that we'd been good at testing, this managed to slip through.

I guess even someone who has been using MySQL for almost 10 years can make silly mistakes. Whoops!

Here's hoping that someone else out there can learn from my mistake!

Lachlan