MySQL is the database behind WordPress, Magento, countless PHP apps and many Node.js and Python projects. Ubuntu’s repositories ship MySQL 8, and installing it takes a few minutes. The parts that trip people up come afterwards: how root logs in, creating users, and remote access. This guide covers all of it.

(Prefer MariaDB? See installing MariaDB on Linux. The commands are almost identical.)

1. Install MySQL Server

sudo apt update
sudo apt install -y mysql-server

The service starts automatically:

systemctl status mysql --no-pager
mysql --version

2. How root logs in: auth_socket

On Ubuntu, the MySQL root account authenticates with the auth_socket plugin: if you’re root on the Linux system, you’re root in MySQL — no password needed, and no password accepted from anyone else:

sudo mysql

This is secure and convenient for administration. It also means tools that log in as root with a password (phpMyAdmin, some installers) will fail. Don’t switch root to a password; create a separate admin user instead (step 4).

3. Run the security script

sudo mysql_secure_installation

It offers to:

  • Enable the VALIDATE PASSWORD component (enforces password strength — fine to enable, choose MEDIUM or STRONG).
  • Remove anonymous users — yes.
  • Disallow remote root login — yes.
  • Remove the test database — yes.
  • Reload privilege tables — yes.

If it asks to set a root password while root uses auth_socket, you can skip it.

4. Create a database and application user

Every application should have its own database and user with access to that database only:

sudo mysql
CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'a-long-random-password';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Test it:

mysql -u appuser -p appdb

Need a human admin account for tools like phpMyAdmin?

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'another-strong-password';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

More on accounts and privileges in MySQL users and GRANT.

5. Key files and paths

Path Purpose
/etc/mysql/mysql.conf.d/mysqld.cnf Server settings
/var/lib/mysql/ Data directory
/var/log/mysql/error.log Error log
/var/run/mysqld/mysqld.sock Local socket

6. Basic tuning for a VPS

MySQL’s defaults are conservative. The setting with the biggest effect is the InnoDB buffer pool — the memory used to cache data and indexes. Edit mysqld.cnf under [mysqld]:

innodb_buffer_pool_size = 1G      # ~50–70% of RAM on a dedicated DB server; less if the web app shares the server
max_connections = 100             # default 151; lower saves memory on small servers

On a 2 GB VPS that also runs PHP and Nginx, 256–512 MB is a reasonable buffer pool. Restart MySQL afterwards:

sudo systemctl restart mysql

More in MySQL performance tuning.

7. Remote access (only if you need it)

MySQL listens on 127.0.0.1 by default, which is what you want when the app runs on the same server. If another server must connect:

  1. Set bind-address = 0.0.0.0 (or the private IP) in mysqld.cnf and restart.
  2. Create a user for the remote host: CREATE USER 'appuser'@'203.0.113.5' IDENTIFIED BY '…'; and grant privileges.
  3. Allow port 3306 only from that IP: sudo ufw allow from 203.0.113.5 to any port 3306 proto tcp.

Never open 3306 to the whole internet. Prefer an SSH tunnel or a VPN for admin access. Full guide: MySQL remote access.

8. Backups

At minimum, a nightly dump:

sudo mysqldump --single-transaction --routines --triggers --all-databases | gzip > /root/mysql-$(date +%F).sql.gz

--single-transaction produces a consistent InnoDB snapshot without locking tables. See mysqldump backup and restore, and copy backups off the server.

Managing the service

sudo systemctl restart mysql
sudo systemctl stop mysql
sudo journalctl -u mysql -n 50
sudo tail -n 50 /var/log/mysql/error.log

Troubleshooting

ERROR 1045 (28000): Access denied for user 'root'@'localhost' — you ran mysql -u root -p as a normal user. Use sudo mysql.

ERROR 2002: Can't connect to local MySQL server through socket — the server isn’t running. Check systemctl status mysql and the error log.

MySQL killed or restarting on a small VPS — the kernel’s OOM killer stopped it because RAM ran out. Lower innodb_buffer_pool_size and max_connections, or add swap.

Forgot the admin password — see resetting the MySQL root password.

Uninstall

sudo apt purge mysql-server mysql-client mysql-common
sudo rm -rf /var/lib/mysql /etc/mysql

The second command deletes every database — back up first.

MySQL is happiest with fast storage and enough RAM for its buffer pool. Our VPS plans use SSD storage and scale up to 40 GB of RAM when your database grows.

  • MySQL
  • Ubuntu
  • Databases