Replication MySQL 5.7 Ubuntu 20.04

SERVER MASTER

192.168.2.10
mysql user : root 
mysql password : password_master$
mysql user : user_replikasi$
mysql password : password_replikasi$

SERVER SLAVE

192.168.2.20
mysql user : root 
mysql password : password_replikasi$

Setting on server master

1. Update Ubuntu

sudo apt update
sudo apt list --upgradable
sudo apt upgrade
sudo reboot

2. Create a Sudo User on Ubuntu

adduser example_user
adduser example_user sudo
su - example_user

3. Install MySQL

wget https://dev.mysql.com/get/mysql-apt-config_0.8.12-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.12-1_all.de
sudo apt update
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 467B942D3A79BD29
sudo apt update
sudo apt-cache policy mysql-server
sudo apt install -f mysql-client=5.7* mysql-community-server=5.7* mysql-server=5.7*
sudo mysql_secure_installation
mysql -u root -p
SELECT VERSION();

4. Masuk ke file sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf:

bind-address	= 192.168.2.10
server-id		= 1
log_bin		= /var/log/mysql/mysql-bin.log
log_bin_index	= /var/log/mysql/mysql-bin.log.index
max_binlog_size  	= 200M
expire_logs_days 	= 2

5. sudo systemctl restart mysql

6. sudo systemctl status mysql

7. create user mysql for replication

sudo mysql -u root -p
CREATE USER 'user_replikasi$'@'192.168.2.20' IDENTIFIED BY 'password_replikasi$';
GRANT REPLICATION SLAVE ON *.* TO 'user_replikasi$'@'192.168.2.20';
SHOW MASTER STATUS;

8. create user untuk aplikasi

CREATE USER 'sinergis_app'@'localhost' IDENTIFIED BY 'password_master$app';
	GRANT ALL PRIVILEGES ON *.* TO 'sinergis_app'@'localhost' WITH GRANT OPTION;
	CREATE USER 'sinergis_app'@'%' IDENTIFIED BY 'password_master$app';
	GRANT ALL PRIVILEGES ON *.* TO 'sinergis_app'@'%' WITH GRANT OPTION;
	FLUSH PRIVILEGES;

Setting on server slave

1. Update Ubuntu

sudo apt update
sudo apt list --upgradable
sudo apt upgrade
sudo reboot

2. Create a Sudo User on Ubuntu

adduser example_user
adduser example_user sudo
su - example_user

3. Install MySQL

wget https://dev.mysql.com/get/mysql-apt-config_0.8.12-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.12-1_all.de
sudo apt update
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 467B942D3A79BD29
sudo apt update
sudo apt-cache policy mysql-server
sudo apt install -f mysql-client=5.7* mysql-community-server=5.7* mysql-server=5.7*
sudo mysql_secure_installation
mysql -u root -p
SELECT VERSION();

4. Masuk ke file sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf:

bind-address	= 192.168.2.20
server-id		= 2
log_bin		= /var/log/mysql/mysql-bin.log
log_bin_index	= /var/log/mysql/mysql-bin.log.index
max_binlog_size  = 200M
expire_logs_days = 2

5. sudo systemctl restart mysql

6. sudo systemctl status mysql

7. user mysql for replication

sudo mysql -u root -p
stop slave;
CHANGE MASTER TO MASTER_HOST ='192.168.2.10', MASTER_USER ='user_replikasi$', MASTER_PASSWORD ='password_replikasi$', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 2065;
start slave

8. to set database back to Read-only mode

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = 1;

9. to set database back to Read + write mode

SET GLOBAL read_only = 0;
UNLOCK TABLES;