BLOG POSTS

MangoHost Blog / How to Install MySQL on Ubuntu 24

How to Install MySQL on Ubuntu 24

Getting MySQL up and running on Ubuntu 24 is probably one of the first things you’ll want to tackle when setting up a new server. Whether you’re migrating from an older Ubuntu version, spinning up a fresh VPS, or just need a reliable database backend for your applications, this guide will walk you through the entire process from start to finish. We’ll cover everything from the basic installation to securing your setup, plus some real-world scenarios and gotchas that could save you hours of debugging later. Trust me, getting this right from the beginning will make your life so much easier when you start deploying actual applications.

How MySQL Installation Works on Ubuntu 24

Ubuntu 24 comes with MySQL 8.0 in its default repositories, which is fantastic because you get all the latest performance improvements and security features out of the box. The installation process leverages the APT package manager, which handles dependencies automatically and sets up the systemd service files for you.

Here’s what happens under the hood when you install MySQL:

APT downloads the MySQL server package and its dependencies
The installation script creates a dedicated mysql user and group
Default configuration files are placed in /etc/mysql/
Data directory is initialized at /var/lib/mysql/
Systemd service files are registered for automatic startup
A temporary root password is generated (if you’re using the Oracle version)

The beauty of Ubuntu’s package management is that it handles all the heavy lifting. No more compiling from source or wrestling with dependency hell like in the old days.

Step-by-Step Installation Guide

Let’s dive into the actual installation process. I’ll show you both the standard Ubuntu repository method and the official MySQL repository method, so you can choose what works best for your situation.

Method 1: Installing from Ubuntu Repositories (Recommended for Most Users)

First, let’s update the package index and install MySQL server:

sudo apt update
sudo apt install mysql-server

That’s it for the basic installation! The service should start automatically, but let’s verify:

sudo systemctl status mysql

You should see something like this:

● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Mon 2024-01-15 10:30:22 UTC; 2min 15s ago

Now comes the crucial part – securing your installation:

sudo mysql_secure_installation

This script will walk you through several security settings. Here’s what I recommend for most setups:

Validate Password Plugin: Yes (choose MEDIUM or STRONG)
Remove anonymous users: Yes
Disallow root login remotely: Yes (unless you specifically need it)
Remove test database: Yes
Reload privilege tables: Yes

Method 2: Installing from Official MySQL Repository

If you need the absolute latest version or want to ensure you’re getting Oracle’s official build, here’s how to do it:

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
sudo apt update
sudo apt install mysql-server

The configuration dialog will let you choose which MySQL version to install. For production environments, I usually stick with the LTS releases.

Initial Configuration and Testing

Let’s make sure everything is working properly. First, let’s connect to MySQL:

sudo mysql -u root -p

Create a test database and user to verify functionality:

CREATE DATABASE testdb;
CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'SecurePassword123!';
GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Now test the connection with your new user:

mysql -u testuser -p testdb

If you can connect successfully, you’re golden!

Real-World Examples and Use Cases

Let me share some scenarios I’ve encountered over the years, including both success stories and spectacular failures.

Scenario 1: Web Application Backend

Most of the time, you’ll be setting up MySQL for a web application. Here’s a typical configuration for a WordPress site:

# Connect to MySQL
sudo mysql -u root -p

# Create database and user for WordPress
CREATE DATABASE wordpress_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'wp_user'@'localhost' IDENTIFIED BY 'YourStrongPassword!@#';
GRANT ALL PRIVILEGES ON wordpress_db.* TO 'wp_user'@'localhost';
FLUSH PRIVILEGES;

Pro tip: Always use utf8mb4 for WordPress databases. I learned this the hard way when emoji support broke on a client’s site.

Scenario 2: Development Environment

For development work, you might want to enable remote connections. Here’s how to configure it safely:

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

# Change bind-address to allow remote connections
bind-address = 0.0.0.0

# Restart MySQL
sudo systemctl restart mysql

Then create a user for remote access:

CREATE USER 'devuser'@'%' IDENTIFIED BY 'DevPassword123!';
GRANT ALL PRIVILEGES ON development_db.* TO 'devuser'@'%';
FLUSH PRIVILEGES;

Warning: Never do this on a production server exposed to the internet without proper firewall rules!

Common Gotchas and How to Fix Them

Here are some issues I’ve run into repeatedly:

Problem	Symptom	Solution
Can’t connect as root	“Access denied for user ‘root’@’localhost'”	Use `sudo mysql -u root` (no password needed initially)
Service won’t start	MySQL fails to start after installation	Check `/var/log/mysql/error.log` for clues
Out of disk space	MySQL crashes or becomes unresponsive	Check `/var/lib/mysql/` disk usage and clean up logs

Performance Tuning and Optimization

Once you have MySQL running, you’ll want to optimize it for your specific use case. Here are some key configuration tweaks:

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

# Add these optimizations under [mysqld]
innodb_buffer_pool_size = 1G  # Set to 70-80% of available RAM
max_connections = 100
query_cache_size = 16M
query_cache_limit = 1M

For a VPS with limited resources, you might need to be more conservative with memory allocation. On a dedicated server, you can be more aggressive with the buffer pool size.

Integration with Other Tools

MySQL plays nicely with a ton of other tools in the ecosystem. Here are some popular combinations:

phpMyAdmin for Web-Based Management

sudo apt install phpmyadmin
sudo mysql -u root -p

# Create a user for phpMyAdmin
CREATE USER 'phpmyadmin'@'localhost' IDENTIFIED BY 'AdminPassword123!';
GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'localhost' WITH GRANT OPTION;

Automated Backups with mysqldump

Set up automated backups with a simple cron job:

# Create 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)
mysqldump -u backup_user -p'BackupPassword123!' --all-databases > $BACKUP_DIR/mysql_backup_$DATE.sql
gzip $BACKUP_DIR/mysql_backup_$DATE.sql

# Make it executable
sudo chmod +x /usr/local/bin/mysql-backup.sh

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

MySQL vs. Alternatives: The Numbers Game

Let’s talk about why you might choose MySQL over other database solutions:

Database	Memory Usage (Idle)	Startup Time	Learning Curve	Best For
MySQL 8.0	~150MB	~3-5 seconds	Medium	Web applications, CMS
PostgreSQL	~120MB	~2-4 seconds	Steep	Complex queries, JSON data
MariaDB	~140MB	~3-5 seconds	Easy (MySQL compatible)	Drop-in MySQL replacement
SQLite	~1MB	Instant	Easy	Small applications, prototyping

MySQL still dominates the web application space with roughly 39% market share according to the latest Stack Overflow Developer Survey. It’s battle-tested, well-documented, and has an enormous community.

Monitoring and Maintenance

Don’t just install MySQL and forget about it! Here are some essential monitoring commands:

# Check MySQL status
sudo systemctl status mysql

# View current connections
mysql -u root -p -e "SHOW PROCESSLIST;"

# Check database sizes
mysql -u root -p -e "SELECT table_schema AS 'Database', 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)' 
FROM information_schema.tables GROUP BY table_schema;"

# Monitor slow queries
sudo tail -f /var/log/mysql/mysql-slow.log

I recommend setting up log rotation to prevent your disk from filling up:

sudo nano /etc/logrotate.d/mysql-server

/var/log/mysql/*.log {
    daily
    missingok
    rotate 52
    compress
    delaycompress
    notifempty
    create 644 mysql adm
    sharedscripts
    postrotate
        /bin/kill -HUP `cat /var/run/mysqld/mysqld.pid 2> /dev/null` 2> /dev/null || true
    endscript
}

Automation and Scripting Possibilities

Once you have MySQL installed, it opens up a whole world of automation possibilities. You can script database deployments, automate schema migrations, and integrate with CI/CD pipelines.

Here’s a simple deployment script I use for staging environments:

#!/bin/bash
# deploy-database.sh

DB_NAME="staging_app"
DB_USER="app_user"
DB_PASS="$(openssl rand -base64 32)"

# Create database and user
mysql -u root -p"$MYSQL_ROOT_PASSWORD" << EOF
CREATE DATABASE IF NOT EXISTS $DB_NAME;
CREATE USER IF NOT EXISTS '$DB_USER'@'localhost' IDENTIFIED BY '$DB_PASS';
GRANT ALL PRIVILEGES ON $DB_NAME.* TO '$DB_USER'@'localhost';
FLUSH PRIVILEGES;
EOF

# Import schema
mysql -u $DB_USER -p"$DB_PASS" $DB_NAME < schema.sql

echo "Database deployed successfully!"
echo "Database: $DB_NAME"
echo "Username: $DB_USER"
echo "Password: $DB_PASS"

This kind of automation becomes invaluable when you're managing multiple environments or deploying frequently.

Troubleshooting Common Issues

Let me share some real-world troubleshooting scenarios I've encountered:

The "MySQL Won't Start After Reboot" Problem

This usually happens when the service isn't enabled for automatic startup:

sudo systemctl enable mysql
sudo systemctl start mysql

The "Too Many Connections" Error

When your application suddenly can't connect:

# Check current connections
mysql -u root -p -e "SHOW STATUS LIKE 'Threads_connected';"

# Increase max_connections temporarily
mysql -u root -p -e "SET GLOBAL max_connections = 200;"

# Make it permanent by editing /etc/mysql/mysql.conf.d/mysqld.cnf
max_connections = 200

The "InnoDB Corruption" Nightmare

This is every DBA's worst nightmare, but it's recoverable:

# Add to mysqld.cnf temporarily
innodb_force_recovery = 1

# Restart MySQL
sudo systemctl restart mysql

# Dump all data
mysqldump -u root -p --all-databases > recovery_backup.sql

# Remove the force_recovery line and restart
sudo systemctl restart mysql

# Restore from backup if needed

Security Best Practices

Security should be your top priority. Here are some additional hardening steps beyond the basic secure installation:

# Disable remote root login completely
mysql -u root -p -e "DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');"

# Remove the test database if it still exists
mysql -u root -p -e "DROP DATABASE IF EXISTS test;"

# Create a dedicated backup user with minimal privileges
mysql -u root -p << EOF
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'BackupUserPassword123!';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;
EOF

Consider implementing SSL/TLS for database connections in production environments. You can generate certificates and configure MySQL to require encrypted connections for sensitive applications.

Conclusion and Recommendations

MySQL on Ubuntu 24 is a solid, reliable foundation for most database needs. The installation process has become incredibly streamlined compared to earlier versions, and the default security posture is much better than it used to be.

Here's my recommendation breakdown:

Use the Ubuntu repository version for most production deployments - it's well-tested and receives security updates through the normal Ubuntu channels
Go with the official MySQL repository if you need cutting-edge features or are building a database-centric application
Always run mysql_secure_installation - no exceptions
Set up automated backups from day one - you'll thank me later
Monitor your installation - use tools like mytop or innotop for real-time monitoring

Whether you're running a simple WordPress blog on a VPS or a complex web application on a dedicated server, MySQL 8.0 on Ubuntu 24 will serve you well. The combination offers excellent performance, strong security defaults, and the reliability you need for production workloads.

Remember that databases are often the bottleneck in web applications, so invest time in proper configuration and monitoring. A well-tuned MySQL installation can handle thousands of concurrent connections and massive datasets without breaking a sweat.

For additional resources and community support, check out the official MySQL documentation at dev.mysql.com/doc/ and the Ubuntu Server Guide at ubuntu.com/server/docs. The MySQL community is incredibly helpful, and you'll find solutions to virtually any problem you might encounter.

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.