Setting up MySQL on Ubuntu 24.04 LTS is a fundamental skill every developer and sysadmin needs in their toolkit. Whether you’re spinning up a new development environment, migrating legacy applications, or architecting production infrastructure, MySQL remains one of the most reliable and widely-adopted relational database solutions. This comprehensive guide walks you through the complete installation process, from initial setup to production-ready configuration, including security hardening, performance tuning, and troubleshooting common issues that can save you hours of debugging.

Understanding MySQL on Ubuntu 24.04

Ubuntu 24.04 LTS ships with MySQL 8.0 in its default repositories, which brings significant improvements over previous versions. The new authentication mechanism (caching_sha2_password), improved performance with better indexing algorithms, and enhanced JSON support make it a solid choice for modern applications.

The installation process has been streamlined compared to earlier Ubuntu versions, but there are still some gotchas around authentication plugins and initial user setup that catch people off guard. MySQL 8.0 also introduces stricter SQL modes by default, which can break legacy applications if you’re not prepared.

Prerequisites and System Requirements

Before diving into installation, make sure your system meets these requirements:

• Ubuntu 24.04 LTS (server or desktop edition)
• At least 1GB RAM (2GB+ recommended for production)
• 500MB free disk space for basic installation
• Root or sudo access
• Stable internet connection for package downloads

First, update your package index to ensure you’re getting the latest versions:

sudo apt update && sudo apt upgrade -y

Step-by-Step MySQL Installation

Method 1: Installing from Ubuntu Repositories

The simplest approach uses Ubuntu’s default repositories:

sudo apt install mysql-server mysql-client -y

This installs both the MySQL server and client tools. The installation process automatically starts the MySQL service and enables it to start on boot.

Check the installation status:

sudo systemctl status mysql

You should see output indicating the service is active and running.

Method 2: Installing from MySQL Official Repository

For the latest MySQL version or specific version requirements, use the official MySQL APT repository:

# Download and install MySQL APT repository configuration
wget https://dev.mysql.com/get/mysql-apt-config_0.8.29-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.29-1_all.deb

# Update package index with new repository
sudo apt update

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

This method gives you more control over MySQL versions and components.

Securing Your MySQL Installation

The mysql_secure_installation script is crucial for production deployments. It handles several security-related configuration tasks:

sudo mysql_secure_installation

You’ll be prompted for several security options:

• Set root password strength validation
• Remove anonymous users
• Disable remote root login
• Remove test database
• Reload privilege tables

For production servers, answer “Y” to all prompts. For development environments, you might want to keep some defaults for convenience.

Initial Configuration and User Setup

MySQL 8.0 uses the auth_socket plugin for the root user by default, which means you can only connect as root using sudo. For application connections, create a dedicated user:

sudo mysql

# Create a new user with password authentication
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'your_strong_password';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

# For remote connections (be cautious in production)
CREATE USER 'admin'@'%' IDENTIFIED BY 'your_strong_password';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;
EXIT;

Test the new user connection:

mysql -u admin -p

Configuration File Optimization

The main MySQL configuration file is located at /etc/mysql/mysql.conf.d/mysqld.cnf. Here are essential settings for different use cases:

Development Environment Configuration

# Edit the configuration file
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# Add or modify these settings
[mysqld]
bind-address = 127.0.0.1
max_connections = 50
innodb_buffer_pool_size = 256M
query_cache_size = 16M
query_cache_limit = 1M

Production Environment Configuration

[mysqld]
bind-address = 0.0.0.0  # Allow remote connections (use with firewall)
max_connections = 200
innodb_buffer_pool_size = 1G  # 70-80% of available RAM
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
query_cache_size = 0  # Disabled in MySQL 8.0+
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

After making configuration changes, restart MySQL:

sudo systemctl restart mysql

Database and User Management Examples

Here are practical examples for common database administration tasks:

Creating Application Databases

mysql -u admin -p

# Create database for a web application
CREATE DATABASE webapp_production CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# Create dedicated user for the application
CREATE USER 'webapp_user'@'localhost' IDENTIFIED BY 'secure_app_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON webapp_production.* TO 'webapp_user'@'localhost';

# For development database
CREATE DATABASE webapp_development CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GRANT ALL PRIVILEGES ON webapp_development.* TO 'webapp_user'@'localhost';

FLUSH PRIVILEGES;

Setting Up Replication User

# Create replication user for master-slave setup
CREATE USER 'replicator'@'%' IDENTIFIED BY 'replication_password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

Performance Tuning and Monitoring

Key Performance Metrics

Monitor these critical MySQL metrics:

# Check current connections
SHOW STATUS LIKE 'Threads_connected';

# Monitor slow queries
SHOW STATUS LIKE 'Slow_queries';

# Check buffer pool utilization
SHOW STATUS LIKE 'Innodb_buffer_pool_pages%';

# View current configuration
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Performance Comparison: Different Storage Engines

Storage Engine	ACID Compliance	Row-level Locking	Foreign Keys	Best Use Case
InnoDB (default)	Yes	Yes	Yes	OLTP, web applications
MyISAM	No	No (table-level)	No	Read-heavy, data warehousing
Memory	No	No	No	Temporary data, caching

Common Issues and Troubleshooting

Connection Issues

Problem: “Can’t connect to MySQL server on ‘localhost'”

Solutions:

# Check if MySQL is running
sudo systemctl status mysql

# Check listening ports
sudo netstat -tlnp | grep mysql

# Restart MySQL service
sudo systemctl restart mysql

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

Authentication Plugin Issues

Problem: “Authentication plugin ‘caching_sha2_password’ cannot be loaded”

Solution: Switch to mysql_native_password for older applications:

mysql -u root -p

ALTER USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;

Memory and Performance Issues

Problem: High memory usage or slow queries

Diagnostic commands:

# Check MySQL process memory usage
ps aux | grep mysqld

# Monitor real-time MySQL performance
mysqladmin -u admin -p processlist

# Check slow query log
sudo cat /var/log/mysql/slow.log

MySQL vs Alternatives Comparison

Database	License	ACID Compliance	JSON Support	Replication	Learning Curve
MySQL 8.0	GPL/Commercial	Yes	Excellent	Master-Slave, Master-Master	Easy
PostgreSQL	Open Source	Yes	Native	Streaming, Logical	Moderate
MariaDB	Open Source	Yes	Good	Master-Slave, Galera	Easy
SQLite	Public Domain	Yes	Limited	No	Very Easy

Real-World Use Cases and Applications

Web Application Stack

MySQL pairs excellently with popular web frameworks:

# LAMP Stack (Linux, Apache, MySQL, PHP)
sudo apt install apache2 php libapache2-mod-php php-mysql

# Node.js with MySQL
npm install mysql2
# or for Promise-based operations
npm install mysql2/promise

E-commerce Platform Database

Example schema for product catalog:

CREATE DATABASE ecommerce CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ecommerce;

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_price (price),
    INDEX idx_stock (stock_quantity)
) ENGINE=InnoDB;

Analytics and Reporting

MySQL 8.0’s window functions make it suitable for analytical queries:

# Example: Monthly sales ranking
SELECT 
    product_name,
    monthly_sales,
    RANK() OVER (ORDER BY monthly_sales DESC) as sales_rank
FROM monthly_product_sales 
WHERE month = '2024-01';

Best Practices and Security Hardening

Security Checklist

• Always use strong passwords and enable password validation plugin
• Disable remote root login and remove anonymous users
• Use SSL/TLS for remote connections
• Regularly update MySQL and apply security patches
• Implement proper firewall rules
• Enable binary logging for point-in-time recovery
• Set up regular automated backups

Backup Strategy Implementation

# Create automated backup script
sudo nano /usr/local/bin/mysql-backup.sh

#!/bin/bash
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
DATABASES="webapp_production webapp_staging"

mkdir -p $BACKUP_DIR

for db in $DATABASES; do
    mysqldump -u backup_user -p'backup_password' \
        --single-transaction \
        --routines \
        --triggers \
        $db > $BACKUP_DIR/${db}_$DATE.sql
    
    # Compress backup
    gzip $BACKUP_DIR/${db}_$DATE.sql
done

# Add to crontab for daily backups at 2 AM
# 0 2 * * * /usr/local/bin/mysql-backup.sh

Firewall Configuration

# Allow MySQL only from specific sources
sudo ufw allow from 192.168.1.0/24 to any port 3306
sudo ufw deny 3306

# For development (less secure)
sudo ufw allow 3306

Monitoring and Maintenance

Set up monitoring to track MySQL health:

# Install monitoring tools
sudo apt install mytop mysql-utilities

# Monitor real-time MySQL activity
mytop -u admin -p

# Check table optimization needs
mysqlcheck -u admin -p --optimize --all-databases

Log Rotation Configuration

# Configure log rotation for MySQL logs
sudo nano /etc/logrotate.d/mysql-server

/var/log/mysql/*.log {
    daily
    missingok
    rotate 52
    compress
    delaycompress
    notifempty
    copytruncate
}

This comprehensive MySQL setup on Ubuntu 24.04 provides a solid foundation for both development and production environments. The key is starting with security in mind, implementing proper monitoring from day one, and following established best practices for configuration and maintenance. Remember that database performance tuning is an iterative process – monitor your specific workload and adjust configuration parameters accordingly.

For additional information, consult the official MySQL 8.0 documentation and Ubuntu’s MySQL community documentation.

---

---

This article incorporates information and material from various online sources. We acknowledge and appreciate the work of all original authors, publishers, and websites. While every effort has been made to appropriately credit the source material, any unintentional oversight or omission does not constitute a copyright infringement. All trademarks, logos, and images mentioned are the property of their respective owners. If you believe that any content used in this article infringes upon your copyright, please contact us immediately for review and prompt action.

This article is intended for informational and educational purposes only and does not infringe on the rights of the copyright owners. If any copyrighted material has been used without proper credit or in violation of copyright laws, it is unintentional and we will rectify it promptly upon notification. Please note that the republishing, redistribution, or reproduction of part or all of the contents in any form is prohibited without express written permission from the author and website owner. For permissions or further inquiries, please contact us.