Linux
OS: RHEL 6.0
Master
IP Address: 192.168.176.218
Slave
IP Address: 192.168.176.216
Master
and Slave are on the same LAN Network
Master
and Slave has MySQL version 5.1.47 installed
Master
allows remote MySQL connections on port 3306.
Database
= apex (This is the database will be replicated to slave)
Phase
I: Configure Master Server (192.168.176.218) for Replication
Step
1 #yum install mysql-server mysql
Step
2 #vim /etc/my.cnf (Add the below lines in [mysqld] section)
server-id
= 1
binlog-do-db=apex
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
Step
3 #Restart mysqld server
#
/etc/init.d/mysqld restart
Step
4 # Login
into MySQL as root user and create the slave user and grant
privileges for replication. Replace slave_user
with user and your_password
with password.
mysql>
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED
BY 'your_password';
mysql>
FLUSH PRIVILEGES;
mysql>
FLUSH TABLES WITH READ LOCK;
mysql>
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
|
File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
|
mysql-bin.000003 | 11128001 | apex |
|
+------------------+----------+--------------+------------------+
1
row in set (0.00 sec)
mysql>
quit;
Please
write down the File (mysql-bin.000003) and Position (11128001)
numbers, we required these numbers later on Slave server. Next
apply READ LOCK to databases to export all the database and
master database information with mysqldump command.
#
mysqldump -u root -p --all-databases --master-data >
/root/dbdump.db
Once
you’ve dump all the databases, now again connect to mysql as root
user and unlcok tables.
mysql>
UNLOCK TABLES;
mysql>
quit;
Upload
the database dump file on Slave Server (192.168.1.2) using SCP
command.
scp
/root/dbdump.db root@192.168.1.2:/root/
Phase
II: Configure Slave Server (192.168.176.218) for Replication
#
yum install mysql-server mysql
#
vi /etc/my.cnf
server-id
= 2
master-host=192.168.176.216
master-connect-retry=60
master-user=slave_user
master-password=yourpassword
replicate-do-db=apex
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
Restart
the MySQL service.
#
/etc/init.d/mysqld restart
Login
into MySQL as root user and stop the slave. Then tell the slave to
where to look for Master log file, that we have write down on master
with SHOW MASTER STATUS; command as File (mysql-bin.000003)
and Position (11128001) numbers. You
must change 192.168.176.216 to the IP address of the Master Server,
and change the user and password accordingly.
#
mysql -u root -p
mysql>
slave stop;
mysql>
CHANGE MASTER TO MASTER_HOST='192.168.176.216',
MASTER_USER='slave_user', MASTER_PASSWORD='yourpassword',
MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=11128001;
mysql>
slave start;
mysql>
show slave status\G
Verifying
MySQL Replication on Master and Slave Server
It’s
really very important to know that the replication is working
perfectly. On Master server create table and insert some
values in it.
On
Master Server
mysql>
create database apex;
mysql>
use apex;
mysql>
CREATE TABLE employee (c int);
mysql>
INSERT INTO employee (c) VALUES (1);
mysql>
SELECT * FROM employee;
+------+
|
c |
+------+
|
1 |
+------+
1
row in set (0.00 sec)
On
Slave Server
Verifying
the SLAVE, by running the same command, it will return the
same values in the slave too.
mysql>
use apex;
mysql>
SELECT * FROM employee;
+------+
|
c |
+------+
|
1 |
+------+
1
row in set (0.00 sec)
That’s
it, finally you’ve configured MySQL Replication in a few
simple steps
