How to Configure MySQL on Linux Server – (Step-by-Step Guide 2026)

To configure MySQL on a Linux server, install MySQL 8, run security hardening, tune the my.cnf file for your RAM and workload, create least-privilege users, open the firewall for approved IPs, enable logs and backups, and monitor performance. This step-by-step 2026 guide covers Ubuntu/Debian, RHEL/AlmaLinux, and practical, production-ready settings.

Configuring MySQL on a Linux server can be straightforward when you follow a structured process. In this guide, you’ll learn how to install, secure, optimize, and maintain MySQL 8 on modern Linux distributions, with real-world settings that work for WordPress, SaaS apps, and high-traffic sites. We’ll keep it beginner-friendly while still technically accurate.

What You’ll Need (Prerequisites)

• A Linux server (Ubuntu 22.04/24.04, Debian 12, RHEL 8/9, AlmaLinux/Rocky 8/9)
• SSH access with sudo privileges
• MySQL 8.x (8.4 LTS is recommended for 2026)
• Firewall access to allow only trusted IPs to port 3306
• A plan for backups and basic monitoring

Install MySQL on Popular Linux Distros

Many Linux flavors provide MariaDB by default. If you specifically need Oracle MySQL 8, use the official repositories. Below are quick-start commands for common distributions.

Ubuntu/Debian (MySQL 8)

# Update and install MySQL server
sudo apt update
sudo apt install -y mysql-server

# Enable and start the service
sudo systemctl enable --now mysql

# Check status
systemctl status mysql

# Verify version
mysql --version

RHEL/AlmaLinux/Rocky (MySQL 8)

# Install MySQL Yum repo if needed (example uses Oracle MySQL repo)
sudo dnf install -y https://dev.mysql.com/get/mysql80-community-release-el9-1.noarch.rpm
sudo dnf module disable -y mysql
sudo dnf install -y mysql-community-server

# Enable and start
sudo systemctl enable --now mysqld

# Check status
systemctl status mysqld

# Verify version
mysql --version

Note: On RHEL-like systems, the service name is usually mysqld. On Debian-based systems, it’s mysql.

Initial Security Hardening

Run the built-in hardening script to remove unsafe defaults and set a strong root password.

sudo mysql_secure_installation

• Set a strong root password (at least 16 chars, mixed types)
• Remove anonymous users
• Disallow remote root login
• Remove test database
• Reload privilege tables

On Ubuntu/Debian, root may authenticate via auth_socket. If you prefer password auth, explicitly set it:

sudo mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'YourStrongPassword!'; FLUSH PRIVILEGES;"

Edit MySQL Configuration (my.cnf)

Modern MySQL stores settings in a few locations, but the main server section is [mysqld]. Common paths:

• Ubuntu/Debian: /etc/mysql/mysql.conf.d/mysqld.cnf (included from /etc/mysql/my.cnf)
• RHEL/AlmaLinux/Rocky: /etc/my.cnf

Core, Networking, and Charset Settings

Use these as safe, production-friendly defaults for MySQL 8 in 2026. Adjust sizes for your RAM.

[mysqld]
# General
user = mysql
pid-file = /run/mysqld/mysqld.pid
socket = /run/mysqld/mysqld.sock
datadir = /var/lib/mysql
skip_name_resolve = ON

# Networking
bind-address = 0.0.0.0        # Use 0.0.0.0 only if firewall restricts access
port = 3306

# Character set & collation (MySQL 8 defaults are already utf8mb4)
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci

# InnoDB (tune sizes to your RAM)
innodb_buffer_pool_size = 2G      # ~50-70% of RAM on dedicated DB server
innodb_buffer_pool_instances = 2
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1

# Connections & caches (start conservative, raise if needed)
max_connections = 200
table_open_cache = 4096
thread_cache_size = 100
tmp_table_size = 64M
max_heap_table_size = 64M

# SQL mode & compatibility
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

# Logging
log_error_verbosity = 3
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1

# Binary logging (enable for point-in-time recovery or replication)
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_expire_logs_seconds = 604800      # 7 days
binlog_format = ROW

# Optional: enforce TLS for remote clients (ensure clients support it)
# require_secure_transport = ON

After editing, restart MySQL:

sudo systemctl restart mysql    # Debian/Ubuntu
# or
sudo systemctl restart mysqld   # RHEL/AlmaLinux/Rocky

Create a Database and Least-Privilege User

Grant only the permissions your application needs, and restrict by host or subnet.

mysql -u root -p

-- Inside the MySQL shell:
CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

CREATE USER 'appuser'@'10.0.0.%' IDENTIFIED BY 'AnotherStrongPassword!';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER
ON appdb.* TO 'appuser'@'10.0.0.%';

FLUSH PRIVILEGES;

Open the Firewall (Safely)

UFW (Ubuntu/Debian)

# Allow only a trusted IP or subnet
sudo ufw allow from 203.0.113.10 to any port 3306 proto tcp
sudo ufw reload
sudo ufw status

firewalld (RHEL/AlmaLinux/Rocky)

# Allow a specific source to port 3306
sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="203.0.113.10" port protocol="tcp" port="3306" accept'
sudo firewall-cmd --reload
sudo firewall-cmd --list-all

SELinux enabled? If you change the default port, label it for MySQL:

sudo semanage port -a -t mysqld_port_t -p tcp 3307   # example for a custom port

Performance Tuning (2026 Best Practices)

• Use InnoDB as the default engine (MySQL 8 default).
• Allocate 50–70% of RAM to innodb_buffer_pool_size on dedicated DB hosts (30–40% on shared hosts).
• Place data and logs on fast SSD/NVMe; separate volume from OS if possible.
• Keep innodb_flush_log_at_trx_commit=1 for durability; use a UPS to protect from power loss.
• Avoid the removed MySQL 8 query cache; optimize indexes and queries instead.

Example Sizing

• 4 GB RAM: innodb_buffer_pool_size=2.5G, innodb_log_file_size=512M, max_connections=150–200
• 16 GB RAM: innodb_buffer_pool_size=10G, innodb_log_file_size=1G, max_connections=300–500

Watch metrics: buffer pool hit rate, threads_running, InnoDB I/O, temporary tables on disk, row lock waits. Adjust gradually and re-test.

Enable Slow Query Log and Optimize

The slow query log surfaces inefficient SQL so you can add indexes or refactor code. We set it earlier; confirm it’s active:

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

Summarize slow queries:

sudo mysqldumpslow -s t /var/log/mysql/mysql-slow.log | head -n 50
# Or use Percona Toolkit for deeper analysis:
# pt-query-digest /var/log/mysql/mysql-slow.log

Add missing indexes, avoid SELECT *, and paginate large result sets. For WordPress, ensure indexes on wp_postmeta(meta_key), wp_options(autoload), and wp_posts(post_type, post_status, post_date).

Backups and Restore (Point-in-Time Ready)

Start with logical dumps for small/medium databases; use physical hot backup for large datasets.

Logical Backups (mysqldump)

# Consistent dump for InnoDB
mysqldump --single-transaction --routines --events --triggers -u root -p appdb > appdb-$(date +%F).sql

# Restore
mysql -u root -p appdb < appdb-2026-01-01.sql

Binary Logs for PITR

# List binary logs
SHOW BINARY LOGS;

# After restoring a full dump, replay binlogs up to a point in time
mysqlbinlog --start-datetime="2026-05-10 10:00:00" --stop-datetime="2026-05-10 11:00:00" /var/log/mysql/mysql-bin.000123 | mysql -u root -p

For very large databases, consider Percona XtraBackup for hot, non-blocking physical backups.

Monitoring and Routine Maintenance

• Uptime and basic stats: mysqladmin status
• Health checks: SHOW ENGINE INNODB STATUS\G
• Table statistics: ANALYZE TABLE key tables monthly
• Enable performance_schema (default ON) for wait events and statement insights
• Track disk space (datadir and log volume) and rotate logs

Troubleshooting Common Issues

• Cannot connect: Verify bind-address, firewall rules, and that the user’s host matches (e.g., 'appuser'@'10.0.0.%').
• Access denied: Check SELECT host, user FROM mysql.user;, then SHOW GRANTS FOR 'user'@'host';.
• Port in use: Confirm 3306 availability with ss -ltnp | grep 3306.
• InnoDB crash recovery: Add innodb_force_recovery=1 (raise carefully) to my.cnf, restart, dump data, then remove.
• High CPU: Inspect SHOW PROCESSLIST; and slow log; add indexes or limit expensive queries.

Production Checklist (Step-by-Step Guide 2026)

• Install MySQL 8 and enable the service
• Run mysql_secure_installation and enforce strong passwords
• Set innodb_buffer_pool_size, logs, and caches based on RAM
• Enable slow query log and review weekly
• Create least-privilege users and restrict by host/subnet
• Open firewall only to trusted IPs; consider TLS
• Enable binary logs for PITR; schedule nightly backups
• Monitor performance_schema, disk space, and error logs
• Document changes and test restores regularly

When to Choose Managed Hosting

If you’d rather not spend weekends tuning buffers or chasing slow queries, a managed VPS or dedicated server can help. At YouStable, our hosting specialists provision optimized MySQL stacks, set sane my.cnf defaults for your workload, configure backups and monitoring, and provide guidance as your traffic grows.

FAQs: Configure MySQL on Linux Server

Final Word

With this step-by-step guide, you can confidently configure MySQL on a Linux server, tighten security, and tune for performance. Keep changes incremental, monitor consistently, and revisit settings as your workload evolves. If you need hands-on help, YouStable’s managed engineers are a chat away.