Install and configure MySQL or MariaDB on Ubuntu for web applications.

Install MariaDB

sudo apt update
sudo apt install -y mariadb-server mariadb-client
sudo systemctl enable --now mariadb

Run Secure Installation

sudo mysql_secure_installation

Follow the prompts to: set root password, remove anonymous users, disable remote root login, and remove the test database.

Create a Database and User

sudo mysql -u root -p

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

-- Create user with strong password
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'SecurePass123!';

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

Essential MySQL Commands

# Login
mysql -u root -p

# Show databases
SHOW DATABASES;

# Select database
USE myapp;

# Show tables
SHOW TABLES;

# Describe table structure
DESCRIBE users;

# Import SQL file
mysql -u root -p myapp < backup.sql

# Export database
mysqldump -u root -p myapp > myapp_backup.sql

Key Configuration Settings

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

# Recommended settings for a web server
[mysqld]
bind-address            = 127.0.0.1   # Only allow local connections
max_connections         = 150
innodb_buffer_pool_size = 256M        # Set to 50–70% of RAM for DB servers
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/slow.log
long_query_time         = 2

sudo systemctl restart mariadb