Mysql dump of slave w/o missing Master data

MySQLmysql-replication

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.

Best Answer

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 !!!

Related Topic