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.
