0

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.

  • Do any slave status counters change when the master's binlog position advances? – Mark Wagner Apr 11 at 21:40
  • Yes - Read_Master_Log_Pos and Exec_Master_Log_Pos both track the master's binlog position. Seconds_Behind_Master remains at 0. Executed_Log_Entries ticks upwards. – Brian Bauman Apr 11 at 21:46
  • What grants does your replication user have on the slave? The fact that databases can be created on the slave, makes me curious if this is a grant issue? – frontsidebus Apr 22 at 18:42
  • None - I was under the impression that slaves log into the master with the replication user, but execute the relay logs as a system user with all permissions. If you run SHOW PROCESSLIST; on a slave with replication running, the Slave_IO process runs under "system user". – Brian Bauman Apr 22 at 19:10
  • Is Galera 7.2 the same version as is in MariaDB 10.3? – Rick James Apr 22 at 22:45
0

After much experimentation with various versions of MariaDB and MySQL in docker containers, I have determined that MariaDB versions of 10.0 or greater will silently fail and continue when they encounter unreadable row events.

Using MariaDB 5.5's mysqlbinlog in a docker container revealed the following errors from replication:

ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 42, event_type: 30
ERROR: Could not read entry at offset 938: Error in log format or read error.

This error falls outside the scope of the question as asked.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.