Introducing Consul

I’m always interested in what Mitchell Hashimoto and Hashicorp are up to, I typically find their projects valuable.  If you’ve heard of Vagrant, you know their work.

I recently became interested in a newer project they have called ‘Consul‘.  Consul is a bit hard to describe.  It is (in part):

• Highly consistent metadata store (a bit like Zookeeeper)
• A monitoring system (lightweight Nagios)
• A service discovery system, both DNS and HTTP-based. (think of something like haproxy, but instead of tcp load balancing, it provides dns lookups with healthy services)

What this has to do with Percona XtraDB Cluster

I’ve had some more complex testing for  …

With MySQL replication, when using the UUID() function to insert or update values in your database you need to be careful to assure the UUIDs are replicated properly. This is primarily a problem in statement-based replication.

Here's a simple example with row-based replication to illustrate UUIDs replicating successfully:

Master

``` master > set binlog_format = 'ROW'; Query OK, 0 rows affected (0.00 sec)

master > insert into uuid_test (binlog_format,uuid_string) values(@@binlog_format,uuid()); Query OK, 1 row affected (0.00 sec)

master > select id,binlog_format,uuid_string,md5(uuid_string) from uuid_test; +----+---------------+--------------------------------------+----------------------------------+ | id | binlog_format | uuid_string | md5(uuid_string) | …

As of MySQL 5.5.41, released on November 28 (last week), Oracle has fixed MySQL Bug 67433, which I filed on October 31, 2012 and wrote about 4 months ago in Stupid and dangerous: SET GLOBAL sql_log_bin. You can see the fix in Launchpad revision 4718.

The MySQL 5.5.41 release notes mention:

Replication: The global scope for the sql_log_bin system variable has been deprecated, and this variable can now be set with session scope only. The statement SET GLOBAL SQL_LOG_BIN now produces an error. It remains possible for now to read the global value of sql_log_bin, but you …

Since we launched the official MySQL repos for Linux a little over a year ago, the offering has grown steadily. Starting off with support for the Yum based family of Red Hat/Fedora/Oracle Linux, we added Apt repos for Debian and Ubuntu in late spring, and throughout all of this, we have been continuously adding more and more MySQL products […]

MySQL Cluster NDB is a transaction engine and allows multi-master for write operations.  One may consider the architecture using other storage engine such as INNODB as slave server to provide the historical database.  This serves the analytic applications with historical data.  The master - slave topology allows scale out for both Master and Slave Servers.

In order to achieve the MySQL Cluster Replication between NDB and other storage engine together with providing historical data,
1. Setup the MySQL Cluster Replication
2. Disable binary logging for data deletion process - Ensure data removal / house keeping process on transaction tables in the MySQL Cluster not to propagate to the slaves.  Otherwise, data from slave servers will also be cleaned up.

Setup the MySQL Cluster Replication
a. With a running MySQL Cluster, management node should be running.   Attaching …

In my last post I covered how to use the query rewrite framework to write your own pre-parse plugin. The interface is simplistic: a string goes in, a string comes out, and the rest is up to your plugin’s internal workings. It doesn’t interact that much with the server. Today I am going to show you the other type of plugins that rewrite queries, post-parse query rewrite plugins. This type is, out of necessity, more tightly coupled with the server; it operates on the internal data structures that make up the query’s parse tree.

Creating the Plugin

Declaring the plugin is similar to declaring a pre-parse plugin: you declare the plugin in the usual way but with the addition of a specific plugin descriptor for the post-parse query rewrite plugin type. This is a struct, as usual:

struct …

One new feature in Percona XtraDB Cluster (PXC) in recent releases was the inclusion of the ability for an existing cluster to auto-bootstrap after an all-node-down event.  Suppose you lose power on all nodes simultaneously or something else similar happens to your cluster. Traditionally, this meant manually re-bootstrapping the cluster, but not any more.

How it works

Given the above all-down situation, if all nodes are able to restart and see each other such that they all agree what the state was and that all nodes have returned, then the nodes will make a decision that it is safe for them to recover PRIMARY state as a whole.

This requires:

• All nodes went down hard — that is; a kill -9, kernel panic, server power failure, or similar event
• All nodes from the last PRIMARY component are restarted …

In the MySQL team, we are currently discussing deprecating the ability to change the collation_database and character_set_database settings. As part of our plan, mysql clients will still be able to access these variables, but they will be read-only.

Introduction

The MySQL manual summarizes these two variables as saying:

This option is dynamic, but only the server should set this information. You should not set the value of this variable manually.

This is to say that usage is already discouraged, but these two variables originally had a purpose of allowing a DBA to change the default character-set temporarily for the current session only. For example:

SET SESSION character_set_database=latin5;
LOAD DATA infile 'foo.txt' ... ;
-- foo.txt is interpreted as if it was in latin5 …

Tue, 2014-12-02 12:19anatoliydimitrov

Puppet is a powerful automation tool that helps administrators manage complex server setups centrally. You can use Puppet to manage MariaDB — let's see how.

With Puppet, you describe system states that you want the Puppet master server [to enforce] on the managed nodes. If you don't have Puppet installed and configured yet, please check the official Puppet documentation.

Before you can use Puppet to manage MariaDB, you must install a Puppet module that sets the proper repository corresponding to your operating system and version of MariaDB. For Red Hat-based distros, including CentOS, you can use the …

This post is a follow-up to my November 19 webinar, “Tips from the Trenches: A Guide to Preventing Downtime for the Over-Extended DBA,” during which I described some of the most common reasons DBAs experience avoidable downtime. The session was aimed at the “over-stretched DBA,” identified as the MySQL DBA short of time or an engineer of another discipline without the depth of the MySQL system. The over-stretched DBA may be prone to making fundamental mistakes that cause downtime through poor response time, operations that cause blocking on important data or administrative mishaps through the lack of best practice monitoring and alerting. (You can download my slides and view the recorded webinar here.)

Monitor the things
One of the aides to keeping the system up and …