Mysql数据备份

---

备份类型

数据备份类型：

1. 全量备份（Full Backup）:

• 备份整个数据集,不管数据是否发生变化。
• 全量备份后,后续的备份可以是增量或差异备份。
• 全量备份需要更多的时间和存储空间。

2. 增量备份（Incremental Backup）:

• 只备份自上次备份以来发生变化的数据部分。
• 增量备份后续可以继续进行增量备份。
• 增量备份速度快,但需要更复杂的还原过程。

3. 差异备份（Differential Backup）:

• 备份自上次全量备份以来发生变化的数据部分。
• 差异备份比增量备份需要更多空间,但还原更简单。
• 差异备份后仍需要全量备份作为基础。

增量备份

还原规则就是先备份先还原。

差异备份
仅备份最近一次完全备份以来变化的数据或者增长的数据，备份较慢，还原简单，直接还原最新的备份。

注意
二进制日志文件不应该与数据文件放在同一磁盘冷、温、热备份。

• 冷备：读、写操作均不可进行，数据库停止服务。

• 温备：读操作可执行；但写操作不可执行。

• 热备：读、写操作均可执行 。

MyISAM：温备，不支持热备
InnoDB：都支持

备份数据方式

• cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份。
• LVM的快照：先加读锁，做快照后解锁，几乎热备；借助文件系统工具进行备份。
• mysqldump：逻辑备份工具，适用所有存储引擎，对MyISAM存储引擎进行温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份。
• xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份。
• MariaDB Backup： 从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现。
• mysqlbackup：热备份， MySQL Enterprise Edition 组件。
• mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。

完全备份

物理冷备份

通过打包备份，数据库文件夹的方式备份。（一般用于数据库迁移）
备份方式：冷备份 ，一定要先停数据库。
备份工具：cp tar等 scp rsync（同步软件）
InnoDB存储引擎的数据库在磁盘上存储成三个文件：db.opt(表属性文件)、表名.frm(表结构文件)、表名.ibd(表数据文件)。

操作实例：

systemctl stop mysqld
scp -r  /var/lib/mysql/    192.168.232.10:/data/
将整个数据包打包过去。
如果修改了配置文件，配置文件也需要一起备份。模拟破坏数据
rm -rf /var/lib/mysqlchown -R mysql.mysql mysql
修改文件的属组属主

恢复成功。

mysqldump备份和恢复

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指令开启事务。

–single-transaction 选项在 mysqldump 命令中的主要作用如下:

1. 保证数据一致性:

• 在备份 InnoDB 引擎的数据库时,使用 --single-transaction 选项可以保证备份过程中数据库处于一致状态。
• 这是因为 --single-transaction 会启动一个事务,并在事务过程中执行 FLUSH TABLES WITH READ LOCK 操作。这样可以确保在备份过程中数据库处于一个时间点的状态,不会出现部分数据变更而其他数据未变更的情况。

2. 避免表锁定:

• 传统的备份方式需要对数据库表进行全局读锁定,这会阻止其他用户对数据库进行读写操作。
• 使用 --single-transaction 选项可以避免对表进行全局读锁定,因为它使用了 InnoDB 的快照机制来备份数据。这样可以最大程度地减少对正常业务的影响。

3. 提高备份效率:

• 相比于全局读锁定,–single-transaction 选项下的备份过程不会阻塞 DML
  操作(INSERT/UPDATE/DELETE)。这样可以提高整个备份过程的效率。

需要注意的是:
在备份过程中,不能有任何 DDL 操作(CREATE/ALTER/DROP TABLE)发生,否则可能会导致备份失败。此选项和–lock-tables（此选项隐含提交挂起的事务）选项是相互排斥,备份大型表时，建议将–single-transaction选项和–quick结合一起使用。

备份脚本例子

#!/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

问题：如果仅仅是数据库内部一张表出现问题，我们却需要还原所有的表格，这样很不方便，也不实际。所以我们需要分表分库备份。

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
donecrontab  -e
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin 
0 1 * * *   /bin/bash    /data/backup
注意mysqldump是否可以执行。

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

增量备份恢复

增量备份是在完全备份的基础上进行的备份。因为mysqldump没有专门的增量备份的选项，所以借助二进制文件来使用。

开启二进制日志功能

vim /etc/my.cnf
[mysqld]
log-bin = /data/mysql/mysql-bin
server-id = 1
binlog_format = STATEMENT 
指定二进制日志记录的格式，可以不写，有默认选项。
注意需要加权限。

基本的备份流程
每周日可以对数据库或表进行完全备份，然后每天进行增量备份操作。

mysqladmin -u root -p flush-logs

此条命令是生成新的二进制日志文件。

mysqlbinlog --no-defaults --base64-output=decode-rows -v  /opt/mysql-bin.000002
--base64-output=decode-rows：使用64位编码机制去解码并按行读取
-v：显示详细内容

断点恢复

#at 302
#201122 16:41:16
插入了“user3”的用户数据#at 623
#201122 16:41:24
插入了“user4”的用户数据（1）基于位置恢复
仅恢复到操作 ID 为“623”之前的数据，即不恢复“user4”的数据
mysqlbinlog --no-defaults --stop-position='623' /opt/mysql-bin.000002 | mysql -uroot -p仅恢复“user4”的数据，跳过“user3”的数据恢复
mysqlbinlog --no-defaults --start-position='682' /opt/mysql-bin.000002 | mysql -uroot -p（2）基于时间点恢复
仅恢复到 16∶41∶24 之前的数据，即不恢复“user4”的数据
mysqlbinlog --no-defaults --stop-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 |mysql -uroot -p仅恢复“user4”的数据，跳过“user3”的数据恢复
mysqlbinlog --no-defaults --start-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 |mysql -uroot -p

如果恢复某条SQL语句之前的所有数据，就stop在这个语句的位置节点或者时间点。
如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或者时间点start。

实战案例

环境：
每天2：30做完全备份，早上10：00误删除了表students，10：10才发现故障，现需要将数据库还原到10：10的状态，且恢复被删除的students表。

 mysqldump -uroot -p123123 -A -F --single-transaction --master-data=2 > /opt/all.sql执行完全备份。

完全备份后数据更新

模拟误删

继续将表更新

insert teachers (name,age,gender)values('test',30,'M');insert teachers (name,age,gender)values('test1',30,'M');

发现数据库变动
准备恢复备份。

grep '\-\- CHANGE MASTER TO'  /opt/all.sql

备份 完全备份后的二进制日志

mysqlbinlog --start-position=154 /data/mysql/mysql-bin.000002 > /opt/inc.sql

找到删除的语句

grep -i "^drop table"  /opt/inc.sql 
sed -i.bak '/^DROP TABLE/d'  /opt/inc.sql 

登录数据库还原
未还原之前

先还原完备，在执行二进制的日志。

source     /opt/all.sql
source     /opt/inc.sql

注意：进行备份还原时，先关闭二进制。临时关闭。