How to set up multi-master slave in MySQL
Today, We will see how to set up a multi-master slave in the MySQL replication process. Read more: Replication with Different Master and Slave Storage Engines
Also Read: Reset ( Re-Sync ) MySQL Master-Slave Replication
MariaDB 10 supports multi-source replication, and each MariaDB Galera node can have up to 64 masters connected to it. So it is possible to use a MariaDB Cluster as an aggregator for many single-instance MariaDB master servers.
Step 1: Open the MySQL configuration file
vi /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
#datadir=/var/lib/mysql
datadir=/home/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
server-id=multi1
Step 2: Note the bin log file name and their position from all the master databases and write them somewhere separately.
Configure master connection for each replication stream, distinguish by default_master_connection session variable:
MariaDB> SET @@default_master_connection='mariadb1';
MariaDB> CHANGE MASTER 'mariadb1' TO MASTER_HOST='10.0.0.71', MASTER_PORT=3306, MASTER_USER='slave', MASTER_PASSWORD='slavepassword', MASTER_USE_GTID=slave_pos;
MariaDB> SET @@default_master_connection='mariadb2';
MariaDB> CHANGE MASTER 'mariadb2' TO MASTER_HOST='10.0.0.72', MASTER_PORT=3306, MASTER_USER='slave', MASTER_PASSWORD='slavepassword', MASTER_USE_GTID=slave_pos;
MariaDB> SET @@default_master_connection='mariadb3';
MariaDB> CHANGE MASTER 'mariadb3' TO MASTER_HOST='10.0.0.33', MASTER_PORT=3306, MASTER_USER='slave', MASTER_PASSWORD='slavepassword', MASTER_USE_GTID=slave_pos;
Step 3: Start all slaves:
MariaDB> START ALL SLAVES;
MariaDB> SHOW WARNINGS;
+-------+------+--------------------------+
| Level | Code | Message |
+-------+------+--------------------------+
| Note | 1937 | SLAVE 'mariadb2' started |
| Note | 1937 | SLAVE 'mariadb3' started |
| Note | 1937 | SLAVE 'mariadb1' started |
+-------+------+--------------------------+Step 4: Check the Multi Slave Status
MariaDB> SHOW ALL SLAVES STATUS\G
