Install MySQL on Ubuntu: Users, Backups and Connection Checks

A working MySQL installation needs more than a running service. Your application needs its own account, a database to use and a backup you can restore. This walkthrough sets up those pieces on an Ubuntu server using its distribution packages.

The commands assume a fresh installation and a sudo-capable account. If the machine already runs MySQL or MariaDB, check the installed packages and existing data before changing it. The MySQL version supplied by apt depends on your Ubuntu release and enabled repositories.

Install the server and check the service

sudo apt update
apt-cache policy mysql-server
sudo apt install mysql-server
sudo systemctl enable --now mysql
systemctl status mysql --no-pager

Look for an active service, then connect through the local administrative account:

sudo mysql

SELECT VERSION();
EXIT;

Ubuntu packages commonly configure local root access through socket authentication, so a separate MySQL root password is not necessarily required. If this is an existing installation with different authentication, use its configured administrator credentials. The Ubuntu installation guide describes the package and service setup.

Create an account for the application

Open sudo mysql again. Replace the example password with a unique secret before running the following statements:

CREATE DATABASE appdb CHARACTER SET utf8mb4;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'REPLACE_WITH_A_UNIQUE_PASSWORD';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appuser'@'localhost';
SHOW GRANTS FOR 'appuser'@'localhost';
EXIT;

This account can work with data in appdb; it cannot create tables. Run schema migrations with a separate account that has the required DDL permissions. MySQL applies CREATE USER and GRANT directly, so these commands do not require FLUSH PRIVILEGES. See the account and privilege documentation for additional privileges.

Test the account over the local socket and enter its password when prompted:

mysql --protocol=SOCKET -u appuser -p appdb -e "SELECT CURRENT_USER(), DATABASE();"

Review the initial security settings

sudo mysql_secure_installation

Review the prompts for your installation, including anonymous accounts, remote root access and the test database. The prompts can differ with the authentication configuration; mysql_secure_installation is not a reason to replace working socket authentication with an application password.

If the application runs on this server, keep MySQL local. Check listeners with sudo ss -ltnp. For an application on another machine, configure a private listener address, an account restricted to that client, and a matching firewall rule. Use authenticated TLS or a protected tunnel for the connection; opening port 3306 to everyone is not a prerequisite for installation.

Make a backup and restore it separately

Once appdb contains tables, take a logical backup. Run this in your shell from a directory you can write to:

umask 077
sudo mysqldump --single-transaction --routines --events --triggers appdb > appdb-backup.sql

--single-transaction supports consistent InnoDB data dumps; avoid schema changes during the dump. Nontransactional tables need a different consistency strategy. Check the command's exit status, then restore into a new, empty test database:

sudo mysql -e "CREATE DATABASE appdb_restore CHARACTER SET utf8mb4;"
sudo mysql appdb_restore < appdb-backup.sql

Inspect restored tables and test the data rather than relying on the file's existence. Stored objects with explicit references to appdb need separate review when restoring under another name. The mysqldump reference covers backup options and consistency limits.

If the service or a query is slow

sudo journalctl -u mysql -n 80 --no-pager

Start with the reported error, query plans and resource usage. Memory settings depend on the workload and other services sharing the machine. In particular, do not copy query_cache_size from an old tuning guide: the query cache was removed in MySQL 8.0. A measured change and a repeatable query test are more useful than a fixed “fast configuration” pasted into every server.