Friday, July 24, 2015

MySQL Master To Slave Replication

Scenario
Master Server: 192.168.213.232
Slave Server: 192.168.213.235
Database Name: sampledb
Replication user: sampledb
Replication password: sampledbpass

1. Configure MySQL on Master Server
a.) vi /etc/my.cnf
#set the following entries
[mysqld]
#master server id
server-id = 1
#local slave replication options
log_bin = /var/lib/mysql/master-bin.log
log_bin_index = /var/lib/mysql/master-bin.log.index
expire-logs-days = 10
log-slave-updates = true
b.) save and exit
c.) service mysqld restart
d.) mysql -u root -p
Enter password:
create database sampledb;
create user sampledb@'127.0.0.1' identified by 'sampledbpass';
create user sampledb@'localhost' identified by 'sampledbpass';
grant replication slave on *.* to 'sampledb'@'%' identified by 'sampledbpass';
show master status;
(take note of the file and position which will be used in the next step(master_log_file & master_log_pos))

2. Configure MySQL on Slave Server

a.) vi /etc/my.cnf
#set the following entries:
[mysqld]
#slave server id
server-id = 2
#local slave replication options
log_bin = /var/lib/mysql/slave-bin.log
log_bin_index = /var/lib/mysql/slave-bin.log.index
log-slave-updates = true
#replicate from master
master-host = 192.168.213.232
master-port = 3306
master-user = sampledb
master-password = sampledbpass
master-connect-retry = 60
#database to replicate
binlog-do-db = sampledb
b.) save and exit
c.) service mysqld restart
d.) mysql -u root -p
Enter password:
create database sampledb;
slave stop;
change master to master_host = '192.168.213.232', master_port = 3306, master_user = 'sampledb', master_password = 'sampledbpass', master_log_file = 'master-bin.xxxxxx', master_log_pos = xxx;
slave start;
show slave status\G;
(take note on the following status below)
SLAVE_IO_STATE: Waiting for master to send event
Slave_IO_Running: Yes
SLAVE_SQL_RUNNING: Yes

No comments:

Post a Comment