> DevOps > MySQL Setup on Ubuntu: Secure DevOps Guide (2026)

Mastering MySQL Deployment on Ubuntu: A DevOps Field Guide

Introduction

Every DevOps engineer has a horror story about a database that was left wide open or a root password that was set to "password123." In the fast-paced world of infrastructure management, it is tempting to take shortcuts to get a service up and running. However, in production, those shortcuts become the technical debt of tomorrow.

This guide is designed to move beyond the "hello world" of database setup. We are building a secure, production-ready MySQL environment on Ubuntu. Whether you are a junior developer looking to understand the stack or a seasoned DevOps professional seeking a standardized checklist, this guide focuses on real-world practices: security hardening, granular user management, and operational efficiency.

In real production environments, databases are the backbone of applications. A single misconfiguration can lead to data leaks, downtime, or performance bottlenecks. That is why this guide not only shows commands but also explains the reasoning behind each step, helping you think like a DevOps engineer instead of just following tutorials.

Prerequisites

Before we execute a single command, ensure your environment meets the following criteria. Skipping these steps often leads to permission errors or security vulnerabilities down the line.

  • Operating System: Ubuntu (20.04, 22.04, or 24.04 LTS recommended).
  • User Access: Non-root user with sudo privileges.
  • Network Configuration: Firewall enabled and SSH access allowed.

In production setups, it is also recommended to use a cloud VM (AWS EC2, DigitalOcean, etc.) with proper IAM roles and restricted access. Avoid using root accounts for daily operations.

Environment Preparation

In a DevOps lifecycle, "drifting" configurations are the enemy. Before installing new software, we must synchronize our local package index with the remote repositories. 

sudo apt update && sudo apt upgrade -y

Why this matters: This ensures your system is fully updated with the latest patches. If you face errors like rate limiting during update, check this guide: Fix 429 Too Many Requests Error in APT Update.

Running outdated packages in production can expose your system to vulnerabilities. Many real-world attacks exploit outdated libraries rather than application code itself.

Installing MySQL Server

We will utilize the official Ubuntu repositories. This ensures stability and compatibility with system packages. 

sudo apt install mysql-server -y
sudo systemctl start mysql
sudo systemctl enable mysql
sudo systemctl status mysql

Always verify services after installation. If MySQL fails, logs are your best friend: 

journalctl -u mysql

In DevOps workflows, verification and logging are critical. Never assume installation success without validation.

The Critical Security Lockdown

A default MySQL installation is not production-safe. It includes test databases and anonymous users. 

sudo mysql_secure_installation
  • Enable password validation
  • Remove anonymous users
  • Disable remote root login
  • Remove test database
  • Reload privileges

These steps are mandatory in production. Skipping them can expose your database to brute-force attacks or unauthorized access. Be sure to remember the root password you define during this step.

Database & User Management

Never use the root account in your application source code. Always secure the root account first, then create a limited-access user. Need a secure password? We highly recommend using the LastPass Password Generator for all database credentials.

1. Access MySQL Shell

Log in explicitly using the root user and the password you just configured. Avoid using socket-based shortcuts.

sudo mysql

2. Reconfigure Root for Remote Access

By default, the root user is restricted to localhost. To allow remote root access (which must be secured by strict firewall rules), we will create a new root user for any host (%), grant it global privileges, and drop the local one to prevent conflicts.

CREATE USER 'root'@'%' IDENTIFIED WITH caching_sha2_password BY 'R7@yT4&wB9^pC2m';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
DROP USER 'root'@'localhost';
FLUSH PRIVILEGES;

3. Create Production Database

CREATE DATABASE production_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

4. Create Application User

Instead of restricting the application user to 'localhost', we will use the '%' wildcard. This allows the user to connect from any remote host, which is essential when applications and databases live on separate servers or within Docker containers.

CREATE USER 'devops_user'@'%' IDENTIFIED BY 'v9#Fq2P$kL8!mXz';

5. Grant Permissions & Exit

GRANT ALL PRIVILEGES ON production_db.* TO 'devops_user'@'%';
FLUSH PRIVILEGES;
EXIT;

6. Test Your New Access

Because we dropped the local root user, standard local socket connections will now fail. To log in with your newly created remote root user from the server terminal, you must force a TCP/IP connection using the loopback address:

sudo mysql -u root -p

Enter the 15-character password when prompted to verify your setup is correct.

Linux terminal showing a successful secure login to MySQL server using the root user and password prompt.

Essential Queries for DevOps

These are commonly used in real production environments.

Monitoring Traffic

SHOW PROCESSLIST;
Output of the MySQL SHOW PROCESSLIST command in a Linux terminal used for monitoring database connections.

Check Database List

SHOW DATABASES;
Terminal output displaying the results of the SHOW DATABASES command in MySQL, including a production database.

Check Users

SELECT user, host FROM mysql.user;
MySQL terminal showing the user and host table, verifying remote access configuration for root and devops users.

Check Table Sizes

SELECT table_name, round(((data_length + index_length) / 1024 / 1024), 2) as size_mb FROM information_schema.TABLES WHERE table_schema = 'production_db';

Credential Rotation

ALTER USER 'devops_user'@'%' IDENTIFIED BY 't5*Jv8@bN3!cY9q';

These queries help in debugging slow systems, identifying heavy tables, and maintaining security.

Maintenance & Performance Optimization

Database maintenance is ongoing. Poor maintenance leads to slow queries and downtime.

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

  • Slow Query Logs: Identify slow queries
  • Backups: Use mysqldump regularly
  • Memory: Optimize buffer pool size

Monitoring tools like Prometheus + Grafana are also recommended in production environments.

Frequently Asked Questions

1. Why MySQL?

Easy setup and widely supported.

2. Docker vs Bare Metal?

Docker is flexible, bare metal gives better performance.

3. Is remote access safe?

Only when restricted using firewall rules and private networking. Opening port 3306 to the entire internet, especially for a root@% user, is highly dangerous. Always use IP whitelisting.

Conclusion

Mastering MySQL deployment requires understanding security, performance, and monitoring. This guide gives you a real DevOps approach rather than just commands.

Always think in terms of reliability, scalability, and security when managing production databases.

f X W