MySql replication is a method to maintain multiple copies of a database on different machines automatically. This arrangement is referred as master-slave replication. By using this arrangement, you can take the backup of the database, you can use one database for read and another for write, you can distribute the transaction load to all databases etc.
The arrangements vary as the requirements. There could be multiple masters and multiple slaves or you could create the tree hierarchy or array of database servers. Please read other reference books or websites for details.
Here, we are going to setup the simple minimalistic configuration. We will have one master and one slave. We have the two MySQL databases on centos operating system.
In this case, whatever is changed on the master database system, it is replicated on slave automatically.
Master Configuration
1 - Do the following configuration in my.cnf file
2 – Restart the mysql;
3 – Login to mysql database and create the user for this replication purpose
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
4 – Flush the permissions
FLUSH PRIVILEGES;
Now you could check the master status
Now you can export the data form db1 database to the other database where you want to replicate the data. So that you could have the records in the slave database those were in the master database before you do it.
Slave Configuration
1- Create the same database as on the master which must be replicated. This database could be created using the importing the database dump.
2 – Edit the my.cnf file
3 – Restart the database
Enable replication with configuring the master settings in slave database:
CHANGE MASTER TO MASTER_HOST='172.17.0.2',MASTER_PORT=3306,MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=120
4 - Start slave
5 - Run command "SHOW SLAVE STATUS\G"
If you see the things like above, the replication will be working fine.
Master Records
Slave Records
