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 …

In this blog post, I’ll look at how you can analyze raw MySQL query logs with ClickHouse.

For typical query performance analyses, we have an excellent tool in Percona Monitoring and Management. You may want to go deeper, though. You might be longing for the ability to query raw MySQL “slow” query logs with SQL.

There are a number of tools to load the MySQL slow query logs to a variety of data stores. For example, you can find posts showing how to do it with LogStash. While very flexible, these solutions always look too complicated and limited in functionality to me.   

By far the best solution to parse and load MySQL slow query logs (among multiple log types supported) is Charity …

The latest release of Percona Monitoring and Management (PMM) 1.8.0 is available as of February 27, 2018.

Percona Monitoring and Management is a free and open-source platform for managing and monitoring MySQL and MongoDB performance. You can run PMM in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL and MongoDB servers to ensure that your data works as efficiently as possible.

This release introduces many improvements in the user interface and optimizations of performance.

New landing page

The home page has been removed in favor of the …

Recently we faced an issue where the config management software had automatically upgraded the mariadb-server-10.1 package to the latest 10.1.31 version. This upgrade broke the galera cluster setup for this installation.

I’ve started to recreate this issue in my local lab setup and I managed to reproduce this problem.

I have created a 3 node galera setup: galera1 (192.168.55.100), galera2 (192.168.55.101) and galera3 (192.168.55.102). All 3 servers run MariaDB-10.1.30. Galera replication is working fine.

This is the basic galera config:

# cat /etc/mysql/conf.d/cluster.cnf
#########################################################
# Galera config
#########################################################

[mysqld]
wsrep_on                                  = ON
wsrep_provider                            = /usr/lib/libgalera_smm.so
wsrep_provider_options                    =
wsrep_cluster_name                        = …