数据库备份是将数据库中的数据和结构以某种格式保存在一个文件中的过程。备份的目的是为了在数据丢失或者数据库发生故障时能够恢复数据
一.数据库备份类型
-
完全备份 (Full Backup)：
完全备份是将整个数据库的所有数据和结构备份到一个文件中的过程。这种备份方式包含数据库中的所有表、索引、存储过程等。完全备份通常是最基本和最常见的备份类型。 -
增量备份 (Incremental Backup)：
增量备份是相对于上一次完全备份或增量备份所做的备份。它只备份自上次备份以来发生变化的数据和日志。增量备份可以节省存储空间和备份时间，但恢复时需要依次应用每个增量备份。
-
差异备份 (Differential Backup)：
差异备份是相对于上一次完全备份所做的备份，它备份自上次完全备份以来发生变化的数据。与增量备份不同的是，差异备份不考虑上一次备份之后的修改，而是基于上一次完全备份的状态。
-
事务日志备份 (Transaction Log Backup)：
事务日志备份是备份数据库事务日志文件的一种方式。事务日志记录了数据库中每个事务的所有操作，包括对数据的修改。事务日志备份用于实现点到点的恢复，通常与完全备份或增量备份一起使用。 -
冷备份和热备份和温备份：
冷备份：是在数据库关闭状态下进行的备份，这样可以确保备份数据的完整性，但是需要停止数据库服务。读、写操作均不可进行，数据库停止服务
热备份：是在数据库运行时进行的备份，允许用户继续访问数据库。热备份通常依赖数据库管理系统的特定功能或者工具来确保备份的一致性。
温备份：读操作可执行；但写操作不可执行
二.备份工具的主要特点和适用情况
-
cp, tar等复制归档工具：
类型：物理备份工具。
适用引擎：所有存储引擎。
备份类型：支持完全备份和部分备份。
特点：简单易用，但只支持冷备（数据库关闭状态）。 -
LVM的快照：
类型：物理备份工具。
适用引擎：所有存储引擎。
备份类型：支持完全备份。
特点：几乎热备，能够在数据库运行时进行备份，借助文件系统工具进行备份管理。 -
mysqldump：
类型：逻辑备份工具。
适用引擎：所有存储引擎，对MyISAM存储引擎进行温备（不需要锁表）。
备份类型：支持完全备份和部分备份。
特点：适合小到中型数据库，可以在数据库运行时进行备份，但在大型数据库中效率可能较低。 -
xtrabackup：
类型：物理备份工具。
适用引擎：主要针对InnoDB存储引擎。
备份类型：支持完全备份和增量备份。
特点：由Percona提供支持，能够进行热备份，效率高。 -
MariaDB Backup：
类型：物理备份工具。
适用引擎：主要针对MariaDB数据库。
备份类型：基于Percona XtraBackup，支持完全备份和增量备份。 -
mysqlbackup：
类型：热备份工具，MySQL Enterprise Edition 组件。
适用引擎：通常用于MySQL Enterprise Edition环境。
备份类型：支持热备份操作。 -
mysqlhotcopy：
类型：物理备份工具。
适用引擎：仅适用于MyISAM存储引擎。
备份类型：几乎冷备，使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。
三.完全备份与恢复
1.物理冷备份
物理备份是指直接复制数据库文件、文件系统或者存储设备的备份方式。通过打包备份 数据库文件夹的方式备份（一般用于数据库迁移）一定要先停数据库
这种备份方式比较快速，适合大型数据库。
典型的物理备份包括文件系统级别的备份、数据库快照、二进制日志备份等。
备份工具： cp tar 等 scp rsync （同步软件）
InnoDB存储引擎的数据库在磁盘上存储成三个文件：db.opt(表属性文件)、表名.frm(表结构文件)、表名.ibd(表数据文件)。
示例：
终端1：192.168.240.14
systemctl stop mysqld
scp -r /var/lib/mysql 192.168.240.13:/data/
scp /etc/my.cnf 192.168.240.13:/etc/
#如果修改了配置文件需要一起恢复
终端2：192.168.240.13
ls /data/mysql
在终端2上查看传输到/data下的数据
rm -rf /var/lib/mysql #模拟数据库被删除
#将终端1上的mysql数据库删除后先不要启动mysql服务
在终端2上将文件传输回终端1的/var/lib下
将/var/lib/mysql/文件的属主属组改为mysql后再启动
scp -r /data/mysql 192.168.240.14:/var/lib/
chown -R mysql.mysql /var/lib/mysql/
systemctl start mysqld
mysql -u -p #登录数据库
show databases;
#查看数据库中的库是否恢复
2.mysqldump 备份与恢复
mysqldump [OPTIONS] database [tables]
#选择数据库 也可以选择数据库和表 只能备份表
mysqldump [OPTIONS] -B DB1 [DB2 DB3...]
#-B 可以选择多个数据库 可以备份数据库
mysqldump [OPTIONS] -A [OPTIONS]
#备份所有数据库 也可以备份数据库mysqldump 常见通用选项：
| 选项 | 含义 |
|---|---|
| -A, --all-databases | #备份所有数据库，含create database |
| -B, --databases db_name… | #指定备份的数据库，包括create database语句 |
| -E, --events： | #备份相关的所有event scheduler |
| -R, --routines： | #备份所有存储过程和自定义函数 |
| --triggers： | #备份表相关触发器，默认启用,用--skip-triggers，不备份触发器 |
| --default-character-set=utf8 | #指定字符集 |
| --master-data[=#]： | #此选项须启用二进制日志 #1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合于主从复 制多机使用 #2：记录为被注释的#CHANGE MASTER TO语句，适合于单机使用,适用于备份还原 #此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启-- single-transaction） |
| -F, --flush-logs | #备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件， 配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--single transaction或-x，--master-data 一起使用实现，此时只刷新一次二进制日志 |
| --compact | #去掉注释，适合调试，节约备份占用的空间,生产不使用 |
| -d, --no-data | #只备份表结构,不备份数据,即只备份create table |
| -t, --no-create-info | #只备份数据,不备份表结构,即不备份create table |
| -n,--no-create-db | #不备份create database，可被-A或-B覆盖 |
| --flush-privileges | #备份mysql或相关时需要使用 |
| -f, --force | #忽略SQL错误，继续执行 |
| --hex-blob | #使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY， BLOB，BIT的数据类型的列时使用，避免乱码 |
| -q, --quick | #不缓存查询，直接输出，加快备份速度 |
mysqldump的MyISAM存储引擎相关的备份选项：
MyISAM不支持事务，只能支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作
-x,--lock-all-tables #加全局读锁，锁定所有库的所有表，同时加--single-transaction或--lock-tables选项会关闭此选项功能，注意：数据量大时，可能会导致长时间无法并发访问数据库
-l,--lock-tables #对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
#注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用
mysqldump的InnoDB存储引擎相关的备份选项：
InnoDB 存储引擎支持事务,可以利用事务的相应的隔离级别,实现热备，也可以实现温备但不建议用
--single-transaction
#此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务
#此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE,此选项和--lock-tables（此选项隐含提交挂起的事务）选项是相互排斥,备份大型表时，建议将--single-transaction选项和--quick结合一起使用
实战备份示例：
（1）完全备份指定库中的部分表
mysqldump -u root -p[密码] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql
mysqldump -uroot -p'123' hellodb students > /data/students.sql
#备份单表 #只保留表结构 -d
ls
students.sql
mysql -uroot -p123
drop table students;
恢复表
mysql -uroot -p'123' hellodb < /data/students.sql
（2）完全备份一个或多个完整的库
mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql
#导出的就是数据库脚本文件
mysqldump -uroot -p'123123' -B hellodb > /data/hellodb.sql
#备份库
mysql -uroot -p123123
drop database hellodb;
#删除库
恢复库
mysql -uroot -p'123123' < /data/hellodb.sql
mysql -uroot -p123123
show databases;
（3）完全备份 MySQL 服务器中所有的库
mysqldump -uroot -p'123123' -A > /data/all_data.sql
#备份所有库
drop database 库名;
mysql -uroot -p'123123' -e 'drop database 库名;'
#删除多个库
恢复库
mysqldump -uroot -p'123123' < /data/all_data.sql
mysql -uroot -p'123123' -e 'show databases;'
#查看所有的库 -e 免交互
cat /data/all_data.sql | grep -i '^create database'
#查看所有备份的库 -i 忽略大小写
生产备份innodb策略
mysqldump -uroot -pabc123 -A -F -E -R --triggers --single-transaction --master-data=1 >${BACKUP}/fullbak_${BACKUP_TIME}.sql
#-F 生成新的二进制日志文件脚本示例：
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
#时间为 年 月 日 时 分 秒
DIR=/backup
DB=hellodb
PASS=test
[ -d $DIR ] || mkdir $DIR
mysqldump -uroot -p "$PASS" -F -E -R --triggers --single-transaction --master-data=2 --default-character-set=utf8 -q -B $DB | gzip > ${DIR}/${DB}_${TIME}.sql.gz生产例子分表分库备份
for db in `mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'`;do mysqldump -B $db | gzip > /backup/$db.sql.gz;done
crontab -e
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
0 1 * * * /bin/bash /data/backup
vim backup.sh
for db in `mysql -uroot -pabc123 -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'`
do mysqldump -B $db | gzip > /backup/$db.sql.gz
done
mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'|while read db;do mysqldump -
B $db | gzip > /backup/$db.sql.gz;done
mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$' | sed -rn 's#(.*)#mysqldump -B \1 | gzip > /backup/\1.sql.gz#p' |bash
四.增量备份与恢复
增量备份和恢复是一种备份策略，通常与完全备份结合使用，以减少备份数据的量和时间。
增量备份只备份自上次备份以来发生变化的数据。这种备份策略相对完全备份来说，可以节省存储空间和备份时间，但在恢复时需要更多的步骤。
注意事项：
增量备份需要数据库使用二进制日志（binlog）来记录每个数据变更。
完全备份通常是周期性执行的，如每天或每周，而增量备份可以更频繁地执行，如每几小时一次
恢复增量备份
恢复增量备份通常需要使用完全备份和一系列的增量备份文件。
注意事项：
确保在恢复时按照备份的顺序依次应用增量备份文件。
增量备份和恢复过程比较复杂，需要谨慎操作，确保数据的完整性和一致性
实战案例：
每天2：30做完全备份，早上10：00误删除了表students，10：10才发现故障，现需要将数据库还原到10：10的状态，且恢复被删除的students表
1.开启二进制日志
vim /etc/my.cnf
log_bin=/data/mysql-bin
server-id = 1
chown mysql.mysql /data/ -R
systemctl restart mysqld2. 2：30执行完全备份
在执行完全备份前需要先关闭二进制日志
进入数据库内
set sql_log_bin=0; #先关闭二进制日志 临时关闭
mysqldump -uroot -p'123123' -A -F --single-transaction --master-data=2 > /data/all.sql
set sql_log_bin=1; #开启二进制备份
#完全备份后数据更新
insert students (name,age,gender) values('cxk',20,'M');
insert students (name,age,gender) values('cxk1',20,'M');
##############10:00 误删除了一个students的表
drop table students;
###########后续其余的表继续更新
insert teachers (name,age,gender)values('test',30,'M');
insert teachers (name,age,gender)values('test1',30,'M');
#####10点10分发现进行还原
grep '\-\- CHANGE MASTER TO' /data/all.sql
#从完全备份中，找到二进制位置
#备份 完全备份后的二进制日志
mysqlbinlog --start-position=154 /data/mysql-bin.000002 > /data/inc.sql
#找到 删除的语句
grep -in "^drop table" /data/inc.sql # n 显示行号
DROP TABLE `students` /* generated by server */
#删除 删表的那一行
sed -i.bak '行号d' /data/inc.sql
进入mysql，关闭二进制日志
加载文件
首先加载所有备份all.sql
source /data/all.sql
其次加载inc.sql
source /data/inc.aql
select * from teachers;
select * from students;
1018
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
