1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.