#StackBounty: #mysql #mariadb #mysql-replication #mysql-cluster #galera MySQL Cluster -> Galera MariaDB Cluster Replication not work…

Bounty: 50

I am setting up a database migration between a 2-node master cluster running MySQL Cluster 7.2 (MySQL 5.5) and a 2-node slave cluster (plus arbitrator) running MariaDB 10.3 with Galera clustering. I currently have all but one of the Galera cluster’s nodes turned off to simplify the choreography.

Configuration appears correct, and everything in SHOW ALL SLAVES STATUS G appears to indicate successful replication. Read_ and Exec_Master_Log_Pos match each other and what the master server reports, no errors are logged, etc. Indeed, adding a database to the master node we’re replicating from will create a database in the slave node. However, none of the data rows are being replicated.

The only oddity in mariadb.err is as follows:

2019-04-11 18:31:19 101 [Warning] Master '[redacted]': Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Internal MariaDB error code: 1193

Master Replication Config:

server-id=13
binlog-format = row
log-bin=mysql-bin
sync_binlog = 1

Slave Replication Config:

server-id               = 1
log_bin                 = /var/log/mysql/mariadb-bin
log_bin_index           = /var/log/mysql/mariadb-bin.index
expire_logs_days        = 10
max_binlog_size         = 100M
relay_log               = /var/log/mysql/relay-bin
relay_log_index         = /var/log/mysql/relay-bin.index
relay_log_info_file     = /var/log/mysql/relay-bin.info
slave_sql_verify_checksum = 0
log_slave_updates
read_only
binlog_format=row

SHOW MASTER STATUS;

File: mysql-bin.000049
Position: 4494988
Binlog_Do_DB: ''
Binlog_Ignore_DB: ''

SHOW ALL SLAVES STATUS G

*************************** 1. row ***************************
               Connection_name: [redacted]13
               Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it
                Slave_IO_State: Waiting for master to send event
                   Master_Host: [redacted]
                   Master_User: replicant
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000049
           Read_Master_Log_Pos: 4494988
                Relay_Log_File: relay-bin-[redacted].000002
                 Relay_Log_Pos: 2982721
         Relay_Master_Log_File: mysql-bin.000049
              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: 4494988
               Relay_Log_Space: 2983028
               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: 13
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
          Retried_transactions: 0
            Max_relay_log_size: 104857600
          Executed_log_entries: 28995
     Slave_received_heartbeats: 57
        Slave_heartbeat_period: 30.000
                Gtid_Slave_Pos: 
1 row in set (0.000 sec)

Initiating the replication

On master, I execute the following:

mysqldump -S /data/mysql/mysql.sock -u [redacted] -p --opt --skip-lock-tables --single-transaction --flush-logs --master-data=2 --databases [redacted] > replication.sql

I then copy the sql to the slave server, and pipe it into the Galera node. This brings the slave up-to-date with the point of the dump.

I then run the following on the slave server:

head -n100 replication.sql | grep "MASTER_LOG_POS"

to get the correct starting file and position for replication, then run the following in SQL:

CHANGE MASTER '[redacted]' TO MASTER_HOST='[redacted]', MASTER_USER='replicant', MASTER_PASSWORD='[redacted]', MASTER_LOG_FILE='[file]', MASTER_LOG_POS=[position];

I’ve already reviewed dozens of posts on this site and others over the past few days, and haven’t found a solution yet. Here are some examples to demonstrate diligence:

MySQL replication not working – no errors – I turned off all filtering to ensure this wasn’t the problem.

mysql replication fail (no error) – We’re using row-based bin-logging

MySQL master-slave replication not updating – No real solutions here. I confirmed that all the positive indicators listed by the first answer are true for me as well.

MySQL says replication is fine but data is not copied – Not using filtering, server IDs are different, etc.


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.