MySQL8.0入门-数据备份和恢复

尽管采取了一些管理措施来保证数据库的安全，但是不确定的意外情况总是有可能造成数据的损失，例如意外的停电、管理员不小心的操作失误都可能会造成数据的丢失。保证数据安全最重要的一个措施是确保对数据进行定期备份。如果数据库中的数据丢失或者出现错误，可以使用备份的数据进行恢复，这样就尽可能地降低了意外原因导致的损失。MySQL提供了多种方法对数据进行备份和恢复。

数据备份

使用MySQLdump命令备份

MySQLdump是MySQL提供的一个非常有用的数据库备份工具。MySQLdump命令执行时，可以将数据库备份成一个文本文件，该文件中实际包含了多个CREATE和INSERT语句，使用这些语句可以重新创建表和插入数据。

MySQLdump备份数据库语句的基本语法格式如下：

mysqldump -u user -h host -ppassword dbname [tbname, [tbname...]] > filename.sql

user表示用户名称；host表示登录用户的主机名称；password为登录密码；dbname为需要备份的数据库名称；tbname为dbname数据库中需要备份的数据表，可以指定多个需要备份的表；右箭头符号>告诉MySQLdump将备份数据表的定义和数据写入备份文件；filename.sql为备份文件的名称。

1. 使用MySQLdump备份单个数据库中的所有表

为了更好地理解MySQLdump工具是如何工作的，这里给出一个完整的数据库例子。首先登录MySQL，按下面数据库结构创建booksDB数据库和各个表，并插入数据记录。数据库和表定义如下：

create database booksDB;
use booksDB;

create table books
(
	books int not null primary key,
    bk_title varchar(50) not null,
    copyright year not null
);

insert into books value (11078, 'Learning Mysql', 2010),
(11079, 'Study html', 2011),
(11080, 'How to use php', 2012),
(11081, 'Teach yourself javascript', 2013),
(11082, 'Guide to Mysql8.0', 2014);

create table authors
(
	auth_id int not null primary key,
    auth_name varchar(20),
    auth_gender char(1)
);

insert into authors values (1001, 'WriteA', 'f'),
(1002, 'WriteB', 'm'),
(1003, 'WriteC', 'n'),
(1004, 'WriteD', 'l'),
(1005, 'WriteE', 'b');

create table authorbook
(
	auth_id int not null,
    bk_id int not null,
    primary key(auth_id, bk_id),
    foreign key(auth_id) references authors(auth_id),
    foreign key(bk_id) references books(books)
);

insert into authorbook values (1001, 11078),
(1002, 11079),
(1003, 11080),
(1004, 11081),
(1005, 11082);

完成数据插入后打开操作系统命令行输入窗口，输入备份命令如下：

C:\>mysqldump -u root -p booksdb > D:/booksdb_20220205.sql
Enter password: ****

输入的路径必须存在，路径下文件夹也存在。

输入密码之后，MySQL便对数据库进行了备份，在D:\文件夹下面查看刚才备份过的文件，使用文本查看器打开文件可以看到其部分文件内容大致如下：

-- MySQL dump 10.13  Distrib 8.0.28, for Win64 (x86_64)
--
-- Host: localhost    Database: booksdb
-- ------------------------------------------------------
-- Server version	8.0.28

/*!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 `authorbook`
--

DROP TABLE IF EXISTS `authorbook`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `authorbook` (
  `auth_id` int NOT NULL,
  `bk_id` int NOT NULL,
  PRIMARY KEY (`auth_id`,`bk_id`),
  KEY `bk_id` (`bk_id`),
  CONSTRAINT `authorbook_ibfk_1` FOREIGN KEY (`auth_id`) REFERENCES `authors` (`auth_id`),
  CONSTRAINT `authorbook_ibfk_2` FOREIGN KEY (`bk_id`) REFERENCES `books` (`books`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `authorbook`
--

LOCK TABLES `authorbook` WRITE;
/*!40000 ALTER TABLE `authorbook` DISABLE KEYS */;
INSERT INTO `authorbook` VALUES (1001,11078),(1002,11079),(1003,11080),(1004,11081),(1005,11082);
/*!40000 ALTER TABLE `authorbook` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `authors`
--

DROP TABLE IF EXISTS `authors`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `authors` (
  `auth_id` int NOT NULL,
  `auth_name` varchar(20) DEFAULT NULL,
  `auth_gender` char(1) DEFAULT NULL,
  PRIMARY KEY (`auth_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `authors`
--

LOCK TABLES `authors` WRITE;
/*!40000 ALTER TABLE `authors` DISABLE KEYS */;
INSERT INTO `authors` VALUES (1001,'WriteA','f'),(1002,'WriteB','m'),(1003,'WriteC','n'),(1004,'WriteD','l'),(1005,'WriteE','b');
/*!40000 ALTER TABLE `authors` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `books`
--

DROP TABLE IF EXISTS `books`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `books` (
  `books` int NOT NULL,
  `bk_title` varchar(50) NOT NULL,
  `copyright` year NOT NULL,
  PRIMARY KEY (`books`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `books`
--

LOCK TABLES `books` WRITE;
/*!40000 ALTER TABLE `books` DISABLE KEYS */;
INSERT INTO `books` VALUES (11078,'Learning Mysql',2010),(11079,'Study html',2011),(11080,'How to use php',2012),(11081,'Teach yourself javascript',2013),(11082,'Guide to Mysql8.0',2014);
/*!40000 ALTER TABLE `books` 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 2022-02-05 22:23:35

可以看到，备份文件包含了一些信息，文件开头首先表明了备份文件使用的MySQLdump工具的版本号；然后是备份账户的名称和主机信息，以及备份的数据库的名称，最后是MySQL服务器的版本号，在这里为8.0.28。

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

备份文件中以“--”字符开头的行为注释语句；以“/!”开头、“/”结尾的语句为可执行的MySQL注释，这些语句可以被MySQL执行，但在其他数据库管理系统中将被作为注释忽略，以提高数据库的可移植性。

2. 使用MySQLdump备份数据库中的某个表

MySQLdump还可以备份数据中的某个表，其语法格式为：

mysqldump -u user -h host -p dbname [tbname, [tbname...]] > filename.sql

tbname表示数据库中的表名，多个表名之间用空格隔开。

备份表和备份数据库中所有表的语句中不同的地方在于，要在数据库名称dbname之后指定需要备份的表名称。

C:\>mysqldump -u root -p booksdb books > D:/booksdb_20220206.sql
Enter password: ****

该语句创建名称为booksdb_20220206.sql的备份文件，文件中包含了前面介绍的SET语句等内容，不同的是，该文件只包含books表的CREATE和INSERT语句。

3. 使用MySQLdump备份多个数据库

如果要使用MySQLdump备份多个数据库，就需要使用--databases参数。备份多个数据库的语句格式如下：

mysqldump -u user -h host -p --databases [dbname,  [dbname...] > filename.sql

使用--databases参数之后，必须指定至少一个数据库的名称，多个数据库名称之间用空格隔开。

比如：

C:\>mysqldump -u root -p --databases booksdb test_db> D:/booksdb_20220207.sql

另外，使用--all-databases参数可以备份系统中所有的数据库，语句如下：

mysqldump -u user -h host -p --all-databases > filename.sql

使用参数--all-databases时，不需要指定数据库名称。

使用MySQLdump备份服务器中的所有数据库，输入语句如下：

mysqldump -u root -p --all-databases > D:/alldbinMySQL.sql

该语句创建名称为alldbinMySQL.sql的备份文件，文件中包含了对系统中所有数据库的备份信息。

如果在服务器上进行备份，并且表均为MyISAM表，就应该考虑使用MySQLhotcopy，因为可以更快地进行备份和恢复。

直接复制整个数据库目录

因为MySQL表保存为文件方式，所以可以直接复制MySQL数据库的存储目录及文件进行备份。MySQL的数据库目录位置不一定相同，在Windows平台下，MySQL 8.0存放数据库的目录通常默认为“C:\Documents and Settings\All Users\ApplicationData\MySQL\MySQL Server 8.0\data”或者其他用户自定义目录；在Linux平台下，数据库目录位置通常为/var/lib/MySQL/，不同Linux版本下目录会有所不同。

这是一种简单、快速、有效的备份方式。要想保持备份的一致性，备份前需要对相关表执行LOCK TABLES操作，然后对表执行FLUSH TABLES。这样当复制数据库目录中的文件时，允许其他客户继续查询表。需要FLUSH TABLES语句来确保开始备份前将所有激活的索引页写入硬盘。当然，也可以停止MySQL服务再进行备份操作。

这种方法虽然简单，但并不是最好的方法。因为这种方法对InnoDB存储引擎的表不适用。使用这种方法备份的数据最好恢复到相同版本的服务器中，不同的版本可能不兼容。

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

使用MySQLhotcopy工具快速备份

MySQLhotcopy是一个Perl脚本，最初由Tim Bunce编写并提供。它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。它是备份数据库或单个表最快的途径，但它只能运行在数据库目录所在的机器上，并且只能备份MyISAM类型的表。MySQLhotcopy在UNIX系统中运行。

数据恢复

管理人员操作的失误、计算机故障以及其他意外情况，都会导致数据的丢失和破坏。当数据丢失或意外破坏时，可以通过恢复已经备份的数据尽量减少数据丢失和破坏造成的损失。

使用MySQL命令恢复

备份的sql文件中包含CREATE、INSERT语句（有时也会有DROP语句）。MySQL命令可以直接执行文件中的这些语句。其语法如下：

mysql -u user -p [dbname] < filename.sql

user是执行backup.sql中语句的用户名；-p表示输入用户密码；dbname是数据库名。如果filename.sql文件为MySQLdump工具创建的包含创建数据库语句的文件，执行的时候不需要指定数据库名。

将之前得备份导入数据库：

C:\>mysql -u root -p booksdb < D:/booksdb_20220206.sql
Enter password: ****

这里得数据库必须存在否者会报错

直接复制到数据库目录

如果数据库通过复制数据库文件备份，可以直接复制备份的文件到MySQL数据目录下实现恢复。通过这种方式恢复时，保存备份数据的数据库和待恢复的数据库服务器的主版本号必须相同。而且这种方式只对MyISAM引擎的表有效，对于InnoDB引擎的表不可用。

对于InnoDB引擎得表，需要把一个表的文件夹+ibdata1（innodb才需要拷贝这个）拷贝进去，删掉ib_logfile1和ib_logfile0两个文件，重新运行mysql。

ibdata1文件一定要赋予执行权限。

执行恢复以前关闭MySQL服务，将备份的文件或目录覆盖MySQL的data目录，启动MySQL服务。对于Linux/UNIX操作系统来说，复制完文件需要将文件的用户和组更改为MySQL运行的用户和组，通常用户是MySQL，组也是MySQL。

数据库迁移

数据库迁移就是把数据从一个系统移动到另一个系统上。数据迁移有以下原因：

1. 需要安装新的数据库服务器。
2. MySQL版本更新。
3. 数据库管理系统的变更（如从Microsoft SQL Server迁移到MySQL）。

相同版本的MySQL数据库之间的迁移

相同版本的MySQL数据库之间的迁移就是在主版本号相同的MySQL数据库之间进行数据库移动。迁移过程其实就是在源数据库备份和目标数据库恢复过程的组合。

在讲解数据库备份和恢复时，已经知道最简单的方式是通过复制数据库文件目录，但是此种方法只适用于MyISAM引擎的表。而对于InnoDB表，不能用直接复制文件的方式备份数据库，因此最常用和最安全的方式是使用MySQLdump命令导出数据，然后在目标数据库服务器使用MySQL命令导入。

比如，将www.abc.com主机上的MySQL数据库全部迁移到www.bcd.com主机上。在www.abc.com主机上执行的命令如下：

mysqldump -h www.bac.com -uroot -ppassword dbname | mysql -h www.bcd.com -uroot -ppassword

MySQLdump导入的数据直接通过管道符“｜”传给MySQL命令导入到主机www.bcd.com数据库中，dbname为需要迁移的数据库名称，如果要迁移全部的数据库，可使用参数--all-databases。

不同版本得MySQL数据库之间得迁移

因为数据库升级等原因，需要将较旧版本MySQL数据库中的数据迁移到较新版本的数据库中。MySQL服务器升级时，需要先停止服务，然后卸载旧版本，并安装新版的MySQL，这种更新方法很简单，如果想保留旧版本中的用户访问控制信息，就需要备份MySQL中的MySQL数据库，在新版本MySQL安装完成之后，重新读入MySQL备份文件中的信息。

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

新版本会对旧版本有一定兼容性。从旧版本的MySQL向新版本的MySQL迁移时，对于MyISAM引擎的表，可以直接复制数据库文件，也可以使用MySQLhotcopy工具、MySQLdump工具。对于InnoDB引擎的表，一般只能使用MySQLdump将数据导出。然后使用MySQL命令导入到目标服务器上。从新版本向旧版本MySQL迁移数据时要特别小心，最好使用MySQLdump命令导出，然后导入目标数据库中。

不同数据库之间的迁移

不同类型的数据库之间的迁移，是指把MySQL的数据库转移到其他类型的数据库，例如从MySQL迁移到Oracle，从Oracle迁移到MySQL，从MySQL迁移到SQL Server等。

迁移之前，需要了解不同数据库的架构，比较它们之间的差异。不同数据库中定义相同类型的数据的关键字可能会不同。例如，MySQL中日期字段分为DATE和TIME两种，而Oracle日期字段只有DATE。另外，数据库厂商并没有完全按照SQL标准来设计数据库系统，导致不同的数据库系统的SQL语句有差别。例如，MySQL几乎完全支持标准SQL语言，而Microsoft SQL Server使用的是T-SQL语言，T-SQL中有一些非标准的SQL语句，因此在迁移时必须对这些语句进行语句映射处理。

数据库迁移可以使用一些工具，例如在Windows系统下，可以使用MyODBC实现MySQL和SQL Server之间的迁移。MySQL官方提供的工具MySQL Migration Toolkit也可以在不同数据库间进行数据迁移。