MySQL 8

Manual Installation and Configuration of MySQL 8.0 on Ubuntu 24.04 VPS for Optimal Performance

Philip Jensen

— 2 min read

Introduction

At ByteHosting, we understand the importance of a reliable and high-performing database system for your applications. If you're setting up a new server, installing MySQL 8.0 on your Ubuntu 24.04 VPS is a great choice for its robustness and features. In this guide, we’ll walk you through the native installation and performance tuning of MySQL 8.0, especially tailored for low-resource environments. Whether you're a developer or a system administrator, our step-by-step instructions will help you optimize MySQL for your needs.

Prerequisites: Setting up your Ubuntu 24.04 VPS

Before we begin, ensure your VPS is running Ubuntu 24.04. We recommend a clean installation to avoid conflicts. If you haven't already, connect to your server via SSH:

ssh username@your-server-ip

Once connected, update your package list and upgrade existing packages to ensure compatibility:

sudo apt update && sudo apt upgrade -y

This step helps prevent issues during the MySQL installation process.

Installing MySQL 8.0 from official repositories

Ubuntu 24.04 includes MySQL 8.0 in its default repositories, making installation straightforward. To install MySQL 8.0, run:

sudo apt install mysql-server -y

During installation, the system will prompt you to set a root password and configure security options. Follow the prompts carefully.

After installation, verify that MySQL is running:

systemctl status mysql

If it's active and running, you're ready to proceed with configuration.

Basic configuration for performance optimization

For low-resource environments, tuning MySQL is crucial. We recommend editing the MySQL configuration file located at /etc/mysql/mysql.conf.d/mysqld.cnf. Here are some essential settings:

[mysqld]
# Basic settings
max_connections = 100
innodb_buffer_pool_size = 256M
query_cache_size = 64M
tmp_table_size = 64M
max_heap_table_size = 64M

# Disable binary logging if not needed
skip-log-bin

# Enable slow query log for troubleshooting
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

Adjust innodb_buffer_pool_size based on your available RAM. For a low-resource VPS, 256MB is a good starting point.

After editing, restart MySQL to apply changes:

sudo systemctl restart mysql

Securing your MySQL installation

Security is vital. Run the built-in security script:

sudo mysql_secure_installation

This script will prompt you to set a root password, remove anonymous users, disallow root login remotely, and remove test databases. Follow the prompts to enhance your server's security.

Verifying the installation and performance tuning

To verify your MySQL installation, log in as root:

sudo mysql -u root -p

Run a simple query to check performance:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

This confirms your configuration changes. For ongoing performance tuning, monitor slow queries and adjust settings accordingly.

Troubleshooting common issues during installation and setup

  • MySQL fails to start: Check logs with journalctl -u mysql for errors.
  • Configuration changes not applying: Ensure you restarted MySQL after editing the config file.
  • Performance issues: Use tools like mysqltuner or pt-query-digest to analyze and optimize.

Conclusion

Installing MySQL 8.0 on your Ubuntu 24.04 VPS is a straightforward process that, when properly configured, provides a solid foundation for your database needs. At ByteHosting, we offer affordable VPS plans that can host your MySQL server with excellent uptime and performance. Remember to regularly monitor and tune your database for optimal results. With these steps, you’re well on your way to a secure, efficient, and high-performing MySQL environment on your Ubuntu VPS.

Read more