In one of the projects I have been working on, one requirement was the ability to stop traffic from reaching a MySQL host which has been lagging behind its master for longer than a specific amount of time and then bring it back online once the lag has gone away. Of course, this is all automated and no human intervention is required.

In this scenario, we are using HAProxy as the load balancer, and I will walk you through how to configure an agent so we can use HAProxy httpchk to flag the host as up or down, via systemd socket and then automatically set the host as being down/up when applicable, in HAProxy.

I will be setting up a systemd service (I’m running centos7 hosts) and creating a listening socket in the MySQL host we want to monitor so haproxy can have access to replication status.

Scenario:

master: po-mysql1
slaves: po-mysql2, po-mysql3, po-mysql4
secondary slaves: …

In a previous post I had mentioned that I was doing a bit of digging into Docker in order to get a better grasp of the technology. Part of that was exploring common administrative tasks. I would venture to say that backups are probably among the most important tasks we take on with database administration, so it’s important to know how to do this for Docker MySQL instances.

There is a fair bit of documentation on how to handle this logically (mysqldump / mydumper) as this is a simple task to perform as long as you can connect to the database instance, so I wanted to approach physical backups using the very common xtrabackup tool. Additionally, we’re trying to think with containers here, so I wanted to make sure that not only would I be taking a backup of the Docker container MySQL instance, but I would do it with another Docker container running …

Percona Live Santa Clara, an annual event where open source database users, developers and enthusiasts come together, was held in April at the Santa-Clara convention centre. Pythian was well represented once more with no less than five presentations and a total of nine attendees.

This year the conference was condensed to two days of breakout sessions and one day of tutorials. Though it was shorter in length, the organizers broadened their horizons by including not only MySQL and MongoDB tracks, but this year they even put together a full PostgreSQL track. Moving from MySQL only to multiple technologies, inspired this year’s tagline: polyglot persistence conference. The increase in number of sessions allowed for a lot more options, but the condensed schedule made it much harder to choose which sessions to attend!

My observation from last year’s …

Earlier this year, I was presented with the challenge of streamlining user access to MySQL, allowing users self-serve access using their LDAP credentials, while logging all access. Of course, various MySQL forks allow for user auditing, but the solution is also needed to eventually support other data storage systems without native user auditing. This gave me the opportunity to do a trial integration of MySQL, Vault, ProxySQL, and LDAP; Vault would be used to dynamically create user accounts, and ProxySQL would be used to limit access and log activity. To evaluate the functionality and configuration of the integration, I used Docker to set up a test environment.

Below I will present the methods used to:

1. Provision the Docker environment.
2. Configure OpenLDAP. …

Introduction

In this post we will explore one approach to MySQL high availability with HAProxy, Consul and Orchestrator.
Let’s briefly go over each piece of the puzzle first:
– HAProxy is usually installed on the application servers or an intermediate connection layer, and is in charge of connecting the application to the appropriate backend (reader or writer). The most common deployment I’ve seen is to have separate ports for writes (which are routed to the master) and reads (which are load balanced over a pool of slaves).
– Orchestrator’s role is to monitor the topology and perform auto recovery as needed.
The key piece here is how we can make HAProxy aware that a topology change has happened, and the answer lies within Consul (and Consul templates).
– Consul is meant to be told the identity of the new master by Orchestrator. By leveraging Consul templates, we can then in turn propagate that …

Introduction

One of the things I like about Continuent Tungsten is how Tungsten Connector can hold traffic while a (graceful) master switch is taking place. This means the application may experience a brief spike in latency, but has not returned any errors from the database layer.

René also described a similar process using ProxySQL and mysqlrpladmin here. In this post we will try to achieve the same feat, a graceful master switchover with ProxySQL and Orchestrator.

ProxySQL considerations

ProxySQL needs to isolate our application from the changes going on at the database layer.
By design, if a query needs to be sent to a hostgroup that has no servers in ONLINE state, ProxySQL waits until either a server becomes available or a timeout expires (mysql-connect_timeout_server_max if I am not …

Introduction

In this article we will explore one approach for deploying ProxySQL behind a load balancer in Google Cloud.

While considering the deployment of ProxySQL, one has basically the following options:

1. Install ProxySQL on existing application server(s)
2. Provision dedicated ProxySQL server(s) between your application servers and the database layer.

Each approach has its pros and cons, but if there’s a significant number of application servers (more than a dozen or so) having a dedicated ProxySQL “layer” can be a more attractive option, specially if there is no service discovery mechanism in place (e.g. Consul).

Let’s consider a simple scenario, with a master and a small number of slaves in a single geographic region. Assuming that you are following the best practice, your database servers should be split into different availability zones. So for ProxySQL, it also …

Percona Live 2018 is coming up soon, and Pythian is excited to be participating again this year. The Percona Live Open Source Database Conference is the premier open source database event for individuals and businesses that develop and use open source database software. This year the conference will take place April 23 – April 25 in Santa Clara, California. Once again Pythian will be hosting the highly anticipated Pythian Community Dinner on Tuesday, April 24th at Pedro’s Restaurant and Cantina.

We’ve also got a fantastic lineup up of speakers. You’ll want to make sure you register for the following sessions:

Monday, April 23, 2018

9:30 am – 12: 30 pm PST

• Derek Downey will present “Hands on ProxySQL”
• Matthias Crauwels & Pep Pla will …

“The single most important lesson I’ve learned is to keep it simple. I find designers sometimes deliver over-complex, generic solutions that could (in theory) do anything, but in reality are remarkably difficult to operate, and often misunderstood.”–John Ryan

I have interviewed John Ryan, Data Warehouse Solution Architect (Director) at UBS.

RVZ

Q1. You are an experienced Data Warehouse architect, designer and developer. What are the main lessons you have learned in your career?

John Ryan: The single most important lesson I’ve learned is to keep it simple. I find designers sometimes deliver over-complex, generic solutions that could (in theory) do anything, but in reality are remarkably difficult to operate, and often misunderstood. I believe this stems from a lack of understanding of the …

Please join Percona’s Principal Support Engineer, Sveta Smirnova, as she presents Performance Schema for MySQL Troubleshooting on Thursday, March 1, 2018, at 10:00 am PST (UTC-8) / 1:00 pm EST (UTC-5).

Register Now

Performance Schema, first introduced in version 5.5, is a really powerful tool. It is actively under development, and each new version provides even more instruments for database administrators.

Performance Schema is complicated. It is also not free: it can slow down performance if you enable certain …