mysqldump -u root -p --all-databases --single-transaction > full_backup.sql

# Backing Up a Single Database
mysqldump -u root -p --single-transaction mydb > mydb_backup.sql

# Back up the specified table
mysqldump -u root -p --single-transaction mydb users orders > tables_backup.sql

# Back up only the table structure
mysqldump -u root -p --no-data mydb > mydb_schema.sql

# Back up only the data
mysqldump -u root -p --no-create-info mydb > mydb_data.sql

# Pattern1：Full Backup（All Libraries）
mysqldump -u root -p --all-databases \
  --single-transaction --routines --triggers \
  > full_backup_$(date +%Y%m%d).sql

# Pattern2：Single-Database Backup
mysqldump -u root -p --single-transaction mydb > mydb.sql

# Pattern3：Single-Table Backup
mysqldump -u root -p --single-transaction mydb users > users.sql

# Pattern4：Structure Only
mysqldump -u root -p --no-data mydb > schema.sql

# Pattern5：Data Only
mysqldump -u root -p --no-create-info mydb > data.sql

mysql -u root -p mydb < mydb_backup.sql

gunzip < mydb_backup.sql.gz | mysql -u root -p mydb

USE mydb;
SOURCE /var/backups/mysql/mydb_backup.sql;

# Steps1：Create the target database
mysql -u root -p -e "CREATE DATABASE mydb_restore;"

# Steps2：Restore Data
mysql -u root -p mydb_restore < mydb_backup.sql

# Steps3：Number of rows to verify
mysql -u root -p -e "SELECT COUNT(*) FROM mydb_restore.users;"

# Restore gzip Compressed Backup
gunzip < /backup/mydb_20260703.sql.gz | mysql -u root -p mydb

# Restore and display progress
pv /backup/mydb_20260703.sql.gz | gunzip | mysql -u root -p mydb

SELECT id, name, email
FROM users
WHERE status = 'active'
INTO OUTFILE '/tmp/active_users.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/active_users.csv'
INTO TABLE users_copy
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

-- Export order data as CSV
SELECT order_id, customer_id, total_amount, order_date
FROM orders
WHERE order_date >= '2026-01-01'
INTO OUTFILE '/tmp/orders_2026.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- Import into another table
LOAD DATA INFILE '/tmp/orders_2026.csv'
INTO TABLE orders_archive
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

LOAD DATA LOCAL INFILE '/home/alice/data/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

-- View binlog Enabled or Disabled
SHOW VARIABLES LIKE 'log_bin%';

-- View Current binlog List of Files
SHOW BINARY LOGS;

-- View the data currently being written binlog
SHOW MASTER STATUS;

-- View binlog Details of the Incident
SHOW BINLOG EVENTS IN 'binlog.000003';

# Steps1：Restore a Full Backup
mysql -u root -p mydb < full_backup_20260703.sql

# Steps2：Find the point in time before the error occurred
mysqlbinlog --base64-output=DECODE-ROWS -v binlog.000003 | grep -A5 "DROP TABLE"

# Steps3：Replay binlog By the target date
mysqlbinlog --start-datetime="2026-07-03 02:00:00" \
  --stop-datetime="2026-07-03 14:30:00" \
  binlog.000003 binlog.000004 | mysql -u root -p

Scene: At 14:25, accidentally executed DROP TABLE orders, need to revert to 14:24:59 state

Timeline:
  02:00  Full backup completed
  ...
  14:24  Last normal operation
  14:25  DROP TABLE orders (operational error)
  14:30  Problem identified, start recovery

Recovery steps:
  1. Restore 02:00 full backup
  2. Use mysqlbinlog to replay binlog from 02:00 to 14:24:59
  3. Verify orders table data integrity

# Execute the command
mysql -u root -p mydb < /backup/full_20260703.sql

mysqlbinlog --start-datetime="2026-07-03 02:00:00" \
  --stop-datetime="2026-07-03 14:24:59" \
  /var/lib/mysql/binlog.000003 \
  /var/lib/mysql/binlog.000004 \
  | mysql -u root -p mydb

# Full Backup
xtrabackup --backup --target-dir=/backup/full -u root -p

# Preparing to Back Up（Applications redo log，Ensure data consistency）
xtrabackup --prepare --target-dir=/backup/full

# Restore Backup
xtrabackup --copy-back --target-dir=/backup/full

# Incremental Backup（Based on the full dataset）
xtrabackup --backup --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/full -u root -p

# Preparing an Incremental Backup
xtrabackup --prepare --apply-log-only --target-dir=/backup/full
xtrabackup --prepare --target-dir=/backup/full \
  --incremental-dir=/backup/inc1

# Full Backup
xtrabackup --backup \
  --target-dir=/data/backup/full_20260703 \
  --user=root --password=Secret123

# Preparation Phase
xtrabackup --prepare \
  --target-dir=/data/backup/full_20260703

# Stop before restoring MySQL
systemctl stop mysqld

# Clear the Data Directory and Restore
rm -rf /var/lib/mysql/*
xtrabackup --copy-back \
  --target-dir=/data/backup/full_20260703

# Repair permissions and start
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

# Usage rsync Transmit to a remote location
rsync -avz /backup/mysql/ backup-server:/data/mysql_backup/

# Usage scp Transmission
scp /backup/mysql/full_20260703.sql.gz backup-server:/data/backup/

#!/bin/bash
# mysql_backup.sh - MySQL Automated Backup Script
BACKUP_DIR="/data/backup/mysql"
RETAIN_DAYS=7
DB_USER="root"
DB_PASS="Secret123"
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/mysql_backup.log"

mkdir -p $BACKUP_DIR
echo "[$DATE] Start Backup..." >> $LOG_FILE

mysqldump -u$DB_USER -p$DB_PASS \
  --all-databases --single-transaction \
  --routines --triggers --set-gtid-purged=OFF \
  | gzip > $BACKUP_DIR/full_${DATE}.sql.gz

if [ $? -eq 0 ]; then
  SIZE=$(du -sh $BACKUP_DIR/full_${DATE}.sql.gz | cut -f1)
  echo "[$DATE] Backup Successful，Size: $SIZE" >> $LOG_FILE
else
  echo "[$DATE] Backup Failed！" >> $LOG_FILE
  exit 1
fi

find $BACKUP_DIR -name "full_*.sql.gz" -mtime +$RETAIN_DAYS -delete
echo "[$DATE] Cleanup ${RETAIN_DAYS} Backup from X days ago" >> $LOG_FILE

#!/bin/bash
# full_backup_restore.sh - Complete Backup and Recovery Verification Script
BACKUP_DIR="/data/backup/mysql"
MYSQL_USER="root"
MYSQL_PASS="Secret123"
REMOTE_HOST="backup-server"
REMOTE_DIR="/data/mysql_backup"
DATE=$(date +%Y%m%d)
LOG="/var/log/backup_restore.log"

echo "=== $DATE Backup and Recovery Process ===" >> $LOG

# 1. Full Backup
mysqldump -u$MYSQL_USER -p$MYSQL_PASS \
  --all-databases --single-transaction \
  --routines --triggers \
  | gzip > $BACKUP_DIR/full_${DATE}.sql.gz
echo "[$DATE] Full backup completed" >> $LOG

# 2. Refresh and Record binlog Location
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "FLUSH BINARY LOGS;"
BINLOG_FILE=$(mysql -u$MYSQL_USER -p$MYSQL_PASS \
  -N -e "SHOW MASTER STATUS;" | awk '{print $1}')
echo "[$DATE] Currently binlog: $BINLOG_FILE" >> $LOG

# 3. Backup binlog Documents
cp /var/lib/mysql/$BINLOG_FILE $BACKUP_DIR/
echo "[$DATE] binlog Backed up" >> $LOG

# 4. Long-distance transmission
rsync -avz $BACKUP_DIR/full_${DATE}.sql.gz \
  $REMOTE_HOST:$REMOTE_DIR/ >> $LOG 2>&1
echo "[$DATE] Remote transmission complete" >> $LOG

# 5. Restore Verification（In the temporary database）
mysql -u$MYSQL_USER -p$MYSQL_PASS \
  -e "CREATE DATABASE IF NOT EXISTS verify_db;"
gunzip < $BACKUP_DIR/full_${DATE}.sql.gz | \
  mysql -u$MYSQL_USER -p$MYSQL_PASS verify_db
ROW_COUNT=$(mysql -u$MYSQL_USER -p$MYSQL_PASS \
  -N -e "SELECT COUNT(*) FROM verify_db.users;")
echo "[$DATE] Verification users Number of lines: $ROW_COUNT" >> $LOG

# 6. Clean Up the Validation Database and Expired Backups
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "DROP DATABASE verify_db;"
find $BACKUP_DIR -name "full_*.sql.gz" -mtime +7 -delete
echo "=== $DATE The backup and restore process is complete ===" >> $LOG

0 2 * * * /usr/local/bin/full_backup_restore.sh