How to set up Mysql replication

Section 1: Master Slave replication
For ex, if you want to replicate from master A to slave B

1. Create a replication account on master A
‘%’ means all the other boxes, so all slave boxes can use
the same user/passwd to replicate data from the master A
mysql> grant replication slave, reload, super on *.*
to ’slave_user’@'%’ identified by ’slave_pass’;

2. Shut down the master server if it is running
sudo /home/y/bin/mysqladmin shutdown -u root –password=’root_passwd’

3. Modify master’s configuration
in /home/y/etc/my.cnf generally
[mysqld]
server-id=master_server_id
log-bin=binlog_name
(do yinst set mysql_config.log_bin=log-bin

4. Restart the master mysql server
then the master will log updates by writing them into the bin-log
to restart the mysql server, run
sudo /home/y/bin/mysqld_safe &

5. Copy the mysql db data from master to slave to make them in sync before replication
by following instructions in Section 2: To get a full copy of the master DB

Note:in terms of how to copy the data from master to slave,
there are multiple ways, if all the database tables you are
replicating are myISAM tables, then you can use the method
mentioned here, otherwise, you might want to check the
Mysql replication link for more details

6. Shut down the slave if it is running
using the same command as step 2 on slave box

7. Configure slave server to know its replication id
modify /home/y/etc/my.cnf on slave server

[mysqld]
server-id=slave_server_id
master-host=master_host
master-user=slave_user
master-password=slave_pass
#specify which database you want to replicate
replicate-do-db = database1_to_be_replicated
replicate-do-db = database2_to_be_replicated
replicate-do-db = database3_to_be_replicated …. (etc)
* slave_server_id is the replication id of the slave
server, it must be different from the master’s ID and
all the other slaves’ ids
* master_host is the name of the master host.
* slave_user/slave_pass must be the user/passwd set up in step 1

8. Restart the slave mysql server (like step 3 and 5)

9. Run “show slave status” under mysql prompt on slave B
check out if the replication is started

10. You can also un “show master status” on master end
to see what is the current bin-log on master box

11. To see if there is any issue, you can check /home/y/logs/mysql/mysqld.err
on both master and slave boxes, to see if there are any issues there.

Section2 :To get a full copy of the master DB

1. Issue read lock to all databases to make sure the current data snapshot on master is consistent
mysql> flush tables with read lock;

2. IMPORTANT:Reset (clean up ) the binary log on master box
since we are going to first copy the current data snapshot from master to slave, the existing binary log has all the update commands logged until now, we need to clean them up before starting the replication, this step is very important, please run this first on the master box before continue to the next steps
mysql> reset master;

3. Stop the mysql processes on the slave box
sudo /home/y/bin/mysqladmin shutdown

4. Scp all the relevant DB files from master box to slave box
scp -c blowfish -r /home/y/var/mysql/data/db_dir1… username@slave_box:/home/y/var/mysql/data
after this is done, change all files owner/group to mysql under /home/y/var/mysql/data on slave box

5. Start the mysql server on slave box sudo /home/y/bin/mysqld_safe &

6. Log into the slave box (optional)

7. Run check tables on command line(optional)
$> mysqlcheck –all-databases
make sure the above give you ok status

8. run “select count() from some_replicated_table” to compare the slave DB with the master DB
* to confirm they have the same data content

9. Now it is good to say we have a good copy of the data from the master box

10. Unlock the tables on the master box
mysql> unlock tables