MySQL Master Slave Replication

MySQL Master Slave Replication is one way to spread the load across multiple servers.Replication allows you to take one database, make an exact copy of it on another server, and set one of them (the slave) to take all its updates from the other (the master). Its just an one-way communication (Master to Slave), Slave just receiving any event or update from Master, so if Master updated Slave will automaticly updated but if the update did on Slave ofcourse Master is won’t updated. If you want update Multiple Server simultaneously each other, you must try MySQL Master Master Replication.

Master address 10.11.218.251

Slave address 10.11.218.252

Setup Master

#vim /etc/my.cnf

log-bin=mysql-bin

server-id=1

binlog-ignore-db=”mysql”

* Restart MySQL Master Service

# killall mysql mysqld mysqld_safe

# mysqld_safe –user=mysql &

* Create an user on the Master server that allows replication on the Slave.

mysql> GRANT REPLICATION SLAVE ON *.* TO replicator@”10.11.218.252″ IDENTIFIED BY ‘123456’;

mysql> flush privileges;

mysql> quit

* Export database that you want to put in the Slave server. (in this case, i want to export haisandb database)

# mysqldump -u root -p haisandb > haisandb.sql

Enter password :

* And then lets see the Master status

mysql> show master status;

+——————+———-+————–+——————+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————+———-+————–+——————+

| mysql-bin.000001 | 340 | | mysql |

+——————+———-+————–+——————+

1 row in set (0.00 sec)

Note: the File and Position row for use on the Slave server.

* Copy your exported database file to the Slave server.

#scp haisandb.sql 10.11.218.252:/root/

Setup Slave

#vim /etc/my.cnf

server-id=2

* Restart MySQL Slave service

#killall mysql mysqld mysqld_safe

#mysqld_safe –user=mysql &

* Import haisandb database that we’ve export on the Master before. If the database doesn’t exist you need to create it first.

#mysql -u root -p

Password:

create database haisandb;

#mysql -u root -p haisandb <haisandb.sql

* Now configure replication and start the Slave service.

mysql>STOP SLAVE;

mysql> CHANGE MASTER TO MASTER_HOST=’10.11.218.251′, MASTER_USER=’replicator’, MASTER_PASSWORD=’123456′, MASTER_PORT=3306, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=340, MASTER_CONNECT_RETRY = 10 ;

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.11.218.251

Master_User: replicator

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 340

Relay_Log_File: mariadb-relay-bin.000002

Relay_Log_Pos: 391

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:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 340

Relay_Log_Space: 687

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

1 row in set (0.00 sec)