你的位置:首页 > 信息动态 > 新闻中心
信息动态
联系我们

MySQL备份和恢复

2021-11-19 5:20:56

文章目录

  • 一、备份恢复概述
    • 1.1 备份作用
    • 1.2 备份的类型
    • 1.3 备份的对象
    • 1.4 备份注意要点
    • 1.5 还原要点
    • 1.6 备份工具
  • 二、备份实战
    • 2.1 数据库冷备份和还原
    • 2.2 mysqldump备份工具
    • 2.3 生产环境备份策略
    • 5.3 实战
      • 5.3.1 特定数据库的备份脚本
      • 5.3.2 分库备份并压缩

一、备份恢复概述

1.1 备份作用

灾难恢复:硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景

1.2 备份的类型

  • 完全备份:备份整个数据库
  • 部分备份: 备份数据库子集,例如部分库或表
  • 增量备份:仅备份最近一次完全备份或增量备份(如果存在增量)以来变化的数据,备份较快,还原复杂
  • 差异备份:仅备份最近一次完全备份以来变化的数据,备份较慢,还原简单

增量备份结构图:
在这里插入图片描述
还原方式,先还原第一天,然后还原第二天,以此类推。

差异备份结构图:
在这里插入图片描述
还原方式,只需要还原第一天和最后一天即可。

  • 冷、温、热备份

    • 冷备:读、写操作均不可进行,数据库停止服务
    • 温备:读操作可执行;但写操作不可执行
    • 热备:读、写操作均可执行
      MyISAM:冷备、温备,不支持热备
      InnoDB:都支持
  • 物理和逻辑备份

    • 物理备份:直接复制数据文件进行备份,与存储引擎有关,占用较多的空间,速度快
    • 逻辑备份:从数据库中"导出"数据另存而进行的备份,与存储引擎无关,占用空间少,速度慢,可能丢失精度

1.3 备份的对象

  • 数据
  • 二进制日志、InnoDB的事务日志
  • 用户帐号,权限设置,程序代码(存储过程、函数、触发器、事件调度器)
  • 服务器的配置文件

1.4 备份注意要点

  • 能容忍最多丢失多少数据
  • 备份产生的负载
  • 备份过程的时长
  • 温备的持锁多久
  • 恢复数据需要在多长时间内完成
  • 需要备份和恢复哪些数据

1.5 还原要点

  • 做还原测试,用于测试备份的可用性
  • 还原演练,写成规范的技术文档

1.6 备份工具

  • 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来快速备份数据库

二、备份实战

2.1 数据库冷备份和还原

1.停止mysql服务
[root@rocky8 my.cnf.d]# systemclt stop mysqld
2.备份数据
[root@rocky8 mysql]# cd / && tar -czf  /opt/mysql_`date +%F`.tar.gz  /var/lib/mysql/*

[root@rocky8 /]# ll /opt
total 9700
-rw-r--r--. 1 root root 9932094 Nov 15 21:00 mysql_2021-11-15.tar.gz

#如果配置及二进制文件相关有特殊设置也需要备份
# 还原,只需要找另外一台机器 将tar包解压到/var/lib/mysql/目录下即可

2.2 mysqldump备份工具

mysqldump是MySQL的客户端命令,通过mysql协议连接至mysql服务器进行备份
命令格式:

1.mysqldump [OPTIONS] database [tables]   #支持指定数据库和指定多表的备份,
但数据库本身定义不备份,不推荐使用

2.mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]  #支持指定数据库备份,包含
数据库本身定义也会备份 --databases等价于-B

3.mysqldump [OPTIONS] --all-databases [OPTIONS]  #备份系统数据库mysql和用户自建的
所有数据库,包含数据库本身定义也会备份 --all-databases等价于-A

mysqldump官方参考文档

-u --user=name user for login if not current user # 指定用户名
-p, --password[=name] Password to use when connecting to server. # 指定密码
-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 选项时,会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新,可通过和--singletransaction或-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     #不缓存查询,直接输出,加快备份速度

–master-data使用

首先需要开启二进制日志,mysql8默认已经开启
1.查看当前二进制位置

mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |     11042 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)

2.备份

# 相当于进行了一次全量备份
[root@rocky8 ~]# mysqldump -uroot -p123 -A --master-data=1 > /tmp/full-master-data.sql

3.查看备份的sql文件,发现第一行加上了二进制日志的起始位置

[root@rocky8 ~]# vim /tmp/full-master-data.sql 
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=11042;

如果 --master-data=2,然后备份的区别是上面一行也生成,只不过是被注释掉的
4.模拟真实环境,给数据库增加记录

mysql> insert into teachers (name,age,gender) values ('马云',18,'M'),('马斯克',20,'M');

查看数据库状态
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | 马云          |  18 | M      |
|   6 | 马斯克        |  20 | M      |
+-----+---------------+-----+--------+

5.重新查看二进制位置

mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |     11361 | No        |
+---------------+-----------+-----------+

6.备份binlog文件 生成增量备份

[root@rocky8 mysql]# mysqlbinlog /var/lib/mysql/binlog.000001 --start-position=11042 > /tmp/inc.sql

6.模拟故障,手动删除/var/lib/hellodb文件夹,即删库

7.退出并重新登录mysql 执行命令

mysql> source /tmp/full-master-data.sql
mysql> source /tmp/inc.sql


# 发现数据竟tm的又回来了
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | 马云          |  18 | M      |
|   6 | 马斯克        |  20 | M      |
+-----+---------------+-----+--------+

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结合一起使用

2.3 生产环境备份策略

InnoDB建议备份策略

mysqldump -uroot -p -A -F -E -R --triggers --single-transaction --master-data=1
--flush-privileges --default-character-set=utf8 --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql

MyISAM建议备份策略

mysqldump -uroot -p -A -F -E -R -x --master-data=1 --flush-privileges  --
triggers  --default-character-set=utf8  --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql

5.3 实战

5.3.1 特定数据库的备份脚本

#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
DB=hellodb
PASSWD=123
[ -d $DIR ] || mkdir $DIR
mysqldump -uroot -p$PASSWD -F -E -R --triggers  --single-transaction --master-data=2 --default-character-set=utf8 -q  -B $DB | gzip > ${DIR}/${DB}_${TIME}.sql.gz

5.3.2 分库备份并压缩

[root@rocky8 ~]# cat mysq_backup1.sh
TIME=`date +%F_%H-%M-%S`
DIR=/backup

PASS=123
for db in `mysql -uroot -p$PASS -e 'show databases'|egrep -v '^(Database|sys|information_schema|performance_schema)$'`;
do 
	mysqldump -uroot -p$PASS -B $db | gzip >  ${DIR}/${DB}_${TIME}.sql.gz
;
done