Maintaining a production dataset at a manageable size can present a considerable challenge during the administration of a MySQL InnoDB Cluster.

Old Days

Back in the day when we only had one main copy of our data (the source), and one read copy (the replica) that we used to look at current and old data from our main system, we used a special trick to remove data without affecting the replica. The trick was to turn off writes to the binary log for our removal commands in the main system. External tools like pt-archiver were also able to use that trick. To stop bypass writing into the binary log, we used the command: SET SQL_LOG_BIN=0.

This mean that on the main production server (replication source), we were purging the data without writing the delete operation into the binary logs:

Current Days

These …

When working with group replication, MySQL router would be the obvious choice for the connection layer. It is tightly coupled with the rest of the technologies since it is part of the InnoDB cluster stack.The problem is that except for simple workloads, MySQL router’s performance is still not on par with other proxies like Haproxy […]

During last Ubuntu Summit in Riga, I got an interesting question from Alex Lutay, engineering manager at Canonical regarding the different ways to perform some Admin API commands in MySQL Shell.

He wanted to know why, in the manual and blogs, we always use separate commands instead of combining them, as he would expect this to have an impact on the connections established with the server.

Let’s illustrate this by looking at the different ways of obtaining the state of a cluster. Here are the four different methods:

Method 1

This is the method most frequently used in the documentation:

$ mysqlsh admin@10.0.0.1
JS> cluster=dba.getCluster()
JS> cluster.status() 

Method 2

This is Alex’s preferred method as he expects to have less round trips with the network:

$ …

Let’s see how to deploy WordPress and MySQL on a Kubernetes Cluster. The Kubernets cluster we are using is OKE (Oracle Kubernetes Engine) in OCI (Oracle Cloud Infrastructure):

OKE Cluster

We start by creating a Kubernetes Cluster on OCI using the Console:

We select the Quick create mode:

We need to name our cluster and make some choices:

When created, we can find it in the OKE Clusters list:

And we can see the pool of workers nodes and the workers:

kubectl

I like to use kubectl directly on my latop to manage my K8s Cluster.

On my Linux Desktop, I need to install kubernetes-client package (rpm).

Then on the K8s Cluster details, you can click on Access Cluster to get all the commands to use:

We need to copy them on our terminal and then, I like to also enable the bash completion for …

When you connect to a server (or cluster) using a TCP proxy level 7, also referred to as an application-level or Layer 7 proxy (highest level of the OSI model), the application doesn’t connect directly to the back-end server(s). The proxy usually understands the protocol used and can eventually take some decisions or even change the request.

The problem when using such proxy (like HA Proxy, ProxySQL and MySQL Router) is that the server doesn’t really know from where the client is connecting. The server sees the IP address of the proxy/router as the source IP of the client.

HA Proxy initially designed the Proxy Protocol, a simple protocol that allows a TCP connection to transport proxy-related information between the client, the proxy server and the destination server. The main purpose of the Proxy Protocol is then to preserve the client’s original IP address (with some others metadata). See …

MySQL InnoDB Cluster is the official High Availability solution for and from MySQL.

MySQL InnoDB Cluster is composed by MySQL Server, MySQL Group Replication, MySQL Router and MySQL Shell.

Before InnoDB Cluster there was no standard solution for MySQL Availability and many custom solutions were used (some better than the others). But there was a good solution using some similar principles of MySQL Group Replication: Galera.

Now that MySQL InnoDB Cluster is mature and easier to orchestrate than galera, I receive a lot of requests on how to migrate from Galera (or Percona XtraDB Cluster) to MySQL InnoDB Cluster.

I already wrote some time ago an article on this process: how to migrate from Galera to MySQL Group Replication.

In this article we will see how we can migrate from Percona XtraDB …

MySQL InnoDB Cluster is the High Availability solution for MySQL. It delivers automatic fail-over and guarantees zero data loss (RPO=0).

RPO: Recovery Point Objective describes the interval of time that might pass during a disruption before the quantity of data lost during that period exceeds the Business Continuity Plan’s maximum allowable tolerance.

Example: our business architecture needs to have RPO=2 minutes. This means that in case of failure, 2 minutes of data can be lost.

However, and we saw this recently in Europe, an entire data center can “disappear” instantaneously… So it’s also important to have a Disaster Recovery plan.

One solution, is to have an InnoDB Cluster (Group Replication) that spans across multiple regions. However, this is often not feasible because of high latency across regions.

Another solution is InnoDB Cluster in one region with Asynchronous …

I wanted to be brave and I installed MySQL Group Replication manually…. it was painful !

Then I realized that managing those servers and especially deal with MySQL Routers was even more painful !

What are my options now ? Is there a solution or do I need to restart from scratch ?

Asking the answer is already answering it… and once again MySQL Shell at the rescue.

MySQL Group Replication

I’ve configured everything manually. I also loaded group_replication and clone plugins and finally after having bootstrapped my Group here is what I have:

mysql> select member_host, member_port port, member_state state, 
       member_role role, member_version version 
       from performance_schema.replication_group_members;
+-------------+------+--------+-----------+---------+
| member_host | port | state  | role      | version | …

A few days ago, a customer got in touch asking how they could use Percona Monitoring and Management (PMM) to monitor the roles played by each node in an InnoDB cluster. More specifically, they wanted to check when one of the nodes changed its role from Primary to Secondary, or vice-versa. PMM allows for a high level of flexibility and customization through its support for custom queries, and we just have to be creative on how we approach the problem. In this post, we present the steps we did to test our solution, including the deployment of a 3-node InnoDB Cluster hosted in the same server (for testing) and a PMM 2 server, and connecting them together. Even though this has already been covered in other blog …

Just recently, I have been asked to look into what a Disaster Recovery site for InnoDB Cluster would look like.

If you’re reading this, then I assume you’re familiar with what MySQL InnoDB Cluster is, and how it is configured, components, etc.

Reminder: InnoDB Cluster (Group Replication, Shell & Router) in version 8.0 has had serious improvements from 5.7. Please try it out.

So, given that, and given that we want to consider how best to fulfill the need, i.e. create a DR site for our InnoDB Cluster, let’s get started.

Basically I’ll be looking at the following scenario:

InnoDB Cluster Source site with a Group Replication Disaster Recovery Site.

Now, just before we get into the nitty-gritty, here’s the scope.

Life is already hard enough, so we want as much automated as possible, so, yes, InnoDB Cluster gets some of that done, but there are other parts we will still have …