How to Setup Mysql Replication on Ubuntu 18.04 (Master-Master)

How to Setup Mysql Replication on Ubuntu 18.04 (Master-Master)
#===============================================================================

Server1:
OS: Ubuntu 18.04
DB: MySQL-Server-5.7
Hostname: db1
IPAddress: 1.0.8.11

Server2:
OS: Ubuntu 18.04
DB: MySQL-Server-5.7
Hostname: db2
IPAddress: 1.0.8.12


# MySQL 5.7 Non-interactive (silent) install of MySQL 5.7 on Ubuntu
MYSQL_ROOT_PASSWORD=root
echo "mysql-server mysql-server/root_password password $MYSQL_ROOT_PASSWORD" | debconf-set-selections
echo "mysql-server mysql-server/root_password_again password $MYSQL_ROOT_PASSWORD" | debconf-set-selections
DEBIAN_FRONTEND=noninteractive apt install -y mysql-server-5.7

Step 1. Edit & Modify the Configuration file of MySql  Server.
# nano /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 0.0.0.0     #comment this line if you want to access mysql remotly

,Add below lines at the end of the file.
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 1

Step 2. Restart Mysql Server.
# systemctl restart mysql.service

Step 3. Login to Mysql Server.
# mysql -u root -proot

Step 4.  Create a new user for Replication & specify the Password to that user.
mysql > create user 'replica1'@'%' identified by 'replica1pass';
mysql > GRANT REPLICATION SLAVE ON *.* TO 'replica1'@'%';
mysql > FLUSH PRIVILEGES;

Step 5. Execute below command to view the File & Position of Master Server 1.
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      219 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+


################# Login to Master Server 2.#################
Step 6. Edit & Modify the Configuration file of MySql Server.
nano /etc/mysql/mysql.conf.d/mysqld.cnf
# bind-address = 0.0.0.0

,Add below lines at the end of the file.
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 2

Step 7. Restart Mysql Server.
# systemctl restart mysql.service

Step 8. Login to Mysql Server.
# mysql -u root -p

Step 9.  Create a new user for Replication & Specify the Password to that user.
mysql > create user 'replica2'@'%' identified by 'replica2pass';
mysql > GRANT REPLICATION SLAVE ON *.* TO 'replica2'@'%';
mysql > FLUSH PRIVILEGES;

Step 10. Specify the following details as given below & make sure to Replace the following settings with your settings.
MASTER_HOST     :   IP Address of Master server 1
MASTER_USER     :   Replication User of Master server 1 that we had created in previous steps.
MASTER_PASSWORD :   Replication User Password of Master server 1 that we had created in previous steps.
MASTER_LOG_FILE :   Your Value of Master Log File of Master server 1.
MASTER_LOG_POS  :   Your Value of Master Log Position of Master server 1.
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST = '1.0.8.11', MASTER_USER = 'replica1', MASTER_PASSWORD = 'replica1pass', MASTER_LOG_FILE = 'mysql-bin.000003', MASTER_LOG_POS = 219;
mysql> start slave;

Step 11. Execute below command to view the File & Position of Master Server 2.
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

################# Login to Master Server 1. #################
Step 12. Specify the following details as given below & make sure to Replace the following settings with your settings.
MASTER_HOST     : IP Address of Master server 2.
MASTER_USER     : Replication User of Master server 2 that we had created in previous steps.
MASTER_PASSWORD : Replication User Password of Master server 2 that we had created in previous steps.
MASTER_LOG_FILE : Your Value of Master Log File of Master server 2.
MASTER_LOG_POS  : Your Value of Master Log Position of Master server 2.
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST = '1.0.8.12', MASTER_USER = 'replica2', MASTER_PASSWORD = 'replica2pass', MASTER_LOG_FILE = 'mysql-bin.000002', MASTER_LOG_POS = 154;
mysql> start slave;

Step 13. For testing a Replication we need to create a new database, it will automatically replicate on Master Server 2.
mysql > create database demo;

################# Login to Master Server 2.#################
Step 14. Login to Mysql Server
# mysql -u root -p

Step 15. View your Replicated Database by using below command.
mysql > show databases;