MySQL Installation

MySQL Installation

MySQL is the most widely deployed relational database. On Ubuntu, you can install MySQL 8.0 directly from Ubuntu's repositories. After installation, the mandatory next step is mysql_secure_installation — the default installation has an empty root password and allows anonymous connections, which is unsafe even on a trusted network. This guide covers installation, initial hardening, and creating the databases and users your applications need.

MySQL on Ubuntu

MySQL on Ubuntu components:
  /etc/mysql/mysql.conf.d/mysqld.cnf  ← main server config
  /var/lib/mysql/                     ← data directory (databases stored here)
  /var/log/mysql/error.log            ← error log
  /run/mysqld/mysqld.sock             ← Unix socket (local connections)

  Management commands:
    mysql        — CLI client
    mysqladmin   — admin operations (status, shutdown, etc.)
    mysqldump    — backup tool
    mysqlcheck   — table check/repair

Installing MySQL

sudo apt update
sudo apt install -y mysql-server

# Verify it's running:
sudo systemctl status mysql
mysql --version

mysql --version output

mysql  Ver 8.0.36-Ubuntu 20.04 for Linux on x86_64 ((Ubuntu))

Securing MySQL

# Run the security wizard (do this immediately after install):
sudo mysql_secure_installation

mysql_secure_installation prompts and recommended answers

Would you like to set up VALIDATE PASSWORD component? → Y
Password validation policy level (LOW/MEDIUM/STRONG): → 1 (MEDIUM)
Remove anonymous users? → Y
Disallow root login remotely? → Y
Remove test database and access? → Y
Reload privilege tables now? → Y

# MySQL 8.0 on Ubuntu uses auth_socket for root by default — login with sudo:
sudo mysql    # No password needed when running as root

# Switch root to password authentication (optional):
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'StrongRootPass!';
FLUSH PRIVILEGES;

Basic administration

# Connect to MySQL:
sudo mysql                        # as root via socket auth
mysql -u irfan -p                 # as a regular user, prompt for password
mysql -u irfan -p -h 127.0.0.1   # over TCP (instead of socket)

# Show databases and users:
SHOW DATABASES;
SELECT user, host, plugin FROM mysql.user;

# Check running processes and slow queries:
SHOW PROCESSLIST;
SHOW STATUS LIKE 'Slow_queries';

Creating databases and users

# Create a database and user for a web application:
sudo mysql

SQL commands to create a database and user

CREATE DATABASE myapp_production CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create a user that can only connect from localhost:
CREATE USER 'myapp'@'localhost' IDENTIFIED BY 'SecurePass!42';

-- Grant all privileges on the application database only (not all databases):
GRANT ALL PRIVILEGES ON myapp_production.* TO 'myapp'@'localhost';

-- Grant read-only access (for reporting):
GRANT SELECT ON myapp_production.* TO 'readonly'@'localhost' IDENTIFIED BY 'ReadOnlyPass!';

FLUSH PRIVILEGES;

⚠️ WARNING: Never grant GRANT ALL PRIVILEGES ON *.* TO 'myapp'@'%' — this gives the application user full access to all databases from any host. Create users with the minimum privileges needed and restrict to localhost or a specific IP range.

Conclusion

After installing MySQL: run mysql_secure_installation immediately, create per-application database users with only the permissions they need (never use root for applications), and restrict users to localhost unless you specifically need remote access. Always use utf8mb4 character set when creating databases — it supports the full Unicode range including emoji, whereas older utf8 in MySQL is actually a 3-byte subset that silently truncates 4-byte characters.