Tue, 2014-07-29 10:00mriddoch

Part 1 of this blog post told the story of creating a binlog router for MaxScale that could connect to a MySQL Replication Master, download the binlog file from that master and store them locally on the MaxScale server. This post will concentrate on the other side of the router, the interaction with the MySQL slaves that will see MaxScale as the replication master.

Router Structure

In some ways the role of master for MaxScale is much closer to the more expected style of interaction that MaxScale was designed to deliver; a connection originates from a client to a MaxScale service. MaxScale then processes that requirement and returns a result back to the client. The most obvious difference is of course that the processing does not involve forwarding the request on to another server, …

In the past I have used pt-slave-delay when I want to maintain an intentionally delayed replica. Now that I have upgraded to MySQL 5.6 I am switching over to use MASTER_DELAY, which is a built-in feature that does the same thing.

For example I can replace this:

``` pt-slave-delay --delay 7d --interval 1m --daemonize

```

With this:

STOP SLAVE; CHANGE MASTER TO MASTER_DELAY = 604800; START SLAVE;

The implementation is similar: the IO thread copies the events to the relay log as fast as normal, but the SQL thread only executes events older than the defined lag. The process to …

Using GTID to attach an asynchronous Slave sounds promising. Lets have a look at the two existing GTID implementations and their integration with Galera.

GTID@MariaDB

There is one GTID used by the cluster and every node increments the common seqno by itself. This works well as long all transactions are replicated by Galera (simplified InnoDB). Because Galera takes care of the Commit Order of the transactions on all nodes. So having identical GTID/seqno from the start there are no problems.

  node1> show global variables like 'gtid_binlog_pos';
  +-----------------+---------+
  | Variable_name   | Value   |
  +-----------------+---------+
  | gtid_binlog_pos | 0-1-504 |
  +-----------------+---------+

  node2> show global variables like 'gtid_binlog_pos';
  +-----------------+---------+
  | Variable_name   | Value   |
  +-----------------+---------+
  | gtid_binlog_pos | 0-1-504 | …

Part 1 of this blog post told the story of creating a binlog router for MaxScale that could connect to a MySQL Replication Master, download binlog file from that master and store them locally on the MaxScale server. This post will concentrate on the other side of the router, the interaction with the MySQL slaves that will see MaxScale as the replication master.Router StructureIn some ways the role of master for MaxScale is much closer to the more expected style of interaction that MaxScale was designed to deliver; a connections originates from a client to a MaxScale service. MaxScale then processes that requirement and returns a result back to the client. The most obvious difference is of course that the processing does not involve forwarding the request on to another server, rather it involves sending …

Mon, 2014-07-28 13:52adonnison

About 4 years ago, the guys at DeNA created the HandlerSocket plugin for MySQL. In Yoshinori Matsunobu's blog, he benchmarks HandlerSocket as more than 7 times the throughput of using the standard libmysql, and nearly twice that of a memcache front end.

So what is HandlerSocket? It is a plugin that bypasses the SQL layer and therefore misses out on the overhead required to parse the SQL. This does mean that you don't have access to SQL statements, but it does provide CRUD (Create, Read, Update, Delete) operations that effectively gives you a NoSQL access to your database.

HandlerSocket, when started, creates a set of listener processes that wait for connections. Two separate TCP ports are …

Galera the synchronous Master-Master replication is quite popular. It is used by Percona XtraDB Cluster, MariaDB Galera Cluster and even patched MySQL binaries exist. Quite often you want to add a slave to a Galera Cluster. This is going to work quite well. All you need is at least configure log_bin, log_slave_updates and server_id on the designated Masters and attach your Slave.

GTID@MariaDB

Even you can use traditional (non GTID) replication. Using non GTID replication is a hassle. As you need to search for the right offset on the new Master to attach your Slave on.

Using GTID promises to be easier. As you simply switch to the new Master and the replication framework finds the new position based on the GTiD automatically.

As a fact we have two GTID implementations

• GTID@MySQL/Percona
• GTID@MariaDB

…

Hi, I recently had the experience of assisting with a migration of a customer MySQL installation to Amazon RDS (Relational Database Service). Amazon RDS is a great platform for hosting your MySQL installation and offers the following list of pros and cons:

• You can scale your CPU, IOPS, and storage space separately by using Amazon RDS. Otherwise you need to take downtime and upgrade physical components of a rack-mounted server.
• Backups, software version patching, failure detection, and (some) recovery is automated with Amazon RDS.
• You lose shell access to your DB instance
• You lose SUPER privilege for regular users. Many SUPER-type statements and commands are provided for as a Stored Procedure.
• It is easy to set up multiple read replicas (slaves in READ_ONLY=1 mode).
• You can set up a secondary sychronous …

Mon, 2014-07-28 10:22claudionanni

MariaDB 10.0 has introduced the functionality to replicate data from more than one Master server. This is what many have been waiting for, especially those who do business intelligence analysis, aggregation and reporting on data coming from different and sometimes related applications.

The way multi-source replication is implemented is extremely simple - for every Master you need what is called a 'connection'. Previously you just had one, the default. Each 'connection' points to a Master and it has, just like regular replication, two threads: IO Thread and SQL Thread. This means that if you have two different Masters using the same Schema name, the two 'connections' associated with the separate Masters would operate on the same Schema on the Slave (specifically the Slave SQL Thread of both connections would work in the same schema). Moreover should the two source schemas contain tables with the …

A bit of history

The latest version of Red Hat Enterprise Linux, one of the most popular and respected Linux distributions in the server market, was released in June 2014, followed by CentOS 7 and Oracle Linux releases in July of the same year.

There are very interesting changes for database administrators in these new releases, among which I would like to highlight the fact that installer now chooses XFS as its filesystem by default, which substitutes ext4 as the preferred format for local data storage. Red …

This is the story of cold blocks and mismatched instances and how they will cause you pain and cost you money until you understand why. Most of the clients that we support run on the Amazon cloud using either RDS … Continue reading →