I have the following setup in my environment.
Galera cluster with 2 nodes: db01
and db02
.
Server with galera load balancer installed: glb01
, applications/clients connect to the cluster via port 8010 of glb01
.
Now I would like to setup a backup server db03
and configure master (galera cluster) <--> slave replication.
I followed the link for the configuration.
/etc/my.cnf.d/server.cnf
mysqld unit in db01
:
# Galera node as master
wsrep_gtid_mode = on
wsrep_gtid_domain_id = 0
server-id = 01
log_slave_updates = on
log-bin = /var/log/mysql/master-bin
log-bin-index = /var/log/mysql/master-bin.index
gtid_domain_id = 1
/etc/my.cnf.d/server.cnf
mysqld unit in db02
:
# Galera node as master
wsrep_gtid_mode = on
wsrep_gtid_domain_id = 0
server-id = 01
log_slave_updates = on
log-bin = /var/log/mysql/master-bin
log-bin-index = /var/log/mysql/master-bin.index
gtid_domain_id = 2
/etc/my.cnf.d/server.cnf
mysqld unit in db03
:
#Replication slave to Galera Cluster
server-id = 02
relay-log-index = /var/log/mysql/slave-relay-bin.index
relay-log = /var/log/mysql/slave-relay-bin
gtid_domain_id = 99
log-bin = /var/log/mysql/slave-bin
log-bin-index = /var/log/mysql/slave-bin.index
binlog_format = mixed
Steps:
1.) In galera cluster master node create replicant
user.
2.) Backup a database testdb
from master node then copy to db03
and restore it.
3.) Check the gitd_binlog_pos
from master node.
show variables like 'gtid_binlog_pos'; # <--- result from db01
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| gtid_binlog_pos | 0-1-2 |
+-----------------+-------+
show variables like 'gtid_binlog_pos'; # <--- result from db02
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| gtid_binlog_pos | 0-1-61 |
+-----------------+-------+
4.) Run the slave in db03
.
set global gtid_slave_pos = "0-1-2"; # <--- I'm using the gtid_binlog_pos from db01
change master to master_host=glb01, # <--- using load balancer host to avoid single point of failure
master_user='replicant',
master_password='password',
master_port=8010,
master_connect_retry=10,
master_use_gtid=slave_pos;
start slave;
5.) Test the master-slave replication. Use mariadb client connect to glb01:8010
and insert data to testdb
. New data can be found in db03
. Seems like everything working.
6.) Test failover by shutting down db01
. Then connect to glb01:8010
and insert new data, however, new data is not in sync in db03
this time. Run show slave status
gives the following error:
Slave_IO_State: Waiting for master to send event
Master_Host: glb01
Master_User: replicant
Master_Port: 8010
Connect_Retry: 10
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 1034
Relay_Log_File: slave-relay-bin.000003
Relay_Log_Pos: 629
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Could not execute Write_rows_v1 event on table testdb.table1; Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin.000002, end_log_pos 533
Skip_Counter: 0
Exec_Master_Log_Pos: 329
Relay_Log_Space: 2097
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Could not execute Write_rows_v1 event on table testdb.table1; Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin.000002, end_log_pos 533
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: Slave_Pos
Gtid_IO_Pos: 0-1-62
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: optimistic
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 2
Any hints would be greatly appreciated.