3

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.

  • You need log-slave-updates on the slave too, if not, mysql will fail to restart. I suppose it is just a mistake when copying it. Additionally, are you aware that you cannot perform a consistent backup just by using cp or rsync on a running server, right? Have you also considered using Enterprise Backup or Percona XtraBackup to ease the process (they use raw files)? – jynus Apr 6 '15 at 16:25
  • How much data (how many GB) in the Master ? (Please run the query in dba.stackexchange.com/questions/8661/… for accurate sizes or run du -h /var/lib/mysql) – RolandoMySQLDBA Apr 6 '15 at 16:26
  • @jynus. Thanks. I actually had log-slave-updates on at some time, but it didn't make any difference. I do have the liberty to shutdown the server before copying. – Ifedi Okonkwo Apr 6 '15 at 16:33
  • @RolandoMySQLDBA. Thanks. The data directory size is less then 5GB. But pray, is this an important parameter regarding my question? To both jynus and Rolando: Please don't fly off the handle: My installation is on Windows!;) – Ifedi Okonkwo Apr 6 '15 at 16:36
2

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.

  • "Copy your data directory...". All of it? It says somewhere in the manual that I "might want to avoid copying" the mysql db directory during copy. And yet I would like to actually copy over the user information (which should be within this same db). I can see you mention auto.cnf for exclusion. But is that all? Do I need to copy the ib_logfiles, for instance? – Ifedi Okonkwo Apr 6 '15 at 17:58
  • Yes, if you want to maintain your users and permisions you have to copy the mysql.* tables too. You can later modify them, but you should do it through sql commands to avoid problems. Forgot to mention to copy the files while the slave is also stopped, too. If you did a non-fast shutdown, you can skip them, as they will be empty and will be recreated. But maintain them as a general rule. – jynus Apr 6 '15 at 19:26
  • I meant the ib_log* files. Keep most others, including the binlogs, ibdata*, .frm, subdirectories, .myi, .myd, etc. The general log and error logs are not essential. – jynus Apr 6 '15 at 19:34
  • I'm accepting your answer with thanks. However, I would recommend fleshing up No 2, which deals with "Copying raw data files". Many folks need to clearly know which files/folders to copy and why. IMHO, a focused step-by-step for this use case is in short supply around the web. – Ifedi Okonkwo Apr 7 '15 at 7:15
  • 1
    @IfediOkonkwo Thank you. However, this is a Q&A site, not a place for tutorials. But I will take not and maybe create a full one on my blog. – jynus Apr 7 '15 at 11:54
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 !!!

  • My previous experience has been that mysqldump is painfully slow, particularly on the restore side of things. Is there a fundamental reason I should avoid raw data copy, apart from the need to temporarily bring down the server? – Ifedi Okonkwo Apr 6 '15 at 17:54
  • Again, why can't I get the MASTER server to start without log-slave-updates? Each time I insert it, I get: 2015-04-06 15:18:12 1660 [ERROR] --gtid-mode=ON or UPGRADE_STEP_1 or UPGRADE_STEP_2 requires --log-bin and --log-slave-updates 2015-04-06 15:18:12 1660 [ERROR] Aborting, unless I also remove the gtid-mode=ON. Indeed, this is one of the recalcitrant issues I'd been battling with. Isn't that weird? Isn't this declaration meant for the SLAVE? – Ifedi Okonkwo Apr 6 '15 at 21:38
  • Documentation says it's required when using gtid_mode : dev.mysql.com/doc/refman/5.6/en/…. It makes sense since the values for gtid_executed and gtid_purged are calculated based on traversing binlogs and relay logs. This allows a GTID master to be used as a slave should failover scenarios come into play. – RolandoMySQLDBA Apr 6 '15 at 21:42
  • Hmm. Reading that link, then this: dev.mysql.com/doc/refman/5.6/en/…, there is certainly a missing link in the Docs! Especially when you get to this: "You must start both A and B with --log-bin to enable binary logging, and B with the --log-slave-updates option so that updates received from A are logged by B to its binary log". Now I'm constrained to start BOTH A and B with --log-slave-updates. That IS weird. Anyhow, I appreciate your time on my thread. – Ifedi Okonkwo Apr 7 '15 at 7:02
  • I'm accepting jynus' answer, though I'm convinced your step-by-step would have worked also. The major reason is, in my use case, it is certainly more comfortable for me to copy raw data files rather than follow the mysqldump pathway. A million thanks, sir! – Ifedi Okonkwo Apr 7 '15 at 7:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.