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.
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.