Home » Msyql » Configure mysql master-slave replication on centos/rhel/ubuntu

Configure mysql master-slave replication on centos/rhel/ubuntu

MySQL replication is a process that allows you to simply maintain multiple copies of a MySQL databases by having them copied without human intervention from a master to a slave database. It is very useful in conditions of Data Security, Fail-over Solution etc… This article will describe that how to mysql master-slave replication on centos/RHEL

Master IP Address is: 192.168.0.100′.
Slave IP Address is: 192.168.0.101′.
Configure mysql on Master server.

#vi /etc/my.conf on centos/RHEL
#vi /etc/mysql/my.cnf on Ubuntu
server-id=1
log-bin=mysql-bin
binlog-do-db=database1
binlog-do-db=database2
binlog-do-db=database2

Save the mysql configuration file and restart mysql server.

#service mysqld restart

Or

#service mysql restart

After successfully restart mysql server check master server status as following.

#mysql –u root –p
#password
mysql> SHOW MASTER STATUS;
+——————+———-+——————————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+——————————–+——————+
| mysql-bin.000262 | 82729 | db1,db2,db3 | manual,mysql |
+——————+———-+——————————–+——————+
1 row in set (0.00 sec)

Please note the File (mysql-bin.000262) and Position (82729) numbers, we required these numbers later on Slave server.
Now create replication user on master server by performing below command.

#mysql -u root –p
#password
mysql> CREATE USER ‘user’@’%’ IDENTIFIED BY ‘password’;
mysql> GRANT RELOAD, SUPER,REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘user’@’%’ IDENTIFIED BY ‘password’;
mysql> FLUSH PRIVILEGES;
Next be appropriate READ LOCK to databases to full dump on master with mysqldump command.
Mysql> USE db1;
mysql> FLUSH TABLES WITH READ LOCK;
Mysql>quit;
#mysqldump –u root –p databasename > dbdump.sql

We can use the LOAD DATA FROM MASTER; command on the slave for the same, But it has the disadvantage f you have a large database on a high-traffic production system.
Finally we have to unlock the tables in db1:

#mysql -u root -p
Enter password:
mysql> UNLOCK TABLES;
mysql> quit;

Now the configuration on the master is completed.

Configure mysql Slave server
To configure the Slave, open the /etc/my.conf file and append the following lines.

server-id=2
master-host= IP ADDRESS
master-user=username
master-password=password
master-connect-retry=60
replicate-do-db=database1

On the slave we first have to create the database db1:

#mysql -u root -p
Enter password:
mysql> CREATE DATABASE db;
mysql> quit;
#service mysqld restart
Or
#service mysql restart

If you want to go the LOAD DATA FROM MASTER; way then there is nothing you must do right now.

#mysql -u root -p
mysql> Enter password:
mysql> LOAD DATA FROM MASTER;
mysql> quit;

If you have imported the master db1 with help of mysqldump then restore it on slave.

#mysql -u root –p dataname < dbdump.sql

And finally after that perform the below command on slave.

#mysql -u root -p
Enter password:
mysql> SLAVE STOP;
mysql> CHANGE MASTER TO MASTER_HOST=’192.168.0.100′, MASTER_USER=’slave_user’, MASTER_PASSWORD=’‘, MASTER_LOG_FILE=’mysql-bin. 000262’, MASTER_LOG_POS=82729;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.100′
Master_User: username
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000262
Relay_Log_File: host9-relay-bin.000002
Relay_Master_Log_File: mysql-bin.000262
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db1
Replicate_Ignore_DB:
Replicate_Do_Table: if you replicating tables

That’s it! Now whenever database is updated on the master, all changes will be replicated to on the slave.

Note: If you want to replicate specific tables then do below on slave.

server-id=2
master-host=192.168.0.100
master-user=username
master-password=password
master-connect-retry=60
replicate-do-table= db.table_name
replicate-do-table=db.table_name

About

I am founder and webmaster of www.linuxpcfix.com and working as a Sr. Linux Administrator (Expertise on Linux/Unix & Cloud Server) and have been in the industry from last 7 years.

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Time limit is exhausted. Please reload the CAPTCHA.

Categorized Tag Cloud