Setting up replication
1. Configure master server parameters. This can be done by assigning an id for the
master server and enabling binary logging. Add the following lines under [mysqld] section of your option file
[mysqld] server-id=1
log-bin
2. Next step is to create a user with replication privileges on the master server, which
can be used by the slave server to connect (create a user each for each of the slaves).
mysql> CREATE USER ‘slave_user’@’slave_host’ IDENTIFIED BY
‘slave_pass’;
mysql> GRANT REPLICATION SLAVE ON *.* TO
‘slave_user’@’slave_host’;
3. Make a copy of master server’s database as a starting point for slaves
shell> mysqldump -u root -p –all-databases –master-data=2 >
dump_file
This creates a dump of master server’s database with a comment at the top indicating the
binary log filename and position in binary log.
You can also find the binary log filename and current position in the binary log by running
the following command
mysql> SHOW MASTER STATUS;
4. Configure a unique server id for each of the slave servers in my.cnf file
[mysqld] server-id=2
5. Copy the dump_file to slave host and import it
shell> mysql -u root -p < dump_file
6. Point the slave to the master server and specify position in the binary log
mysql> CHANGE MASTER TO
Copyright © 2008 Hewlett-Packard Development Company, L.P. All rights reserved.
-> MASTER_HOST = ‘master_host_name’,
-> MASTER_USER = ‘slave_user’,
-> MASTER_PASSWORD = ‘slave_pass’,
-> MASTER_LOG_FILE = ‘master_log_file’,
-> MASTER_LOG_POS = master_log_pos;
7. Start the slave
mysql> START SLAVE;
Now slave will connect to the master using the parameters we have configured and start
replicating.
You can verify the status of the slave by running
mysql> SLAVE STATUS\G

Post By Gishore J Kallarackal (2,121 Posts)

Gishore J Kallarackal is the founder of techgurulive. The purpose of this site is to share information about free resources that techies can use for reference. You can follow me on the social web, subscribe to the RSS Feed or sign up for the email newsletter for your daily dose of tech tips & tutorials. You can content me via @twitter or e-mail.

Website: → Techgurulive

Connect