Tip of the Day — Slave Setup
I set up a new server pair today. You know people complain sometimes that replication isn’t durable enough (and when it breaks at 3:00 AM I don’t think it is durable enough 
), but no one complains that it is too complex to set up.
step one - set up your servers with their operating systems and MySQL
step two - on the master you need to add the replication user:
GRANT REPLICATION SLAVE ON *.* TO ‘repl_user’@‘ip_of_new_slave_server’
IDENTIFIED BY ‘repl_user_password’;
step three - you need to get your starting information about the master (we assume that the master isn’t in production yet)
SHOW MASTER STATUS;
This will display the binary log file currently being written to and the log position in the file.
step four - starting the slave. Plug in the information you determined above. You will also need the IP address of the master server. On Linux run the ifconfig command, on Winodows it would be ipconfig.
RESET SLAVE;
CHANGE MASTER TO master_log_file=‘master_log’,
master_log_pos=master_log_position, master_user=‘repl_user’,
master_password=‘repl_user_password’, master_host=‘ip_of_master’;
START SLAVE;
SHOW SLAVE STATUS;
That’s it. You should be up and running.
So set up a slave server just for fun!!
4 Comments so far
Leave a reply
This is assuming that the bin-log on the master is already enabled and that the master has been running with it enabled since day 1 before any data got in.
You would have to sync the data from the master to the slave first otherwise (which is most likely the case in real world conditions) and then start replicating from the position right after where your sync occurred.
In either case, it’s worth looking at Baron Schwartz’s maatkit: http://maatkit.sourceforge.net/
In particular, mk-table-sync:
This tool offers unique synchronization algorithms that can efficiently find and resolve differences between two MySQL tables, which need not be on the same server. A smart DBA can repair an out-of-sync slave server without reloading all the data in a fraction of the time otherwise required. It is the only such tool that can optionally operate over replication, guaranteeing consistency and avoiding race conditions.
I purposely didn’t take into account any type of data synchronization. The reason why is simply space. I will leave what to do with a master server that already has data on it to a further tip. I will do a tip for tomorrow on how to do the data sync.
But, you are correct. In this case today I was setting up a brand new server, so what I said was perfectly accurate (taking into account that these two servers were brand new..hence step one of the tip (setup OS and MySQL). Now that the master/slave is set up I can add data(bases) to the master and they will be replicated to the slave.
Baron’s table sync tool is really designed for syncing a master and slave after they “drift”. If you are setting up a slave off a master that has a large dataset it is probably best to use something like an lvm snapshot to get things in sync to begin. Tomorrow I will cover that technique.
Looking forward to it
[…] tip is a follow-up to yesterday’s post about setting up a new slave server with a new master (one that has no current data on it). Many […]