数据库备份是将数据库中的数据和结构以某种格式保存在一个文件中的过程。备份的目的是为了在数据丢失或者数据库发生故障时能够恢复数据

一.数据库备份类型

1. 完全备份 (Full Backup)： 完全备份是将整个数据库的所有数据和结构备份到一个文件中的过程。这种备份方式包含数据库中的所有表、索引、存储过程等。完全备份通常是最基本和最常见的备份类型。
2. 增量备份 (Incremental Backup)： 增量备份是相对于上一次完全备份或增量备份所做的备份。它只备份自上次备份以来发生变化的数据和日志。增量备份可以节省存储空间和备份时间，但恢复时需要依次应用每个增量备份。
   
3. 差异备份 (Differential Backup)： 差异备份是相对于上一次完全备份所做的备份，它备份自上次完全备份以来发生变化的数据。与增量备份不同的是，差异备份不考虑上一次备份之后的修改，而是基于上一次完全备份的状态。
   
4. 事务日志备份 (Transaction Log Backup)： 事务日志备份是备份数据库事务日志文件的一种方式。事务日志记录了数据库中每个事务的所有操作，包括对数据的修改。事务日志备份用于实现点到点的恢复，通常与完全备份或增量备份一起使用。
5. 冷备份和热备份和温备份： 冷备份：是在数据库关闭状态下进行的备份，这样可以确保备份数据的完整性，但是需要停止数据库服务。读、写操作均不可进行，数据库停止服务
   热备份：是在数据库运行时进行的备份，允许用户继续访问数据库。热备份通常依赖数据库管理系统的特定功能或者工具来确保备份的一致性。
   温备份：读操作可执行；但写操作不可执行

二.备份工具的主要特点和适用情况

1. cp, tar等复制归档工具： 类型：物理备份工具。
   适用引擎：所有存储引擎。
   备份类型：支持完全备份和部分备份。
   特点：简单易用，但只支持冷备（数据库关闭状态）。
2. LVM的快照： 类型：物理备份工具。
   适用引擎：所有存储引擎。
   备份类型：支持完全备份。
   特点：几乎热备，能够在数据库运行时进行备份，借助文件系统工具进行备份管理。
3. mysqldump： 类型：逻辑备份工具。
   适用引擎：所有存储引擎，对MyISAM存储引擎进行温备（不需要锁表）。
   备份类型：支持完全备份和部分备份。
   特点：适合小到中型数据库，可以在数据库运行时进行备份，但在大型数据库中效率可能较低。
4. xtrabackup： 类型：物理备份工具。
   适用引擎：主要针对InnoDB存储引擎。
   备份类型：支持完全备份和增量备份。
   特点：由Percona提供支持，能够进行热备份，效率高。
5. MariaDB Backup： 类型：物理备份工具。
   适用引擎：主要针对MariaDB数据库。
   备份类型：基于Percona XtraBackup，支持完全备份和增量备份。
6. mysqlbackup： 类型：热备份工具，MySQL Enterprise Edition 组件。
   适用引擎：通常用于MySQL Enterprise Edition环境。
   备份类型：支持热备份操作。
7. 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 常见通用选项：

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'' -B hellodb > /data/hellodb.sql #备份库 mysql -uroot -p drop database hellodb; #删除库

恢复库 mysql -uroot -p'' < /data/hellodb.sql mysql -uroot -p show databases;

（3）完全备份 MySQL 服务器中所有的库

mysqldump -uroot -p'' -A > /data/all_data.sql #备份所有库 drop database 库名; mysql -uroot -p'' -e 'drop database 库名;' #删除多个库 恢复库 mysqldump -uroot -p'' < /data/all_data.sql mysql -uroot -p'' -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 

四.增量备份与恢复

增量备份和恢复是一种备份策略，通常与完全备份结合使用，以减少备份数据的量和时间。

增量备份只备份自上次备份以来发生变化的数据。这种备份策略相对完全备份来说，可以节省存储空间和备份时间，但在恢复时需要更多的步骤。

注意事项：

恢复增量备份

恢复增量备份通常需要使用完全备份和一系列的增量备份文件。

注意事项：

实战案例：

每天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 mysqld

2.  2：30执行完全备份

在执行完全备份前需要先关闭二进制日志

进入数据库内 set sql_log_bin=0; #先关闭二进制日志 临时关闭 mysqldump -uroot -p'' -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;