MariaDB备份与恢复 #

备份类型
├── 按数据范围
│   ├── 完全备份
│   ├── 增量备份
│   └── 差异备份
├── 按服务器状态
│   ├── 热备份（在线备份）
│   ├── 温备份（只读状态）
│   └── 冷备份（停止服务）
└── 按备份内容
    ├── 逻辑备份
    └── 物理备份

# 备份单个数据库
mariadb-dump -u root -p mydb > mydb_backup.sql

# 备份多个数据库
mariadb-dump -u root -p --databases mydb1 mydb2 > multi_db_backup.sql

# 备份所有数据库
mariadb-dump -u root -p --all-databases > all_db_backup.sql

# 备份单个表
mariadb-dump -u root -p mydb users > users_backup.sql

# 备份多个表
mariadb-dump -u root -p mydb users orders > tables_backup.sql

# 只备份结构
mariadb-dump -u root -p --no-data mydb > structure.sql

# 只备份数据
mariadb-dump -u root -p --no-create-info mydb > data.sql

# 包含存储过程和函数
mariadb-dump -u root -p --routines --triggers mydb > full_backup.sql

# 包含事件
mariadb-dump -u root -p --events mydb > backup_with_events.sql

# 单事务备份（InnoDB）
mariadb-dump -u root -p --single-transaction mydb > backup.sql

# 锁表备份
mariadb-dump -u root -p --lock-tables mydb > backup.sql

# 主从复制信息
mariadb-dump -u root -p --master-data=2 mydb > backup.sql

# 压缩备份
mariadb-dump -u root -p mydb | gzip > mydb_backup.sql.gz

# 解压恢复
gunzip < mydb_backup.sql.gz | mariadb -u root -p mydb

# 远程备份
mariadb-dump -h remote_host -u root -p mydb > backup.sql

# 通过SSH备份
ssh user@remote "mariadb-dump -u root -p mydb" > backup.sql

# Ubuntu/Debian
sudo apt install mariadb-backup

# CentOS/RHEL
sudo yum install MariaDB-backup

# 创建完全备份
mariabackup --backup --target-dir=/backup/full --user=root --password

# 准备备份（应用日志）
mariabackup --prepare --target-dir=/backup/full

# 创建增量备份
mariabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full --user=root --password

# 准备增量备份
mariabackup --prepare --target-dir=/backup/full --incremental-dir=/backup/inc1

# 停止MariaDB服务
sudo systemctl stop mariadb

# 恢复数据
mariabackup --copy-back --target-dir=/backup/full

# 修改权限
sudo chown -R mysql:mysql /var/lib/mysql

# 启动服务
sudo systemctl start mariadb

# 恢复数据库
mariadb -u root -p mydb < backup.sql

# 恢复到新数据库
mariadb -u root -p -e "CREATE DATABASE mydb_new"
mariadb -u root -p mydb_new < backup.sql

-- 登录MariaDB后执行
USE mydb;
SOURCE /path/to/backup.sql;

# 从备份文件提取单个表
sed -n '/CREATE TABLE `users`/,/CREATE TABLE/p' backup.sql > users.sql

# 恢复
mariadb -u root -p mydb < users.sql

-- 导出为CSV
SELECT * FROM users 
INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- 导出特定列
SELECT id, name, email FROM users 
INTO OUTFILE '/tmp/users_partial.csv'
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

-- 导出带表头
SELECT 'id', 'name', 'email'
UNION ALL
SELECT id, name, email FROM users 
INTO OUTFILE '/tmp/users_with_header.csv'
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

-- 导入CSV
LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- 导入并跳过表头
LOAD DATA INFILE '/tmp/users_with_header.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

-- 导入特定列
LOAD DATA INFILE '/tmp/users_partial.csv'
INTO TABLE users (id, name, email)
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

-- 查看二进制日志状态
SHOW VARIABLES LIKE 'log_bin';

-- 查看二进制日志列表
SHOW BINARY LOGS;

-- 查看当前日志
SHOW MASTER STATUS;

# 查看日志内容
mariadb-binlog /var/lib/mysql/mysql-bin.000001

# 恢复到指定时间点
mariadb-binlog --stop-datetime="2024-01-15 10:00:00" /var/lib/mysql/mysql-bin.000001 | mariadb -u root -p

# 从指定时间点开始恢复
mariadb-binlog --start-datetime="2024-01-15 10:00:00" /var/lib/mysql/mysql-bin.000001 | mariadb -u root -p

# 恢复时间范围
mariadb-binlog --start-datetime="2024-01-15 10:00:00" --stop-datetime="2024-01-15 12:00:00" /var/lib/mysql/mysql-bin.000001 | mariadb -u root -p

# 恢复到指定位置
mariadb-binlog --stop-position=1000 /var/lib/mysql/mysql-bin.000001 | mariadb -u root -p

# 从指定位置开始恢复
mariadb-binlog --start-position=1000 /var/lib/mysql/mysql-bin.000001 | mariadb -u root -p

备份策略示例
├── 周日：完全备份
├── 周一：增量备份
├── 周二：增量备份
├── 周三：增量备份
├── 周四：增量备份
├── 周五：增量备份
└── 周六：增量备份

#!/bin/bash

# 备份目录
BACKUP_DIR="/backup/mariadb"
DATE=$(date +%Y%m%d)
TIME=$(date +%H%M%S)

# 创建备份目录
mkdir -p $BACKUP_DIR/$DATE

# 完全备份（周日）
if [ $(date +%u) -eq 7 ]; then
    mariadb-dump -u root -p'password' --all-databases --single-transaction --routines --triggers --events | gzip > $BACKUP_DIR/$DATE/full_backup_$TIME.sql.gz
else
    # 增量备份（使用二进制日志）
    mariadb-dump -u root -p'password' --all-databases --single-transaction | gzip > $BACKUP_DIR/$DATE/incremental_backup_$TIME.sql.gz
fi

# 删除7天前的备份
find $BACKUP_DIR -type d -mtime +7 -exec rm -rf {} \;

# 备份二进制日志
cp /var/lib/mysql/mysql-bin.* $BACKUP_DIR/$DATE/

# 添加到crontab
crontab -e

# 每天凌晨2点备份
0 2 * * * /scripts/backup_mariadb.sh

# 检查备份文件完整性
gzip -t backup.sql.gz

# 查看备份内容
zcat backup.sql.gz | head -100

# 统计备份大小
ls -lh backup.sql.gz

# 在测试环境恢复
mariadb -u root -p test_db < backup.sql

# 验证数据
mariadb -u root -p test_db -e "SELECT COUNT(*) FROM users;"

# 备份到AWS S3
mariadb-dump -u root -p mydb | gzip | aws s3 cp - s3://mybucket/backups/mydb.sql.gz

# 备份到阿里云OSS
mariadb-dump -u root -p mydb | gzip | ossutil cp - oss://mybucket/backups/mydb.sql.gz

# 从S3恢复
aws s3 cp s3://mybucket/backups/mydb.sql.gz - | gunzip | mariadb -u root -p mydb

# 从OSS恢复
ossutil cp oss://mybucket/backups/mydb.sql.gz - | gunzip | mariadb -u root -p mydb

备份检查清单
├── 备份是否成功？
├── 备份文件是否完整？
├── 备份是否可恢复？
├── 备份是否加密？
├── 备份是否异地存储？
├── 备份保留时间是否合理？
└── 是否有恢复演练？