mysql备份和恢复

爱喝鱼片汤

已于 2024-06-26 19:54:39 修改

阅读量1.8k

点赞数 31

CC 4.0 BY-SA版权

文章标签： mysql 数据库

于 2024-06-26 19:53:23 首次发布

本文链接：https://blog.csdn.net/2401_83883919/article/details/139794479

1、完全备份一个或多个完整的库（包括其中所有的表）

2、完全备份 MySQL 服务器中所有的库

① 数据库中sql语句查询开启的日志类型等信息

② 修改配置文件设置日志的保存路径、开启等操作

一、数据库备份的分类

1）从物理与逻辑的角度，数据库备份可分为

1、物理备份：对数据库操作系统的物理文件(如数据文件日志文件等)的备份

冷备份(脱机备份)：是在关闭数据库的时候进行的；

热备份(联机备份)：数据库处于运行状态，依赖于数据库的日志文件；

温备份：数据库运行状态，锁定表格(不可写入但可读)的状态下进行备份操作；

2、逻辑备份：对数据库逻辑组件(如:库、表等数据库对象)的备份。把数据转化成sql语句形式导出到文件，恢复的时候再数据把文件导入进去，把sql语句再执行一遍。

2）从数据库的备份策略角度，备份可分为：

1、完全备份：每次对数据库进行完整的备份，但备份的数据中很多重复。

2、差异备份：备份自从上次完全备份之后被修改过的文件

3、增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

在企业中，一般增量备份和完全备份策略配合使用。

面试题：你们公司数据库怎么备份的？

写自动备份脚本交给crontab定时操作，比如每周选一天业务量较小的情况下做一次完全备份，其他时间做增量备份。

二、常见的备份方法

1）tar压缩包（完全备份，物理冷备）

备份时数据库处于关闭状态，直接打包数据库文件（）。备份速度快，恢复时也是最简单的；

2）mysqldump（完全备份，逻辑热备）

mysqldump是innodb引擎常用的逻辑备份工具。还有mysqlhotcopy，但仅能备份MyISAM和ARCHIVE表（用的少了）；

3）二进制日志（增量备份）

二进制日志进行增量备份，需要刷新二进制日志

4）第三方工具备份（完全备份、增量备份，物理热备）

免费的MySQL物理热备份软件Percona XtraBackup（物理备份效果好，只能备份innodb和xtradb两种引擎的表，用的很多）；xtrabackup备份方法：通过xtrabackup备份mysql

三、MySQL完全备份

完全备份是对整个数据库、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。完全备份与恢复操作简单，但缺点是重复数据多、占用空间大、操作时间长。

1）数据库完全备份与恢复

1、物理冷备份与恢复，也可用作数据库迁移

关闭MySQL数据库
使用tar命令直接打包数据库文件夹直接替换现有MySQL目录即可
mysqldump备份与恢复

【demo】物理冷备和恢复的方式实现数据迁移

准备2台mysql服务器7-5有数据，7-4为空库，把数据从7-5迁移到7-4数据库。

MySQL服务器1
#关闭数据库服务
systemctl stop mysql;
#完全备份data数据
tar zcf /opt/mysql_backup_$(%Y%y%d) /usr/local/mysql/data/
#查看数据库备份文件
tar tf mysql_backup_20240620
#远程传输到另外一台主机
scp mysql_backup_20240620 192.168.170.100/opt

MySQL服务器2
#关闭数据库服务
systemctl stop mysql;
#去opt目录下，把tar包解压得到data目录及数据
cd /opt/
tar xf mysql_backup_20240620
#把本机服务的myaql的data数据目录先进行备份重命名
cd /usr/local/mysql/
mv data/ data.bak
#把解压后的数据导入到/usr/local/mysql/中；
mv /opt/data /usr/local/mysql/
#重启MySQL服务
systemctl restart mysql
#登录数据库即可查看到完全备份的文件，即完成了从mysql服务器1迁移数据到了mysql服务器2

验证迁移结果：7-4数据库能够看到7-5的全部数据

2）mysqldump备份与恢复

如果公司业务不能中断，那么就不能使用冷备法迁移了，要用到热备工具。

MySQL自带的备份工具，可方便实现对MySQL的备份；
可以将指定的库、表导出为SQL脚本；
使用命令mysql导入备份的数据

1、完全备份一个或多个完整的库（包括其中所有的表）

#备份的是数据库包含的表的数据，不包含库这个对象的相关数据
mysqldump -u root -p[密码] 库名1 [库名2] … > /备份路径/备份文件名.sql

#备份的会包含数据库等对象的相关数据
mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql

2、完全备份 MySQL 服务器中所有的库

mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql

3、完全备份某库中指定的表

mysqldump -u root -p[密码] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql
#使用“-d”选项，说明只保存数据库的表结构，不使用“-d”选项，说明表数据也进行备份

4、恢复mysqldump工具备份的数据

① 登录数据库恢复

先登录数据库，再执行 source sql文件路径（如果sql文件里只备份了表，需要先 use 切换库再执行 source）

② 命令行数据恢复

mysql -uroot -p密码 -e 'SQL语句;'         #免登录数据库执行操作，-e的效果是处理完后退出数据库
mysql -uroot -p密码 -e 'show databases;'  #免登录数据库查看库

#恢复库（包含库的所有数据）

mysql -u root -p密码 < sql文件路径  
cat sql文件路径 | mysql -u root -p密码

#恢复表

mysql -u root -p密码 库名 < sql文件路径
cat sql文件路径 | mysql -u root -p密码 库名

补充热备工具：阿里云DTS数据传输服务工具，专门做数据迁移的工具，从线下数据库迁移到云上数据库的工具。

四、二进制日志实现增量备份

1）查询和设置常见的日志类型

二进制日志保存了所有更新或者可能更新数据库的操作。二进制日志在启动MySQL服务器后开始记录，并在文件达到max binlog size所设置的大小或者接收到flush logs命令后重新创建新的日志文件。只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份。MySQL 的日志默认保存位置为 /usr/1ocal/mysql/data/中

① 数据库中sql语句查询开启的日志类型等信息

#查看通用查询日志/二进制日志/慢查询日志是否开启、查看慢查询时间设置
show variables like 'general% | log_bin% | %slow% | long_query_time';

#设置开启慢查询
set global slow_query_log=ON;

② 修改配置文件设置日志的保存路径、开启等操作

vim /etc/my.cnf
[mysqld]
##错误日志，用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启
log-error=/usr/local/mysql/data/mysql_error.1og

##通用查询日志，用来记录MySQL的所有连接和语句，默认是关闭的，开启
qeneral_loq=0N
general_log_file=/usr/local/mysql/data/mysql_general.log

##二进制日志(binlog)，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启，需要指定一下保存路径
log-bir-mysql-bin
#也可以log_bin=mysql-bin
binlog_format=statement 或 row

##慢查询日志，用来记录所有执行时间超过1ong_query_time秒的语句，可以找到哪些査询语句执行时间长，以便于优化，默认是关闭的，开启慢查询；指定保存路径；设置超过5秒执行的语句被记录，缺省时为10秒
slow_query_1og=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5

修改配置前，数据库的data目录中没有慢查询、通用查询等日志文件，配置修改后重启mysql服务就会生成这些文件了。

2）二进制日志增量备份

#通过刷新二进制日志实现增量备份
mysqladmin -u root -p密码 flush-logs

#查看二进制日志内容
mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制文件路径

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

statement：基于sql语句只记录操作命令；
row：基于行，会把命令操作后的所有行数据记录下来；
mixed：混合模式，一般情况下是基于行记录，繁忙情况下会切换至基于sql语句记录；

3）二进制日志增量恢复

#使用二进制日志增量恢复
mysqlbinlog --no-defaults 二进制文件路径 | mysql -u root -p密码

断点恢复

基于位置点恢复

mysqlbinlog --no-defaults --start-position='开始位置点' --stop-position='结束位置点'  二进制文件路径 | mysql -u root -p密码

基于时间点恢复

mysqlbinlog --no-defaults --start-datetime='YYYY-mm-dd HH:MM:SS' --stop-datetime='YYYY-mm-dd HH:MM:SS'  二进制文件路径 | mysql -u root -p密码

断点恢复的原则
如果要恢复到某条sql语句之前的所有数据，就stop在这个语句的位置点或时间点之前
如果要恢复某条sql语句及其之后的所有数据，就从这个语句的位置点或时间点开始start