Introduction
This guide walks through how to setup MySQL and create a user on Ubuntu 24.04, from installing the server through securing it and creating a dedicated, least-privilege database account. MySQL remains one of the most widely used database management systems for web applications, internal tools, and backend services, thanks to its reliability, performance, and huge ecosystem of tooling and documentation.
Whether you’re standing up a database for a new application, provisioning a development environment, or preparing a production server, the steps below cover the full process — including two things a lot of quick-start guides skip: running MySQL’s built-in security hardening script, and understanding the real trade-off involved in switching authentication plugins, rather than just doing it blindly.
By the end of this guide, you’ll have MySQL installed and secured on Ubuntu 24.04, along with a new database and a dedicated user scoped to only that database — not root, and not blanket access to everything on the server.
Implementation
I. Prerequisites
Before you setup MySQL and create a user on Ubuntu 24.04, make sure you have:
- Administrative (sudo or root) access to an Ubuntu 24.04 server or workstation
- A stable internet connection to download packages
- Basic familiarity with the Linux command line
Start by updating your system’s package index and upgrading any outdated packages:
sudo apt update sudo apt upgrade -y
Keeping your system current before installing new software reduces the chance of dependency conflicts and ensures you’re working from the latest available package versions, including security patches.
II. Install MySQL Server
With your system updated, install the MySQL server package:
sudo apt install mysql-server -y
Ubuntu’s package manager will pull in MySQL along with its required dependencies. Once installed, the MySQL service starts automatically. You can confirm it’s running with:
sudo systemctl status mysql
You should see active (running) in the output. If it’s not running for any reason, start it manually:
sudo systemctl start mysql sudo systemctl enable mysql
The enable command ensures MySQL starts automatically on every server boot, which you’ll generally want for any server running a production or long-lived application.
III. Secure the MySQL Installation
This step is commonly skipped in quick installation guides, but it shouldn’t be. MySQL ships with a built-in script that walks through several important hardening steps: removing anonymous users, disabling remote root login, removing the test database, and reloading privilege tables. Run it right after installation:
sudo mysql_secure_installation
You’ll be prompted through a series of yes/no questions and a password policy setup. For most installations, the recommended answers are:
- Validate Password Component: Enable it, and choose a strength level (Medium or Strong is recommended)
- Remove anonymous users: Yes
- Disallow root login remotely: Yes, unless you have a specific, well-secured reason to allow it
- Remove test database and access to it: Yes
- Reload privilege tables now: Yes
Skipping this step leaves your MySQL installation in a notably weaker default state — anonymous access and a publicly known test database are exactly the kind of low-hanging fruit automated scanners look for.
IV. Understand MySQL’s Authentication Plugins
Before touching authentication settings, it’s worth understanding what you’d actually be changing. Since MySQL 8.0, the default authentication plugin is caching_sha2_password, which offers stronger password hashing than the older mysql_native_password plugin.
Some older client libraries, drivers, or applications don’t yet support caching_sha2_password and will fail to connect until you either upgrade the client or switch the account to the older, less secure plugin. This is a real compatibility issue you may run into, but it’s important to treat it as a targeted workaround for specific accounts that need it — not a default step you should apply to your root user or every new account without a reason.
Security note: Downgrading authentication to
mysql_native_passwordweakens password hashing strength. Only do this for the specific accounts that genuinely need it for legacy client compatibility, and prefer upgrading the client library first if that’s a realistic option.
V. Change an Account’s Authentication Method (If Needed)
If you’ve confirmed you actually need mysql_native_password compatibility for a specific account — for example, an older application driver that can’t yet negotiate caching_sha2_password — you can switch it like this:
sudo mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_strong_password';"
Replace your_strong_password with a strong, unique password — never reuse a password across services, and avoid short or predictable values.
Better alternative for most cases: Rather than downgrading the root account, apply this change only to the specific application user that needs it (covered in the next steps), and leave
rooton the stronger default plugin. Changing root’s authentication method broadly increases risk for no real benefit in most setups.
VI. Log Into the MySQL Shell
To start creating a database and user, log in as root:
sudo mysql -u root -p
Enter your root password when prompted. This drops you into the MySQL interactive shell, indicated by the mysql> prompt.
VII. Create a New Database
Inside the MySQL shell, create a database for your application or user:
CREATE DATABASE example_db;
Choose a database name that reflects its actual purpose (app_production, blog_dev, etc.) rather than a generic placeholder, especially if you expect to manage multiple databases on the same server over time.
VIII. Create a New MySQL User
Next, create a dedicated user account rather than sharing root credentials across applications:
CREATE USER 'sam'@'localhost' IDENTIFIED BY 'Str0ng-Unique-Passw0rd!';
Security note: Replace both the username and password with real, strong values before running this in any real environment. A short numeric password like
2114offers essentially no protection against brute-force attempts and should never be used, even temporarily, outside of a fully isolated local test environment.
The 'sam'@'localhost' syntax restricts this user to connections originating from the local machine. If your application connects from a different host, adjust the hostname portion accordingly — for example, 'sam'@'192.168.1.50' for a specific remote host, or 'sam'@'%' to allow connections from any host (only recommended when combined with proper network-level restrictions like a firewall or private network).
IX. Grant Privileges to the New User
With the user and database created, grant the appropriate privileges. For an application that needs full control over just its own database:
GRANT ALL PRIVILEGES ON example_db.* TO 'sam'@'localhost'; FLUSH PRIVILEGES;
The FLUSH PRIVILEGES command reloads MySQL’s internal privilege tables, ensuring the new grant takes effect immediately.
Best practice:
ALL PRIVILEGESgrants full control over the specified database only — not the entire MySQL server — which is a reasonable scope for many application accounts. However, if this user only needs to read and write data (not alter table structure or manage other users), consider a narrower grant instead, such as:GRANT SELECT, INSERT, UPDATE, DELETE ON example_db.* TO 'sam'@'localhost';This follows the principle of least privilege — granting only what’s actually needed reduces the potential damage if this specific account’s credentials are ever compromised.
X. Exit and Verify the New User
Exit the MySQL shell:
exit
Then verify the new account works by logging in with it directly:
mysql -u sam -p example_db
Enter the password you set. If everything is configured correctly, you’ll land in the MySQL shell with access scoped to example_db.
XI. Common MySQL User Management Commands
A few additional commands are useful once your user and database are set up:
List all MySQL users:
SELECT user, host FROM mysql.user;
Change a user’s password:
ALTER USER 'sam'@'localhost' IDENTIFIED BY 'NewStrongerPassword!';
Revoke privileges:
REVOKE ALL PRIVILEGES ON example_db.* FROM 'sam'@'localhost';
Delete a user entirely:
DROP USER 'sam'@'localhost';
Keeping these commands on hand makes it much easier to manage accounts over time as applications, team members, or access requirements change.
XII. Troubleshooting Common Issues
“Access denied for user” errors: Double-check the username, password, and host portion of the account ('sam'@'localhost' vs 'sam'@'%') — a mismatch here is the most common cause of unexpected access denials.
Application can’t connect due to authentication plugin errors: This typically means the client library doesn’t support caching_sha2_password. Either upgrade the client library, or switch just that specific account to mysql_native_password as covered in Step V — not the whole server.
MySQL service won’t start after installation: Check the service logs for specifics:
sudo journalctl -u mysql.service
This will usually point directly to a configuration or permissions issue.
XIII. Backing Up and Restoring Your Database
Once your database and user are in place, it’s worth knowing the basics of backup and restore before you actually need them in an emergency. MySQL includes a built-in utility, mysqldump, for exporting a database to a portable SQL file.
Back up a single database:
mysqldump -u sam -p example_db > example_db_backup.sql
This creates a .sql file containing all the SQL statements needed to recreate the database’s structure and data from scratch.
Restore a database from a backup file:
mysql -u sam -p example_db < example_db_backup.sql
Note: Restoring assumes the target database already exists. If you’re restoring onto a fresh server, create the empty database first with
CREATE DATABASE example_db;before running the restore command.
Back up all databases on the server (requires an account with broader privileges, such as root):
sudo mysqldump -u root -p --all-databases > all_databases_backup.sql
For anything beyond local testing, automate this with a scheduled cron job, and store backups somewhere separate from the database server itself — a local-only backup provides no protection if the server’s disk fails or the instance is compromised entirely.
XIV. Best Practices for Ongoing MySQL Security
A few habits worth adopting once your initial setup is complete:
- Avoid using the root account for day-to-day application connections — always use a scoped, dedicated user instead
- Rotate passwords periodically, especially for accounts with broad privileges
- Restrict remote access by binding MySQL to
127.0.0.1unless remote connections are genuinely required, and pair any remote access with firewall rules limiting which hosts can connect - Keep MySQL updated with
sudo apt update && sudo apt upgrade, since database software is a common target for known-vulnerability exploitation when left outdated - Back up your databases regularly, and test that backups can actually be restored
For a browser-based way to manage users and databases visually instead of through the command line, see our guide on installing phpMyAdmin on Ajenti Control Panel.
XV. Conclusion
You’ve now successfully set up MySQL and created a user on Ubuntu 24.04 — covering installation, running the security hardening script, understanding the trade-offs around authentication plugins, and creating a dedicated database and user scoped to least-privilege access rather than defaulting to root. This foundation sets you up to connect applications securely, manage multiple databases and users cleanly as your projects grow, and avoid some of the common security shortcuts that quick-start guides often skip entirely.
For deeper reference on user management, privileges, and authentication, see the official MySQL documentation.
Frequently Asked Questions
Do I need to run mysql_secure_installation if this is just a local development environment? It’s still recommended, even for local development, since it takes only a couple of minutes and removes genuinely risky defaults like anonymous accounts and the test database — habits that are easy to skip locally and then forget about when the same server configuration gets reused elsewhere.
Should I always switch to mysql_native_password for compatibility? No — only switch the specific account that actually needs it due to a client library limitation. caching_sha2_password is the modern, stronger default, and broadly downgrading authentication (especially for root) reduces security for no real benefit in most setups.
What’s the safest way to allow remote connections to MySQL? Bind MySQL to specific trusted hosts using the hostname portion of the user account (e.g., 'sam'@'192.168.1.50'), combine it with firewall rules restricting which IPs can reach port 3306, and avoid using 'user'@'%' (any host) unless it’s paired with strict network-level controls.
How do I create a user that can connect from any host, and is that safe? Use 'username'@'%' in place of 'username'@'localhost'. It’s only reasonably safe when combined with a strong, unique password and firewall rules that limit which external hosts can actually reach the MySQL port in the first place — on its own, it significantly widens your attack surface.
What’s the difference between GRANT ALL PRIVILEGES and more specific grants like SELECT, INSERT, UPDATE? ALL PRIVILEGES gives a user full control over the specified database, including the ability to alter table structures and manage permissions within it. Narrower grants like SELECT, INSERT, UPDATE, DELETE restrict the account to reading and writing data only, which is often sufficient for application accounts and reduces the impact if those specific credentials are ever compromised.
Related Articles:
How to Disable/Lock a MySQL User Account from CLI?
How to Change MySQL User Authentication Plugin for Password?