首页 > 编程笔记 > MySQL笔记 阅读：8

MySQL数据备份的3种方法（新手必看）

数据备份是数据库管理员非常重要的工作之一。系统意外崩溃或者硬件损坏都可能导致数据库的丢失，因此 MySQL 管理员应该定期备份数据库，使得在意外情况发生时的损失尽可能降低。

本节将介绍数据备份的 3 种方法。

使用mysqldump命令备份

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

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

mysqldump 备份数据库语句的基本语法格式如下： 
mysqldump  –u user –h host –ppassword dbname[tbname, [tbname...]]> filename.sql

1) 使用mysqldump备份单个数据库中的所有表

【实例 1】使用 mysqldump 命令备份数据库中的所有表，执行过程如下：
为了更好地理解 mysqldump 工具是如何工作的，这里给出一个完整的数据库例子。首先登录 MySQL，按下面数据库结构创建 booksDB 数据库和各个表，并插入数据记录。数据库和表定义如下： 
CREATE DATABASE booksDB;
use booksDB;

CREATE TABLE books
(
    bk_id INT NOT NULL PRIMARY KEY,
    bk_title VARCHAR(50) NOT NULL,
    copyright YEAR NOT NULL
);

INSERT INTO books
VALUES (11078, 'Learning MySQL', 2010),
       (11033, 'Study Html', 2011),
       (11035, 'How to use php', 2003),
       (11072, 'Teach yourself javascript', 2005),
       (11028, 'Learning C++', 2005),
       (11069, 'MySQL professional', 2009),
       (11026, 'Guide to MySQL 8.0', 2008),
       (11041, 'Inside VC++', 2011);

CREATE TABLE authors
(
    auth_id INT NOT NULL PRIMARY KEY,
    auth_name VARCHAR(20),
    auth_gender CHAR(1)
);

INSERT INTO authors
VALUES (1001, 'WriterX', 'f'),
       (1002, 'WriterA', 'f'),
       (1003, 'WriterB', 'm'),
       (1004, 'WriterC', 'f'),
       (1011, 'WriterD', 'f'),
       (1012, 'WriterE', 'm'),
       (1013, 'WriterF', 'm'),
       (1014, 'WriterG', 'f'),
       (1015, 'WriterH', 'f');

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 (bk_id)
);

INSERT INTO authorbook
VALUES (1001, 11033), (1002, 11035), (1003, 11072), (1004, 11028),
       (1011, 11078), (1012, 11026), (1012, 11041), (1014, 11069);
完成数据插入后打开操作系统命令行输入窗口，输入备份命令如下： 
C:\ >mysqldump -u root -p booksdb > C:/backup/booksdb_20220301.sql
     Enter password: **

这里要保证 C 盘下 backup 文件夹存在，否则将提示错误信息：系统找不到指定的路径。

输入密码之后，MySQL 便对数据库进行了备份，在 C:\backup 文件夹下面查看刚才备份过的文件，使用文本查看器打开文件可以看到其部分文件内容大致如下： 
-- 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 */;
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 */;
SET character_set_client = utf8mb4 ;
CREATE TABLE `authorbook` (
  `auth_id` int(11) NOT NULL,
  `bk_id` int(11) 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` (`bk_id`)
) 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 (1012,11026),(1004,11028),(1001,11033),
(1002,11035),(1012,11041),(1014,11069),(1003,11072),(1011,11078);
/*!40000 ALTER TABLE `authorbook` ENABLE KEYS */;
UNLOCK TABLES;
......
可以看到，备份文件包含了一些信息：
备份文件接下来的部分是一些 SET 语句，这些语句将一些系统变量值赋给用户定义变量，以确保被恢复的数据库的系统变量和原来备份时的变量相同，例如： 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
该 SET 语句将当前系统变量 character_set_client 的值赋给用户定义变量 @old_character_set_client。其他变量与此类似。

备份文件的最后几行 MySQL 使用 SET 语句恢复服务器系统变量原来的值，例如： 
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
该语句将用户定义的变量 @old_character_set_client 中保存的值赋给实际的系统变量 character_set_client。

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

另外，备份文件开始的一些语句以数字开头，代表的是 MySQL 版本号，这些语句只有在指定的MySQL版本或者比该版本高的情况下才能执行。例如，40101，表明这些语句只有在 MySQL 版本号为 4.01.01 或者更高的条件下才可以被执行。

2) 使用mysqldump备份数据库中的某个表

在前面 mysqldump 的语法中介绍过，mysqldump 还可以备份数据中的某个表，其语法格式为： 
mysqldump  –u user –h host –p dbname  [tbname, [tbname...]] > filename.sql
tbname 表示数据库中的表名，多个表名之间用空格隔开。

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

【实例 2】备份 booksDB 数据库中的 books 表，输入语句如下： 
mysqldump -u root -p booksDB books > C:/backup/books_20220301.sql
该语句创建名称为 books_20220301.sql 的备份文件，文件中包含了前面介绍的SET语句等内容，不同的是，该文件只包含 books 表的 CREATE 和 INSERT 语句。

3) 使用mysqldump备份多个数据库

如果要使用 mysqldump 备份多个数据库，就需要使用 --databases 参数。

备份多个数据库的语句格式如下： 
mysqldump  –u user –h host –p --databases  [dbname, [dbname...]] > filename.sql
使用 --databases 参数之后，必须指定至少一个数据库的名称，多个数据库名称之间用空格隔开。

【实例 3】使用 mysqldump 备份 booksDB 和 test_db 数据库，输入语句如下： 
mysqldump -u root -p --databases  booksDB test_db>C:\backup\books_testDB_20220301.sql
该语句创建名称为 books_testDB_20220301.sql 的备份文件，该文件中包含了创建两个数据库 booksDB 和 test_db 所必需的所有语句。

另外，使用 --all-databases 参数可以备份系统中所有的数据库，语句如下： 
mysqldump  –u user –h host –p --all-databases > filename.sql
使用参数 --all-databases 时，不需要指定数据库名称。

【实例 4】使用 mysqldump 备份服务器中的所有数据库，输入语句如下： 
mysqldump  -u root -p --all-databases > C:/backup/alldbinMySQL.sql
该语句创建名称为 alldbinMySQL.sql 的备份文件，文件中包含了对系统中所有数据库的备份信息。

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

直接复制整个数据库目录

因为 MySQL 表保存为文件方式，所以可以直接复制 MySQL 数据库的存储目录及文件进行备份。

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

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

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

使用mysqlhotcopy工具快速备份

mysqlhotcopy 是一个 Perl 脚本，最初由 Tim Bunce 编写并提供。它使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 来快速备份数据库。

mysqlhotcopy 是备份数据库或单个表最快的方法，但它只能运行在数据库目录所在的机器上，并且只能备份 MyISAM 类型的表。mysqlhotcopy 在 UNIX 系统中运行。

mysqlhotcopy 命令语法格式如下： 
mysqlhotcopy db_name_1, ... db_name_n /path/to/new_directory

【实例 5】使用 mysqlhotcopy 备份 test_db 数据库到 /usr/backup 目录下，输入语句如下： 
mysqlhotcopy -u root -p test_db /usr/backup
要想执行 mysqlhotcopy，需要访问备份的表文件，并设置表的 SELECT 权限、RELOAD 权限（以便能够执行 FLUSH TABLES）和 LOCK TABLES 权限。

提示，mysqlhotcopy 只能将表所在的目录复制到另一个位置，且只能用于备份 MyISAM 和 ARCHIVE 表。备份 InnoDB 类型的数据表时会出现错误信息。由于它复制本地格式的文件，因此也不能移植到其他硬件或操作系统下。

相关文章