Sauvegarde & restauration

# Sauvegarde d'une base
mysqldump -u root -p --single-transaction --routines --triggers --events ma_bdd > backup_$(date +%F).sql
 
# Sauvegarde de toutes les bases
mysqldump -u root -p --all-databases --single-transaction > toutes_bdd_$(date +%F).sql
 
# Compression directe (gain ~90%)
mysqldump -u root -p --single-transaction ma_bdd | gzip > backup_$(date +%F).sql.gz

# Depuis un fichier .sql
mysql -u root -p ma_bdd < backup.sql
 
# Depuis un fichier compressé
gunzip -c backup.sql.gz | mysql -u root -p ma_bdd
 
# Reset complet : drop + create + restore
mysql -u root -p -e "DROP DATABASE IF EXISTS ma_bdd; CREATE DATABASE ma_bdd;"
mysql -u root -p ma_bdd < backup.sql

# Vérifier le positionnement actuel
SHOW MASTER STATUS;
 
# After backup point, export binlog
mysqlbinlog --start-datetime='2024-06-01 00:00:00' /var/log/mysql/binlog.000123 > incremental.sql

# dump logique complet
pg_dump -U postgres -Fc --compress=9 -f backup_$(date +%F).dump ma_bdd
 
# En SQL brut
pg_dump -U postgres -Fp -f backup_$(date +%F).sql ma_bdd
 
# Toutes les bases
pg_dumpall -U postgres > toutes_bdd_$(date +%F).sql

# Avec pg_restore (format custom)
pg_restore -U postgres -d ma_bdd -v backup.dump
 
# En créant la base d'abord
createdb ma_bdd
pg_restore -U postgres -d ma_bdd backup.dump
 
# SQL brut
psql -U postgres ma_bdd < backup.sql

# Configuration nécessaire dans postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'
 
# Restauration à un point
pg_restore -U postgres --no-owner --no-privileges -d ma_bdd backup.dump
# Puis replay des WAL depuis le checkpoint jusqu'au point souhaité

# Backup natif (sans arrêt)
sqlite3 ma_base.db ".backup ma_base.db.bak"
 
# Via commande shell
sqlite3 ma_base.db ".dump" > backup.sql
sqlite3 nouvelle_base.db < backup.sql
 
# Compression
sqlite3 ma_base.db ".dump" | gzip > backup_$(date +%F).sql.gz

# /etc/cron.d/db-backup
0 3 * * * root /opt/scripts/backup_mysql.sh >> /var/log/db-backup.log 2>&1

#!/bin/bash
# /opt/scripts/backup_mysql.sh
DATE=$(date +%F)
RETAIN=14
 
mkdir -p /backup/mysql
 
mysqldump -u backup_user -p"$(cat /etc/mysql/backup_pass)" \
    --single-transaction --routines --triggers \
    ma_bdd | gzip > "/backup/mysql/ma_bdd_${DATE}.sql.gz"
 
# Supprimer les backups anciens
find /backup/mysql -name "ma_bdd_*.sql.gz" -mtime +${RETAIN} -delete
 
echo "Backup terminé: ${DATE}" | mail -s "DB Backup OK" admin@example.com

#!/bin/bash
DATE=$(date +%F)
RETAIN=14
 
mkdir -p /backup/postgres
 
pg_dump -U postgres -Fc --compress=9 -f "/backup/postgres/ma_bdd_${DATE}.dump" ma_bdd
 
find /backup/postgres -name "ma_bdd_*.dump" -mtime +${RETAIN} -delete

-- Lister les utilisateurs MySQL
SELECT user, host FROM mysql.user;
 
-- Lister les droits
SHOW GRANTS FOR 'utilisateur'@'%';