Tuesday, June 18, 2024

MySQL 8 - Part III: Master - Master Replication with GTID

Theory:
  • The method based on global transaction identifiers (GTIDs) is transactional and therefore does not require working with log files or positions within these files, which greatly simplifies many common replication tasks. 
  • Replication using GTIDs guarantees consistency between source and replica as long as all transactions committed on the source have also been applied on the replica.
  • Enabling log-slave-updates on a replica means, the replica writes updates that are received from a master to its own binary logs. So a replica can become a intermediate master of another replica. This option is a stepping stone for various simple to complex level replication setups and new technologies like Group Replication.

1. General information

Master Node 01
IP: 192.168.56.59
Hostname: edb-saigon.taolaoxibup.com
OS: Red Hat Enterprise Linux release 9.4 (Plow)
MySQL version: Ver 8.0.37-commercial for Linux on x86_64 (MySQL Enterprise Server - Commercial)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| classicmodels      |
| information_schema |
| mysql              |
| performance_schema |
| sg2hn              |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

Master Node 02
IP: 192.168.56.29
Hostname: edb-hanoi.taolaoxibup.com
OS: Red Hat Enterprise Linux release 9.4 (Plow)
MySQL version: Ver 8.0.37-commercial for Linux on x86_64 (MySQL Enterprise Server - Commercial)

2. Preparation on all Master Nodes
  • Turn off Firewall
  • Disable SELinux
  • Configuring /etc/hosts
    • 192.168.56.59   edb-saigon.taolaoxibup.com     edb-saigon
    • 192.168.56.29   edb-hanoi.taolaoxibup.com       edb-hanoi

3. Update Configuration file on all Master Nodes

Master Node 01
[root@mysql-saigon ~]# vi /etc/my.cnf
server-id=1
bind-address=192.168.56.59
log-bin=saigon-bin
gtid-mode=ON
enforce-gtid-consistency
log-slave-updates

[root@mysql-saigon ~]# systemctl restart mysqld.service 

Master Node 02
[root@mysql-hanoi ~]# vi /etc/my.cnf
server-id=2
bind-address=192.168.56.29
log-bin=hanoi-bin
gtid-mode=ON
enforce-gtid-consistency
log-slave-updates

[root@mysql-hanoi ~]# systemctl restart mysqld.service 

4. Master Node 1 - Create user for replication

mysql> create user 'replica_sg2hn'@'%' identified with mysql_native_password by 'xxxxxxxx';
Query OK, 0 rows affected (0.06 sec)

mysql> create user 'replica_hn2sg'@'%' identified with mysql_native_password by 'xxxxxxxx';
Query OK, 0 rows affected (0.06 sec)

mysql> grant replication slave on *.* to 'replica_sg2hn'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'replica_hn2sg'@'%';
Query OK, 0 rows affected (0.03 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

5. Master Node 1 - Backup All database of Master Node 1 and transfer to Master Node 2

[mysql@mysql-saigon ~]$ mysqldump --all-databases --flush-privileges --single-transaction --flush-logs --triggers --routines --events --hex-blob --user=root  --password > /source/allDBs.sql

[mysql@mysql-saigon ~]$ scp /source/allDBs.sql mysql@192.168.56.29:/source

6. Master Node 2 - Import into Database

mysql> select host, user from mysql.user;

mysql> source /source/allDBs.sql

mysql> select host, user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | cherry           |
| %         | hanoi_replica    |
| %         | replica          |
| %         | replica_hn2sg    |
| %         | replica_sg2hn    |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
9 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| classicmodels      |
| information_schema |
| mysql              |
| performance_schema |
| sg2hn              |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

7. Setup Communication between Master Nodes

Master Node 1

mysql> stop replica;
Query OK, 0 rows affected (0.02 sec)

mysql> CHANGE MASTER TO MASTER_HOST = '192.168.56.29', MASTER_PORT = 3306, MASTER_USER = 'replica_sg2hn', MASTER_PASSWORD = 'xxxxxxxx', MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 8 warnings (0.06 sec)

mysql> start replica;
Query OK, 0 rows affected (0.04 sec)

mysql> show replica status\G;
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 192.168.56.29
                  Source_User: replica_sg2hn
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: hanoi-bin.000004
          Read_Source_Log_Pos: 157
               Relay_Log_File: mysql-saigon-relay-bin.000002
                Relay_Log_Pos: 373
        Relay_Source_Log_File: hanoi-bin.000004
           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: 590
              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: 2
                  Source_UUID: 38714d50-2ca6-11ef-97b1-08002746eacf
             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: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Source_TLS_Version: 
       Source_public_key_path: 
        Get_Source_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

Master Node 2

mysql> stop replica;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST = '192.168.56.59', MASTER_PORT = 3306, MASTER_USER = 'replica_hn2sg', MASTER_PASSWORD = 'xxxxxxxx', MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 8 warnings (0.06 sec)

mysql> start replica;
Query OK, 0 rows affected (0.03 sec)

mysql> show replica status\G;
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 192.168.56.59
                  Source_User: replica_hn2sg
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: saigon-bin.000008
          Read_Source_Log_Pos: 157
               Relay_Log_File: mysql-hanoi-relay-bin.000002
                Relay_Log_Pos: 375
        Relay_Source_Log_File: saigon-bin.000008
           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: 591
              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: 871fbf43-2b13-11ef-8c45-08002746eacf
             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: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Source_TLS_Version: 
       Source_public_key_path: 
        Get_Source_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

mysql> SHOW REPLICAS;
mysql> SHOW PROCESSLIST;

8. Test the Replication
8.1 Change data on all Master Nodes

Master Node 1

mysql> show tables;
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| customers               |
| employees               |
| offices                 |
| orderdetails            |
| orders                  |
| payments                |
| productlines            |
| products                |
+-------------------------+
8 rows in set (0.00 sec)

mysql> create table temp_sg_01 as select * from customers;
Query OK, 122 rows affected (0.07 sec)
Records: 122  Duplicates: 0  Warnings: 0

Master Node 2

mysql> show tables;
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| customers               |
| employees               |
| offices                 |
| orderdetails            |
| orders                  |
| payments                |
| productlines            |
| products                |
+-------------------------+
8 rows in set (0.00 sec)

mysql> create table temp_hn_01 as select * from customers;
Query OK, 122 rows affected (0.07 sec)
Records: 122  Duplicates: 0  Warnings: 0

8.2 Verify information

Master Node 1

mysql> show tables;
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| customers               |
| employees               |
| offices                 |
| orderdetails            |
| orders                  |
| payments                |
| productlines            |
| products                |
| temp_hn_01              |
| temp_sg_01              |
+-------------------------+
10 rows in set (0.01 sec)

Master Node 2

mysql> show tables;
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| customers               |
| employees               |
| offices                 |
| orderdetails            |
| orders                  |
| payments                |
| productlines            |
| products                |
| temp_hn_01              |
| temp_sg_01              |
+-------------------------+
10 rows in set (0.01 sec)

Ref:
at
Labels: