Replication has been the core functionality, allowing high availability in MySQL for decades already. However, you may still encounter replication errors that keep you awake at night. One of the most common and challenging to deal with starts with: “Got fatal error 1236 from source when reading data from binary log“. This blog post is […]
Replication being slow—replication lag—is a common complaint, but MySQL replication is actually really fast. Let’s run a controlled experiment and peek inside the Performance Schema binary logs to see why.
Replication being slow—replication lag—is a common complaint, but MySQL replication is actually really fast. Let’s run a controlled experiment and peek inside the Performance Schema and binary logs to see why.
Replication being slow—replication lag—is a common complaint, but MySQL replication is actually really fast. Let’s run a controlled experiment and peek inside the Performance Schema and binary logs to see why.
TL;DR: Make sure to run “SET persist_only disabled_storage_engines=’MyISAM’, persist sql_generate_invisible_primary_key=ON;” on all instances and restart each one in your MySQL InnoDB Cluster.
Ok, what does “safe from naughtiness” mean?:
– Anyone creating tables that aren’t InnoDB, as this doesn’t make
sense, after all, it is an “InnoDB” cluster.
– Making sure all tables have a Primary Key (invisible or
not).
– Making sure that my (invisible) primary keys are visible to the
cluster as it will rightfully complain if they aren’t!
This basically means that once you’ve got it all up and running you won’t run into those horrible situations whereby someone, somewhere, creates a MyISAM table that didn’t have a Primary Key and thus leave you with a broken cluster.
Eg.
MySQL rtnode-01:3306 ssl JS > vlc.status()
{
"clusterName": "VLC",
"clusterRole": "PRIMARY", …
[Read more]
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 …
[Read more]Some time ago, we saw how we could deploy WordPress on OCI using MySQL HeatWave Database Service with Read Replicas. We had to modify WordPress to use a specific plugin that configures the Read/Write Splitting on the application (WordPress): LudicrousDB.
Today, we will not modify WordPress to split the Read and Write operations, but we will use MySQL Router 8.2.0 (see [1], [2], [3]).
Architecture
The …
[Read more]As a MySQL database administrator, you’re likely familiar with the SHOW REPLICA STATUS command. It is an important command for monitoring the replication status on your MySQL replicas. However, its output can be overwhelming for beginners, especially regarding the binary log coordinates. I have seen confusion amongst new DBAs on which binary log file and position represent what in the replication.
In this guide, we’ll simplify the SHOW REPLICA STATUS output, focusing on the critical binary log coordinates essential for troubleshooting and managing replication.
The key binlog coordinates
Before we delve into the output, let’s understand the key binlog coordinates we’ll be working with:
- Master_Log_File: This is the name of the primary binary log file that the I/O thread is currently reading from.
- Read_Master_Log_Pos: It represents the …
Many of us, old MySQL DBAs used
Seconds_Behind_Source
from SHOW REPLICA
STATUS
to find out the status and correct execution of
(asynchronous) replication.
Please pay attention of the new terminology. I’m sure we’ve all used the old terminology.
However, MySQL replication has evolved a lot and the replication team has worked to include a lot of useful information about all the replication flavors available with MySQL.
For example, we’ve added parallel replication, group replication,
… all that information is missing from the the good old
SHOW REPLICA STATUS
result.
There much better ways to monitoring and observing the
replication process(es) using Performance_Schema
.
Currently in Performance_Schema
, there are 15 tables
relating to replication instrumentation:
+------------------------------------------------------+
| …
[Read more]
This blog post will cover the basic setup of the InnoDB ClusterSet environment, which provides disaster tolerance for InnoDB Cluster deployments by associating a primary InnoDB Cluster with one or more replicas in alternate locations/different data centers. InnoDB ClusterSet automatically manages replication from the primary cluster to the replica clusters via a specific ClusterSet Async replication channel. If the primary cluster becomes inaccessible due to a loss of network connectivity or a data center issue, you can make a replica cluster active in its place.
Now, let’s see in detail how exactly we can configure the topology.
We have used the sandbox environment available via MySQLShell utility for this setup.
Environment
Cluster1: 127.0.0.1:3308 127.0.0.1:3309 127.0.0.1:3310 Cluster2: 127.0.0.1:3311 127.0.0.1:3312 127.0.0.1:3313 Router: …[Read more]