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/mysql/my.cnf on Ubuntu
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.
Or
After successfully restart mysql server check master server status as following.
#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.
#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;
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:
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.
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:
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> 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.
And finally after that perform the below command on slave.
Enter password:
mysql> SLAVE STOP;
mysql> CHANGE MASTER TO MASTER_HOST=’192.168.0.100′, MASTER_USER=’slave_user’, MASTER_PASSWORD=’
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;
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.
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
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 since more than 14 years.
