I am trying to enable replication from an older version of MariaDB on the master (5.5.36) to a newer version on the slave (10.1.10). Since the master doesn’t support GTID I’m trying to turn it off…
After successfully preparing the backup I attempt to start replication:
CHANGE MASTER TO
MASTER_HOST=‘x.x.x.x’,
MASTER_USER=‘replicate’,
MASTER_PASSWORD=‘xxxx’,
MASTER_PORT=3306,
MASTER_LOG_FILE=‘mysql-bin.005214’,
MASTER_LOG_POS=890018419,
MASTER_CONNECT_RETRY=10,
MASTER_USE_GTID=no;
This is what I’m seeing in the mysql error log:
[ERROR] Slave I/O: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table ‘mysql.gtid_slave_pos’ doesn’t exist, Internal MariaDB error code: 1146
[Note] Slave SQL thread initialized, starting replication in log ‘mysql-bin.005214’ at position 890018419, relay log ‘./mysql-relay-bin.000001’ position: 4
[ERROR] Slave SQL: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table ‘mysql.gtid_slave_pos’ doesn’t exist, Internal MariaDB error code: 1146
[Note] Slave I/O thread: connected to master ‘replicate@x.x.x.x:3306’,replication started in log ‘mysql-bin.005214’ at position 890018419
[ERROR] Slave SQL: Error ‘Duplicate entry ‘46082’ for key ‘PRIMARY’’ on query. Default database: ‘etl_execution’. Query: ‘INSERT INTO batches (batch_file, completed_at, created_at, status) VALUES (’/home/x/x.ebf’, NULL, ‘2016-01-20 09:14:03’, ‘executing’)', Internal MariaDB error code: 1062
[Warning] Slave: Duplicate entry ‘46082’ for key ‘PRIMARY’ Error_code: 1062
[ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with “SLAVE START”. We stopped at log ‘mysql-bin.005214’ position 890018495
I feel like I’m close to getting the replication up and running but not sure what to do now- any help would be much appreciated- thanks guys.
And here is the output of show slave status:
SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: x.x.x.x
Master_User: replicate
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.005215
Read_Master_Log_Pos: 18081409
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 609
Relay_Master_Log_File: mysql-bin.005214
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: Error ‘Duplicate entry ‘46082’ for key ‘PRIMARY’’ on query. Default database: ‘etl_execution’. Query: ‘INSERT INTO batches (batch_file, completed_at, created_at, status) VALUES (’/home/x/x.ebf’, NULL, ‘2016-01-20 09:14:03’, ‘executing’)’
Skip_Counter: 0
Exec_Master_Log_Pos: 890018495
Relay_Log_Space: 236200034
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: Error ‘Duplicate entry ‘46082’ for key ‘PRIMARY’’ on query. Default database: ‘etl_execution’. Query: ‘INSERT INTO batches (batch_file, completed_at, created_at, status) VALUES (’/home/x/.ebf’, NULL, ‘2016-01-20 09:14:03’, ‘executing’)’
Replicate_Ignore_Server_Ids:
Master_Server_Id: 101
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
I was able to resolve by importing the data again from master, prepping the backup (ammending logs), starting mysql with the slave off, and then specifying master info with ‘Using_Gtid’ set to ‘no’. I still see:
[ERROR] Slave I/O: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table ‘mysql.gtid_slave_pos’ doesn’t exist, Internal MariaDB error code: 1146
In the error log, but replication is working.