MySQL : Master-Slave Replication

MySQL Server (Master) IP : 192.168.0.1
MySQL Server (Slave) IP : 192.168.0.2

1. Install MySQL in both server

2. Configure the MySQL Server (Master)

#vi /etc/my.cnf.d/mysql-server.cnf

- Add the following lines under the [mysqld] section.

server-id=1
log_bin=mysql-bin 

#sysemctl restart mysqld 

- Create a database USER that will be used to binf the master and slave.

#mysql -u root -p 

mysql> CREATE USER 'replica'@'192.168.0.2' IDENTIFIED BY 'XXXXXX';
mysql> GRANT REPLICATION SLAVE ON *.*TO 'replica'@'192.168.0.2'; 
mysql> FLUSH PRIVILEGES;
mysql> SHOW MASTER STATUS\G
mysql> EXIT;

3. Configure the MySQL Server (Slave)

#vi /etc/my.cnf.d/mysql-server.cnf

- Add the following lines under the [mysqld] section.

server-id=2
log_bin=mysql-bin 
binlog_do_db = exampledb #for specifies the database to be replicated.

#sysemctl restart mysqld 

- Configures the Slave node to replicate from the Master node

#mysql -u root -p 
#mysql> STOP SLAVE;
#mysql> CHANGE MASTER TO
MASTER_HOST='192.168.0.1', 
MASTER_PORT=3306,
MASTER_USER='replica' ,
MASTER_PASSWORD='XXXXXX',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1232; 
#mysql> START SLAVE;

4. Testing MySQL Master-Slave replication

Master

#mysql -u root -p 
#mysql> CREATE DATABASE replication_db; 
#mysql> SHOW DATABASES;

Slave

#mysql -u root -p 
#mysql> SHOW DATABASES;

Source:

• http://www.tecmint.com/how-to-setup-mysql-master-slave-replication-in-rhel-centos-fedora/
• https://www.howtoforge.com/how-to-skip-certain-errors-in-mysql-replication