Percona XtraDB Cluster (PXC) is different enough from async replication that it can be a bit of a puzzle how to do things the Galera way.  This post will attempt to illustrate the basics of setting up 2 node PXC cluster from scratch.

Requirements

Two servers (could be VMs) that can talk to each other.  I’m using CentOS for this post.  Here’s a dirt-simple Vagrant setup: https://github.com/jayjanssen/two_centos_nodes to make this easy (on Virtualbox).

These servers are talking over the 192.168.70.0/24 internal network for our example.

jayj@~/Src $ git clone https://github.com/jayjanssen/two_centos_nodes.git
jayj@~/Src $ cd two_centos_nodes
jayj@~/Src/two_centos_nodes $ vagrant up
 Bringing machine 'node1' up …

Introduction   For a long time there was a Bug#6295 in implementation of BEFORE triggers related to handling of NOT NULL column. The problem was that if a column is declared as NOT NULL, it wasn’t possible to do INSERT NULL (or UPDATE to NULL) even though there was associated trigger, setting NOT-NULL value.

For example:

• There is the table ‘t1′ with a NOT NULL column ‘c1′
• The table has BEFORE INSERT trigger which sets the ‘c1′ column to NOT NULL value (SET NEW.c1 = 1)
• User executes the SQL statement INSERT INTO t1 VALUES(NULL) that fails with the following error:     ERROR 1048 (23000): Column ‘c1′ cannot be null
• The user will get the same error if there is a BEFORE UPDATE trigger that sets the ‘c1′ column to NOT NULL …

As a leader of a technical operations team I often have to work on technical operations engineer hiring. This process involves a lot of interviews with candidates and during those interviews along with many challenging practical questions I really love to ask questions like “What are the most important resources you think an Operations Engineer should follow?”, “What books in your opinion are must-read for a techops engineer?” or “Who are your personal heroes in IT community?”. Those questions often give me a lot of information about candidates, their experience, who they are looking up to in the community, what they are interested in, and if they are actively working on improving their professional level.

Recently, one of the candidates asked me to share my lists with him and I thought this information could be valuable to other people so I have decided to share it here on my blog.

Must-Read Books List

First …

Today, while at Oracle's MySQL Connect conference in San Francisco, I had the opportunity to talk to Gerardo Narvaja and that inspired me to try using OQGraphv3 and TokuDB. Of course, the first challenge is to compile it on my MacBook which has MacOS Lion 10.7. I installed the Apple's latest XCode Command Line tools and eventually compiled a MariaDB which had both TokuDB and OQGraph available

Introduction
Having written an application to work with an unsharded MySQL setup, how do we migrate the application to a sharded setup ?

The above problem can be divided into two parts

• Migrating the MySQL Store (with minimum disruption to ongoing transactions)
• Migrating the Application.

This blog deals with the first problem of migrating the MySQL Store. The second problem of migrating the application will be handled in a separate blog post, and we shall assume for now that we just replace the old unsharded non-Fabric aware application with the new Fabric aware application.


  Most enterprises start with a single server setup to develop a prototype and later realize that they need to scale. This …

Day one of MySQL Connect day 1 was a great day of technical information.

Hands-On Lab

Tomas gave another stunning keynote that covered 5.6, 5.7 and covered new innovations like the Fabric sharding software.

I sat in on a hands-on session to learn the tricks of how to run explain on another process.

Davi from Linkedin gave an amazing talk on InnoDB internals that I missed due to being in a great session on Fabric.

The evening reception was a great chance to see old friends, make new ones, and enjoy the MySQL Community.

…

In the previous post, we had a look at the Performance Schema tables to monitor MySQL replication. We discussed why these were needed, what advantages they have to offer and what each table stands for. We also did a small exercise to see how one can select a particular table given the data that is needed. We will now dive deeper and look at the individual tables to understand what fields they are made of. We will also go through different scenarios to see how the different pieces of data can be used.

Note that these tables report the status/configuration of a replication slave. So, these are empty at the master server or at a standalone server. All the SELECTs we will talk of, are done at the slave. Lets take the tables one by one and go through them.

…

MySQL Fabric was officially introduced during Tomas his keynote at MySQL Connect 2013. MySQL Fabric will help you managing lots of MySQL server supporting both high-availability and sharding. Just like Connector/J, MySQL Connector/Python v1.1.1 Alpha has support for MySQL Fabric on MySQL Labs. Note that the MySQL Fabric as well as support for it in Connector/Python is Alpha.

When you download MySQL Connector/Python from MySQL Labs, you will also see a ZIP archive containing the MySQL Utilities documentation …

Yet another MySQL DMR (5.7.2) is out and here is a short update on the testing improvements in this release.

• Test suite migration
  • Default storage engine in mysql-test-run.pl (MTR) changed from MyISAM to innodb.
  • Migrated parts, sys_vars, perfschema, funcs_1, funcs_2 and opt_trace  suites to run with innodb. 
  •   MyISAM variants retained for engine dependent tests.
  • Suites that are not yet migrated continue to run with MyISAM using include file force_myisam_default.inc.
• All new features qualified as per the process described at http://anithagopi.blogspot.in/2013/05/new-feature-qualification.html
•  Around 150 new MTR tests added already to 5.7
• Code Coverage at …

In this article we discuss a use case for starting with a single server (single shard setup) and scaling gradually as the application and its data grows. We also describe here the different aspects of creating shards using MySQL Fabric and what each of it means.

Note: Unlike the previous example the present example is not working with data that already exists.

Note: The example also covers how to modify the python application code to work with Fabric.
Prototyping the Application on a Single MySQL Shard Setup
The figure below depicts the difference in the initial setup between using and not using MySQL Fabric.
  Note: For the initial setup the Global Group can contain just another MySQL Server running on the same machine.


…