Replication

In MariaDB master/slave replication there is a feature called attribute promotion/demotion.

Simply put, it is about how the slave behaves or should behave if the master and slave have different column definitions or even a different number of columns or a different sequence of columns.

Use case of the customer

This week we discussed with a customer the case of how he could perform a rolling schema upgrade (RSU) in a Galera cluster.

With previous schema changes he has always had problems, which has led to a …

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:

SQL> SHOW GLOBAL VARIABLES LIKE '%parallel%';
+-------------------------------+------------+
| …

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.

Creating database accounts for the MaxScale Load Balancer

The MaxScale load balancer connects itself with the application users to the 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:

if one runs DML on a table that has no indexes, a full table scan is done. with RBR, the slave might need to scan the full table for each row changed.

The consequence of this behaviour is that the Slave starts lagging. It was further mentioned: …

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

A normal MariaDB Replication is a Pull Replication. This means that a Slave connects to its Master and gathers or better requests Binary Log information from the Master and applies them in a streaming way.

In some set-ups the Slave is …

Table of 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
  • Testing of new Character Set
• MySQL Pump
• MySQL Master/Slave Replication for Character Set conversion
• MySQL Shell, mysqlsh Upgrade Checker Utility

Introduction

Recently we had a consulting engagement where we had to help the customer to migrate from latin1 Character Set to …

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.

The main problem in a Master/Master Replication set-up is to make the service highly available for the application …

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 …

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.

The replication filtering rules vary depending on the binary log format (ROW and

STATEMENT) See …

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.

I set up two servers with identical table definitions and changed the character set on one column on the slave from latin1 to utf8.

Master:

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL …

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 …

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.

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.

The process was designed as follows:

Preparation of Proof of Concept (PoC)

To simulate all cases …

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

SQL> GRANT SUPER ON userdb.* TO root@127.0.0.111;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

2014-12-09 14:53:55 7457 [Warning] Did not write failed 'GRANT SUPER ON userdb.* TO root@127.0.0.111' into binary log while granting/revoking privileges in databases.
2014-12-09 14:53:55 7457 [ERROR] Slave SQL: Error 'Incorrect usage of DB GRANT and GLOBAL PRIVILEGES' on query. Default …

Global Transaction ID (GTID) is one of the major features that were introduced in MySQL 5.6 which provides a lot of benefits. I have talked about the GTID concept, implementation and possible troubleshooting at Percona Live London 2014, you can download the slides from our presentations repository or from my session at Percona Live.

On the other hand, there are some important things you should consider before deploying GTID in production, I’m going to list them here in this blog post.

Table of …

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). In this article I’ll summarize how to troubleshoot MySQL replication for the most common issues we might face with a simple comparison how can we get them solved in the different replication methods (Classic VS GTID).

There are two main operations we might need to do in a replication setup:

• Skip or ignore a statement that …

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:

• An unstable network between two Galera Cluster locations.
• A separation of a reporting slave and the Galera Cluster so that heavy reports on the slave do not affect the Galera Cluster performance.
• Mixing different sources in a slave or a Galera Cluster (fan-in replication).

This article is based on earlier research work (see MySQL Cluster - Cluster …

In a previous post I was talking about How to Setup MySQL Replication using the classic method (based on binary logs information). In this article I’ll go through the transaction-based replication implementation using GTID in different scenarios.

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 …

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.

MySQL …

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:

• Installation of your database from different Repositories.
• Configuration of your database instance.
• Track configuration changes of your database instance. …

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:

• No slave lag
• No lost transactions
• Read scalability and write …

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.

To find those inconsistencies or differences we need 2 tools from the Percona Toolkit: pt-table-checksum and pt-table-sync.

Requirements

The following requirements have to …

Table of Contents

• Accounts not properly closing connections
• Unused indexes
• Who created temporary (disk) tables
• Accounts which never connected since last start-up
• Users which never connected since last start-up
• Totally unused accounts (never connected since last restart and not used to check Stored Program or View privileges) since last start-up
• SHOW FULL PROCESSLIST
• Storage Engines per schema
• Tables without a Primary Key
• Bad SQL queries of users
• SHOW PROFILE in PERFORMANCE_SCHEMA
• SELECT, INSERT, UPDATE and …

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:

• The InnoDB double write buffer (innodb_doublewrite) should not be disabled anymore for Galera when using v2.0 and higher!!! The reason for this is: When MySQL crashes InnoDB pages might get corrupted during the crash. They would be fixed by the blocks from the double write buffer during auto-recovery. But if the double write buffer is disabled they …

There is a product out in the wild which is called SchoonerSQL
[ 1
] from an American company called Schooner Information Technology
[ 2
]. They claim to have top-performance
[ 3
] and high-availability features with up 99.999% availability
[ 1
].
The technology seems to be based on InnoDB, SSD and some kind of replication with failover mechanism.

Unfortunately this product is neither open source nor is it really transparent what they are doing. Which is quite suspicious… Because this lack of …

On Tuesday, September 4, 2012 the next DOAG SIG MySQL meeting will take place in Hamburg (Germany) with the topic Replication.

Possible presentations are: MySQL Replication, Galera Cluster, Replication with Oracle GoldenGate, Replication with Zimory Scale and Tungsten Replicator.

The event will presumably be located at the Hotel Böttcherhof at Wöhlerstrasse 2.

Introduction

Galera is a synchronous Multi-Master Replication for MySQL. It is therefore in competition with several other MySQL architectures:

• Master-Master Replication with MySQL
• MySQL Cluster
• The non-open source product called Schooner

Very often they can be easily replaced by Galera’s synchronous Multi-Master Replication for MySQL.

All those products have some advantages and disadvantages. Very often MySQL Master-Master Replication is used in the field because of its simplicity to set-up. But …

Introduction

MySQL/Galera synchronous Multi-Master Replication consists of 2 parts:

• The wsrep patches for MySQL (codership-mysql) and
• the Galera Replication Plugin (galera).

If you do not want to download the prepared binaries you can build it on you own.
First you have to download the native MySQL sources, then patch it with the Galera wsrep patches and compile it. In a second step you have to build the Galera Plugin.

This is especially useful because in the standard Galera binary tar balls the garbd …

Galera features

Galera provides the following features:

• 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 row level replication
• Direct client connections
• Drop-in replacement for native MySQL

Galera benefits

Benefits using Galera Replication:

• High Availability
• No slave lag
• No lost transactions
• No more data inconsistency
• Smaller client latencies
• Read scalability …

A customer rose a support case with a problem on his Master-Master set-up. The 2^nd Master claims to have a problem:

master2> SHOW SLAVE STATUS<br>G
                ...
    Master_Log_File: master1-bin.000014
Read_Master_Log_Pos: 97975045
   Slave_IO_Running: No
  Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 97975045
      Last_IO_Errno: 1236
      Last_IO_Error: Got fatal error 1236 from master when reading data from
                     binary log: 'Client requested master to start replication …

A few days ago I had to deal with MySQL Cluster replication. I did not do this for a while so I was prepared to expect some interesting surprises once again.

For those who MySQL Cluster - Cluster circular replication is the daily business they may skip this article. For all the others they possibly can profit from our learnings.

I am talking about the following MySQL Cluster set-up:

More detailed information about such set-ups you can find in the MySQL Cluster documentation.

Situations that lead to a …

FromDual is a support partner of MariaDB plc, Codership Oy and Oracle Corporation. This gives us the opportunity to offer you customized enterprise support subscriptions for your mission-critical MariaDB, Galera or MySQL databases for all three vendors.

Alternatively, we can offer you our remote DBA services (without SLA) or our MariaDB, Galera and MySQL trainings.

MariaDB and MariaDB Cluster Support Subscriptions

• MariaDB Enterprise support subscription: 7x24 support subscription, duration: 1 to 3 years, …

Introduction

One of the features that make MySQL so great is its easy replication set-up. If you are experienced and know-how to do it, it takes you about 15 minutes to set-up a slave. What you have in the end is a replication from one master to one or several slaves. So you can build a top-down data stream pyramid and spread your data on many slaves.

From time to time some customers are asking for the other way: Many masters replicating to one slave (which is also called multi-source replication). For …

Electing a slave as new master and aligning the other slaves to the new master

In a simple MySQL Replication set-up you have high-availability (HA) on the read side (r). But for the master which covers all the writes (w) and the time critical read (rt) there is no HA implemented. For some situations this can be OK. For example if you have rarely writes or if you can wait until a new Master is set up.

But in other cases you need a fast failover to a new master.

In the following article it is shown how to …

According to Johans wishes I write down my concerns about MySQL (Cluster) replication. These items are things I run again and again into it with customers:

• SQL-nodes are still loosing too easy connection to cluster after data node or management node restart (which leads into gaps, see next point). Automatic failover or reconnection is just a dream (maybe it works in about 90% of the cases at least).
• Gaps: Whenever I do a cluster reconfiguration (should not be necessary too often, see loosing connection …

MySQL provides its replication for High Availability (HA) and for read Scale-out. Generally it is known that in a MySQL replication you can only replicate from one Master to many slaves. In this paper it is shown how a set-up can look like to replicate from two masters to one slave.

Caution: Handle this information with care!!!