How to setup MySQL replication

MySQL replication is the technique to replicate one mysql database (master) to one or more databases (slave), using replication, we can:

1) Separate read and write
Write is only done on master server, all the read operations are done one the slave servers. We can also run expensive report on slave servers without affecting the performance of master and other slaves.

2) backup.
MySQL stores all the files used for database/tables in data/ directory, to backup mysql, we can backup the data/ directory. However backing up data/ while MySQL is running will give us an inconsistent copy of data/. To make consistent backup, we need to stop MySQL first, and this will cause downtime.
In MySQL replication environment, we can stop one slave and do the backup on slaves. Master and other slaves are still online during our backup.


To setup MySQL replication, each server in the replication needs to have a unique server-id. The valid range of server-id is 0 to 2^32-1, but replication only allows positive server-id.

I have 3 servers called: centos, centos-1, centos-2. All servers have MySQL freshly installed.

[root@centos ~]# getent hosts | grep centos
192.168.100.100 centos
192.168.100.101 centos-1
192.168.100.102 centos-2

Below are the steps I did to setup replication from centos to centos-1 and centos-2.
1. update /etc/my.cnf and restart MySQL
on centos:

[root@centos ~]# cat <<EOF > /etc/my.cnf
[mysqld]
server-id = 100
log-bin = master-bin.log 
EOF 
[root@centos ~]# service mysql restart

on centos-1:

[root@centos-1 ~]# cat <<EOF > /etc/my.cnf
[mysqld]
server-id = 101
relay-log-index = slave-relay-bin.index
relay-log = slave-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=performance_schema.%
EOF
[root@centos-1 ~]# service mysql restart

on centos-2:

[root@centos-2 ~]# cat <<EOF > /etc/my.cnf
[mysqld]
server-id = 102
relay-log-index = slave-relay-bin.index
relay-log = slave-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=performance_schema.%
EOF
[root@centos-2 ~]# service mysql restart

2. Get master bin log and position.
mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000007 |      272 |              |                  |
+-------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

3. On master, create account used for replication. Slave uses this account to connect to master. Multiple clients can share the same account, we can also create one account for each slave. This account need to have privilege "REPLICATION SLAVE".

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
mysql> FLUSH PRIVILEGES;

4. Setup slave using the information found in step 2 and 3.
On both centos-1 and centos-2

mysql> change master to
    -> master_host='192.168.100.100',
    -> master_port=3306,
    -> master_user='rep1',
    -> master_password='slavepass',
    -> master_log_file='master-bin.000007',
    -> master_log_pos=272;
mysql> start slave;

5. Confirm replication is working:

[root@centos-1 data]# tail -1 centos-1.err
130626  9:47:16 [Note] Slave I/O thread: connected to master 'rep1@192.168.100.100:3306',replication started in log 'master-bin.000007' at position 272
[root@centos-2 data]# tail -1 centos-2.err
130626  9:48:04 [Note] Slave I/O thread: connected to master 'rep1@192.168.100.100:3306',replication started in log 'master-bin.000007' at position 272

we can also insert some data on master, querying on any slave will show us the data we inserted on master.

We are done! MySQL replication is up and running.

reference:
http://dev.mysql.com/doc/refman/5.6/en/replication-howto.html
http://top-performance.blogspot.com/2012/03/how-to-setup-mysql-replication-in-11.html