Getting MySQL working with self-signed SSL certificates is pretty simple. Having it working with a certificate signed by a trusted authority is also very simple, we just need to set the correct path and privileges to the file. The problem comes when we need to make MySQL validate the certificate signature against the authority public key.

I’ve searched on the internet but wasn’t able to find much information about it. There are a good number of posts on how to set up your own certificate authority and self-sign your certificates, but not much about how to use one signed by a public trusted authority.

I used a certificate signed by a Let’s Encrypt on my tests but the concepts and steps shared here should work for any public trusted authority. I also generated one certificate to be used by MySQL server and another one to be used by the client. It is possible to use the …

How scheduler and script stand in supporting failover (Percona and Marco example) 

In part one of this series,  I had illustrated how simple scenarios may fail or have problems when using Galera native support inside ProxySQL. In this post, I will repeat the same tests but using the scheduler option and the external script.

The Scheduler

First a brief explanation about the scheduler.

The scheduler inside ProxySQL was created to allow administrators to extend ProxySQL capabilities. The scheduler gives the option to add any kind of script or application and run it at the specified interval of time. The scheduler was also the initial first way we had to deal with Galera/Percona XtraDB Cluster (PXC) node management in case of issues. 

…

How native ProxySQL stands in failover support (both v2.0.15 and v2.1.0)

In recent times I have been designing several solutions focused on High Availability and Disaster Recovery. Some of them using Percona Server for MySQL with group replication, some using Percona XtraDB Cluster (PXC). What many of them had in common was the use of ProxySQL for the connection layer. This is because I consider the use of a layer 7 Proxy preferable, given the possible advantages provided in ReadWrite split and SQL filtering. 

The other positive aspect provided by ProxySQL, at least for Group Replication, is the native support which allows us to have a very quick resolution of possible node failures.

ProxySQL has Galera …

Editor’s Note: Because our bloggers have lots of useful tips, every now and then we update and bring forward a popular post from the past. Today’s post was originally published on November 26, 2019.

It’s not uncommon these days for us to use a high availability stack for MySQL consisting of Orchestrator, Consul and ProxySQL. You can read more details about this stack by reading Matthias Crauwels’ blog post How to Autoscale ProxySQL in the Cloud as well as Ivan Groenwold’s post on MySQL High Availability With ProxySQL, Consul and Orchestrator. The high-level concept is simply that Orchestrator will monitor the state of the MySQL replication topology and report changes to Consul which in turn can update ProxySQL hosts using …

Kubernetes has been a big trend for a while now, particularly well-suited for microservices. Running your main databases on Kubernetes is probably NOT what you are looking for. However, there’s a niche market for them. My colleague Stephen Thorn did a great job explaining this in The Criticality of a Kubernetes Operator for Databases. If you are considering running your database on Kubernetes, have a look at it first. And, if after reading it you start wondering how the Operator works, Stephen also wrote an Introduction to Percona Kubernetes Operator for Percona XtraDB Cluster (PXC), which presents the Kubernetes architecture and how the Percona Operator simplifies the deployment of a …

Here at Pythian we get a lot of exposure to new technologies and implementation strategies via the work we do internally and for our clients. The most noteworthy technology stack that I’ve seen get a lot of traction in the MySQL community recently is the high availability stack including Orchestrator, Consul and ProxySQL. 

I won’t dive too deeply into the details of this implementation as there are several blog posts that our team has submitted on this topic, but the key thing I want you to keep in mind for this particular topic is the usage of Consul as a “source of truth” for the state of your MySQL replication clusters. If Orchestrator or its adjacent scripts are running as expected, Consul should always have the latest information pertaining to the state of your cluster. This is incredibly valuable. In fact, …

Here at Pythian we like to pay attention to what brings people to our site, and one of the main queries we’ve been seeing is “How can I allow a remote MySQL database connection?” Since our mission is to help you love your data, we asked Matthias Crauwels, Lead Database Consultant on one of Pythian’s MySQL teams to address this topic. Over to Matthias!

I’m hoping this may be the start of a series of posts where I can share some common MySQL knowledge in a (hopefully) clear and understandable manner. So let’s get started.

Technical requirements

Historically MySQL allows a client to connect in two different ways. You can either use a local socket solution (on a Linux/Unix system this is called a Unix socket, whereas on Windows systems it’s a named pipe) or via a TCP connection to a listening port (by default 3306).

Since the introduction of MySQL 8.0, a third way is also available using a newer …

Editor’s Note: Because our bloggers have lots of useful tips, every now and then we update and bring forward a popular post from the past. Today’s post was originally published on May 23, 2018.

In recent weeks I’ve been focusing on Docker in order to get a much better understanding of the containerized world that is materializing in front of us. Containers aren’t just for stateless applications anymore and we’re seeing more cases where MySQL and other databases are being launched in a containerized fashion, so it’s important to know how to configure your MySQL container!

In docker hub, you will see an option for this by doing a volume mount from the Docker host to the container on /etc/mysql/conf.d. But the problem is that the container image you’re using may …

Schema changes are the big challenges in Galera replication. So, it is recommended to understand the schema changes operation for everyone who uses the Percona XtraDB Cluster (PXB)/Galera clusters. In this blog, I am going to explain the operation and impact of the various schema changes methods used in the PXB/Galera cluster.

• Schema changes with “wsrep_OSU_method = TOI”
• Schema changes with “wsrep_OSU_method = RSU”
• Schema changes with “ONLINE ALGORITHMS”
• Schema changes with “pt-osc”
• Schema changes with “gh-ost”

For testing:

• I have configured the 3-node Percona Xtradb Cluster (8.0.19).
• Executing read/write load using the sysbench.

mysql> select @@wsrep_cluster_address\G
*************************** 1. row …

After spending four years working with ProxySQL in production, I’ve learned a few interesting lessons about interpreting and processing query rules. I hope to save you some time (and avoid wrong turns) with this summary of ProxySQL query rules.

ProxySQL query engine is very powerful and supports the building of complex rule sets. These can be used to route traffic to backend MySQL instances, rewrite queries, and for traffic mirroring, among other use cases.

When the rule set is short and simple, you can easily understand what the outcome for a certain query would be. However, for complex sets or combined scenarios, you need a deeper understanding of the engine logic.

Note 1: Rules are processed in rule_id order

It might seem like a trivial start, but during testing or implementation, …