Tuesday, 12 November 2013

Percona Live in 30 seconds

I'm on the train home from London. Stalled at a signal waiting to move on. Here are some thoughts/facts/findings from my past 2.5 days.

- Walking from Waterloo to Kensington can be done. It's not a bad thing after a 3 hour train journey.
- Pubs in London also close at 10.30pm on a Sunday
- Don't trust the wifi near the guy with a pelicase on his shoulder
- Belgians are good peoples that love Indian food.
- Book 60 and 112 will come
- Double Decker buses go the long way around
- The MySQL community is alive
- It matters not what branch/fork you use, free beer/food/bus/OOS unites all
- MariaDB 10 soon. Stay tuned.
- "Peter in a Box" tipped as a hot favourite gift this Christmas.
- Using AWS? Spread yourself across AZ, Regions.
- System security is generally overlooked
- setenforce 1 (learn how to use selinux)
- Sysbench is not as understood as I understand. Lua.
- Henrik will not resist a MySQL conference invite :D
- Common_schema to become part of the server (one day, Shlomi!)
- Galera, PXC remains a hot topic with more and more production installs
- Fractal trees don't lose their leaves in the fall
- Unix load still misunderstood by many. See > 1, check what the CPU and IO is actually doing
- Backups are pretty important, RDBA sets the standard for backup ref. architecture. Mydumper wts.
- Oracle Engineers now available at PL events
- PerconaLive++
- The Crazy Italian guy is still crazy

Now there you have it, you missed a lot if you didn't make it this year!

Wednesday, 23 October 2013

MySQL Community Dinner

Dear Community,

it is my pleasure to announce that there will be a community dinner on the evening of Monday the 11th of November in London coinciding with the Percona Live Conference. In the same spirit as the community dinner in Santa Clara, the event will be `pay your own way` so it's important to bring some cash on the evening so that splitting the bill is as painless as possible.

The venue is Masala Zone in Earl's Court, which is a short walk from the Millenium Gloucester. For anyone that would like to walk over we will meet in the foyer of the hotel at ~7pm and walk over. 

Please make your way over to our eventbrite page to register your attendance so that we can give the guys at Masala Zone good notice of how many to expect.

The link above will take you to the Masala Zone site where you can see what's on the menu. 


Please let me know if you've got any questions in the comments.

Looking forward to seeing you there!

Monday, 14 October 2013

MySQL Replication Filters: replicate-ignore-table & ON DELETE CASCADE

It's no secret that you shouldn't rely on replication filtering. Recently a customer asked if an `ON DELETE CASCADE` would still affect the data on a slave if the table was being ignored through replication filtering. It was one of those occasions that I couldn't give a confident answer without a quick test but alas the gut was right and the obvious answer is yes, "ON DELETE|UPDATE CASCADE" will change data even if you replicate using replication filters.

I tested using RBR and SBR. If anything I was questioning the behaviour of RBR here but it turns out to be consistent with SBR. I had a master-slave setup already deployed for some other testing so it was easy to implement the FKs needed for this. The dataset is the trusty world db available from dev.mysql.com and I needed to change the constraints on the table to match the conditions proposed.


mysql> ALTER TABLE City DROP FOREIGN KEY City_ibfk_1;
Query OK, 5000 rows affected (0.06 sec)
Records: 5000 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE City ADD CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
Query OK, 5000 rows affected (0.06 sec)
Records: 5000 Duplicates: 0 Warnings: 0

mysql> SELECT COUNT(*) FROM City WHERE CountryCode = 'UKR';
| COUNT(*) |
| 57 |
1 row in set (0.00 sec)


mysql> SELECT COUNT(*) FROM City WHERE CountryCode = 'UKR';
| COUNT(*) |
| 57 |
1 row in set (0.00 sec)

So our row counts match. I'll update the my.cnf file for the replication filtering.


cat /etc/my.cnf
replicate-ignore-table = "world.City"

Restart MySQL on the slave and check the status;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_User: repl
Master_Port: 3306
Replicate_Ignore_Table: world.City
1 row in set (0.00 sec)

Filter exists, lets test it. Update a row on the master and compare the values between the instances;


mysql> UPDATE City SET population=90000 WHERE ID = 3482;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM City WHERE ID = 3482;
| id | Name | CountryCode | District | Population |
| 3482 | Mukatševe | UKR | Taka-Karpatia | 90000 |
1 row in set (0.00 sec)

Checking the slave we can see that it remains the old value;


mysql> SELECT * FROM City WHERE ID = 3482;
| ID | Name | CountryCode | District | Population |
| 3482 | Mukatševe | UKR | Taka-Karpatia | 89000 |
1 row in set (0.00 sec)

Now if we delete the parent record in the Country table we can see that the CASCADE affects the 'ignored' table on the Slave too.


mysql> DELETE FROM Country WHERE Code = 'UKR';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT COUNT(*) FROM City WHERE CountryCode = 'UKR';
| COUNT(*) |
| 0 |
1 row in set (0.00 sec)


mysql> SELECT COUNT(*) FROM City WHERE CountryCode = 'UKR';
| COUNT(*) |
| 0 |
1 row in set (0.00 sec)

If you're running with FK Constraints with ON DELETE|UPDATE CASCADE and replication filtering on your slaves you might not have the data you think you have.

Tuesday, 8 October 2013

Bristol MySQL Meetup

The next Bristol (and surrounding areas) MySQL meetup will take place on Thursday 10th of October in Bristol (currently) scheduled for 7.00pm at the Watershed. I say currently because it might be moved if we can find a quieter place. I personally found the Watershed pretty noisy. The topic of the night will be tools. We'll have a 50,000ft overview of some of the tools and then make some time for some demos. According to the RSVP list there's a nice mix of Dev and Ops so there should be a mixture of experience and hopefully some inspirational use cases for tools being used in the wild. Right this moment the agenda of tools will be some from the following;
  • pt-toolkit
  • openark-toolkit
  • Oracle's MySQL Utilities
  • Xtrabackup
  • Mydumper
  • Common_schema
  • AOT
To give you guys and girls further incentive to attend the meetup, we have some swag to unload. There will be some Pythian and Percona t-shirts, some Oracle/MySQL usb drives & stickers and a ticket to Percona Live London to unburden myself of. I'd like to pass out some swag for participation over chance so make sure to bring a tasty question or story about how you use DBA/Dev tools. The Percona Live UK ticket is a very kind donation from Percona, so a HUGE `thank you` goes to Kortney and Tom for arranging that for us. The caveat to bagging swag/ticket: it's limited to attendees only and there will be a photo of the proud owners posted on the meetup page (with you permission of course). We will also have a discount code made available for anyone looking to attend PLUK. Follow the latest news for Percona Live London using the #perconalive hashtag or via Percona.com. Please post any questions or suggestions for Thursday night topic/venue.

Saturday, 31 August 2013

PLUK Attendees: Show of hands

It will soon be November and we'll be queuing up to register for the Percona Live conference once again. I spoke with some of this year's Percona Live London committee we all agreed that it would be great to arrange a community dinner if there was a community interest. In the past years of the PLUK conference there has not been a community event to attend in order to discuss sessions and make new friends. I for one love the `Pythian-arranged Community Dinner` at Pedro's that occurs annually in Santa Clara during the Percona Live Conference. I know that there are many attendees that do London and CA each year and wager that they'd agree that it's a great event. So lets have it... please r.s.v.p here and if the numbers are good enough we'll schedule something the evening of the tutorials. Please let me know of your interest in the comments below. Thanks :D

Saturday, 17 August 2013

Bristol MySQL Meetup & Holland Part II

So just a quick note to express how great it felt to get the first MySQL Meetup in the UK's Bristol done. We had great enthusiastic attendees bringing their own curiosity about the product. We spent a couple of hours in conversation about a broad range of MySQL related topics. Amongst the points discussed were; MySQL Connect, Percona Live London, InnoDB vs. MyISAM, replication, query tuning and Ben gave a demo on pt-query-digest and pt-visual-explain. Although we touched many aspects it was really a primer for what we would like to achieve going forward. We're planning content and dates for the next meetup so keep an eye on the meetup page. I want to express that all comers are welcome whether you're fantastically experienced or a curious noob there's place for you. The group has already made many friends such as TomD from Percona, Stoker from Oracle and Marco from Pythian. They've all donated some sought after swag and we're thinking of fun ways to reward it to our group members.

Holland Part II

Since I penned the first part of the Holland Framework article series I've been immensely busy but things are beginning to slow down. I'll find some time to complete that series for anyone sitting on the edge of their seat awaiting the sequel as this next part was the driver for my blog post. The company I'm working with are currently using Holland in production to make file-per-table mysqldump backups using holland and whilst I would always recommend a mixed backup set (logical and physical) I'm not satisfied in the DR plan using only mysqldump so there's more Holland framework in my immediate future.

That is all from me for now. Hope you have a great weekend.

Friday, 24 May 2013

Holland Backup Manager

Part 1 - Installing Holland Backup Manager

I spoke at Percona Live Conference and Expo 2013 about backups. Part of the talk focussed on the backup products in the ecosystem that will help you make a backup of your MySQL data. This follow-up article touches on one of the frameworks from my talk, the Holland Backup Manager. I was able to have a chat with some of the guys on the Rackspace booth about Holland and had some questions regarding features answered.

Holland is a backup framework focussing mostly on MySQL backups but it is pluggable so you can write add backup providers to extend it to your own needs. Using the framework you're able to configure and deploy backup jobs of varying scope to multiple machines. The framework which was originally developed at Rackspace, currently supports mysqldump, lvm, xtrabackup and pgdump (Postgres) also sqllite. The latest version is number 1.0.8 which brings a few nice new features with it. I'll talk about the most significant of these in the third of the series.
I want to show some installation, configuration and then use of the framework. I'm a fan of this project and if you've got the need to standardise whilst being flexible then Holland could be the solution you've been looking for.

Firstly, head over to github to grab the files. https://github.com/holland-backup/holland
I've been using vagrant to provide a quick VM environment. This OS I'm using here is a CentOS 6.4 64bit host running in Virtualbox. All dependencies for Holland should be available on the popular package managers but if you're using something other then debian (apt-get) or redhat based (yum) then you might require some research elsewhere to meet the requirements of Holland.

Download the zip archive from github (the extension was lost in wget but it is a .zip file)
[vagrant@node1 ~]$ wget https://github.com/holland-backup/holland/archive/master.zip ... Saving to: `master' 100%[==================================================>] 602,186 572K/s in 1.0s (572 KB/s) - `master' saved [602186/602186]

So then proceed to unzip your archive
[vagrant@node1 ~]$ unzip ./master
Archive: ./master
creating: holland-master/
inflating: holland-master/CHANGES.txt
inflating: holland-master/INSTALL
inflating: holland-master/LICENSE
inflating: holland-master/README
creating: holland-master/config/
inflating: holland-master/config/README

ProTip: Take some time to go through the various Holland directories. Throughout the directories there README and INSTALL files that will explain more about the accompanying files.

[vagrant@node1 holland-master]$ ls -l
total 56
-rw-rw-r-- 1 vagrant vagrant 7064 May 13 17:45 CHANGES.txt
drwxrwxr-x 4 vagrant vagrant 4096 May 13 17:45 config
drwxrwxr-x 3 vagrant vagrant 4096 May 13 17:45 contrib
drwxrwxr-x 4 vagrant vagrant 4096 May 13 17:45 docs
drwxrwxr-x 7 vagrant vagrant 4096 May 13 17:45 holland
-rw-rw-r-- 1 vagrant vagrant 1782 May 13 17:45 INSTALL
-rw-rw-r-- 1 vagrant vagrant 3290 May 13 17:45 LICENSE
drwxrwxr-x 13 vagrant vagrant 4096 May 13 17:45 plugins
-rw-rw-r-- 1 vagrant vagrant 514 May 13 17:45 README
-rw-rw-r-- 1 vagrant vagrant 62 May 13 17:45 setup.cfg
-rw-rw-r-- 1 vagrant vagrant 1441 May 13 17:45 setup.py
drwxrwxr-x 2 vagrant vagrant 4096 May 13 17:45 tests
-rwxr-xr-x 1 vagrant vagrant 1782 May 13 17:45 tomsay.py

Before going any further lets take care of the dependencies. They're easily met using yum/apt or if you want to compile them yourself then go right ahead;
  - python-setuptools
  - mysqldb (python's mysql connector)

Now, lets take care of some directories that need to exists. These are configurable later, but for simplicity sake we'll roll forward with the Holland defaults. Your environment is likely to be different especially if you're mounting remote storage to store the backup files you make.

[vagrant@node1 config]$ sudo mkdir -p /etc/holland [vagrant@node1 plugins]$ sudo mkdir -p /var/log/holland [vagrant@node1 plugins]$ sudo mkdir -p /var/spool/holland

Now the directories we've created will only be writable to root since we needed elevated privileges to make them. You might choose to chmod the directories if you're going to use a specific backup user to run your backup jobs. Now we're ready to run the python setup file from the root of the unarchived directory
[vagrant@node1 holland-master]$ sudo python ./setup.py install
running install
running bdist_egg
running egg_info
creating holland.egg-info
writing holland.egg-info/PKG-INFO
Installing holland script to /usr/bin

Installed /usr/lib/python2.6/site-packages/holland-1.0.8-py2.6.egg
Processing dependencies for holland==1.0.8
Finished processing dependencies for holland==1.0.8

Without any errors in the previous output it's safe to assume we're installed.
[vagrant@node1 config]$ holland --version
Holland Backup v1.0.8
Copyright (c) 2008-2010 Rackspace US, Inc.
More info available at http://hollandbackup.org

Lets review what is in the plugin directory. These are the providers. Some are core libraries for the use of particular providers. There's also an example and random directory which we will reference in the next article on config.

[vagrant@node1 plugins]$ ls -w1

There are a couple of essential installs we need to take care of before we're ready to tackle the plugins for the backup tools. These are the holland.lib.common & holland.lib.mysql. For each you need to run the setup.py file from within the directories. The output should look something like the following.

[vagrant@node1]$ cd holland.lib.common/
[vagrant@node1 holland.lib.common]$ sudo python setup.py install
running install
Adding holland.lib.common 1.0.8dev to easy-install.pth file

Installed /usr/lib/python2.6/site-packages/holland.lib.common-1.0.8dev-py2.6.egg
Processing dependencies for holland.lib.common==1.0.8dev
Finished processing dependencies for holland.lib.common==1.0.8dev

[vagrant@node1 holland.lib.common]$ cd ../holland.lib.mysql/
[vagrant@node1 holland.lib.mysql]$ sudo python setup.py install
running install
Adding holland.lib.mysql 1.0.8dev to easy-install.pth file

Installed /usr/lib/python2.6/site-packages/holland.lib.mysql-1.0.8dev-py2.6.egg
Processing dependencies for holland.lib.mysql==1.0.8dev
Finished processing dependencies for holland.lib.mysql==1.0.8dev

Now, I'm mainly interested in mysqldump and xtrabackup but you might want to use the mysql_lvm. I don't think there are many takers for mysqlhotcopy these days. The same steps apply for each provider you want to install on each machine.
[vagrant@node1 holland.backup.mysqldump]$ sudo python setup.py install running install ... Adding holland.backup.mysqldump 1.0.8dev to easy-install.pth file Installed /usr/lib/python2.6/site-packages/holland.backup.mysqldump-1.0.8dev-py2.6.egg Processing dependencies for holland.backup.mysqldump==1.0.8dev Finished processing dependencies for holland.backup.mysqldump==1.0.8dev

and then finally from the xtrabackup directory;
[vagrant@node1 plugins]$ cd holland.backup.xtrabackup/ [vagrant@node1 holland.backup.xtrabackup]$ sudo python setup.py install running install ... Adding holland.backup.xtrabackup 1.0.8dev to easy-install.pth file Installed /usr/lib/python2.6/site-packages/holland.backup.xtrabackup-1.0.8dev-py2.6.egg Processing dependencies for holland.backup.xtrabackup==1.0.8dev Finished processing dependencies for holland.backup.xtrabackup==1.0.8dev

So in summary it's the library files for the plugins;
  - holland.lib.common
  - holland.lib.mysql
  - holland.lib.lvm (depending if you're going to use it)

In part two I'll be giving an overview of the config files and then part three will be some cookbook-like config for the various providers.

Friday, 29 March 2013

Tres Important MySQL stuff in here...

We all know that it's very important to test your backups. It's also quite important to use indexes. It's somewhat important to ensure that your users have only the privileges to do what they need to do. But it's extremely important to make sure you dine with your MySQL friends at Pedros on the 23rd of April 2013. Please goto this page --> MySQL Community Dinner <-- and add your name to the comments.

See you there!

Thursday, 21 March 2013

Yet another Percona Live 2013 Post

It was in doubt but today it was confirmed that I'll be in Santa Clara in April for the MySQL conference hosted by Percona. This will be my 4th Percona conference and I'm delighted to be attending again. The Percona guys put on a great conference, nobody can deny them that. Although I'm not speaking at this year's conference, my esteemed colleagues will be. The recently awarded Oracle Ace for MySQL, Marco Tusa, Danil Zburivsky, Francisco Bordenave and Ben Mildren will be delivering talks and I'm thrilled that Pythian founder, Paul Vallée is also going to be in town to talk about how Pythian are driving down human error. 

I did submit some papers as I was eager to talk at this year's conference. By my own error I submitted some community flavour papers which were not selected for delivery at the conference. I was dis-heartened for a while and began to convince myself that it didn't matter if I miss Percona Live 2013. I planned to get back to the drawing board and produce some killer-prose for the next cfp. I had all but resigned to the fact PL2013 was off. I decided was going to re-focus my annual budget for other extra-curricular activities; maybe on something other then conference. Then it rained down reasons for me to attend, in fact it felt like monsoon season. There was a force unbeknown to me at work. As I stood in my kitchen, washing the dishes and listening to episode 129 of the OurSQL podcast (because that's how I roll) it was entirely about Percona Live; it hit me that I couldn't stave off this year. Shlomi Noach featured in the discussion about the plethora of great topics in the schedule for the week. It was too much, I couldn't handle it. I couldn't listen to the whole show (sorry Sheeri & Gerry!). All of these ideas/events/facts made it impossible for me to abstain from the transatlantic trip in the name of database geekery. 

  • I won a conference pass from Shlomi Noach's blog (thank you, Shlomi)
  • 2013 conference is a day longer then 2012 - that's great value!
  • SO many potentially great talks and tutorials (too many for 1 person to attend)
  • Oracle announced the GA release of 5.6
  • Pythian are sponsors of the event, something that we all wanted in 2012
  • Oracle announce that they are attending and talking!
  • Colleagues I've worked with everyday but have never shared a beer with are attending
  • Ex-colleagues and clients will also be in attendance 

It's an expensive feat to travel to the States for the week, however after entering openark's competition for a ticket to the conference, I was afforded my pass and a catalyst to organise the trip. I spoke with Marco after Shlomi had announced I had won and he re-enforced that it was an important conference to attend. I couldn't disagree with this guy, after all he's usually right ;-) 

As one of the only Pythian-ites not speaking at the event I've promised my team-lead to keep the rest of the team updated, so expect some tweets and blog posts through out the conference. 

So maybe it's destiny, maybe it's just the strong magnetism of MySQL and it's ecosystem but I'm looking forward to the week and seeing all of the great people that make it what it is, both organisers and attendees. 

See you all in approximately a month. Don't forget Pedro's on Tuesday evening for the MySQL community dinner some food and a great chance to mingle with the MySQL community. Download common schema if you didn't already, it's a really great project and if you're attending the conference be sure to attend Shlomi's talk for more info about QueryScript.

See you in April!

Saturday, 5 January 2013


So we made it around the sun again, despite the doubt cast by those pesky Mayans. I can't be angry at a culture that were unable to predict their own demise. I wanted to record some goals for the forthcoming 12 months...
  • Improve time management skills.
  • Learn more about SQL and the relational theory.
  • Formal training on new programming language.
  • Deeper understanding of innodb internals.
  • Deeper understanding of Galera Replication.
Not too many. Realistic.

oh and as per usual - blog more!