看完就懂——MySQL备份与恢复前言一、数据库备份的分类二、MySQL完全备份三、MySQL数据库增量备份与恢复操作四、恢复数据库和表

71 阅读 0 评论 47 点赞

我是靠谱客的博主老迟到海燕，最近开发中收集的这篇文章主要介绍看完就懂——MySQL备份与恢复前言一、数据库备份的分类二、MySQL完全备份三、MySQL数据库增量备份与恢复操作四、恢复数据库和表，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

前言
一、数据库备份的分类
- 1.1 数据丢失的原因
- 1.2 数据库备份的分类
- - 1.2.1 从物理与逻辑的角度
  - 1.2.2 从数据库的备份策略角度
- 1.3 常见的备份方法
二、MySQL完全备份
- 2.1 完全备份概念
- 2.2 优缺点
- 2.3 完全备份分类
- - 2.3.1 物理冷备份与恢复
  - 2.3.2 mysqldump备份与恢复
- 2.4 实验MySQL数据库完全备份操作
- - 2.4.1 物理冷备份与恢复（tar命令直接打包数据库文件夹）
- 2.5 mysqldump备份操作
- 2.6 mysql恢复数据库和表
- - 2.6.1 数据恢复的两种方法
  - 2.6.2 使用source命令恢复数据库
  - 2.6.3 使用mysql命令恢复数据库
  - 2.6.4 使用mysql命令恢复表
三、MySQL数据库增量备份与恢复操作
- 3.1 增量备份概念
- 3.2 使用mysqldump命令进行完全备份存在的问题
- 3.3 增量备份优缺点
- 3.4 增量备份：二进制日志备份
- 3.5 增量恢复MySQL数据库步骤
- 3.6 MySQL增量恢复的三种方法
- - 3.6.1一般恢复
  - 3.6.2 基于位置恢复
  - 3.6.3 基于时间点恢复
四、恢复数据库和表
- 4.1 数据恢复的两种方法
- 4.2 使用source命令恢复数据库
- 4.3 使用mysql命令恢复数据库
- 4.4 使用mysql命令恢复表

前言

在生产环境中，数据的安全性是至关重要的，任何数据的丢失都可能产生严重的后果，所以如何做好备份就成了必要的重要工作

一、数据库备份的分类

1.1 数据丢失的原因

程序错误
人为错误（大部分原因）
运算错误
磁盘故障
灾难（如火灾，地震）和偷窃

1.2 数据库备份的分类

1.2.1 从物理与逻辑的角度

从物理与逻辑的角度，备份可分为：

物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
数据库在物理层面分成了三类文件，每一个文件的名字以表的名字开始，扩展名指出文件类型
- 表结构文件：.frm文件存储表定义
- 表数据文件的扩展名为.MYD（ MYData）
- 表索引文件的扩展名是.MYI（ MYIndex）
物理备份方法：
- 冷备份：是在关闭数据库的时候进行的
- 热备份：数据库处于运行状态，这种备份方法依赖于数据库的日志文件
- 温备份：数据库锁定表格(不可写入但可读)的状态下进行备份操作
逻辑备份：对数据库逻辑组件（如表等数据库对象）的备份

1.2.2 从数据库的备份策略角度

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

完全备份：每次对数据库进行完整的备份
差异备份：备份自从上次完全备份之后被修改过的文件
增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

1.3 常见的备份方法

物理冷备

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

专用备份工具mydump或mysqlhotcopy

mysqldump常用的逻辑备份工具
mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

启用二进制日志进行增量备份

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

第三方工具备份

免费的MySQL热备份软件Percona XtraBackup、Xtrabackup、innobackupex、xbstream

二、MySQL完全备份

2.1 完全备份概念

是对整个数据库的备份、数据库结构和文件结构的备份
保存的是备份完成时刻的数据库
是增量备份的基础

2.2 优缺点

优点：

备份与恢复操作简单方便

缺点：

数据存在大量的重复
占用大量的备份空间，空间利用率低
备份与恢复时间长

2.3 完全备份分类

2.3.1 物理冷备份与恢复

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

2.3.2 mysqldump备份与恢复

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

2.4 实验MySQL数据库完全备份操作

2.4.1 物理冷备份与恢复（tar命令直接打包数据库文件夹）

（1）备份数据库

[root@localhost ~]# systemctl stop mysqld                                ###关闭数据库####
[root@localhost ~]# mkdir /backup                                        ###在根下新建个backup目录
[root@localhost ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/           #####/usr/local/mysql/data备份压缩
[root@localhost ~]# cd /backup/                                               ###进入/backup/         
[root@localhost backup]# ll                                                        ##查看备份
总用量 1328
-rw-r--r-- 1 root root 1359664 8月  12 22:39 mysql_all-2018-08-12.tar.gz

（2）模拟故障

[root@localhost backup]# mkdir /bak                                            ####在根下新建bak目录
[root@localhost backup]# mv /usr/local/mysql/data/ /bak/          ####将目录data数据移动到bak 里面去
[root@localhost backup]# cd /usr/local/mysql/                             #####仅mysql目录查看data
[root@localhost mysql]# ll                                                              ####查看后data目录被移走 ，模拟故障成功
总用量 64
drwxr-xr-x  2 mysql mysql  4096 8月   4 19:19 bin
-rw-r--r--  1 mysql mysql 17987 9月  13 2017 COPYING
-rw-r--r--  1 mysql mysql 17987 9月  13 2017 COPYING-test
drwxr-xr-x  2 mysql mysql    55 8月   4 19:19 docs
drwxr-xr-x  3 mysql mysql  4096 8月   4 19:19 include
drwxr-xr-x  4 mysql mysql   191 8月   4 19:19 lib
drwxr-xr-x  4 mysql mysql    30 8月   4 19:19 man
drwxr-xr-x 10 mysql mysql  4096 8月   4 19:20 mysql-test
-rw-r--r--  1 mysql mysql  2478 9月  13 2017 README
-rw-r--r--  1 mysql mysql  2478 9月  13 2017 README-test
drwxr-xr-x 28 mysql mysql  4096 8月   4 19:20 share
drwxr-xr-x  2 mysql mysql    90 8月   4 19:20 support-files
drwxr-xr-x  3 mysql mysql    17 8月   4 19:19 usr

（3）数据库恢复

[root@localhost mysql]# mkdir /restore                                                                                        ####根目录新建restore 目录
[root@localhost mysql]# tar xzvf /backup/mysql_all-2020-10-12.tar.gz -C /restore/              ####将备份数据库解压到 /restore目录下  不要无脑刷，时间节点不一样
usr/local/mysql/data/
usr/local/mysql/data/ibdata1
usr/local/mysql/data/ib_logfile1
usr/local/mysql/data/ib_logfile0
.....以下省略.....

（4）启动数据库

[root@localhost mysql]# mv /restore/usr/local/mysql/data/ /usr/local/mysql/          ####将备份数据移动到到/usr/local/mysql中
[root@localhost mysql]# systemctl start mysqld                                                          ####启动mysql数据库
[root@localhost mysql]# systemctl status mysqld                                                        ####查看mysql数据库启动状态  ，日志文件显示正常。

2.5 mysqldump备份操作

将指定的库、表、或全部的库导出为SQL脚本
mysqldump备份需要和mysql进行数据交互，如果关闭mysql 则无法备份和恢复

备份操作语法

语法结构
1.备份指定库中的部分表：
mysqldump [选项] 库名 [表名1] [表名2] … > /备份路径/备份文件名
2.备份一个或多个完整的库（包括其中所有的表）
mysqldump [选项] --databases 库名1 [库名2] … > /备份路径/备份文件名
3.备份 MySQL 服务器中所有的库。
mysqldump [选项] --all-databases  > /备份路径/备份文件名

（1）mysqldump命令对单个库进行完全备份

mysqldump -u用户名-p[密码][选项][数据库名]>/备份路径/备份文件名
单库备份的示例
mysqldump -u root -p auth > /backuplauth.sql
mysqldump -u root -p mysql > /bakcup/mysql.sql

（2）mysqldump命令对多个库进行完全备份

mysqldump -u用户名-p[密码][选项]--databases库名1[库名2]... >/备份路径/备份文件名
多库备份的示例
mysqldump -u root -p --databases auth mysql > /backupldatabases-auth-mysql.sql

（3）mysqldump命令对所有库进行完全备份

mysqldump -u用户名-p[密码][选项]--all-databases > /备份路径/备份文件名
所有库备份的示例
mysqldump -u root -p --opt --all-databases > /backup/all-data.sql

（4）mysqldump命令对某些表进行完全备份

mysqldump -u用户名-p[密码][选项][数据库名][数据表名]> /备份路径/备份文件名

（5）mysqldump命令直接备份表结构

mysqldump -u用户名-p[密码]-d [数据库名] [数据表名]> /备份路径/备份文件名

2.6 mysql恢复数据库和表

2.6.1 数据恢复的两种方法

使用 mysqldump命令导出的SQL备份脚本，在进行数据恢复时可使用以下方法导入

source命令
mysql命令

2.6.2 使用source命令恢复数据库

使用 source恢复数据库的步骤

登录到 MySQL数据库
执行 source备份sql脚本的路径（绝对路径）

案例：

mysql > source /backup/all-data.sql ##source命令在mysql库中使用

2.6.3 使用mysql命令恢复数据库

语法：
mysql -u 用户名 -p [密码] < 库备份脚本的路径
##此处用了导入<符号，而不是导出>符号
例如
mysql -u root -p < /backup/all-data.sql

2.6.4 使用mysql命令恢复表

语法
mysql -u 用户名 -p [密码] < 表备份脚本的路径
例如
mysql -u root -p mysql < /backup/yiku-yibiao.sql

注意：

恢复表时同样可以使用 source或者mysql命令进行
source恢复表的操作与恢复库的操作相同
当备份文件中只包含表的备份，而不包括创建库的语句时，必须指定库名，且目标库必须存在
在生产环境中，可以使用shell脚本自动实现定期备份

三、MySQL数据库增量备份与恢复操作

3.1 增量备份概念

只有那些在上次完全备份或者增量备份后被修改的文件才会被备份
增量备份就是备份自上一次备份之后增加或变化的文件或者内容

3.2 使用mysqldump命令进行完全备份存在的问题

备份数据中有重复数据
备份时间与恢复时间长

3.3 增量备份优缺点

优点：

没有重复数据，效率高，空间利用率最大化
备份量不大，时间短

缺点：

恢复麻烦：需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复
安全性较低

3.4 增量备份：二进制日志备份

MySQL没有提供直接的增量备份方法
可通过MySQL提供的二进制日志间接实现增量备份

MySQL的配置文件的[mysqld]项中加入log-bin=filepath项（filepath是二进制文件的路径），如log-bin=mysql-bin，然后重启mysqld服务。
二进制日志文件的默认路径为/usr/local/mysql/data
[root@localhost mysql]# vim /etc/my.cnf                                               #####编辑my.cnf配置文件
[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysqld]                                                                                                       #####找到这个模块,在[mysqld]项中加入配置 log-bin=mysql-bin
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
log-bin=/usr/local/mysql/data/mysql-bin                            ####这个地方开启二进制日志功能

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES
                                      
[root@localhost mysql]# systemctl restart mysqld                                           ####重启数据库
[root@localhost ~]# ll /usr/local/mysql/data/
总用量 122924
-rw-r----- 1 mysql mysql       56 8月   9 16:16 auto.cnf
-rw-r----- 1 mysql mysql      308 8月  15 05:50 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 8月  15 05:50 ibdata1
-rw-r----- 1 mysql mysql 50331648 8月  15 05:50 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 8月   9 16:16 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 8月  15 05:50 ibtmp1
drwxr-x--- 2 mysql mysql     4096 8月   9 16:16 mysql
-rw-r----- 1 mysql mysql      154 8月  15 05:50 mysql-bin.000001                            ####二进制日志文件
-rw-r----- 1 mysql mysql       39 8月  15 05:50 mysql-bin.index
drwxr-x--- 2 mysql mysql     8192 8月   9 16:16 performance_schema
drwxr-x--- 2 mysql mysql     8192 8月   9 16:16 sys

MySQL二进制日志对备份的意义
- 二进制日志保存了所有更新或者可能更新数据库的操作
- 二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件

语法
mysqladmin -u 用户名 -p [密码] flush-logs

只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份

补充

mysql --no-defaults  ##检查二进制日志文件
mysql --no-defaults --base 64 -out=decode-rows -v
                     64位解码  输出   逐行输出  自动换行

3.5 增量恢复MySQL数据库步骤

增量恢复MySQL数据库步骤

3.6 MySQL增量恢复的三种方法

3.6.1一般恢复

将所有备份的二进制日志内容全部恢复

mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p

3.6.2 基于位置恢复

数据库在某一时间点可能既有错误的操作，也有正确的操作
可以基于精准的位置跳过错误的操作
恢复数据到指定位置

mysqlbinlog --stop-position='操作id' 二进制日志 |mysql -u 用户名 -p 密码

从指定的位置开始恢复数据

mysqlbinlog --start-position='操作id' 二进制日志 |mysql -u 用户名 -p 密码

3.6.3 基于时间点恢复

使用基于时间点的恢复，可能会出现在一个时间点里既同时存在正确的操作又存在错误的操作，所以我们需要一种更为精确的恢复方式
从日志开头截止到某个时间点的恢复

mysqlbinlog [--no-defaults] --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 |mysql -u 用户名 -p 密码

从某个时间点到日志结尾的恢复

mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' 二进制日志 |mysql -u 用户名 -p 密码

从某个时间点到某个时间点的恢复

mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 |mysql -u 用户名 -p 密码

四、恢复数据库和表

4.1 数据恢复的两种方法