Sunday, November 15, 2026

MySQL 8.4 LTS Master-Slave Enterprise Runbook

MySQL 8.4 LTS Master-Slave Enterprise Runbook (Cross-Platform)

MySQL 8.4 LTS Master-Slave Enterprise Runbook

Live Dynamic Engine Configurator

Master & Slave Node Parameters & Resource Sizing

Edits update all commands, paths, and buffer pool parameters in real-time.

Primary Node (Master) server_id = 1
11G
Secondary Node (Slave) server_id = 2
11G

Phase 00: Pre-Installation & Capacity Planning

STEP 0.1

Verify Disk Partitions, Mountpoints & Inode Capacity

CRITICAL

Ensure block storage backing the datadir has adequate free space (>3x working dataset size) on both servers.

Run on Master (db-master.internal) 
df -hT /var/lib/mysql
mount | grep -E 'ext4|xfs'Get-CimInstance Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3} | Select-Object DeviceID, @{Name="FreeSpace(GB)";Expression={"{0:N2}" -f ($_.FreeSpace/1GB)}}
Run on Slave (db-slave.internal) 
df -hT /var/lib/mysql
mount | grep -E 'ext4|xfs'Get-CimInstance Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3} | Select-Object DeviceID, @{Name="FreeSpace(GB)";Expression={"{0:N2}" -f ($_.FreeSpace/1GB)}}
STEP 0.2

Check Network Ports & Firewall Reachability

HIGH

Verify bidirectional TCP communication over port 3306 between both nodes.

Run on Master (db-master.internal) 
ping -c 3 192.168.10.51
nc -zv 192.168.10.51 3306Test-NetConnection -ComputerName 192.168.10.51 -Port 3306
Run on Slave (db-slave.internal) 
ping -c 3 192.168.10.50
nc -zv 192.168.10.50 3306Test-NetConnection -ComputerName 192.168.10.50 -Port 3306

Phase 01: OS Kernel, Storage & Security Preparation

STEP 1.1

Disable Transparent Huge Pages (Linux) / Verify Lock Pages Privilege (Windows)

HIGH

Prevent memory fragmentation on Linux or configure large page privileges on Windows.

Run on Master (db-master.internal) 
echo never | sudo tee /sys/kernel/mm/transparent_hugepage/enabled
echo never | sudo tee /sys/kernel/mm/transparent_hugepage/defragWrite-Host "Verify SeLockMemoryPrivilege for MySQL service account via secpol.msc"
Run on Slave (db-slave.internal) 
echo never | sudo tee /sys/kernel/mm/transparent_hugepage/enabled
echo never | sudo tee /sys/kernel/mm/transparent_hugepage/defragWrite-Host "Verify SeLockMemoryPrivilege for MySQL service account via secpol.msc"
STEP 1.2

Configure Firewall Rules for Port 3306 & 33060

HIGH

Open TCP ports in OS firewall across both servers.

Run on Master (db-master.internal) 
sudo firewall-cmd --add-port=3306/tcp --permanent && sudo firewall-cmd --reloadsudo ufw allow 3306/tcp && sudo ufw status verboseNew-NetFirewallRule -DisplayName "MySQL 8.4 Port 3306" -Direction Inbound -Protocol TCP -LocalPort 3306 -Action Allow
Run on Slave (db-slave.internal) 
sudo firewall-cmd --add-port=3306/tcp --permanent && sudo firewall-cmd --reloadsudo ufw allow 3306/tcp && sudo ufw status verboseNew-NetFirewallRule -DisplayName "MySQL 8.4 Port 3306" -Direction Inbound -Protocol TCP -LocalPort 3306 -Action Allow

Phase 02: MySQL 8.4 LTS Engine Installation

STEP 2.1

Deploy Binaries & Initialize Datadir

CRITICAL

Extract archive binaries or packages and initialize database instance using custom configured Base Paths and Data Directories.

Run on Master (db-master.internal) 
sudo groupadd mysql && sudo useradd -r -g mysql -s /bin/false mysql
sudo mkdir -p /usr/local/mysql /var/lib/mysql
sudo chown -R mysql:mysql /usr/local/mysql /var/lib/mysql
cd /tmp && wget https://cdn.mysql.com/Downloads/MySQL-8.4/mysql-8.4.3-linux-glibc228-x86_64.tar.xz
sudo tar -xf mysql-8.4.3-linux-glibc228-x86_64.tar.xz -C /usr/local/mysql --strip-components=1
sudo /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/var/lib/mysqlExpand-Archive -Path "C:\Downloads\mysql-8.4.3-winx64.zip" -DestinationPath "C:\Program Files\MySQL"
New-Item -ItemType Directory -Force -Path "C:\ProgramData\MySQL\Data"
& "C:\Program Files\MySQL\bin\mysqld.exe" --initialize-insecure --basedir="C:\Program Files\MySQL" --datadir="C:\ProgramData\MySQL\Data"
Run on Slave (db-slave.internal) 
sudo groupadd mysql && sudo useradd -r -g mysql -s /bin/false mysql
sudo mkdir -p /usr/local/mysql /var/lib/mysql
sudo chown -R mysql:mysql /usr/local/mysql /var/lib/mysql
cd /tmp && wget https://cdn.mysql.com/Downloads/MySQL-8.4/mysql-8.4.3-linux-glibc228-x86_64.tar.xz
sudo tar -xf mysql-8.4.3-linux-glibc228-x86_64.tar.xz -C /usr/local/mysql --strip-components=1
sudo /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/var/lib/mysqlExpand-Archive -Path "C:\Downloads\mysql-8.4.3-winx64.zip" -DestinationPath "C:\Program Files\MySQL"
New-Item -ItemType Directory -Force -Path "C:\ProgramData\MySQL\Data"
& "C:\Program Files\MySQL\bin\mysqld.exe" --initialize-insecure --basedir="C:\Program Files\MySQL" --datadir="C:\ProgramData\MySQL\Data"

Phase 03: Master-Slave Replication Setup

STEP 3.1

Deploy Server Configuration Profiles (my.cnf / my.ini)

CRITICAL

Apply unique server_id, custom basedir/datadir paths, and calculated buffer pool sizes.

Master Config Profile 
sudo tee /etc/my.cnf > /dev/null << 'EOF'
[mysqld]
server-id                       = 1
basedir                         = /usr/local/mysql
datadir                         = /var/lib/mysql
bind-address                    = 0.0.0.0
log-bin                         = mysql-bin
binlog_format                   = ROW
gtid_mode                       = ON
enforce_gtid_consistency        = ON
innodb_buffer_pool_size         = 11G
EOF$cfg = @"
[mysqld]
server-id                       = 1
basedir                         = C:\Program Files\MySQL
datadir                         = C:\ProgramData\MySQL\Data
bind-address                    = 0.0.0.0
log-bin                         = mysql-bin
binlog_format                   = ROW
gtid_mode                       = ON
enforce_gtid_consistency        = ON
innodb_buffer_pool_size         = 11G
"@
Set-Content -Path "C:\ProgramData\MySQL\my.ini" -Value $cfg -Encoding ASCII
Slave Config Profile 
sudo tee /etc/my.cnf > /dev/null << 'EOF'
[mysqld]
server-id                       = 2
basedir                         = /usr/local/mysql
datadir                         = /var/lib/mysql
bind-address                    = 0.0.0.0
log-bin                         = mysql-bin
relay-log                       = slave-relay-bin
binlog_format                   = ROW
gtid_mode                       = ON
enforce_gtid_consistency        = ON
read_only                       = 1
innodb_buffer_pool_size         = 11G
EOF$cfg = @"
[mysqld]
server-id                       = 2
basedir                         = C:\Program Files\MySQL
datadir                         = C:\ProgramData\MySQL\Data
bind-address                    = 0.0.0.0
log-bin                         = mysql-bin
relay-log                       = slave-relay-bin
binlog_format                   = ROW
gtid_mode                       = ON
enforce_gtid_consistency        = ON
read_only                       = 1
innodb_buffer_pool_size         = 11G
"@
Set-Content -Path "C:\ProgramData\MySQL\my.ini" -Value $cfg -Encoding ASCII
STEP 3.2

Create Replication User & Start Replication

CRITICAL

Execute replication privileges on master and configure connection on slave.

Run on Master (db-master.internal) 
CREATE USER 'repl_user'@'192.168.10.51' IDENTIFIED WITH caching_sha2_password BY 'StrongReplPass#2026!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.10.51';
FLUSH PRIVILEGES;
Run on Slave (db-slave.internal) 
CHANGE MASTER TO
  MASTER_HOST='192.168.10.50',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='StrongReplPass#2026!',
  MASTER_AUTO_POSITION=1;

START SLAVE;
SHOW SLAVE STATUS\G
By

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)