MySQL 5.7 and 8.0 have an issue of stopping replication on all slaves.
A colleague brought an article to my attention. I did not see it on Planet MySQL where I get most of the MySQL news (or it did not catch my eye there). As it is interesting replication stuff, I think it is important to bring it to the attention of the MySQL Community, so I am writing this short post.
The surprising part for me is that it uses my 4-year-old work for online migration to GTID
The goal of this tutorial is to show you how to use multi-master to aggregate databases with the same name, but different data from different masters, on the same slave.
Example :
- master1 => a French subsidiary
- master2 => a British subsidiary
Both have the same database PRODUCTION but the data are totally different.
This screenshot is made from my own monitoring tool:
PmaControl. You have to read 10.10.16.232 on master2 and not
10.10.16.235.
The fault of my admin system! :p)
We will start with three servers—2 masters and 1 slave—you can add more masters if needed. For this tutorial, I used Ubuntu 12.04. I’ll let you choose the right procedure for your distribution from Downloads.
Scenario
- …
MySQL supports three types for binlog format. For safer binlog based replication its recommended to use ROW based replication. But even though in some worst cases this leads to data inconsistency. Later MySQL came up with the concept of GTID (global transaction identifiers) which generates the unique binlog entries to avoid any data inconsistency. This …
The post MySQL GTID vs MariaDB GTID appeared first on SQLgossip.
I was recently asked a question by mail about MySQL Lossless Semi-Synchronous Replication. As I think the answer could benefit many people, I am answering it in a blog post. The answer brings us to the internals of transaction committing, of semi-synchronous replication, of MySQL (server) crash recovery, and of storage engine (InnoDB) crash recovery. I am also debunking some misconceptions that I have often seen and heard repeated by many. Let’s start by stating one of those misconceptions.
One of those misconceptions is the following (this is NOT true): semi-synchronous enabled slaves are always the most up-to-date slaves (again, this is NOT true). If you hear it yourself, then please call people out on it to avoid this spreading more. Even if some slaves have semi-synchronous replication disabled (I will use semi-sync for …
[Read more]
A replication user is necessary to set up the relationship
Primary
/Replica
. This is a short step
but it needs a bit more of attention.
From the MySQL 5.7 documentation (highlights are my own):
Although you do not have to create an account specifically for replication, you should be aware that the replication user name and password are stored in plain text in the master info repository file or table (see Section 16.2.4.2, “Slave Status Logs”). Therefore, you may want to create a separate account that has privileges only for the replication process, to minimize the possibility of compromise to other accounts.
The following command specifically will allow replication from all databases and tables connecting from all hosts. For security reasons you may want to limit access to replication only to the IP address of the server doing the …
[Read more]Replication is one of the well-known features that allows us to build an identical copy of a database. It is supported in almost every RDBMS. The advantages of replication may be huge, especially HA (High Availability) and load balancing. But what if we need to build replication between 2 heterogeneous databases like MySQL and PostgreSQL? Can we continuously replicate changes from a MySQL database to a PostgreSQL database? The answer to this question is pg_chameleon.
For replicating continuous changes, pg_chameleon uses the mysql-replication library to pull the row images from MySQL, which are transformed into a jsonb object. A pl/pgsql function in postgres decodes the jsonb and replays the changes into the postgres database. In order to setup this type of replication, your mysql binlog_format must be “ROW”.
A few points you should know before setting up this …
[Read more]In this post we will see a case study of a Galera Cluster migration to AWS Aurora and quick solution to the replication issue. A friend received an error in…
The post Galera cluster to AWS Aurora migration & HA_ERR_FOUND_DUPP_KEY first appeared on Change Is Inevitable.
MySQL replication has evolved a lot in 5.6 ,5.7 and 8.0. This presentation focus on the changes made in parallel replication. It covers MySQL 8.0. It was presented at Mydbops database meetup on 04-08-2016 in Bangalore.
In this blog post, we’ll discuss how to set a replication from MySQL 8.0 to MySQL 5.7. There are some situations that having this configuration might help. For example, in the case of a MySQL upgrade, it can be useful to have a master that is using a newer version of MySQL to an older version slave as a rollback plan. Another example is in the case of upgrading a master x master replication topology.
Officially, replication is only supported between consecutive major MySQL versions, and only from a lower version master to a higher version slave. Here is an example of a supported scenario:
5.7 master –> 8.0 slave
while the opposite is not supported:
8.0 master –> 5.7 slave
In this blog post, I’ll walk through how to overcome the …
[Read more]