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.

 高橋木工所 スマートII キッチンボード 60Wオープン 幅60.3×奥行51×高さ195cm ホワイト 家電ボード 食器棚
ラギオール・アン・オブラック チーズナイフ ペーパーストーン [5172] LAGUIOLE EN
運賃見積り 直送品 TRUSCO 軽量150型背側板付棚 W1800XD300XH2100 4段 TLA76K-24
EBM 18-8 折りたたみ式 レインスタンド 48本立 3494000 ▲▲【smtb-u】
【最安値挑戦中!SPU他7倍〜】業務用エアコン 三菱 PMZ-ERMP63SFM P63 2.5馬力 単相200V ワイヤード [♪▲]
日本製 国産こたつ掛け敷き布団セット【かれん】 7尺長方形 和室 洋室 レトロ こたつ布団 コタツ布団 掛布団 掛け布団 敷布団 敷き布団 ふとん ぶとん こたつぶとん 炬燵布団 火燵布団 掛け敷きセ
01201608 GEO-501G ジオ?プロダクト 鍋5点セット【同梱配送不可】【代引き不可】【沖縄?離島配送不可】
TOKIO【PTD-T1890M】ミーティングテーブル
FUJIFILM X-A3 レンズキット [シルバー]
【工事費込セット(商品+基本工事)】[RAS-BJ40G2-W] 日立 ルームエアコン BJシリーズ 白くまくん ベーシックモデル 冷暖房:14畳程度 2017年モデル 単相200V・15A くらしセ

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