第 19 章 数据库备份与恢复

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

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

1. 物理备份与逻辑备份

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

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

2. mysqldump 实现逻辑备份

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

2.0 准备数据

CREATE DATABASE dbtest_backup;
USE dbtest_backup;

CREATE TABLE `class` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `className` VARCHAR(30) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

insert into class(className) values('1班');
insert into class(className) values('2班');
insert into class(className) values('3班');

CREATE TABLE `student` (
    `studentno` int NOT NULL AUTO_INCREMENT,
    `name` varchar(20) DEFAULT NULL,
    `class` int DEFAULT NULL,
    PRIMARY KEY (`studentno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into student(name,class) values('张三',1);
insert into student(name,class) values('李四',2);
insert into student(name,class) values('王五',3);

2.1 备份一个数据库

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

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

基本语法：

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

说明

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

举例：使用 root 用户备份 dbtest_backup 数据库：

mysqldump -uroot -p dbtest_backup>dbtest_backup.sql #备份文件存储在当前目录下
mysqldump -uroot -p dbtest_backup > /var/lib/mysql/dbtest_backup.sql

备份文件剖析：

-- MySQL dump 10.13  Distrib 8.4.3, for Linux (x86_64)
--
-- Host: localhost    Database: dbtest_backup
-- ------------------------------------------------------
-- Server version	8.4.3

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `class`
--

DROP TABLE IF EXISTS `class`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `class` (
  `id` int NOT NULL AUTO_INCREMENT,
  `className` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `class`
--

LOCK TABLES `class` WRITE;
/*!40000 ALTER TABLE `class` DISABLE KEYS */;
INSERT INTO `class` VALUES (1,'1班'),(2,'2班'),(3,'3班');
/*!40000 ALTER TABLE `class` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `student`
--

DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `student` (
  `studentno` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `class` int DEFAULT NULL,
  PRIMARY KEY (`studentno`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `student`
--

LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (1,'张三',1),(2,'李四',2),(3,'王五',3);
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2024-12-07  8:34:05

• --开头的都是 SQL 语句的注释；

• 以 /*！ 开头、*/ 结尾的语句为可执行的 MySQL 注释，这些语句可以被 MySQL 执行，但在其他数据库管理系统中被作为注释忽略，这可以提高数据库的可移植性；

• 文件开头指明了备份文件使用的 MySQLdump 工具的版本号；接下来是备份账户的名称和主机信息，以及备份的数据库的名称；最后是 MySQL 服务器的版本号，在这里为 8.4.3。

• 备份文件接下来的部分是一些 SET 语句，这些语句将一些系统变量值赋给用户定义变量，以确保被恢复的数据库的系统变量和原来备份时的变量相同，例如：

  # 该SET语句将当前系统变量character_set_client的值赋给用户定义变量@old_character_set_client，其他变量与此类似。
  
  /*!40101SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT*/;
  

• 备份文件的最后几行 MySQL 使用 SET 语句恢复服务器系统变量原来的值，例如：

  # 该语句将用户定义的变量@old_character_set_client中保存的值赋给实际的系统变量character_set_client。
  
  /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  

• 后面的 DROP 语句、CREATE 语句和 INSERT 语句都是还原时使用的。例如，DROP TABLE IF EXISTS student 语句用来判断数据库中是否还有名为 student 的表，如果存在，就删除这个表；CREATE 语句用来创建 student 的表；INSERT 语句用来还原数据。

• 备份文件开始的一些语句以数字开头。这些数字代表了 MySQL 版本号，告诉我们这些语句只有在制定的 MySQL 版本或者比该版本高的情况下才能执行。例如，40101 表明这些语句只有在 MySQL 版本号为 4.01.01 或者更高的条件下才可以被执行。文件的最后记录了备份的时间。

2.2 备份全部数据库

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

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

备份除了系统库以外的所有库

mysqldump -uroot -pxxxxxx --databases $(mysql -uroot -pxxxxxx -N -e "SHOW DATABASES WHERE \`Database\` NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')") > user_database.sql

2.3 备份部分数据库

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

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

mysqldump -uroot -p --databases atguigu atguigu2 > two_database.sql
mysqldump -uroot -p -B atguigu atguigu2 > two_database.sql

2.4 备份部分表

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

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

举例：备份 dbtest_backup 数据库下的 class 表

mysqldump -uroot -p  dbtest_backup class > class.sql
# 备份多张表
mysqldump -uroot -p  dbtest_backup class student > 2_tables_bak.sql

备份表的备份文件不存在数据库的删除和创建语句。

2.5 备份单表的部分数据

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

mysqldump -uroot -p dbtest_backup class --where="id <= 3 " > dbtest_backup_part_id3_low_bak.sql

2.6 排除某些表的备份

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

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

通过如下命令判断备份文件中没有 student 表结构

grep "student" no_stu_bak.sql

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

只备份结构的话可以使用--no-data简写为-d选项；

只备份数据可以使用--no-create-info简写为-t选项。

• 只备份结构

mysqldump -uroot -p dbtest_backup --no-data > dbtest_backup_no_data_bak.sql

# 使用grep命令，没有找到insert相关语句，表示没有数据备份。
grep "INSERT" dbtest_backup_no_data_bak.sql

• 只备份数据

mysqldump -uroot -p dbtest_backup --no-create-info > dbtest_backup_no_create_info_bak.sql

# 使用grep命令，没有找到create相关语句，表示没有数据结构。
grep "CREATE" dbtest_backup_no_create_info_bak.sql

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

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

举例：备份整个 dbtest_backup 库，包含存储过程及事件：

• 使用下面的 SQL 可以查看当前库有哪些存储过程或者函数

SELECT SPECIFIC_NAME,ROUTINE_TYPE,ROUTINE_SCHEMA
FROM information_schema.Routines 
WHERE ROUTINE_SCHEMA="dbtest_backup";
+---------------+--------------+----------------+
| SPECIFIC_NAME | ROUTINE_TYPE | ROUTINE_SCHEMA |
+---------------+--------------+----------------+
| rand_string   | FUNCTION     | dbtest_backup  |
| insert_s1     | PROCEDURE    | dbtest_backup  |
+---------------+--------------+----------------+

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

mysqldump -uroot -p -R -E --databases dbtest_backup > fun_dbtest_backup_bak.sql

查询备份文件中是否存在函数

grep -C 5 "rand_string" fun_dbtest_backup_bak.sql

2.9 mysqldump 常用选项

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

mysqldump 其他常用选项如下：

--add-drop-database：在每个CREATE DATABASE语句前添加DROP DATABASE语句。

--add-drop-tables：在每个CREATE TABLE语句前添加DROP TABLE语句。

--add-locking：用LOCK TABLES和UNLOCK TABLES语句引用每个表转储。重载转储文件时插入得更快。

--all-database,-A：转储所有数据库中的所有表。与使用--database选项相同，在命令行中命名所
有数据库。

--comment[=0|1]：如果设置为0，禁止转储文件中的其他信息，例如程序版本、服务器版本和主机。

--skip-comments与--comments=0的结果相同。默认值为1，即包括额外信息。

--compact：产生少量输出。该选项禁用注释并启用--skip-add-drop-tables、--no-setnames
、--skip-disable-keys和--skip-add-locking选项。

--compatible=name：产生与其他数据库系统或旧的MySQL服务器更兼容的输出，值可以为ansi、MySQL323、MySQL40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_table_options或者no_field_options。

--complete_insert,-c：使用包括列名的完整的INSERT语句。

--debug[=debug_options],-#[debug_options]：写调试日志。

--delete，-D：导入文本文件前清空表。

--default-character-set=charset：使用charsets默认字符集。如果没有指定，就使用utf8。

--delete--master-logs：在主复制服务器上，完成转储操作后删除二进制日志。该选项自动启用-master-data。

--extended-insert，-e：使用包括几个VALUES列表的多行INSERT语法。这样使得转储文件更小，重载文件时可以加速插入。

--flush-logs，-F：开始转储前刷新MySQL服务器日志文件。该选项要求RELOAD权限。

--force，-f：在表转储过程中，即使出现SQL错误也继续。

--lock-all-tables，-x：对所有数据库中的所有表加锁。在整体转储过程中通过全局锁定来实现。
该选项自动关闭--single-transaction和--lock-tables。

--lock-tables，-l：开始转储前锁定所有表。用READ LOCAL锁定表以允许并行插入MyISAM表。对
于事务表（例如InnoDB和BDB），--single-transaction是一个更好的选项，因为它根本不需要锁
定表。

--no-create-db，-n：该选项禁用CREATE DATABASE /*!32312 IF NOT EXIST*/db_name语
句，如果给出--database或--all-database选项，就包含到输出中。

--no-create-info，-t：只导出数据，而不添加CREATE TABLE语句。

--no-data，-d：不写表的任何行信息，只转储表的结构。

--opt：该选项是速记，它可以快速进行转储操作并产生一个能很快装入MySQL服务器的转储文件。该选
项默认开启，但可以用--skip-opt禁用。

--password[=password]
-p[password]：当连接服务器时使用的密码。

-port=port_num，-P port_num：用于连接的TCP/IP端口号。

--protocol={TCP|SOCKET|PIPE|MEMORY}：使用的连接协议。

--replace，-r –replace和--ignore：控制替换或复制唯一键值已有记录的输入记录的处理。如果
指定--replace，新行替换有相同的唯一键值的已有行；如果指定--ignore，复制已有的唯一键值的输
入行被跳过。如果不指定这两个选项，当发现一个复制键值时会出现一个错误，并且忽视文本文件的剩余
部分。

--silent，-s：沉默模式。只有出现错误时才输出。

--socket=path，-S path：当连接localhost时使用的套接字文件（为默认主机）。

--user=user_name，-u user_name：当连接服务器时MySQL使用的用户名。

--verbose，-v：冗长模式，打印出程序操作的详细信息。

--xml，-X：产生XML输出。

提示

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

3. mysql 命令恢复数据

使用 mysqldump 命令将数据库中的数据备份成一个文本文件。需要恢复时，可以使用mysql 命令来恢复备份的数据。

mysql 命令可以执行备份文件中的CREATE 语句和INSERT 语句。通过 CREATE 语句来创建数据库和表。通过 INSERT 语句来插入备份的数据。

基本语法：

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

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

3.1 单库备份中恢复单库

如果备份文件中包含了创建数据库的语句，则恢复的时候不需要指定数据库名称，否则需要指定数据库名称

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

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

3.2 全量备份恢复

mysql –u root –p < all.sql

3.3 从全量备份中恢复单库

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

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

3.4 从单库备份中恢复单表

这个需求还是比较常见的。比如说我们知道哪个表误操作了，那么就可以用单表恢复的方式来恢复。

举例：我们有 dbtest_backup 整库的备份，但是由于 class 表误操作，需要单独恢复出这张表。

cat dbtest_backup.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `class`/!d;q' > class_structure.sql 

cat dbtest_backup.sql | grep --ignore-case 'insert into `class`' > class_data.sql 
#用shell语法分离出创建表的语句及插入数据的语句后 再依次导出即可完成恢复 

use dbtest_backup; 
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. MyISAM 存储引擎类型的表物理备份和恢复

4.1 MyISAM 表数据备份

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

• 在 Windows 平台下，MySQL8.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 语句来确保开始备份前将所有激活的索引页写入硬盘。

这种方式方便、快速，但不是最好的备份方法，因为实际情况可能不允许停止 MySQL 服务器或者锁住表，而且这种方法对 InnoDB 存储引擎的表不适用。对于 MyISAM 存储引擎的表，这样备份和还原很方便，但是还原时最好是相同版本的 MySQL 数据库，否则可能会存在文件类型不同的情况。

注意，物理备份完毕后，执行UNLOCK TABLES来结算其他客户对表的修改行为。

说明

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

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

4.2 MyISAM 存储引擎类型的表的物理备份恢复流程

前面说过，可以通过直接复制数据的操作备份数据。通过这种方式备份的数据，可以直接被复制到 MySQL 的数据目录下。

在 MySQL 服务器停止运行后，将备份的数据库文件复制到 MySQL 的数据目录下，重启 MySQL 服务即可。

注意点：

• 通过这种方式恢复数据时，必须确保备份数据的数据库和待恢复的数据库服务器的主版本号相同。

  因为只有 MySQL 数据库主版本号相同时，才能保证这两个 MySQL 数据库文件类型是相同的。

• 这种方式对MyISAM 类型的表比较有效，对于 InnoDB 类型的表则不可用。

  因为 InnoDB 表的表空间不能直接复制。

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

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

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

演示案例

现在的需求是先备份 dbtest_myisam 库，然后使用 rm 命令删除该库，最后通过备份的数据库文件恢复该库，具体操作步骤如下。

(0)建库建表插数据

create database dbtest_myisam;
use dbtest_myisam;
create table test(id int) engine=myisam;
insert into test values(1),(2),(3);

(1)dbtest_myisam 库想要获得一致的备份，需要关闭该库，或者锁定并刷新相关表。执行如下语句。

mysql> USE dbtest_myisam;
mysql> FLUSH TABLES test WITH READ LOCK;

FLUSH TABLES ... WITH READ LOCK 可以指定表名，也可以不指定表名

• 当指定表名时，FLUSH TABLES 会将指定的表关闭，将表的任何未刷新的数据刷新到磁盘。并对指定的表加读锁。
• 如果不指定表名，FLUSH TABLES 会关闭所有打开的表，并将所有表的更改刷新到磁盘。会加一个全局的读锁。
• MyISAM 引擎的表，只支持表级锁，不支持行级锁
• SELECT * FROM performance_schema.metadata_locks 能查到部分锁结构

(2)把数据目录下的 dbtest_myisam 文件复制到/opt 目录下，如下所示，完成数据库备份。

cd /var/lib/mysql
cp -r dbtest_myisam/ /opt/

(3)在 dbtest_myisam 库中执行 UNLOCK TABLES 命令，如下所示。

mysql> UNLOCK TABLES;

(4)在 dbtest_myisam 库中执行删除数据操作，如下所示，这时表 test 中的数据会被删除。

mysql> DELETE FROM test;

(5)删除数据目录下的 dbtest_myisam 文件夹及其中的文件，如下所示。

cd /var/lib/mysql/
rm -rf dbtest_myisam

(6)重新复制文件到数据目录下，如下所示。

cd /var/lib/mysql/
cp -r /opt/dbtest_myisam ./

(7)重启服务器，查看表 test 中的数据，如下所示。这时会报错，因为该表处于只读状态。

systemctl restart mysqld

mysql>select * FROM test;
ERROR 1036 (HY000): Table 'test' is read only

(8)在 dbtest_myisam 库中给目标端文件授予权限，如下所示

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

(9)在 dbtest_myisam 库中再次查看 test 中的数据，如下所示。

select * FROM test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

至此，MyISAM 存储引擎类型的表的物理备份和恢复就完成了。

5.InnoDB 存储引擎类型的表的物理导入导出

MyISAM 存储引擎类型的表的物理备份和恢复方案并不适用于 InnoDB 存储引擎类型的表。

假设现在把 dbtest 库中表 test 的.ibd文件复制到目标库 dbtest2 的数据目录下。对于 MyISAM 存储引擎类型的表，这样操作是没问题的；但是，对于 InnoDB 存储引擎类型的表，这样操作是不可行的。

因为一张 InnoDB 存储引擎类型的表除了包含着两个物理文件，还需要在数据字典中进行注册。如果直接将这两个物理文件拷贝到目标库的数据目录中，目标库的数据字典中没有对应的表 test，系统是不会识别和接受的。

在 MySQL5.6 中引入了可传输表空间（Transportable Tablespace），可以通过导出和导入表空间的方式来实现物理复制表的功能。

注意

可传输表空间仅支持独立表空间中的表，不支持系统表空间和通用表空间中的表。

假设现在的需求是在 dbtest2 库中复制一张与表 test 相同的表 test_bak，具体操作步骤如下：

(0)建库建表插入数据

create database dbtest;
use dbtest;
create table test(id int) engine=innodb;
insert into test values(1),(2),(3);

create database dbtest2;

(1)在 dbtest2 库中执行如下 SQL 语句，创建一张具有相同表结构的空表。

USE dbtest2;
CREATE TABLE test_bak LIKE dbtest.test;  

(2)在 dbtest2 库中执行如下 SQL 语句，这时 test_bak.ibd 文件会被删除。

ALTER TABLE test_bak DISCARD TABLESPACE;

(3)在 dbtest 库中执行如下 SQL 语句，这时在 dbtest 库的数据目录下会生成一个 test.cfg 文件。

USE dbtest;
FLUSH TABLE test FOR EXPORT;

(4)在 dbtest 库的数据目录下复制物理文件，如下所示：

cp test.cfg ../dbtest2/test_bak.cfg  
cp test.ibd ../dbtest2/test_bak.ibd

(5)在 dbtest2 库中执行 UNLOCK TABLES 命令，如下所示，这时 test.cfg 文件会被删除。

UNLOCK TABLES;

(6)在 dbtest2 库的数据目录下给目标端文件授予权限，如下所示：

cd /var/lib/mysql/dbtest2
chown -R mysql:mysql *
chmod -R 755 *

(7)在 dbtest2 库中执行如下 SQL 语句，将 test_bak.ibd 文件作为表 test_bak 的表空间。由于这个文件中的内容和 test.ibd 文件中的内容是相同的，因此表 test_bak 拥有表 test 相同的结构和数据。

ALTER TABLE test_bak IMPORT TABLESPACE;

(8)在 dbtest2 库中查看表 test_bak 中的数据。如下所示：

select * from test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

6. 表的导出与导入

6.1 表的导出

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

在 MySQL 中，可以使用 SELECT…INTO OUTFILE 语句将表中的数据导出到文本文件中。其语法如下所示。

SELECT columnlist FROM table WHERE condition INTO OUTFILE 'filename' [OPTIONS] 
-- OPTIONS 选项 
FIELDS TERMINATED BY 'value' 
FIELDS [OPTIONALLY] ENCLOSED BY 'value' 
FIELDS ESCAPED BY 'value' 
LINES STARTING BY 'value' 
LINES TERMINATED BY 'value'

SELECT columnlist FROM table WHERE condition 是一条查询语句，查询结果返回满足指定条件的一条或多条记录；

INTO OUTFILE 语句的作用是把前面 SELECT 语句查询出来的结果导出到名为 filename 的外部文件中；

[OPTIONS] 为可选项。OPTIONS 部分的语法包括 FIELDS 和 LINES 子句，其可能的取值如下

• FIELDS TERMINATED BY 'value'：用于设置字段之间的分隔字符。value 取值可以是单个或多个字符，默认为制表符\t。
• FIELDS [OPTIONALLY] ENCLOSED BY 'value'：用于设置字段的包围字符。value 取值只能是单个字符。如果使用了 OPTIONALLY 关键字，则只能包括 CHAR、VARCHAR 等字符字段。
• FIELDS ESCAPED BY 'value'：用于设置如何写入或读取特殊字符。value 取值只能是单个字符，即设置转义字符，默认为反斜杠'\'。
• LINES STARTING BY 'value'：用于设置每行数据开头的字符。value 取值可以是单个或多个字符，在默认情况下不使用任何字符。
• LINES TERMINATED BY 'value'：用于设置每行数据结尾的字符。value 取值可以是单个或多个字符，默认值为换行符'\n'。

注意，FIELDS 和 LINES 子句都是可选的。如果这两个子句都被指定了，那么 FIELDS 子句必须位于 LINES 子句的前面。

使用 SELECT...INTO OUTFILE 语句可以非常快速地把一张表转储到服务器上。想要在服务器端主机之外的部分客户端主机上创建结果文件，就不能使用 SELECT...INTO OUTFILE 语句。在这种情况下，应该在客户端主机上使用 MySQL -e "SELECT..." > filename 这样的命令来生成文件。

下面使用 SELECT…INTO OUTFILE 将 dbtest 数据库下表 test 中的数据导出到文本文件。

(1)选择数据库 dbtest，并查询 test 表，执行结果如下所示。

mysql>use dbtest;
mysql>select * from test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

(2)mysql 默认对导出的目录有权限限制，也就是说，在使用命令行进行导出的时候，需要指定目录进行操作。查询 secure_file_priv 参数的值：

SHOW GLOBAL VARIABLES LIKE '%secure%';
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| require_secure_transport | OFF                   |
| secure_file_priv         | /var/lib/mysql-files/ |
+--------------------------+-----------------------+

参数secure_file_priv的可选值和作用分别是：

• 如果设置为 empty，表示不限制文件生成的位置，这是不安全的设置；
• 如果设置为一个表示路径的字符串，就要求生成的文件只能放在这个指定的目录，或者它的子目录；
• 如果设置为 NULL，就表示禁止在这个 MySQL 实例上执行 select...into outfile 操作。

(3)上面结果中显示，secure_file_priv 变量的值为 /var/lib/mysql-files/，将导出目录设置为该目录，SQL 语句如下。

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

(4)查看 /var/lib/mysql-files/test.txt 文件。

# more /var/lib/mysql-files/test.txt
1
2
3

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

使用 mysqldump 命令可以备份数据库，将数据导出为包含 CREATE、INSERT 语句的 SQL 文件。不仅如此，使用 mysqldump 命令还可以将数据导出为纯文本文件，语法如下所示。

mysqldump -u root -p password -T path dbname [tables] [OPTIONS]
-- OPTIONS 选项
--fields-terminated-by=value
--fields-enclosed-by=value
--fields-optionally-enclosed-by=value
--fields-escaped-by=value
--lines-terminated-by=value

只有指定了-T参数才可以导出文本文件

path 表示导出数据的目录；tables 为要导出的表名，如果不指定表名，则将导出数据库 dbname 中所有的表；[OPTIONS]为可选项，这些选项需要结合-T 参数使用。OPTIONS 部分的语法与 SELECT...INTO OUTFILE 语句中的 OPTIONS 部分的语法相同，这里不再赘述。

例如，将 dbtest 库下表 test 中的数据导出到文本文件中，命令如下所示。

mysqldump -uroot -p -T "/var/lib/mysql-files/" dbtest test

执行上述命令后，将在指定目录/var/lib/mysql-files/下生成 test.sql 和 test.txt 文件。

打开 test.sql 文件，其中包含创建 test 的 CREATE 语句，如下所示。

# more test.sql 
-- MySQL dump 10.13  Distrib 8.4.3, for Linux (x86_64)
--
-- Host: localhost    Database: dbtest
-- ------------------------------------------------------
-- Server version	8.4.3

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `test`
--

DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test` (
  `id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2024-12-18  9:55:55

打开 test.txt 文件，其中只包含表 text 中的数据，如下所示

# more test.txt
1
2
3

可以对导出的数据设置特定格式。例如，使用 FIELDS 选项，要求字段之间用使用逗号分隔，字符串类型的字段值使用双引号引起来。命令如下所示：

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

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

mysql 是一个功能丰富的工具命令，使用该命令可以在命令模式下执行 SQL 语句，并将查询结果导入文本文件中。相比 mysqldump 命令，mysql 命令导出的结果的可读性更强。mysql 命令的语法如下所示。

mysql -u root -p --execute="SELECT 语句" dbname > filename.txt

该命令使用了--execute 选项，表示执行该选项后面的语句并退出，该语句必须用双引号引起来；dbname 为要导出的数据库名；在导出的文件中，不同的列之间使用制表符分隔，第一行中包含各个字段名。

举例 1：使用 MySQL 命令将 dbtest 库中 test 表的数据导出到文本文件中，具体操作步骤如下：

mysql -uroot -p --execute="SELECT * FROM test;" dbtest > "/var/lib/mysql-files/test.txt"

# more test.txt 
id
1
2
3

可以看到，text.txt 文件中包含每个字段名和各条记录，显示格式与 MySQL 命令行的 SELECT 查询结果格式一致。

在使用 MySQL 命令时，还可以指定查询结果显示格式。如果表中一条记录包含的字段较多，一行不能完全显示，则可以使用 --vertical 参数将一条记录分多行显示。

举例 2：分行导出 dbtest 库下 test 表的数据到文本文件中，具体操作步骤如下：

使用 --vertical 参数分行导出

mysql -uroot -p --vertical --execute="SELECT * FROM test;" dbtest > "/var/lib/mysql-files/test2.txt"
# more test2.txt
*************************** 1. row ***************************
id: 1
*************************** 2. row ***************************
id: 2
*************************** 3. row ***************************
id: 3

示例 3：导出 dbtest 库下 test 表的数据到 HTML 文件中，具体操作步骤如下：

使用 --html 参数

mysql -uroot -p --html --execute="SELECT * FROM test;" dbtest > "/var/lib/mysql-files/test3.html"

# more test3.html 
<TABLE BORDER=1>
    <TR>
    	<TH>id</TH>
    </TR>
    <TR>
    	<TD>1</TD>
    </TR>
    <TR>
    	<TD>2</TD>
    </TR>
    <TR>
    	<TD>3</TD>
    </TR>
</TABLE>

举例 4：导出 dbtest 库下表 test 的数据到 XML 文件中，具体操作步骤如下：

使用--XML参数

mysql -uroot -p --xml --execute="SELECT * FROM test;" dbtest > "/var/lib/mysql-files/test4.xml"

# more test4.xml 
<?xml version="1.0"?>

<resultset statement="SELECT * FROM test" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
	<field name="id">1</field>
  </row>

  <row>
	<field name="id">2</field>
  </row>

  <row>
	<field name="id">3</field>
  </row>
</resultset>

6.2 导入文本文件

在 MySQL 中，既可以将数据导出到外部文件中，也可以从外部文件导入数据。MySQL 提供了导入数据的工具，包括 LOAD DATA INFILE 语句和 mysqlimport 命令。

6.2.1 使用 LOAD DATA INFILE 语句导入文本文件

LOAD DATA INFILE 语句用于从一个文本文件中读取行，并装入一张表中，文件名必须是字符串。

LOAD DATA INFILE 语句的语法如下所示：

LOAD DATA [LOCAL] INFILE filename INTO TABLE tablename [OPTIONS] [IGNORE number LINES]

-- OPTIONS 选项
FIELDS TERMINATED BY 'value'
FIELDS [OPTIONALLY] ENCLOSED BY 'value'
FIELDS ESCAPED BY 'value'
LINES STARTING BY 'value'
LINES TERMINATED BY 'value'

在 LOAD DATA INFILE 语句中，关键字 INFILE 后面的 filename 文件为导入数据的来源；tablename 表示待导入的表名；[OPTIONS] 为可选项，OPTIONS 部分的语法与 SELECT...INTO OUTFILE 语句中的 OPTIONS 部分相同，这里不再赘述；IGNORE number LINES 表示忽略文件开头处的行数，number 表示忽略的行数。执行该语句需要 FILE 权限。

6.2.1.1 简单案例：不使用 FIELDS 选项备份、导入数据

使用 SELECT...INTO OUTFILE 语句将 atguigu 库下表 account 中的数据导出到文本文件中，如下所示：

mysql> SELECT * FROM dbtest.test INTO OUTFILE '/var/lib/mysql-files/test.txt';

删除表 test 中的数据，执行完后查看表中的数据，如下所示

mysql> DELETE FROM dbtest.test;

mysql> SELECT * FROM test;
Empty set (0.00 sec)

从文本文件 test.txt 中恢复数据，如下所示：

mysql> LOAD DATA INFILE '/var/lib/mysql-files/test.txt' INTO TABLE dbtest.test;

再次查询表 test 中的数据，如下所示：

mysql> SELECT * FROM test;
1
2
3

6.2.1.2 复杂案例：导入 CSV 文件

LOAD DATA INFILE '/path/to/yourfile.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS (col1,col2,col3,...);

FIELDS TERMINATED BY ','：指定 CSV 文件中的字段分隔符。

ENCLOSED BY '"'：指定字段值的引号。

LINES TERMINATED BY '\n'：指定行结束符。

IGNORE 1 ROWS：忽略第一行表头。

如果需要导入的表结构和数据并不匹配，比如表结构存在自增的 id 字段。就需要显式的指定字段名。

注意换行符

windows 的换行符是\r\n Linux 的换行符是\n

6.2.2 使用 mysqlimport 语句导入文本文件

mysqlimport 命令也可以导入文本文件，而且不需要登录 MySQL 客户端。mysqlimport 命令提供了许多和LOAD DATA INFILE 语句相同的功能，大多数参数直接对应LOAD DATA INFILE 语句。

mysqlimport 命令的语法如下所示：

mysqlimport -uroot -p dbname filename.txt [OPTIONS]

-- OPTIONS 选项
--fields-terminated-by=value
--fields-enclosed-by=value
--fields-optionally-by=value
--lines-terminated-by=value
--ignore-lines=n

其中，dbname 为导入的表所在的数据库名。注意，mysqlimport 命令不指定导入数据库的表名，表名由导入文件名确定，即文件名作为表名，在导入数据之前该表必须存在。

[OPTIONS]为可选项，OPTIONS 部分的语法与 SELECT...INTO OUTFILE 语句中 OPTIONS 部分的语法相同，这里不再赘述。

例如，使用 mysqlimport 命令将 account.txt 文件的内容导入 atguigu 库下的表 account 中，字段之间使用逗号分隔，字符串类型的字段值使用双引号引起来，具体操作步骤如下。

(1) 将 atguigu 库下表 account 中的数据导出到 account.txt 文件中，字段之间使用逗号分隔，字符串类型的字段值使用双引号引起来，如下所示。

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

(2) 删除表 account 中的数据，执行完后查看表中的数据，如下所示。

mysql> DELETE FROM atguigu.account;
mysql> SELECT * FROM account;
Empty set (0.00 sec)

(3) 使用 mysqlimport 命令将 account.txt 文件的内容导入 atguigu 库下的表 account 中，如下所示。

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

(4) 再次查询表 account 中的数据，如下所示。

mysql> SELECT * FROM account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
| 1  | 张三    | 90      |
| 2  | 李四    | 100     |
| 3  | 王五    | 0       |
+----+--------+---------+

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

• --columns=column_list, -c column_list：该选项采用逗号分隔的列名作为其值。列名的顺序只是如何匹配数据文件列和表列。
• --compress, -C：压缩在客户端和服务器之间发送的所有信息（如果二者均支持压缩）
• -d, --delete：导入文本文件前清空表。
• --force, -f：忽视错误。例如，如果某个文本文件的表不存在，就继续处理其他文件。不使用--force，若表不存在，则 mysqlimport 退出。
• --host=host_name, -h host host_name：将数据导入给定主机上的 MySQL 服务器，默认主机是 localhost。
• --ignore, -i：参见 --replace 选项的描述。
• --ignore-lines=n：忽视数据文件的前 n 行。
• --local, -L：从本地客户端读入输入文件。
• --lock-tables, -l：处理文本文件前锁定所有表，以便写入。这样可以确保所有表在服务器上保持同步。
• --password[=password], -p[password]：当连接服务器时使用的密码。如果使用短选项形式(-p)，选项和密码之间不能有空格。如果在命令行中 --password 或-p 选项后面没有密码值，就提示输入一个密码。
• --port=port_num, -P port_num：用户连接的 TCP/IP 端口号。
• --protocol={TCP|SOCKET|PIPE|MEMORY}：使用的连接协议。
• -replace, -r--replace 和 --ignore：控制复制唯一键值已有记录的输入记录的处理。如果指定 --replace，新行替换有相同唯一键值的已有行；如果指定 --ignore，复制已有唯一键值的输入行被跳过；如果不指定这两个选项，当发现一个复制键值时会出现一个错误，并且忽视文本文件的剩余部分。
• --silent, -S：沉默模式。只有出现错误时才输出信息。
• --user=username, -u user_name：当连接服务器时 MysQL 使用的用户名。
• --verbose, -V：冗长模式。打印出程序操作的详细信息。
• --version, -V：显示版本信息并退出。

7. 数据库的迁移

7.1 概述

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

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

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

7.2 迁移方案

• 物理迁移

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

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

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

• 逻辑迁移

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

7.3 迁移注意点

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 表示要迁移所有的数据库。通过这种方式可以直接实现迁移。

7.3.2 不同版本的数据库之间迁移注意点

例如，原来很多服务器使用 5.7 版本的 MySQL 数据库，在 8.0 版本推出来以后，改进了 5.7 版本的很多缺陷，因此需要把数据库升级到 8.0 版本。

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

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

7.3.3 不同数据库之间迁移注意点

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

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

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

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

7.4 迁移小结

8. 误删数据的预防方案和恢复方案

传统的高可用架构是不能预防误删数据的，因为主库的一个 drop table 命令，会通过 binlog 传给所有从库和级联从库，进而导致整个集群的实例都会执行这个命令。

为了找到解决误删数据的更高效的方法，我们需要先对和 MySQL 相关的误删数据，做下分类：

• 使用 delete 语句误删数据行；
• 使用 drop table 或者 truncate table 语句误删数据表;
• 使用 drop database 语句误删数据库；
• 使用 rm 命令误删整个 MySQL 实例。

8.1 DELETE 误删数据行

预防方案

• 代码上线前，必须SQL 审查、审计。
• 建议开启安全模式，把sql_safe_updates参数的值设置为ON。强制要求每次执行 UPDATE/DELETE 操作的时候，WHERE 条件后面都需要跟索引字段。如果没有加 WHERE 条件，或者 WHERE 条件后面没有索引字段，必须使用 LIMIT 关键字。

数据恢复方案

使用Flashback工具恢复数据。

原理：修改 binlog内容，拿回原库重放。如果误删数据涉及到了多个事务的话，需要将事务的顺序调过来再执行。

使用前提：binlog_format=row 和 binlog_row_image=FULL。

8.2 truncate/drop：误删数据库/表

8.2.1 背景

delete 全表是很慢的，需要生成回滚日志、写 redo、写 binlog。所以，从性能角度考虑，优先考虑使用 truncate table 或者 drop table 命令。

使用 delete 命令删除的数据，你还可以用 Flashback 来恢复。而使用 truncate/drop table 和 drop database 命令删除的数据，就没办法通过 Flashback 来恢复了。因为，即使我们配置了 binlog_format=row，执行这三个命令时，记录的 binlog 还是 statement 格式。binlog 里面就只有一个 truncate/drop 语句，这些信息是恢复不出数据的。

8.2.2 预防方案

在生产环境中，可以采用下面建议的方案来尽量避免使用 TRUNCATE/DROP 语句误删数据库/表。

(1)账户权限分离。

对于核心的数据库，一般不能随便分配写权限。想要获取写权限，需要进行审批，并且不同的账号、不同的数据库之间要进行权限分离，避免一个账户可以删除所有数据库。例如，只授予业务开发人员 DML 权限，而不给予其 TRUNCATE/DROP 权限。即使是 DBA 团队人员，日常也只能使用只读账号，在必要的时候才能使用有更新权限的账号。

(2)制定操作规范。

例如，在删除数据库之前，先对该表执行执行重命名操作（比如加_to_be_deleted），确保对业务无影响后再删除该张表。

(3)设置延迟复制备库。

简单地说，延迟复制就是设置一个固定的延迟时间，通过 CHANGE MASTER TO MASTER_DELAY=N 命令可以指定这个备库持续保持跟主库有 N 秒的延迟，例如，设置为 3600 秒，表示让从库落后主库 1 小时。延迟复制可以在数据库被误操作后，快速地恢复数据。例如，有人误操作了主库中的某张表，那么，在延迟时间内，从库中的数据并没有发生改变，就可以用从库中的数据进行快速恢复。

此外，延迟复制还可以用来解决以下问题：

• 用来做延迟测试，比如做好的数据库读写分离，把从库作为读库，那么想知道当数据产生延迟的时候到底会发生什么，就可以使用这个特性模拟延迟。
• 用于老数据的查询等需求，比如你经常需要查看某天前一个表或者字段的数值，你可能需要把备份恢复后进行查看，如果有延迟从库，比如延迟一周，那么就可以解决这样类似的需求。

8.2.3 数据恢复方案

使用 TRUNCATE/DROP 语句删掉的数据是没法通过二进制日志恢复的。因为二进制日志里面只有一条 TRUNCATE/DROP 语句，单凭这些信息是无法恢复数据的。

在这种情况下，要想恢复数据，就需要采用全量备份+增量日志的方案。该方案要求线上定期的全量备份，并且实时备份二进制日志。在这两个条件都具备的情况下，可以使用二进制日志恢复数据；否则使用本章讲到的物理备份或逻辑备份的方式恢复误操作的数据。

例如：有人误删了一个库，时间为下午 3 点。步骤如下：

1. 取最近一次全量备份。假设设置数据库备份是一天一备，最近备份数据是当天凌晨 2 点；
2. 用备份恢复出一个临时库；（注意：这里选择临时库，而不是直接操作主库）
3. 取出凌晨 2 点之后的 binlog 日志；
4. 剔除误删除数据的语句外，其它语句全部应用到临时库。（前面讲过 binlog 的恢复）
5. 最后恢复到主库

8.3 rm：误删 MySQL 实例

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

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