Kolbe Kegel's Blog

October 02, 2014

How to get MySQL 5.6 parallel replication and Percona XtraBackup to play nice together

MySQL 5.6 introduces a new "multi-threaded slave" feature (aka "parallel replication") that turns the "slave SQL thread" into a number of distinct threads that each apply events to different schemas. This means that separate slave "worker threads" will be executing different positions in the slave's relay log (corresponding, of course, to different positions in the master's binary log) and that the Relay_Master_Log_File and Exec_Master_Log_Pos values in SHOW SLAVE STATUS output are no longer an accurate indicator of the current position of the slave. This causes severe problems if using Percona XtraBackup/innobackupex with the --slave-info option to get a backup that can be used to seed a new slave. Let's take a look at how multi-threaded slave works in MySQL 5.6, how innobackupex gets position information, how to recognize problems, and how to work around them to get a consistent backup that can be used for seeding a new slave.

Read More

June 12, 2014

RHEL7 & the transition from MySQL to MariaDB: A first look.

Red Hat Enterprise Linux 7 was declared GA on Tuesday, more than 3.5 years after the release of RHEL 6. A big piece of news with this release is that it includes MariaDB Server instead of Oracle MySQL as "the default implementation of MySQL in Red Hat Enterprise Linux 7". What does this mean? When you need to install a "MySQL server", the one included in the RHEL7 repositories, and the one officially supported by Red Hat, will be MariaDB. This is great news for the MariaDB team and a testament to the incredibly hard work they've done in the areas of security, stability, performance, and openness. But what does it mean for users of RHEL7? Let's take a look.

Read More

June 03, 2014

Using mysql_embedded and mysqld --bootstrap to tinker with privilege tables

A couple weeks ago, I started wondering about some alternative approaches to resetting the MySQL or MariaDB password. The tried-and-true methods of --skip-grant-tables and --init-file are fine, but I thought there must be some more "simple" (in terms of fewer steps at least) and more programmatic way. It occurred to me that it should be possible to use the embedded server (libmysqld) to accomplish this task. In this post, I'll look at how to use the mysql_embedded tool to inspect and modify grant tables, as well as how to use the --bootstrap option to mysqld to do the same.

Read More

March 27, 2014

WebScaleSQL! Will it build?

There was an exciting announcement today about WebScaleSQL, the new "branch" (not a fork, they say!) of MySQL created by folks from MySQL engineering teams at Facebook, LinkedIn, and Twitter. They've collaborated to bring together improvements to MySQL that they've found to be useful for the gigantic scale they work with. WebScaleSQL is based on MySQL 5.6.16 and building it is very easy.

Read More

March 25, 2014

Get rid of orphaned InnoDB temporary tables, the right way

If InnoDB or XtraDB in MySQL or MariaDB are interrupted while performing an ALTER TABLE, they'll leave a temporary table sitting in your data directory. This thing will be taking up space, which is no good. If you're using innodb-file-per-table, or course, you can solve that problem by removing the .frm and .ibd files for the table.

Read More

February 17, 2014

MySQL to MariaDB migration: handling privilege table differences when using mysqldump

Migrating from MySQL to MariaDB is generally a straightforward procedure: you can shut down MySQL, install MariaDB, and start it up with a very good chance of success. When upgrading/crossgrading/migrating from one release of MySQL or MariaDB to another, it is important to run mysql_upgrade. The same is true when migrating from MySQL to MariaDB, in part because MariaDB has some different definitions for certain privilege tables.

Read More

February 12, 2014

The Sign: row-based binary logging and integer signedness in MySQL and MariaDB

An interesting question appeared in #mysql on Freenode recently. A user was trying to build a client that would act as a replication slave so that it could consume row-based replication events coming via the replication stream, in order to transform them into JSON that could be sent to ElasticSearch. Neat idea! But he ran into a problem: it's not possible to tell whether integer values in the replication stream are signed or unsigned. Let's take a look at how that works. 

Read More

September 17, 2013

A hash-based GROUP BY strategy for MySQL

Sometimes MySQL just doesn't choose the most efficient way to execute a query.GROUP BY is a good example. A customer recently wanted to add a unique key over some columns of a large (~50GB) table, and they first had to find duplicates. In this case, no suitable index was available that would help with the query. That means "Using temporary".

Read More