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

Question

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:

1. I am using version 5.6.23
2. I have just turned on GTID mode, when I never really mastered the "old" system yet!
3. 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

1. In order to copy raw data files from the master, what should be my step-by-step process?
2. Exactly what files within the data directory must I NOT copy, and why?
3. 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.

Answer 1

I assume GTID has been already enabled on the master:

1. Stop the master server
2. Copy your data directory to the slave host
3. 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.
4. 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)
5. Start the slave host
6. 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;
7. 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.
8. 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.

Answer 2

OBSERVATION #1

The slave needs log-slave-updates in my.ini

OBSERVATION #2

Both Master and Slave need binlog_gtid_simple_recovery in my.ini

MySQL Restart is required on both Master and Slave to include new options

SUGGESTION

Since DB is 5GB, just mysqldump the database on the Master. No need for raw copying

STEP 01 : On the Master, run this

RESET MASTER;
GRANT REPLICATION CLIENT,REPLICATION_SLAVE ON *.*
TO replicator@'%' IDENTIFIED BY 'repl1cat0r';

STEP 02 : On the Slave, run this

STOP SLAVE;
CHANGE MASTER TO
    MASTER_HOST='IP Address of Master',
    MASTER_PORT=3306,
    MASTER_USER='replicator',
    MASTER_PASSWORD='repl1cat0r',
    MASTER_HEARTBEAT_PERIOD=1,
    MASTER_AUTO_POSITION=1
;

STEP 03 : Dump the data on the Master

set mysql_user=root
set mysql_conn=-u%mysql_user% -p
set dump_options=--routines --triggers --events -A --apply-slave-statements
mysqldump -uroot -p %dump_options% > C:\mysqldata.sql

STEP 04 : Copy the dump to the Slave (leave dump in C:)

STEP 05 : Load dump into the Slave

mysql %mysql_conn% < C:\mysqldata.sql

STEP 06: Verify replication

Login to mysql on the Slave and run `SHOW SLAVE STATUS\G`

Give it a Try !!!