How to install and optimization MySQL on Ubuntu 22.04 LTS

Installing and optimizing MySQL on Ubuntu 22.04 LTS is essential for achieving high performance and reliability for your database-driven applications. This guide will walk you through the steps of installing MySQL and then optimizing its configuration for best performance.

Optimize MySQL: Elevate Your Database Performance on Ubuntu 22.04

Step 1. Update system and install MySQL

Before installing MySQL, it’s good practice to update your package index. Open a terminal and run the following commands:

sudo apt update && sudo apt upgrade -y
sudo apt install mysql-server

Type Y to confirm the settings, then go to the MySQL security step.

Step 2. Secure MySQL Installation

MySQL installation comes with a script called mysql_secure_installation that allows you to easily improve the security of your database server.

sudo mysql_secure_installation

• Press y|Y for Yes, any other key for No: Leave blank and type Enter
• Please set the password for root here: Enter the password and confirm
• The next steps Enter Y to delete unnecessary users, and database tests.

Step 3. Optimization MySQL on Ubuntu 22.04

Determine the configuration

First, to determine which file is configuring for MYSQL run the following command.

mysqld --help --verbose | head -n13

As you can see in the image below the config file is set up in the order of /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf, you can modify the config in any of the files.

Edit the MySQL configuration file located at /etc/mysql/my.cnf:

sudo nano /etc/mysql/my.cnf

Key Configuration Tweaks

InnoDB Buffer Pool Size: This setting determines the amount of memory allocated to the InnoDB buffer pool. A good rule of thumb is to set it to 70-80% of your system’s RAM if MySQL is the only major application running on the server.

innodb_buffer_pool_size = 2G

Adjust 2G according to your server’s memory.

Query Cache: Enable query caching to improve performance for repeated queries.

query_cache_type = ON
query_cache_size = 256M

Max Connections: Increase the maximum number of connections if your application needs to handle many concurrent users.

max_connections = 500

Thread Cache Size: Helps in reusing threads, reducing the overhead of creating new ones.

thread_cache_size = 50

Table Open Cache: Increase this value to improve the performance of table open operations.

table_open_cache = 2000

Next, after changing the config you can check if the config has any problems with the following command. If there is an error you will receive a notice and recheck the value of that config.

mysqld --defaults-file=/etc/mysql/my.cnf --validate-config

Restart MYSQL after the config change is done.

sudo service mysql restart

Note: You need to adjust the configuration values to suit your application/website for optimal performance. Additionally, you can refer to this configuration file, which has been compiled by experienced experts.

Step 4. Create a Database and User

To manage MySQL, you need to log in as the root user:

sudo mysql

It’s a good practice to create a separate user and database for your applications. Here’s how you can create a new database and a user with all privileges on that database:

CREATE DATABASE db_name;
CREATE USER 'user_name'@'localhost' IDENTIFIED BY "password";
GRANT ALL PRIVILEGES ON db_name.* TO 'user_name'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
\q;

As you’ve seen, installing and optimizing MySQL on Ubuntu 22.04 LTS is a straightforward process that can be accomplished with minimal effort. With MySQL up and running, you’re ready to start managing your databases effectively and efficiently.