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:
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?)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
GLOBAL.GTID_PURGED
; is this causing any issues? If yes, how to solve it?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 aSET @@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 thegtid_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 orAUTO
. Only use this option for a server where GTIDs are in use if you are sure that the required GTID set is already present ingtid_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
.