MySQL change master causes Gtid to be lost

Change master causes Gtid to be lost
Recovering from Innobackupex causes Binlog to be pulled from the main standby gtid inconsistent.
This type of error cannot be repaired by constructing a null transaction mode.
The change master mode is required to point to the next bit of the failed event. Then the bitwise-Point (master_auto_position=0) to pull the binlog.

Slave_io_state:queueing Master event to the relay log
master_host:10.1.1.111
Master_user:repl
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000003
read_master_log_pos:478283
relay_log_file:relay-bin.000002
relay_log_pos:361
relay_master_log_file:mysql-bin.000003
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:1050
Last_error:error ' Table ' Kelvin ' already exists ' on query. Default database: ' Test '. Query: ' CREATE TABLE ' Kelvin ' (
' ID ' bigint () not NULL,
' username ' varchar (ten) not NULL DEFAULT ' Kelvin ',
' passwd ' varchar (4000) not NULL DEFAULT ' Kelvin ',
' CreateDate ' int (ten) is not NULL,
' Groups ' varchar (2) Not NULL,
PRIMARY KEY (' id ')
) Engine=innodb DEFAULT Charset=utf8 '
skip_counter:0
exec_master_log_pos:151
relay_log_space:478691
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:1050
Last_sql_error:error ' Table ' Kelvin ' already exists ' on query. Default database: ' Test '. Query: ' CREATE TABLE ' Kelvin ' (
' ID ' bigint () not NULL,
' username ' varchar (ten) not NULL DEFAULT ' Kelvin ',
' passwd ' varchar (4000) not NULL DEFAULT ' Kelvin ',
' CreateDate ' int (ten) is not NULL,
' Groups ' varchar (2) Not NULL,
PRIMARY KEY (' id ')
) Engine=innodb DEFAULT Charset=utf8 '
Replicate_ignore_server_ids:
master_server_id:113306
Master_uuid:26e3db40-51d4-11e7-adc8-000c29a459b4
Master_info_file:/opt/56/data/master.info
sql_delay:0
Sql_remaining_delay:null
Slave_sql_running_state:
master_retry_count:86400
Master_bind:
Last_io_error_timestamp:
last_sql_error_timestamp:170616 00:36:53
MASTER_SSL_CRL:
Master_ssl_crlpath:
retrieved_gtid_set:26e3db40-51d4-11e7-adc8-000c29a459b4:1-1612
Executed_gtid_set:
Auto_position:1
1 row in Set (0.00 sec)

Stop slave;
Change Master to master_log_file= ' Relay_master_log_file ', master_log_pos=exec_master_log_pos+1,master_auto_ position=0;
Start slave;

Show slave status \g

Slave_io_state:
master_host:10.1.1.111
Master_user:repl
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000003
read_master_log_pos:152
relay_log_file:relay-bin.000002
relay_log_pos:314
relay_master_log_file:mysql-bin.000003
Slave_io_running:no
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:152
relay_log_space:512
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:1236
Last_io_error:got fatal Error 1236 from master when reading data from binary log: ' Log event entry exceeded max_allowed_p Acket; Increase max_allowed_packet on Master; The first event ' mysql-bin.000003 ' at $, the last event read from '/opt/56/binlog/mysql-bin.000003 ' at, the last Byt E read from '/opt/56/binlog/mysql-bin.000003 ' at 171. '
last_sql_errno:0
Last_sql_error:
Replicate_ignore_server_ids:
master_server_id:113306
Master_uuid:26e3db40-51d4-11e7-adc8-000c29a459b4
Master_info_file:/opt/56/data/master.info
sql_delay:0
Sql_remaining_delay:null
Slave_sql_running_state:slave have read all relay log; Waiting for the slave I/O thread to update it
master_retry_count:86400
Master_bind:
last_io_error_timestamp:170616 00:39:13
Last_sql_error_timestamp:
MASTER_SSL_CRL:
Master_ssl_crlpath:
Retrieved_gtid_set:
Executed_gtid_set:
auto_position:0
1 row in Set (0.00 sec)

When you observe you will find that the master server no longer requires the slave server to pull the binary log to synchronize the data. Possible causes include a primary server expiration system variable expire_logs_days--through a binary log or someone manually removing binary logs from the master server by clearing the binary log command or the Rm-f command or maybe you have some cronjob files older than the binary log, Requires disk space, and so on. So make sure that you always have the binary logs that are required to be present on the primary server, and you can update your program to keep the slave server required to monitor the output of the slave state by monitoring the "relay_master_log_file" variable slave the binary log. Additionally, if you set the expire_log_days in MY.CNF old Binlogs automatically expires and is removed. This means that when MySQL opens a new Binlog file, it checks the old binlogs and clears any values earlier than expire_logs_days (in days). Slightly the server adds a feature that expires the log based on the total number of files that are not used by the age of Binlog files. So in this configuration, if you get spike traffic, it may cause binlogs to disappear sooner than you expect. For more information, see limit the number of binlog files.

Change MASTER to master_log_file= ' relay_master_log_file+1 ', master_log_pos=4;

Show slave status \g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:10.1.1.111
Master_user:repl
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000004
read_master_log_pos:68299
relay_log_file:relay-bin.000002
relay_log_pos:68469
relay_master_log_file:mysql-bin.000004
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:68299
relay_log_space:68667
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:113306
Master_uuid:26e3db40-51d4-11e7-adc8-000c29a459b4
Master_info_file:/opt/56/data/master.info
sql_delay:0
Sql_remaining_delay:null
Slave_sql_running_state:slave have read all relay log; Waiting for the slave I/O thread to update it
master_retry_count:86400
Master_bind:
Last_io_error_timestamp:
Last_sql_error_timestamp:
MASTER_SSL_CRL:
Master_ssl_crlpath:
retrieved_gtid_set:26e3db40-51d4-11e7-adc8-000c29a459b4:10008-10152
executed_gtid_set:26e3db40-51d4-11e7-adc8-000c29a459b4:10008-10152
auto_position:0
1 row in Set (0.00 sec)

Finally, use Pt-table-checksum and pt-table-sync to check for data consistency issues

MySQL change master causes Gtid to be lost