MySQL数据库备份与恢复

Posted on Edited on In , Views: Word count in article: 22k Reading time ≈ 20 mins.
  • 在任何数据库环境中，总会有不确定的意外情况发生，比如例外的停电、计算机系统中的各种软硬件故障、人为破坏、管理员误操作等是不可避免的，这些情况可能会导致数据的丢失、服务器瘫痪等严重的后果。存在多个服务器时，会出现主从服务器之间的数据同步问题。
  • 为了有效防止数据丢失，并将损失降到最低，应定期对MySQL数据库服务器做备份。如果数据库中的数据丢失或者出现错误，可以使用备份的数据进行恢复。主从服务器之间的数据同步问题可以通过复制功能实现。

1、物理备份与逻辑备份

  • 物理备份：备份数据文件，转储数据库物理文件到某一目录。物理备份恢复速度比较快，但占用空间比较大MySQL中可以用xtrabackup工具来进行物理备份。
  • 逻辑备份：对数据库对象利用工具进行导出工作，汇总入备份文件内。逻辑备份恢复速度慢，但占用空间小，更灵活。MySQL中常用的逻辑备份工具为mysqldump。逻辑备份就是备份sql语句，在恢复的时候执行备份的sql语句实现数据库数据的重现。

2、mysqldump实现逻辑备份

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

2.1 备份一个数据库

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

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

  • 基本语法：

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

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

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    root@beb6345b706a:/# mkdir /var/lib/mysql/backup
    root@beb6345b706a:/# mysqldump dbtest >/var/lib/mysql/backup/dbtest.sql -uroot -p
    Enter password: 
    root@beb6345b706a:/# cd /var/lib/mysql/backup
    root@beb6345b706a:/var/lib/mysql/backup# ls -l
    total 2644
    -rw-r--r-- 1 root root 2705480 May  6 02:59 dbtest.sql
    root@beb6345b706a:/var/lib/mysql/backup# cat dbtest.sql |head -n 100
    -- MySQL dump 10.13  Distrib 8.0.26, for Linux (x86_64)
    --
    -- Host: localhost    Database: dbtest
    -- ------------------------------------------------------
    -- Server version       8.0.26

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

    DROP TABLE IF EXISTS `account`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!50503 SET character_set_client = utf8mb4 */;
    CREATE TABLE `account` (
      `id` int NOT NULL AUTO_INCREMENT,
      `name` varchar(32) DEFAULT NULL,
      `balance` decimal(10,2) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3;
    /*!40101 SET character_set_client = @saved_cs_client */;

    --
    -- Dumping data for table `account`
    --

    LOCK TABLES `account` WRITE;
    /*!40000 ALTER TABLE `account` DISABLE KEYS */;
    INSERT INTO `account` VALUES (4,'张三',40.00),(5,'李四',0.00),(6,'王五',100.00);
    /*!40000 ALTER TABLE `account` 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-05-06  2:59:19

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

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

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

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

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

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

      1
      2
      # 该语句将用户定义的变量@old_character_set_client中保存的值赋给实际的系统变量character-set_client。
      /*140101 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参数：

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

2.3 备份部分数据库

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

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

2.4 备份部分表

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

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

2.5 备份单表的部分数据

  • 有些时候一张表的数据量很大，我们只需要部分数据。这时就可以使用--where选项了。where后面附带需要满足的条件。举例：备份student表中id小于10的数据：

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

2.6 排除某些表的备份

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

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

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

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

    • 只备份结构

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

    • 只备份数据

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

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

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

    1
    2
    3
    4
    5
    6
    # 使用下面的SQL可以查看当前库有哪些存储过程或者函数。
    mysql> SELECT SPECIFIC_NAME,ROUTINE_TYPE ,ROUTINE_SCHEMA FROM
    information_schema.Routines WHERE ROUTINE_SCHEMA="dbtest";

    # 备份数据，函数以及存储过程。
    mysqldump -uroot -p -R -E --databases dbtest > fun_dbtest_bak.sql

2.9 mysqldump常用选项

  • mysqldump其他常用选项如下：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    --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，禁止转储文件中的其他信息，例如程序版本、服务器版本和主机。--skipcomments与--comments=0的结果相同。默认值为1，即包括额外信息。

    --compact：产生少量输出。该选项禁用注释并启用--skip-add-drop-tables、--no-set-names、--skipdisable-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：在主复制服务器上，完成转储操作后删除二进制日志。该选项自动启用-masterdata。

    --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 –help，可以获得特定版本的完整选项列表。

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

3、mysql命令恢复数据

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

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

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

3.1 单库备份中恢复单库

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

    1
    mysql -uroot -p < dbtest.sql

  • 否则需要指定数据库名称，如下所示：

    1
    mysql -uroot -p dbtest < dbtest.sql

3.2 全量备份恢复

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

    1
    2
    3
    mysql –u root –p < all.sql

    mysql -uroot -pxxxxxx < all.sql

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

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

3.3 从全量备份中恢复单库

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

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

3.4 从单库备份中恢复单表

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

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    cat dbtest.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `class`/!d;q' >
    class_structure.sql

    cat dbtest.sql | grep --ignore-case 'insert into `class`' > class_data.sql

    # 用shell语法分离出创建表的语句及插入数据的语句后再依次导出即可完成恢复。
    use dbtest;
    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、物理备份：直接复制整个数据库

  • 直接将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之前。

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

  • 步骤：

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

  • 要求：

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

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    # 使用SELECT…INTO OUTFILE将atguigu数据库中account表中的记录导出到文本文件。
    use atguigu;
    select * from account;
    mysql> select * from account;
    +----+--------+---------+
    | id | name   | balance |
    +----+--------+---------+
    | 1  | 张三    |   90    |
    | 2  | 李四    |   100   |
    | 3  | 王五    |   0     |
    +----+--------+---------+
    3 rows in set (0.01 sec)

    # mysql默认对导出的目录有权限限制，也就是说使用命令行进行导出的时候，需要指定目录进行操作。查询secure_file_priv值：
    mysql> SHOW GLOBAL VARIABLES LIKE '%secure%';
    +--------------------------+-----------------------+
    | Variable_name            | Value                 |
    +--------------------------+-----------------------+
    | require_secure_transport | OFF                   |
    | secure_file_priv         | /var/lib/mysql-files/ |
    +--------------------------+-----------------------+
    2 rows in set (0.02 sec)
    # 参数secure_file_priv的可选值和作用分别是：
    # --->如果设置为empty，表示不限制文件生成的位置，这是不安全的设置；
    # --->如果设置为一个表示路径的字符串，就要求生成的文件只能放在这个指定的目录，或者它的子目录；
    # --->如果设置为NULL，就表示禁止在这个MySQL实例上执行select ... into outfile操作。

    # 上面结果中显示，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命令导出文本文件

  • ②使用mysqldump命令导出文本文件。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    # 使用mysqldump命令将将atguigu数据库中account表中的记录导出到文本文件：
    mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigu account

    # 打开account.sql文件，其内容包含创建account表的CREATE语句。
    [root@node1 mysql-files]# cat account.sql
    -- MySQL dump 10.13 Distrib 8.0.26, for Linux (x86_64)
    --
    -- Host: localhost Database: atguigu
    -- ------------------------------------------------------
    -- Server version 8.0.26
    /*!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 `account`
    --
    DROP TABLE IF EXISTS `account`;
    /*!40101 SET @saved_cs_client = @@character_set_client */;
    /*!50503 SET character_set_client = utf8mb4 */;
    CREATE TABLE `account` (
    `id` int NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `balance` int NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;
    /*!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 2022-01-07 23:19:27

    # 打开account.txt文件，其内容只包含account表中的数据。
    [root@node1 mysql-files]# cat account.txt
    1	张三	90
    2	李四	100
    3	王五	0
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    # 使用mysqldump将atguigu数据库中的account表导出到文本文件，使用FIELDS选项，要求字段之间使用逗号“，”间隔，所有字符类型字段值用双引号括起来：
    mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigu account --fields-terminatedby=',' --fields-optionally-enclosed-by='\"'

    # 语句mysqldump语句执行成功之后，指定目录下会出现两个文件account.sql和account.txt。
    # 打开account.sql文件，其内容包含创建account表的CREATE语句。
    [root@node1 mysql-files]# cat account.sql
    -- MySQL dump 10.13 Distrib 8.0.26, for Linux (x86_64)
    --
    -- Host: localhost Database: atguigu
    -- ------------------------------------------------------
    -- Server version 8.0.26
    /*!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 `account`
    --
    DROP TABLE IF EXISTS `account`;
    /*!40101 SET @saved_cs_client = @@character_set_client */;
    /*!50503 SET character_set_client = utf8mb4 */;
    CREATE TABLE `account` (
    `id` int NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `balance` int NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;
    /*!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 2022-01-07 23:36:39

    # 打开account.txt文件，其内容包含创建account表的数据。从文件中可以看出，字段之间用逗号隔开，字符类型的值被双引号括起来。
    [root@node1 mysql-files]# cat account.txt
    1,"张三",90
    2,"李四",100
    3,"王五",0

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

  • ③使用mysql命令导出文本文件。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    # 使用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
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    # 将atguigu数据库account表中的记录导出到文本文件，使用--veritcal参数将该条件记录分为多行显示：
    mysql -uroot -p --vertical --execute="SELECT * FROM account;" atguigu >
    "/var/lib/mysql-files/account_1.txt"

    # 打开account_1.txt文件，其内容包含创建account表的数据。
    [root@node1 mysql-files]# cat account_1.txt
    *************************** 1. row ***************************
    id: 1
    name: 张三
    balance: 90
    *************************** 2. row ***************************
    id: 2
    name: 李四
    balance: 100
    *************************** 3. row ***************************
    id: 3
    name: 王五
    balance: 0
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    # 将atguigu数据库account表中的记录导出到xml文件，使用--xml参数，具体语句如下。
    mysql -uroot -p --xml --execute="SELECT * FROM account;" atguigu>"/var/lib/mysqlfiles/account_3.xml"

    [root@node1 mysql-files]# cat account_3.xml
    <?xml version="1.0"?>
    <resultset statement="SELECT * FROM account"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <row>
    <field name="id">1</field>
    <field name="name">张三</field>
    <field name="balance">90</field>
    </row>
    <row>
    <field name="id">2</field>
    <field name="name">李四</field>
    <field name="balance">100</field>
    </row>
    <row>
    <field name="id">3</field>
    <field name="name">王五</field>
    <field name="balance">0</field>
    </row>
    </resultset>

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

6.2 表的导入

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 使用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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 选择数据库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如下：
mysql> select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
| 1  | 张三    |   90    |
| 2  | 李四    |   100   |
| 3  | 王五    |   0     |
+----+--------+---------+
3 rows in set (0.00 sec)

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 导出文件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-terminatedby=',' --fields-optionally-enclosed-by='\"'

# 查询account表中的数据：
mysql> select * from account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
| 1  | 张三    |   90    |
| 2  | 李四    |   100   |
| 3  | 王五    |   0     |
+----+--------+---------+
3 rows in set (0.00 sec)
  • 除了前面介绍的几个选项之外，mysqlimport支持需要选项，常见的选项有：
    • –columns=-column_Iist，-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，-1：处理文本文件前锁定所有表，以便写入。这样可以确保所有表在服务器上保持同步。
    • –password[=password]，-p[password]：当连接服务器时使用的密码。如果使用短选项形式（-p），选项和密码之间不能有空格。如果在命令行中–password或-p选项后面没有密码值，就提示输入一个密码。
    • –port=port_num，-Pport_num：用户连接的TCP/IP端口号。
    • –protocol={TCPISOCKETIPIPEIMEMORY）：使用的连接协议。
    • –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 迁移注意点

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

    • 方式1：因为迁移前后MySQL数据库的主版本号相同，所以可以通过复制数据库目录来实现数据库迁移，但是物理迁移方式只适用于MyISAM引擎的表。对于InnoDB表，不能用直接复制文件的方式备份数据库。
    • 方式2：最常见和最安全的方式是使用mysqldump命令导出数据，然后在目标数据库服务器中使用MySQL命令导入。
    1
    2
    3
     #host1的机器中备份所有数据库,并将数据库迁移到名为host2的机器上
    mysqldump –h host1 –uroot –p –-all-databases|
    mysql –h host2 –uroot –p
    • 在上述语句中，“|”符号表示管道，其作用是将mysqldump备份的文件给mysql命令；“–all-databases”表 示要迁移所有的数据库。通过这种方式可以直接实现迁移。

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

    • 例如，原来很多服务器使用5.7版本的MySQL数据库，在8.0版本推出来以后，改进了5.7版本的很多缺陷， 因此需要把数据库升级到8.0版本。
    • 旧版本与新版本的MySQL可能使用不同的默认字符集，例如有的旧版本中使用latin1作为默认字符集，而 最新版本的MySQL默认字符集为utf8mb4。如果数据库中有中文数据，那么迁移过程中需要对默认字符集进行修改，不然可能无法正常显示数据。
    • 高版本的MySQL数据库通常都会兼容低版本，因此可以从低版本的MySQL数据库迁移到高版本的MySQL数据库。

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

    • 不同数据库之间迁移是指从其他类型的数据库迁移到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 迁移小结