MySQL高级篇知识点——数据库备份与恢复

刺骨的言语ヽ痛彻心扉 2023-10-07 21:18 14阅读 0赞

#### 目录 ####

*  1.物理备份与逻辑备份
 *  2. mysqldump 实现逻辑备份
 *   *  2.1.备份一个数据库
     *  2.2.备份全部数据库
     *  2.3.备份部分数据库
     *  2.4.备份部分表
     *  2.5.备份单表的部分数据
     *  2.6.排除某些表的备份
     *  2.7.只备份结构或只备份数据
     *  2.8.备份中包含存储过程、函数、事件
     *  2.9.mysqldump 常用选项
 *  3.MySQL 命令恢复数据
 *   *  3.1.单库备份中恢复单库
     *  3.2.全量备份恢复
     *  3.3.从全量备份中恢复单库
     *  3.4.从单库备份中恢复单表
     *  4.物理备份：直接复制整个数据库
 *  5.物理恢复：直接复制到数据库目录
 *   *  5.1.步骤：
     *  5.2.要求
 *  6.表的导出与导入
 *   *  6.1.表的导出
     *   *  6.1.1.使用 SELECT…INTO OUTFILE 导出文本文件
         *  6.1.2.使用 mysqldump 命令导出文本文件
         *  6.1.3.使用 mysql 命令导出文本文件
     *  6.2.表的导入
     *   *  6.2.1.使用 LOAD DATA INFILE 方式导入文本文件
         *  6.2.2.使用 mysqlimport 方式导入文本文件
 *  7.数据库迁移
 *   *  7.1.概述
     *  7.2.迁移方案
     *  7.3.迁移注意点
     *  7.4.迁移小结
 *  8. 删库了不敢跑，能干点啥？
 *   *  8.1.delete：误删行
     *  8.2.truncate/drop：误删库/表
     *  8.3.延迟复制备库
     *  8.4.预防误删库/表的方法
     *  8.5.rm：误删 MySQL 实例
 *  9.附录：MySQL常用命令
 *   *  9.1.mysql
     *  9.2.mysqladmin
     *  9.3.mysqlbinlog
     *  9.4.mysqldump
     *  9.5.mysqlimport/source
     *  9.6.mysqlshow

> 本文笔记整理来自尚硅谷视频[https://www.bilibili.com/video/BV1iq4y1u7vj/?p=195][https_www.bilibili.com_video_BV1iq4y1u7vj_p_195]，相关资料可在视频评论区进行获取。

在任何数据库环境中，总会有**不确定的意外情况**发生，比如例外的停电、计算机系统中的各种软硬件故障、人为破坏、管理员误操作等是不可避免的，这些情况可能会导致**数据的丢失**、**服务器瘫痪**等严重的后果。存在多个服务器时，会出现主从服务器之间的**数据同步问题**。

为了有效防止数据丢失，并将损失降到最低，应**定期**对 MySQL 数据库服务器做**备份**。如果数据库中的数据丢失或者出现错误，可以使用备份的数据**进行恢复**。主从服务器之间的数据同步问题可以通过复制功能实现。

## 1.物理备份与逻辑备份 ##

**（1）物理备份**：备份数据文件，转储数据库物理文件到某一目录。物理备份恢复速度比较快，但占用空间比较大，MySQL 中可以用 **xtrabackup** 工具来进行物理备份。

**（2）逻辑备份**：对数据库对象利用工具进行导出工作，汇总入备份文件内。逻辑备份恢复速度慢，但占用空间小，更灵活。MySQL 中常用的逻辑备份工具为 mysqldump 。逻辑备份就是 备份 SQL 语句 ，在恢复的时候执行备份的 SQL 语句实现数据库数据的重现。

## 2. mysqldump 实现逻辑备份 ##

mysqldump 是 MySQL 提供的一个非常有用的**数据库备份工具**。

### 2.1.备份一个数据库 ###

mysqldump 命令执行时，可以将数据库备份成一个**文本文件**，该文件中实际上包含多个 CREATE 和 INSERT 语句，使用这些语句可以重新创建表和插入数据。

*  查出需要备份的表的结构，在文本文件中生成一个 CREATE 语句。
 *  将表中的所有记录转换成一条 INSERT 语句。

**（1）基本语法**

mysqldump –u 用户名称 –h 主机名称 –p密码 待备份的数据库名称[tbname, [tbname...]]> 备份文件名 称.sql

> 说明： 备份的文件并非一定要求后缀名为 .sql，例如后缀名为 .txt 的文件也是可以的。

（2）举例：使用 root 用户备份 atguigu 数据库：

mysqldump -uroot -p atguigu>atguigu.sql # 备份文件存储在当前目录下

mysqldump -uroot -p atguigudb1 > /var/lib/mysql/atguigu.sql

### 2.2.备份全部数据库 ###

若想用 mysqldump 备份整个实例，可以使用 --all-databases 或 -A 参数：

mysqldump -uroot -pxxxxxx --all-databases > all_database.sql 
    mysqldump -uroot -pxxxxxx -A > all_database.sql

备份文件剖析：  
![在这里插入图片描述][a100cd90d9274a278ec4d76d0dcba701.png]  
![在这里插入图片描述][4e9fe95123f94740a7d8d41a890741d3.png]

### 2.3.备份部分数据库 ###

使用 --databases 或 -B 参数了，该参数后面跟数据库名称，多个数据库间用空格隔开。如果指定 databases 参数，备份文件中会存在创建数据库的语句，如果不指定参数，则不存在。语法如下：

mysqldump –u user –h host –p --databases [数据库的名称1 [数据库的名称2...]] > 备份文件名 称.sql

举例：

mysqldump -uroot -p --databases atguigu atguigu12 >two_database.sql

mysqldump -uroot -p -B atguigu atguigu12 > two_database.sql

### 2.4.备份部分表 ###

比如，在表变更前做个备份。语法如下：

mysqldump –u user –h host –p 数据库的名称 [表名1 [表名2...]] > 备份文件名称.sql

举例：备份 atguigu 数据库下的 book 表。

mysqldump -uroot -p atguigu book> book.sql

book.sql文件内容如下：

![在这里插入图片描述][9375cf5437f6469d9ade78e7993a34c4.png]  
![在这里插入图片描述][fb2a55c1b2644ba09cb71a7e272ec3fa.png]

可以看到，book 文件和备份的库文件类似。不同的是，book文件只包含 book 表的DROP、CREATE和 INSERT 语句。备份多张表使用下面的命令，比如备份 book 和 account 表：

#备份多张表 
    mysqldump -uroot -p atguigu book account > 2_tables_bak.sql

### 2.5.备份单表的部分数据 ###

有些时候一张表的数据量很大，我们只需要部分数据。这时就可以使用 --where 选项了。where 后面附带需要满足的条件。

举例：备份 student 表中 id 小于10的数据：

mysqldump -uroot -p atguigu student --where="id < 10 " > student_part_id10_low_bak.sql

内容如下所示，insert 语句只有 id 小于 10 的部分：

![在这里插入图片描述][32dd055d649749cab6350ce5b299c9be.png]

### 2.6.排除某些表的备份 ###

如果我们想备份某个库，但是某些表数据量很大或者与业务关联不大，这个时候可以考虑排除掉这些表，同样的，选项 --ignore-table 可以完成这个功能。

mysqldump -uroot -p atguigu --ignore-table=atguigu.student > no_stu_bak.sql

通过如下指定判定文件中没有 student 表结构：

grep "student" no_stu_bak.sql

### 2.7.只备份结构或只备份数据 ###

只备份结构的话可以使用 --no-data 简写为 -d 选项；只备份数据可以使用 --no-create-info 简写为 -t 选项。

**（1）只备份结构**

mysqldump -uroot -p atguigu --no-data > atguigu_no_data_bak.sql 
    # 使用 grep 命令，没有找到insert相关语句，表示没有数据备份。 
    [root@node1 ~]# grep "INSERT" atguigu_no_data_bak.sql 
    [root@node1 ~]#

**（2）只备份数据**

mysqldump -uroot -p atguigu --no-create-info > atguigu_no_create_info_bak.sql 
    # 使用grep命令，没有找到create相关语句，表示没有数据结构。 
    [root@node1 ~]# grep "CREATE" atguigu_no_create_info_bak.sql 
    [root@node1 ~]#

### 2.8.备份中包含存储过程、函数、事件 ###

（1）mysqldump 备份默认是不包含存储过程，自定义函数及事件的。可以使用 --routines 或 -R 选项来备份存储过程及函数，使用 --events 或 -E 参数来备份事件。

（2）举例：备份整个atguigu库，包含存储过程及事件。  
使用下面的 SQL 可以查看当前库有哪些存储过程或者函数。

mysql> SELECT SPECIFIC_NAME,ROUTINE_TYPE ,ROUTINE_SCHEMA FROM information_schema.Routines WHERE ROUTINE_SCHEMA="atguigu";

下面备份 atguigu 库的数据，函数以及存储过程。

mysqldump -uroot -p -R -E --databases atguigu > fun_atguigu_bak.sql

### 2.9.mysqldump 常用选项 ###

mysqldump 其他常用选项如下：

![在这里插入图片描述][6b91a43739a84bbf8e784715a77a1102.png]  
![在这里插入图片描述][449b1a0d01444121a719624022230a88.png]

运行帮助命令 mysqldump --help ，可以获得特定版本的完整选项列表。

> 提示：如果运行 mysqldump 没有 --quick 或 --opt 选项，mysqldump 在转储结果前将整个结果集装入内存。如果转储大数据库可能会出现问题，该选项默认启用，但可以用 --skip-opt 禁用。如果使用最新版本的 mysqldump 程序备份数据，并用于恢复到比较旧版本的 MySQL 服务器中，则不要使用 --opt 或 -e 选项。

## 3.MySQL 命令恢复数据 ##

（1）使用 mysqldump 命令将数据库中的数据备份成一个文本文件。需要恢复时，可以使用 **mysql** 命令来恢复备份的数据。mysql 命令可以执行备份文件中的 CREATE 语句和 INSERT 语句。通过 CREATE 语句来创建数据库和表。通过 INSERT 语句来插入备份的数据。

（2）**基本语法**

mysql –u root –p [dbname] < backup.sql

其中，dbname 参数表示数据库名称。该参数是可选参数，可以指定数据库名，也可以不指定。指定数据库名时，表示还原该数据库下的表。此时需要确保 MySQL 服务器中已经创建了该名的数据库。不指定数据库名时，表示还原文件中所有的数据库。此时 sql 文件中包含有CREATE DATABASE语句，不需要 MySQL 服务器中已存在这些数据库。

### 3.1.单库备份中恢复单库 ###

使用 root 用户，将之前练习中备份的 atguigu.sql 文件中的备份导入数据库中，命令如下。如果备份文件中包含了创建数据库的语句，则恢复的时候不需要指定数据库名称，如下所示。

# 备份文件中包含了创建数据库的语句
    mysql -uroot -p < atguigu.sql
    # 备份文件中不包含了创建数据库的语句
    mysql -uroot -p atguigu4< atguigu.sql

### 3.2.全量备份恢复 ###

如果我们现在有昨天的全量备份，现在想整个恢复，则可以这样操作：

mysql –u root –p < all.sql
    
    mysql -uroot -pxxxxxx < all.sql

执行完后，MySQL 数据库中就已经恢复了 all.sql 文件中的所有数据库。

> 补充：如果使用 --all-databases 参数备份了所有的数据库，那么恢复时不需要指定数据库。对应的 sql 文件包含有 CREATE DATABASE 语句，可通过该语句创建数据库。创建数据库后，可以执行 sql 文件中的 USE 语句选择数据库，再创建表并插入记录。

### 3.3.从全量备份中恢复单库 ###

可能有这样的需求，比如说我们只想恢复某一个库，但是我们有的是整个实例的备份，这个时候我们可以从全量备份中分离出单个库的备份。

sed -n '/^-- Current Database: `atguigu`/,/^-- Current Database: `/p' all_database.sql > atguigu.sql 
    # 分离完成后我们再导入atguigu.sql即可恢复单个库

### 3.4.从单库备份中恢复单表 ###

这个需求还是比较常见的。比如说我们知道哪个表误操作了，那么就可以用单表恢复的方式来恢复。举例：我们有 atguigu 整库的备份，但是由于 class 表误操作，需要单独恢复出这张表。

cat atguigu.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `class`/!d;q' > class_structure.sql 
    cat atguigu.sql | grep --ignore-case 'insert into `class`' > class_data.sql 
    # 用shell语法分离出创建表的语句及插入数据的语句后 再依次导出即可完成恢复 
    
    use atguigu; 
    mysql> source class_structure.sql; 
    Query OK, 0 rows affected, 1 warning (0.00 sec) 
    
    mysql> source class_data.sql; 
    Query OK, 1 row affected (0.01 sec)

### 4.物理备份：直接复制整个数据库 ###

（1）直接将 MySQL 中的数据库文件复制出来。这种方法最简单，速度也最快。MySQL 的数据库目录位置不一定相同：

*  在 Windows 平台下，MySQL 8.0 存放数据库的目录通常默认为 “C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Data ”或者其他用户自定义目录；
 *  在 Linux 平台下，数据库目录位置通常为/var/lib/mysql/；
 *  在 MAC OSX 平台下，数据库目录位置通常为“/usr/local/mysql/data”

但为了保证备份的一致性。需要保证：

*  方式 1：备份前，将服务器停止。
 *  方式 2：备份前，对相关表执行 FLUSH TABLES WITH READ LOCK 操作。这样当复制数据库目录中的文件时，允许其他客户继续查询表。同时，FLUSH TABLES语句来确保开始备份前将所有激活的索引页写入硬盘。

（2）这种方式方便、快速，但不是最好的备份方法，因为实际情况可能**不允许停止MySQL服务器**或者**锁住表**，而且这种方法对 InnoDB 存储引擎的表不适用。对于 MyISAM 存储引擎的表，这样备份和还原很方便，但是还原时最好是相同版本的MySQL数据库，否则可能会存在文件类型不同的情况。注意，物理备份完毕后，执行 UNLOCK TABLES 来结算其他客户对表的修改行为。

> 说明： 在 MySQL 版本号中，第一个数字表示主版本号，主版本号相同的 MySQL 数据库文件格式相同。

此外，还可以考虑使用相关工具实现备份。比如，**MySQLhotcopy 工具**。MySQLhotcopy 是一个 Perl 脚本，它使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 来快速备份数据库。它是备份数据库或单个表最快的途径，但它只能运行在数据库目录所在的机器上，并且只能备份 MyISAM 类型的表。多用于 MySQL 5.5 之前。

## 5.物理恢复：直接复制到数据库目录 ##

### 5.1.步骤： ###

（1）演示删除备份的数据库中指定表的数据；  
（2）将备份的数据库数据拷贝到数据目录下，并重启MySQL服务器。  
（3）查询相关表的数据是否恢复。需要使用下面的 chown 操作。

### 5.2.要求 ###

（1）必须确保备份数据的数据库和待恢复的数据库服务器的主版本号相同。因为只有 MySQL 数据库主版本号相同时，才能保证这两个 MySQL 数据库文件类型是相同的。  
（2）这种方式对 MyISAM 类型的表比较有效 ，对于 InnoDB 类型的表则不可用。因为 InnoDB 表的表空间不能直接复制。  
（3）在 Linux 操作系统下，复制到数据库目录后，一定要将数据库的用户和组变成 mysql，命令如下：

chown -R mysql.mysql /var/lib/mysql/dbname

其中，两个 mysql 分别表示组和用户；“-R”参数可以改变文件夹下的所有子文件的用户和组；“dbname”参数表示数据库目录。

> 提示：Linux 操作系统下的权限设置非常严格。通常情况下，MySQL 数据库只有 root 用户和 mysql 用户组下的 mysql 用户才可以访问，因此将数据库目录复制到指定文件夹后，一定要使用 chown 命令将文件夹的用户组变为 mysql，将用户变为 mysql。

## 6.表的导出与导入 ##

### 6.1.表的导出 ###

#### 6.1.1.使用 SELECT…INTO OUTFILE 导出文本文件 ####

（1）在 MySQL 中，可以使用 SELECT…INTO OUTFILE 语句将表的内容导出成一个文本文件。

（2）举例：使用 SELECT…INTO OUTFILE 将 atguigu 数据库中 account 表中的记录导出到文本文件。  
① 选择数据库 atguigu，并查询 account 表，执行结果如下所示。

![在这里插入图片描述][7c0d1e707b394a9b87b0b21c7f2d1bbf.png]

② MySQL 默认对导出的目录有权限限制，也就是说使用命令行进行导出的时候，需要指定目录进行操作。查询 secure\_file\_priv 值：

![在这里插入图片描述][05e7685d07094bcca12f603a051b812f.png]  
![在这里插入图片描述][a3b08daf6c3d4961945c29a94255473a.png]

③ 上面结果中显示，secure\_file\_priv 变量的值为 /var/lib/mysql-files/，导出目录设置为该目录，SQL 语句如下：

SELECT * FROM account INTO OUTFILE "/var/lib/mysql-files/account.txt";

④ 查看 /var/lib/mysql-files/account.txt 文件。

1 张三 90 
    2 李四 100 
    3 王五 0

#### 6.1.2.使用 mysqldump 命令导出文本文件 ####

（1）举例 1：使用 mysqldump 命令将将 atguigu 数据库中 account 表中的记录导出到文本文件。

mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigu account

mysqldump 命令执行完毕后，在指定的目录 /var/lib/mysql-files/ 下生成了 account.sql 和 account.txt 文件。

（2）举例 2：使用 mysqldump 将 atguigu 数据库中的 account 表导出到文本文件，使用 FIELDS 选项，要求字段之间使用逗号“，”间隔，所有字符类型字段值用双引号括起来。

mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigu account --fields-terminated- by=',' --fields-optionally-enclosed-by='\"'

语句 mysqldump 语句执行成功之后，指定目录下会出现两个文件 account.sql 和 account.txt。

#### 6.1.3.使用 mysql 命令导出文本文件 ####

（1）举例 1：使用 mysql 语句导出 atguigu 数据中 account 表中的记录到文本文件。

mysql -uroot -p --execute="SELECT * FROM account;" atguigu> "/var/lib/mysqlfiles/account.txt"

打开 account.txt 文件，其内容包含创建 account 表的数据。

[root@node1 mysql-files]# cat account.txt 
    id name balance 
    1 张三 90 
    2 李四 100 
    3 王五 0

（2）举例 2：将 atguigu 数据库 account 表中的记录导出到文本文件，使用 --veritcal 参数将该条件记录分为多行显示。

mysql -uroot -p --vertical --execute="SELECT * FROM account;" atguigu > "/var/lib/mysql-files/account_1.txt"

（3）举例 3：将 atguigu 数据库 account 表中的记录导出到 xml 文件，使用 --xml 参数，具体语句如下。

mysql -uroot -p --xml --execute="SELECT * FROM account;" atguigu>"/var/lib/mysqlfiles/account_3.xml"

![在这里插入图片描述][147542f8e17f4cdc9eb8a8200a7b6ea6.png]

说明：如果要将表数据导出到 html 文件中，可以使用 --html 选项。然后可以使用浏览器打开。

### 6.2.表的导入 ###

#### 6.2.1.使用 LOAD DATA INFILE 方式导入文本文件 ####

**（1）举例 1**  
使用 SELECT…INTO OUTFILE 将 atguigu 数据库中 account 表的记录导出到文本文件。

SELECT * FROM atguigu.account INTO OUTFILE '/var/lib/mysql-files/account_0.txt';

删除 account 表中的数据：

DELETE FROM atguigu.account;

从文本文件 account.txt 中恢复数据：

LOAD DATA INFILE '/var/lib/mysql-files/account_0.txt' INTO TABLE atguigu.account;

查询 account 表中的数据：

mysql> select * from account; 
    +----+--------+---------+
    | id | name   | balance | 
    +----+--------+---------+
    | 1 | 张三    | 90 		| 
    | 2 | 李四    | 100 	| 
    | 3 | 王五    | 0 		| 
    +----+--------+---------+
    3 rows in set (0.00 sec)

**（2）举例 2**  
选择数据库 atguigu，使用 SELECT…INTO OUTFILE 将 atguigu 数据库 account 表中的记录导出到文本文件，使用 FIELDS 选项和 LINES 选项，要求字段之间使用逗号"，"间隔，所有字段值用双引号括起来：

SELECT * FROM atguigu.account INTO OUTFILE '/var/lib/mysql-files/account_1.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '\"';

删除 account 表中的数据：

DELETE FROM atguigu.account;

从 /var/lib/mysql-files/account.txt 中导入数据到 account 表中：

LOAD DATA INFILE '/var/lib/mysql-files/account_1.txt' INTO TABLE atguigu.account FIELDS TERMINATED BY ',' ENCLOSED BY '\"';

查询 account 表中的数据，具体 SQL 如下：

select * from account; mysql> select * from account; 
    +----+--------+---------+
    | id | name   | balance | 
    +----+--------+---------+
    | 1  | 张三   | 90	 	| 
    | 2  | 李四   | 100 	| 
    | 3  | 王五   | 0 		| 
    +----+--------+---------+
    3 rows in set (0.00 sec)

#### 6.2.2.使用 mysqlimport 方式导入文本文件 ####

举例：导出文件 account.txt，字段之间使用逗号"，"间隔，字段值用双引号括起来。

SELECT * FROM atguigu.account INTO OUTFILE '/var/lib/mysql-files/account.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '\"';

删除 account 表中的数据：

DELETE FROM atguigu.account;

使用 mysqlimport 命令将 account.txt 文件内容导入到数据库 atguigu 的 account 表中：

mysqlimport -uroot -p atguigu '/var/lib/mysql-files/account.txt' --fields-terminated- by=',' --fields-optionally-enclosed-by='\"'

查询 account 表中的数据：

select * from account; mysql> select * from account; 
    +----+--------+---------+
    | id | name   | balance | 
    +----+--------+---------+
    | 1  | 张三   | 90 		| 
    | 2  | 李四   | 100 	| 
    | 3  | 王五   | 0 		| 
    +----+--------+---------+
    3 rows in set (0.00 sec)

除了前面介绍的几个选项之外，mysqlimport 支持需要选项，常见的选项有：

![在这里插入图片描述][98c69dd40cb4436aa36a036aafd9c1c7.png]  
![在这里插入图片描述][a9a68070a329447d88960d940cae9870.png]

## 7.数据库迁移 ##

### 7.1.概述 ###

（1）数据迁移 (data migration) 是指选择、准备、提取和转换数据，并**将数据从一个计算机存储系统永久地传输到另一个计算机存储系统的过程**。此外，验证迁移数据的完整性 和 退役原来旧的数据存储，也被认为是整个数据迁移过程的一部分。

（2）数据库迁移的原因是多样的，包括服务器或存储设备更换、维护或升级，应用程序迁移，网站集成，灾难恢复和数据中心迁移。

（3）根据不同的需求可能要采取不同的迁移方案，但总体来讲，MySQL 数据迁移方案大致可以分为**物理迁移**和**逻辑迁移**两类。通常以尽可能**自动化**的方式执行，从而将人力资源从繁琐的任务中解放出来。

### 7.2.迁移方案 ###

**（1）物理迁移**  
① 物理迁移适用于大数据量下的整体迁移。使用物理迁移方案的优点是比较快速，但需要停机迁移并且要求 MySQL 版本及配置必须和原服务器相同，也可能引起未知问题。

② 物理迁移包括拷贝数据文件和使用 XtraBackup 备份工具两种。

③ 不同服务器之间可以采用物理迁移，我们可以在新的服务器上安装好同版本的数据库软件，创建好相同目录，建议配置文件也要和原数据库相同，然后从原数据库方拷贝来数据文件及日志文件，配置好文件组权限，之后在新服务器这边使用 mysqld 命令启动数据库。

**（2）逻辑迁移**  
逻辑迁移适用范围更广，无论是**部分迁移**还是**全量迁移**，都可以使用逻辑迁移。逻辑迁移中使用最多的就是通过 mysqldump 等备份工具。

### 7.3.迁移注意点 ###

**（1）相同版本的数据库之间迁移注意点**  
相同版本的数据库之间迁移指的是在主版本号相同的 MySQL 数据库之间进行数据库移动。

方式 1： 因为迁移前后 MySQL 数据库的 主版本号相同 ，所以可以通过复制数据库目录来实现数据库迁移，但是物理迁移方式只适用于 MyISAM 引擎的表。对于 InnoDB 表，不能用直接复制文件的方式备份数据库。

方式 2： 最常见和最安全的方式是使用 mysqldump 命令 导出数据，然后在目标数据库服务器中使用 MySQL 命令导入。

举例：

# host1的机器中备份所有数据库,并将数据库迁移到名为host2的机器上 
    mysqldump –h host1 –uroot –p –-all-databases| 
    mysql –h host2 –uroot –p

在上述语句中，“|”符号表示管道，其作用是将mysqldump备份的文件给mysql命令；“–all-databases”表示要迁移所有的数据库。通过这种方式可以直接实现迁移。

**（2）不同版本的数据库之间迁移注意点**  
① 例如，原来很多服务器使用 5.7 版本的 MySQL 数据库，在 8.0 版本推出来以后，改进了 5.7 版本的很多缺陷，因此需要把数据库升级到 8.0 版本。

② 旧版本与新版本的 MySQL 可能使用不同的默认字符集，例如有的旧版本中使用 latin1 作为默认字符集，而最新版本的 MySQL 默认字符集为 utf8mb4。如果数据库中有中文数据，那么迁移过程中需要**对默认字符集进行修改**，不然可能无法正常显示数据。

③ 高版本的 MySQL 数据库通常都会**兼容低版本**，因此可以从低版本的 MySQL 数据库迁移到高版本的 MySQL 数据库。

**（3）不同数据库之间迁移注意点**  
① 不同数据库之间迁移是指从其他类型的数据库迁移到MySQL数据库，或者从MySQL数据库迁移到其他类型的数据库。这种迁移没有普适的解决方法。

② 迁移之前，需要了解不同数据库的架构，**比较它们之间的差异**。不同数据库中定义相同类型的数据的 关 键字可能会不同 。例如，MySQL中日期字段分为DATE和TIME两种，而ORACLE日期字段只有DATE；SQL Server数据库中有 ntext、Image 等数据类型，MySQL 数据库没有这些数据类型；MySQL支持的ENUM和SET类型，这些SQL Server数据库不支持。

③ 另外，数据库厂商并没有完全按照 SQL 标准来设计数据库系统，导致不同的数据库系统的 SQL 语句 有差别。例如，微软的 SQL Server软件使用的是 T-SQL 语句，T-SQL 中包含了非标准的 SQL 语句，不能和 MySQL 的 SQL 语句兼容。

④ 不同类型数据库之间的差异造成了互相**迁移的困难**，这些差异其实是商业公司故意造成的技术壁垒。但是不同类型的数据库之间的迁移并 不是完全不可能 。例如，可以使用 MyODBC 实现 MySQL和SQL Server 之间的迁移。MySQL官方提供的工具 MySQL Migration Toolkit 也可以在不同数据之间进行数据迁移。MySQL 迁移到 Oracle 时，需要使用 mysqldump 命令导出sql文件，然后， 手动更改 sql 文件中的CREATE 语句。

### 7.4.迁移小结 ###

![在这里插入图片描述][93ee467e280d4aed944c8f5f78e0d328.png]

## 8. 删库了不敢跑，能干点啥？ ##

传统的高可用架构是不能预防误删数据的，因为主库的一个drop table命令，会通过binlog传给所有从库和级联从库，进而导致整个集群的实例都会执行这个命令。为了找到解决误删数据的更高效的方法，我们需要先对和 MySQL 相关的误删数据，做下分类：  
① 使用 delete 语句误删数据行；  
② 使用 drop table 或者 truncate table 语句误删数据表；  
③ 使用 drop database 语句误删数据库；  
④ 使用 rm 命令误删整个 MySQL 实例。

### 8.1.delete：误删行 ###

![在这里插入图片描述][972dd58b2cbd46c9a607ca7713fe6330.png]

### 8.2.truncate/drop：误删库/表 ###

![在这里插入图片描述][c5b4369156814b9db159be8b4755665e.png]

### 8.3.延迟复制备库 ###

（1）如果有**非常核心**的业务，不允许太长的恢复时间，可以考虑搭建延迟复制的备库。一般的主备复制结构存在的问题是，如果主库上有个表被误删了，这个命令很快也会被发给所有从库，进而导致所有从库的数据表也都一起被误删了。

（2）**延迟复制**的备库是一种特殊的备库，通过 CHANGE MASTER TO MASTER\_DELAY = N 命令，可以指定这个备库持续保持跟主库有 N 秒的延迟 。比如你把 N 设置为 3600，这就代表了如果主库上有数据被误删了，并且在1小时内发现了这个误操作命令，这个命令就还没有在这个延迟复制的备库执行。这时候到这个备库上执行 stop slave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。

### 8.4.预防误删库/表的方法 ###

![在这里插入图片描述][fee8dc7ec70349d0a4347216221add8e.png]  
![在这里插入图片描述][95d579679d9c48bebe9185cb2e228f17.png]

### 8.5.rm：误删 MySQL 实例 ###

（1）对于一个有高可用机制的 MySQL 集群来说，不用担心 rm 删除数据 了。只是删掉了其中某一个节点的数据的话，HA 系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作。我们要做的就是在这个节点上把数据恢复回来，再接入整个集群。

（2）但如果是恶意地把整个集群删除，那就需要考虑跨机房备份，跨城市备份。

## 9.附录：MySQL常用命令 ##

### 9.1.mysql ###

该 mysql 不是指 mysql 服务，而是指 mysql 的客户端工具。语法如下：

mysql [options] [database]

**（1）连接选项**

# 参数 ：
    -u, --user=name 		指定用户名
    -p, --password[=name] 	指定密码
    -h, --host=name 		指定服务器 IP 或域名
    -P, --port=# 			指定连接端口 
    
    # 示例 ：
    mysql -h 127.0.0.1 -P 3306 -u root -p
    mysql -h127.0.0.1 -P3306 -uroot -p密码

**（2） 执行选项**

-e, --execute=name 执行SQL语句并退出

此选项可以在 MySQL 客户端执行 SQL 语句，而不用连接到 MySQL 数据库再执行，对于一些批处理脚本，这种方式尤其方便。

#示例： 
    mysql -uroot -p db01 -e "select * from tb_book";

![在这里插入图片描述][a119bad59a924c389683ad5abddab62f.png]

### 9.2.mysqladmin ###

mysqladmin 是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。可以通过 mysqladmin --help 指令查看帮助文档。

![在这里插入图片描述][e7195fe99b984492b9a677066009557f.png]

#示例：
    mysqladmin -uroot -p create 'test01'; 
    mysqladmin -uroot -p drop 'test01'; 
    mysqladmin -uroot -p version;

### 9.3.mysqlbinlog ###

由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到 mysqlbinlog 日志管理工具。语法：

mysqlbinlog [options] log-files1 log-files2 ... 
    #选项：
    -d, --database=name : 指定数据库名称，只列出指定的数据库相关操作。
    -o, --offset=# : 忽略掉日志中的前n行命令。
    -r,--result-file=name : 将输出的文本格式日志输出到指定文件。
    -s, --short-form : 显示简单格式， 省略掉一些信息。
    --start-datatime=date1 --stop-datetime=date2 : 指定日期间隔内的所有日志。
    --start-position=pos1 --stop-position=pos2 : 指定位置间隔内的所有日志。

### 9.4.mysqldump ###

mysqldump 客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插入表的 SQL 语句。语法如下：

mysqldump [options] db_name [tables] 
    
    mysqldump [options] --database/-B db1 [db2 db3...] 
    
    mysqldump [options] --all-databases/-A

### 9.5.mysqlimport/source ###

mysqlimport 是客户端数据导入工具，用来导入 mysqldump 加 -T 参数后导出的文本文件。语法如下：

mysqlimport [options] db_name textfile1 [textfile2...]

示例：

mysqlimport -uroot -p test /tmp/city.txt

如果需要导入 sql 文件,可以使用 mysql 中的source 指令：

source /root/tb_book.sql

### 9.6.mysqlshow ###

mysqlshow 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。语法如下：

mysqlshow [options] [db_name [table_name [col_name]]]

参数：

--count 显示数据库及表的统计信息（数据库，表 均可以不指定）
    -i 显示指定数据库或者指定表的状态信息

[https_www.bilibili.com_video_BV1iq4y1u7vj_p_195]: https://www.bilibili.com/video/BV1iq4y1u7vj?p=169
[a100cd90d9274a278ec4d76d0dcba701.png]: https://img-blog.csdnimg.cn/a100cd90d9274a278ec4d76d0dcba701.png
[4e9fe95123f94740a7d8d41a890741d3.png]: https://img-blog.csdnimg.cn/4e9fe95123f94740a7d8d41a890741d3.png
[9375cf5437f6469d9ade78e7993a34c4.png]: https://img-blog.csdnimg.cn/9375cf5437f6469d9ade78e7993a34c4.png
[fb2a55c1b2644ba09cb71a7e272ec3fa.png]: https://img-blog.csdnimg.cn/fb2a55c1b2644ba09cb71a7e272ec3fa.png
[32dd055d649749cab6350ce5b299c9be.png]: https://img-blog.csdnimg.cn/32dd055d649749cab6350ce5b299c9be.png
[6b91a43739a84bbf8e784715a77a1102.png]: https://img-blog.csdnimg.cn/6b91a43739a84bbf8e784715a77a1102.png
[449b1a0d01444121a719624022230a88.png]: https://img-blog.csdnimg.cn/449b1a0d01444121a719624022230a88.png
[7c0d1e707b394a9b87b0b21c7f2d1bbf.png]: https://img-blog.csdnimg.cn/7c0d1e707b394a9b87b0b21c7f2d1bbf.png
[05e7685d07094bcca12f603a051b812f.png]: https://img-blog.csdnimg.cn/05e7685d07094bcca12f603a051b812f.png
[a3b08daf6c3d4961945c29a94255473a.png]: https://img-blog.csdnimg.cn/a3b08daf6c3d4961945c29a94255473a.png
[147542f8e17f4cdc9eb8a8200a7b6ea6.png]: https://img-blog.csdnimg.cn/147542f8e17f4cdc9eb8a8200a7b6ea6.png
[98c69dd40cb4436aa36a036aafd9c1c7.png]: https://img-blog.csdnimg.cn/98c69dd40cb4436aa36a036aafd9c1c7.png
[a9a68070a329447d88960d940cae9870.png]: https://img-blog.csdnimg.cn/a9a68070a329447d88960d940cae9870.png
[93ee467e280d4aed944c8f5f78e0d328.png]: https://img-blog.csdnimg.cn/93ee467e280d4aed944c8f5f78e0d328.png
[972dd58b2cbd46c9a607ca7713fe6330.png]: https://img-blog.csdnimg.cn/972dd58b2cbd46c9a607ca7713fe6330.png
[c5b4369156814b9db159be8b4755665e.png]: https://img-blog.csdnimg.cn/c5b4369156814b9db159be8b4755665e.png
[fee8dc7ec70349d0a4347216221add8e.png]: https://img-blog.csdnimg.cn/fee8dc7ec70349d0a4347216221add8e.png
[95d579679d9c48bebe9185cb2e228f17.png]: https://img-blog.csdnimg.cn/95d579679d9c48bebe9185cb2e228f17.png
[a119bad59a924c389683ad5abddab62f.png]: https://img-blog.csdnimg.cn/a119bad59a924c389683ad5abddab62f.png
[e7195fe99b984492b9a677066009557f.png]: https://img-blog.csdnimg.cn/e7195fe99b984492b9a677066009557f.png