MySQL 8.x Production Guide Part 1: Installation & Enterprise-Level Security on Ubuntu 24

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:

1. VALIDATE PASSWORD COMPONENT - Choose YES for production
   • Enforces strong password policies
   • Recommended: Set to STRONG (Level 2)
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)
3. Remove anonymous users - Choose YES
   • Anonymous users are a security risk
4. Disallow root login remotely - Choose YES
   • Root should only connect from localhost
   • We'll create separate users for remote access
5. Remove test database - Choose YES
   • Test databases can be accessed by anyone
6. 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:

# 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

# 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

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

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

sudo systemctl restart mysql
sudo systemctl status mysql

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

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

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

# 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

# Use sudo to login
sudo mysql

# Or specify socket authentication
mysql -u root

# 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;

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

sudo ufw status verbose
sudo ufw status numbered

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

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 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

# 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

# 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:

1. Check MySQL version compatibility:

   mysql --version
   lsb_release -a  # Check Ubuntu version
   

2. Review all error logs:

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

3. Verify system resources:

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

4. 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.