MySQL Basics — Installation, Users, Privileges, and Essential Queries

# Ubuntu / Debian
sudo apt update && sudo apt install -y mysql-server
sudo systemctl enable --now mysql

# CentOS / RHEL (MariaDB)
sudo yum install -y mariadb-server
sudo systemctl enable --now mariadb

# Check version
mysql --version

# Run the security wizard (root password, remove anonymous users, etc.)
sudo mysql_secure_installation

# Connect as root (Ubuntu — socket authentication)
sudo mysql -u root

# Connect as root (password authentication)
mysql -u root -p

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

-- Create user
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'StrongPass123!';
CREATE USER 'appuser'@'%' IDENTIFIED BY 'StrongPass123!';  -- Allow remote connections

-- Grant privileges
GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'appuser'@'%';

-- Apply
FLUSH PRIVILEGES;

-- Check users and privileges
SELECT user, host FROM mysql.user;
SHOW GRANTS FOR 'appuser'@'localhost';

-- Revoke privileges / drop user
REVOKE ALL PRIVILEGES ON myapp.* FROM 'appuser'@'localhost';
DROP USER 'appuser'@'localhost';

-- List databases
SHOW DATABASES;

-- Select database
USE myapp;

-- List tables
SHOW TABLES;

-- Table structure
DESCRIBE users;
SHOW CREATE TABLE users;

-- Process list (running queries)
SHOW PROCESSLIST;

-- Check slow queries
SHOW STATUS LIKE 'Slow_queries';
SHOW VARIABLES LIKE 'slow_query%';

CREATE TABLE users (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(255) NOT NULL UNIQUE,
  status     ENUM('active','inactive') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_email (email),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- INSERT
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES
  ('Bob', 'bob@example.com'),
  ('Carol', 'carol@example.com');

-- SELECT
SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC LIMIT 10;
SELECT u.name, COUNT(o.id) AS orders
FROM users u LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id HAVING orders > 0;

-- UPDATE
UPDATE users SET status = 'inactive' WHERE id = 2;

-- DELETE
DELETE FROM users WHERE status = 'inactive' AND created_at < NOW() - INTERVAL 30 DAY;

[mysqld]
# Basic settings
character-set-server = utf8mb4
collation-server     = utf8mb4_unicode_ci

# Connections
max_connections      = 200
connect_timeout      = 10

# InnoDB buffer pool (50–70% of total memory)
innodb_buffer_pool_size = 1G

# Slow query log
slow_query_log       = 1
slow_query_log_file  = /var/log/mysql/slow.log
long_query_time      = 1      # 1 second or longer

# Binary log (for replication and PITR)
log_bin              = /var/log/mysql/mysql-bin
expire_logs_days     = 7

# Apply configuration
sudo systemctl restart mysql

# Verify settings
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"