MySQL replication is a process that allows you to easily maintain multiple copies of a MySQL data by having them copied automatically from a master to a slave database. This can helpful for many reasons including facilating a backup for the data, a way to load balancing for MySql,or simply for scale out
We use below IP address for our set up in this example.
123.34.56.79- Master Database
123.23.34.67- Slave Database
Setup
sudo apt-get install mysql-server mysql-client1. Configure Master Database
sudo vi /etc/mysql/my.cnf
We need to make few changes in this file.
bind-address = 127.0.0.1tobind-address = 123.34.56.79After that we need to change in another line.server-id = 1Now change in log_bin line.
log_bin = /var/log/mysql/mysql-bin.logFinally se the database which we want to replicate.
binlog_do_db = newdbSave and exit configuration file.
Restart MySql service.
sudo service mysql restartNext, we need to grant privileges to the slave. The command should be
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';FLUSH PRIVILEGES;Now,
USE newdb;Lock the database to prevent new changes.
FLUSH TABLES WITH READ LOCK;and then,
SHOW MASTER STATUS;mysql> SHOW MASTER STATUS;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000001 | 137 | newdab | |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)From this position slave db will start replicating. Keep this number with you will use it later.Keep current MySql console as it is so it will remian locked as it is. So now open another terminal window and start dump of master db by using below command.mysqldump -u root -p --opt newdb > newdb.sqlafter that come to oringinal window and type below commandUnlock tables;Quit;Now let us configure slave database.2. Configure the Slave DB
Login into MySql shell in slave server and create new database.
CREATE DATABASE newdb;Exit;Import database which we have already exported from master database.mysql -u root -p newdb < /path/to/newdb.sqlNow need to change in configuration file.
sudo vi /etc/mysql/my.cnfFirst of all we change the server-id value which we have set 1 for master so for slave it is something different. We need to ensure that the value of server-id must be unique.
server-id = 2Following three crieteria we need to feel properly.
relay-log = /var/log/mysql/mysql-relay-bin.loglog_bin = /var/log/mysql/mysql-bin.logbinlog_do_db = newdbNow need to refresh MySql for getting effect of change of configuration.sudo service mysql restartNext step is to enable MySql replication from console.
CHANGE MASTER TO MASTER_HOST='123.34.56.79',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 187;That's it !! you have configured a master and slave server.
Now Activating the slave server.
START SLAVE;For check the details of the slave replication by below command.
SHOW SLAVE STATUSGIf you find any issue with connection you can use below command to skip it.
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START; Done.