Tuesday, June 18, 2024

MySQL 8 - Part II: Master - Slave Replication with Binary log

Theory:
The traditional method is based on replicating events from the source's binary log, and requires the log files and positions in them to be synchronized between source and replica.

1. General information

Master Node
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)

Slave Node
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 Master/Slave Node
  • 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 Master/Slave Server config file

Master Node
[root@mysql-saigon ~]# vi /etc/my.cnf
server-id=1
bind-address=192.168.56.59
log-bin=saigon-bin

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

Slave Node
[root@mysql-hanoi ~]# vi /etc/my.cnf
server-id=2
bind-address=192.168.56.29
log-bin=hanoi-bin
read_only=1 (this variable prevents normal user to execute DDL, DML on Slave server)

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

4. Master Node - Create user for replication

mysql> select host,user from mysql.user where user='replica';
Empty set (0.01 sec)

mysql> create user 'replica'@'%' identified with mysql_native_password by 'Qazxsw123$';
Query OK, 0 rows affected (0.02 sec)

mysql> select host,user from mysql.user where user='replica';
+---------------+---------+
| host          | user    |
+---------------+---------+
| %             | replica |
+---------------+---------+
1 row in set (0.00 sec)

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

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

5. Master Node - Check Master Log Position

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| saigon-bin.000001 |      857 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

6. Master Node - Backup All database of Master and transfer to Slave Node

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

[mysql@mysql-saigon ~]$ mysqldump -u root -p  -A > /source/allDBdump.sql
Enter password: 

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

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

7. Slave Node - Restore Database Dump into Slave Server

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

[mysql@mysql-hanoi ~]$ mysql -u root -p  < /source/allDBdump.sql 
Enter password: 

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

8. Slave Node - Setup Slave to Communicate Master Database

mysql> stop replica;
mysql> show warnings;

mysql> change master to master_host='192.168.56.59', master_user='replica', master_password='Qazxsw123$', master_log_file='saigon-bin.000001', master_log_pos=857;
Query OK, 0 rows affected, 8 warnings (0.07 sec)

mysql> start replica;
mysql> show processlist;

mysql> show replica status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.56.59
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: saigon-bin.000001
          Read_Master_Log_Pos: 857
               Relay_Log_File: mysql-hanoi-relay-bin.000004
                Relay_Log_Pos: 327
        Relay_Master_Log_File: saigon-bin.000001
             Slave_IO_Running: Yes
            Slave_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_Master_Log_Pos: 857
              Relay_Log_Space: 713
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 871fbf43-2b13-11ef-8c45-08002746eacf
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)
ERROR: 
No query specified

mysql> select * from performance_schema.replication_applier_status_by_worker\G;
mysql> show processlist;

9. Test Replication and Verify the Results

Master Node
- Create/Drop tables
- Insert/Update/Delete 

Slave Node
- Verify change data

10. Purge Binary log files

mysql> show binary logs;
+-------------------+-----------+-----------+
| Log_name          | File_size | Encrypted |
+-------------------+-----------+-----------+
| saigon-bin.000001 |       180 | No        |
| saigon-bin.000002 |       659 | No        |
| saigon-bin.000003 |     14301 | No        |
| saigon-bin.000004 |     14301 | No        |
| saigon-bin.000005 |       157 | No        |
+-------------------+-----------+-----------+
5 rows in set (0.01 sec)

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| saigon-bin.000005 |      157 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> purge binary logs to 'saigon-bin.000003';
Query OK, 0 rows affected (0.05 sec)

+-------------------+-----------+-----------+
| Log_name          | File_size | Encrypted |
+-------------------+-----------+-----------+
| saigon-bin.000003 |     14301 | No        |
| saigon-bin.000004 |     14301 | No        |
| saigon-bin.000005 |       157 | No        |
+-------------------+-----------+-----------+
3 rows in set (0.00 sec)

mysql> PURGE BINARY LOGS BEFORE now();
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show binary logs;
+-------------------+-----------+-----------+
| Log_name          | File_size | Encrypted |
+-------------------+-----------+-----------+
| saigon-bin.000005 |       157 | No        |
+-------------------+-----------+-----------+
1 row in set (0.00 sec)

Default values for configuring expired log
mysql> show variables like '%binlog_expire%';
+-------------------------------+--------+
| Variable_name                     | Value  |
+-------------------------------+--------+
| binlog_expire_logs_auto_purge | ON     |
| binlog_expire_logs_seconds    | 604800 |            // 7 days
+-------------------------------+--------+
2 rows in set (0.00 sec)

Recommendation: Do not purge logs manually. Configuring purging automatically in /etc/my.cnf and restart service
  • expire_logs_days = 10     // using with old version, disable binlog_expire_logs_seconds before setting this param
  • max_binlog_size = 100M
Ref:
at
Labels: