3

I am fairly new to the whole replication process of mysql so this may be an easy question to answer. I have a master and and slave. I need to set up another slave so obviously I will need to make the dump from the current slave because I CAN NOT take the master offline for a second. How can I be sure that during the time I am making the dump of the current slave database that I do not miss any master data that is newly created over that time?

Thanks all.

pickledtink
  • 73
  • 1
  • 5

3 Answers3

4

I have an interesting method but you will have to stop replicaton to do

For DB server M1, S1, and S2

STEP01) On S2, install same version of MySQL that S1 has

STEP02) On S2, make sure server_id in /etc/my.cnf is different from server_id in S1

STEP03) On S1, STOP SLAVE;

STEP04) On S1, SHOW SLAVE STATUS\G

STEP05) Write down the following two values from STEP 04

  • Master_Host (MHOST)
  • Master_Port (MPORT)
  • Relay_Master_Log_File (RMLF)
  • Exec_Master_Log_Pos (EMLP)

STEP06) On S1, mysqldump ... --all-databases > /root/MySQLDataForSlave.sql

STEP07) On S1, START SLAVE;

STEP08) On S1, scp /root/MySQLDataForSlave.sql S2:/root/.

STEP09) On S2, mysql ... < /root/MySQLDataForSlave.sql

STEP10) On S2, run this command in the mysql client using values from STEP05

CHANGE MASTER TO
master_host='MHOST',
master_port=MPORT,
master_user='repluser',
master_password='replpass',
master_log_file='RMLF',
master_log_pos=EMLP;

STEP11) On S2, START SLAVE;

STEP12) On S2, SHOW SLAVE STATUS\G (If Slave_IO_Running and Slave_SQL_Running are Yes, CONGRATULATIONS !!!)

STEP13) On S2, SHOW SLAVE STATUS\G over and over again until Seconds_Behind_Master = 0;

I actually wrote a script to automate this in the DBA StackExchange back on Feb 06, 2012.

Give it a Try !!!

Community
  • 1
RolandoMySQLDBA
  • 16,364
  • 3
  • 47
  • 80
  • This is actually the process I have used a couple of times and worked. But I am not a DBA so was never 100% sure that I managed to grab all of the data and not miss a beat. This particular case the master does alot of transactions so I can not miss one. Thank you. – pickledtink Oct 04 '12 at 17:08
  • I have done things like this for years. I wouldn't worry. Here is why: Look back at Step05. The RMLF and EMLP represents that last binlog entry from the Master that got executed on the Slave. No matter where replication is, I always start from that position. All relay logs are erased and SQL is freshly downloaded from RMLF,EMLP. The only thing that can go wrong is if the binlog RMLF does not exist. This should never be the case since you are using an active Slave that can see the Master and its binlogs. I also have an rsync technique : http://serverfault.com/a/257426/69271 – RolandoMySQLDBA Oct 04 '12 at 17:33
  • The rsync is awesome. I just read through it and will give it a try later. Thanks for linking that here. – pickledtink Nov 03 '12 at 14:46
2

Disable replication on the slave before taking the snapshot. Hint - stop the IO thread first and let the SQL thread complete before stopping it then generating the snapshot.

symcbean
  • 19,931
  • 1
  • 29
  • 49
2

Use following guide to introduce additional slave to your existing environment:

MySQL :: MySQL 5.0 Reference Manual :: 16.1.1.9 Introducing Additional Slaves to an Existing Replication Environment

alexus
  • 12,342
  • 27
  • 115
  • 173