The latest from the MySQL community

Recently, we experimented with active/passive clustering of MySQL via Corosync and Pacemaker.  While many choose to use DRBD, our requirement was to simply have storage fail over to the other node.  In the real world, this would likely be a SAN.  For our POC, we chose to use NFS.

This document will provide a high level overview of a redundant and highly-available clustering solution for MySQL in an active/passive clustering configuration. While there are many ways to make MySQL highly available each has its strengths and weaknesses. These will be explained herein.

Components of the Solution
This solution requires only freely available Open Source components. Each is depicted in the diagram below:

 Two server nodes (virtual servers or physical servers)
 Virtual IP (VIP)
 Shared Storage such as a SAN with filesystem which can be mounted on either node
 …

We manage hundreds of servers and have a need to add and remove DBAs, application developers, and so on regularly. Doing this manually is just beyond the scope of what we can realistically manage. Since we are already using LDAP, we wanted to find a way to integrate it with MySQL.

After reading many, many articles and getting diverse opinions as to whether or not it was even possible, we decided to give it a try. From what we read, it appears that MySQL Enterprise edition does exactly what we want; unfortunately, we don’t have an Enterprise support contract ruling that out as an option. Knowing that Percona tends to mimic many of the Enterprise features, we decided to pursue doing this with Percona Server. Further reading showed this could be possible with the PAM plugin which would then authenticate via LDAP. A little indirect, but seemed like our only solution.

Since MySQL does not provide a community version of its LDAP …

With the imminent release of ProxySQL as stable version, and its presentation at PerconaLive Amsterdam , I decide to write few blog posts to describe how to install, configure and use it without digging too much through documentation.
Moreover, practical examples are often easier to understand.

This tutorial is a long one, although none of the topics are discussed in details to avoid making it even longer (or boring).

For this tutorial, we will use a small setup with 4 nodes:
- node1 (192.168.124.101) , mysql master
- node2 (192.168.124.102) , mysql slave
- node3 (192.168.124.103) , mysql slave
- app (192.168.124.104) , application server where proxysql will also run


Replication setup
Without going into …

In our previous post, we showed you how to interpret reports generated by pt-query-digest. Today we’d like to cover some of its more advanced features, as it is a pretty extensive tool with lots of functionality. We’ll also show you what information you should be looking for, and how to derive conclusions based on that data. 

This is the eleventh installment in the ‘Become a MySQL DBA’ blog series. Our previous posts in the DBA series include Analyzing SQL Workload with pt-query-digest, Query Tuning Process, …

MySQL sys version 1.5.0 has just been released.

It has significant contributions from both external contributors (thank you Daniël and Shlomi) and internal (thank you Jesper and Morgan). Jesper particularly made many significant improvements in this release.

Here’s a full summary of the changes:

Improvements

• The `format_bytes` function now shows no decimal places when outputting a simple bytes value
• The `processlist`/`x$processlist` views where improved, changes include:
  • The `pid` and `program_name` of the connection are shown, if set within the `performance_schema.session_connect_attrs` table (Contributed by Daniël van Eeden)
  • Issue #50 – The current statement progress is reported via …

Auditing your database means tracking access and changes to your data and db objects. The Audit Log Plugin has been shipped with Percona Server since 5.5.37/5.6.17, for a little over 12 months. Prior to the Audit Log Plugin, you had to work in darker ways to achieve some incarnation of an audit trail.

We have seen attempts at creating audit trails using approaches such as ‘sniffing the wire’, init files, in-schema ‘on update’ fields, triggers, proxies and trying to parse the traditional logs of MySQL (slow, general, binary, error). All of these attempts miss a piece of the pie, i.e. if you’re sniffing tcp traffic you’ll miss local connections, parsing binary logs you’re missing any reads. Your reasons for audit logging might be down to compliance requirements (HIPAA, PCI DSS) or you may need a way to examine database activity or track the connections incoming.

Over the past …

There are cases where we need to implement MySQL partitioning, or at least test if it is an option to solve an issue. However, how are we able to test it when the table on our production environment has hundreds of millions of rows, several composite indexes and/or the size on disk is hundreds of gigabytes?

Testing environments usually don’t have all the data that production has and if they have, probably you are not testing all the use-cases at a database level. Therefore, is it possible to test MySQL Partitioning on production impacting as less as possible?

When we execute pt-online-schema-change, it creates a table, triggers, and then copies the data. As we are going to test partitioning we are going to need both tables – with and without partitioning – and we are going to use triggers to keep both tables consistent. A good thing about changing a table to use partitioning is that, usually, …

Originally Posted by Adam Bird

There needed to be a better way for agents in Zendesk to track the tasks that had been assigned them. Using the Cronofy calendar API we created an app that keeps those assignments synchronized with the agent’s calendar.

This post shares our learnings from interacting with the Zendesk API, hopefully to help you with your own Zendesk project.

The Ruby source code for our integration is also available.

When a Ticket is a Task

Tickets are the core entity in the Zendesk domain. Tasks are just Tickets of type : ‘task’. This gives them an additional attribute due_at.

We also need to track the status of the ticket to know when it’s …

There needed to be a better way for agents in Zendesk to track the tasks that had been assigned them. Using the Cronofy calendar API we created an app that keeps those assignments synchronized with the agent's calendar.

This post shares our learnings from interacting with the Zendesk API, hopefully to help you with your own Zendesk project.

The Ruby source code for our integration is also available.

When a Ticket is a Task

Tickets are the core entity in the Zendesk domain. Tasks are just Tickets of type : 'task'. This gives them an additional attribute due_at.

We also need to track the …

In Python it is easily possible to findout the name of a Unicode character and findout some properties about that character. The module which does that is called unicodedata.

An example:

>>> import unicodedata
>>> unicodedata.name('☺')
'WHITE SMILING FACE'

This module uses the data as released in the UnicodeData.txt file from the unicode.org website.

So if UnicodeData.txt is a 'database', then we should be able to import it into MySQL and use it!

I wrote a small Python script to automate this. The basic steps are:

• Download UnicodeData.txt
• Create a unicodedata.ucd table
• Use LOAD DATA LOCAL INFILE to load the data

This isn't difficult especially because the file doesn't have the actual characters in it. It is …