Aller au contenu

Feuille de chaleur MySQL

MySQL - La base de données Open Source la plus populaire au monde

MySQL est un système de gestion de base de données relationnelle (RDBMS) largement utilisé qui utilise le langage de requête structuré (SQL). Il est connu pour sa fiabilité, ses performances et sa facilité d'utilisation, ce qui en fait un choix populaire pour les applications web et les solutions d'entreprise.

Copier toutes les commandes Générer PDF

Sommaire

  • [Installation] (LINK_0)
  • [Commandes de base] (LINK_0)
  • [Opérations de base de données] (LINK_0)
  • [Table des opérations] (__LINK_0___)
  • Types de données
  • [Opérations du CRUD] (LINK_0)
  • [Indexes] (LINK_0)
  • [Contredit] (LINK_0)
  • [Joints] (LINK_0)
  • [Fonctions] (LINK_0)
  • [Procédures confirmées] (LINK_0)
  • [Triggers] (LINK_0)
  • Vues
  • [Gestion de l'utilisateur] (LINK_0)
  • Retour et restauration
  • [Optimisation du rendement] (LINK_0)
  • [Réplication] (LINK_0)
  • [Sécurité] (LINK_0)
  • [Surveiller] (LINK_0)
  • [Meilleures pratiques] (LINK_0)

Installation

Ubuntu/Debian

# Update package index
sudo apt update

# Install MySQL Server
sudo apt install mysql-server

# Secure MySQL installation
sudo mysql_secure_installation

# Start MySQL service
sudo systemctl start mysql

# Enable MySQL to start on boot
sudo systemctl enable mysql

# Check MySQL status
sudo systemctl status mysql

CentOS/RHEL/Fedora

# Install MySQL repository
sudo dnf install mysql-server

# Start and enable MySQL
sudo systemctl start mysqld
sudo systemctl enable mysqld

# Get temporary root password
sudo grep 'temporary password' /var/log/mysqld.log

# Secure installation
sudo mysql_secure_installation
```_

### MACOS
```bash
# Using Homebrew
brew install mysql

# Start MySQL
brew services start mysql

# Connect to MySQL
mysql -u root -p

# Using MySQL installer
# Download from https://dev.mysql.com/downloads/mysql/
```_

### Fenêtres
```bash
# Download MySQL Installer from https://dev.mysql.com/downloads/installer/
# Run the installer and follow the setup wizard

# Start MySQL from Services or Command Prompt
net start mysql

# Connect to MySQL
mysql -u root -p

Coq

# Pull MySQL image
docker pull mysql:8.0

# Run MySQL container
docker run --name mysql-container -e MYSQL_ROOT_PASSWORD=mypassword -p 3306:3306 -d mysql:8.0

# Connect to MySQL in container
docker exec -it mysql-container mysql -u root -p

# Run with persistent data
docker run --name mysql-container \
  -e MYSQL_ROOT_PASSWORD=mypassword \
  -p 3306:3306 \
  -v mysql-data:/var/lib/mysql \
  -d mysql:8.0

Commandes de base

Connexion à MySQL

-- Connect to MySQL server
mysql -u username -p

-- Connect to specific database
mysql -u username -p database_name

-- Connect to remote server
mysql -h hostname -u username -p

-- Connect with specific port
mysql -h hostname -P 3306 -u username -p

-- Connect and execute command
mysql -u username -p -e "SHOW DATABASES;"

Informations de base

-- Show MySQL version
SELECT VERSION();

-- Show current user
SELECT USER();

-- Show current database
SELECT DATABASE();

-- Show current date and time
SELECT NOW();

-- Show system variables
SHOW VARIABLES;

-- Show specific variable
SHOW VARIABLES LIKE 'version';

-- Show status
SHOW STATUS;

-- Show processes
SHOW PROCESSLIST;

Aide et documentation

-- Get help
HELP;

-- Help for specific topic
HELP 'SELECT';

-- Show available help categories
HELP 'contents';

-- Describe table structure
DESCRIBE table_name;
DESC table_name;

-- Show table creation statement
SHOW CREATE TABLE table_name;

Opérations de bases de données

Création de bases de données

-- Create database
CREATE DATABASE database_name;

-- Create database with character set
CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create database if not exists
CREATE DATABASE IF NOT EXISTS database_name;

-- Create database with specific options
CREATE DATABASE database_name
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci
ENCRYPTION = 'Y';

Gestion des bases de données

-- Show all databases
SHOW DATABASES;

-- Use database
USE database_name;

-- Show current database
SELECT DATABASE();

-- Show database creation statement
SHOW CREATE DATABASE database_name;

-- Alter database
ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Drop database
DROP DATABASE database_name;

-- Drop database if exists
DROP DATABASE IF EXISTS database_name;

Informations sur la base de données

-- Show database size
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 tables in database
SHOW TABLES;

-- Show tables with pattern
SHOW TABLES LIKE 'user%';

-- Show table status
SHOW TABLE STATUS;

-- Show table count
SELECT COUNT(*) AS table_count
FROM information_schema.tables
WHERE table_schema = 'database_name';

Tableau des opérations

Création de tableaux

-- Basic table creation
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table with foreign key
CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    user_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Table with multiple constraints
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),
    category_id INT,
    stock_quantity INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category (category_id),
    INDEX idx_price (price),
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- Temporary table
CREATE TEMPORARY TABLE temp_data (
    id INT,
    value VARCHAR(100)
);

Modifier les tableaux

-- Add column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Add column with position
ALTER TABLE users ADD COLUMN middle_name VARCHAR(50) AFTER first_name;

-- Modify column
ALTER TABLE users MODIFY COLUMN email VARCHAR(150) NOT NULL;

-- Change column name and type
ALTER TABLE users CHANGE COLUMN username user_name VARCHAR(60) NOT NULL;

-- Drop column
ALTER TABLE users DROP COLUMN phone;

-- Add primary key
ALTER TABLE table_name ADD PRIMARY KEY (column_name);

-- Drop primary key
ALTER TABLE table_name DROP PRIMARY KEY;

-- Add foreign key
ALTER TABLE posts ADD CONSTRAINT fk_user_id 
FOREIGN KEY (user_id) REFERENCES users(id);

-- Drop foreign key
ALTER TABLE posts DROP FOREIGN KEY fk_user_id;

-- Add index
ALTER TABLE users ADD INDEX idx_email (email);

-- Drop index
ALTER TABLE users DROP INDEX idx_email;

-- Rename table
ALTER TABLE old_table_name RENAME TO new_table_name;

Renseignements sur le tableau

-- Describe table structure
DESCRIBE users;
DESC users;

-- Show table creation statement
SHOW CREATE TABLE users;

-- Show table columns
SHOW COLUMNS FROM users;

-- Show table indexes
SHOW INDEXES FROM users;

-- Show table status
SHOW TABLE STATUS LIKE 'users';

-- Get table size
SELECT 
    table_name AS 'Table',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'database_name'
AND table_name = 'users';

Tables déroulantes

-- Drop table
DROP TABLE table_name;

-- Drop table if exists
DROP TABLE IF EXISTS table_name;

-- Drop multiple tables
DROP TABLE table1, table2, table3;

-- Truncate table (delete all data, keep structure)
TRUNCATE TABLE table_name;

Types de données

Types numériques

-- Integer types
TINYINT     -- 1 byte, -128 to 127
SMALLINT    -- 2 bytes, -32,768 to 32,767
MEDIUMINT   -- 3 bytes, -8,388,608 to 8,388,607
INT         -- 4 bytes, -2,147,483,648 to 2,147,483,647
BIGINT      -- 8 bytes, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

-- Unsigned integers
INT UNSIGNED        -- 0 to 4,294,967,295
BIGINT UNSIGNED     -- 0 to 18,446,744,073,709,551,615

-- Decimal types
DECIMAL(10,2)       -- Fixed-point number
NUMERIC(10,2)       -- Synonym for DECIMAL
FLOAT               -- Single-precision floating-point
DOUBLE              -- Double-precision floating-point

-- Boolean
BOOLEAN             -- Synonym for TINYINT(1)
BOOL                -- Synonym for BOOLEAN

Types de chaînes

-- Fixed-length strings
CHAR(10)            -- Fixed length, 0-255 characters

-- Variable-length strings
VARCHAR(255)        -- Variable length, 0-65,535 characters

-- Text types
TINYTEXT            -- 0-255 characters
TEXT                -- 0-65,535 characters
MEDIUMTEXT          -- 0-16,777,215 characters
LONGTEXT            -- 0-4,294,967,295 characters

-- Binary types
BINARY(10)          -- Fixed-length binary
VARBINARY(255)      -- Variable-length binary
TINYBLOB            -- 0-255 bytes
BLOB                -- 0-65,535 bytes
MEDIUMBLOB          -- 0-16,777,215 bytes
LONGBLOB            -- 0-4,294,967,295 bytes

Types de date et d'heure

-- Date and time types
DATE                -- YYYY-MM-DD
TIME                -- HH:MM:SS
DATETIME            -- YYYY-MM-DD HH:MM:SS
TIMESTAMP           -- YYYY-MM-DD HH:MM:SS (with timezone)
YEAR                -- YYYY

-- Examples
CREATE TABLE events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    event_date DATE,
    event_time TIME,
    event_datetime DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

JSON Type

-- JSON column
CREATE TABLE user_preferences (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    preferences JSON,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Insert JSON data
INSERT INTO user_preferences (user_id, preferences) VALUES
(1, '{"theme": "dark", "language": "en", "notifications": true}');

-- Query JSON data
SELECT preferences->'$.theme' AS theme FROM user_preferences WHERE user_id = 1;
SELECT JSON_EXTRACT(preferences, '$.language') AS language FROM user_preferences;

CRUD Opérations

INSCRIRE Opérations

-- Basic insert
INSERT INTO users (username, email, password) 
VALUES ('john_doe', 'john@example.com', 'hashed_password');

-- Insert multiple rows
INSERT INTO users (username, email, password) VALUES
('alice', 'alice@example.com', 'password1'),
('bob', 'bob@example.com', 'password2'),
('charlie', 'charlie@example.com', 'password3');

-- Insert with all columns
INSERT INTO users VALUES 
(NULL, 'dave', 'dave@example.com', 'password4', NOW());

-- Insert from another table
INSERT INTO users_backup (username, email)
SELECT username, email FROM users WHERE created_at < '2023-01-01';

-- Insert or update (ON DUPLICATE KEY UPDATE)
INSERT INTO users (id, username, email) VALUES (1, 'john_doe', 'newemail@example.com')
ON DUPLICATE KEY UPDATE email = VALUES(email);

-- Insert ignore (skip duplicates)
INSERT IGNORE INTO users (username, email, password) 
VALUES ('existing_user', 'existing@example.com', 'password');

SELECT Opérations

-- Basic select
SELECT * FROM users;

-- Select specific columns
SELECT username, email FROM users;

-- Select with alias
SELECT username AS user_name, email AS user_email FROM users;

-- Select with WHERE clause
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE username = 'john_doe';
SELECT * FROM users WHERE created_at > '2023-01-01';

-- Select with multiple conditions
SELECT * FROM users WHERE username = 'john_doe' AND email = 'john@example.com';
SELECT * FROM users WHERE id IN (1, 2, 3);
SELECT * FROM users WHERE username LIKE 'john%';
SELECT * FROM users WHERE email IS NOT NULL;

-- Select with ORDER BY
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY username ASC, created_at DESC;

-- Select with LIMIT
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;
SELECT * FROM users ORDER BY id LIMIT 5;

-- Select with GROUP BY
SELECT COUNT(*) as user_count FROM users;
SELECT DATE(created_at) as date, COUNT(*) as daily_users 
FROM users GROUP BY DATE(created_at);

-- Select with HAVING
SELECT DATE(created_at) as date, COUNT(*) as daily_users 
FROM users 
GROUP BY DATE(created_at) 
HAVING COUNT(*) > 5;

MISE À JOUR Opérations

-- Basic update
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

-- Update multiple columns
UPDATE users SET 
    email = 'newemail@example.com',
    password = 'new_hashed_password'
WHERE id = 1;

-- Update with conditions
UPDATE users SET password = 'new_password' WHERE username = 'john_doe';

-- Update multiple rows
UPDATE users SET is_active = FALSE WHERE created_at < '2022-01-01';

-- Update with JOIN
UPDATE users u
JOIN user_profiles p ON u.id = p.user_id
SET u.email = p.contact_email
WHERE p.is_primary = TRUE;

-- Update with subquery
UPDATE users SET status = 'premium'
WHERE id IN (SELECT user_id FROM subscriptions WHERE plan = 'premium');

-- Update with CASE
UPDATE users SET 
    status = CASE 
        WHEN created_at > '2023-01-01' THEN 'new'
        WHEN created_at > '2022-01-01' THEN 'regular'
        ELSE 'old'
    END;

DELETE Opérations

-- Basic delete
DELETE FROM users WHERE id = 1;

-- Delete with conditions
DELETE FROM users WHERE username = 'john_doe';
DELETE FROM users WHERE created_at < '2022-01-01';

-- Delete multiple rows
DELETE FROM users WHERE id IN (1, 2, 3);

-- Delete with JOIN
DELETE u FROM users u
JOIN user_profiles p ON u.id = p.user_id
WHERE p.is_deleted = TRUE;

-- Delete with subquery
DELETE FROM users 
WHERE id IN (SELECT user_id FROM inactive_users);

-- Delete all rows (keep table structure)
DELETE FROM users;

-- Truncate table (faster for deleting all rows)
TRUNCATE TABLE users;

Indices

Création d'index

-- Create index on single column
CREATE INDEX idx_username ON users(username);

-- Create index on multiple columns
CREATE INDEX idx_name_email ON users(username, email);

-- Create unique index
CREATE UNIQUE INDEX idx_email ON users(email);

-- Create index with specific length
CREATE INDEX idx_username_partial ON users(username(10));

-- Create index on expression
CREATE INDEX idx_email_domain ON users((SUBSTRING_INDEX(email, '@', -1)));

-- Create fulltext index
CREATE FULLTEXT INDEX idx_content ON posts(title, content);

Gestion des index

-- Show indexes for table
SHOW INDEXES FROM users;

-- Show index usage
SHOW INDEX FROM users;

-- Drop index
DROP INDEX idx_username ON users;

-- Add index using ALTER TABLE
ALTER TABLE users ADD INDEX idx_created_at (created_at);

-- Drop index using ALTER TABLE
ALTER TABLE users DROP INDEX idx_created_at;

-- Analyze index usage
EXPLAIN SELECT * FROM users WHERE username = 'john_doe';

-- Show index statistics
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'database_name';

Types d'index

-- B-Tree index (default)
CREATE INDEX idx_btree ON users(username) USING BTREE;

-- Hash index
CREATE INDEX idx_hash ON users(id) USING HASH;

-- Fulltext index for text search
CREATE FULLTEXT INDEX idx_fulltext ON posts(content);

-- Spatial index for geometric data
CREATE SPATIAL INDEX idx_location ON locations(coordinates);

-- Composite index
CREATE INDEX idx_composite ON orders(customer_id, order_date, status);

-- Partial index with WHERE clause (MySQL 8.0+)
CREATE INDEX idx_active_users ON users(username) WHERE is_active = TRUE;

Contraintes

Clé primaire

-- Add primary key during table creation
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50)
);

-- Add primary key to existing table
ALTER TABLE users ADD PRIMARY KEY (id);

-- Composite primary key
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- Drop primary key
ALTER TABLE users DROP PRIMARY KEY;

Clé étrangère

-- Add foreign key during table creation
CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    title VARCHAR(200),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Add foreign key to existing table
ALTER TABLE posts ADD CONSTRAINT fk_user_id 
FOREIGN KEY (user_id) REFERENCES users(id);

-- Foreign key with actions
ALTER TABLE posts ADD CONSTRAINT fk_user_id 
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

-- Drop foreign key
ALTER TABLE posts DROP FOREIGN KEY fk_user_id;

-- Show foreign keys
SELECT 
    CONSTRAINT_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = 'database_name';

Contrainte unique

-- Add unique constraint during table creation
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

-- Add unique constraint to existing table
ALTER TABLE users ADD CONSTRAINT uk_email UNIQUE (email);

-- Composite unique constraint
ALTER TABLE users ADD CONSTRAINT uk_username_email UNIQUE (username, email);

-- Drop unique constraint
ALTER TABLE users DROP INDEX uk_email;

Vérifier la contrainte

-- Add check constraint during table creation
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    price DECIMAL(10,2) CHECK (price > 0),
    stock_quantity INT CHECK (stock_quantity >= 0)
);

-- Add check constraint to existing table
ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price > 0);

-- Complex check constraint
ALTER TABLE users ADD CONSTRAINT chk_email 
CHECK (email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- Drop check constraint
ALTER TABLE products DROP CHECK chk_price;

Pas de contrainte null

-- Add NOT NULL during table creation
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

-- Add NOT NULL to existing column
ALTER TABLE users MODIFY COLUMN email VARCHAR(100) NOT NULL;

-- Remove NOT NULL constraint
ALTER TABLE users MODIFY COLUMN phone VARCHAR(20) NULL;

Rejoignez

Intégration interne

-- Basic inner join
SELECT u.username, p.title
FROM users u
INNER JOIN posts p ON u.id = p.user_id;

-- Inner join with WHERE clause
SELECT u.username, p.title, p.created_at
FROM users u
INNER JOIN posts p ON u.id = p.user_id
WHERE p.created_at > '2023-01-01';

-- Multiple inner joins
SELECT u.username, p.title, c.name as category
FROM users u
INNER JOIN posts p ON u.id = p.user_id
INNER JOIN categories c ON p.category_id = c.id;

Joindre à gauche

-- Left join (all users, even without posts)
SELECT u.username, p.title
FROM users u
LEFT JOIN posts p ON u.id = p.user_id;

-- Left join with NULL check
SELECT u.username, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username;

-- Left join to find users without posts
SELECT u.username
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE p.id IS NULL;

Droite Joindre

-- Right join (all posts, even if user doesn't exist)
SELECT u.username, p.title
FROM users u
RIGHT JOIN posts p ON u.id = p.user_id;

-- Right join to find orphaned posts
SELECT p.title
FROM users u
RIGHT JOIN posts p ON u.id = p.user_id
WHERE u.id IS NULL;

Pleine adhésion extérieure

-- Full outer join (MySQL doesn't support FULL OUTER JOIN directly)
-- Use UNION of LEFT and RIGHT joins
SELECT u.username, p.title
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
UNION
SELECT u.username, p.title
FROM users u
RIGHT JOIN posts p ON u.id = p.user_id;

Rejoindre

-- Cross join (Cartesian product)
SELECT u.username, c.name
FROM users u
CROSS JOIN categories c;

-- Cross join with WHERE clause
SELECT u.username, c.name
FROM users u
CROSS JOIN categories c
WHERE u.is_active = TRUE;

Se joindre

-- Self join to find users in same city
SELECT u1.username as user1, u2.username as user2, u1.city
FROM users u1
INNER JOIN users u2 ON u1.city = u2.city AND u1.id != u2.id;

-- Self join for hierarchical data
SELECT e.name as employee, m.name as manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Fonctions

Fonctions de chaîne

-- String manipulation
SELECT CONCAT('Hello', ' ', 'World');                    -- Hello World
SELECT CONCAT_WS('-', 'John', 'Doe', '123');            -- John-Doe-123
SELECT LENGTH('Hello World');                            -- 11
SELECT CHAR_LENGTH('Hello World');                       -- 11
SELECT UPPER('hello world');                             -- HELLO WORLD
SELECT LOWER('HELLO WORLD');                             -- hello world
SELECT SUBSTRING('Hello World', 1, 5);                  -- Hello
SELECT LEFT('Hello World', 5);                          -- Hello
SELECT RIGHT('Hello World', 5);                         -- World
SELECT TRIM('  Hello World  ');                         -- Hello World
SELECT LTRIM('  Hello World');                          -- Hello World
SELECT RTRIM('Hello World  ');                          -- Hello World
SELECT REPLACE('Hello World', 'World', 'MySQL');        -- Hello MySQL
SELECT REVERSE('Hello');                                 -- olleH
SELECT REPEAT('Ha', 3);                                  -- HaHaHa
SELECT LPAD('123', 5, '0');                             -- 00123
SELECT RPAD('123', 5, '0');                             -- 12300

-- String search
SELECT LOCATE('World', 'Hello World');                  -- 7
SELECT POSITION('World' IN 'Hello World');              -- 7
SELECT INSTR('Hello World', 'World');                   -- 7
SELECT FIND_IN_SET('b', 'a,b,c,d');                    -- 2

-- Pattern matching
SELECT 'Hello World' LIKE 'Hello%';                     -- 1 (true)
SELECT 'Hello World' REGEXP '^Hello';                   -- 1 (true)

Fonctions numériques

-- Mathematical functions
SELECT ABS(-15);                                         -- 15
SELECT CEIL(4.3);                                        -- 5
SELECT CEILING(4.3);                                     -- 5
SELECT FLOOR(4.7);                                       -- 4
SELECT ROUND(4.567, 2);                                  -- 4.57
SELECT TRUNCATE(4.567, 2);                              -- 4.56
SELECT MOD(10, 3);                                       -- 1
SELECT POW(2, 3);                                        -- 8
SELECT POWER(2, 3);                                      -- 8
SELECT SQRT(16);                                         -- 4
SELECT RAND();                                           -- Random number 0-1
SELECT RAND(123);                                        -- Random with seed

-- Aggregate functions
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT username) FROM users;
SELECT SUM(price) FROM products;
SELECT AVG(price) FROM products;
SELECT MIN(price) FROM products;
SELECT MAX(price) FROM products;
SELECT GROUP_CONCAT(username) FROM users;

Fonctions de la date et de l'heure

-- Current date and time
SELECT NOW();                                            -- Current datetime
SELECT CURDATE();                                        -- Current date
SELECT CURRENT_DATE();                                   -- Current date
SELECT CURTIME();                                        -- Current time
SELECT CURRENT_TIME();                                   -- Current time
SELECT UNIX_TIMESTAMP();                                 -- Unix timestamp
SELECT UTC_TIMESTAMP();                                  -- UTC datetime

-- Date formatting
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');                  -- 2023-12-25
SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y');            -- Monday, December 25, 2023
SELECT TIME_FORMAT(NOW(), '%H:%i:%s');                  -- 14:30:45

-- Date arithmetic
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);                 -- Tomorrow
SELECT DATE_SUB(NOW(), INTERVAL 1 WEEK);                -- Last week
SELECT DATEDIFF('2023-12-25', '2023-12-01');           -- 24
SELECT TIMESTAMPDIFF(HOUR, '2023-12-01 10:00:00', '2023-12-01 15:30:00'); -- 5

-- Date extraction
SELECT YEAR(NOW());                                      -- 2023
SELECT MONTH(NOW());                                     -- 12
SELECT DAY(NOW());                                       -- 25
SELECT HOUR(NOW());                                      -- 14
SELECT MINUTE(NOW());                                    -- 30
SELECT SECOND(NOW());                                    -- 45
SELECT DAYNAME(NOW());                                   -- Monday
SELECT MONTHNAME(NOW());                                 -- December
SELECT DAYOFWEEK(NOW());                                -- 2 (Monday)
SELECT DAYOFYEAR(NOW());                                -- 359
SELECT WEEK(NOW());                                      -- 52

Fonctions conditionnelles

-- IF function
SELECT IF(price > 100, 'Expensive', 'Affordable') as price_category
FROM products;

-- CASE statement
SELECT 
    name,
    price,
    CASE 
        WHEN price < 50 THEN 'Budget'
        WHEN price < 200 THEN 'Mid-range'
        ELSE 'Premium'
    END as category
FROM products;

-- IFNULL and COALESCE
SELECT IFNULL(phone, 'No phone') FROM users;
SELECT COALESCE(phone, email, 'No contact') FROM users;

-- NULLIF
SELECT NULLIF(username, '') FROM users;  -- Returns NULL if username is empty

Fonctions JSON

-- JSON creation
SELECT JSON_OBJECT('name', 'John', 'age', 30);
SELECT JSON_ARRAY('apple', 'banana', 'orange');

-- JSON extraction
SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');  -- John
SELECT '{"name": "John", "age": 30}'->'$.name';                -- John
SELECT '{"name": "John", "age": 30}'->>'$.name';               -- John (unquoted)

-- JSON modification
SELECT JSON_SET('{"name": "John"}', '$.age', 30);
SELECT JSON_INSERT('{"name": "John"}', '$.age', 30);
SELECT JSON_REPLACE('{"name": "John", "age": 25}', '$.age', 30);
SELECT JSON_REMOVE('{"name": "John", "age": 30}', '$.age');

-- JSON search
SELECT JSON_SEARCH('["apple", "banana", "orange"]', 'one', 'banana');
SELECT JSON_CONTAINS('["apple", "banana"]', '"banana"');

Procédures stockées

Création de procédures stockées

-- Simple stored procedure
DELIMITER //
CREATE PROCEDURE GetUserCount()
BEGIN
    SELECT COUNT(*) as user_count FROM users;
END //
DELIMITER ;

-- Stored procedure with parameters
DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

-- Stored procedure with OUT parameter
DELIMITER //
CREATE PROCEDURE GetUserStats(OUT total_users INT, OUT active_users INT)
BEGIN
    SELECT COUNT(*) INTO total_users FROM users;
    SELECT COUNT(*) INTO active_users FROM users WHERE is_active = TRUE;
END //
DELIMITER ;

-- Stored procedure with INOUT parameter
DELIMITER //
CREATE PROCEDURE UpdateUserStatus(INOUT user_id INT, IN new_status VARCHAR(20))
BEGIN
    UPDATE users SET status = new_status WHERE id = user_id;
    SELECT ROW_COUNT() INTO user_id;
END //
DELIMITER ;

Procédures de stockage avancées

-- Stored procedure with variables and conditions
DELIMITER //
CREATE PROCEDURE ProcessUser(IN user_id INT)
BEGIN
    DECLARE user_count INT DEFAULT 0;
    DECLARE user_status VARCHAR(20);

    SELECT COUNT(*) INTO user_count FROM users WHERE id = user_id;

    IF user_count > 0 THEN
        SELECT status INTO user_status FROM users WHERE id = user_id;

        IF user_status = 'pending' THEN
            UPDATE users SET status = 'active' WHERE id = user_id;
            SELECT 'User activated' as message;
        ELSE
            SELECT 'User already processed' as message;
        END IF;
    ELSE
        SELECT 'User not found' as message;
    END IF;
END //
DELIMITER ;

-- Stored procedure with loop
DELIMITER //
CREATE PROCEDURE CreateSampleUsers(IN count INT)
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE i <= count DO
        INSERT INTO users (username, email) 
        VALUES (CONCAT('user', i), CONCAT('user', i, '@example.com'));
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

-- Stored procedure with cursor
DELIMITER //
CREATE PROCEDURE ProcessAllUsers()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE user_id INT;
    DECLARE user_email VARCHAR(100);

    DECLARE user_cursor CURSOR FOR 
        SELECT id, email FROM users WHERE is_active = TRUE;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN user_cursor;

    user_loop: LOOP
        FETCH user_cursor INTO user_id, user_email;

        IF done THEN
            LEAVE user_loop;
        END IF;

        -- Process each user
        UPDATE users SET last_processed = NOW() WHERE id = user_id;
    END LOOP;

    CLOSE user_cursor;
END //
DELIMITER ;

Gestion des procédures stockées

-- Call stored procedure
CALL GetUserCount();
CALL GetUserById(1);

-- Call procedure with OUT parameters
CALL GetUserStats(@total, @active);
SELECT @total, @active;

-- Show stored procedures
SHOW PROCEDURE STATUS;
SHOW PROCEDURE STATUS WHERE Db = 'database_name';

-- Show procedure definition
SHOW CREATE PROCEDURE GetUserCount;

-- Drop stored procedure
DROP PROCEDURE IF EXISTS GetUserCount;

-- Alter stored procedure (MySQL doesn't support ALTER PROCEDURE)
-- You need to DROP and CREATE again

Gestion des erreurs dans les procédures stockées

DELIMITER //
CREATE PROCEDURE SafeUpdateUser(IN user_id INT, IN new_email VARCHAR(100))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    UPDATE users SET email = new_email WHERE id = user_id;

    IF ROW_COUNT() = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'User not found';
    END IF;

    COMMIT;
END //
DELIMITER ;

Déclencheurs

Création de déclencheurs

-- BEFORE INSERT trigger
DELIMITER //
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
    SET NEW.username = LOWER(NEW.username);
END //
DELIMITER ;

-- AFTER INSERT trigger
DELIMITER //
CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit (user_id, action, timestamp)
    VALUES (NEW.id, 'INSERT', NOW());
END //
DELIMITER ;

-- BEFORE UPDATE trigger
DELIMITER //
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();

    IF NEW.email != OLD.email THEN
        SET NEW.email_verified = FALSE;
    END IF;
END //
DELIMITER ;

-- AFTER UPDATE trigger
DELIMITER //
CREATE TRIGGER after_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit (user_id, action, old_values, new_values, timestamp)
    VALUES (
        NEW.id, 
        'UPDATE', 
        JSON_OBJECT('email', OLD.email, 'status', OLD.status),
        JSON_OBJECT('email', NEW.email, 'status', NEW.status),
        NOW()
    );
END //
DELIMITER ;

-- BEFORE DELETE trigger
DELIMITER //
CREATE TRIGGER before_user_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO deleted_users (original_id, username, email, deleted_at)
    VALUES (OLD.id, OLD.username, OLD.email, NOW());
END //
DELIMITER ;

Déclencheurs avancés

-- Trigger with conditions
DELIMITER //
CREATE TRIGGER update_product_stock
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    DECLARE current_stock INT;

    SELECT stock_quantity INTO current_stock 
    FROM products 
    WHERE id = NEW.product_id;

    IF current_stock >= NEW.quantity THEN
        UPDATE products 
        SET stock_quantity = stock_quantity - NEW.quantity 
        WHERE id = NEW.product_id;
    ELSE
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Insufficient stock';
    END IF;
END //
DELIMITER ;

-- Trigger for automatic calculations
DELIMITER //
CREATE TRIGGER calculate_order_total
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    DECLARE total DECIMAL(10,2) DEFAULT 0;

    SELECT SUM(p.price * oi.quantity) INTO total
    FROM order_items oi
    JOIN products p ON oi.product_id = p.id
    WHERE oi.order_id = NEW.id;

    SET NEW.total_amount = IFNULL(total, 0);
END //
DELIMITER ;

Gestion des déclencheurs

-- Show triggers
SHOW TRIGGERS;
SHOW TRIGGERS FROM database_name;
SHOW TRIGGERS LIKE 'user%';

-- Show trigger definition
SHOW CREATE TRIGGER before_user_insert;

-- Drop trigger
DROP TRIGGER IF EXISTS before_user_insert;

-- Get trigger information
SELECT 
    TRIGGER_NAME,
    EVENT_MANIPULATION,
    EVENT_OBJECT_TABLE,
    ACTION_TIMING
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'database_name';

Vues

Création de vues

-- Simple view
CREATE VIEW active_users AS
SELECT id, username, email, created_at
FROM users
WHERE is_active = TRUE;

-- View with JOIN
CREATE VIEW user_posts AS
SELECT 
    u.username,
    u.email,
    p.title,
    p.content,
    p.created_at as post_date
FROM users u
JOIN posts p ON u.id = p.user_id;

-- View with aggregation
CREATE VIEW user_stats AS
SELECT 
    u.id,
    u.username,
    COUNT(p.id) as post_count,
    MAX(p.created_at) as last_post_date
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username;

-- View with complex logic
CREATE VIEW product_summary AS
SELECT 
    p.id,
    p.name,
    p.price,
    c.name as category,
    CASE 
        WHEN p.stock_quantity = 0 THEN 'Out of Stock'
        WHEN p.stock_quantity < 10 THEN 'Low Stock'
        ELSE 'In Stock'
    END as stock_status,
    (p.price * 0.9) as discounted_price
FROM products p
JOIN categories c ON p.category_id = c.id;

Vues à jour

-- Create updatable view
CREATE VIEW user_profiles AS
SELECT id, username, email, first_name, last_name
FROM users
WHERE is_active = TRUE;

-- Update through view
UPDATE user_profiles 
SET email = 'newemail@example.com' 
WHERE id = 1;

-- Insert through view
INSERT INTO user_profiles (username, email, first_name, last_name)
VALUES ('newuser', 'new@example.com', 'New', 'User');

-- Delete through view
DELETE FROM user_profiles WHERE id = 1;

Gestion des vues

-- Show views
SHOW TABLES;  -- Views appear with tables
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- Show view definition
SHOW CREATE VIEW active_users;

-- Describe view structure
DESCRIBE active_users;

-- Alter view
ALTER VIEW active_users AS
SELECT id, username, email, created_at, last_login
FROM users
WHERE is_active = TRUE;

-- Drop view
DROP VIEW IF EXISTS active_users;

-- Get view information
SELECT 
    TABLE_NAME,
    VIEW_DEFINITION,
    IS_UPDATABLE
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'database_name';

Gestion des utilisateurs

Création d'utilisateurs

-- Create user with password
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

-- Create user for any host
CREATE USER 'username'@'%' IDENTIFIED BY 'password';

-- Create user for specific host
CREATE USER 'username'@'192.168.1.100' IDENTIFIED BY 'password';

-- Create user with authentication plugin
CREATE USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

-- Create user with password expiration
CREATE USER 'username'@'localhost' 
IDENTIFIED BY 'password' 
PASSWORD EXPIRE INTERVAL 90 DAY;

Accorder des privilèges

-- Grant all privileges on all databases
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';

-- Grant all privileges on specific database
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

-- Grant specific privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'username'@'localhost';

-- Grant privileges on specific table
GRANT SELECT, UPDATE ON database_name.users TO 'username'@'localhost';

-- Grant specific column privileges
GRANT SELECT (username, email), UPDATE (email) ON database_name.users TO 'username'@'localhost';

-- Grant execute privilege for stored procedures
GRANT EXECUTE ON database_name.* TO 'username'@'localhost';

-- Grant with grant option (user can grant privileges to others)
GRANT SELECT ON database_name.* TO 'username'@'localhost' WITH GRANT OPTION;

-- Apply privilege changes
FLUSH PRIVILEGES;

Gestion des privilèges des utilisateurs

-- Show current user
SELECT USER();

-- Show user privileges
SHOW GRANTS;
SHOW GRANTS FOR 'username'@'localhost';

-- Show all users
SELECT User, Host FROM mysql.user;

-- Show user details
SELECT 
    User, 
    Host, 
    authentication_string,
    password_expired,
    password_lifetime
FROM mysql.user;

-- Revoke privileges
REVOKE INSERT, UPDATE ON database_name.* FROM 'username'@'localhost';
REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'localhost';

-- Change user password
ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';

-- Set password expiration
ALTER USER 'username'@'localhost' PASSWORD EXPIRE;
ALTER USER 'username'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- Lock/unlock user account
ALTER USER 'username'@'localhost' ACCOUNT LOCK;
ALTER USER 'username'@'localhost' ACCOUNT UNLOCK;

-- Drop user
DROP USER 'username'@'localhost';

Rôles (MySQL 8.0+)

-- Create role
CREATE ROLE 'app_read', 'app_write', 'app_admin';

-- Grant privileges to role
GRANT SELECT ON database_name.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON database_name.* TO 'app_write';
GRANT ALL PRIVILEGES ON database_name.* TO 'app_admin';

-- Grant role to user
GRANT 'app_read' TO 'username'@'localhost';
GRANT 'app_read', 'app_write' TO 'username'@'localhost';

-- Set default role
ALTER USER 'username'@'localhost' DEFAULT ROLE 'app_read';

-- Activate role in session
SET ROLE 'app_write';
SET ROLE ALL;

-- Show roles
SHOW GRANTS FOR 'username'@'localhost' USING 'app_write';

-- Drop role
DROP ROLE 'app_read';

Sauvegarde et restauration

sauvegarde mysqldump

# Backup single database
mysqldump -u username -p database_name > backup.sql

# Backup multiple databases
mysqldump -u username -p --databases db1 db2 db3 > backup.sql

# Backup all databases
mysqldump -u username -p --all-databases > all_databases.sql

# Backup with additional options
mysqldump -u username -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  database_name > backup.sql

# Backup specific tables
mysqldump -u username -p database_name table1 table2 > tables_backup.sql

# Backup structure only (no data)
mysqldump -u username -p --no-data database_name > structure.sql

# Backup data only (no structure)
mysqldump -u username -p --no-create-info database_name > data.sql

# Compressed backup
mysqldump -u username -p database_name | gzip > backup.sql.gz

# Remote backup
mysqldump -h remote_host -u username -p database_name > backup.sql

Restauration de sauvegarde

# Restore database
mysql -u username -p database_name < backup.sql

# Restore and create database
mysql -u username -p -e "CREATE DATABASE database_name;"
mysql -u username -p database_name < backup.sql

# Restore compressed backup
gunzip < backup.sql.gz | mysql -u username -p database_name

# Restore from remote
mysql -h remote_host -u username -p database_name < backup.sql

# Restore with progress (using pv)
pv backup.sql | mysql -u username -p database_name

Sauvegarde de la connexion binaire

-- Enable binary logging (in my.cnf)
-- log-bin=mysql-bin
-- server-id=1

-- Show binary logs
SHOW BINARY LOGS;

-- Show current binary log position
SHOW MASTER STATUS;

-- Backup binary logs
-- Use mysqlbinlog utility

# Backup binary logs
mysqlbinlog mysql-bin.000001 > binlog_backup.sql

# Backup multiple binary logs
mysqlbinlog mysql-bin.000001 mysql-bin.000002 > binlog_backup.sql

# Backup with date range
mysqlbinlog --start-datetime="2023-01-01 00:00:00" \
           --stop-datetime="2023-01-02 00:00:00" \
           mysql-bin.000001 > binlog_backup.sql

# Point-in-time recovery
mysqlbinlog --start-position=123456 mysql-bin.000001 | mysql -u username -p

Script de sauvegarde automatisé

#!/bin/bash
# backup_mysql.sh

# Configuration
DB_USER="backup_user"
DB_PASS="backup_password"
DB_NAME="database_name"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_$DATE.sql"

# Create backup directory
mkdir -p $BACKUP_DIR

# Perform backup
mysqldump -u $DB_USER -p$DB_PASS \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  $DB_NAME > $BACKUP_FILE

# Compress backup
gzip $BACKUP_FILE

# Remove old backups (keep last 7 days)
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

echo "Backup completed: ${BACKUP_FILE}.gz"

Optimisation des performances

Optimisation des requêtes

-- Use EXPLAIN to analyze queries
EXPLAIN SELECT * FROM users WHERE username = 'john_doe';
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE username = 'john_doe';

-- Use indexes effectively
-- Bad: Full table scan
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- Good: Use index
SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

-- Use LIMIT for large result sets
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- Use EXISTS instead of IN for subqueries
-- Bad
SELECT * FROM users WHERE id IN (SELECT user_id FROM posts);

-- Good
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM posts p WHERE p.user_id = u.id);

-- Use proper JOIN syntax
-- Bad: Implicit join
SELECT u.username, p.title
FROM users u, posts p
WHERE u.id = p.user_id;

-- Good: Explicit join
SELECT u.username, p.title
FROM users u
INNER JOIN posts p ON u.id = p.user_id;

Optimisation de l'indice

-- Analyze table to update index statistics
ANALYZE TABLE users;

-- Check index usage
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    CARDINALITY,
    SUB_PART
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'database_name'
ORDER BY TABLE_NAME, INDEX_NAME;

-- Find unused indexes
SELECT 
    s.TABLE_SCHEMA,
    s.TABLE_NAME,
    s.INDEX_NAME
FROM information_schema.STATISTICS s
LEFT JOIN information_schema.INDEX_STATISTICS i
    ON s.TABLE_SCHEMA = i.TABLE_SCHEMA
    AND s.TABLE_NAME = i.TABLE_NAME
    AND s.INDEX_NAME = i.INDEX_NAME
WHERE s.TABLE_SCHEMA = 'database_name'
    AND i.INDEX_NAME IS NULL
    AND s.INDEX_NAME != 'PRIMARY';

-- Optimize table
OPTIMIZE TABLE users;

-- Check table for errors
CHECK TABLE users;

-- Repair table if needed
REPAIR TABLE users;

Optimisation de la configuration

-- Show current configuration
SHOW VARIABLES;
SHOW VARIABLES LIKE 'innodb%';
SHOW VARIABLES LIKE 'query_cache%';

-- Important variables to tune
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';      -- Should be 70-80% of RAM
SHOW VARIABLES LIKE 'innodb_log_file_size';         -- 25% of buffer pool size
SHOW VARIABLES LIKE 'max_connections';               -- Based on application needs
SHOW VARIABLES LIKE 'query_cache_size';             -- For read-heavy workloads
SHOW VARIABLES LIKE 'tmp_table_size';               -- For complex queries
SHOW VARIABLES LIKE 'max_heap_table_size';          -- Should match tmp_table_size

-- Show status variables
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW STATUS LIKE 'Qcache_hits';
SHOW STATUS LIKE 'Qcache_inserts';

Surveillance de la performance

-- Show running processes
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- Kill long-running query
KILL QUERY process_id;
KILL CONNECTION process_id;

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Performance Schema queries
-- Top queries by execution time
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000 as avg_time_sec,
    SUM_TIMER_WAIT/1000000000 as total_time_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- Table I/O statistics
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    COUNT_READ,
    COUNT_WRITE,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA = 'database_name'
ORDER BY COUNT_READ + COUNT_WRITE DESC;

Réplication

Installation de la réplication maître-esclave

-- Master configuration (my.cnf)
-- [mysqld]
-- server-id = 1
-- log-bin = mysql-bin
-- binlog-do-db = database_name

-- Create replication user on master
CREATE USER 'replication'@'%' IDENTIFIED BY 'replication_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;

-- Get master status
SHOW MASTER STATUS;
-- Note the File and Position values

-- Slave configuration (my.cnf)
-- [mysqld]
-- server-id = 2
-- relay-log = mysql-relay-bin

-- Configure slave
CHANGE MASTER TO
    MASTER_HOST = 'master_ip',
    MASTER_USER = 'replication',
    MASTER_PASSWORD = 'replication_password',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 154;

-- Start slave
START SLAVE;

-- Check slave status
SHOW SLAVE STATUS\G

-- Stop slave
STOP SLAVE;

Réplication Master-Master

-- Server 1 configuration (my.cnf)
-- [mysqld]
-- server-id = 1
-- log-bin = mysql-bin
-- auto-increment-increment = 2
-- auto-increment-offset = 1

-- Server 2 configuration (my.cnf)
-- [mysqld]
-- server-id = 2
-- log-bin = mysql-bin
-- auto-increment-increment = 2
-- auto-increment-offset = 2

-- Configure each server as slave to the other
-- On Server 1:
CHANGE MASTER TO
    MASTER_HOST = 'server2_ip',
    MASTER_USER = 'replication',
    MASTER_PASSWORD = 'replication_password',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 154;

-- On Server 2:
CHANGE MASTER TO
    MASTER_HOST = 'server1_ip',
    MASTER_USER = 'replication',
    MASTER_PASSWORD = 'replication_password',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 154;

Surveillance des réplications

-- Check replication status
SHOW SLAVE STATUS\G

-- Important fields to monitor:
-- Slave_IO_Running: Should be 'Yes'
-- Slave_SQL_Running: Should be 'Yes'
-- Seconds_Behind_Master: Replication lag
-- Last_Error: Any replication errors

-- Check master status
SHOW MASTER STATUS;

-- Show binary logs
SHOW BINARY LOGS;

-- Reset slave
STOP SLAVE;
RESET SLAVE;

-- Skip replication error
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

Sécurité

SSL/TLS Configuration

-- Check SSL status
SHOW VARIABLES LIKE 'have_ssl';
SHOW STATUS LIKE 'Ssl_cipher';

-- Require SSL for user
ALTER USER 'username'@'%' REQUIRE SSL;

-- Require specific SSL options
ALTER USER 'username'@'%' REQUIRE X509;
ALTER USER 'username'@'%' REQUIRE CIPHER 'AES256-SHA';

-- Connect with SSL
-- mysql --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem

Chiffrement des données

-- Enable encryption at rest (my.cnf)
-- [mysqld]
-- early-plugin-load=keyring_file.so
-- keyring_file_data=/var/lib/mysql-keyring/keyring

-- Create encrypted table
CREATE TABLE sensitive_data (
    id INT PRIMARY KEY AUTO_INCREMENT,
    data TEXT
) ENCRYPTION='Y';

-- Encrypt existing table
ALTER TABLE users ENCRYPTION='Y';

-- Check encryption status
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    CREATE_OPTIONS
FROM information_schema.TABLES
WHERE CREATE_OPTIONS LIKE '%ENCRYPTION%';

Comptabilisation des audits

-- Enable audit log plugin
-- INSTALL PLUGIN audit_log SONAME 'audit_log.so';

-- Configure audit log (my.cnf)
-- [mysqld]
-- plugin-load-add=audit_log.so
-- audit_log_file=/var/log/mysql/audit.log
-- audit_log_format=JSON

-- Show audit log status
SHOW VARIABLES LIKE 'audit_log%';

-- Filter audit events
SET GLOBAL audit_log_include_accounts = 'user1@localhost,user2@%';
SET GLOBAL audit_log_exclude_accounts = 'root@localhost';

Pratiques exemplaires en matière de sécurité

-- Remove anonymous users
DELETE FROM mysql.user WHERE User='';

-- Remove test database
DROP DATABASE IF EXISTS test;

-- Disable remote root login
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

-- Set strong passwords
ALTER USER 'root'@'localhost' IDENTIFIED BY 'strong_password';

-- Limit user privileges
-- Don't grant ALL PRIVILEGES unless necessary
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'localhost';

-- Use specific hosts instead of '%'
CREATE USER 'app_user'@'192.168.1.100' IDENTIFIED BY 'password';

-- Enable password validation
-- INSTALL COMPONENT 'file://component_validate_password';
SHOW VARIABLES LIKE 'validate_password%';

-- Set password policy
SET GLOBAL validate_password.policy = 'STRONG';
SET GLOBAL validate_password.length = 12;

Surveillance

Surveillance de la performance

-- Enable Performance Schema
-- [mysqld]
-- performance_schema = ON

-- Monitor current connections
SELECT 
    PROCESSLIST_ID,
    PROCESSLIST_USER,
    PROCESSLIST_HOST,
    PROCESSLIST_DB,
    PROCESSLIST_COMMAND,
    PROCESSLIST_TIME,
    PROCESSLIST_STATE,
    PROCESSLIST_INFO
FROM performance_schema.processlist
WHERE PROCESSLIST_COMMAND != 'Sleep';

-- Monitor table locks
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    LOCK_TYPE,
    LOCK_DURATION,
    LOCK_STATUS
FROM performance_schema.metadata_locks
WHERE OBJECT_TYPE = 'TABLE';

-- Monitor memory usage
SELECT 
    EVENT_NAME,
    CURRENT_COUNT_USED,
    CURRENT_NUMBER_OF_BYTES_USED,
    LOW_COUNT_USED,
    HIGH_COUNT_USED
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/innodb%'
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC;

Surveillance du système

# Monitor MySQL processes
ps aux | grep mysql

# Monitor MySQL connections
netstat -an | grep :3306

# Monitor MySQL files
lsof -p $(pgrep mysqld)

# Monitor disk usage
df -h /var/lib/mysql

# Monitor MySQL logs
tail -f /var/log/mysql/error.log
tail -f /var/log/mysql/slow.log

# Monitor system resources
top -p $(pgrep mysqld)
iostat -x 1

Scénario de suivi automatisé

#!/bin/bash
# mysql_monitor.sh

MYSQL_USER="monitor_user"
MYSQL_PASS="monitor_password"
THRESHOLD_CONNECTIONS=80
THRESHOLD_SLOW_QUERIES=10

# Check connection count
CONNECTIONS=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW STATUS LIKE 'Threads_connected';" | awk 'NR==2 {print $2}')

if [ $CONNECTIONS -gt $THRESHOLD_CONNECTIONS ]; then
    echo "WARNING: High connection count: $CONNECTIONS"
fi

# Check slow queries
SLOW_QUERIES=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW STATUS LIKE 'Slow_queries';" | awk 'NR==2 {print $2}')

if [ $SLOW_QUERIES -gt $THRESHOLD_SLOW_QUERIES ]; then
    echo "WARNING: High slow query count: $SLOW_QUERIES"
fi

# Check replication lag (if slave)
LAG=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master" | awk '{print $2}')

if [ ! -z "$LAG" ] && [ $LAG -gt 60 ]; then
    echo "WARNING: Replication lag: $LAG seconds"
fi

echo "MySQL monitoring completed at $(date)"

Meilleures pratiques

Conception de la base de données

-- Use appropriate data types
-- Bad: VARCHAR(255) for everything
CREATE TABLE users (
    id VARCHAR(255),
    age VARCHAR(255),
    is_active VARCHAR(255)
);

-- Good: Specific data types
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    age TINYINT UNSIGNED,
    is_active BOOLEAN DEFAULT TRUE
);

-- Normalize your database
-- Avoid redundant data
-- Use foreign keys to maintain referential integrity

-- Use meaningful names
-- Bad
CREATE TABLE u (
    i INT,
    n VARCHAR(50),
    e VARCHAR(100)
);

-- Good
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

-- Add appropriate indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_created_at ON posts(created_at);

-- Use constraints to ensure data integrity
ALTER TABLE posts ADD CONSTRAINT fk_posts_user_id 
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE products ADD CONSTRAINT chk_price 
CHECK (price > 0);

Demander les meilleures pratiques

-- Use prepared statements to prevent SQL injection
PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
SET @user_id = 1;
EXECUTE stmt USING @user_id;
DEALLOCATE PREPARE stmt;

-- Use LIMIT for large result sets
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- Use specific columns instead of SELECT *
-- Bad
SELECT * FROM users;

-- Good
SELECT id, username, email FROM users;

-- Use EXISTS instead of IN for better performance
-- Bad
SELECT * FROM users WHERE id IN (SELECT user_id FROM posts);

-- Good
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM posts p WHERE p.user_id = u.id);

-- Use UNION ALL instead of UNION when duplicates are acceptable
SELECT username FROM active_users
UNION ALL
SELECT username FROM inactive_users;

-- Use proper JOIN syntax
SELECT u.username, p.title
FROM users u
INNER JOIN posts p ON u.id = p.user_id
WHERE u.is_active = TRUE;

Meilleures pratiques en matière de rendement

-- Regular maintenance
ANALYZE TABLE users;
OPTIMIZE TABLE users;
CHECK TABLE users;

-- Monitor slow queries
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- Use connection pooling in applications
-- Configure appropriate connection limits

-- Partition large tables
CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

-- Use read replicas for read-heavy workloads
-- Separate read and write operations

-- Cache frequently accessed data
-- Use application-level caching (Redis, Memcached)
-- Use MySQL query cache for repeated queries

Pratiques exemplaires en matière de sécurité

-- Use least privilege principle
-- Grant only necessary permissions
GRANT SELECT, INSERT, UPDATE ON app_db.users TO 'app_user'@'localhost';

-- Use strong passwords
-- Enable password validation plugin
-- Set password expiration policies

-- Enable SSL/TLS
-- Encrypt sensitive data
-- Use audit logging

-- Regular security updates
-- Monitor for suspicious activity
-- Backup encryption keys securely

-- Validate input in application layer
-- Use prepared statements
-- Escape special characters

Les meilleures pratiques de sauvegarde

# Regular automated backups
# Full backup daily
mysqldump -u backup_user -p --single-transaction --all-databases > full_backup.sql

# Incremental backup using binary logs
mysqlbinlog mysql-bin.000001 > incremental_backup.sql

# Test restore procedures regularly
# Store backups in multiple locations
# Encrypt backup files
# Document recovery procedures

# Monitor backup success
# Set up alerts for backup failures
# Verify backup integrity

Résumé

MySQL est un système de gestion de base de données relationnelle robuste et polyvalent qui alimente des millions d'applications dans le monde entier. Cette feuille de triche complète couvre les opérations essentielles MySQL de l'installation de base à des sujets avancés comme la réplication et l'optimisation des performances.

Principales forces: - Reliabilité: Expérience éprouvée dans les environnements de production - Performance: Optimisé pour les applications de haute performance - Scalabilité: Prise en charge de diverses stratégies d'échelle, y compris la réplication et la partition - Flexibilité: fonctionne bien avec différents langages et cadres de programmation - Communauté: grande communauté et grande documentation

Cas de la meilleure utilisation: - Applications Web et systèmes de gestion du contenu - Plates-formes de commerce électronique - Entreposage et analyse des données - Applications d'entreprise - Environnements de développement et d'essai

** Considérations importantes :** - Un entretien et une surveillance réguliers sont essentiels pour une performance optimale - Une bonne stratégie d'indexation est essentielle pour la performance des requêtes - La configuration de sécurité doit être mise en œuvre dès le début - Les procédures de sauvegarde et de récupération doivent être testées régulièrement

En suivant les pratiques et les techniques décrites dans cette feuille de triche, vous pouvez concevoir, mettre en œuvre et maintenir des bases de données MySQL sécurisées, performantes et fiables.