内容简介：本文主要介绍MariaDB Mariabackup热备份的方法与实践，包括Mariabackup的基本概念、热备份的基本概念、Mariabackup的工作原理等内容。通过实际案例讲解Mariabackup热备份的实施过程，帮助读者掌握Mariabackup热备份的技能。风哥教程参考MariaDB官方文档Mariabackup、Backup and Recovery等相关内容。
Part01-基础概念与理论知识
1.1 Mariabackup的基本概念
Mariabackup是MariaDB提供的一个热备份工具，用于在不停止数据库服务的情况下创建数据库备份。它基于Percona XtraBackup，支持InnoDB、Aria等存储引擎的热备份。
Mariabackup的主要特点：
- 支持热备份，不影响数据库服务
- 支持全量备份和增量备份
- 支持压缩备份
- 支持远程备份
- 支持备份验证
1.2 热备份的基本概念
热备份是指在数据库正常运行的情况下进行备份，不需要停止数据库服务。热备份的主要优点：
- 不影响业务运行
- 可以在任何时间点进行备份
- 适合7*24小时运行的系统
热备份的主要挑战：
- 需要处理数据一致性问题
- 可能会影响数据库性能
- 备份过程需要更多的系统资源
1.3 Mariabackup的工作原理
Mariabackup的工作原理：
- 开始备份时，Mariabackup会记录当前的LSN（Log Sequence Number）
- 复制InnoDB数据文件
- 复制Redo日志文件
- 在备份过程中，会持续复制新产生的Redo日志
- 备份完成后，使用Redo日志来确保数据一致性
更多视频教程www.fgedu.net.cn
Part02-生产环境规划与建议
2.1 备份策略规划
备份策略规划建议：
- 全量备份：每周进行一次全量备份
- 增量备份：每天进行一次增量备份
- 备份验证：每月进行一次备份恢复测试
- 备份保留：保留最近30天的备份
2.2 Mariabackup配置建议
Mariabackup配置建议：
- 设置合理的备份目录：确保备份目录有足够的存储空间
- 配置备份用户：创建专门的备份用户，授予必要的权限
- 设置备份参数：根据数据库大小和性能要求设置合适的参数
- 监控备份过程：监控备份过程，及时发现和解决问题
2.3 备份存储建议
备份存储建议：
- 使用外部存储：将备份存储在外部存储设备上
- 多副本存储：在不同地点存储备份副本
- 加密存储：对备份数据进行加密，保护数据安全
- 定期清理：定期清理过期备份，释放存储空间
学习交流加群风哥微信: itpux-com
Part03-生产环境项目实施方案
3.1 Mariabackup安装与配置
更多学习教程公众号风哥教程itpux_com
# 安装Mariabackup
MariaDB [(none)]> yum install mariadb-backup
# 创建备份用户
CREATE USER ‘backup’@’fgedu.localhost’ IDENTIFIED BY ‘backup_password’;
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO ‘backup’@’fgedu.localhost’;
FLUSH PRIVILEGES;
# 创建备份目录
mkdir -p /backup/mariabackup
chown mysql:mysql /backup/mariabackup
MariaDB [(none)]> yum install mariadb-backup
# 创建备份用户
CREATE USER ‘backup’@’fgedu.localhost’ IDENTIFIED BY ‘backup_password’;
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO ‘backup’@’fgedu.localhost’;
FLUSH PRIVILEGES;
# 创建备份目录
mkdir -p /backup/mariabackup
chown mysql:mysql /backup/mariabackup
3.2 热备份实施
# 全量热备份
MariaDB [(none)]> mariabackup –backup –target-dir=/backup/mariabackup/full –user=backup –password=backup_password
# 增量热备份
mariabackup –backup –target-dir=/backup/mariabackup/incremental –incremental-basedir=/backup/mariabackup/full –user=backup –password=backup_password
# 压缩备份
mariabackup –backup –target-dir=/backup/mariabackup/compressed –compress –user=backup –password=backup_password
# 查看备份信息
mariabackup –info –target-dir=/backup/mariabackup/full
MariaDB [(none)]> mariabackup –backup –target-dir=/backup/mariabackup/full –user=backup –password=backup_password
# 增量热备份
mariabackup –backup –target-dir=/backup/mariabackup/incremental –incremental-basedir=/backup/mariabackup/full –user=backup –password=backup_password
# 压缩备份
mariabackup –backup –target-dir=/backup/mariabackup/compressed –compress –user=backup –password=backup_password
# 查看备份信息
mariabackup –info –target-dir=/backup/mariabackup/full
3.3 备份恢复实施
# 准备备份
MariaDB [(none)]> mariabackup –prepare –target-dir=/backup/mariabackup/full
# 准备增量备份
mariabackup –prepare –target-dir=/backup/mariabackup/full –incremental-dir=/backup/mariabackup/incremental
# 停止数据库服务
systemctl stop mariadb
# 清空数据目录
rm -rf /mariadb/fgdata/*
# 恢复备份
mariabackup –copy-back –target-dir=/backup/mariabackup/full
# 修复权限
chown -R mysql:mysql /mariadb/fgdata
# 启动数据库服务
systemctl start mariadb
MariaDB [(none)]> mariabackup –prepare –target-dir=/backup/mariabackup/full
# 准备增量备份
mariabackup –prepare –target-dir=/backup/mariabackup/full –incremental-dir=/backup/mariabackup/incremental
# 停止数据库服务
systemctl stop mariadb
# 清空数据目录
rm -rf /mariadb/fgdata/*
# 恢复备份
mariabackup –copy-back –target-dir=/backup/mariabackup/full
# 修复权限
chown -R mysql:mysql /mariadb/fgdata
# 启动数据库服务
systemctl start mariadb
学习交流加群风哥QQ113257174
Part04-生产案例与实战讲解
4.1 全量热备份案例
场景描述：使用Mariabackup进行全量热备份。
# 执行全量热备份
MariaDB [(none)]> mariabackup –backup –target-dir=/backup/mariabackup/full_20230101 –user=backup –password=backup_password
# 查看备份过程
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: Starting backup with following arguments:
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: –backup
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: –target-dir=/backup/mariabackup/full_20230101
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: –user=backup
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: –password=*redacted*
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: Using 16777216 bytes as block size
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Generating a list of tablespaces
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Starting 1 threads for parallel data file backup
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Added table `fgedudb`.`fgedu_users`
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Added table `fgedudb`.`fgedu_articles`
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Creating backup for file `/mariadb/fgdata/fgedudb/fgedu_users.ibd`
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Creating backup for file `/mariadb/fgdata/fgedudb/fgedu_articles.ibd`
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Backup created in directory ‘/backup/mariabackup/full_20230101’
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Backup completed successfully
# 查看备份目录
ls -l /backup/mariabackup/full_20230101/
total 102400
-rw-rw—- 1 mysql mysql 10485760 Jan 1 00:00 fgedu_users.ibd
-rw-rw—- 1 mysql mysql 10485760 Jan 1 00:00 fgedu_articles.ibd
-rw-rw—- 1 mysql mysql 1234 Jan 1 00:00 backup-my.cnf
-rw-rw—- 1 mysql mysql 23 Jan 1 00:00 xtrabackup_binlog_info
-rw-rw—- 1 mysql mysql 135 Jan 1 00:00 xtrabackup_checkpoints
-rw-rw—- 1 mysql mysql 2560 Jan 1 00:00 xtrabackup_info
-rw-rw—- 1 mysql mysql 1048576 Jan 1 00:00 xtrabackup_logfile
MariaDB [(none)]> mariabackup –backup –target-dir=/backup/mariabackup/full_20230101 –user=backup –password=backup_password
# 查看备份过程
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: Starting backup with following arguments:
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: –backup
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: –target-dir=/backup/mariabackup/full_20230101
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: –user=backup
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: –password=*redacted*
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: Using 16777216 bytes as block size
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Generating a list of tablespaces
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Starting 1 threads for parallel data file backup
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Added table `fgedudb`.`fgedu_users`
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Added table `fgedudb`.`fgedu_articles`
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Creating backup for file `/mariadb/fgdata/fgedudb/fgedu_users.ibd`
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Creating backup for file `/mariadb/fgdata/fgedudb/fgedu_articles.ibd`
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Backup created in directory ‘/backup/mariabackup/full_20230101’
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Backup completed successfully
# 查看备份目录
ls -l /backup/mariabackup/full_20230101/
total 102400
-rw-rw—- 1 mysql mysql 10485760 Jan 1 00:00 fgedu_users.ibd
-rw-rw—- 1 mysql mysql 10485760 Jan 1 00:00 fgedu_articles.ibd
-rw-rw—- 1 mysql mysql 1234 Jan 1 00:00 backup-my.cnf
-rw-rw—- 1 mysql mysql 23 Jan 1 00:00 xtrabackup_binlog_info
-rw-rw—- 1 mysql mysql 135 Jan 1 00:00 xtrabackup_checkpoints
-rw-rw—- 1 mysql mysql 2560 Jan 1 00:00 xtrabackup_info
-rw-rw—- 1 mysql mysql 1048576 Jan 1 00:00 xtrabackup_logfile
执行结果：
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: Backup completed successfully
total 102400
-rw-rw—- 1 mysql mysql 10485760 Jan 1 00:00 fgedu_users.ibd
-rw-rw—- 1 mysql mysql 10485760 Jan 1 00:00 fgedu_articles.ibd
-rw-rw—- 1 mysql mysql 1234 Jan 1 00:00 backup-my.cnf
-rw-rw—- 1 mysql mysql 23 Jan 1 00:00 xtrabackup_binlog_info
-rw-rw—- 1 mysql mysql 135 Jan 1 00:00 xtrabackup_checkpoints
-rw-rw—- 1 mysql mysql 2560 Jan 1 00:00 xtrabackup_info
-rw-rw—- 1 mysql mysql 1048576 Jan 1 00:00 xtrabackup_logfile
total 102400
-rw-rw—- 1 mysql mysql 10485760 Jan 1 00:00 fgedu_users.ibd
-rw-rw—- 1 mysql mysql 10485760 Jan 1 00:00 fgedu_articles.ibd
-rw-rw—- 1 mysql mysql 1234 Jan 1 00:00 backup-my.cnf
-rw-rw—- 1 mysql mysql 23 Jan 1 00:00 xtrabackup_binlog_info
-rw-rw—- 1 mysql mysql 135 Jan 1 00:00 xtrabackup_checkpoints
-rw-rw—- 1 mysql mysql 2560 Jan 1 00:00 xtrabackup_info
-rw-rw—- 1 mysql mysql 1048576 Jan 1 00:00 xtrabackup_logfile
4.2 增量热备份案例
场景描述：使用Mariabackup进行增量热备份。
# 执行全量热备份
MariaDB [(none)]> mariabackup –backup –target-dir=/backup/mariabackup/full_20230101 –user=backup –password=backup_password
# 执行增量热备份
mariabackup –backup –target-dir=/backup/mariabackup/incremental_20230102 –incremental-basedir=/backup/mariabackup/full_20230101 –user=backup –password=backup_password
# 查看增量备份过程
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: Starting backup with following arguments:
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: –backup
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: –target-dir=/backup/mariabackup/incremental_20230102
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: –incremental-basedir=/backup/mariabackup/full_20230101
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: –user=backup
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: –password=*redacted*
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: Using 16777216 bytes as block size
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Generating a list of tablespaces
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Starting 1 threads for parallel data file backup
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Added table `fgedudb`.`fgedu_users`
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Added table `fgedudb`.`fgedu_articles`
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Creating backup for file `/mariadb/fgdata/fgedudb/fgedu_users.ibd`
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Creating backup for file `/mariadb/fgdata/fgedudb/fgedu_articles.ibd`
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Backup created in directory ‘/backup/mariabackup/incremental_20230102’
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Backup completed successfully
# 查看增量备份目录
ls -l /backup/mariabackup/incremental_20230102/
total 10240
-rw-rw—- 1 mysql mysql 1048576 Jan 2 00:00 fgedu_users.ibd
-rw-rw—- 1 mysql mysql 1048576 Jan 2 00:00 fgedu_articles.ibd
-rw-rw—- 1 mysql mysql 1234 Jan 2 00:00 backup-my.cnf
-rw-rw—- 1 mysql mysql 23 Jan 2 00:00 xtrabackup_binlog_info
-rw-rw—- 1 mysql mysql 135 Jan 2 00:00 xtrabackup_checkpoints
-rw-rw—- 1 mysql mysql 2560 Jan 2 00:00 xtrabackup_info
-rw-rw—- 1 mysql mysql 1048576 Jan 2 00:00 xtrabackup_logfile
MariaDB [(none)]> mariabackup –backup –target-dir=/backup/mariabackup/full_20230101 –user=backup –password=backup_password
# 执行增量热备份
mariabackup –backup –target-dir=/backup/mariabackup/incremental_20230102 –incremental-basedir=/backup/mariabackup/full_20230101 –user=backup –password=backup_password
# 查看增量备份过程
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: Starting backup with following arguments:
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: –backup
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: –target-dir=/backup/mariabackup/incremental_20230102
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: –incremental-basedir=/backup/mariabackup/full_20230101
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: –user=backup
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: –password=*redacted*
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: Using 16777216 bytes as block size
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Generating a list of tablespaces
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Starting 1 threads for parallel data file backup
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Added table `fgedudb`.`fgedu_users`
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Added table `fgedudb`.`fgedu_articles`
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Creating backup for file `/mariadb/fgdata/fgedudb/fgedu_users.ibd`
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Creating backup for file `/mariadb/fgdata/fgedudb/fgedu_articles.ibd`
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Backup created in directory ‘/backup/mariabackup/incremental_20230102’
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: mariabackup: Backup completed successfully
# 查看增量备份目录
ls -l /backup/mariabackup/incremental_20230102/
total 10240
-rw-rw—- 1 mysql mysql 1048576 Jan 2 00:00 fgedu_users.ibd
-rw-rw—- 1 mysql mysql 1048576 Jan 2 00:00 fgedu_articles.ibd
-rw-rw—- 1 mysql mysql 1234 Jan 2 00:00 backup-my.cnf
-rw-rw—- 1 mysql mysql 23 Jan 2 00:00 xtrabackup_binlog_info
-rw-rw—- 1 mysql mysql 135 Jan 2 00:00 xtrabackup_checkpoints
-rw-rw—- 1 mysql mysql 2560 Jan 2 00:00 xtrabackup_info
-rw-rw—- 1 mysql mysql 1048576 Jan 2 00:00 xtrabackup_logfile
执行结果：
2023-01-01 00:00:00 0x7f1234567890 Mariabackup: Backup completed successfully
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: Backup completed successfully
total 10240
-rw-rw—- 1 mysql mysql 1048576 Jan 2 00:00 fgedu_users.ibd
-rw-rw—- 1 mysql mysql 1048576 Jan 2 00:00 fgedu_articles.ibd
-rw-rw—- 1 mysql mysql 1234 Jan 2 00:00 backup-my.cnf
-rw-rw—- 1 mysql mysql 23 Jan 2 00:00 xtrabackup_binlog_info
-rw-rw—- 1 mysql mysql 135 Jan 2 00:00 xtrabackup_checkpoints
-rw-rw—- 1 mysql mysql 2560 Jan 2 00:00 xtrabackup_info
-rw-rw—- 1 mysql mysql 1048576 Jan 2 00:00 xtrabackup_logfile
2023-01-02 00:00:00 0x7f1234567890 Mariabackup: Backup completed successfully
total 10240
-rw-rw—- 1 mysql mysql 1048576 Jan 2 00:00 fgedu_users.ibd
-rw-rw—- 1 mysql mysql 1048576 Jan 2 00:00 fgedu_articles.ibd
-rw-rw—- 1 mysql mysql 1234 Jan 2 00:00 backup-my.cnf
-rw-rw—- 1 mysql mysql 23 Jan 2 00:00 xtrabackup_binlog_info
-rw-rw—- 1 mysql mysql 135 Jan 2 00:00 xtrabackup_checkpoints
-rw-rw—- 1 mysql mysql 2560 Jan 2 00:00 xtrabackup_info
-rw-rw—- 1 mysql mysql 1048576 Jan 2 00:00 xtrabackup_logfile
4.3 备份恢复案例
场景描述：使用Mariabackup恢复备份。
# 准备全量备份
MariaDB [(none)]> mariabackup –prepare –target-dir=/backup/mariabackup/full_20230101
# 准备增量备份
mariabackup –prepare –target-dir=/backup/mariabackup/full_20230101 –incremental-dir=/backup/mariabackup/incremental_20230102
# 停止数据库服务
systemctl stop mariadb
# 清空数据目录
rm -rf /mariadb/fgdata/*
# 恢复备份
mariabackup –copy-back –target-dir=/backup/mariabackup/full_20230101
# 修复权限
chown -R mysql:mysql /mariadb/fgdata
# 启动数据库服务
systemctl start mariadb
# 验证数据完整性
mysql -u root -p -e “SELECT COUNT(*) FROM fgedudb.fgedu_users;
”
+———-+
| COUNT(*) |
+———-+
| 1000 |
+———-+
MariaDB [(none)]> mariabackup –prepare –target-dir=/backup/mariabackup/full_20230101
# 准备增量备份
mariabackup –prepare –target-dir=/backup/mariabackup/full_20230101 –incremental-dir=/backup/mariabackup/incremental_20230102
# 停止数据库服务
systemctl stop mariadb
# 清空数据目录
rm -rf /mariadb/fgdata/*
# 恢复备份
mariabackup –copy-back –target-dir=/backup/mariabackup/full_20230101
# 修复权限
chown -R mysql:mysql /mariadb/fgdata
# 启动数据库服务
systemctl start mariadb
# 验证数据完整性
mysql -u root -p -e “SELECT COUNT(*) FROM fgedudb.fgedu_users;
”
+———-+
| COUNT(*) |
+———-+
| 1000 |
+———-+
执行结果：
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: Starting prepare with following arguments:
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: –prepare
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: –target-dir=/backup/mariabackup/full_20230101
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: mariabackup: using the following InnoDB configuration for recovery:
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: innodb_data_home_dir = .
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: innodb_data_file_path = ibdata1:12M:autoextend
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: innodb_log_group_home_dir = .
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: innodb_log_files_in_group = 2
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: innodb_log_file_size = 50331648
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: mariabackup: Starting InnoDB instance for recovery.
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: mariabackup: Using 104857600 bytes for buffer pool (set by –use-memory parameter)
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: mariabackup: Starting log scan
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: mariabackup: Completed log scan, found 100 redo log entries
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: mariabackup: Starting InnoDB instance for recovery.
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: mariabackup: Using 104857600 bytes for buffer pool (set by –use-memory parameter)
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: mariabackup: Starting log scan
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: mariabackup: Completed log scan, found 50 redo log entries
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: mariabackup: Recovery completed successfully
Stopping mariadb.service…
Starting mariadb.service…
+———-+
| COUNT(*) |
+———-+
| 1000 |
+———-+
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: –prepare
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: –target-dir=/backup/mariabackup/full_20230101
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: mariabackup: using the following InnoDB configuration for recovery:
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: innodb_data_home_dir = .
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: innodb_data_file_path = ibdata1:12M:autoextend
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: innodb_log_group_home_dir = .
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: innodb_log_files_in_group = 2
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: innodb_log_file_size = 50331648
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: mariabackup: Starting InnoDB instance for recovery.
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: mariabackup: Using 104857600 bytes for buffer pool (set by –use-memory parameter)
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: mariabackup: Starting log scan
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: mariabackup: Completed log scan, found 100 redo log entries
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: mariabackup: Starting InnoDB instance for recovery.
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: mariabackup: Using 104857600 bytes for buffer pool (set by –use-memory parameter)
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: mariabackup: Starting log scan
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: mariabackup: Completed log scan, found 50 redo log entries
2023-01-02 01:00:00 0x7f1234567890 Mariabackup: mariabackup: Recovery completed successfully
Stopping mariadb.service…
Starting mariadb.service…
+———-+
| COUNT(*) |
+———-+
| 1000 |
+———-+
风哥提示：安全开发是防止SQL注入的第一道防线
Part05-风哥经验总结与分享
5.1 Mariabackup最佳实践
风哥提示：在使用Mariabackup进行热备份时，应根据数据库大小和业务需求选择合适的备份策略，并定期测试备份恢复。
- 使用热备份：在数据库正常运行的情况下进行备份，不影响业务
- 结合全量和增量备份：每周进行全量备份，每天进行增量备份
- 压缩备份：减少存储空间占用
- 备份到外部存储：确保备份的安全性
- 定期测试恢复：确保备份有效
5.2 热备份技巧
- 选择合适的备份时间：在业务低峰期进行备份
- 监控备份过程：实时监控备份进度，及时发现问题
- 合理设置并行度：根据服务器性能设置合适的并行度
- 使用压缩：减少备份文件大小，节省存储空间
- 定期清理过期备份：释放存储空间
5.3 常见问题与解决方案
- 备份速度慢：增加并行度，使用压缩，或在低峰期执行备份
- 备份失败：检查数据库连接，确保权限正确，检查磁盘空间
- 恢复失败：检查备份文件是否完整，确保数据库版本兼容
- 存储空间不足：定期清理过期备份，或扩展存储空间
- 性能影响：在低峰期执行备份，或限制备份的资源使用
# Mariabackup自动化备份脚本示例
#!/bin/bash
# mariabackup_auto.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`
# 配置
BACKUP_DIR=”/backup/mariabackup”
DATE=$(date +%Y%m%d)
TIME=$(date +%H%M%S)
USER=”backup”
PASSWORD=”backup_password”
LOG_FILE=”$BACKUP_DIR/backup.log”
# 创建备份目录
mkdir -p $BACKUP_DIR/$DATE
# 记录开始时间
echo “Backup started at $(date)” >> $LOG_FILE
# 全量备份
if [ ! -d “$BACKUP_DIR/full” ];
then
echo “Performing full backup” >> $LOG_FILE
mariabackup –backup –target-dir=$BACKUP_DIR/full –user=$USER –password=$PASSWORD
if [ $? -eq 0 ];
then
echo “Full backup completed successfully” >> $LOG_FILE
else
echo “Full backup failed” >> $LOG_FILE
exit 1
fi
else
echo “Performing incremental backup” >> $LOG_FILE
mariabackup –backup –target-dir=$BACKUP_DIR/$DATE –incremental-basedir=$BACKUP_DIR/full –user=$USER –password=$PASSWORD
if [ $? -eq 0 ];
then
echo “Incremental backup completed successfully” >> $LOG_FILE
else
echo “Incremental backup failed” >> $LOG_FILE
exit 1
fi
fi
# 清理过期备份（保留30天）
find $BACKUP_DIR -type d -mtime +30 -exec rm -rf {} \;
echo “Old backups cleaned up” >> $LOG_FILE
# 记录结束时间
echo “Backup finished at $(date)” >> $LOG_FILE
echo “———————————-” >> $LOG_FILE
#!/bin/bash
# mariabackup_auto.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`
# 配置
BACKUP_DIR=”/backup/mariabackup”
DATE=$(date +%Y%m%d)
TIME=$(date +%H%M%S)
USER=”backup”
PASSWORD=”backup_password”
LOG_FILE=”$BACKUP_DIR/backup.log”
# 创建备份目录
mkdir -p $BACKUP_DIR/$DATE
# 记录开始时间
echo “Backup started at $(date)” >> $LOG_FILE
# 全量备份
if [ ! -d “$BACKUP_DIR/full” ];
then
echo “Performing full backup” >> $LOG_FILE
mariabackup –backup –target-dir=$BACKUP_DIR/full –user=$USER –password=$PASSWORD
if [ $? -eq 0 ];
then
echo “Full backup completed successfully” >> $LOG_FILE
else
echo “Full backup failed” >> $LOG_FILE
exit 1
fi
else
echo “Performing incremental backup” >> $LOG_FILE
mariabackup –backup –target-dir=$BACKUP_DIR/$DATE –incremental-basedir=$BACKUP_DIR/full –user=$USER –password=$PASSWORD
if [ $? -eq 0 ];
then
echo “Incremental backup completed successfully” >> $LOG_FILE
else
echo “Incremental backup failed” >> $LOG_FILE
exit 1
fi
fi
# 清理过期备份（保留30天）
find $BACKUP_DIR -type d -mtime +30 -exec rm -rf {} \;
echo “Old backups cleaned up” >> $LOG_FILE
# 记录结束时间
echo “Backup finished at $(date)” >> $LOG_FILE
echo “———————————-” >> $LOG_FILE
通过以上措施，可以有效实现MariaDB的Mariabackup热备份，确保数据安全和系统稳定。
from MariaDB视频:www.itpux.com
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息