Introduction: MySQL in Production Environments

Setting up MySQL in a production environment is vastly different from running it on your local development machine. Production databases require robust security measures, reliable backup strategies, performance optimization, and careful access control to ensure data integrity and business continuity.

This comprehensive guide walks you through installing and configuring MySQL 8.x (or higher) on an Ubuntu 24 VPS with production-grade security, automated backup systems, and optional remote access configuration. Whether you're deploying a new application or migrating from an existing setup, following these best practices will help you build a secure and reliable database infrastructure.

What You'll Learn

Installing MySQL 8.x on Ubuntu 24.04 LTS
Implementing production-level security hardening
Configuring automated backup strategies
Setting up secure remote database access
Performance tuning for production workloads
Monitoring and maintenance best practices

Prerequisites

Before starting this guide, ensure you have:

Ubuntu 24.04 LTS VPS - Fresh installation recommended
Root or sudo access - Administrative privileges required
Minimum 2GB RAM - 4GB+ recommended for production
At least 20GB disk space - More for larger databases
Stable internet connection - For package downloads
Basic Linux command-line knowledge - Terminal comfort required

Server Preparation

First, update your system packages to ensure you have the latest security patches:

sudo apt update && sudo apt upgrade -y

Install essential utilities that we'll need throughout this guide:

sudo apt install -y wget curl vim ufw software-properties-common

Step 1: Installing MySQL 8.x on Ubuntu 24

Installing MySQL Server

Update the package index and install MySQL:

# Update package lists
sudo apt update

# Install MySQL Server
sudo apt install -y mysql-server

# Verify installation
mysql --version

Expected output:

mysql  Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)

Starting and Enabling MySQL Service

Ensure MySQL starts automatically on system boot:

# Start MySQL service
sudo systemctl start mysql

# Enable MySQL to start on boot
sudo systemctl enable mysql

# Check MySQL status
sudo systemctl status mysql

You should see output indicating that MySQL is active and running.

Step 2: Production-Level Security Configuration

Running MySQL Secure Installation

The first critical security step is running the mysql_secure_installation script, which helps you implement essential security measures:

sudo mysql_secure_installation

You'll be prompted with several security options:

VALIDATE PASSWORD COMPONENT - Choose YES for production
- Enforces strong password policies
- Recommended: Set to STRONG (Level 2)
Set root password - Create a strong, unique password
- Use at least 16 characters
- Include uppercase, lowercase, numbers, and symbols
- Store securely (use a password manager)
Remove anonymous users - Choose YES
- Anonymous users are a security risk
Disallow root login remotely - Choose YES
- Root should only connect from localhost
- We'll create separate users for remote access
Remove test database - Choose YES
- Test databases can be accessed by anyone
Reload privilege tables - Choose YES
- Apply all security changes immediately

Configuring MySQL for Production Security

Edit the MySQL configuration file to implement additional security measures:

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Add or modify the following settings in the [mysqld] section:

[mysqld]
# Basic Settings
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql

# Security Settings
# Bind to localhost only (change if you need remote access)
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1

# Disable LOCAL INFILE (prevents local file reading attacks)
local-infile = 0

# Disable symbolic links (security risk)
symbolic-links = 0

# Enable SSL/TLS connections
require_secure_transport = ON

# Set strict SQL mode for data integrity
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

# Logging for security auditing
log_error = /var/log/mysql/error.log
general_log = 0
general_log_file = /var/log/mysql/mysql.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2

# Connection limits (adjust based on your needs)
max_connections = 150
max_connect_errors = 10
max_allowed_packet = 64M

# Performance and Buffer Settings (adjust based on RAM)
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

# Character set (UTF-8 support)
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Save the file and restart MySQL to apply changes:

sudo systemctl restart mysql

Setting Up SSL/TLS for Encrypted Connections

MySQL 8.x automatically generates SSL certificates, but for production, you should use your own certificates:

# Create directory for SSL certificates
sudo mkdir -p /etc/mysql/ssl
cd /etc/mysql/ssl

# Generate CA key and certificate (valid for 10 years)
sudo openssl genrsa 2048 > ca-key.pem
sudo openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca-cert.pem

# Generate server certificate
sudo openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -out server-req.pem
sudo openssl rsa -in server-key.pem -out server-key.pem
sudo openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# Generate client certificate
sudo openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem -out client-req.pem
sudo openssl rsa -in client-key.pem -out client-key.pem
sudo openssl x509 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

# Set proper permissions
sudo chown mysql:mysql /etc/mysql/ssl/*
sudo chmod 600 /etc/mysql/ssl/*-key.pem
sudo chmod 644 /etc/mysql/ssl/*-cert.pem

Update MySQL configuration to use these certificates:

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Add these lines under [mysqld]:

# SSL/TLS Configuration
ssl-ca = /etc/mysql/ssl/ca-cert.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-key = /etc/mysql/ssl/server-key.pem

Restart MySQL:

sudo systemctl restart mysql

Creating Dedicated Database Users

Never use the root account for applications. Create dedicated users with minimal privileges following the principle of least privilege - granting only the permissions necessary for the user's specific role.

Understanding Database Privileges

MySQL offers various privilege levels. Here's what each common privilege allows:

SELECT - Read data from tables
INSERT - Add new records to tables
UPDATE - Modify existing records
DELETE - Remove records from tables
CREATE - Create new tables and databases
DROP - Delete tables and databases
ALTER - Modify table structure
INDEX - Create and drop indexes
REFERENCES - Create foreign key constraints (required for schema migrations)
LOCK TABLES - Lock tables for consistency
CREATE TEMPORARY TABLES - Create temporary tables

Application User Setup

For a typical web application using an ORM like Prisma, Sequelize, or TypeORM, you'll need these privileges for the application user:

# Login to MySQL
sudo mysql -u root -p

# Create application database
CREATE DATABASE myapp_production;

# Create application user with secure password
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'Strong_Password_123!@#';

# Grant privileges for normal application operations AND schema migrations
# These are the MINIMUM privileges needed for frameworks like Prisma/TypeORM
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, REFERENCES
ON myapp_production.* TO 'myapp_user'@'localhost';

# Apply privileges
FLUSH PRIVILEGES;

Why REFERENCES privilege? Modern ORMs like Prisma use foreign key constraints to maintain referential integrity. Without the REFERENCES privilege, migrations will fail with error: "REFERENCES command denied to user".

Read-Only User for Applications

If you have reporting tools or analytics services that only need to read data:

# Create read-only user
CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'ReadOnly_Pass_789!@#';

# Grant only SELECT privilege
GRANT SELECT ON myapp_production.* TO 'readonly_user'@'localhost';

FLUSH PRIVILEGES;

Backup User Setup

Create a dedicated user for automated backups with read-only access across all databases:

# Create backup user
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'Backup_Strong_Pass_456!@#';

# Grant privileges needed for mysqldump and backups
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';

FLUSH PRIVILEGES;

Migration User (Optional - For CI/CD)

If you run migrations separately from your application (e.g., in CI/CD pipelines), create a dedicated migration user:

# Create migration user
CREATE USER 'migration_user'@'localhost' IDENTIFIED BY 'Migration_Pass_321!@#';

# Grant privileges needed for schema changes
GRANT CREATE, DROP, ALTER, INDEX, REFERENCES ON myapp_production.* TO 'migration_user'@'localhost';

FLUSH PRIVILEGES;

Verify User Privileges

# View all users and their hosts
SELECT user, host FROM mysql.user;

# Check specific user privileges
SHOW GRANTS FOR 'myapp_user'@'localhost';

# Exit MySQL
EXIT;

Security Best Practices for User Management

Never use root for applications - Root has ALL privileges and poses a security risk if credentials are compromised
One user per application - Don't share database users across different applications
Separate users for different purposes - Use different users for app runtime, backups, migrations, and monitoring
Limit host access - Use 'localhost' when possible instead of '%' (any host)
Strong passwords required - Use password managers and rotate passwords regularly
Audit privileges quarterly - Review and remove unnecessary privileges
Remove unused users - Delete users that are no longer needed

Privileges to AVOID Granting to Application Users

These privileges should ONLY be granted to administrative users, never to application users:

GRANT OPTION - Allows user to grant privileges to others
SUPER - Full administrative access
CREATE USER - Can create new database users
RELOAD - Can flush logs and caches
SHUTDOWN - Can stop the MySQL server
PROCESS - Can view all running processes
FILE - Can read/write files on the server

Implementing Firewall Rules

Configure UFW (Uncomplicated Firewall) to restrict MySQL access. By default, only allow local connections:

# Enable UFW if not already enabled
sudo ufw enable

# Allow SSH (important - don't lock yourself out!)
sudo ufw allow OpenSSH

# MySQL should NOT be accessible from outside by default
# Only allow if you specifically need remote access (see later section)

# Check firewall status
sudo ufw status

Configuring AppArmor for MySQL

AppArmor provides mandatory access control. Ensure MySQL's AppArmor profile is active:

# Check AppArmor status
sudo aa-status | grep mysql

# If not enabled, load the profile
sudo aa-enforce /etc/apparmor.d/usr.sbin.mysqld

# Verify
sudo systemctl restart mysql

Regular Security Maintenance

Security is an ongoing process. Schedule these regular tasks:

Weekly: Review MySQL error logs for suspicious activity
Monthly: Update MySQL to latest security patches
Quarterly: Audit user accounts and remove unused ones
Quarterly: Review and update passwords
Annually: Renew SSL/TLS certificates

# Check for MySQL updates
sudo apt update
sudo apt list --upgradable | grep mysql

# Apply updates
sudo apt upgrade mysql-server

Troubleshooting Common Issues (FAQ)

Encountering issues during MySQL setup? Here are solutions to the most common problems:

❓ Cannot set root password with mysql_secure_installation - "Failed! Error: SET PASSWORD has no significance"

Problem:

MySQL 8.x on Ubuntu uses the auth_socket authentication plugin by default for the root user. This means it authenticates using your system user credentials instead of a password.

Solution 1: Set Root Password Manually (Recommended)

# Step 1: Login to MySQL (no password needed with sudo)
sudo mysql

# Step 2: Change authentication method and set password
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourStrongPassword123!@#';

# Step 3: Flush privileges
FLUSH PRIVILEGES;

# Step 4: Exit MySQL
EXIT;

# Step 5: Now run mysql_secure_installation
sudo mysql_secure_installation
# Enter the password you just set when prompted

Solution 2: Keep auth_socket (More Secure)

# Step 1: Create a separate admin user with password
sudo mysql

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'StrongPassword123!@#';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT;

# Step 2: Run mysql_secure_installation and skip root password
sudo mysql_secure_installation
# Press ENTER when asked about root password
# Answer YES to all other questions

Verify Authentication Method:

sudo mysql -e "SELECT user, host, plugin FROM mysql.user WHERE user='root';"

❓ MySQL service fails to start after configuration changes

Check error logs:

sudo tail -50 /var/log/mysql/error.log

Common causes and solutions:

Syntax error in configuration file:

# Test configuration syntax
sudo mysqld --validate-config

# If there's an error, review your changes in:
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Incorrect file permissions:

sudo chown -R mysql:mysql /var/lib/mysql
sudo chown -R mysql:mysql /var/log/mysql

Insufficient disk space:
```
df -h
# Free up space if needed
```

Restart MySQL:

sudo systemctl restart mysql
sudo systemctl status mysql

❓ SSL/TLS certificate generation fails or MySQL won't start

Check certificate file permissions:

ls -la /etc/mysql/ssl/
# Ensure ownership and permissions are correct
sudo chown mysql:mysql /etc/mysql/ssl/*
sudo chmod 600 /etc/mysql/ssl/*-key.pem
sudo chmod 644 /etc/mysql/ssl/*-cert.pem

Verify certificate paths in configuration:

sudo grep -E "ssl-ca|ssl-cert|ssl-key" /etc/mysql/mysql.conf.d/mysqld.cnf

Test SSL connection:

sudo mysql -e "SHOW VARIABLES LIKE '%ssl%';"
# ssl_ca, ssl_cert, ssl_key should show your certificate paths

Temporary workaround (disable SSL to troubleshoot):

# Comment out SSL lines in configuration
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
# Add # before ssl-ca, ssl-cert, ssl-key, require_secure_transport

sudo systemctl restart mysql

❓ "Access denied for user 'root'@'localhost'" error

If using auth_socket (default):

# Use sudo to login
sudo mysql

# Or specify socket authentication
mysql -u root

If using password authentication:

# Ensure you're using the -p flag
mysql -u root -p
# Then enter your password

# Reset root password if forgotten
sudo mysql
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPassword123!@#';
FLUSH PRIVILEGES;
EXIT;

Check authentication plugin:

sudo mysql -e "SELECT user, host, plugin FROM mysql.user WHERE user='root';"

❓ Firewall is blocking MySQL but I configured UFW correctly

Check UFW status and rules:

sudo ufw status verbose
sudo ufw status numbered

Verify MySQL is listening:

sudo netstat -tlnp | grep mysql
# or
sudo ss -tlnp | grep mysql

Check bind-address configuration:

sudo grep bind-address /etc/mysql/mysql.conf.d/mysqld.cnf
# Should show: bind-address = 127.0.0.1 (for local only)
# or bind-address = 0.0.0.0 (for remote access)

Allow MySQL through firewall (if needed):

# Allow from specific IP only
sudo ufw allow from 192.168.1.100 to any port 3306

# Or allow from subnet
sudo ufw allow from 192.168.1.0/24 to any port 3306

# Reload UFW
sudo ufw reload

❓ How do I reset MySQL completely if setup goes wrong?

⚠️ WARNING: This will delete all databases and data!

Complete MySQL reset:

# Step 1: Stop MySQL
sudo systemctl stop mysql

# Step 2: Backup any important data (if possible)
sudo cp -r /var/lib/mysql /var/lib/mysql_backup

# Step 3: Remove MySQL completely
sudo apt purge mysql-server mysql-client mysql-common mysql-server-core-* mysql-client-core-*
sudo rm -rf /etc/mysql /var/lib/mysql
sudo apt autoremove
sudo apt autoclean

# Step 4: Reinstall MySQL
sudo apt update
sudo apt install mysql-server

# Step 5: Start fresh setup
sudo systemctl start mysql
sudo mysql_secure_installation

❓ Which authentication method should I use: auth_socket or password?

Use auth_socket (default) if:

✅ You only access MySQL from the local server
✅ You want maximum security for production
✅ Your applications run on the same server
✅ You prefer sudo-based authentication

Use mysql_native_password if:

✅ You need remote MySQL access
✅ You use GUI tools like MySQL Workbench
✅ You're migrating from older MySQL versions
✅ Your workflow requires password authentication

Switch authentication method:

# To password-based:
sudo mysql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourPassword';
FLUSH PRIVILEGES;

# To socket-based:
sudo mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;
FLUSH PRIVILEGES;

Still Having Issues?

If you're still experiencing problems after trying these solutions:

Check MySQL version compatibility:

mysql --version
lsb_release -a  # Check Ubuntu version

Review all error logs:

sudo journalctl -u mysql -n 50
sudo tail -100 /var/log/mysql/error.log

Verify system resources:

free -h  # Check available memory
df -h    # Check disk space

Search for specific error messages in the MySQL documentation or community forums

Conclusion: Your MySQL Foundation is Secure

Congratulations! You've successfully installed MySQL 8.x on Ubuntu 24 and implemented enterprise-level security measures that will protect your production database from common threats and vulnerabilities.

Continue to Part 2: In our companion article, we cover automated backup strategies, secure remote access configuration, and performance optimization techniques. Check out "MySQL 8.x Production Guide Part 2" to complete your production-ready MySQL setup.

Quick Reference: Essential Commands

# Check MySQL service status
sudo systemctl status mysql

# View MySQL error logs
sudo tail -f /var/log/mysql/error.log

# Connect to MySQL
sudo mysql -u root -p

# Show all databases
SHOW DATABASES;

# Show all users
SELECT user, host FROM mysql.user;

# Verify SSL is enabled
SHOW VARIABLES LIKE '%ssl%';

Questions about MySQL installation or security? Drop them in the comments below! For the complete production guide including backups and performance tuning, check out Part 2 of this series.