The latest from the MySQL community

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:

I have published enough Debezium MySQL connector tutorials for taking snapshots from Read Replica. To continue my research I wanted to do something for AWS RDS Aurora as well. But aurora is not using binlog bases replication. So we can’t use the list of tutorials that I published already. In Aurora, we can get the binlog file name and its position from its snapshot of the source Cluster. So I used a snapshot for loading the historical data, and once it’s loaded we can resume the CDC from the main cluster.

Requirements:

1. Running aurora cluster.
2. Aurora cluster must have binlogs enabled.
3. Make binlog retention period to a minimum 3 days(its a best practice).
4. Debezium connector should be able to access both the clusters.
5. Make sure you have different security …

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 …

MySQL InnoDB Cluster has introduced by the MySQL team for the High Availability ( HA ) purpose . It provides a complete high availability solution for MySQL.

Alright, I am planning to write the series of the blogs about the InnoDB Cluster configurations / Management with MySQL Shell / Monitoring etc …

In this blog I am going to show the InnoDB Cluster configuration with three nodes .

What is InnoDB Cluster ?

MySQL InnoDB Cluster is the Combination of,

• MySQL shell
• Group Replication ( GR )
• MySQL Router

Lab Environment :

I have prepared my lab with three servers,

• OS : Centos 7.7
• MySQL 8.0.18 ( latest version )

The server details are ,

• 192.168.91.11 ( hostname : sakthilabs11 )
• 192.168.91.12 ( hostname : sakthilabs12 )
…

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 – …

As you could read in this previous post, PHP 7.4 is now completely supporting MySQL 8.0 and the new default authentication plugin.

I wanted to make a summary table providing and overview of all PHP versions and how they supported MySQL 8.0 and the different authentication plugins.

As I am a RPM based distribution user, I’m using the famous repository of remi since a lot of years, and I use it then also to install PHP 7.4.0 and 7.4.1…

I created a new user to test to …

Credit: AWS

CDC is becoming more popular nowadays. Many organizations that want to build a realtime/near realtime data pipe and reports are using the CDC as a backbone to powering their real-time reports. Debezium is an opensource product from RedHat and it supports multiple databases (both SQL and NoSQL). Apache Kafka is the core of this.

Managing and scaling Kafka clusters is not easy for everyone. If you are using AWS for your infra then let AWS manage the cluster. AWS has MSK is a managed Kafka service. We are going to configure Debezium with AWS MSK.

Configuration File:

If you are already worked with AWS MSK, then you might be familiar with this configuration file. This is similar to the RDS Parameter group but here you need to upload a with your parameter name and its value. If you are using MSK for the first time, then it’ll make you a bit confused. No worries, I’ll give you the steps to do …