> 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

Verify the service status:

Linux terminal output using the journalctl command to check MySQL service logs for troubleshooting

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.

Database & User Management

Never use root in applications. Always create a limited-access user.

1. Access MySQL Shell

sudo mysql

2. Create Database

CREATE DATABASE production_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3. Create User

CREATE USER 'devops_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';

4. Grant Permissions

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

This approach follows the principle of least privilege, reducing risk in case of compromise.

Essential Queries for DevOps

These are commonly used in real production environments.

Monitoring Traffic

SHOW PROCESSLIST;

Check Database List

SHOW DATABASES;

Check Users

SELECT user, host FROM mysql.user;

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'@'localhost' IDENTIFIED BY 'NewSecurePassword789!';

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.

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