MySQL之mysqldump的使用详解和记一次备库的恢复

说明: 以下所有操作针对与mysql8.0

一、mysqldump 简介

mysqldump 是 MySQL 自带的逻辑备份工具。

它的备份原理是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的DML语句，当我们需要还原这些数据时，只要执行这些 DML语句，即可将对应的数据还原。（说白点就跟我们平时手动执行sql是一样的，先创建执行语句创建数据库,如何创建表，如果表存在则删除再创建，然后在执行插入，依次循环所有数据库）
如 使用mysqldump导出数据后的sql语句如下：

-- MySQL dump 10.13  Distrib 8.0.26, for Linux (x86_64)
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- 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' */;
/*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;
/*!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 */;


--
-- Current Database: `nacos_config`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `nacos_config` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `joolun_config`;

--
-- Table structure for table `config_info`
--

DROP TABLE IF EXISTS `config_info`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `config_info` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
  `data_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'data_id',
  `group_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `tenant_id` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' COMMENT '租户字段',
  `c_schema` text CHARACTER SET utf8 COLLATE utf8_bin,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_configinfo_datagrouptenant` (`data_id`,`group_id`,`tenant_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=807 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC COMMENT='config_info';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `config_info`
--
LOCK TABLES `config_info` WRITE;
/*!40000 ALTER TABLE `config_info` DISABLE KEYS */;
INSERT INTO `config_info` VALUES (1,'dynamic_routes','DEFAULT_GROUP','routes');
/*!40000 ALTER TABLE `config_info` ENABLE KEYS */;
UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=@OLD_INNODB_STATS_AUTO_RECALC */;

/*!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-04-29 22:47:05

说明：在mysql中 /! …/ 不是注释，是mysql为了保持兼容，它把一些特有的仅在mysql上用的语句放在/!../中，这样这些语句如果在其他数据库中是不会被执行，但在mysql中它会执行，/*!40101这种40101是标识的自定义标识的一个序号（个人猜想）。

二、备份命令

2.1 命令格式

mysqldump [选项] 数据库名 [表名] > 脚本名
或
mysqldump [选项] --数据库名 [选项 表名] > 脚本名
或
mysqldump [选项] --all-databases [选项] > 脚本名

Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

2.2 选项说明

具体请查看 mysqldump 参数解析（详细）
参数名 缩写 含义

--host	-h	服务器IP地址
--port	-P	服务器端口号
--user	-u	MySQL 用户名
--pasword	-p	MySQL 密码
--databases		-B 指定要备份的数据库
--all-databases	-A	备份mysql服务器上的所有数据库
--compact		压缩模式，产生更少的输出
--comments		添加注释信息
--complete-insert		输出完成的插入语句
--lock-tables		备份前，锁定所有数据库表
--no-create-db/--no-create-info		禁止生成创建数据库语句
--force		当出现错误时仍然继续备份操作
--default-character-set		指定默认字符集
--add-locks		备份数据库表时锁定数据库表

2.3 实例

备份所有数据库：

mysqldump -uroot -p --all-databases > /backup/mysqldump/all.db

备份指定数据库：

mysqldump -uroot -p test > /backup/mysqldump/test.db

备份指定数据库指定表(多个表以空格间隔)

mysqldump -uroot -p mysql db event > /backup/mysqldump/2table.db

备份指定数据库排除某些表

mysqldump -uroot -p test --ignore-table=test.t1 --ignore-table=test.t2 > /backup/mysqldump/test2.db

三、还原命令

3.1 系统行命令

mysqladmin -uroot -p create db_name 
mysql -uroot -p db_name < /backup/mysqldump/db_name.sql
mysql -uroot -p  < /backup/mysqldump/all.sql

注：在导入备份数据库前，db_name如果没有，是需要创建的； 而且与db_name.db中数据库名是一样的才可以导入,db_name为空时导入所有数据。

3.2 soure 方法

mysql > use db_name
mysql > source /backup/mysqldump/db_name.db

实例

注:用户名:root 密码:DbPasswd 生成的sql脚本问 db.sql
导出数据库为DBName的表结构(不导数据)

mysqldump -uroot -pDbPasswd -d DBName > db.sql

导出数据库为DBName的表结构及其所有数据(不加-d即可)

mysqldump -uroot -pDbPasswd DBName > db.sql;

导出数据库为DBName的表(test)的结构

mysqldump -uroot -pDbPasswd -d DBName test > db.sql

导出数据库为DBName的表(test)的结构及其所有数据(不加-d即可)

mysqldump -uroot -pDbPasswd DBName test > db.sql 

建从库导出数据
mysqldump -uroot -proot@123.0 -A -B -F --events -x --master-data=1 --set-gtid-purged=OFF | gzip > /opt/all.sql.gz
导入数据
[root@db-03 ~]# gzip -d all.sql.gz
[root@db-03 ~]# mysql -uroot -pfan@123.0 < all.sql

一般不建议导出所有数据库 应为导出所有数据库 会将 系统表也导出 建议导出指定的一个或几个数据库