I’m going to develop my cluster further and create MariaDB(MySQL) geo-cluster by enabling MariaDB(MySQL) Master-Master Replication. I’ve already deployed MariaDB cluster in the second datacenter (nodes 4, 5 and 6) and now I need to configure Master-Master replication between two MariaDB clusters. The replication will take place between node 1 (first datacenter, we will name it Master1) and node 4 (second datacenter, we will name it Master2).
First of all we need to check a couple of variables that may impact or break replication.
- skip-networking. If
skip-networking=1, the server will limit connections to localhost only, and prevent all remote slaves from connecting. - bind_address. Similarly, if the address the server listens for TCP/IP connections is 127.0.0.1 (localhost), remote slaves connections will fail.
Stop MariaDB on both nodes:
|
1 |
$ service mysql stop |
Master1
Edit /etc/my.cnf.d/server.cnf file. I’ve added under the [mysqld] section:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
######replication###### #Choose ID that will uniquely identify your host. server-id = 61 #Optionally you could set report_host parameter for servers to report each other their hostnames. report_host = Master1 IP #binlog-format = row #the journal of changes log_bin = /var/lib/mysql/mariadb-bin #index file. info about what journal is active and what journals used before log_bin_index = /var/lib/mysql/mariadb-bin.index #the journal of changes received from the replica relay_log = /var/lib/mysql/relay-bin # the same as log_bin_index relay_log_index = /var/lib/mysql/relay-bin.index # Don't write information_schema to binlog binlog-ignore-db = information_schema # ...and don't replicate it replicate-ignore-db = information_schema #With this parameter we are telling to MariaDB which databases to replicate. This parameter is optional. #replicate-do-db = testdb ######replication###### |
Now start MariaDB server.
|
1 |
$ service mysql start |
And create user that will be used for replicating data between our servers. Grant appropriate privileges to the user.
|
1 2 3 |
$ mysql -uroot -p MariaDB [(none)]> create user 'replusr'@'%' identified by 'replusr'; MariaDB [(none)]> grant replication slave on *.* to 'replusr'@'%'; |
Now you need to prevent any changes to the data while you view the binary log position. You’ll use this to tell the slave at exactly which point it should start replicating from.
- Flush and lock all tables by running
FLUSH TABLES WITH READ LOCK. Keep this session running – exiting it will release the lock. - Get the current position in the binary log by running
SHOW MASTER STATUS:
|
1 2 3 4 5 6 |
SHOW MASTER STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | mariadb-bin.000006 | 2443483| | | +--------------------+----------+--------------+------------------+ |
- Record the File and Position details. If binary logging has just been enabled, these will be blank.
- Now, with the lock still in place, copy the data from the Master1 to the Master2. Open second terminal window and use mysqldump:
|
1 2 3 |
$ mysqldump -u root -p testdb > testdb.sql $ scp testdb.sql root@Master2 IP:/root |
- Once the data has been copied, you can release the lock on the Master1 by running UNLOCK TABLES.
|
1 |
UNLOCK TABLES; |
Master2
Edit /etc/my.cnf.d/server.cnf file. I’ve added under the [mysqld] section:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
######replication###### #Choose ID that will uniquely identify your host. server-id = 62 #Optionally you could set report_host parameter for servers to report each other their hostnames. report_host = Master2 IP #binlog-format = row #the journal of changes log_bin = /var/lib/mysql/mariadb-bin #index file. info about what journal is active and what journals used before log_bin_index = /var/lib/mysql/mariadb-bin.index #the journal of changes received from the replica relay_log = /var/lib/mysql/relay-bin # the same as log_bin_index relay_log_index = /var/lib/mysql/relay-bin.index # Don't write information_schema to binlog binlog-ignore-db = information_schema # ...and don't replicate it replicate-ignore-db = information_schema #With this parameter we are telling to MariaDB which databases to replicate. This parameter is optional. #replicate-do-db = testdb ######replication###### |
Now start MariaDB server.
|
1 |
$ service mysql start |
Restore DB from dump we sent from Master1:
|
1 2 |
$cd /root $mysql -u root -p testdb < testdb.sql |
And create user that will be used for replicating data between our servers. Grant appropriate privileges to the user.
|
1 2 3 |
$ mysql -uroot -p MariaDB [(none)]> create user 'replusr'@'%' identified by 'replusr'; MariaDB [(none)]> grant replication slave on *.* to 'replusr'@'%'; |
To start replication enter following commands.
|
1 2 3 |
MariaDB [(none)]> STOP SLAVE; MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='Master1 IP', MASTER_PORT=3308,MASTER_USER='replusr',MASTER_PASSWORD='replusr', MASTER_LOG_FILE='mariadb-bin.000006', MASTER_LOG_POS=2443483; MariaDB [(none)]> START SLAVE; |
For MASTER_LOG_FILE and MASTER_LOG_POS I have used information from “show master status” on the Master1.
Check status information of the slave threads.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: Master1 IP Master_User: replusr Master_Port: 3308 Connect_Retry: 60 Master_Log_File: mariadb-bin.000006 Read_Master_Log_Pos: 2462896 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 19952 Relay_Master_Log_File: mariadb-bin.000006 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 2462896 Relay_Log_Space: 20244 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 61 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative 1 row in set (0.00 sec) |
Note that Read_Master_Log_Pos and Exec_Master_Log_Pos are in sync which is good indicator that our databases are in sync.
Check status information about binary log files of the Master2 node. We will need this information to start replication on Master1 node.
|
1 2 3 4 5 |
SHOW MASTER STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | mariadb-bin.000002 | 390556043| | | |
Master1
Start replicating data from Master2 to Master1 node.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
MariaDB [(none)]> STOP SLAVE; MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='Master2 IP', MASTER_PORT=3308,MASTER_USER='replusr',MASTER_PASSWORD='replusr', MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=390556043; MariaDB [(none)]> START SLAVE; MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: Master2 IP Master_User: replusr Master_Port: 3308 Connect_Retry: 60 Master_Log_File: mariadb-bin.000002 Read_Master_Log_Pos: 390556043 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 539 Relay_Master_Log_File: mariadb-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 390556043 Relay_Log_Space: 831 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 62 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative 1 row in set (0.00 sec) |
Everything seems to be OK.
You can now create a test database (testdb) and check that replication is working!
