# Ubuntu/Debian
apt install mariadb-server
# CentOS/RHEL
dnf install mariadb-server
[mysqld]
# Connexions
bind-address = 0.0.0.0
max_connections = 150
max_user_connections = 50
# Mémoire
innodb_buffer_pool_size = 4G # 50-75% RAM
innodb_buffer_pool_instances = 4 # 1 par Go
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1 # ACID compliant
# Performance
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_io_capacity = 200
# Cache
query_cache_type = 0 # Désactivé pour MySQL 8+
table_open_cache = 2000
thread_cache_size = 8
-- Création utilisateur
CREATE USER 'username'@'localhost'
IDENTIFIED BY 'password';
-- Attribution privilèges
GRANT ALL PRIVILEGES ON database.*
TO 'username'@'localhost';
GRANT SELECT, INSERT ON database.table
TO 'username'@'localhost';
-- Application privilèges
FLUSH PRIVILEGES;
-- Liste utilisateurs
SELECT user, host FROM mysql.user;
-- Révocation privilèges
REVOKE ALL PRIVILEGES ON database.*
FROM 'username'@'localhost';
-- Création base
CREATE DATABASE dbname
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
-- Liste bases
SHOW DATABASES;
-- Taille bases
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;
-- Structure tables
SHOW CREATE TABLE table_name;
DESCRIBE table_name;
¶ Maintenance Tables
-- Analyse tables
ANALYZE TABLE table_name;
-- Optimisation tables
OPTIMIZE TABLE table_name;
-- Réparation tables
REPAIR TABLE table_name;
-- Vérification tables
CHECK TABLE table_name;
# Sauvegarde complète
mysqldump -u root -p --all-databases > backup.sql
# Base spécifique
mysqldump -u root -p database > database_backup.sql
# Table spécifique
mysqldump -u root -p database table > table_backup.sql
# Options utiles
mysqldump --opt --single-transaction \
--skip-lock-tables --events --routines \
--triggers database > backup.sql
#!/bin/bash
# backup_mysql.sh
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
MYSQL_USER="backup_user"
MYSQL_PASSWORD="password"
# Création répertoire
mkdir -p $BACKUP_DIR/$DATE
# Sauvegarde par base
mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -N -e \
"SHOW DATABASES" | while read dbname
do
if [ "$dbname" != "information_schema" ] && \
[ "$dbname" != "performance_schema" ] && \
[ "$dbname" != "mysql" ]
then
mysqldump -u$MYSQL_USER -p$MYSQL_PASSWORD \
--single-transaction \
--routines \
--triggers \
--events \
$dbname | gzip > "$BACKUP_DIR/$DATE/$dbname.sql.gz"
fi
done
# Nettoyage vieux backups (7 jours)
find $BACKUP_DIR -type d -mtime +7 -exec rm -rf {} \;
# Restauration complète
mysql -u root -p < backup.sql
# Base spécifique
mysql -u root -p database < database_backup.sql
# Restauration compressée
gunzip < backup.sql.gz | mysql -u root -p database
-- Activation slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
-- Analyse avec mysqldumpslow
mysqldumpslow -s t /var/log/mysql/slow.log
-- Activation
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES';
-- Requêtes les plus longues
SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration,
SQL_TEXT
FROM performance_schema.events_statements_history_long
ORDER BY TIMER_WAIT DESC
LIMIT 10;
-- Statistiques tables
SELECT object_schema, object_name, count_star,
sum_timer_wait
FROM performance_schema.table_io_waits_summary_by_table
ORDER BY sum_timer_wait DESC;
-- Création index
CREATE INDEX idx_name ON table (column);
CREATE INDEX idx_composite ON table (col1, col2);
-- Analyse utilisation index
SHOW INDEX FROM table;
EXPLAIN SELECT * FROM table WHERE column = 'value';
-- Index non utilisés
SELECT * FROM sys.schema_unused_indexes;
-- Index redondants
SELECT * FROM sys.schema_redundant_indexes;
-- Status InnoDB
SHOW ENGINE INNODB STATUS;
-- Buffer pool info
SELECT pool_id, pool_size, free_buffers, pages_data
FROM information_schema.innodb_buffer_pool_stats;
-- Configuration recommandée
SET GLOBAL innodb_buffer_pool_size = 4294967296; # 4GB
SET GLOBAL innodb_buffer_pool_instances = 4;
#!/bin/bash
# monitor_connections.sh
THRESHOLD=100
CURRENT=$(mysqladmin status | awk '{print $4}')
if [ $CURRENT -gt $THRESHOLD ]; then
echo "ALERTE: $CURRENT connexions actives"
mysql -e "SHOW PROCESSLIST" | \
awk '{print $1,$2,$3,$4,$5,$6}' | \
sort | uniq -c | sort -nr
fi
#!/bin/bash
# monitor_space.sh
mysql -e "
SELECT table_schema,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema
HAVING SUM(data_length + index_length) > 1024*1024*100;" # >100MB
-- Tables les plus volumineuses
SELECT
table_schema as 'Database',
table_name as 'Table',
round(((data_length + index_length) / 1024 / 1024), 2) 'Size (MB)'
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC
LIMIT 10;
-- Statistiques requêtes
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;
-- État cache
SHOW GLOBAL STATUS LIKE '%cache%';
- Désactiver les comptes par défaut
- Utiliser SSL pour les connexions
- Limiter les privilèges utilisateurs
- Audit régulier des permissions
- Configuration adaptée à la RAM
- Index optimisés
- Monitoring actif
- Analyse régulière des logs
¶ Maintenance
- Backups réguliers et testés
- Optimisation périodique des tables
- Surveillance de l'espace disque
- Mise à jour des statistiques
- Schéma des bases
- Procédures de backup/restore
- Configuration serveur
- Procédures d'urgence