Most people install MySQL with just two commands and call it a day—something like installing the package and starting the service. It works, the server comes up, and everything looks fine… until it hits real production load.

Instead of rushing into installation, we focus on everything that actually determines whether your MySQL 8.4 setup will survive in production. We walk through capacity planning, hardware validation, and—most critically—proper storage layout. We prepare the OS with the right limits and kernel tuning, eliminate conflicts like MariaDB, and ensure security, networking, and time synchronization are correctly configured before MySQL even touches the system.

We also cover the things most people ignore: clean environment checks, directory structure planning, monitoring readiness, and defining a backup strategy before installation. Finally, we validate everything through a production checklist so nothing is left to assumption.

Because in reality, MySQL performance is not decided at install time—it’s decided before install.

Around 80% of real-world performance issues come from bad disk layout and poor OS tuning, not MySQL itself. This guide helps you avoid those mistakes and build a stable, predictable, and production-ready MySQL 8.4 standalone system from the ground up.

Pre-Planning We Will Perform

1. Capacity & Growth Planning

Estimate current workload (TPS, connections, data size)
Plan for future growth (6–12 months)
Define RAM vs dataset ratio (buffer pool sizing readiness)

2. Hardware Validation

CPU cores vs workload type (OLTP vs analytics)
RAM sizing for InnoDB buffer pool
Disk type validation (SSD/NVMe preferred)

3. Storage Layout (Critical)

Separate mount points:
- /var/lib/mysql (data)
- /logs (binlog, slow log)
- /backup
Avoid root (/) filesystem usage
Choose proper filesystem (XFS recommended)

4. OS Configuration

File descriptor limits (ulimit)
Kernel tuning (sysctl)
Swappiness control
Transparent HugePages (THP) handling

5. Conflict Removal

Remove MariaDB completely
Ensure no old MySQL configs are interfering

6. Security & OS Hardening

Firewall rules
SELinux/AppArmor awareness
Dedicated MySQL user

7. Repository Readiness

Configure official MySQL 8.4 LTS repo
Avoid outdated distro packages

8. User & Permissions Planning

Proper ownership for MySQL directories
Principle of least privilege

9. Network Readiness

Port planning (3306 or custom)
Latency considerations
Host binding strategy

10. Time Synchronization (VERY IMPORTANT)

Configure NTP / Chrony
Validate system clock consistency

🧹 11. Clean Environment Check

No leftover configs
No conflicting services
Clean package state

12. Directory Preparation

Pre-create:
- Data directory
- Log directory
- Temp directory
Assign correct permissions

13. Monitoring Readiness

Setup tools like:
- Prometheus / exporters
- Basic OS monitoring (CPU, disk, IO)

14. Backup Strategy (Before Install!)

Logical vs Physical backups
Define retention policy
Plan restore testing

15. Risk Checklist (Most Ignored)

What happens if disk fills?
What if MySQL crashes?
Do we have alerting?
Do we have rollback steps?

1. Capacity & Planning

Before installing MySQL 8.4, we define a clear baseline for our system capacity to avoid guesswork later. In this setup, we are working with a server that has 8 GB RAM, 4 CPU cores, and dedicated storage allocation—including 100 GB for the MySQL data directory, 10 GB reserved for logs (slow query logs, error logs, binlogs if needed), and an additional 10 GB for scripts, tools, and operational files.

This separation is intentional and critical for production systems. By isolating data and logs, we prevent log growth from impacting database performance or causing disk exhaustion on the main data volume. With 8 GB RAM, we can later allocate a significant portion (60% – 80%) to the InnoDB buffer pool, ensuring efficient memory utilization for caching data. The 4-core CPU provides sufficient parallel processing for moderate OLTP workloads, but proper tuning will still be required to handle concurrency efficiently.

This step ensures that our MySQL deployment is not just installed, but planned with resource boundaries, growth considerations, and operational safety in mind, forming the foundation for a stable and scalable production environment.

OS & Hardware Readiness

Before installing MySQL 8.4, we validate that the system meets a minimum production baseline and is structured correctly for database workloads—not general-purpose usage.

For this setup, we are using:

CPU: 4+ cores
RAM: 8 GB
Disk: SSD (non-negotiable for production databases)

Basic validation is done using:

nproc     # CPU cores
free -h   # Memory
lsblk     # Disk layout
df -h     # Mounted filesystems

[centos@db1] ~ ➜ nproc
4
[centos@db1] ~ ➜ free -h
               total        used        free      shared  buff/cache   available
Mem:           7.5Gi       987Mi       6.1Gi       5.0Mi       672Mi       6.5Gi
Swap:          5.0Gi       631Mi       4.4Gi
[centos@db1] ~ ➜ lsblk
NAME        MAJ:MIN RM  SIZE RO TYPE MOUNTPOINTS
sda           8:0    0  100G  0 disk
sdb           8:16   0   50G  0 disk
├─sdb1        8:17   0  600M  0 part /boot/efi
├─sdb2        8:18   0    1G  0 part /boot
└─sdb3        8:19   0 48.4G  0 part
  ├─cs-root 253:0    0 43.4G  0 lvm  /
  └─cs-swap 253:1    0    5G  0 lvm  [SWAP]
sdc           8:32   0   10G  0 disk
sdd           8:48   0   10G  0 disk
sr0          11:0    1 10.5G  0 rom
[centos@db1] ~ ➜ df -h
Filesystem           Size  Used Avail Use% Mounted on
devtmpfs             3.8G     0  3.8G   0% /dev
tmpfs                3.8G     0  3.8G   0% /dev/shm
tmpfs                1.5G  9.8M  1.5G   1% /run
efivarfs             256K   33K  219K  13% /sys/firmware/efi/efivars
/dev/mapper/cs-root   44G  7.2G   37G  17% /
/dev/sdb2            960M  575M  386M  60% /boot
/dev/sdb1            599M  7.3M  592M   2% /boot/efi
tmpfs                765M  108K  765M   1% /run/user/1000
tmpfs                765M   52K  765M   1% /run/user/42
[centos@db1] ~ ➜

Storage Layout

For production MySQL, disk design matters more than CPU. A bad layout will cause performance issues even on powerful hardware.

✅ Filesystem Choice

We use:

XFS (recommended) → Better for large files, parallel I/O, and stability under load

Mount options:

noatime → Prevents disk writes on file access (reduces unnecessary I/O)
nodiratime → Same optimization for directories

👉 These reduce overhead and improve disk efficiency.

📂 Planned Disk Layout

We intentionally separate workloads at the filesystem level:

1. Data Directory (Critical)

Mount: /var/lib/mysql
Size: 100 GB
Purpose: Stores all database files (InnoDB data, indexes)

Why important?

Data is the core workload → needs maximum I/O performance
Prevents logs or other files from consuming database space
Easier to scale (can move/expand independently)

2. Logs Directory

Mount: /mysql-logs (or /logs/mysql)
Size: 10 GB
Includes:
- Slow query log
- General log (if enabled)
- Error log
- Binary logs (optional)

Why important?

Logs are write-heavy and bursty
Prevents log spikes from filling data disk
Helps in troubleshooting without impacting DB performance

3. Scripts / Tools Directory

Mount: /mysql-tools (or similar)
Size: ~1 GB (you can extend to 10 GB if needed)
Includes:
- Backup scripts
- Maintenance jobs
- Admin tools

Why important?

Keeps operational files separate from DB data
Avoids accidental clutter in critical directories
Easier management and automation

⚙️ Why This Planning Matters

Isolation = Stability
→ Logs won’t crash your database by filling disk
Performance Optimization
→ Data disk handles reads/writes efficiently without interference
Operational Safety
→ Easier backups, monitoring, and troubleshooting
Scalability
→ You can upgrade storage independently (data vs logs)

From our system check:

/dev/sda → 100 GB → Used for MySQL Data
/dev/sdc → 10 GB → Used for Logs
/dev/sdd → 10 GB → Used for Tools/Scripts

👉 Root (/) is already on LVM and left untouched to keep OS stable.

🧱 Step 1: Create Physical Volumes (PV)

pvcreate /dev/sda
pvcreate /dev/sdc
pvcreate /dev/sdd

Why?
These commands convert raw disks into LVM Physical Volumes, making them manageable under LVM. Without this, we cannot use LVM features like resizing or snapshots.

📦 Step 2: Create Volume Groups (VG)

vgcreate mysql_data_vg /dev/sda
vgcreate mysql_logs_vg /dev/sdc
vgcreate mysql_tools_vg /dev/sdd

Why?
A Volume Group is a pool of storage.
We created separate VGs to isolate workloads:

mysql_data_vg → critical database data
mysql_logs_vg → write-heavy logs
mysql_tools_vg → scripts & utilities

👉 This prevents logs or tools from consuming database space.

🔧 Step 3: Create Logical Volumes (LV)

lvcreate -l 100%FREE -n mysql_data_lv mysql_data_vg
lvcreate -l 100%FREE -n mysql_logs_lv mysql_logs_vg
lvcreate -l 100%FREE -n mysql_tools_lv mysql_tools_vg

Why?
Logical Volumes are the usable storage units (like virtual disks).

We used 100%FREE to allocate full disk space
LVM allows future extension without downtime

🧪 Step 4: Format with XFS

mkfs.xfs /dev/mysql_data_vg/mysql_data_lv
mkfs.xfs /dev/mysql_logs_vg/mysql_logs_lv
mkfs.xfs /dev/mysql_tools_vg/mysql_tools_lv

Why XFS?

Optimized for large files (InnoDB tables)
Better parallel I/O handling
Industry standard for MySQL production

📂 Step 5: Create Mount Points

mkdir -p /var/lib/mysql
mkdir -p /mysql-logs
mkdir -p /mysql-tools

Why?

These directories act as attachment points for disks
MySQL uses /var/lib/mysql as default data directory

🔗 Step 6: Mount the Volumes (Testing)

mount /dev/mysql_data_vg/mysql_data_lv /var/lib/mysql
mount /dev/mysql_logs_vg/mysql_logs_lv /mysql-logs
mount /dev/mysql_tools_vg/mysql_tools_lv /mysql-tools

Why?

Temporarily attaches disks
Allows validation before making permanent changes

⚙️ Step 7: Permanent Mount Configuration

Edit /etc/fstab:

vi /etc/fstab

Add:

/dev/mysql_data_vg/mysql_data_lv   /var/lib/mysql   xfs  defaults,noatime,nodiratime  0 0
/dev/mysql_logs_vg/mysql_logs_lv   /mysql-logs      xfs  defaults,noatime,nodiratime  0 0
/dev/mysql_tools_vg/mysql_tools_lv /mysql-tools     xfs  defaults,noatime,nodiratime  0 0

Apply:

mount -a

Why?

Ensures disks mount automatically after reboot
noatime,nodiratime reduces unnecessary disk writes → better performance

🔐 Step 8: Set Ownership

chown -R mysql:mysql /var/lib/mysql
chown -R mysql:mysql /mysql-logs

Why?

MySQL runs under mysql user
Incorrect permissions = MySQL will fail to start

🧠 Final Architecture

Component	Mount Point	Purpose
Data Disk (100GB)	`/var/lib/mysql`	Database files
Logs Disk (10GB)	`/mysql-logs`	Slow, error, binlogs
Tools Disk (10GB)	`/mysql-tools`	Scripts, backups

OS Configuration – Limits & File Descriptors (Production Setup)

Before installing MySQL 8.4, we must ensure the OS is tuned to handle high concurrency and file usage. One of the most critical parameters is the open file limit (ulimit -n), because MySQL uses file descriptors for tables, connections, logs, and temporary files.

🔍 Why Open File Limit is Important

Each table, connection, and log file consumes file descriptors
Low limits →
- “Too many open files” errors
- Connection failures
- Random crashes under load

👉 For production, 65535 is a safe baseline

✅ Step 1: Verify Current Limit

ulimit -n

👉 Expected:

✔️ In your case, it’s already set correctly after relogin — good.

🔍 Step 2: Verify PAM Configuration

cat /etc/pam.d/system-auth

Check for:

session     required     pam_limits.so

Why?
This ensures that /etc/security/limits.conf is actually applied when users log in.

⚠️ Important Note (Do NOT Modify This File)

You saw this:

# Generated by authselect
# Do not modify this file manually.

👉 That means:

File is managed by authselect
Any manual changes will be overwritten
Incorrect edits can break login/authentication

✔️ So we never edit /etc/pam.d/system-auth

🛠️ Step 3: Configure Limits Properly

Edit:

vi /etc/security/limits.conf

Add:

mysql soft nofile 65535
mysql hard nofile 65535

(Optional but recommended for admin user too):

* soft nofile 65535
* hard nofile 65535

🧠 Why This Matters

soft → default usable limit
hard → maximum allowed limit
MySQL runs as mysql user → must explicitly set for it

🔄 Step 4: Apply Changes

Logout and login again OR
Restart session

Then verify:

ulimit -n

🔍 Step 5: Post-Install Verification (VERY IMPORTANT)

After MySQL is installed and running:

1. Check MySQL Process Limit

pidof mysqld
cat /proc/<PID>/limits | grep "Max open files"

👉 Expected:

Max open files            65535

2. Check MySQL Internal Value

mysql -u root -p -e "SHOW VARIABLES LIKE 'open_files_limit';"

👉 This confirms MySQL is actually using the OS limit.

Setting ulimit only in the shell is meaningless for production—what actually matters is making it persistent through PAM. Many MySQL setups fail not because limits weren’t configured, but because they weren’t applied correctly at the OS level. This leads to situations where MySQL silently runs with lower limits than expected, eventually causing errors under load. In our setup, we verified that pam_limits.so is active, ensuring that the configured limits are consistently enforced.

✅ What We Achieved

We have successfully configured a high file descriptor limit (65535), verified that PAM is applying these limits correctly, and ensured the system is ready to handle high connections and table usage. This creates a solid OS-level foundation for MySQL to operate efficiently under production workloads.

🔥 Key Takeaway

Most MySQL performance issues don’t originate from MySQL itself—they start at the OS level. Properly configured limits prevent hidden bottlenecks and allow the database to scale reliably. With this in place, the next critical step is kernel tuning (sysctl), swap optimization, and disabling Transparent HugePages (THP), where the real performance gains begin.

THP & System Tuning

During OS tuning on CentOS Stream 9, we first validated the system-wide file descriptor capacity using:

cat /proc/sys/fs/file-max

The value returned was extremely high, confirming that the kernel is not a limiting factor for file descriptors at the global level. This complements our earlier ulimit configuration and ensures MySQL won’t hit OS-level file limits under load.

🔹 Transparent HugePages (THP)

Next, we checked the status of Transparent HugePages:

cat /sys/kernel/mm/transparent_hugepage/enabled

Output:

[always] madvise never

This indicates THP is enabled by default (always). However, for MySQL (especially InnoDB), THP is not recommended because it can introduce latency spikes and inefficient memory allocation.

To fix this, we disabled THP at runtime:

echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

Verification:

cat /sys/kernel/mm/transparent_hugepage/enabled

Output:

always madvise [never]

✔️ THP is now disabled, ensuring predictable memory behavior and stable performance under load.

🔹 System Tuning Profile (tuned)

By default, the system was using a generic profile (often virtual-guest), which is not optimized for database workloads. We switched to a more suitable profile:

tuned-adm profile throughput-performance

Initially, the TuneD service was not active, so it was automatically started. Verification:

tuned-adm active

Output:

Current active profile: throughput-performance

Why this profile?

Optimized CPU scheduling
Better disk I/O throughput
Suitable for high-performance database workloads

🔹 Custom Tuning (Cleanup Decision)

A custom TuneD configuration was briefly attempted under:

/etc/tuned/throughput-performance/

However, it was removed to keep the system:

Simple
Maintainable
Aligned with standard production practices

Instead, we rely on:

Official throughput-performance profile
Explicit THP control

✅ What We Achieved

Verified kernel-level file descriptor capacity
Disabled THP for MySQL-safe memory handling
Applied a database-friendly system tuning profile
Avoided unnecessary custom complexity

🔥 Key Takeaway

OS-level tuning has a direct impact on MySQL performance. Small changes like disabling THP and selecting the right TuneD profile can significantly improve stability and consistency, especially under production workloads.

Remove Conflicts (VERY COMMON ISSUE)

Before installing MySQL Enterprise:

dnf remove -y mariadb*

👉 Because
MariaDB conflicts with MySQL

Verify Removal

rpm -qa | grep -i maria

👉 Expected: No output

🧠 Real DBA Insight

Many installation failures or strange runtime issues happen because MariaDB was not fully removed. Even leftover libraries can interfere with MySQL 8.4.

Repository Readiness

/var/lib/mysql → Data (100G) ✔️
/mysql-logs → Logs (10G) ✔️
/mysql-tools → Tools / binaries / backups (10G) ✔️ ← we use this now

🎯 Goal for `/mysql-tools`

Use it for:

MySQL RPM repo (offline repo)
Backup scripts
Admin tools
Future upgrades

🔥 STEP 1: Move Repository to `/mysql-tools`

Right now RPMs are in home directory → not correct for production.

mkdir -p /mysql-tools/repo/mysql-8.4
mv ~/mysql-commercial-*.rpm /mysql-tools/repo/mysql-8.4/
cd /mysql-tools/repo/mysql-8.4/

🔥 STEP 2: Clean Unnecessary RPMs (IMPORTANT)

Production does NOT need debug/test packages:

rm -f *debuginfo*.rpm
rm -f *debug*.rpm
rm -f *test*.rpm

👉 Keeps repo lean and avoids confusion

🔥 STEP 3: Create Local Repo

dnf install createrepo -y
createrepo .

🔥 STEP 4: Configure Repo File

vi /etc/yum.repos.d/mysql-local.repo

Add:

[mysql-local]
name=MySQL 8.4 Local Repo
baseurl=file:///mysql-tools/repo/mysql-8.4
enabled=1
gpgcheck=0

🔥 STEP 5: Reload & Verify

dnf clean all
dnf makecache
dnf repolist

🔥 STEP 6: Validate Installation Source

dnf list mysql-commercial-server

✔️ If visible → repo ready

Security & OS Settings (Production Hardening)

Before installing MySQL 8.4, we ensure the operating system is properly secured and configured. A database server should expose only what is necessary and restrict everything else. Security at this stage prevents misconfigurations that are difficult to fix later.

🔹 Firewall Configuration

We first reviewed the currently open ports:

firewall-cmd --list-ports

Example output:

80/tcp 443/tcp 3306/tcp 5901/tcp 514/udp

Since this is a database server, HTTP (80) is not required and was removed:

firewall-cmd --permanent --remove-port=80/tcp
firewall-cmd --reload

For MySQL (3306/tcp), we allow access because the server is running inside a private network. This is acceptable as long as the database is not publicly exposed.

👉 Best practice:

Public server → ❌ Do NOT open 3306
Private network → ✅ Allowed with controlled access

🔹 SSH Security (Very Important)

SSH is the primary access method to the server, so it must be secured properly.

1. Disable Root Login

Edit:

vi /etc/ssh/sshd_config

Set:

PermitRootLogin no

Restart SSH:

systemctl restart sshd

Why?

Prevents direct root access
Forces controlled privilege escalation via sudo

2. Use Key-Based Authentication (Recommended)

Instead of passwords:

ssh-copy-id user@server-ip

Then disable password login:

PasswordAuthentication no

Why?

Eliminates brute-force password attacks
More secure and standard in production

3. Change Default SSH Port (Optional but Recommended)

Port 2222

Why?

Reduces automated attack attempts on port 22

🔹 SELinux Awareness

Check status:

getenforce

Enforcing → ✅ Secure
Permissive → ⚠️
Disabled → ❌

👉 In production, keep SELinux enabled and configure policies instead of disabling it blindly.

🔹 Disable Unnecessary Services

List enabled services:

systemctl list-unit-files --type=service | grep enabled

Disable anything not required (example):

systemctl disable postfix
systemctl stop postfix

Why?

Reduces attack surface
Frees system resources

🔹 Directory Permissions

We ensure restricted access to critical directories:

chmod 750 /var/lib/mysql
chmod 750 /mysql-logs

Why?

Prevents unauthorized access
Protects sensitive database files

🔹 Hostname & Network Validation

hostnamectl

Optional:

ping -c 2 $(hostname)

Why?

Prevents DNS-related connection delays
Ensures proper system identity

🔹 Disable Reverse DNS (MySQL Level Later)

skip-name-resolve

Why?

Avoids DNS lookup delays during authentication
Improves connection performance

🧠 Real DBA Insight

Opening unnecessary ports = security risk
Weak SSH configuration = easy entry point
Running extra services = hidden vulnerabilities

👉 Security is not a single setting—it’s a layered approach across OS, network, and access control.

✅ What We Achieved

Removed unnecessary exposed services (like HTTP)
Allowed MySQL access in a controlled private network
Secured SSH access using best practices
Reduced system attack surface
Prepared a hardened OS for MySQL deployment

🔥 Key Takeaway

A database server should be minimal, restricted, and purpose-built.
The more services and access points you expose, the higher the risk.

User & Permissions (Enterprise Setup – Local Repo Context)

With the MySQL 8.4 Enterprise package confirmed from our local repository, the next step is to ensure that user creation and directory permissions are correctly handled before first startup. This becomes even more important in enterprise setups where we use custom disk mounts and strict OS controls.

🔹 MySQL User Creation (Automatic)

When installing the package:

dnf install -y mysql-commercial-server

✔️ The mysql system user and group are automatically created

❗ However, it only manages the database process—it should not be used for operational tasks like backups or scripts

🔹 Ownership for Data & Logs (Critical)

Since we are using custom mounted volumes:

/var/lib/mysql   → 100GB (data)
/mysql-logs      → 10GB (logs)
/mysql-tools     → 10GB (tools/scripts)

We must explicitly assign ownership:

chown -R mysql:mysql /var/lib/mysql
chown -R mysql:mysql /mysql-logs

Set permissions:

chmod 750 /var/lib/mysql
chmod 750 /mysql-logs

Why?

Ensures MySQL has proper read/write access
Prevents unauthorized users from accessing sensitive data
Avoids startup failures due to permission issues

🔹 Tools & Scripts Directory (Separation of Duties)

The /mysql-tools directory is reserved for:

Backup scripts
Maintenance jobs
Admin utilities

This directory must NOT be owned by MySQL.

🔹 Create DBA Group (CentOS Best Practice)

groupadd dba
usermod -aG dba centos

🔹 Set Ownership & Permissions

chown -R root:dba /mysql-tools
chmod 750 /mysql-tools

What this ensures:

root → full control
dba group → read & execute
Others → no access

🔍 Verify

ls -ld /var/lib/mysql /mysql-logs /mysql-tools

Expected:

drwxr-x--- mysql mysql ...
drwxr-x--- mysql mysql ...
drwxr-x--- root  dba   ...

🧠 Real DBA Insight

MySQL (mysql user) should only handle data and logs
Backup scripts should run via:
- root
- or a dedicated admin user

👉 Never mix:

Database engine
Operational scripts
Administrative access

✅ What We Achieved

Ensured correct ownership for MySQL directories
Separated operational tools from database engine
Applied least-privilege access model
Built a secure and scalable permission structure

🔥 Key Takeaway

A production database is not just about performance—it’s about controlled access and responsibility separation.
By keeping MySQL, admin tools, and users properly isolated, we reduce risk and improve maintainability.

Network Readiness (Before MySQL Installation)

Before installing MySQL 8.4, we validate that the system’s network configuration is stable and predictable. Databases rely heavily on consistent networking for connections, replication, and application access—so even small misconfigurations can lead to latency or connectivity issues.

🔹 1. Verify IP Address

ip a

What to check:

Correct network interface (e.g., eth0, ens33)
IP address is properly assigned

✔ Ensure the server has a static IP, not a dynamic (DHCP) one

Why?

Prevents IP changes that can break application connections
Required for production stability and firewall rules

🔹 2. Verify Hostname

hostname

Also check full details:

hostnamectl

✔ Ensure hostname is properly set (e.g., db1, mysql-prod)

Why?

Used in logs, monitoring, and replication setups
Avoids confusion in multi-server environments

🔹 3. Verify DNS Resolution

ping -c 2 google.com

Optional internal check:

ping -c 2 $(hostname)

Why?

Confirms outbound DNS resolution
Prevents delays caused by reverse DNS lookups
Ensures external connectivity (for updates, monitoring, etc.)

🔹 4. Validate Local Host Mapping

Check:

cat /etc/hosts

Ensure entry like:

127.0.0.1   localhost
<your-ip>   db1

Why?

Prevents hostname resolution delays
Important for MySQL connections and logging

🧠 Real DBA Insight

Dynamic IP in production = disaster waiting to happen
Broken DNS = slow connections and timeouts
Wrong hostname = confusion in clusters and monitoring

👉 Networking issues often look like “database problems” but are actually OS-level misconfigurations.

✅ What We Achieved

Verified static IP configuration
Ensured proper hostname setup
Confirmed DNS and network resolution
Prepared system for stable MySQL connectivity

🔥 Key Takeaway

A database server must have a stable identity on the network.
If IP, hostname, or DNS is inconsistent, your database will behave unpredictably—no matter how well it is configured.

Time Synchronization (VERY IMPORTANT)

Before installing MySQL 8.4, we verified that the system time is correctly synchronized. In production environments, time consistency is critical for replication, logging, and backup accuracy.

🔍 Verification

timedatectl

Output:

Local time: Fri 2026-04-17 21:06:01 IST
Universal time: Fri 2026-04-17 15:36:01 UTC
Time zone: Asia/Kolkata (IST, +0530)
System clock synchronized: yes
NTP service: active

✅ What This Confirms

✔ Correct timezone configured (Asia/Kolkata)
✔ System clock is synchronized
✔ NTP service (chrony) is active
✔ No manual time drift

👉 This means no additional configuration is required.

🧠 Why This Matters for MySQL

Replication:
Time mismatch can break consistency across nodes
Backups:
Incorrect timestamps make recovery unreliable
Logs & Debugging:
Events must follow a consistent timeline
Transactions:
Time-based queries depend on accurate system time

🧠 Real DBA Insight

Most guides focus on configuring NTP, but in real environments:

👉 The important step is verification, not assumption

Even if NTP is installed:

It might not be active
It might not be syncing
Time drift might still exist

✔ In our case, everything is properly aligned—this is production-ready.

🔥 Key Takeaway

Time synchronization is a silent dependency.
If it’s wrong, everything looks fine—until things start breaking in subtle ways.

👉 Always verify:

NTP is active
Clock is synchronized
Timezone is correct

Directory Preparation (Before MySQL Installation)

Before starting MySQL 8.4, we ensure that all required directories are properly created, structured, and ready for use. Since we are using a custom disk layout (LVM + XFS), this step is critical to avoid initialization and runtime issues.

🔍 Our Directory Layout

Based on our setup:

/var/lib/mysql   → 100GB (data)
/mysql-logs      → 10GB (logs)
/mysql-tools     → 10GB (scripts & tools)

These directories are already mounted on separate logical volumes, but we still need to ensure they are clean and ready.

🔹 1. Ensure Directories Exist

mkdir -p /var/lib/mysql
mkdir -p /mysql-logs
mkdir -p /mysql-tools

Why?

Guarantees paths exist before MySQL installation
Prevents installer fallback to default locations

🔹 2. Clean Existing Content (VERY IMPORTANT)

rm -rf /var/lib/mysql/*
rm -rf /mysql-logs/*

⚠️ Only run this if:

Fresh setup
No existing data

Why?

Removes leftover files from previous installs

Create Subdirectories for Logs

mkdir -p /mysql-logs/slow
mkdir -p /mysql-logs/error
mkdir -p /mysql-logs/binlog

Why?

Keeps logs organized
Easier monitoring and troubleshooting

🧠 Real DBA Insight

MySQL installer does not prepare directories for custom layouts
Leftover files = silent failures later
Clean directories = predictable initialization

👉 This step ensures MySQL starts with a known clean state

✅ What We Achieved

Prepared all required directories
Ensured clean and empty environment
Applied correct ownership and permissions
Structured logs for better operations

🔥 Key Takeaway

Directory preparation is not just about creating folders—it’s about ensuring a clean, controlled, and predictable starting point for MySQL.

Monitoring Tools Ready

Before installing MySQL, monitoring must already be in place. A production database without monitoring is essentially a blind system—you won’t know there’s a problem until users complain.

🔹 What to Prepare

At minimum, ensure visibility into:

CPU usage
Memory usage
Disk I/O and latency
Disk space utilization
Network activity

Basic tools:

top
iostat -x 1
vmstat 1
df -h

(Optional advanced setup):

Prometheus + Node Exporter
MySQL Exporter (after install)
Grafana dashboards

🧠 Why Before Installation?

Establish baseline performance
Detect issues during installation itself
Avoid “we don’t know what changed” scenarios

🧠 Real DBA Insight

Most teams install monitoring after issues start—which is already too late.

👉 Good DBAs monitor from day 0, not day 30.

💾 13. Backup Strategy (Before Install!)

Before even starting MySQL, you must define how you will recover from failure.

🔹 Decide Backup Type

Logical backup → mysqldump (simple, slower, portable)
Physical backup → MySQL Enterprise Backup / XtraBackup (faster, production-grade)

🔹 Define Strategy

Backup frequency (daily, hourly, etc.)
Retention policy (7 days, 30 days, etc.)
Storage location (local + remote)

🔹 Prepare Backup Location

mkdir -p /backup/mysql
chmod 750 /backup/mysql
chown root:dba /backup/mysql

🔹 Create Backup User (after MySQL install)

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'strong_password';
GRANT SELECT, RELOAD, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';

🧠 Why Before Install?

Backup is not optional—it’s part of the design
Without restore capability → system is not production-ready

🧠 Real DBA Insight

Backups are useless if:

Not tested
Not automated
Not restorable

👉 Always think: “Can I restore right now?”

⚠️ 14. Risk Checklist (Most People Miss)

Before proceeding with installation, we validate common failure scenarios that are often ignored.

🔹 Storage Risks

What happens if /var/lib/mysql gets full?
Is monitoring in place for disk usage?

🔹 Performance Risks

Is disk isolated (data vs logs)? ✔️
Is OS tuned? ✔️

🔹 Security Risks

Are unnecessary ports closed?
Is SSH hardened?

🔹 Operational Risks

Do we have backup strategy? ✔️
Are logs separated? ✔️
Are permissions correct? ✔️

🔹 Failure Handling

What happens if MySQL fails to start?
Do we have logs accessible?
Is there a rollback plan?

🧠 Real DBA Insight

Most outages are not due to MySQL bugs—they happen because:

Disk fills up
No monitoring alerts
No backup or recovery plan

👉 Production failures are usually predictable and preventable

✅ What We Achieved

Monitoring readiness before installation
Backup strategy defined and prepared
Risks identified and mitigated
System prepared beyond just installation

🔥 Final Takeaway

Installing MySQL is easy.
Running MySQL in production is about planning, visibility, and recovery.

👉 At this point, the system is fully prepared for a production-grade MySQL 8.4 installation.

🚀 FINAL PRODUCTION CHECKLIST

Before install, ensure:

✔ CPU & RAM validated
✔ Separate disk mounted (VERY IMPORTANT)
✔ OS tuned (limits + sysctl)
✔ Swap behavior controlled
✔ MariaDB removed
✔ Repo ready
✔ Network + time sync OK
✔ Directories prepared
✔ Monitoring tools installed
✔ Backup strategy defined

You’re now not just installing MySQL—you’re deploying it like a real DBA in production 💯

Pre-Planning We Will Perform

1. Capacity & Growth Planning

2. Hardware Validation

3. Storage Layout (Critical)

4. OS Configuration

5. Conflict Removal

6. Security & OS Hardening

7. Repository Readiness

8. User & Permissions Planning

9. Network Readiness

10. Time Synchronization (VERY IMPORTANT)

🧹 11. Clean Environment Check

12. Directory Preparation

13. Monitoring Readiness

14. Backup Strategy (Before Install!)

15. Risk Checklist (Most Ignored)

1. Capacity & Planning

OS & Hardware Readiness

Storage Layout

✅ Filesystem Choice

📂 Planned Disk Layout

1. Data Directory (Critical)

2. Logs Directory

3. Scripts / Tools Directory

⚙️ Why This Planning Matters

🧱 Step 1: Create Physical Volumes (PV)

📦 Step 2: Create Volume Groups (VG)

🔧 Step 3: Create Logical Volumes (LV)

🧪 Step 4: Format with XFS

📂 Step 5: Create Mount Points

🔗 Step 6: Mount the Volumes (Testing)

⚙️ Step 7: Permanent Mount Configuration

🔐 Step 8: Set Ownership

🧠 Final Architecture

OS Configuration – Limits & File Descriptors (Production Setup)

🔍 Why Open File Limit is Important

✅ Step 1: Verify Current Limit

🔍 Step 2: Verify PAM Configuration

⚠️ Important Note (Do NOT Modify This File)

🛠️ Step 3: Configure Limits Properly

🧠 Why This Matters

🔄 Step 4: Apply Changes

🔍 Step 5: Post-Install Verification (VERY IMPORTANT)

1. Check MySQL Process Limit

2. Check MySQL Internal Value

✅ What We Achieved

🔥 Key Takeaway

THP & System Tuning

🔹 Transparent HugePages (THP)

🔹 System Tuning Profile (tuned)

🔹 Custom Tuning (Cleanup Decision)

✅ What We Achieved

🔥 Key Takeaway

Remove Conflicts (VERY COMMON ISSUE)

Verify Removal

🧠 Real DBA Insight

🎯 Goal for /mysql-tools

🔥 STEP 1: Move Repository to /mysql-tools

🔥 STEP 2: Clean Unnecessary RPMs (IMPORTANT)

🔥 STEP 3: Create Local Repo

🔥 STEP 4: Configure Repo File

🔥 STEP 5: Reload & Verify

🔥 STEP 6: Validate Installation Source

Security & OS Settings (Production Hardening)

🔹 Firewall Configuration

🔹 SSH Security (Very Important)

1. Disable Root Login

2. Use Key-Based Authentication (Recommended)

3. Change Default SSH Port (Optional but Recommended)

🔹 SELinux Awareness

🔹 Disable Unnecessary Services

🔹 Directory Permissions

🔹 Hostname & Network Validation

🔹 Disable Reverse DNS (MySQL Level Later)

🧠 Real DBA Insight

✅ What We Achieved

🔥 Key Takeaway

User & Permissions (Enterprise Setup – Local Repo Context)

🔹 MySQL User Creation (Automatic)

🔹 Ownership for Data & Logs (Critical)

🎯 Goal for `/mysql-tools`

🔥 STEP 1: Move Repository to `/mysql-tools`