MySQL Forums
Forum List  »  Replication

Re: 1 Master -> Many slaves, need to update dump file
Posted by: Irfan Ali
Date: July 11, 2012 03:20PM

What replication format you are using ?
what is your overall database size ?
what table engine you are using for database MyISAM or INNoDB ?

You are following wrong approach to setup new slaves. There is no need to use the dump file you created first time this will take huge time to sync from binlogs after database restore because currently MySQL replication is single threaded. Infact i prefer LVM snapshots or percona xtrabackup tool for backups over mysqldump.

I prefer to create new slave you shall use exisitng slave nor master to not to disturb any writes on master.

To create fresh new slave. Use following steps.
1) Use any of slave from where you want to create new slave.
2) login to slave machine, login to mysql, STOP SLAVE first this will stop all writes but not reads, and record replication positions from SHOW SLAVE STATUS\G.
3) backup your database either from mysqldump or backups tools i described earlier.
4) copy database to new slave and reload it to new slave.
5) copy existing slave my.cnf to new slave and change server-id to unique value which is not in use in your replication cluster.
6) Allow new slave ip on master with REPLICATION SLAVE privilege.
7) on new slave, execute,
CHANGE MASTER TO
MASTER_HOST = 'master-ip',
MASTER_USER = 'repuser',
MASTER_PASSWORD = 'reppass',
MASTER_LOG_FILE = 'Exec_Master_Log_File',
MASTER_LOG_POS = Exec_Master_Pos;
START SLAVE;
SHOW SLAVE STATUS\G to verify new slave started smoothly.
Exec_Maser_Log_File & Exec_Master_Pos can be fetched from SHOW SLAVE STATUS\G values recorded earlier from slave where replication is prepared.

>> If I lock the tables to do a new dump, then purge the existing binlog files, will the current slaves have any replication issues? What is the best practices for doing something like this?

This is already covered in replication steps i described earlier, plus as far as purge of binlogs is concern there is no hard and fast rule when to purge binlogs.
The safest way is to check every slave status and check for minimal binary log file its replicating and you purge all binary logs before that. Infact you can set purge_binary_logs parameter in master my.cnf to purge all old binlogs automatically.
let say purge all binlogs older than 1 month.

purge_binary_logs = 30

Hope that helps.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: 1 Master -> Many slaves, need to update dump file
1084
July 11, 2012 03:20PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.