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.
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
![Update system and install MySQL](https://arriveddev.com/wp-content/uploads/2022/04/how-to-install-mysql-on-ubuntu-22-04-20-04-1.jpg)
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.
![Secure MYSQL on Ubuntu](https://arriveddev.com/wp-content/uploads/2022/04/how-to-install-mysql-on-ubuntu-22-04-20-04-2.jpg)
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.
![Check config MySQL](https://arriveddev.com/wp-content/uploads/2022/04/how-to-install-mysql-on-ubuntu-22-04-20-04-3.jpg)
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.
– Advertising –