replication

Due to an application error, our replication stopped for 5 days (over Easter). After the problem was solved, the replication was supposed to catch up, which turned out to be very slow. All the usual tricks (innodb_flush_log_at_trx_commit, sync_binlog, etc.) had already been exhausted. So we tried our hand at parallel replication of the MariaDB server.

Parallel replication is deactivated by default:

Aufgrund eines applikatorischen Fehlers ist unsere Replikation während 5 Tagen stehen geblieben (über Ostern). Nachdem das Problem gelöst war, sollte die Replikation aufholen, was sich als sehr zäh herausstellte. All die üblichen Tricks (innodb_flush_log_at_trx_commit, sync_binlog, etc.) wurden bereits ausgereizt. Also haben wir uns an der parallelen Replikation des MariaDB Servers versucht.

Per default ist die parallel Replikation deaktiviert:

For this how-to we were working with a MariaDB 10.6.5 database server and the newest MariaDB MaxScale 6.3.0 (aka 2.6) load balancer/proxy.

As a starting point we had a working MariaDB Master/Master replication with one active Master (M_a) and one passive Master (M_p). Whereas the passive Master was set to read_only. As a replication user we used the user replication.

This week I came across an old known issue reported in May 2010: Master/Slave Replication with binlog_format = ROW and tables without a Primary Key is a bad idea! Especially if these tables are huge.

Why this is a bad idea is described in the bug report #53375:

Table of Contents

• How to make MariaDB Pull Replication as secure as possible
• MariaDB Push Replication
• Pushing data with the FederatedX Storage Engine and Triggers
• Traffic mirroring with MariaDB MaxScale or ProxySQL

How to make MariaDB Pull Replication as secure as possible

Contents

• Introduction
• Character Sets
• Steps to convert Character Set to utf8mb4
  • Analyzing the Server
  • Analyzing the Application and the Clients
  • Preparation of the Server Settings and the Application
  • Convert Tables to utf8mb4

Important: FromDual does NOT recommend to use an automated VIP failover technology as described below for a MariaDB/MySQL Master/Master Replication. In such a topology we recommend a manual VIP failover with prepared scripts!

Some of our customers still want to have old-style MariaDB Master/Master Replication Clusters. Time goes by, new technologies appear but some old stuff still remains.

Most (relational) DBAs and DB application developers know the concept of a primary key ("PK") and what it is good for. However, much too often one still encounters table definitions without a PK. True, the relational theory based on sets does not need a PK, and all operations (insert, select, update, delete) can also be done on tables for which no PK was defined. If performance doesn't matter (or the data volume is small, a typical situation in tests), the lack of a PK does not immediately cause negative consequences.

From time to time we see in customer engagements that MySQL Master/Slave replication is set-up doing schema or table level replication filtering. This can be done either on Master or on Slave. If filtering is done on the Master (by the binlog_{do|ignore}_db settings), the binary log becomes incomplete and cannot be used for a proper Point-in-Time-Recovery. Therefore FromDual recommends AGAINST this approach.

Recently I was working on a clients question and stumbled over an issue with replication and mixed character sets. The client asked, wether it is possible to replicate data to a table on a MySQL slave, where one column had a different character set, than the column in the same table on the master.

Most of you know, that it is possible to synchronize MySQL and MariaDB servers using replication. But with the latest releases, it is also possible to use more than just two servers as a multi-master setup.

Most of you know that both MySQL and MariaDB support replication in a hierarchical master-slave-setup, to propagate changes across all connected servers.

But with the latest releases, a slave can have more than one master.

The keyword: Multi-Source replication

It is supported from MySQL 5.7 and MariaDB 10.0 on, and this article describes how to set it up.

A MySQL customer of FromDual has different manufacturing plants spread across the globe. They are operated by local companies. FromDuals customer wants to maintain the manufacturing receipts centralized in a MySQL database in the Head Quarter in Europe. Each manufacturing plant should only see their specific data.

Manufacturing log information should be reported backup to European Head Quarter MySQL database.

We have recently seen a case where the following command was executed on a Galera Cluster node:

Global Transaction ID (GTID) is one of the major features that were introduced in MySQL 5.6 which provides a lot of benefits.

In previous posts, I was talking about how to set up MySQL replication, Classic Replication (based on binary logs information) and Transaction-based Replication (based on GTID).

Sometimes it could be desirable to replicate from a Galera Cluster to a single MySQL slave or to an other Galera Cluster. Reasons for this measure could be:

The following topics will be covered in this blog:

• What is the concept of GTID protocol?
• GTID Replication Implementation
  • Fresh Installations
  • Adding New Slave
• Migration from classic replication to GTID replication
  • How to perform the migration?
  • Is online migration from classic to GTID replication available?
  • Workaround ??
• GTID Benefits

It's not usual to find an easy source on how to setup MySQL replication, I thought it might be useful at least for the beginners to write a direct and simple howto blog on setting up Master/Slave replication in MySQL using the classic method (binary log information). Check out my post GTID In Action for information about transaction-based replication using GTID.

Before going through the replication setup steps, I think it's better to explain first how Replication works in MySQL.

During and after Henriks great talk about Galera Cluster at the FrOSCon 2012 in St. Augustin we found 2 important things related to Galera Cluster for MySQL: