理论+实验——MySQL备份与恢复

一、数据库备份的分类

1.1 备份的重要性

（1）在生产环境中，数据的安全性至关重要
（2）任何数据的丢失都可能产生严重的后果
（3）造成数据丢失的原因：

• 程序错误
• 人为操作错误
• 运算错误
• 磁盘故障
  灾难（如火灾、地震）和盗窃

1.2 数据库备份的分类

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

• 物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
  ◆ 物理备份方法：
  ① 冷备份（脱机备份）：是在关闭数据库的时候进行的
  ② 热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件
  ③ 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作
• 逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份

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

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

1.3 常见的备份方法

（1）物理冷备

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

（2）专用备份工具 mydump 或 mysqlhotcpy

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

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

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

（4）第三方工具备份

• 免费的MySQL热备份软件 Percona XtraBackup

二、MySQL完全备份与恢复

2.1 完全备份概述

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

2.2 完全备份优缺点

优点

• 备份与恢复操作简单方便

缺点

• 数据存在大量的重复
• 占用大量的备份空间
• 备份与恢复时间长

2.3 完全备份分类

物理冷备份与恢复

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

mysqldump备份与恢复

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

2.3.1 MySQL物理冷备份及恢复

备份数据库：

[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备份压缩
tar: Removing leading `/' from member names
/usr/local/mysql/data/
/usr/local/mysql/data/ibdata1
/usr/local/mysql/data/ib_logfile1
.
.
.
/usr/local/mysql/data/ib_buffer_pool
[root@localhost ~]# cd /backup/                                               ###进入/backup/         
[root@localhost backup]# ll                                                        ##查看备份
total 1384
-rw-r--r-- 1 root root 1413924 Sep 13 22:24 mysql_all-2020-09-13.tar.gz

模拟故障：

[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目录被移走 ，模拟故障成功
total 64
drwxr-xr-x  2 mysql mysql  4096 Sep 13 18:42 bin
-rw-r--r--  1 mysql mysql 17987 Sep 13  2017 COPYING
-rw-r--r--  1 mysql mysql 17987 Sep 13  2017 COPYING-test
drwxr-xr-x  2 mysql mysql    55 Sep 13 18:41 docs
drwxr-xr-x  3 mysql mysql  4096 Sep 13 18:41 include
drwxr-xr-x  4 mysql mysql   191 Sep 13 18:42 lib
drwxr-xr-x  4 mysql mysql    30 Sep 13 18:42 man
drwxr-xr-x 10 mysql mysql  4096 Sep 13 18:42 mysql-test
-rw-r--r--  1 mysql mysql  2478 Sep 13  2017 README
-rw-r--r--  1 mysql mysql  2478 Sep 13  2017 README-test
drwxr-xr-x 28 mysql mysql  4096 Sep 13 18:42 share
drwxr-xr-x  2 mysql mysql    90 Sep 13 18:42 support-files
drwxr-xr-x  3 mysql mysql    17 Sep 13 18:42 usr

数据库恢复：

[root@localhost mysql]# mkdir /restore                                                                                        ####根目录新建restore 目录
[root@localhost mysql]# tar zxvf /backup/mysql_all-2020-09-13.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_buffer_pool
[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数据库启动状态，日志文件显示正常。
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2020-09-13 22:45:27 CST; 7s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 28858 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 28839 ExecStartPre=/usr/local/mysql/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 28860 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─28860 /usr/local/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/mysqld.p...

Sep 13 22:45:27 localhost.localdomain mysqld[28858]: 2020-09-13T14:45:27.023023Z 0 [Note] Ser...06
Sep 13 22:45:27 localhost.localdomain mysqld[28858]: 2020-09-13T14:45:27.023058Z 0 [Note] IPv...e.
Sep 13 22:45:27 localhost.localdomain mysqld[28858]: 2020-09-13T14:45:27.023066Z 0 [Note]   -...';
Sep 13 22:45:27 localhost.localdomain mysqld[28858]: 2020-09-13T14:45:27.023076Z 0 [Note] Ser...'.
Sep 13 22:45:27 localhost.localdomain mysqld[28858]: 2020-09-13T14:45:27.030563Z 0 [Note] Eve...ts
Sep 13 22:45:27 localhost.localdomain mysqld[28858]: 2020-09-13T14:45:27.030797Z 0 [Note] /us...s.
Sep 13 22:45:27 localhost.localdomain mysqld[28858]: Version: '5.7.20'  socket: '/usr/local/m...on
Sep 13 22:45:27 localhost.localdomain mysqld[28858]: 2020-09-13T14:45:27.030815Z 0 [Note] Exe...k.
Sep 13 22:45:27 localhost.localdomain mysqld[28858]: 2020-09-13T14:45:27.030819Z 0 [Note] Beg...es
Sep 13 22:45:27 localhost.localdomain systemd[1]: Started MySQL Server.
Hint: Some lines were ellipsized, use -l to show in full.

2.3.2 mysqldump 备份与恢复

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

（1）mysqldump 命令库内特定的表进行完全备份

mysqldump [选项] 库名 [表名1