目录
一、备份和恢复
1、备份:
2、备份的方法:
2.1物理备份:
2.2、逻辑备份
2.3增量备份:
一、备份和恢复
1、备份:
先备份再恢复
备份:完全备份,增量备份
完全备份:将整个数据库完整的进行备份
增量备份:在完全备份的基础之上,对后续新增的内容进行备份
备份的需求:
- 在生产环境中,数据的安全至关重要,任何数据的丢失都可能产生严重的后果。
- 数据为什么会丢失?可能是程序操作,运算错误,磁盘故障,不可预期的事件(地震之类),人为操作等。
2、备份的方法:
冷备份:关机备份,停止mysql服务,然后进行备份
热备份:开机备份,无需关闭mysql服务,然后进行备份
物理备份:对数据库系统的物理文件(数据文件,日志文件,)进行备份
逻辑备份:只是对数据库的逻辑组件进行备份(表结构),以sql语句的形式,把库,表结构以及表数据进行备份保存。(直接在数据库系统中删除全部文件,逻辑备份无法恢复)
2.1物理备份:
一般采用完全备份,对整个数据库进行完整的打包备份
优点:操作简单
缺点:数据库文件占用量是很大的,占用空间太大,备份和恢复的时间都很长,而且需要暂停数据库服务
创建两个库,两张表
安装打包软件:
yum -y install xz
恢复必须建立在备份的基础上
打包备份:
#压缩备份 tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
将/usr/local/mysql/data/目录备份打包到/opt/
解压:
#解压恢复 tar Jxvf /opt/mysql_all_2023-11-06.tar.xz
删库跑路:支持物理删除
删除/usr/local/mysql/data数据库目录:
恢复还原:
之后将备份解压出来的/opt/usr/local/mysql/data,将最后的data目录复制到真正的mysql目录下。/usr/local/mysql/
cp -a usr/local/mysql/data/ /usr/local/mysql/
systemctl restart mysqld.service
检测恢复情况:
数据库迁移:
这里复制过来的所属权限要改一下:
chown -R mysql.mysql data
打包备份最好关闭服务。避免新的数据进入被覆盖,也可能会报错导致恢复失败
如何把本地的数据库迁移上云?
开放式问题:除了演示的之外还有什么方法上云
dts工具支持热迁移。
2.2、逻辑备份
热备份当中的逻辑备份:
这时mysql自带的工具:mysqldump
只能在终端执行
1、备份单个库:
mysqldump -u root -p123456 --databases 库名 > /opt/ku1.sql
结尾必须是.sql
2、备份多个库:
mysqldump -u root -p123456 --databases 库名1 库名2 > /opt/ku1ku2.sql
3、备份全部库:
mysqldump -u root -p123456 --all-databases > /opt/allku.sql
恢复还原:
mysql -u root -p < /opt/sql文件名
热备份开着服务备份:
先看服务起没起,端口起没起
rm -rf data,物理删除,不能恢复
只能逻辑方式删除:命令行删除
mysql -u root -p123456 -e 'show databases;'
mysql -e:执行完一次之后自动退出
逻辑删除库:
恢复还原:
备份还原多个库:
备份:
mysqldump -u root -p --databases ku ku1 > /opt/kuku1.sql
删除库:
多个库一起恢复:
恢复指定库和指定表:
要先指定库或表备份
不进库删除:
恢复:
指定库名进行恢复
恢复多个指定表:
先备份:
删除:
恢复:
检测
异地迁移恢复:
先在主机1备份一个全部备份文件
主机2远程复制:
直接恢复:
可以用sql语句的方式直接备份恢复
实验:mysql1的全部数据库的逻辑备份文件,导入到mysql2,那么有重名的库是否会覆盖,不重名的库是否还在。
总结:
物理冷备份和物理热备份
特点:简单
缺点:占用的备份空间比较大
mysqldump:这是mysql自带的备份文件的命令
特点:方便,简单。但是只能基于逻辑上的表结构表数据恢复。物理删除之后再用逻辑恢复会报错
他也可以作为数据迁移。也会占用大空间。
比较物理备份相对来说占的空间要小的多
2.3增量备份:
新增的数据进行备份
增量备份用的也是mysqldump
特点:没有重复数据,备份量小,时间短
mysqldump增量备份恢复表数据期间,表会锁定。
缺点:备份时锁表,必然会影响业务。超过10G大小时,耗时会比较长,导致服务不可用
增量备份的过程:
- mysql提供的一种二进制日志实现增量备份。
二进制文件怎么来?
修改配置文件:/etc/my.cnf
log-bin=mysql-bin
binlog_format=MIXED
重启服务
mysql二进制日志记录格式有三种:
- STATEMENT:基于sql语句
记录修改的sql语句,在高并发情况下记录sql语句的顺序可能会出错,恢复数据时,可能会导致丢失和误差。效率比较高
- ROW:基于行
精准记录每一行的数据,准确率高,但是恢复的时效率低
- MIXED:既可以根据sql语句,也可以根据行
在正常情况下使用STATEMENT,一旦发生高并发,会智能自动切换到ROW行
先建表插入几个数据,再修改配置文件,重启服务
二进制文件所在目录:
/usr/local/mysql/data
之后表中新加入内容:
查看二进制内容命令:
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002
刷新命令:会新增一个二进制文件
mysqladmin -u root -p flush-logs
把增量的部分删除:
断点,每次刷新会生成新的一个二进制文件
恢复:
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000003
注意的是要恢复的二进制文件是哪个文件
位置恢复和时间恢复
基于位置点来进行恢复:
1、从某一个点开始恢复到最后
mysqlbinlog --no-defaults --start-position='位置点' 文件名 | mysql -u root -p123456
2、从开头一直恢复到某个位置
mysqlbinlog --no-defaults --stop-position='位置点' 文件名 | mysql -u root -p
3、从指定点开始———指定结束点
mysqlbinlog --no-defaults --start-position='位置点' --stop-position'位置点' 文件名 | mysql -u root -p
打个新的断点,防止写入要操作的断点
查看位置点:
at后面的数字就是位置点
选commit后面的位置点
操作实验:
先mysqladmin -u root -p flush-logs
先刷新出一个二进制备份文件000001
然后创建一个新表test
这样创建的数据1-4都会记录到00001中
然后再mysqladmin -u root -p flush-logs
刷新一个000002二进制备份文件
对表进行操作:新插入5-8
插入5-8的操作就记录在二进制文件000002中
断点会记录所有新增操作,直到下一次新增断点,新操作会记录到新增断点中
再基于位置点恢复的话,是基于二进制备份文件的操作对表进行新增操作,不会像物理和逻辑备份一样,完全清空表的内容。是基于目前表来进行操作。下面的基于时间点操作同理。
基于时间点来进行恢复
- 从某个时间点开始
mysqlbinlog --no-defaults --start-datetime='时间点' 文件 | mysql -u root -p
- 从开头,到指定的结尾时间点
mysqlbinlog --no-defaults --stop-datetime='时间点' 文件 | mysql -u root -p
- 指定时间范围:
mysqlbinlog --no-defaults --start-datetime='时间点' --stop-datetime='时间点' 文件 | mysql -u root -p
查看时间点:
时间格式YYYY-MM-DD HH:MM:SS
基于时间点内的操作:
总结:
在生产中,通过binlog进行增量恢复是非常好用的方法
我们只需要对binlog文件进行备份,随时可以进行备份和恢复
附加题:写一个脚本,每个月的20号对数据库,打一个断点。
断点之后自动进行增量备份
如何打开mysql的默认日志:
打开/etc/my.cnf
错误日志单独记录:
记录通用访问日志:
记录慢查询日志:指定慢查询时间,超过5s才会记录
重启即生效
日志文件在data目录下
MySQL 的日志默认保存位置为 /usr/local/mysql/data
vim /etc/my.cnf
[mysqld]
##错误日志,用来记录当MySQL启动、停止或运行时发生的错误信息,默认已开启
log-error=/usr/local/mysql/data/mysql_error.log #指定日志的保存位置和文件名
##通用查询日志,用来记录MySQL的所有连接和语句,默认是关闭的
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
##二进制日志(binlog),用来记录所有更新了数据或者已经潜在更新了数据的语句,记录了数据的更改,可用于数据恢复,默认已开启
log-bin=mysql-bin #也可以 log_bin=mysql-bin
##慢查询日志,用来记录所有执行时间超过long_query_time秒的语句,可以找到哪些查询语句执行时间长,
以便于优化,默认是关闭的
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5 #设置超过5秒执行的语句被记录,缺省时为10秒
systemctl restart mysqld
mysql -u root -p
show variables like 'general%'; #查看通用查询日志是否开启
show variables like 'log_bin%'; #查看二进制日志是否开启
show variables like '%slow%'; #查看慢查询日功能是否开启
show variables like 'long_query_time'; #查看慢查询时间设置
set global slow_query_log=ON; #在数据库中设置开启慢查询的方法
实验:
数据库迁移上云:
1、整体备份
2、sql文件 如果复制到另外一个库,不是重名命的库,是否会被覆盖,相同的表名会不会覆盖,相同的库名会不会覆盖
3、增量备份,位置节点和时间点,注意一下断点
4、一个工具:xtrabackup(DTS)
5、使用工具,要有完整的流程,从安装 使用 备份 结果 报错记录下来,形成文档。
附加题:
写一个脚本,每个月的20号,对数据库打一个断点。
断点之后进行自动进行增量备份。