MySQL_备份与迁移，导入与导出

曾经终败给现在 2023-07-03 12:07 20阅读 0赞

数据备份是管理数据库非常重要的工作，系统意外崩溃或硬件损坏都可能造成数据的丢失，所以应该定期备份数据库，意外发生时可使用备份还原数据库，尽量减少损失。

# 数据备份 #

## mysqldump命令 ##

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

mysqldump -u user -h host -p dbname [tbname...]>filename.sql
    
    -- user 用户名
    -- host 登录主机
    -- dbname 数据库名
    -- tbname 表名
    -- > 指定备份文件
    -- filename 备份文件名

**备份一个数据库中所有表**

mysqldump -h localhost -u root -p scm>d:/scm_backup.sql

回车后输入密码便对数据进行了备份，备份文件示例

-- MySQL dump 10.13  Distrib 5.7.28, for Win64 (x86_64)
    --
    -- Host: localhost    Database: scm
    -- ------------------------------------------------------
    -- Server version	5.7.28-log
    
    /*!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 */;
    /*!40101 SET NAMES utf8 */;
    /*!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 `category`
    --
    
    DROP TABLE IF EXISTS `category`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `category` (
      `CategoryID` int(11) NOT NULL AUTO_INCREMENT,
      `Name` varchar(20) COLLATE utf8_bin NOT NULL,
      `Remark` varchar(200) COLLATE utf8_bin DEFAULT NULL,
      PRIMARY KEY (`CategoryID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Dumping data for table `category`
    --
    
    LOCK TABLES `category` WRITE;
    /*!40000 ALTER TABLE `category` DISABLE KEYS */;
    INSERT INTO `category` VALUES (1,'水果',''),(2,'饮料','甜甜的'),(3,'儿童图书',''),(4,'糖果',''),(7,'粮油','甜甜的'),(9,'家居用品','甜甜的');
    /*!40000 ALTER TABLE `category` ENABLE KEYS */;
    UNLOCK TABLES;
    
    --

文件开头首先表明了使用工具的版本号，备份账户信息，主机信息，备份数据库名称和MySQL服务器版本号。

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

备份文件中以“–” 开头的为注释语句，以“/\* ！”和“\*/”结尾的语句为可执行的MySQL注释，这些语句会被MySQL执行，但在其他数据库管理系统中会被作为注释，提高数据库的移植性。

另外一些语句以数字开头，代表了MySQL的版本号限制，这些语句只有在指定的MySQL版本或更高版本中才能执行。

**备份一个数据库中某个表**

mysqldump -h localhost -u root -p scm category>d:/scm_category_backup.sql

**备份多个数据库**

mysqldump -h localhost -u root -p --databases dbname[ dbname ...]>filename.sql

**备份所有数据库**

mysqldump -h localhost -u root -p --all-databases>filename.sql

## 直接复制数据库目录进行备份 ##

因为MySQL表保存为文件形式，所以可以直接复制数据库的存储目录和文件进行备份。MySQL的存储目录位置不一定相同，Windows下一般安装目录中的data目录，Linux下通常是/var/lib/mysql，需查找确认。

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

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

## 使用mysqlhotcopy工具快速备份 ##

mysqlhotcopy是一个Perl脚本，使用lock tables，flush tables，cp或scp来快速备份数据库。它是备份数据库或单个表的最快途径，但它只能运行在数据库目录所在的机器上，并且只能备份MyISAM类型的表。在Linux中运行：

mysqlhotcopy db_name1,...db_namen path/to/new_directory

要执行mysqlhotcopy必须可以访问要备份的表文件，具有查询表的select权限。

# 数据还原 #

## 使用MySQL命令 ##

对于已经备份的包含create和insert语句的文本文件，可以通过MySQL命令导入到数据库中。

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

如果备份文件时通过Mysqldump创建的包含创建数据库语句的文件，执行的时候不需要指定数据库名称。

如果已经登陆MySQL服务器，还可以使用source命令导入SQL文件

source filename

执行source前，必须使用use语句选择数据库

## 直接复制到数据库目录 ##

如果数据库通过复制数据库文件实现备份，可以直接复制备份文件到MySQL数据目录下实现还原。通过这种还原方式，必须保持备份的数据库和还原的数据库主版本号一致，而且这种方式对MyISAM有效，对InnoDB无效。

## mysqlhotcopy快速恢复 ##

将备份的数据库文件复制到MySQL存放数据的位置，重启服务。如果root用户执行操作，必须指定数据库文件的所有者

cp -R /user/backup/test /user/local/mysql/data

如果要恢复的数据库已经存在，需要使用drop删除之后再恢复。

# 数据库迁移 #

数据库迁移就是把数据从一个系统移动到另一个系统上，数据迁移的原因：

*  需要安装新版本的数据库服务器
 *  MySQL版本更新
 *  数据库管理系统变更

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

最常用和最安全的方式是使用Mysqldump命令导出数据，在目标数据库服务器中年通过MySQL命令导入

mysqldump -h host1 -u root -p password dbname|mysql -h host2 -u root -p password dbname

导出的数据可以直接通过管道符“|”传入MySQL命令。

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

新版本对旧版本有一定的兼容性。从旧版本向新版本迁移时，对于MyISAM的表可以直接复制数据文件，也可以使用Mysqldump和mysqlhotcopy。对于InnoDB的表，一般只能通过Mysqldump导出再使用MySQL命令导入到目标服务器上。从新版本向旧版本迁移要小心，最好Mysqldump。

## 不同数据库之间迁移 ##

迁移之前，需要了解不同数据库的架构，比较它们的差异。因为数据库厂商不会按照标准SQL设计数据库，导致不同数据库中的SQL语句差异，迁移时需要对这些语句进行映射处理。

可使用工具完成数据库迁移，如使用myodbc实现MySQL和SQL server 之间的迁移。

# 表的导出 #

有事需要将MySQL数据库中的数据导出到外部存储文件中，MySQL数据库中的数据可以导出成SQL文件，xml文件，HTML文件，同样这些文件也可以导入到MySQL数据库中

MySQL允许使用具有导出定义的select语句进行数据的导出操作，该文件被创建在服务器主机上，必须拥有文件写入的权限，才能使用此语法，可以将被选择的数据写入文件

select ... into outfile filename [options]
    select * from emp where sal>2000 into outfile 'd:/emp_backup.txt'

使用Mysqldump命令

mysqldump -h localhost -u root -p scm>d:/scm_backup.sql

使用MySQL命令导出文件

mysql -u root -p -execute="select ..." dbname>filename

# 表的导入 #