Copying MySQL Raw Data Files for Master-Slave Replication and GTIDs

I have been developing and running a mysql-based web application (intranet-based) for some time now. I wish to establish a step-by-step sane sequence for creating a slave from the running MySQL master. I have decided I should start the slave server by copying MySQL raw data files. A couple dozen times, I've gone through the relevant section of the manual, and I'm still dizzy.

I once got replication going (years ago) using this same approach, but at present, I'm in a bit of a fix. I think the complexity for me has been increased by the following:

• I am using version 5.6.23

• I have just turned on GTID mode, when I never really mastered the "old" system yet!

• Generally, most tutorials and help posts out there are currently either outdated or in "transition", given the recent changes and advances in replication functionality that came on board during the 5.6.xx series.

Currently, I have the following config declarations:

MASTER:

log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog-format=MIXED
enforce-gtid-consistency
gtid-mode=ON
log-slave-updates

SLAVE:

log-bin=mysql-bin
server-id=2
read-only=ON
gtid-mode=ON
enforce-gtid-consistency
binlog_format=MIXED

• In order to copy raw data files from the master, what should be my

step-by-step process?

• Exactly what files within the data directory must I NOT copy, and

why?

• On the slave, what are the gotchas, especially regarding the GTID

mode?

I'm sorry if aspects of my question sound vague. I'd be more than willing to elucidate as might be necessary.

Thank you in advance.

I assume GTID has been already enabled on the master:

• Stop the master server

• Copy your data directory to the slave host

• Start the master. Technically it is not needed, but if you do not feel safe with what you do and want to be 100% sure everything is correct before continuing with replication- you can start it in read-only mode. Once you see that it is not needed, you can skip that step. This is only to avoid doing anything wrong on the master because you do not trust yourself.

• Delete or move away specifically the auto.cnf file so it can be recreated with a different number on restart (otherwise it will be considered the same server and replication will fail)

• Start the slave host

• Configure replication, the difference would be that instead of using a file and a log position, you will execute CHANGE MASTER TO ... MASTER_AUTO_POSITION = 1;

• Start replication - slave will automatically keep up with the changes. If for some reason the auto_position does not work, you will need to SET gtid_purged to the value on SHOW MASTER STATUS of the read_only master.

• Check that replication is running and without errors with SHOW SLAVE STATUS- gtid_executed should show the same range than the master. Remember to unset read_only on the master if you set it before.

Everything is fully documented on the manual (coming from standard replication). You have a Percona post talking about it here.

StackExchange Database Administrators Q#97125, answer score: 3