大连外经贸网站,长沙专业做网站排名,营销加盟网站建设,什么是百度推广MySQL 提供了多种备份方式#xff0c;每种方式适用于不同的场景和需求。根据备份的粒度、速度、恢复时间和对数据库的影响#xff0c;可以选择合适的备份策略。主要备份方式有三大类#xff1a;逻辑备份#xff08;mysqldump#xff09;#xff0c;物理备份和二进制文件备…MySQL 提供了多种备份方式每种方式适用于不同的场景和需求。根据备份的粒度、速度、恢复时间和对数据库的影响可以选择合适的备份策略。主要备份方式有三大类逻辑备份mysqldump物理备份和二进制文件备份。
一、逻辑备份Logical Backup
逻辑备份是通过导出SQL语句或表结构和数据来实现的。它不直接复制物理文件而是生成可以重新执行的SQL文件。逻辑备份的优点是易于理解和恢复但通常比物理备份慢。
1、mysqldump工具
mysqldump是MySQL自带的命令行工具用于导出数据库的表结构和数据。它可以生成完整的SQL文件支持单个表、多个表或整个数据库的备份。
备份方式
全量备份备份整个数据库或多个数据库。部分备份可以只备份特定的表或数据库。增量备份可以通过–master-data和–single-transaction选项结合二进制日志进行增量备份。
mysqldump备份优点
支持跨版本恢复。如8.0版本导出备份文件可以在5.7版本Mysql上进行还原可以选择性地恢复部分数据。生成的SQL文件易于理解和修改。
mysqldump备份缺点
备份速度较慢尤其是大数据库。备份期间可能会锁定表除非使用–single-transaction。恢复时间较长因为需要重新执行SQL语句。
示例mysql命令非sql语句
1、备份所有数据库
mysqldump -u root -p --all-databases all_databases.sql运行结果 会在当前目录下生成全量的sql文件。 查看如下 包含所有的库表和数据信息。
2、备份单个数据库
指定备份demo1数据库输出到demo1.sql文件中。
mysqldump -u root -p demo1 demo1.sql运行结果 查看如下 包含demo1库中的表和数据信息。
3、备份单个表
指定demo1数据库的employees表进行备份输出到employees.sql文件中。
mysqldump -u root -p demo1 employees employees.sql运行结果 查看如下
4、使用压缩
使用压缩方式进行备份。
mysqldump -u root -p demo1 | gzip demo1.sql.gz如果备份文件非常大且又需要频繁备份的场景。建议备份时启用压缩。
2、mysqlpump工具
mysqlpump是MySQL 5.7引入的一个新工具类似于mysqldump但性能更好尤其是在处理大数据库时。它支持并行备份和更细粒度的控制。
mysqlpump优点
支持并行备份备份速度更快。支持更多的并发操作。可以选择性地备份存储过程、触发器等。
mysqlpump缺点
不支持MySQL 5.6及之前版本。恢复时间仍然较长。
示例
1、备份全部数据库
mysqlpump -u root -p --all-databases all_databases1.sql运行结果
2、备份单个数据库
备份demo1数据库输出到demo1_1.sql文件中。
mysqlpump -u root -p demo1 demo1_1.sql运行结果
3、并行备份多个数据库
备份demo1数据库和sys数据库到multiple_databases.sql文件中。
mysqlpump -u root -p --parallel-schemasdemo1,sys multiple_databases.sql运行结果
二、物理备份Physical Backup
物理备份是直接复制数据库的物理文件如.frm、.ibd等。物理备份的速度通常比逻辑备份快因为它是直接复制文件而不是生成SQL语句。物理备份可以分为热备份、温备份和冷备份。
1、冷备份Cold Backup
冷备份是指在数据库完全停止的情况下进行的备份。这种方式最简单但会对业务造成停机影响。
步骤 1、停止MySQL服务。 2、复制数据目录中的所有文件。 3、重启MySQL服务。
示例
停止MySQL服务
sudo systemctl stop mysql
复制数据目录
cp -R /var/lib/mysql /backup/mysql_cold_backup
重启MySQL服务
sudo systemctl start mysql优点
简单易用。备份速度快。恢复速度快。
缺点
需要停机不适合生产环境。无法进行增量备份。
2、温备份Warm Backup
温备份是指在数据库处于只读模式的情况下进行的备份。这种方式不会完全停止数据库但会阻止写操作因此对业务的影响较小。
步骤 1、将数据库设置为只读模式。 2、复制数据目录中的所有文件。 3、恢复数据库的读写模式。
示例
-- 设置只读模式
SET GLOBAL readonly ON;
-- 复制数据目录
cp -R /var/lib/mysql /backup/mysql_warm_backup
-- 恢复读写模式
SET GLOBAL readonly OFF;优点
不需要完全停机。备份速度快。恢复速度快。
缺点
写操作会被阻止可能影响业务。无法进行增量备份。
3、热备份Hot Backup
热备份是指在数据库正常运行的情况下进行的备份。这种方式不会影响数据库的正常读写操作适合生产环境。常见的热备份工具有Percona XtraBackup和MySQL Enterprise Backup。
1、Percona XtraBackup
Percona XtraBackup是一个开源的热备份工具专门用于InnoDB和XtraDB存储引擎。它可以进行在线备份并且支持增量备份和压缩。
特点
支持热备份不影响数据库的正常运行。支持增量备份和全量备份。支持压缩和流式备份。支持备份加密。支持并行备份和恢复。
示例
安装Percona XtraBackup
sudo apt-get install percona-xtrabackup-80进行全量备份
xtrabackup --userroot --passwordyour_password --backup --target-dir/backup/full进行增量备份
xtrabackup --userroot --passwordyour_password --backup --target-dir/backup/incremental --incremental-basedir/backup/full恢复备份
xtrabackup --prepare --target-dir/backup/full
xtrabackup --copy-back --target-dir/backup/full2、MySQL Enterprise Backup
MySQL Enterprise Backup是Oracle提供的商业备份工具专为MySQL企业版设计。它提供了类似Percona XtraBackup的功能但还有一些额外的企业级特性如自动备份调度、备份验证等。
特点
支持热备份不影响数据库的正常运行。支持增量备份和全量备份。支持压缩和流式备份。支持备份加密。支持并行备份和恢复。提供自动备份调度和备份验证。
优点
支持热备份不影响数据库的正常运行。支持增量备份节省存储空间。备份速度快恢复速度快。适合大规模生产环境。
缺点
需要购买 MySQL 企业版许可证。
三、二进制日志备份Binary Log Backup
二进制日志binary log记录了所有对数据库的更改操作如INSERT、UPDATE、DELETE等。通过备份二进制日志可以在全量备份的基础上进行增量恢复从而减少恢复时间。
步骤 1、启用二进制日志。 2、定期备份二进制日志文件。 3、在恢复时先恢复全量备份再应用二进制日志中的更改恢复到指定时间点。
示例
查看当前的二进制日志文件
mysql SHOW MASTER LOGS;查看日志的路径
SHOW VARIABLES LIKE datadir;
SHOW VARIABLES LIKE log_bin%;备份二进制日志文件
cp /var/lib/mysql/mysql-bin. /backup/binlogs/应用二进制日志进行恢复
mysqlbinlog /backup/binlogs/mysql-bin.000001 | mysql -u root -p优点
支持增量备份节省存储空间。可以进行精确的时间点恢复。适合高可用性和灾难恢复场景。
缺点
需要启用二进制日志可能会增加I/O开销。二进制日志文件较大需要定期清理。
四、逻辑恢复
逻辑备份是通过mysqldump或mysqlpump等工具生成的SQL文件。恢复时可以通过执行这些SQL文件来重新创建数据库和表结构并插入数据。
1、使用mysqldump备份文件恢复
步骤 1、导入SQL文件将备份的SQL文件通过mysql命令行工具导入到目标数据库中。 2、指定数据库如果备份的是单个数据库或多个数据库确保在导入时指定正确的数据库名称。
示例
1、恢复整个数据库
mysql -u root -p all_databases.sql运行结果 所有的数据库都恢复到了备份文件生成的时间点。
2、恢复单个数据库
恢复demo1数据库执行demo1.sql文件
mysql -u root -p demo1 demo1.sql运行结果
3、恢复单个表
恢复demo1数据库的employees表执行employees.sql文件。
mysql -u root -p demo1 employees.sql运行结果
优点
支持跨版本恢复。可以选择性地恢复部分数据如单个表或存储过程。生成的SQL文件易于理解和修改。
缺点
恢复速度较慢尤其是大数据库。如果备份文件较大可能会占用较多内存和磁盘I/O。需要重新执行SQL语句可能导致锁表或阻塞其他操作。
2、使用mysqlpump备份文件恢复
mysqlpump是MySQL 5.7引入的一个新工具支持并行备份和更细粒度的控制。恢复过程与mysqldump类似几乎一样。
示例
恢复整个数据库
mysql -u root -p all_databases.sql恢复单个数据库
指定恢复demo1数据库执行demo1.sql文件
mysql -u root -p demo1 demo1.sql优点
支持并行恢复速度更快。支持更多的并发操作。可以选择性地恢复部分数据。
缺点
不支持MySQL 5.6及以下版本。
五、物理恢复
物理备份是直接复制数据库的物理文件如.frm、.ibd等。物理备份的恢复速度通常比逻辑备份快因为它是直接恢复文件而不是重新执行SQL语句。
1、冷备份的恢复
冷备份是在数据库完全停止的情况下进行的备份。恢复时只需将备份的文件复制回原始位置并重启MySQL服务。
步骤 1、停止MySQL服务。 2、复制备份文件将备份的数据目录复制回原始位置。 3、重启MySQL服务。
示例
停止MySQL服务
sudo systemctl stop mysql复制备份文件
cp -R /backup/mysql_cold_backup/ /var/lib/mysql/重启MySQL服务
sudo systemctl start mysql优点
恢复速度快。不需要重新执行SQL语句。
缺点
需要停机不适合生产环境。无法进行增量恢复。
2、温备份的恢复
温备份是在数据库处于只读模式的情况下进行的备份。恢复时只需将备份的文件复制回原始位置并恢复数据库的读写模式。
步骤 1、设置只读模式。 2、复制备份文件将备份的数据目录复制回原始位置。 3、恢复读写模式。
示例
-- 设置只读模式
SET GLOBAL readonly ON;-- 复制备份文件
cp -R /backup/mysql_warm_backup/ /var/lib/mysql/-- 恢复读写模式
SET GLOBAL readonly OFF;优点
不需要完全停机。恢复速度快。
缺点
写操作会被阻止可能影响业务。无法进行增量恢复。
3、热备份的恢复
热备份是在数据库正常运行的情况下进行的备份。常见的热备份工具有Percona XtraBackup和MySQL Enterprise Backup。恢复时需要先准备备份文件然后将其复制回原始位置。
1、使用Percona XtraBackup恢复
步骤 1、准备备份文件使用XtraBackup --prepare命令准备备份文件确保其处于一致状态。 2、停止MySQL服务。 3、复制备份文件将准备好的备份文件复制回原始位置。 4、重启MySQL服务。
示例
准备全量备份
xtrabackup --prepare --target-dir/backup/full如果有增量备份同时也要指定准备增量备份
xtrabackup --prepare --apply-log-only --target-dir/backup/full --incremental-dir/backup/incremental停止MySQL服务
sudo systemctl stop mysql删除现有数据目录
rm -rf /var/lib/mysql/复制备份文件
xtrabackup --copy-back --target-dir/backup/full修改数据目录权限
chown -R mysql:mysql /var/lib/mysql重启MySQL服务
sudo systemctl start mysql优点
支持热备份不影响数据库的正常运行。支持增量备份节省存储空间。恢复速度快。
缺点
需要准备备份文件增加了一步操作。恢复过程中需要停止MySQL服务。
2、使用MySQL Enterprise Backup恢复
MySQL Enterprise Backup是Oracle提供的商业备份工具恢复过程与Percona XtraBackup类似但提供了更多企业级特性。
步骤 1、准备备份文件使用mysqlbackup --apply-log命令准备备份文件。 2、停止MySQL服务。 3、复制备份文件将准备好的备份文件复制回原始位置。 4、重启MySQL服务。
示例
准备全量备份
mysqlbackup --userroot --passwordyour_password --backup-dir/backup/full --apply-log prepare如果有增量备份同时也要准备增量备份
mysqlbackup --userroot --passwordyour_password --backup-dir/backup/full --incremental-backup-dir/backup/incremental --apply-log prepare停止MySQL服务
sudo systemctl stop mysql删除现有数据目录
rm -rf /var/lib/mysql/复制备份文件
mysqlbackup --userroot --passwordyour_password --backup-dir/backup/full --copy-back修改数据目录权限
chown -R mysql:mysql /var/lib/mysql重启MySQL服务
sudo systemctl start mysql优点
支持热备份不影响数据库的正常运行。支持增量备份节省存储空间。恢复速度快。提供自动备份调度和备份验证。
缺点
需要购买MySQL企业版许可证。
六、二进制日志恢复
MySQL的二进制日志Binary Log简称binlog记录了所有对数据库的更改操作如INSERT、UPDATE、DELETE等。通过二进制日志可以实现增量备份、主从复制和时间点恢复。
1、什么是二进制日志
二进制日志是MySQL中的一种日志文件记录了所有对数据库的更改操作。它以二进制格式存储因此不能直接阅读但可以通过工具解析。
二进制日志的主要用途包括
主从复制主库将二进制日志发送给从库从库根据日志重放这些操作保持与主库同步。增量备份在全量备份的基础上使用二进制日志进行增量备份减少备份时间和存储空间。时间点恢复通过二进制日志可以在某个特定的时间点恢复数据避免不必要的数据丢失。
2、启用二进制日志
要使用二进制日志首先需要在MySQL配置文件中启用它。编辑/etc/my.cnf或/etc/mysql/my.cnf文件添加或修改以下配置项。
示例
[mysqld]启用二进制日志
log_binmysql-bin设置二进制日志的格式STATEMENT、ROW 或 MIXED
binlog_formatROW设置二进制日志的过期天数可选
expire_logs_days7设置二进制日志的最大文件大小可选
max_binlog_size100M设置唯一的服务器 ID用于主从复制
server_id1解释
log_bin指定二进制日志的文件名前缀。默认情况下MySQL会将日志文件保存在数据目录中通常是/var/lib/mysql/。binlog_format设置二进制日志的格式。常见的格式有 STATEMENT记录SQL语句。ROW记录每一行的变化推荐使用尤其是对于InnoDB表。MIXED混合模式MySQL会根据情况自动选择STATEMENT或ROW。 expire_logs_days设置二进制日志的保留天数。超过这个天数的日志将被自动删除。max_binlog_size设置单个二进制日志文件的最大大小。当达到这个大小时MySQL会创建一个新的日志文件。server_id设置唯一的服务器ID用于主从复制。
重启MySQL服务 修改配置文件后重启MySQL服务以使配置生效
sudo systemctl restart mysql3、查看二进制日志文件
启用二进制日志后MySQL会在数据目录中生成一系列以mysql-bin.000001、mysql-bin.000002等命名的文件。
相关示例
1、查看二进制日志文件列表
SHOW BINARY LOGS;输出示例
2、查看当前正在使用的二进制日志文件
SHOW MASTER STATUS;输出示例 解释
File当前正在使用的二进制日志文件。Position当前日志文件中的写入位置。
4、备份二进制日志
为了确保数据的安全性建议定期备份二进制日志。
常见的备份方法
1、手动备份
你可以直接复制二进制日志文件到备份目录中。由于二进制日志文件是追加写入的因此可以安全地复制它们而不会影响MySQL的正常运行。
示例
创建备份目录
mkdir -p /backup/binlogs复制二进制日志文件
cp /var/lib/mysql/mysql-bin. /backup/binlogs/2、mysqlbinlog工具备份
mysqlbinlog是MySQL提供的一个工具用于解析和导出二进制日志文件。你可以使用它将二进制日志转换为SQL文件便于备份和恢复。
示例 1、将所有二进制日志导出为SQL文件
mysqlbinlog /var/lib/mysql/mysql-bin.000001 /backup/binlogs/mysql-bin.000001.sql
mysqlbinlog /var/lib/mysql/mysql-bin.000002 /backup/binlogs/mysql-bin.000002.sql2、指定时间范围的备份
mysqlbinlog --start-datetime2023-10-01 00:00:00 --stop-datetime2023-10-02 00:00:00 /var/lib/mysql/mysql-bin.000001 /backup/binlogs/mysql-bin.000001.sql解释
–start-datetime和–stop-datetime指定导出的时间范围。
3、指定行数的备份 mysqlbinlog --start-position1234 --stop-position5678 /var/lib/mysql/mysql-bin.000001 /backup/binlogs/mysql-bin.000001.sql解释
–start-position和–stop-position指定导出的位置范围。
4、从远程服务器进行复制备份
mysqlbinlog --read-from-remote-server --host192.168.1.100 --userroot --passwordyour_password /var/lib/mysql/mysql-bin.000001 /backup/binlogs/mysql-bin.000001.sql解释
–read-from-remote-server从远程服务器读取二进制日志适用于主从复制环境。
5、自动化备份
为了实现自动化的二进制日志备份可以编写一个脚本并结合cron定时任务来定期执行备份。
以下是一个简单的Shell脚本示例 第一步编写自动化脚本(backup_binlogs.sh)如下
#!/bin/bash# 配置变量
BACKUP_DIR/backup/binlogs
DATE$(date %Y%m%d_%H%M%S)
MYSQL_USERroot
MYSQL_PASSWORDyour_password# 创建备份目录如果不存在
mkdir -p $BACKUP_DIR# 获取当前的二进制日志文件列表
BINARY_LOGS$(mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e SHOW BINARY LOGS | awk NR1 {print $1})# 备份每个二进制日志文件
for LOG in $BINARY_LOGS; doFILENAME${BACKUP_DIR}/${LOG}_${DATE}.sql.gzecho Backing up binary log: $LOG to $FILENAME# 使用 mysqlbinlog 导出并压缩日志文件mysqlbinlog -u $MYSQL_USER -p$MYSQL_PASSWORD /var/lib/mysql/$LOG | gzip $FILENAMEif [ $? -eq 0 ]; thenecho Backup of $LOG completed successfully.elseecho Backup of $LOG failed.fi
done# 清理旧备份保留最近7天的备份
find $BACKUP_DIR -type f -name *.sql.gz -mtime 7 -exec rm {} \;第二步设置定时任务 1、打开crontab编辑器
crontab -e2、添加以下行每天凌晨2点执行备份脚本
0 2 * * * /path/to/backup_binlogs.sh /var/log/binlog_backup.log 21 3、保存并退出。 解释
0 2 * * * 表示每天凌晨2点执行。/path/to/backup.sh替换为你的备份脚本的实际路径。 /var/log/mysql_backup.log 21将输出和错误日志重定向到指定的日志文件。
6、清理二进制日志
随着时间的推移二进制日志文件会逐渐增多占用大量磁盘空间。因此定期清理不再需要的二进制日志是非常重要的。 你可以通过以下方式清理二进制日志
1、自动清理
通过配置expire_logs_days参数可以让MySQL自动清理超过指定天数的二进制日志。例如设置expire_logs_days7表示保留最近7天的二进制日志超过7天的日志将被自动删除。
2、手动清理
你可以使用PURGE BINARY LOGS命令手动清理二进制日志。
常见的清理方式
按文件名清理删除指定文件名之前的二进制日志。 sql示例
PURGE BINARY LOGS TO mysql-bin.000010;按日期清理删除指定日期之前的二进制日志。 sql示例
PURGE BINARY LOGS BEFORE 2023-10-01 00:00:00;清空所有二进制日志删除所有二进制日志并重置日志编号。请注意这会影响主从复制谨慎使用。 sql示例
RESET MASTER;7、使用二进制日志进行恢复
二进制日志可以用于增量恢复尤其是在全量备份的基础上。通过应用二进制日志中的操作可以将数据库恢复到某个特定的时间点。结合恢复全量备份和二进制日志恢复。
示例 假设你已经有一个过去的全量备份并且希望恢复到某个时间点。
1、恢复全量备份首先恢复最近的全量备份。
mysql -u root -p full_backup.sql2、应用二进制日志使用mysqlbinlog解析并应用二进制日志文件。你可以根据需要指定时间范围或位置范围。
mysqlbinlog --start-datetime2023-10-01 00:00:00 --stop-datetime2023-10-02 10:00:00 /var/lib/mysql/mysql-bin.000001 | mysql -u root -p解释 为什么要先进行全量备份的恢复呢 全量备份是数据库的一个完整副本包含了所有表、索引、视图等对象的数据。它是数据库恢复的基础提供了恢复过程中的“起点”。二进制日志记录了自全量备份之后的所有数据库更改操作。如果你有的只是二进制文件没有之前的备份文件那么恢复是无法进行的。简单说就是二进制文件的起点行不知道是哪一句增删改的语句甚至当前的数据库中表都不存在了。
过程推理说明 假设2025年的1月4日15点30分你不小心删除了某张重要表的数据。 第一步假设每天的0点都进行全量的数据库备份 你要先找到1月4日0点的全量备份文件。执行恢复此文件将数据库还原到1月4日0点的状态。 第二步 找到正在执行的binlog日志文件不确定可以指定最近的两个指定开始时间为1月4日0点结束时间会1月4日15点0分误删表的前一小段时间点。 这样就相当于在1月4日0点当时数据库的基础上重新执行了0点到15点这段时间内的所有增删改的语句从而实现了故障恢复的效果。
8、监控二进制日志
为了确保二进制日志的健康状态建议定期监控其使用情况。你可以使用以下查询来获取二进制日志的相关信息
查看二进制日志的状态 SHOW MASTER STATUS;运行结果
查看二进制日志的文件列表
SHOW BINARY LOGS;运行结果
查看二进制日志的事件 SHOW BINLOG EVENTS IN mysql-bin.000001;运行结果
监控二进制日志的大小 SELECT *FROM performance_schema.file_summary_by_instanceWHERE file_name LIKE %mysql-bin%;运行结果
乘风破浪会有时直挂云帆济沧海
