一.数据库备份
1.数据备份的重要性
-
备份的主要目的是灾难恢复
-
在生产环境中,数据的安全性至关重要
-
任何数据的丢失都可能产生严重的后果
5
造成数据丢失的原因 ♢程序错误
♢人为操作错误 ♢运算错误
♢磁盘故障 ♢不可控因素
2.数据库备份的方法
(1)从物理与逻辑的角度,备份分为(物理备份、逻辑备份)
物理备份: 对数据库操作系统的物理文件(如数据文件、日志文件等)的备份
逻辑备份:对数据库逻辑组件(如:表等数据库对象)的备份
物理备份方法
冷备份(脱机备份):是在关闭数据库的时候进行的
热备份(联机备份):数据库处于运行状态,依赖于数据库的日志文件
温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作
(2)从数据库的备份策略角度,备份可分为
-
完全备份:每次对数据库进行完整的备份
-
差异备份:备份自从上次完全备份之后被修改过的文件
-
增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份
(3)常见的备份方法
物理冷备
- 备份时数据库处于关闭状态,直接打包数据库文件
- 备份速度快,恢复时也是最简单的
专用备份工具mydump或mysqlhotcopy
- myaqldump常用的逻辑备份工具
- mysqlhotcopy仅拥有备份MyISM和ARCHIVE表
启用二进制日志进行增量备份
- 进行增量备份,需要刷新二进制日志
第三方工具备份
- 免费MySQL热备份软件Percona XtraBackup
3.MySQL完全备份
(1)了解MySQL完全备份
是对整个数据库,数据库结构和文件结构的备份
保存的是备份完成时刻的数据库
是差异备份与增量备份的基础
优点
- 备份与恢复操作简单方便
缺点
- 数据存在大量的重复
- 占用大量的备份空间
- 备份与恢复时间长
(2)数据库完全备份分类
物理冷备份与恢复
- 关闭MySQL数据库
- 使用tar命令直接打包数据库文件夹
- 直接替换现有MySQL目录即可
mysqldump备份与恢复
• Mysql自带的备份工具,可方便实现对MySQL的备份
• 可以将指定的库、表导出为SQL脚本
• 使用命令mysql导入备份的数据
4.MySQL增量备份
1.使用mysqldump进行完全备份存在的问题
• 备份数据中有重复数据
• 备份时间与恢复时间过长
2.是自上一次备份后增加/变化的文件或者内容
3.特点
• 没有重复数据,备份不大,时间短
• 恢复需要上次完全备份及完全备份之后所有的增量备份才能恢复,且要对所有增备份进行逐个反推恢复
4.MySQL没有提供直接的增量备份方法
5.可通过MySQL提供的二进制日志间接实现增量备份
6.MySQL二进制日志对备份的意义
- 二进制日志保存了所有更新或者可能更新数据库的操作
- 二进制日志在启动MySQL服务器后开始记录,并在文件达到max_ binlog_ size所设 置的大小或者接收到flush logs命令后重新创建新的日志文件
- 只需定时执行flush logs方法重新创建新的日志,生成二进制文件序列,并及时把这些日志保存到安全的地方就完成了-一个时间段的增量备份
5.MySQL数据库增量恢复
一般恢复
- 将所有备份的二进制日志内容全部恢复
基于位置恢复
-
数据库在某一时间点可能既有错误的操作也有正确的操作
-
可以基于精准的位置跳过错误的操作
基于时间点恢复
- 跳过某个发生错误的时间点实现数据恢复
二.日志管理
MySQL常用日志类型及开启
日志通常保存在 etc/my.cnf文件中
1.错误日志
指定日志的保存位置和文件名
log-error=/usr/local/mysql/data/mysql_error.log
#用来记录当MySQL启动、停止或运行时发生的错误信息,默认已开启
2.通用查询日志
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
#用来记录MySQL的所有连接和语句,默认是关闭的
3、二进制日志(binlog)
log-bin=mysql-bin
或
log_bin=mysql-bin
#用来记录所有当MySQL启动、停止或运行时发送的错误信息,默认是关闭的
4、慢查询日志
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5
systemctl restart mysqld
#用来记录所有执行时间超过long_query_time秒的语句,可以找到哪些查询语句执行时间长,以便于优化,
查看日志状态
1、查看通用查询日志是否开启
2、查看二进制日志是否开启
show variables like ‘log_bin%’;
3、查看慢查询日功能是否开启
show variables like ‘%slow%’;
4.查看慢查询时间设置
5.在数据库中设置开启慢查询的方法
set global slow_query_log=ON;
该方法重启服务失效
三.MySQL备份、恢复相关操作
物理备份
热备份(Hot Backup):热备份也称为在线备份(Online Backup),是指在数据库运行的过程中进行备份,对生产环境中的数据库运行没有任何影响。常见的热备方案是利用mysqldump、XtraBackup等工具进行备份。
冷备份(Cold Backup):冷备份也称为离线备份(Offline Backup),是指在数据库关闭的情况下进行备份,这种备份非常简单,只需要关闭数据库,复制相关的物理文件即可。目前,线上数据库一般很少能够接受关闭数据库,所以该备份方式很少使用。
温备份(Warm Backup):温备份也是在数据库运行的过程中进行备份,但是备份会对数据库操作有所影响。该备份利用锁表的原理备份数据库,由于影响了数据库的操作,故该备份方式也很少使用。
优点:
备份和恢复操作都比较简单,能够跨mysql的版本,恢复速度快,属于文件系统级别的。
冷备份与恢复
[root@localhost ~]# systemctl stop mysqld.service
[root@localhost ~]# mkdir /backup
[root@localhost ~]# tar zcf /backup/mysql_all_$(date +%F).tar.XZ /usr/local/mysql/data/
tar: 从成员名中删除开头的“/”
###关闭数据库,之后打包
[root@localhost ~]# mkdir bak
[root@localhost ~]# mv /usr/local/mysql/data/ /bak/
[root@localhost ~]# mkdir restore
[root@localhost ~]# tar zxf /backup/mysql_all_2022-06-06.tar.XZ -C restore/
[root@localhost ~]# mv restore/usr/local/mysql/data/ /usr/local/mysql/
[root@localhost ~]# systemctl start mysqld.service
恢复数据库,采用将备份数据mv成线上库文件夹的方式
逻辑备份是对数据库逻辑组件(如:表等数据库对象)的备份
逻辑备份是以文件为基本单位将数据从主机复制到备机
指对数据库逻辑组件(如表等数据库对象)的备份
从数据库的备份策略角度来看,备份又可分为完全备份、差异备份和增量备份
优点:
恢复简单。备份的结果为ASCII文件,可以编辑。与存储引擎无关。可以通过网络备份和恢复。
缺点:
备份或恢复都需要mysql服务器进程参与。备份结果占据更多的空间,浮点数可能会丢失精度。还原之后,缩影需要重建。
四、MySQL 完全备份与恢复
- 数据备份的重要性
备份的主要目的是灾难恢复
在生产环境中,数据的安全性至关重要
任何数据的丢失都可能产生严重的后果
造成数据丢失的原因
程序错误
人为操作错误.
运算错误
磁盘故障
灾难(如火灾、地震)和盗窃
3.数据库备份的分类
- 从物理和逻辑角度分
物理备份
对数据库操作系统的物理文件(如数据文件、日志文件等)的备份
物理备份的方法 :
冷备份(脱机备份) :是在关闭数据库的时候进行的;
热备份(联机备份) :数据库处于运行状态,依赖于数据库的日志文件;
温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作。
逻辑备份
对数据库逻辑组件(如:表等数据库对象)的备份
完全备份:每次对数据库进行完整的备份
是对整个数据库、数据库结构和文件结构的备份
保存的是备份完成时刻的数据库
是差异备份与增量备份的基础
优点:备份与恢复操作简单方便 缺点:数据存在大量的重复、占用大量的备份空间及备份与恢复时间长
差异备份:备份自从上次完全备份之后被修改过的文件
增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份
五、Mysqldump完全备份与恢复
1、备份文件
[root@localhost /]# systemctl stop mysqld.service
#关闭mysql服务
[root@localhost /]# mkdir /backup/
#创建备份目录
[root@localhost /]# rpm -q xz
#查看xz是否安装
[root@localhost /]# yum install xz -y
#安装xz工具
[root@localhost /]# tar Jcf /backup/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data
#打包数据文件并以日期为文件名
[root@localhost /]# cd /backup/
[root@localhost backup]# ls
2、完全恢复
[root@localhost backup]# scp /backup/mysql_all_2022-06-06.tar.xz 192.168.13.10:/opt
#scp 远程连接发送文件
#########主机2###########
[root@localhost opt]# systemctl stop mysqld.service
cd[root@localhost opt]# cd /opt
[root@localhost opt]# ls
[root@localhost opt]# mkdir /opt/bak/
[root@localhost opt]# tar jxf mysql_all_2022-06-06.tar.xz -C /opt/bak/
[root@localhost bak]# mysql -u root -p
Enter password:
#设置的密码
mysql> show databases;
#查看库
mysql>use wangbao;
#进入库
mysql> show tables ;
#查看表
mysql> select * from qqq;
#表内容
逻辑备份
mysqldump是常用的逻辑备份工具。
mysqldump可以将指定的库、表导出为sql脚本。
1.完全备份一个或多个库
mysqldump -uroot -p密码] --databases 库名1 [库名2] >备份路径/备份文件名.sql
#导出后就是数据库脚本文件
[root@localhost bak]# mysqldump -uroot -p --databases wangbao >/opt/mysql.sql
#完全备份一个库
[root@localhost bak]# mysqldump -uroot -p --databases wangbao yan >/opt/mysql.sql
#完全备份多个库
2.完全备份mysql服务器中的所有的库(包括库中的表)
mysqldump -u root -p --all-databases > /备份路径/备份文件名.sql
[root@localhost bak]# mysqldump -u root -p --all–databases >/opt/all.sql
3.完全备份指定库中的部分表
mysqldump -u root -p [密码] [-d] 库名 表名1 [表名2] … > /备份路径/备份文件名.sql
[root@localhost opt]# mysqldump -u root -p wanbao qqq >/opt/qqq.sql
4.查看备份文件
[root@localhost opt]# cat qqq.sql |grep -v “^–” |grep -v “^/” |grep -v “^$”
#过滤-- / 以及空行为开头的行
5.mysqldump完全恢复
启动mysql
[root@localhost opt]# systemctl start mysqld
[root@localhost opt]# mysql -u root -p -e ‘drop database wangbao’;
#"-e"选项,用于指定连接mysql后执行命令,命令执行完后自动退出
[root@localhost opt]# mysql -u root -p -e ‘SHOW DATABASES’;
###恢复########
[root@localhost opt]# mysql -u root -p < /opt/mysql.sql
[root@localhost opt]# mysql -u root -p ‘SHOW DATABASES’
6.恢复数据表
#####备份表####
[root@localhost opt]# mysqldump -u root -p wangbao qqq > /opt/mysql/wb_qqq.sql
[root@localhost opt]# mysql -u root -p -e ‘drop table wangbao.qqq;’
[root@localhost opt]# mysql -u root -p wangbao < /opt/mysql/wb_qqq.sql
六、MySQL增量备份与恢复
1.mysql增量备份特点
特点:通过二进制日志间接实现(flush logs刷新日志),没有冗余、备份时间短、恢复时间长
2.备份类别
现在/etc/my.cnf中[mysqld]子配置中设置log-cin=mysql-bin开启二进制日志
然后再:binlog_format=MIXED指定二进制的记录格式为混合模式
STAEMENT 基于sql语句备份,较快,高并发是容易出错(默认是这个类型)
ROW 基于数据库行内容备份,较慢,安全准确
MIXED 混合模式,兼有两者优点,生产环境一般都用这个
3.备份命令
mysqladmin -u root -p[密码] flush-logs
#备份日志
mysqladmin --no-defaults --base64 -output=decode-rows -v 备份路径
#查看备份日志
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# systemctl restart mysqld.service
[root@localhost ~]# ls -l /usr/local/mysql/data/mysql-bin.*
[root@localhost ~]# mysqladmin -u root -p flush-logs
Enter password:
##备份日志文件
[root@localhost ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000002
####查看备份日志
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1/;
/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
DELIMITER /!/;
#at 4
#220607 14:44:30 server id 1 end_log_pos 123 CRC32 0xc90356af Start: binlog v 4, server v 5.7.20-log created 220607 14:44:30
#Warning: this binlog is either in use or was not closed properly.
#at 123
#220607 14:44:30 server id 1 end_log_pos 154 CRC32 0xc0200398 Previous-GTIDs
#[empty]
SET @@SESSION.GTID_NEXT= ‘AUTOMATIC’ /* added by mysqlbinlog / /!/;
DELIMITER ;
#End of log file
/!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0/;
4.恢复类别
类别 命令
一般恢复 mysqlbinlog --no-defaults 备份路径
位置恢复 mysqlbinlog --no-defaults --start-position=‘位置点’ --stop-position=‘位置点’ 备份路径
时间恢复 mysqlbinlog --no-defaults --start-datetime=‘时间点’ --stop-datetime=‘时间点’ 备份路径
一般恢复
mysql> mysql> insert into qqq values(‘saddsa’, ‘男’,’ ') ;
Query OK, 1 row affected (0.00 sec)
mysql> insert into qqq values(‘sgagasgsa’, ‘男’,’ ') ;
Query OK, 1 row affected (0.00 sec)
##删除数据
[root@localhost ~]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000002
###日志恢复###
mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000001 | mysql -u root -p
###日志会保存在上一份日志中
位置恢复
[root@localhost ~]# mysqlbinlog --no-defaults --stop-datetime=‘25955’ /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p
时间恢复
[root@localhost ~]# mysqlbinlog --no-defaults --start-datetime=‘2022-6-7 16:04:04’ --stop-datetime=‘2022-6-7 16:09:02’ /usr/local/mysql/data/mysql-bin.000002 |mysql -u root -p