mysql

MySQL / MariaDB

Administer, optimize, and secure MySQL and MariaDB databases in development and production environments.

When to Use

• You need a mature, widely supported relational database.
• Your stack depends on MySQL-specific features or compatibility (WordPress, Magento, many PHP frameworks).
• You are setting up source-replica replication for read scaling.
• You want to tune InnoDB for high-throughput transactional workloads.

Prerequisites

• Linux server (Debian/Ubuntu or RHEL-based) or Docker.
• Root or sudo access for package installation.
• Familiarity with SQL fundamentals.

Installation and Setup

# Debian / Ubuntu — MySQL 8
sudo apt update
sudo apt install -y mysql-server

# RHEL / Amazon Linux
sudo dnf install -y mysql-server
sudo systemctl enable --now mysqld

# Run the secure installation wizard
sudo mysql_secure_installation
# Prompts: set root password, remove anonymous users, disable remote root, remove test db

# Verify
mysql --version
sudo systemctl status mysql

Initial User and Database Setup

sudo mysql -u root -p

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

-- Create an application user with strong auth
CREATE USER 'myapp'@'%' IDENTIFIED BY 'strong_password_here';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'myapp'@'%';
FLUSH PRIVILEGES;

-- Verify
SHOW GRANTS FOR 'myapp'@'%';

mysql CLI Reference

# Connect
mysql -u myapp -p -h 127.0.0.1 mydb

# Execute a single statement
mysql -u myapp -p -e "SELECT COUNT(*) FROM orders;" mydb

# Import a SQL file
mysql -u myapp -p mydb < schema.sql

# Export query results to CSV
mysql -u myapp -p -e "SELECT * FROM users" mydb \
  | tr '\t' ',' > users.csv

-- Inside the mysql shell
SHOW DATABASES;
USE mydb;
SHOW TABLES;
DESCRIBE users;
SHOW CREATE TABLE users\G
SHOW PROCESSLIST;
SHOW ENGINE INNODB STATUS\G

Configuration Tuning

Edit /etc/mysql/mysql.conf.d/mysqld.cnf (or /etc/my.cnf on RHEL).