# Dump a single database
mysqldump -u root -p myapp_db > myapp_db_backup.sql

# Dump with a timestamp in the filename
mysqldump -u root -p myapp_db > myapp_db_$(date +%Y%m%d_%H%M%S).sql

# Dump multiple databases
mysqldump -u root -p --databases myapp_db analytics_db > multi_db.sql

# Dump all databases
mysqldump -u root -p --all-databases > all_databases.sql

# --single-transaction: consistent snapshot of InnoDB tables without locking
# This is the most important option for live InnoDB backups
mysqldump -u backup_user -p   --single-transaction   --quick   myapp_db > myapp_db.sql

# --routines: include stored procedures and functions
# --events: include scheduled events
# --triggers: include triggers (included by default, but explicit is clearer)
mysqldump -u backup_user -p   --single-transaction   --routines   --events   --triggers   myapp_db > myapp_db_full.sql

# --quick: fetch rows one by one instead of buffering entire table in RAM
# Essential for large tables to avoid memory exhaustion

# --compress: compress data between client and server
mysqldump -u backup_user -p --compress --single-transaction myapp_db   | gzip > myapp_db.sql.gz

# --master-data=2: include CHANGE MASTER TO comment (for replication setup)
# Also records the binary log position at backup time
mysqldump -u root -p   --single-transaction   --master-data=2   myapp_db > myapp_db_with_binlog_pos.sql

# Dump specific tables from a database
mysqldump -u backup_user -p myapp_db orders customers products   --single-transaction > partial_backup.sql

# Dump a table with a WHERE filter (e.g. last 30 days only)
mysqldump -u backup_user -p myapp_db orders   --where="order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)"   --single-transaction > recent_orders.sql

# Dump only the structure, no data
mysqldump -u backup_user -p --no-data myapp_db > schema_only.sql

# Dump only the data, no CREATE TABLE statements
mysqldump -u backup_user -p --no-create-info myapp_db > data_only.sql

# Restore a full database dump
mysql -u root -p myapp_db < myapp_db_backup.sql

# Restore from a compressed dump
gunzip < myapp_db.sql.gz | mysql -u root -p myapp_db

# Create the target database first if it doesn't exist
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS myapp_db"
mysql -u root -p myapp_db < myapp_db_backup.sql

# Restore a dump that includes CREATE DATABASE statements
mysql -u root -p < all_databases.sql

# Monitor restore progress (estimate based on file size)
pv myapp_db_backup.sql | mysql -u root -p myapp_db

# Enable binary logging in my.cnf
# [mysqld]
# log_bin = /var/log/mysql/mysql-bin
# binlog_format = ROW
# expire_logs_days = 7   (or binlog_expire_logs_seconds = 604800)
# server_id = 1

# Check if binary logging is enabled
# SHOW VARIABLES LIKE 'log_bin';

# List current binary log files
# SHOW BINARY LOGS;

# Find the binary log position at dump time (from --master-data=2 dump)
# Look for a comment like:
# -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000042', MASTER_LOG_POS=154;

# Point-in-time recovery: restore full dump, then replay binary logs

# Step 1: Restore the most recent full dump
mysql -u root -p myapp_db < myapp_db_full.sql

# Step 2: Apply binary log events up to the point of failure
# (or up to just before a bad DELETE/DROP)
mysqlbinlog   --start-position=154   --stop-datetime="2024-07-03 14:30:00"   /var/log/mysql/mysql-bin.000042   /var/log/mysql/mysql-bin.000043   | mysql -u root -p myapp_db

# To skip a specific bad event (e.g. an accidental DROP TABLE)
# Use --stop-position to stop just before it, then --start-position to skip past it
mysqlbinlog --start-position=154 --stop-position=9821 mysql-bin.000042   | mysql -u root -p myapp_db
mysqlbinlog --start-position=10045 mysql-bin.000042   | mysql -u root -p myapp_db

# mysqlpump: parallel version of mysqldump (included with MySQL 5.7+)
mysqlpump   --user=backup_user   --password   --default-parallelism=4   --add-drop-database   --databases myapp_db   > myapp_pump.sql

# Compress output
mysqlpump   --compress-output=LZ4   --databases myapp_db   > myapp_pump.lz4

# Decompress LZ4
mysqlpump --uncompress < myapp_pump.lz4 | mysql -u root -p myapp_db

# MySQL Shell provides the fastest logical backup tool
# Install MySQL Shell: https://dev.mysql.com/doc/mysql-shell/en/

# Start MySQL Shell
mysqlsh --user=root --password

# Inside MySQL Shell:
# Dump entire instance (all databases)
# util.dumpInstance('/backup/full_dump', {threads: 8, compression: 'zstd'})

# Dump a specific database
# util.dumpSchemas(['myapp_db'], '/backup/myapp_dump', {threads: 8})

# Dump specific tables
# util.dumpTables('myapp_db', ['orders', 'customers'], '/backup/partial')

# Restore from MySQL Shell dump
# util.loadDump('/backup/full_dump', {threads: 8, resetProgress: true})

# Install Percona XtraBackup
# apt install percona-xtrabackup-80  (for MySQL 8.0)

# Full backup
xtrabackup   --backup   --target-dir=/backup/full   --user=backup_user   --password=BackupP@ss

# Prepare the backup (applies redo logs to make it consistent)
xtrabackup --prepare --target-dir=/backup/full

# Restore (MySQL must be stopped)
systemctl stop mysql
rsync -av --delete /backup/full/ /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

# Incremental backup (only changes since last backup)
# Step 1: Take a full backup first
xtrabackup --backup --target-dir=/backup/full   --user=backup_user --password=BackupP@ss

# Step 2: Incremental backup (stores only changes)
xtrabackup --backup   --target-dir=/backup/inc1   --incremental-basedir=/backup/full   --user=backup_user --password=BackupP@ss

# Step 3: Another incremental on top of inc1
xtrabackup --backup   --target-dir=/backup/inc2   --incremental-basedir=/backup/inc1   --user=backup_user --password=BackupP@ss

# Prepare full backup, then apply incrementals in order
xtrabackup --prepare --apply-log-only --target-dir=/backup/full
xtrabackup --prepare --apply-log-only --target-dir=/backup/full --incremental-dir=/backup/inc1
xtrabackup --prepare --target-dir=/backup/full --incremental-dir=/backup/inc2
# Now /backup/full is a fully consistent point-in-time snapshot

# Verify a mysqldump file is valid SQL (syntax check, no restore)
mysqlcheck --check-only-changed --all-databases -u root -p

# Actually test the restore on a separate server or container
# This is the only real test
docker run --name mysql-restore-test   -e MYSQL_ROOT_PASSWORD=testpass   -d mysql:8.0

docker exec -i mysql-restore-test   mysql -uroot -ptestpass < myapp_db_backup.sql

# Verify data integrity after restore
docker exec mysql-restore-test   mysql -uroot -ptestpass -e "
    SELECT TABLE_NAME, TABLE_ROWS
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = 'myapp_db'
    ORDER BY TABLE_NAME;"

# Compare row counts between source and restored database

#!/bin/bash
# /usr/local/bin/mysql_backup.sh

BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_USER="backup_agent"
DB_PASS="BackupP@ss"
DATABASES="myapp_db analytics_db"
RETENTION_DAYS=7

mkdir -p "$BACKUP_DIR"

for DB in $DATABASES; do
  FILENAME="$BACKUP_DIR/${DB}_${DATE}.sql.gz"

  mysqldump     -u "$DB_USER"     -p"$DB_PASS"     --single-transaction     --routines     --events     --triggers     "$DB"     | gzip > "$FILENAME"

  if [ $? -eq 0 ]; then
    echo "$(date): Backed up $DB to $FILENAME" >> /var/log/mysql_backup.log
  else
    echo "$(date): FAILED backup of $DB" >> /var/log/mysql_backup.log
    exit 1
  fi
done

# Remove backups older than RETENTION_DAYS
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +"$RETENTION_DAYS" -delete

echo "$(date): Cleanup complete" >> /var/log/mysql_backup.log

# Add to crontab (run at 2 AM daily)
# crontab -e
# 0 2 * * * /usr/local/bin/mysql_backup.sh