Not the answer you need?
Register and ask your own question!

Unable to get replication going- can't turn off GTID?

alyssenkoalyssenko EntrantInactive User Role Novice
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 '[email protected]: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.

Comments

  • alyssenkoalyssenko Entrant Inactive User Role Novice
    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
  • alyssenkoalyssenko Entrant Inactive User Role Novice
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.