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 …

For Linux, the most common way to distribute software is binary packages in the rpm or deb format. Most packages are included in the official distribution repositories or 3rd party software repositories. Nevertheless, there are some cases where you need to install just a few standalone packages.   You might be able to use the local package install tools, namely dpkg or rpm, however, there are cases where packages can’t be installed due to the dependencies and you need to install all dependencies manually. It might take some time and isn’t always an easy process. But there is a solution that can help – you can create your own local repository and deploy your packages to it.

Let’s discuss how to create your local repositories to make your life easier.

RPM-Based Distributions

RPM-based operating systems work with rpm packages and the most common package manager for them is yum.   While newer …

First I want to thank everyone who attended my December 19, 2019 webinar “Top 3 Features of MySQL“. Recording and slides are available on the webinar page.

Here are answers to the questions from participants which I was not able to provide during the webinar.

Q: When do undo logs and redo logs come into play? Can you please explain the background operations of these logs?

A: These two are completely different structures.

Undo Logs

Belong to a single active transaction. It contains information on how to undo the latest change to the clustered index, performed by this transaction.

Let’s demonstrate how they work with an example.

Consider this table:

In my previous post, I have shown you how to take the snapshot from Read Replica with GTID for Debezium MySQL connector. GTID concept is awesome, but still many of us using the replication without GTID. For these cases, we can take a snapshot from Read replica and then manually push the Master binlog information to the offsets topic. Injecting manual entry for offsets topic is already documented in Debezium. I’m just guiding you the way to take snapshot from Read replica without GTID.

Requirements:

• Setup master slave replication.
• The slave must have log-slave-updates=ON else connector will fail to read from beginning onwards.
• Debezium connector should be able to …

Many things in the MySQL 8.0 Series has evolved. I’ve discussed many of those things in prior blogs such as items like the MySQL-Router w/InnoDB Cluster, plus a Series on Enterprise Backup with InnoDB Cluster Backup & Restore Use Cases. But now its time to update everyone on the evolution of the Yum Repo packaging… Read More »

Many things in the MySQL 8.0 Series has evolved. I’ve discussed many of those things in prior blogs such as items like the MySQL-Router w/InnoDB Cluster, plus a Series on Enterprise Backup with InnoDB Cluster Backup & Restore Use Cases. But now its time to update everyone on the evolution of the Yum Repo packaging in MySQL 8.0.18!… Read More »

When you installed the Debezium MySQL connector, then it’ll start read your historical data and push all of them into the Kafka topics. This setting can we changed via snapshot.mode parameter in the connector. But if you are going to start a new sync, then Debezium will load the existing data its called Snapshot. Unfortunately, if you have a busy transactional MySQL database, then it may lead to some performance issues. And your DBA will never agree to read the data from Master Node.[Disclaimer: I’m a DBA :) ]. So I was thinking of figuring out to take the snapshot from the Read Replica, once the snapshot is done, then start read the realtime data from the Master. I found this useful information in a StackOverflow answer.

If your binlog uses GTID, you should be able to make a CDC tool like Debezium read the snapshot from the replica, then when that’s done, switch to the master to read the binlog. But if you don’t use …

This blog discusses a few concepts about Docker and how we can use it to run a MySQL async replication environment. Docker is a tool designed to make it easier for developers and sysadmins to create/develop, configure, and run applications with containers. The container allows us to package all parts of the application it needs, such as libraries, dependencies like code, configurations, and runtime engine. Docker runtime containers are platform-independent so the package created can be shipped one platform to another platform.

Dockerhub is the repository where you can find containerized docker images for applications like MySQL, Percona Server for MySQL, and MariaDB. Using the example below, I will show you how to set up a docker container from the …

One of our support customers approached us with the following problem the other day:

mysql> CREATE TABLE child_table (
`id` int unsigned auto_increment,
`column1` varchar(64) NOT NULL,
parent_id int unsigned NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT FOREIGN KEY (parent_id) REFERENCES parent_table (id));
ERROR 1215 (HY000): Cannot add foreign key constraint

They could not create a table with an FK relation! So, of course, we asked to see the parent table definition, which was:

CREATE TABLE `parent_table` (
  `id` int unsigned auto_increment, 
  `column1` varchar(64) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  
PARTITION BY HASH (id) 
PARTITIONS 4;

The parent table is partitioned!  This immediately explained the problem; partitioned tables can not be part of an FK relationship, as described (in point 10) here – …

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 …