In this tutorial, you will install MySQL Server on Ubuntu, run mysql_secure_installation to harden the default setup, understand root's default authentication method, and create a dedicated non-root database and user for an application.
Prerequisites
A server/VPS running Ubuntu 22.04 or 24.04
A non-root user account with sudo privileges
Basic familiarity with the Linux command line
Let an AI agent do this for you
Copy a ready-made prompt for an AI coding assistant with terminal access to your server (Claude Code, Cursor, or similar) — it can carry out the steps below for you. Review what it plans to run before it executes anything.
MySQL is one of the most widely used relational database servers on Linux, and Ubuntu's official apt repositories carry a stable, well-tested build of it. A default installation is reasonably locked down out of the box, but it still leaves a few things — an unset root password policy, a test database, anonymous accounts — that you should clean up before putting any real application data behind it. This tutorial walks through installing MySQL Server, running its built-in hardening script, and setting up a dedicated non-root user for an application, which is the correct way to connect an app to the database instead of using the root account.
Step 1 — Install MySQL Server
Update your local package index and install the mysql-server package from Ubuntu's official repositories:
This installs the server daemon, the mysql command-line client, and a systemd unit that's enabled to start automatically on boot.
Step 2 — Verify the Service Is Running
bash
systemctl status mysql
You should see active (running) in the output. Press q to return to the shell. If the service isn't running, start it with sudo systemctl start mysql and check sudo journalctl -u mysql for errors.
Step 3 — Run the Built-in Security Script
The mysql-server package ships with mysql_secure_installation, an interactive script that walks through several hardening steps that a fresh install doesn't apply for you automatically. Run it:
bash
sudo mysql_secure_installation
It will prompt you through the following, in order:
Whether to set up the validate_password component, which enforces a minimum password strength policy (length, mixed case, numbers, special characters) for any account created or changed afterward.
Setting (or confirming) a password for the MySQL root account.
Removing anonymous user accounts — these exist by default in some MySQL distributions to allow anyone to log in without a username, and are meant only for testing.
Tricknowtech VPS Hosting
Dedicated KVM resources and full root access — deployed in under 60 seconds, no ticket required.
Disallowing root login from remote hosts, so the root account can only be used from the local machine.
Removing the test database along with its associated privileges — it's world-accessible by default and isn't meant for production use.
Reloading the privilege tables so all of the above changes take effect immediately.
For a production server, answer yes (Y) to every prompt.
Step 4 — Check How Root Authenticates
On a fresh Ubuntu install, the MySQL root account typically doesn't use a password at all — it authenticates via the auth_socket (also called unix_socket) plugin, which checks that you're running the mysql client as the Linux root user (via sudo) rather than checking a password. That's why sudo mysql gets you straight into a root MySQL shell with no password prompt. You can confirm this:
bash
sudo mysql -e "SELECT user, host, plugin FROM mysql.user;"
If the plugin column for root@localhost shows auth_socket (or unix_socket), root can only be reached locally through sudo mysql — there's no password to enter. If you have a specific reason to log in as root with a password instead (for example, a GUI tool that connects over TCP), switch the auth method:
bash
sudo mysql
sql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'a_strong_password_here';
FLUSH PRIVILEGES;
“unix_socket authentication is actually MORE secure for local-only root access — there's no password to leak, brute-force, or reuse elsewhere. Only make this change if something specifically requires password-based root login. If you do, replace a_strong_password_here with a long, unique password, and treat it as a secret from that point on: don't write it in scripts, commit it to a repo, or share it over chat.”
Step 5 — Create an Application Database and a Dedicated User
Never point an application at MySQL using the root account. If that application (or a vulnerability in it) is ever compromised, an attacker with root database access can read or modify anything on the server. Instead, create a database and a user scoped only to that database. Log in and run the following:
bash
sudo mysql
sql
CREATE DATABASE appdb;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'a_different_strong_password';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;
EXIT;
Notice the grant is scoped to appdb.* — every table in the appdb database — not *.* (every table in every database). This is the least-privilege pattern: appuser can do anything it needs within its own database, but has no visibility into any other database on the server. Pick a_different_strong_password carefully — it's a secret credential your application will use to authenticate, so generate something long and random, store it in your application's environment configuration (not in source control), and don't reuse the root password from Step 4.
Step 6 — Confirm MySQL Only Listens on Localhost
By default, MySQL on Ubuntu binds only to 127.0.0.1, meaning it will not accept connections from outside the machine at all — no firewall rule is even needed to protect it from the network, because it isn't reachable over the network in the first place. You can confirm this setting:
This should output bind-address = 127.0.0.1. Leave it as-is unless you have a genuine need for something else (another server) to connect to this MySQL instance directly over the network.
“If you do need remote access — say, an application server on a different machine connecting to this database — changing bind-address to the server's private or public IP must be paired with a firewall rule that restricts port 3306 to that specific trusted source IP, never opened to the whole internet. Using ufw, that looks like: sudo ufw allow from your_trusted_ip to any port 3306 — replacing your_trusted_ip with the actual trusted source IP address. Do not run a broad sudo ufw allow 3306, which would let any host on the internet attempt to reach MySQL.”
Step 7 — Verify: Connect as the New User
Confirm appuser can actually authenticate and reach appdb:
bash
mysql -u appuser -p appdb
Enter the password you set in Step 5 when prompted. You should land at a mysql> prompt. Run SELECT DATABASE(); to confirm you're connected to appdb, then type EXIT; to leave.
Troubleshooting: "Access Denied for User"
If mysql -u appuser -p appdb fails with something like ERROR 1045 (28000): Access denied for user 'appuser'@'localhost', it's almost always one of two things: a wrong password, or a host mismatch between how the user was created and how you're connecting.
The user was created as 'appuser'@'localhost', which in MySQL means connections made through the local Unix socket (the default when you run mysql without a -h flag, or with -h localhost). If you instead connect with an explicit TCP address, e.g. mysql -u appuser -p -h 127.0.0.1 appdb, MySQL treats that as a different connection path than the socket, and 'appuser'@'localhost' won't match it — you'd need a separate grant for 'appuser'@'127.0.0.1' (or a broader host pattern) for that to work. Keep the host part of the connection consistent with the host part the user was granted for: no -h flag (or -h localhost) for a @'localhost' user, and a matching @'127.0.0.1' or @'%' grant if you specifically need TCP.
