Introduction
In Ubuntu 22.04, it provides MySQL 8 by default in repo. This article is not about the installation of MySQL, but about how to configure after the installation. I use this for my personal memo about the whole process for later reference.
Prerequisites
- Ubuntu 22.04
- Non-root user with sudo access
- Internet connection
Install MySQL
First of all, you need to make sure all of your packages are up to date.
sudo apt update
Then, install mysql server.
sudo apt install mysql-server
Check if the service is running.
sudo systemctl is-active mysql
If it is not active, run:
sudo systemctl start mysql
MySQL Configuration
For the fresh installation, you need to run secure script to keep your database secured.
Warning: As of July 2022, an error will occur when you run the mysql_secure_installation script without some further configuration. The reason is that this script will attempt to set a password for the installation’s root MySQL account but, by default on Ubuntu installations, this account is not configured to connect using a password.To bypass this issue, you have to run the following commands:
Login to MySQL.
sudo mysql
Then change root account authentication to one that uses password.
mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
Exit MySQL.
mysql > exit
Then mysql_secure_installation can be triggered without any issue
Run mysql_secure_installation script
mysql_secure_installation
You can then choose your perferences by yourself. I use most of the default options.
(Optional) Swith root account authentication method back to default:
mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;
Then, you can login with root by "sudo mysql".
Create New Account
Now, MySQL has finished setup with only root account. It is gernally not a good idea to use root account. This part is about create a new user account and grant it privileges.
Login to MySQL with root account:
sudo mysql
or if you haven't done switch authentication method:
mysql -u root -p
Create a new user with
mysql > CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';
Grant privilieges
mysql > GRANT ALL ON *.* TO '<usernname>'@'localhost';
Details about create options:
Conclusion
After finishing above steps, you will get a basic workable MySQL database. It will give you a non-root account with local access. For remote access, I will put it in other articles.
This article is inspired by "How To Install MySQL on Ubuntu 22.04" from Digital Ocean. I tailored it for my personal needs. Original article contains more details. If you are interested in more details, please follow the link below:
Hazel Virdó