mysql数据库的备份和恢复

mysql数据库的备份和恢复

一、备份和恢复

1.备份：

备份得目的是什么：备灾

在生产环境中，数据的安全性非常重要

造成数据丢失的原因：

1、程序出错

2、人为的问题

3、磁盘故障

先备份在恢复

备份：完全备份，增量备份

完全备份：将整个数据库完整得进行备份

增量备份：在完全备份的基础之上，对后续新增的内容进行备份

备份的需求：

1. 在生产环境中，数据的安全至关重要，任何数据的丢失都可能产生严重的后果。
2. 数据为什么会丢失？可能是程序操作，运算错误，磁盘故障，不可预期的事件（地震之类），人为操作等。

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语句的方式直接备份恢复

总结：

物理冷备份和物理热备份

特点：简单

缺点：占用的备份空间比较大

mysqldump：这是mysql自带的备份文件的命令

特点：方便，简单。但是只能基于逻辑上的表结构表数据恢复。物理删除之后再用逻辑恢复会报错

他也可以作为数据迁移。也会占用大空间。

比较物理备份相对来说占的空间要小的多

2.3增量备份：

新增的数据进行备份

增量备份用的也是mysqldump

特点：没有重复数据，备份量小，时间短

mysqldump增量备份恢复表数据期间，表会锁定。

缺点：备份时锁表，必然会影响业务。超过10G大小时，耗时会比较长，导致服务不可用

增量备份的过程：

1. mysql提供的一种二进制日志实现增量备份。

二进制文件怎么来？

修改配置文件：/etc/my.cnf

log-bin=mysql-bin

binlog_format=MIXED

重启服务

mysql二进制日志记录格式有三种：

1.STATEMENT：基于sql语句

记录修改的sql语句，在高并发情况下记录sql语句的顺序可能会出错，恢复数据时，可能会导致丢失和误差。效率比较高

2.ROW：基于行

精准记录每一行的数据，准确率高，但是恢复的时效率低

3.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中

插入5-8的操作就记录在二进制文件000002中

断点会记录所有新增操作，直到下一次新增断点，新操作会记录到新增断点中

再基于位置点恢复的话，是基于二进制备份文件的操作对表进行新增操作，不会像物理和逻辑备份一样，完全清空表的内容。是基于目前表来进行操作。下面的基于时间点操作同理

*基于时间点来进行恢复*

1. 从某个时间点开始

mysqlbinlog --no-defaults --start-datetime=‘时间点’ 文件 | mysql -u root -p

​ 2.从开头，到指定的结尾时间点

mysqlbinlog --no-defaults --stop-datetime=‘时间点’ 文件 | mysql -u root -p

3.指定时间范围：

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; #在数据库中设置开启慢查询的方法

##慢查询日志，用来记录所有执行时间超过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; #在数据库中设置开启慢查询的方法