MySQL Essentials

calendarDec 12, 2024 · 3 min read · database-knowhow mysql sql  ·
Share on: linkedincopy

MySQL commands, optimization, and Docker setup.

Docker Setup

Docker Run

 1# Run MySQL
 2docker run -d \
 3  --name mysql \
 4  -e MYSQL_ROOT_PASSWORD=rootpassword \
 5  -e MYSQL_DATABASE=mydb \
 6  -e MYSQL_USER=myuser \
 7  -e MYSQL_PASSWORD=password \
 8  -v mysql-data:/var/lib/mysql \
 9  -p 3306:3306 \
10  mysql:8
11
12# Connect
13docker exec -it mysql mysql -u myuser -p

Docker Compose

 1version: '3.8'
 2
 3services:
 4  mysql:
 5    image: mysql:8
 6    container_name: mysql
 7    environment:
 8      MYSQL_ROOT_PASSWORD: rootpassword
 9      MYSQL_DATABASE: mydb
10      MYSQL_USER: myuser
11      MYSQL_PASSWORD: password
12    volumes:
13      - mysql-data:/var/lib/mysql
14      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
15    ports:
16      - "3306:3306"
17    command: --default-authentication-plugin=mysql_native_password
18    healthcheck:
19      test: ["CMD", "mysqladmin", "ping", "-h", "localhost"]
20      interval: 10s
21      timeout: 5s
22      retries: 5
23    restart: unless-stopped
24
25  phpmyadmin:
26    image: phpmyadmin:latest
27    container_name: phpmyadmin
28    environment:
29      PMA_HOST: mysql
30      PMA_USER: myuser
31      PMA_PASSWORD: password
32    ports:
33      - "8080:80"
34    depends_on:
35      - mysql
36    restart: unless-stopped
37
38volumes:
39  mysql-data:

Basic Commands

 1# Connect
 2mysql -h localhost -u myuser -p
 3
 4# Show databases
 5SHOW DATABASES;
 6
 7# Use database
 8USE mydb;
 9
10# Show tables
11SHOW TABLES;
12
13# Describe table
14DESCRIBE users;
15SHOW CREATE TABLE users;
16
17# Show indexes
18SHOW INDEX FROM users;
19
20# Show processlist
21SHOW PROCESSLIST;
22
23# Show variables
24SHOW VARIABLES LIKE '%timeout%';
25
26# Export database
27mysqldump -u myuser -p mydb > backup.sql
28
29# Import database
30mysql -u myuser -p mydb < backup.sql

MySQL-Specific Features

AUTO_INCREMENT

 1CREATE TABLE users (
 2    id INT AUTO_INCREMENT PRIMARY KEY,
 3    username VARCHAR(50) NOT NULL,
 4    email VARCHAR(100) NOT NULL
 5);
 6
 7-- Get last inserted ID
 8SELECT LAST_INSERT_ID();
 9
10-- Reset AUTO_INCREMENT
11ALTER TABLE users AUTO_INCREMENT = 1;

ENUM Type

1CREATE TABLE orders (
2    id INT AUTO_INCREMENT PRIMARY KEY,
3    status ENUM('pending', 'processing', 'completed', 'cancelled') DEFAULT 'pending',
4    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
5);

Full-Text Search

 1-- Create table with FULLTEXT index
 2CREATE TABLE articles (
 3    id INT AUTO_INCREMENT PRIMARY KEY,
 4    title VARCHAR(200),
 5    content TEXT,
 6    FULLTEXT INDEX idx_search (title, content)
 7) ENGINE=InnoDB;
 8
 9-- Search
10SELECT *, MATCH(title, content) AGAINST('mysql database' IN NATURAL LANGUAGE MODE) AS relevance
11FROM articles
12WHERE MATCH(title, content) AGAINST('mysql database' IN NATURAL LANGUAGE MODE)
13ORDER BY relevance DESC;
14
15-- Boolean mode
16SELECT * FROM articles
17WHERE MATCH(title, content) AGAINST('+mysql -oracle' IN BOOLEAN MODE);

Stored Procedures

 1DELIMITER //
 2
 3CREATE PROCEDURE GetUserOrders(IN userId INT)
 4BEGIN
 5    SELECT * FROM orders WHERE user_id = userId;
 6END //
 7
 8DELIMITER ;
 9
10-- Call procedure
11CALL GetUserOrders(1);
12
13-- Procedure with OUT parameter
14DELIMITER //
15
16CREATE PROCEDURE GetOrderCount(IN userId INT, OUT orderCount INT)
17BEGIN
18    SELECT COUNT(*) INTO orderCount FROM orders WHERE user_id = userId;
19END //
20
21DELIMITER ;
22
23-- Call
24CALL GetOrderCount(1, @count);
25SELECT @count;

Triggers

 1DELIMITER //
 2
 3CREATE TRIGGER before_user_update
 4BEFORE UPDATE ON users
 5FOR EACH ROW
 6BEGIN
 7    SET NEW.updated_at = CURRENT_TIMESTAMP;
 8END //
 9
10DELIMITER ;

Optimization

 1-- Analyze table
 2ANALYZE TABLE users;
 3
 4-- Optimize table
 5OPTIMIZE TABLE users;
 6
 7-- Check table
 8CHECK TABLE users;
 9
10-- Repair table
11REPAIR TABLE users;
12
13-- Show table status
14SHOW TABLE STATUS LIKE 'users';
15
16-- Explain query
17EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
18EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = 'test@example.com';

Indexes

 1-- Create index
 2CREATE INDEX idx_email ON users(email);
 3
 4-- Composite index
 5CREATE INDEX idx_name_email ON users(last_name, first_name, email);
 6
 7-- Unique index
 8CREATE UNIQUE INDEX idx_username ON users(username);
 9
10-- Full-text index
11CREATE FULLTEXT INDEX idx_content ON articles(content);
12
13-- Drop index
14DROP INDEX idx_email ON users;
15
16-- Show indexes
17SHOW INDEX FROM users;

Related Snippets