Bug #74465 Attempt to Sync Slave with Master -- sets Master ServerID to same as slave.
Submitted: 20 Oct 2014 20:40 Modified: 28 May 2016 1:59
Reporter: Jerry Richart Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Utilities Severity:S1 (Critical)
Version:1.4.4 OS:Windows (6.1)
Assigned to: CPU Architecture:Any

[20 Oct 2014 20:40] Jerry Richart
Description:
After using "mysqldbexport" on master, copying the backup to the slave and running "mysqldbimport" on slave -- slave thinks master is using the same serverid as slave.

Here are the instructions I follows:
Extracted from http://dev.mysql.com/doc/mysql-utilities/1.3/en/utils-task-provision-slave.html

$ mysqldbexport --server=root:root@localhost:13001 --all --export=both --rpl=master --rpl-user=rpl:rpl > data.sql
$ mysqldbimport --server=root:root@localhost:13002 data.sql
# Source on localhost: ... connected.
# Importing definitions from data.sql.
ERROR: The import operation contains GTID statements that require the global gtid_executed system variable on the target to be empty (no value). The gtid_executed value must be reset by issuing a RESET MASTER command on the target prior to attempting the import operation. Once the global gtid_executed value is cleared, you may retry the import.
$ mysql -uroot -proot -h 127.0.0.1 --port=13002 -e "RESET MASTER"
$ mysqldbimport --server=root:root@localhost:13002 data.sql
# Source on localhost: ... connected.
# Importing definitions from data.sql.
CAUTION: The following 1 warning messages were included in the import file:
# WARNING: A partial export from a server that has GTIDs enabled will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to generate the GTID statement, use the --skip-gtid option. To export all databases, use the --all and --export=both options.
#...done.

There are several operations listed here. 
The first one we see is the execution of the mysqldbexport utility to create a file that includes an export of all databases as designated with the --all option. 
We add the '--export=both' option to ensure we include the definitions as well as the data.

We also add the --rpl=master option which instructs mysqldbexport to generate the replication commands with respect to the source server being the master. 
Lastly, we include the replication user and password to be included in the CHANGE MASTER command.

Next, we see an attempt to run the import using mysqldbimport but we see there is an error. 
The reason for the error is the mysqldbimport utility detected a possible problem on the slave whereby there were global transaction identifiers (GTIDs) recorded from the master. 
You can see this situation if you setup replication prior to running the import. 
The way to resolve the problem is to run the RESET MASTER command on the slave as shown in the next operation.

The next operation is a rerun of the import and in this case it succeeds. 
We see a warning that is issued any time there are replication commands detected in the input stream whenever GTIDs are enabled.

How to repeat:
run "show slave status\G" -- record what it thinks is the master's serverid.
run mysqldbexport
--
mysqldbexport --server=dd-username:password@localhost:3306 --all --export=both --rpl=master --rpl-user=repl_slave_user:replslaveuserpassword > f:\MySQL\Backups\backup.sql
--
issue "reset master" command on slave
--
copy backup from master to slave
--
issue mysqldbimport command on slave
mysqldbimport --server=username:password@localhost:3306 F:\mysql\backups\backup.sql
--
run "show slave status\G" again.  you will see it reporting the serverid of the master to be the same as the slave.
[29 Oct 2014 18:01] Chuck Bell
I cannot repeat this problem as stated in the initial description (and the documentation). However, I see a difference in how the utility is connecting to the master in the last part of your report, but I need more information.

Please include the output of the export run. I cannot verify the problem without the addition of the output of the export command. More specifically, I need to see the CHANGE MASTER portion.

It would also help to see the commands you used to setup replication initially.
[28 Apr 2016 1:59] Philip Olson
Changing to "Need Feedback"
[29 May 2016 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".