Master-Slave Replication
Master DB Details:192.168.0.1
Slave DB Details:192.168.0.2
Install MySQL Server on both the servers: yum install mysql-server mysql
Phase1: Configure Master Server
1. vim /etc/my.cnf
2. Add below lines in the [mysqld] section
server-id = 1
binlog-do-db=(name of Database)
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
expire_logs_days = 1
max_binlog_size = 1G
3. Restart mysql service
4. Login to mysql as root user and create slave user for replication and grant privileges.
mysql -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '123';
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;'
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 387 | pcfunda | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> quit;
5. Note down the File and Position as it will be used on slave server for replication purpose.
6. mysql> UNLOCK TABLES; // Remove the read lock on the tables
mysql> quit;
Phase2: Configure Slave Server
1. vim /etc/my.cnf
2. Add below lines in the [mysqld] section
server-id = 2
3. Restart mysql service
4. Login to mysql as root user and run below query
mysql> CHANGE MASTER TO MASTER_HOST=' ',MASTER_USER='slaveuser', MASTER_PASSWORD='L0bjet_DeVeL0pEr$', MASTER_LOG_FILE='mysql-bin.000076', MASTER_LOG_POS=12253;
//MASTER_HOST=ip address of Master server
//MASRER_USER=slave user created at master server
//MASTER_PASSWORD=slave user password
//MASTER_LOG_FILE=Fil192.168.0.1e name noted from the output on the master server
//MASTER_LOG_POS=Position noted from the output on the master server
mysql> quit;
5. Restart mysql service
Setup done for replicating a database from master server to slave server.
In case we want to take replication of multiple databases then below changes are required
MASTER: add lines to my.cnf
=============================
binlog-do-db=database_name_1
binlog-do-db=database_name_2
binlog-do-db=database_name_3
SLAVE: add lines to my.cnf
=============================
replicate-do-db=database_name_1
replicate-do-db=database_name_2
replicate-do-db=database_name_3
To see Master Server Status
SHOW MASTER STATUS;
To see Slave server status
SHOW SLAVE STATUS \G;
0 on: "How To create Mysql Replica in Linux "