In this blog series, we will discuss various MySQL NDB Cluster replication topologies possible with a demonstration. We will start with a simple case i.e. one source (formerly called master) and one replica (formerly called slave).

Note: With MySQL version (8.0.21), we have started changing the term “master” to “source”, the term “slave” to “replica”. So in this blog we will refer these terms ‘source’ and ‘replica’ wherever applicable.

Let’s create two MySQL NDB Cluster with the following environment, from which one will be termed as ‘source’ cluster while the other one will be ‘replica’ cluster. For now, let’s stick to the identical environment from both the cluster. Later in the following blog series, we will change the environments and will run the replication.

• MySQL NDB Cluster version (Latest GA version)
• 1 Management node
• 4 Data nodes …

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 …

From MySQL 5.7, we had a Multi-threaded Slave (MTS) Applier mechanism called LOGICAL_CLOCK to overcome the problems of parallel replication within a database.

To further improve the parallelisation mechanism, from MySQL 8 (5.7.22) we have write-set replication, so before going further , lets look at the difference between Logical clock (LC) and Writeset.

LOGICAL_CLOCK

Transactions that are part of the same binary log group commit on a master are applied in parallel on a slave. The dependencies between transactions are tracked based on their timestamps to provide additional parallelisation where possible.

WRITESET

Write-set is a mechanism to track independent transactions that can be executed in parallel in the slave. Parallelising on write sets has potentially much more parallelism than logical_clock ,since it does not depend …

MySQL Replication is a very common topology, widely used in various architecture.
People use it, among others, for High Availability, Read Scalability or Geographic Redundancy.

Another use case is to use MySQL Replication to seamlessly integrate a newer version of the server in your architecture.
Let’s say you are running MySQL 5.7 then you can easily setup a 8.0 instance as a replica of your 5.7.

Extending this idea it is also possible to replicate your MySQL 5.7 (or 8.0 obviously) to a MySQL Database Service instance, the true MySQL PaaS on Oracle Cloud Infrastructure.

The post Replicate from MySQL 5.7 to MySQL Database Service first appeared on dasini.net - Diary of a MySQL experts.

This is a simple article with an examples for “how to configure replication filters for channel”

Starting from MySQL 8.0 replication filters can be global or channel-specific, enabling you to configure multi-source replicas with replication filters on specific replication channels. Channel specific replication filters are particularly useful in a multi-source replication topology when the identical database or table is present on multiple sources, and the replica is only required to replicate it from one source.

Example:

CHANGE REPLICATION FILTER REPLICATE_DO_DB =(test,test3,test4 ), REPLICATE_REWRITE_DB = ((test1,test3),(test2,test4)) FOR CHANNEL "node1";

my.cnf confiration to make replication filter setting persistent:

replicate-do-db=node1:test
replicate-do-db=node1:test3
replicate-do-db=node1:test4
replicate-rewrite-db=node1:test1->test3 …

MySQL Ndb Cluster provides durability for data by default via logging and checkpointing.

In addition, users can take backups at any time, which allows for disaster recovery, replication synchronisation, data portability and other use cases.

This post looks at the backup and restore mechanisms in MySQL Ndb Cluster.

MySQL Ndb Cluster architecture recap

MySQL Ndb Cluster is a distributed SQL relational database :

• Designed for low overhead read + write scale out, high availability, high throughput and low latency.
• Providing distributed parallel joins, transactions, row locks, foreign keys.
• Data is primarily stored and managed by a set of independent data node processes.
• Data is accessed via distributed MySQL servers and …

MySQL 8.0.22 was released roughly a week ago. It includes some nice additions to replication that we would like to call out. Here they are:

• Automatic Asynchronous Replication Connection Failover (WL#12649). This work, by Hemant Dangi, implements a mechanism in asynchronous replication that makes the replica automatically try to re-establish an asynchronous replication connection to another replication source, in case the current connection gets interrupted;
• New terminology for replica related statements (WL#14171).

… Tweet Share

The amount of transactions committed is an important information, but how could you return an accurate value?

This is a question I got from my colleague Ivan, he was challenging with global status values like COM_COMMIT or HANDLER_COMMIT, then checking in innodb_metrics… but this was not accurate.

In fact depending which storage engine you are using, if binary logs are enabled, if you rollback transactions, if you are using auto_commit, etc… all those parameters influence those values.

So the first question was “What metrics or else should we use ?”. In my opinion, the most accurate “counter” for transactions are the GTIDs.

And this is why I created yet another MySQL Shell plugin that does that calculation:

This plugin is available on my GitHub repository …

This post was first published on Oracle MySQL Blog.

If you are running MySQL on premise, it’s maybe the right time to think about migrating your lovely MySQL database somewhere where the MySQL Team prepared a comfortable place for it to stay running and safe.

This awesome place is MySQL Database Service in OCI. For more information about what MDS is and what it provides, please check this blog from my colleague Airton Lastori.

One important word that should come to your mind when we talk about MDS is SECURITY !

Therefore, MDS endpoint can only be a private IP in OCI. This means you won’t be able to expose your MySQL …

Today we will see how to use MySQL Database Service aka MDS with WordPress.

To achieve this easy task, we will use the architecture we already deployed in this article.

We have then two Compute Instances on OCI, 1 running WordPress (Apache and PHP) and one running MySQL 8.0.

The Plan

This is how we will proceed to migrate to MDS with minimal maintenance time, we will:

1. create a MDS instance
2. verify if the database is ready to act as replication source
3. dump the MySQL instance running on OCI for being migrated to MDS.
4. load the dump in MDS
5. create a user dedicated to the replication
6. create a replication channel on MDS (from OCI to MDS)
7. modify WordPress config to point to MDS

Create a MDS …