T
T
tzn2021-11-19 17:47:44
MySQL
tzn, 2021-11-19 17:47:44

MySQL multi-master-master-master?

Good day.
I can not collect 3 master servers in a heap. Who faced, tell me where I'm wrong

SERVER_1

nano /etc/mysql/mysql.conf.d/mysqld.cnf

server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
auto-increment-increment = 2
auto-increment-offset = 1
bind-address = SERVER_1


sudo service mysql restart

mysql -u root -p

create user 'replication_user'@'SERVER_2' identified by 'pass';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'SERVER_2';
FLUSH PRIVILEGES;

SHOW MASTER STATUS;


File=mysql-bin.SERVER_1
Position=POSTION_SERVER_1


STOP SLAVE;
CHANGE MASTER TO master_host='SERVER_2', master_port=3306, master_user='replication_user', master_password='pass', master_log_file='mysql-bin.SERVER_2', master_log_pos=POSTION_SERVER_2;
START SLAVE;


SERVER_2

nano /etc/mysql/mysql.conf.d/mysqld.cnf

server_id = 2
log_bin = /var/log/mysql/mysql-bin.log
auto-increment-increment = 2
auto-increment-offset = 2
bind-address = SERVER_2


sudo service mysql restart

mysql -u root -p

create user 'replication_user'@'SERVER_3' identified by 'pass';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'SERVER_3';
FLUSH PRIVILEGES;

SHOW MASTER STATUS;


File=mysql-bin.SERVER_2
Position=POSTION_SERVER_2


STOP SLAVE;
CHANGE MASTER TO master_host='SERVER_3', master_port=3306, master_user='replication_user', master_password='pass', master_log_file='mysql-bin.SERVER_3', master_log_pos=POSTION_SERVER_3;
START SLAVE;


SERVER_3

nano /etc/mysql/mysql.conf.d/mysqld.cnf

server_id = 3
log_bin = /var/log/mysql/mysql-bin.log
auto-increment-increment = 2
auto-increment-offset = 3
bind-address = SERVER_3


sudo service mysql restart

mysql -u root -p

create user 'replication_user'@'SERVER_1' identified by 'pass';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'SERVER_1';
FLUSH PRIVILEGES;

SHOW MASTER STATUS;


File=mysql-bin.SERVER_3
Position=POSTION_SERVER_3


STOP SLAVE;
CHANGE MASTER TO master_host='SERVER_1', master_port=3306, master_user='replication_user', master_password='pass', master_log_file='mysql-bin.SERVER_1', master_log_pos=POSTION_SERVER_1;
START SLAVE;


a5de278410e6a7d82c29445c3742085a.jpg

It seems like I close the ring ... but the error is the same

10 [ERROR] [MY-010584] [Repl] Slave I/O for channel '': error connecting to master '[email protected]' - retry-time: 60 retries: 16 message: Host 'HOST' is not allowed to connect to this MySQL server, Error_code: MY-001130

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
Alexander Karabanov, 2021-11-20
@karabanov

You created a user on SERVER_1'replication_user'@'SERVER_2' , and then you try to connect to it from SERVER_3 , but there is no user 'replication_user'@'SERVER_3'on SERVER_1 .
In order not to get confused, make one user everywhere, but replace the last IP octet with a wildcard, for example 'replication_user'@'10.10.10.%'.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question