MySQL数据备份-6ren

MySQL数据备份

转载作者：撒哈拉更新时间：2024-10-12 10:24:49

MySQL数据备份是数据库管理员非常重要的工作之一。系统意外崩溃或者硬件的损坏都可能导致数据的丢失，因此MySQL管理员应该定期地备份数据，使得在意外情况发生时最大限度地减少损失。本节将介绍数据备份的3种方法.

11.1.1 使用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备份单个数据库中的所有表

【例11.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 9.0', 2024),

(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_20240301.sql

Enter password: **

。

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

输入密码之后，MySQL便对数据库进行备份。使用文本查看器打开C:\backup文件夹下刚才备份过的文件，部分文件内容大致如下:

-- MySQL dump 10.13  Distrib 9.0.1, for Win64 (x86_64)

--

-- Host: localhost    Database: booksdb

-- ------------------------------------------------------

-- Server version    9.0.1

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

--

-- 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,'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');

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

  `bk_id` int NOT NULL,

  `bk_title` varchar(50) NOT NULL,

  `copyright` year NOT NULL,

  PRIMARY KEY (`bk_id`)

) 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 (11026,'Guide to MySQL 9.0',2024),(11028,'Learning C++',2005),(11033,'Study Html',2011),(11035,'How to use php',2003),(11041,'Inside VC++',2011),(11069,'MySQL professional',2009),(11072,'Teach yourself javascript',2005),(11078,'Learning MySQL',2010);

/*!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 2024-07-25 18:39:42

。

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

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

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

。

该SET语句将当前系统变量character_set_client的值赋给用户自定义变量@old_character_ set_client。其他变量与此类似.

备份文件的最后几行是使用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还可以备份数据库中的某张表.

备份某张表和备份数据库中所有表的语句的不同之处在于，要在数据库名称dbname之后指定需要备份的表名称.

【例11.2】备份booksDB数据库中的表books，SQL语句如下:

mysqldump -u root -p booksDB books > C:/backup/books_20240301.sql

。

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

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

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

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

。

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

【例11.3】使用mysqldump备份数据库booksDB和test_db，SQL语句如下:

mysqldump -u root -p --databases  booksDB test_db>C:\backup\books_testDB_20240301.sql

。

该语句创建名称为“books_testDB_20240301.sql”的备份文件，该文件中包含了创建两个数据库booksDB和test_db所必需的所有语句.

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

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

。

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

【例11.4】使用mysqldump备份服务器中的所有数据库，输入语句如下:

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

。

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

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

11.1.2 直接复制整个数据库目录

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

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

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

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

11.1.3 使用MySQLhotcopy工具快速备份

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

MySQLhotcopy命令的语法格式如下:

mysqlhotcopy db_name_1, ... db_name_n  /path/to/new_directory

。

db_name_1,…,db_name_n分别为需要备份的数据库的名称；“/path/to/new_directory”指定备份文件目录.

【例11.5】使用MySQLhotcopy备份数据库test_db到“/usr/backup”目录下，SQL语句如下:

mysqlhotcopy  -u root –p test_db /usr/backup

。

要想执行MySQLhotcopy，必须可以访问备份的表文件，具有那些表的SELECT权限、RELOAD权限（以便能够执行FLUSH TABLES）和LOCK TABLES权限.

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

。

最后此篇关于MySQL数据备份的文章就讲到这里了,如果你想了解更多关于MySQL数据备份的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！。

文章推荐：在Java程序中监听mysql的binlog

文章推荐：深入解析SpringAI框架：在Java应用中实现智能化交互的关键

文章推荐： .NET程序获取当前IP经纬度，并通过经纬度实现天气查询功能

文章推荐： apisix~自定义文件上传代理插件~支持form-data文件和kv参数

mysql - 只有 Mysql OR mysql+sqlite OR mysql+自己的解决方案
目前我正在构建相当大的网络系统，我需要强大的 SQL 数据库解决方案。我选择 Mysql 而不是 Postgres，因为一些任务需要只读(MyISAM 引擎)而其他任务需要大量写入(InnoDB)。
mysql - Linux/mysql 将 mysql 表输出写入文件并保持 mysql 格式。
我在 mysql 中使用如下命令。当它显示表格数据时，它被格式化为一个非常干净的表格，间距均匀且 |作为列分隔符。 SELECT * FROM TABLE_NAME; 当我从 CLI 运行命令时，如下
mysql - 无法从终端加载 mysql 但可以使用系统首选项启动 mysql
我知道这个问题之前已经被问过好几次了，我已经解决了很多问题，但到目前为止没有任何效果。 MySQL 试图将自身安装到的目录 (usr/local/mysql) 肯定有问题。关于我的错误的奇怪之处在于我
mysql - 在 mysql 数据查询上获取不需要的输出 mysql
以下是我的 SQL 数据结构，我正在尝试如下两个查询: Select Wrk_ID, Wrk_LastName, Skill_Desc from Worker, Skill where
mysql - 将本地 mysql 服务器复制到基于云的 mysql
我们有一个本地 mysql 服务器(不在公共(public)域上)，并希望将该服务器复制到我们拥有的 google 云 sql 实例。我的问题是:1.这可能吗？2.我们的本地服务器只能在本地网络上访问
mysql - MySQL 触发器上 MySQL 变量的算术运算
我有一个表(test_table)，其中一些字段值(例如字段 A、B 和 C)是从外部应用程序插入的，还有一个字段(字段 D)，我想从现有表(store_table)插入其值，但在插入前者(A、B 和
mysql - 无法在创建 MySQL 服务器的同一计划中使用 mysql
我想创建一个 AWS RDS 实例，然后使用 terraform 管理数据库用户。因此，首先，我创建了一个 RDS 实例，然后使用创建的 RDS 实例初始化 mysql 提供程序，以进一步将其用于用户
mysql - MySql 用户数量对 MySql 性能影响大吗？
当用户在我的网站上注册时，他们会在我的一个数据库中创建自己的表格。该表存储用户发布的所有帖子。我还想做的是也为他们生成自己的 MySql 用户——该用户仅有权从他们的表中读取、写入和删除。创建它应该
mysql - mysql 表中的下和子类别(coldfusion，mysql)
我有一个关于 ColdFusion 和 Mysql 的问题。我有两个表:PRODUCT 和 PRODUCT_CAT。我想列出包含一些标记为:IS_EXTRANET=1 的特殊产品的类别。所以我写了这个
mysql - 如何使用 MYSQL 运算符选择列所有值都必须可用 - MYSQL
我想获取 recipes_id 列的值，以获取包含 ingredient_id 的 2,17 和 26 条目的值。假设 ingredient_id 2 丢失则不获取记录。我已经尝试过 IN 运算符
mysql - MySQL 服务器和 MySQL 客户端有什么区别
在 Ubuntu 中，我通常安装两者，但 MySQL 的客户端和服务器之间有什么区别。作为奖励，当一个新语句提到它需要 MySQL 5.x 时，它是指客户端、服务器还是两者兼而有之。例如这个链接ht
mysql - mysql - 如何在没有触发器和手动插入的情况下在插入时生成/自动增加 guid mysql？
我重新访问了我的数据库并注意到我有一些 INT 类型的主键。这还不够独特，所以我想我会有一个指导。我来自微软 sql 背景，在 ssms 中你可以选择类型为“uniqeidentifier”并自
mysql - Oracle MySQL 与 MySQL 相同吗？
我的系统上有 MySQL，我正在尝试确定它是 Oracle MySQL 还是 MySQL。 Oracle MySQL 有区别吗: http://www.oracle.com/us/products/m
mysql - 本地 mysql 服务器和生产 mysql 服务器之间的显着性能差异
我是在生产 MySQL 中运行的应用程序的新维护者。之前的维护者已经离开，留下的文档很少，而且联系不上了。我面临的问题是执行以下请求大约需要 10 秒: SELECT COUNT(*) FROM `
mysql - 如何自动将数据从一个 MySQL 数据库传输到另一个 MySQL 数据库？
我有两个位于不同机器上的 MySQL 数据库。我想自动将数据从一台服务器传输到另一台服务器。比方说，我希望每天早上 4:00 进行数据传输。可以吗？是否有任何 MySQL 内置功能可以让我们做到这一
mysql - 从 mysql 目录外的 mysql 表查询？
有什么方法可以使用 jdbc 查询位于 mysql 根目录之外的目录中的 mysql 表，还是必须将它们移动到 mysql 根目录内的数据库文件夹中？我在 Google 上搜索时没有找到任何东西。最
mysql - 使用另一个 mysql 表的值更新 Mysql 表
我在 mysql 数据库中有两个表。成员和 ClassNumbers。两个表都有一个付费年份字段，都有一个代码字段。我想用代码数字表中的值更新成员表中的付费年份，其中成员中的代码与 ClassNumb
mysql - 是否可以将本地 MySQL 数据库复制到远程 MySQL 数据库？
情况:我有 2 台服务器，其中一台当前托管一个实时 WordPress 站点，我希望能够将该站点转移到另一台服务器，以防第一台服务器出现故障。传输源文件很容易；传输数据库是我需要弄清楚如何做的。两台服
mysql - 使用 mysql 查询复制 mysql 数据库
Phpmyadmin 有一个功能是“复制数据库到”..有没有mysql查询来写这个函数？类似于将 db A 复制到新的 db B。最佳答案首先创建复制数据库: CREATE DATABASE du
mysql - 当 mySQL 已安装并由另一个应用程序配置时，为新应用程序配置 mySQL
我有一个使用 mySQL 作为后端的库存软件。我已经在我的计算机上对其进行了测试，并且运行良好。当我在计算机上安装我的软件时，我必须执行以下步骤: 安装 mySQL 服务器将用户名指定为“root

撒哈拉

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城