Showing entries 1 to 10 of 72
10 Older Entries »
Displaying posts with tag: gtid (reset)
Fixing low durability GTID replica with Voodoo

At my FOSDEM talk earlier this year, I gave a trick for fixing a crashed GTID replica.  I never blogged about this, so now is a good time.  What is pushing me to write on this today is my talk at MinervaDB Athena 2020 this Friday.  At this conference, I will present more details about MySQL replication crash safety.  So you know what to do if you want to learn more about this subject.  Let's now

A Legacy Behavior of MySQL Corrupting Restored Backups (replicate-same-server-id = OFF)

In my previous post (Puzzled by MySQL Replication), I describe a weird, but completely documented, behavior of replication that had me scratching my head for hours because it was causing data corruption.  I did not give too many details then as I also wanted allowing you to scratch your head if you wished.  In this post, I describe this behavior in more details.

But first I need to apologize to

Puzzled by MySQL Replication (War Story)

Recently, I was puzzled by MySQL replication !  Some weird, but completely documented, behavior of replication had me scratching my head for hours.  I am sharing this war story so you can avoid losing time like me (and also maybe avoid corrupting your data when restoring a backup).  The exact justification will come in a follow-up post, so you can also scratch your head trying to understand what

MySQL Convert Binlog Based Replication To GTID Replication Without Downtime

This title may be suitable for the new age MySQL Users. Because in 5.7 onwards its already supported to enable GTID online. But still few of my mission critical databases are in 5.6 and handling 70k QPS. So I know enabling GTID needs downtime for this. But in my case, the GTID has been already implemented. But still the replication is using Binlog file name and position for replicating the data.

This is my slave status. You can see the GTID has been enabled but the Auto_Position is still 0 which means still my replication is binlog filename and position. No issues with the replication. But the MySQL world already moved to GTID for better control on replication and Failover.

             Master_Server_Id: 10010
                  Master_UUID: c924545b-a3e3-11e8-8a39-42010a280410
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL …
[Read more]
MySQL Group Replication

So MySQL's group replication came out with MySQL 5.7. Now that is has been out a little while people are starting to ask more about it.

Below is an example of how to set this up and a few pain point examples as I poked around with it.
I am using three different servers,


mysql> INSTALL PLUGIN group_replication SONAME '';
Query OK, 0 rows affected (0.02 sec)

vi my.cnf

[Read more]
MySQL Master High Availability and Failover: more thoughts

Some months ago, Shlomi Noach published a series about Service Discovery.  In his posts, Shlomi describes many ways for an application to find the master.  He also gives detail on how these solutions cope with failing-over to a slave, including their integration with Orchestrator.

This is a great series, and I recommend its reading for everybody implementing master failover, with or without

MySQL Master Replication Crash Safety Part #3: GTID

This is a follow-up post in the MySQL Master Replication Crash Safety series.  In the two previous posts, we explored the consequence of reducing durability on masters (including setting sync_binlog to a value different than 1) when slaves are using legacy file+position replication.  In this post, we cover GTID replication.  This introduces a new inconsistency scenario with a potential

MySQL Tutorial – Understanding The Seconds Behind Master Value

In a MySQL hosting replication setup, the parameter Seconds_Behind_Master (SBM), as displayed by the SHOW SLAVE STATUS command, is commonly used as an indication of the current replication lag of the slave. In this blog post, we examine how to understand and interpret this value in various situations.

Possible Values of  Seconds Behind Master

The value of SBM, as explained in the  MySQL documentation, depends on the state of the MySQL slave in general, and the states of MySQL slave SQL_THREAD and IO_THREAD in particular. While IO_THREAD connects with the master and reads the updates, SQL_THREAD applies these updates on the slave. Let’s examine the possible values of SBM during different states of the MySQL Slave.

When SBM Value is Null

  • SBM is …
[Read more]
How to Quickly Add a Node to an InnoDB Cluster or Group Replication

Quickly Add a Node to an InnoDB Cluster or Group Replication (Shutterstock)

In this blog, we’ll look at how to quickly add a node to an InnoDB Cluster or Group Replication using Percona XtraBackup.

Adding nodes to a Group Replication cluster can be easy (documented here), but it only works if the existing nodes have retained all the binary logs since the creation of the cluster. Obviously, this is possible if you create a new cluster from scratch. The nodes rotate old logs after some time, however. Technically, if the


 set is non-empty, it means you will need another method to add a new node to a cluster. You also …

[Read more]
How To Fix MySQL Replication After an Incompatible DDL Command

MySQL supports replicating to a slave that is one release higher. This allows us to easily upgrade our MySQL setup to a new version, by promoting the slave and pointing the application to it. However, though unsupported, there are times when the MySQL version of slave deployed is one release lower. In this scenario, if your application has been performing much better on an older version of MySQL, you would like to have a convenient option to downgrade. You can simply promote the slave to get the old performance back.

The MySQL manual says that ROW based replication can be used to replicate to a lower version, provided that no DDLs replicated are incompatible with the slave. One such incompatible command is ALTER USER which is a new feature in MySQL 5.7 and not available on 5.6. :

[Read more]
Showing entries 1 to 10 of 72
10 Older Entries »