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 

MySQL 8 - Part I: Installation

1. Download MySQL packages from Oracle eDelivery (Server, Shell, ...)

[root@mysql-saigon mysql8]# ls -l
total 961092
-rw-r--r--. 1 root root   4010566 Mar 31 16:46 mysql-commercial-backup-8.0.37-1.1.el9.x86_64.rpm
-rw-r--r--. 1 root root  20388471 Mar 31 16:46 mysql-commercial-backup-debuginfo-8.0.37-1.1.el9.x86_64.rpm
-rw-r--r--. 1 root root   4105053 Mar 31 16:46 mysql-commercial-client-8.0.37-1.1.el9.x86_64.rpm
-rw-r--r--. 1 root root  27305094 Mar 31 16:46 mysql-commercial-client-debuginfo-8.0.37-1.1.el9.x86_64.rpm
-rw-r--r--. 1 root root   1457867 Mar 31 16:46 mysql-commercial-client-plugins-8.0.37-1.1.el9.x86_64.rpm
-rw-r--r--. 1 root root   2052547 Mar 31 16:46 mysql-commercial-client-plugins-debuginfo-8.0.37-1.1.el9.x86_64.rpm
-rw-r--r--. 1 root root    570629 Mar 31 16:46 mysql-commercial-common-8.0.37-1.1.el9.x86_64.rpm
-rw-r--r--. 1 root root   7680309 Mar 31 16:46 mysql-commercial-debuginfo-8.0.37-1.1.el9.x86_64.rpm
-rw-r--r--. 1 root root   7311390 Mar 31 16:46 mysql-commercial-devel-8.0.37-1.1.el9.x86_64.rpm
-rw-r--r--. 1 root root   2383993 Mar 31 16:46 mysql-commercial-icu-data-files-8.0.37-1.1.el9.x86_64.rpm
-rw-r--r--. 1 root root   1588746 Mar 31 16:46 mysql-commercial-libs-8.0.37-1.1.el9.x86_64.rpm
-rw-r--r--. 1 root root   2412596 Mar 31 16:47 mysql-commercial-libs-debuginfo-8.0.37-1.1.el9.x86_64.rpm
-rw-r--r--. 1 root root  57277741 Mar 31 16:48 mysql-commercial-server-8.0.37-1.1.el9.x86_64.rpm
-rw-r--r--. 1 root root  27151180 Mar 31 16:48 mysql-commercial-server-debug-8.0.37-1.1.el9.x86_64.rpm
-rw-r--r--. 1 root root 170157585 Mar 31 16:48 mysql-commercial-server-debug-debuginfo-8.0.37-1.1.el9.x86_64.rpm
-rw-r--r--. 1 root root 220976584 Mar 31 16:48 mysql-commercial-server-debuginfo-8.0.37-1.1.el9.x86_64.rpm
-rw-r--r--. 1 root root 366474178 Mar 31 16:50 mysql-commercial-test-8.0.37-1.1.el9.x86_64.rpm
-rw-r--r--. 1 root root  20801351 Mar 31 16:50 mysql-commercial-test-debuginfo-8.0.37-1.1.el9.x86_64.rpm
-rw-r--r--. 1 root root   4425344 Mar 31 16:57 mysql-router-commercial-8.0.37-1.1.el9.x86_64.rpm
-rw-r--r--. 1 root root  35571693 Mar 31 16:57 mysql-router-commercial-debuginfo-8.0.37-1.1.el9.x86_64.rpm
-rw-r--r--. 1 root root      1472 Apr 27 20:53 README.txt

EDB - pgBackRest - Part I: Installation

In this section, we will install pgBackRest using with EPAS (EDB Enterprise Advance Server). So we need to pay attention to something such as:

• User is enterprisedb, not postgres
• Grant right privileges to enterprisedb user

1. Configuring Postgres YUM repository

[root@edb-nhatrang ~]# dnf repolist 

[root@edb-nhatrang ~]# dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

[root@edb-nhatrang ~]# dnf repolist 

2. Configuring EPEL Repository

[root@edb-nhatrang ~]# dnf install https://dl.fedoraproject.org/pub/epel/epel-release-latest-9.noarch.rpm

[root@edb-nhatrang ~]# dnf repolist 

3. Install pgBackRest

[root@edb-nhatrang ~]# dnf install pgbackrest

Using EDB Postgres Advanced Server, the enterprisedb system user will execute the pgbackrest command. The following commands will change the ownership of the pgBackRest directories:

[root@edb-nhatrang ~]# chown -R enterprisedb: /var/lib/pgbackrest

[root@edb-nhatrang ~]# chown -R enterprisedb: /var/log/pgbackrest

[root@edb-nhatrang ~]# chown -R enterprisedb: /var/spool/pgbackrest

4. Verify installation

[enterprisedb@edb-nhatrang ~]$ pgbackrest version

pgBackRest 2.52

[enterprisedb@edb-nhatrang ~]$ less /etc/pgbackrest.conf 

[global]

repo1-path=/var/lib/pgbackrest

#[main]

#pg1-path=/var/lib/pgsql/10/data

Ref:

• EDB Docs - pgBackRest (enterprisedb.com)
• pgBackRest - Reliable PostgreSQL Backup & Restore
• How to Perform PostgreSQL Backup and Restore Operations with pgBackRest? | SERHAT CELIK DATABASE BLOG

EDB - Barman - Part IV: Remote Recovery Database

 Note:

• The recover command is used to recover a whole server.
• Remote recovery - Add the --remote-ssh-command option to the invocation of the recovery command. Doing this will allow Barman to execute the copy on a remote server, using the provided command to connect to the remote host. The SSH connection between Barman and the remote host must use the public key exchange authentication method.
• Barman wraps PostgreSQL's Point-in-Time Recovery (PITR), allowing you to specify a recovery target, either as a timestamp, as a restore label, or as a transaction ID.
• Barman 2.4 also adds the --standby-mode option for the recover command which, if specified, properly configures the recovered instance as a standby by creating a standby.signal file (from PostgreSQL versions lower than 12), or by adding standby_mode = on to the generated recovery configuration.
• Barman allows you to specify a target timeline for recovery using the --target-tli option. This can be set to a numeric timeline ID or one of the special values latest (to recover to the most recent timeline in the WAL archive) and current (to recover to the timeline which was current when the backup was taken). If this option is omitted then PostgreSQL versions 12 and above will recover to the latest timeline and PostgreSQL versions below 12 will recover to the current timeline.

1. Check backups

[barman@barman-server ~]$ barman list-backup employees4streaming
employees4streaming 20240614T102115 - Fri Jun 14 10:21:22 2024 - Size: 979.7 MiB - WAL Size: 0 B (tablespaces: tbs_ihrp_data:/u01/ihrp/data, tbs_ihrp_index:/u01/ihrp/index)
employees4streaming 20240613T161509 - Thu Jun 13 16:15:17 2024 - Size: 960.1 MiB - WAL Size: 9.8 MiB (tablespaces: tbs_ihrp_data:/u01/ihrp/data, tbs_ihrp_index:/u01/ihrp/index)
employees4streaming 20240613T153802 - Thu Jun 13 15:38:09 2024 - Size: 921.1 MiB - WAL Size: 19.2 MiB (tablespaces: tbs_ihrp_data:/u01/ihrp/data, tbs_ihrp_index:/u01/ihrp/index)
 
[barman@barman-server ~]$ ls -la /barman/backup/employees4streaming/incoming/
[barman@barman-server ~]$ ls -la /barman/backup/employees4streaming/streaming/
[barman@barman-server ~]$ ls -la /barman/backup/employees4streaming/wals/0000000100000001/
total 29768
drwxr-xr-x. 2 barman barman    4096 Jun 14 10:22 .
drwxr-xr-x. 3 barman barman      45 Jun 14 10:22 ..
-rw-------. 1 barman barman   16467 Jun 13 15:38 000000010000000100000004
-rw-------. 1 barman barman 5838855 Jun 13 15:50 000000010000000100000005
-rw-------. 1 barman barman 5812523 Jun 13 16:14 000000010000000100000006
-rw-------. 1 barman barman 5846212 Jun 13 16:14 000000010000000100000007
-rw-------. 1 barman barman 2635336 Jun 13 16:15 000000010000000100000008
-rw-------. 1 barman barman   16469 Jun 13 16:15 000000010000000100000009
-rw-------. 1 barman barman   16393 Jun 13 16:15 00000001000000010000000A
-rw-------. 1 barman barman   16570 Jun 13 16:48 00000001000000010000000B
-rw-------. 1 barman barman 5838885 Jun 14 09:58 00000001000000010000000C
-rw-------. 1 barman barman 4225404 Jun 14 10:16 00000001000000010000000D
-rw-------. 1 barman barman   16490 Jun 14 10:16 00000001000000010000000E
-rw-------. 1 barman barman   16486 Jun 14 10:17 00000001000000010000000F
-rw-------. 1 barman barman   16489 Jun 14 10:17 000000010000000100000010
-rw-------. 1 barman barman   16485 Jun 14 10:19 000000010000000100000011
-rw-------. 1 barman barman   16488 Jun 14 10:19 000000010000000100000012
-rw-------. 1 barman barman   16488 Jun 14 10:20 000000010000000100000013
-rw-------. 1 barman barman   16489 Jun 14 10:20 000000010000000100000014
-rw-------. 1 barman barman   16484 Jun 14 10:21 000000010000000100000015
-rw-------. 1 barman barman   16465 Jun 14 10:21 000000010000000100000016
 

EDB - Barman - Part III: Backup via Streaming protocol

In Part I, we have already configured backup via Streaming protocol. In this section, we will backup database with this method.

Note:

• The reuse_backup option can’t be used with the postgres backup method at this time
• If a backup has been compressed using the backup_compression option then barman recover is able to uncompress the backup on recovery. Using the recovery_staging_path option in order to choose a suitable location for the staging directory to unpcompress. Set recovery_staging_path in the global/server config or use the --recovery-staging-path option with the barman recover command

1. Check information

💩Database server

edb=# select * from pg_replication_slots;

-[ RECORD 1 ]-------+-----------
slot_name           | barman
plugin              |
slot_type           | physical
datoid              |
database            |
temporary           | f
active              | t
active_pid          | 11580
xmin                |
catalog_xmin        |
restart_lsn         | 0/C9000000
confirmed_flush_lsn |
wal_status          | reserved
safe_wal_size       |
two_phase           | f
conflicting         |

 

edb=# show archive_command ;
                     archive_command                     
---------------------------------------------------------
 barman-wal-archive barman-server employees4streaming %p
(1 row)

 

[enterprisedb@edb-saigon pg_wal]$ ls -ltr

total 98320

-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 10:01 0000000100000000000000CB

-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 10:01 0000000100000000000000CC

-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 10:03 0000000100000000000000CD

-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 10:03 0000000100000000000000CE

-rw-------. 1 enterprisedb enterprisedb      359 Jun 13 10:03 0000000100000000000000C8.00000028.backup

-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 11:01 0000000100000000000000C9
drwx------. 2 enterprisedb enterprisedb     8192 Jun 13 11:01 archive_status
-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 11:01 0000000100000000000000CA