Home / Questions / 452889
Accepted
Julian
Asked: 2022-05-19 05:35:06 +0800 CST 2022-05-19 05:35:06 +0800 CST

MySQL - Add a new database to Bidirectional replication

  • 772

These days I was working with MySQL to achieve a bidirectional replication that mainly allows me to synchronize two databases, in this way I solved a large part of the problem that I was facing. However, now I have another one: how can I add a third database to the replication and maintain the bidirectional relationship? (In this case it would become tridirectional, I think).

To pass clean, I have three databases and I need them to be synchronized, I managed to do it with two through a master-master relationship but now I need to incorporate a third database and keep it working in the same way.

From what I saw with the auto_increment, what it currently does is give the even numbers to one DB and the odd ones to the other, how would this work if I incorporate a third DB?

mysql

1 Answers

  1. Best Answer

    I managed to find the solution to this problem, I am going to summarize the steps in a simple way to help possible people who have this problem in the future.

    1.- The first thing is to make sure you have a version equal to or greater than MySQL 5.7, in my case I was working without realizing it with 5.5 and apparently there was not one of the commands needed to carry out this.

    2.- The second step is to make sure that the configuration is adequate for what we are going to do, I leave below the configuration of the "my.ini" file of my server A

    Remember that "server-id" cannot be repeated.

    [mysqld]

server-id = 1
log-bin = mysql-bin.log
binlog-ignore-db=information_schema
replicate-ignore-db=information_schema
relay-log="mysql-relay-log"
auto-increment-increment = 3
auto-increment-offset = 1
binlog_do_db = vla_gestion
master-info-repository=TABLE
relay-log-info-repository=TABLE
log_error = "mysql_error.log"

    In my case, the rest of the servers maintain the same configuration except for "auto-increment-offset", since these values ​​should be: For server B = 2 and for Server C = 3. This will guarantee that the IDs of the rows we add don't collide when synchronizing.

    At this point it is recommended to restart the MySQL service.

    3.- The third step is to create the users on each machine, it would be best to leave everyone with the same name and password to avoid confusion.

    CREATE USER 'replicator'@'%' IDENTIFIED BY '[replicator_password]';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%' IDENTIFIED BY '[replicator_password]';

    4.- Now comes the most difficult part of the replication, we must connect all the databases to each other. I am going to explain conceptually what we should do and then I will show you the code. The way we are going to connect the 3 databases is through something MySQL calls "channels". With this we will be able to connect to more than one user simultaneously from the same server. Therefore I am going to exemplify with server A, which should connect to C and B.

    The first thing would be to stop our slave.

    stop slave;

    Now we are going to connect to server B, for this I must check the status of the master in said server. So on server B we are going to run this line:

    show master status

    With this information we are going to return to server A and we are going to use all this statement to make the connection

    CHANGE MASTER TO MASTER_HOST = 'ip_servidor', 
MASTER_USER = 'usuario', 
MASTER_PASSWORD = 'constraseña', 
MASTER_LOG_FILE = 'mysql-bin.000001', 
MASTER_LOG_POS = 107 
FOR CHANNEL 'master-2'

    Make sure you put the ip, username and password correctly. In "MASTER_LOG_FILE" and in "MASTER_LOG_POS" we are going to complete it with the information provided by the statement made on server B.

    Finally we raise the slave again with:

    start slave;

    Remember the importance of assigning a channel , in this way when we connect to another machine what we will do is change the channel, to 'master-3' for example. So we do not lose the previous connection.

    To finish, what would be missing would be to make sure that all the connections are made, in this case they would be: A with B and C, B with A and C, C with A and B.

    And voila, this would be all to have a master-master-master synchronized database in MySQL. I hope everything is clear.

    • 1