How to import data into master/replica structure in MySQL

Question

I have a file called db.sql; this file contains the data for one database. I want to import this data into a database called db in the newly established master/replica structure.

I used mysql -u USER -p'PASS' db < db.sql command to import the database into the master server. After import, The date was inserted into master server successfully but did not insert into the replica server. (But still, work without error)

I check the content of the db.sql file, which includes:

SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

SET @@GLOBAL.GTID_PURGED='server-1:1-376,
server-2:1-2086154';

#end of the file
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

I know the reason for that is disabling binlog, yet I have three questions:

1. I have many backup files exported in this way; how to import them into my current master server, then it goes to the replica server. (I think If I remove those lines, it will fix my problem but is there anyways to do it directly by MySQL?)
2. For newly exported backups, how to prevent this issue in a way that can import directly? The below script produce the db.sql file.

MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"

mysql ${MYSQL_CONN} -ANe"STOP SLAVE" 2> /dev/null

mysqldump ${MYSQL_CONN}  --single-transaction --quick ${DB_NAME}  2> /dev/null > ${OUTPUT_PATH}/db.sql

mysql ${MYSQL_CONN} -ANe"START SLAVE"  2> /dev/null

3. The backup files come from different master/replicas with different GLOBAL.GTID_PURGED; is this causing any issues? If yes, how to solve it?

Answer 1

https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html says:

If you do not set the --set-gtid-purged option, the default is that a SET @@GLOBAL.gtid_purged statement is included in the dump output if GTIDs are enabled on the server you are backing up, and the set of GTIDs in the global value of the gtid_executed system variable is not empty. A SET @@SESSION.sql_log_bin=0 statement is also included if GTIDs are enabled on the server.

This matches what you are seeing in your dump output.

A bit later, the same page says:

The possible values for the --set-gtid-purged option are as follows:

...

OFF

SET @@GLOBAL.gtid_purged is not added to the output, and SET @@SESSION.sql_log_bin=0 is not added to the output. For a server where GTIDs are not in use, use this option or AUTO. Only use this option for a server where GTIDs are in use if you are sure that the required GTID set is already present in gtid_purged on the target server and should not be changed, or if you plan to identify and add any missing GTIDs manually.

I recommend setting --set-gtid-purged=OFF when you run mysqldump.