So, I admit the title for this post is a bit ambiguous. Not only is it the second post I’ve written in a short period of time, but it’s also focused on ProxySQL 2.0. As promised in the previous post, I’ve upgraded my testing environment to ProxySQL 2.0 and was interested to see if there are any differences in behaviour from the previous version. As it turns out, there are.

In the comments section of the previous post, you’ll find a comment by ProxySQL Founder and CEO, René Cannaò:

“Indeed ProxySQL doesn’t support caching_sha2_password authentication plugin, but since ProxySQL 2.0.3 (March 2019) a client connection using caching_sha2_password will be automatically switched to mysql_native_password.

With regards to “OPT_CHARSET_NAME=utf8”, the problem is that your backend it is not MySQL 8.0 …

When I run a benchmark and want to measure the CPU efficiency of something, I find it’s often a good choice to run a benchmark program, as well as the database, on the same server. This is in order to eliminate network impact and to look at single-thread performance, to eliminate contention.

Usually, this approach gives rather stable results; for example, benchmarking MySQL with Sysbench OLTP Read-Only workload I get a variance of less than one percent between 1-minute runs.

In this case, though, I was seeing some 20 percent difference between the runs, which looked pretty random and would not go away even with longer 10-minute runs.

The benchmark I did was benchmarking MySQL through ProxySQL (all running on the same machine):

Sysbench -> ProxySQL -> MySQL 

As I thought more about possible reasons, I thought CPU scheduling might be a problem. As requests pass …

In this blog, we will test a new security feature added in ProxySQL 2.0.9. Since a time ago, we have had the ability to block queries using mysql_query_rules table matching a group of queries using reg exp like a blacklist. Check out a previous blog for how to config “ProxySQL Firewalling” using the mysql_query_rules table.

You can improve a whitelist using the mysql_query_rules table, but it is difficult if you have hundreds of queries.

ProxySQL 2.0.9 introduces two new tables for the firewall whitelist algorithm:

Admin> SELECT name AS tables FROM main.sqlite_master WHERE type='table' AND name IN ('mysql_firewall_whitelist_rules','mysql_firewall_whitelist_users') ORDER BY name;
+--------------------------------+
| tables                         | …

In the MySQL ecosystem, orchestrator is the most popular and well-respected high availability and topology management tool, integrating well with other solutions such as ProxySQL. It facilitates automatic (or manual) discovery, refactoring and recovery of a replicated MySQL environment, and comes complete with both command-line (CLI) and web interfaces for both humans and machines to interact with.

As we all know, humans are prone to errors and as such accidents can happen, particularly when humans and computers interact with each other! Recently, one of these situations related to the web interface of orchestrator during topology refactoring with its drag-and-drop capabilities, where a drop occurred unintentionally and thus had an impact on replication.

When …

If you are thinking of using ProxySQL in our Percona XtraDB Cluster environment, I’ll explain how to use ProxySQL 2 for failover tasks.

How to Test

ProxySQL uses the “weight” column to define who is the WRITER node. For this example, I’ll use the following list of hostnames and IPs for references:

+-----------+----------------+
| node_name | ip             |
+-----------+----------------+
| pxc1      | 192.168.88.134 |
| pxc2      | 192.168.88.125 |
| pxc3      | 192.168.88.132 |
+-----------+----------------+

My current WRITER node is the “pxc1” node, but how can I see who is the current WRITER? It’s easy, just run the following query:

proxysql> select hostgroup_id, comment, hostname, status, weight from runtime_mysql_servers;

This is the output: …

A database proxy is a wonderful tool that is able to provide significant functionality across various use cases. For example, a seamless master role switch to another node for maintenance; transparency with read and write connections; or providing automatic, intelligent database load balancing.

In the MySQL world, these proxies provide a single entry point into MySQL databases for the calling client applications. Or put differently, the proxy is a middle layer sitting between a MySQL database and an application. The application connects to a proxy, which then forwards connections into the database.

Good proxies make MySQL database clusters appear like single databases by hiding the “behind-the-scenes-plumbing” from the application. One …

ProxySQL and Orchestrator are usually installed to achieve high availability when using MySQL replication. On a failover (or graceful takeover) scenario, Orchestrator will promote a slave, and ProxySQL will redirect the traffic. Depending on how your environment is configured, and how long the promotion takes, you could end up in a scenario where you need manual intervention.

In this post, we are going to talk about some considerations when working with ProxySQL in combination with pt-heartbeat (part of Percona Toolkit), with the goal of making your environment more reliable.

Why Would We Want pt-heartbeat With ProxySQL?

If you have intermediate masters, the seconds_behind_master metric is not good enough. Slave servers that are attached to intermediate masters will report the seconds_behind_master relative to their own …

MySQL query rewrite for ClickHouse using ProxySQL  Introduction

ProxySQL in September 2017 announced support for ClickHouse as backend. ProxySQL is a popular open source, high performance and protocol-aware proxy server for MySQL and its forks. ClickHouse is an open source column-oriented database management system capable of real time generation of analytical data reports using SQL queries. To support ClickHouse as a backend, ProxySQL acts as a data bridge between MySQL protocol and ClickHouse protocol, allowing MySQL clients to execute queries in ClickHouse through it. ClickHouse’s SQL query syntax is different than MySQL’s syntax, and migrating application from MySQL to ClickHouse isn’t just a matter of changing connections endpoint but it also requires modifying some queries. This needs development time, but not always possible. One of ProxySQL most widely used feature is indeed the ability of …

Use ClickHouse like MySQL with ProxySQL Introduction

We have several customers on ClickHouse now for both columnar database analytics and archiving MySQL data, You can access data from ClickHouse with clickhouse-client but this involves some learning  and also limitations technically. Our customers are very comfortable using MySQL so they always preferred a MySQL client for ClickHouse query analysis and reporting, Thankfully ProxySQL works as a optimal bridge between ClickHouse and MySQL client, This indeed was a great news for us and our customers worldwide. This blog post is about how we can use MySQL client with ClickHouse.

Installation

• https://github.com/sysown/proxysql/releases/  (**Download the package starting with clickhouse )
• Dependencies installation:
  • yum -y install …

While we’ve had MySQL Group Replication support in ProxySQL since version 1.3 (native as of v1.4), development has continued in subsequent versions. I’d like to describe a scenario of how latency can affect ProxySQL in a MySQL Group Replication environment, and outline a few new features that might help mitigate those issues. Before we dive into the specifics of the discussion, however, let’s take a quick overview of ProxySQL and Group Replication for those who may not be familiar.

MySQL Group Replication

Similar in functionality to Percona XtraDB Cluster or Galera, MySQL Group Replication is the only synchronous native HA solution for MySQL*. With built-in automatic distributed recovery, conflict detection, and group membership, MySQL GR provides a completely native HA solution for MySQL environments.