MySQl Replication

Mysql Server Replication

Prerequisites

This example assumes you have two servers running Ubuntu 18.04, which can communicate with each other over a private network. If your hosting provider doesn’t offer private IP addresses, you can use the public IP addresses and configure your firewall to allow traffic on port 3306 only from trusted sources.

Setup Networking

  1. cd /etc/netplan/
  2. Add to config file 10-lxc.yaml
  3. sudo netplan try

 

System Updates 

  • sudo apt-get update
  • sudo apt-get install mysql-server
  • sudo apt-get update
  • sudo apt-get install mysql-server

Configure Master

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

bind-address = MASTER IP
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

sudo systemctl restart mysql

sudo mysql

CREATE USER ‘replica’@’SLAVE IP’ IDENTIFIED BY ‘replica_password’;

GRANT REPLICATION SLAVE ON *.* TO ‘replica’@’SLAVE IP’;

SHOW MASTER STATUS\G

 

OUTPUT—–

*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 629
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

Configure Master

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

bind-address = SLAVE IP
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

sudo systemctl restart mysql

sudo mysql

CREATE USER ‘replica’@’MASTERIP’ IDENTIFIED BY ‘replica_password’;

GRANT REPLICATION SLAVE ON *.* TO ‘replica’@’MASTER IP’;

SHOW MASTER STATUS\G

 

OUTPUT—–

*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 620
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

STOP SLAVE;

CHANGE MASTER TO
MASTER_HOST=’MASTER IP ‘,
MASTER_USER=’replica’,
MASTER_PASSWORD=’replica_password’,
MASTER_LOG_FILE=’mysql-bin.000001′,
MASTER_LOG_POS=629;

START SLAVE;

TEST on MASTER

sudo mysql
CREATE DATABASE replicatest;
SHOW DATABASES;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| replicatest |
| sys |
+——————–+
5 rows in set (0.00 sec)
Verify the changes on your slave server 

Leave a Reply

Your email address will not be published.