MySQL replication introduction

Database plays the essential role in the information system such as websites. To avoid the SPF of MySQL, in production system, we use master-slave replication to synchronise the data and MySQL-proxy to enhance the IO performance.

Advantages of MySQL replication

• Avoid of SPF, when the master server is down, we can use slave server to provide the service
• We can use master server to handle the write request and slaves to handle the read IO.

 Here is how MySQL replication works

• Master server write the data changes to Binary log
• Slave servers IO Thread read from master binary log and put to Relay log.
• Slave server SQL thread read the changes from relay log and implement the change to Slave database.

 

Test Environment:

1. Master server 192.168.139.100
2. Slave server 192.168.139.200

To simplify the illustration,  we suppose both master and slave are from scratch without any data.

1. Set the binary log and server-id:

Master  - my.cnf
[mysqld]
log-bin=mysql-bin            //the binary log must be enabled
server-id=100                    //unique ID in MySQL cluster
Slave – my.cnf
[mysqld]
log-bin=mysql-bin            //the binary log must be enabled
server-id=200                    //unique ID in MySQL cluste

system restart may be required if the parameter changed.

2. Create the user dedicated for replication.

It is not neccessary but strongly recommended that a specified user will be created for replication

create user 'repl'@'192.168.139.200' identified by 'mysql';
grant replication slave on *.* to repl@'192.168.139.200' identified by
'mysql';

3. Get master status in master server

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 |      120 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

4. In the slave server, set the master server and synchronise parameters

mysql>change master to

master_host='192.168.139.100',

master_user='repl',

master_password='mysql',

master_log_file='mysql-bin.000001',

master_log_pos=120;

5. Start the mysql slave
6. Check the status

mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.139.100
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File: X002-relay-bin.000001
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
------ omitted ------

Now we can test by create database/table and insert data into the master server and confirm we can get in the slave server.