You are here

replication

MariaDB's parallel replication to catch up

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:

MariaDBs parallele Replikation zum Aufholen

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:

MariaDB MaxScale Load Balancer with Master/Master Replication

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 (Ma) and one passive Master (Mp). Whereas the passive Master was set to read_only. As a replication user we used the user replication.

Learning from the Bugs Database

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:

MariaDB Master/Master GTID based Replication with keepalived VIP

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.

How the Lack of a Primary Key May Effectively Stop the Slave

Taxonomy upgrade extras: 

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.

MySQL replication with filtering is dangerous

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.

Why is varchar(255) not varchar(255)?

Taxonomy upgrade extras: 

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.

Replication in a star

Taxonomy upgrade extras: 

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.

Binlog format MIXED with filtering

Binlog format MIXED changes the binary log format (ROW or STATEMENT) depending on the queries (deterministic or not). This makes it impossible to define 100% correctly working binary log filter rules.

Controlling worldwide manufacturing plants with MySQL

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.

gtid_replication_customer.png

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

failed MySQL DDL commands and Galera replication

Taxonomy upgrade extras: 

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

Things you should consider before using GTID

Taxonomy upgrade extras: 

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

Replication Troubleshooting - Classic VS GTID

Taxonomy upgrade extras: 

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).

Replication channel failover with Galera Cluster for MySQL

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:

How to Setup MySQL Master/Slave Replication ?

Taxonomy upgrade extras: 

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.

FromDual Ops Center for MariaDB and MySQL (focmm)

The FromDual Ops Center for MariaDB and MySQL (focmm) is a browser-based Graphical User Interface (GUI) to operate and manage your MariaDB and MySQL database farms (2 - 99 instances) more easily.
If further provides tools to implement your own Database as a Service (DBaaS) infrastructure.


FromDual Ops Center covers the full life-cycle of a database:

Undefined

Galera Cluster for MySQL

Features

Galera is a synchronous multi-master replication Cluster for MySQL/InnoDB databases, having features like:

  • Synchronous replication
  • Active-active multi-master topology
  • Read and write to any cluster node
  • Automatic membership control, failed nodes drop from the cluster
  • Automatic node joining
  • True parallel replication, on row level
  • Direct client connections, native MySQL look & feel

Benefits

The benefits of these features are:

Undefined

Check and fix MySQL Replication inconsistencies

There are many different possibilities how you can achieve inconsistencies between Master and Slave in a MySQL replication, intentional and non-intentional ones. How to achieve them is not the topic of this article.

What we want to know is how can we detect inconsistencies or differences between Master and Slave and how can we fix them.

Undefined

Galera Cluster discussions at FrOSCon 2012

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:

SchoonerSQL

There is a product out in the wild which is called SchoonerSQL [ 1 ] from an American company called Schooner Information Technology [ 2 ].

Undefined

Pages

Subscribe to RSS - replication