Mysql8.0 Installation with Master-Replica setup
Published: 2023-10-21 19:17:25

Update the package repository:    
    sudo apt update

Install MySQL Server: 
sudo apt install mysql-server

Check the MySQL service status:
sudo systemctl status mysql

Start MySQL service: 
sudo systemctl start mysql

Stop MySQL service:  
sudo systemctl stop mysql

Enable MySQL to start on boot: 
sudo systemctl enable mysql

Check MySQL service status again:
sudo systemctl status mysql

We have to do the above steps in two different DB servers, then below steps will be done for making one server as Master and another to Replica.


**Setting Up Master and Replica Servers:**
Database Server 1 (Master, with Binary Log Enabled)
Stop the MySQL service:
           sudo systemctl stop mysql
Edit the MySQL configuration file:
            sudo vi /etc/mysql/my.cnf
Add or modify the following settings in the [mysqld] section:
[mysqld]
           bind-address =0.0.0.0
           server-id = 1
           log_bin = mysql-bin
Start the MySQL service: 
            sudo systemctl start mysql

 Database Server 2 (Replica, with Binary Log Enabled)
Stop the MySQL service:
           sudo systemctl stop mysql
Edit the MySQL configuration file:
            sudo vi /etc/mysql/my.cnf
Add or modify the following settings in the [mysqld] section:
[mysqld]
           bind-address =0.0.0.0
           server-id = 1
           log_bin = mysql-bin
Start the MySQL service: 
            sudo systemctl start mysql

Verify that both database servers are working fine.

**Setting Up Replication**
===================================
We have to set up the replication between DB server 1 and DB server 2. We will make Db server 1 as Master and DB server 2 as Replica.

 **On the Master Server:**

Create a new user for the replication.

CREATE USER 'replica1'@'%' IDENTIFIED BY 'Ratxzhudfs!34Ihv';
GRANT REPLICATION SLAVE ON *.*TO 'replica1'@'%';
FLUSH PRIVILEGES;

**Check for the Master status.**

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 157
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
Make note of the values, especially File and Position.

**Check for the master host ip: mysql> \! ip r**


Below always read_only=OFF means it is always ready for the Write. (master database).

mysql> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF    |
+---------------+-------+
1 row in set (0.00 sec)



**On the Replica Server:**

Before setting up replication, check if there's an existing replication process:

Show replica status\G

If there's an existing replication, reset it:
mysql> reset replica;
Query OK, 0 rows affected (0.02 sec)

Set up replication with the following command, replacing the placeholders with appropriate values:

CHANGE REPLICATION SOURCE TO SOURCE_HOST='MasterIP', SOURCE_USER='replica1', SOURCE_PASSWORD='Ratxzhudfs!34Ihv',SOURCE_LOG_FILE='mysql-bin.000001', SOURCE_LOG_POS=157, GET_SOURCE_PUBLIC_KEY=1; 
mysql>start replica;

Ensure Slave_IO_Running and Slave_SQL_Running are set to Yes.
Ensure the Seconds_Behind_Master is 0, indicating that the replica is in sync.
Then do below to check Master and Replica are in sync.

**Show replica status\G**

mysql> show replica status\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: Master IP
                  Source_User: replica1
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.000001
          Read_Source_Log_Pos: 157
               Relay_Log_File: ip-172-34-48-192-relay-bin.000002
                Relay_Log_Pos: 326
        Relay_Source_Log_File: mysql-bin.000001
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 157
              Relay_Log_Space: 547
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Source_Server_Id: 1
                  Source_UUID: 4381638b-6d7e-11ee-b231-02292c44f988
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 1
            Network_Namespace:
1 row in set (0.00 sec)


**Note: Seconds_Behind_Source: 0 means Master is in-sync with Replica.**

It should be always ON if the database is Replica.

mysql> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)


**Manual Failover:**

When Master gone down writes will fail and below are the details that on replica side to check on Show Replica status\G

You may see in the slave's status that the replication is running (Slave_IO_Running and Slave_SQL_Running are set to Yes), but the replication position (Read_Master_Log_Pos and Exec_Master_Log_Pos) will not change.

                                                              OR

The slave's status will show that replication is not running (Slave_IO_Running and Slave_SQL_Running are set to No). You will typically see errors in the slave's error log indicating that the connection to the master was lost

**Performing the manual failover:**
Promote the Slave to Master:
On the current slave server, stop the replication with STOP Replica and do reset replica all;
Use CHANGE MASTER TO to clear the replication settings (so this server becomes independent of the old master).
Update your applications to point to the new master server (the former slave).
Start writing data to the new master. It is now the primary database server.
**Update the Old Master:**
On the old master server, you should reconfigure it as a slave to the new master.
Use CHANGE MASTER TO on the old master to set up replication from the new master.
Start the replication on the old master with START Replica.