MySQL Installation and Secure First Configuration on Ubuntu Servers

💡 What Will You Learn in This Guide?

In this guide, you will learn how to install MySQL database server from scratch on Ubuntu (22.04 LTS and above) systems, securely configure the root password, and create a special user account for daily use.
You will also see step by step how to manage the MySQL service, adjust UFW settings for secure network access, and troubleshoot possible connection problems.

🚀 Once this guide is completed, you will have a secure MySQL server ready for a production environment.

🧠 Stage 1 – Analyze (Comprehension) Content

Main Technical Topic:
Installing MySQL on Ubuntu Linux and applying the secure initial configuration.

Problem Solved:
On newly installed Ubuntu systems, the MySQL root account works with the auth_socket plugin by default. This creates passwordless connection problems. The guide aims to solve this problem safely and make it ready for the production environment.

Steps:

1. Update the package manager and install MySQL server.
2. Edit the authentication of the root account.
3. Configure security settings with the mysql_secure_installation script.
4. Create and authorize new user.
5. Check service status.
6. Set access rules with UFW firewall.

---

⚙️ 1. MySQL Installation

Run the following commands to install MySQL 8 from Ubuntu's official repositories:

sudo apt update
sudo apt install mysql-server -y

Once the installation is complete, check the status of the service:

sudo systemctl status mysql

💡 If the service status is “active (running)”, MySQL has been installed successfully. If not, you can start the service manually:

sudo systemctl start mysql

---

🔐 2. Secure Initial Configuration

In new installations, the root account does not require a password. This may cause the mysql_secure_installation script to fail. So first change the authentication method.

2.1 Changing Root Authentication

sudo mysql

Run the following command in the MySQL shell:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'GucluBirParola!';

Then exit:

exit

---

2.2 Running the Security Script

sudo mysql_secure_installation

Provide the suggested answers in the following steps:

Question	Suggested Answer
Use VALIDATE PASSWORD component?	Y
Password policy level	STRONG (2)
Remove anonymous users?	Y
Turn off root's remote access?	Y
Remove test database?	Y

🔒 These steps make MySQL safe for production environment.

---

2.3 Root Back to auth_socket (Optional)

If you want to access MySQL only with the sudo mysql command:

sudo mysql
ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;
exit

---

👥 3. Creating a New MySQL User

It is not safe to use the root account for daily work. To create a new user:

sudo mysql
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'CokGucluBirParola!';
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, INDEX, DELETE, SELECT, REFERENCES, RELOAD ON *.* TO 'appuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
exit;

💡 This user can now access the database without root privileges. For connection testing:

mysql -u appuser -p

---

🧱 4. Service Management Commands

MySQL is managed through systemd. Frequently used service commands are below:

Transaction	Command
Start service	sudo systemctl start mysql
Stop service	sudo systemctl stop mysql
Restart service	sudo systemctl restart mysql
View service status	sudo systemctl status mysql
Enable autostart	sudo systemctl enable mysql

🚀 Thanks to the enable command, MySQL is automatically turned on every time the system is restarted.

---

🔥 5. Firewall (UFW) Settings

By default MySQL only allows connection via localhost (127.0.0.1). If remote access is required, specify only trusted IPs.

sudo ufw allow from 192.168.1.10 to any port 3306

Check the status:

sudo ufw status

⚠️ NEVER use the sudo ufw allow 3306 command directly. This exposes MySQL to the internet and poses a major security risk.

---

🧰 6. Installation Test

To test that the installation was successful:

mysqladmin -u appuser -p version

And create a simple database:

mysql -u appuser -p -e "CREATE DATABASE testdb; SHOW DATABASES;"

If testdb appears in the list, MySQL is running properly.

---

💬 Frequently Asked Questions (FAQ)

1. What is the $auth_socket$ plugin?

auth_socket authenticates with the system user account. No password required but does not allow remote access.

2. Why is $mysql_secure_installation$ necessary?

This script rejects weak passwords, deletes anonymous users, and secures MySQL by turning off root remote access.

3. My PHP or application cannot connect to MySQL, why?

Some PHP versions do not include caching_sha2_password support. Change the user plugin with the following command:

ALTER USER 'appuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Parola!';

4. MySQL does not open, what should I do?

Check the status:

sudo systemctl status mysql
sudo journalctl -xe

These logs show the cause of the error (e.g. port conflict or missing permissions).

5. How to test remote connection with UFW?

You can test it from the IP you allow with this command:

mysql -h <sunucu_ip_adresi> -u appuser -p

⚠️ Common Errors and Solutions

Error	Reason	Solution
Unknown database	No target database	Use the command CREATE DATABASE
Access denied for user	Incorrect password or authorization	Create new password with ALTER USER
Table already exists	Same table available	Export with parameter --add-drop-table
Broken Turkish characters	Charset mismatch	Add --default-character-set=utf8mb4
Missing authorization error	Insufficient user permission	Work as root or full power user

🧭 Result

In this guide, you have completed MySQL 8 installation on Ubuntu, root password configuration, user management, service control and UFW security. Now your system is both secure and production ready.

☁️ You can automate MySQL installation and take your security to the next level by performing all these operations with a single click through the GenixNode Platform.