MySQL 8.0: Installation, Security, and Basic Management

Install MySQL 8.0

apt update
apt install mysql-server -y
systemctl enable mysql
systemctl start mysql
mysql --version

Secure the Installation

mysql_secure_installation

Wizard prompts (recommended answers):

• Set root password → Yes
• Remove anonymous users → Yes
• Disallow remote root login → Yes
• Remove test database → Yes
• Reload privilege tables → Yes

Connect to MySQL

mysql -u root -p

Essential Admin Commands

-- Create a database
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create a user
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'StrongP@ssw0rd!';

-- Grant permissions
GRANT ALL PRIVILEGES ON myapp.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;

-- List all databases
SHOW DATABASES;

-- List all users
SELECT user, host FROM mysql.user;

-- Check database size (MB)
SELECT table_schema AS 'Database',
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema;

-- Show active connections
SHOW PROCESSLIST;

-- Kill a slow query
KILL QUERY process_id;

Backup a Database

mysqldump -u root -p myapp > myapp_backup_$(date +%Y%m%d).sql

Restore a Database

mysql -u root -p myapp < myapp_backup_20260101.sql

Allow Remote Access (only from trusted IP)

-- Create a user that can connect from a specific IP
CREATE USER 'myuser'@'203.0.113.10' IDENTIFIED BY 'password';
GRANT ALL ON myapp.* TO 'myuser'@'203.0.113.10';

-- Also open the port in UFW
ufw allow from 203.0.113.10 to any port 3306