Skip to main content

MYSQL MASTER SLAVE REPLICATION

MySQL replication is a feature that allows us to maintain multiple copies of a MySQL data and data get automatically copied to slave server.
Here Iam using Masters and slave ip as:
Master ip : 1.1.1.1
Slave ip : 2.2.2.2

SETUP MYSQL MASTER

# yum install mysql-server mysql       [Install it based on centos version]

#vi /etc/my.cnf
server-id=1
binlog-do-db=databasename
relay-log=/var/lib/mysql/mysql-relay-bin
relay-log-index=/var/lib/mysql/mysql-relay-bin.index
log-error=/var/lib/mysql/mysql.err
master-info-file=/var/lib/mysql/mysql-master.info
relay-log-info-file=/var/lib/mysql/mysql-relay-log.info
log-bin=/var/lib/mysql/mysql-bin

#service mysqld restart

# mysql -u root -p
>GRANT REPLICATION SLAVE ON *.* TO 'replicauser'@'2.2.2.2' IDENTIFIED BY 'replicapass';
>FLUSH PRIVILEGES;
>FLUSH TABLES WITH READ LOCK;
  • replicauser = is new MySQL user for replicating purpose.
  • 2.2.2.2 = is Slave IP
  • replicapass = is password for the new MySQL user.
>SHOW MASTER STATUS;




In my case I have to note the mysql-bin.000002 and 106, yours will be different.

Now create database that we give in configuration file

#mysql -u root -p

 >CREATE DATABASE DATABASENAME;
>FLUSH PRIVILEGES;

>EXIT;

#mysqldump -u root -p databasename > databasename.sql
Here we want to specify name of our database instead of database name

Now we have to transfer our database to slave
#scp -P 22 /root/databasename.sql root@2.2.2.2:/root/

SETUP MYSQL SLAVE NODE

#yum install mysql-server mysql -y
#vi /etc/my.cnf

server-id=2
master-host=1.1.1.1
master-connect-retry=60
master-user=replicauser
master-password=replicapass
replicate-do-db=databasename
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin

Change 1.1.1.1 with IP address of Master server. Change replicauser, replicapass and databasename with yours.

#mysql -u root -p
CREATE DATABASE databasename;
quit;

Restore database you have transferred previously using this command:
#mysql -u root -p databasename < /root/databasename.sql

Restart MySQL server to apply / load changes:
#service mysqld restart

#mysql -u root -p
>slave stop;
>CHANGE MASTER TO MASTER_HOST='111.111.111.111', MASTER_USER='replicauser', MASTER_PASSWORD='replicapass', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=12345

((Change 1.1.1.1 replicauser, replicapass, mysql-bin.000001 and 12345 with yours.)) 

>slave start;
>show slave status\G




>quit;


FINISHING UP

Leave Slave server for a while and go back to Master server. Login back to MySQL shell at Master server:
#mysql -u root -p
>UNLOCK TABLES;
>quit;

TESTING

On Master Server
mysql> use databasename;
mysql> CREATE TABLE hello (c int);
mysql> INSERT INTO employee (c) VALUES (1);
mysql> SELECT * FROM hello;

O/P

+------+
|  c  |
+------+
|  1  |
+------+
1 row in set (0.00 sec)
On Slave Server
mysql> use databasename;
mysql> SELECT * FROM hello;

O/p
+------+
|  c  |
+------+
|  1  |
+------+
1 row in set (0.00 sec)