Snappy

Helpdesk Support

Official Supportmonk Company Blog!

MYSQL – Replication [ Master – Slave replication]

March 1, 2013 , 8:36 am


You can replicate your mysql server to one or more slave server. Mysql replication is a synchronous process because your master server will not wait for the slave servers to apply the changes and slave server need not be be connected permanently to the master server. Slave server will update all the changes latter and replicate all the changes. All the write operations are performing in the master server and read can takes place in slave box. Asynchronous replication is more faster than synchronous replication.

Here I am going to demonstrate  how to create a Master-Slave mysql replication. For example Master server is 172.16.9.154 and slave server is 172.16.9.155 . Here we have one slave server.

Mysql – Master server configuration :-

In master server we should specify the unique” ID “and ” master bin log” .  From master bin log slave getting the data from the master server.

Step 1. Shut down the mysql server

Step 2. open your my.cnf file and update the settings under  [mysqld] section.

++++++++

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

server-id = 1

log-bin=admod-bin

relay-log=db-relay-bin

++++++++

Here binary logs are enabled using “log-bin” and  the name for the log-bin is admod-bin, you can use any name as per your wish

server-id=1 , it should be a positive integer , by default this value will be “0”

relay-log are executed on slave server. Which contains the events reads from the master bin log.

 

After this setting we need to restart the mysql server.

Step 3: Create a replication user.

In order to connect our slave server to master and get the update , we need to create a user and grant this user for the privilege to get the mysql updates. So we should create this user in our master server and grant replication privilege for the slave server for the mysql databases.

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘replication’@’172.16.9.155’ IDENTIFIED BY ‘password’;

Here user name is replication, password is password.

 

Step 4: mysql> FLUSH TABLES WITH READ LOCK;

Flush all the tables and block write statements

mysql> SHOW MASTER STATUS \G;

***************************

File: admod-bin.00006

Position: 550

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

***************************

Mysql – Slave server configuration :-

Step 1: open your my.cnf file and update the settings under  [mysqld] section.

We should provide server ID, master server IP, replication user name and its password. So that slave server can connect to the master server.

++++++++

[mysqld]

server-id=2

master-host = 172.16.9.154

master-user = replication

master-password = password

master-port = 3306

++++++++

After this edit restart the mysql server in your slave box. Master information will be stored in  /var/lib/mysql/master.info your slave server.

Step 2: mysql> stop slave;

Step 3: mysql> reset slave;

Step 4: mysql> CHANGE MASTER TO MASTER_HOST=’172.16.9.154′, MASTER_USER=’replication’, MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’admod-bin.00006′;

Here : 172.16.9.154 master server IP , ” replication” replication user we have created in master box, admod-bin.00006 is the bin-log and the value we have get it from “SHOW MASTER STATUS” on Master box.

Step 5: mysql> START SLAVE;

Step 6: mysql> show slave status \G;

***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.9.154

Master_User: replication

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: admod-bin.00006

Read_Master_Log_Pos: 550

Relay_Log_File: mysqld-relay-bin.00001

Relay_Log_Pos: 934

Relay_Master_Log_File: admod-bin.00006

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0

***************************

Slave server read the changes in bin-log and these changes update to the relay-logs  and update to slave SQL Thread. We can verify our replication status is fine by checking Seconds_Behind_Master,Slave_IO_Running and Slave_SQL_Running values.

 

Envelope Icon

Get Updates Your Email!

Subscribe to Supportmonk and receive blog posts to your email!

Subscribe Via Email

SupportMonk on Facebook


Outsourced Customer Support