本文已参与「新人创作礼」活动，一起开启掘金创作之路。

备份

使用 mysqldump 备份

格式

# mysqldump [options] 
# --user <username> or -u <username>
# --password <password> or -p<password>

示例

# 整库备份
mysqldump -u root -pmysql2022 --all-databases > alldump.sql
# 时间点恢复
mysqldump -u root -p123456 --databases test --single-transaction --master-data > master-dump.sql
# 备份指定表
mysqldump -u root -p123456 --databases test --tables table_1 > table_1-dump.sql
# 忽略表
mysqldump -u root -p123456 --databases test --ignore-table=test.table_1 > ignore-table-dump.sql
# 指定行(假设备份test数据库中 table_1表 id大于2的 10条 数据)
mysqldump -u root -p123456 --databases test --tables table_1 --where="id > 2 limit 10" > where-dump.sql
# 远程备份: [] 包裹的是可选的，作用是使用ssl，如果没有启用可以不加
mysqldump --all-databases --routines --events --triggers -h <IP地址> -P 3306 -u <用户名> -p<密码> [--ssl-cert=client-cert.pem --ssl-key=client-key.pem] > host-dump.sql
# 备份不包含数据的sql
mysqldump -u root -p123456 --databases test --tables table_1 --no-data > no-data-dump.sql
# 仅备份insert：--complete-insert 表示，在 insert sql中，加入要插入的列名
mysqldump -u root -p123456 --databases test --tables table_1 --no-create-db --no-create-info --complete-insert > insert-dump.sql
# 新数据替换
# 假设需要将数据从生产数据恢复到以有一些数据的开发服务器；将生产数据合并到开发过程中，可以使用 --reokact 选项
# 该选项将使用 replace info 语句而不是 insert 语句；
mysqldump -u root -p123456 --databases test --tables table_1 --skip-add-drop-table --no-create-info --replace > replace-dump.sql
# 二进制备份（备份始终在从服务器上进行;要获取备份时主服务器的二进制日志位置，可以使用--dump-slave; 如果从主服务器上进行二进制日志备份，需要使用--master-data）
mysqldump --all-databases -h 27.50.161.163 -P 3306 -u admin -p123456 --ssl-cert=client-cert.pem --ssl-key=client-key.pem --single-transaction --master-data > master.sql

--all-databases：备份整库，如果需要备份指定数据库，则使用 --databases test

此命令默认不会备份存储过程和事件，如果需要备份则需要加入以下参数；在 --all-databases 后面加入

--routines：备份存储过程

--events：备份事件

--single-transaction：此参数将事务隔离模式更改为 REPEATABLE READ模式，并执行 START TRANSACTION来提供一致的备份；适用于 InnoDB 之类的事务表，因为它在START TRANSACTION执行时可以保存数据库的一致状态而不阻塞任何应用程序

--master-data：选择将服务器的二进制日志的位置输出到 master-dump.sql 文件，如果 --master-data=2，他将打印注释，他也使用 FLUSH TABLES WITH READ LOCK语句来获取二进制日志的快照

使用 mysqlpump 备份

# 并行处理 --default-parallelism：表示指定4个线程同时处理
mysqlpump -u root -p123456 --default-parallelism=4 > parallelism-pump.sql
# 给某一个库指定线程 --parallel-schemas=2:test：表示给test数据库设置两个线程处理，其他默认4个线程处理；多个数据库,分割；可以设置多个--parallel-schemas
mysqlpump -u root -p123456 --default-parallelism=4 --parallel-schemas=2:test > parallel-schemes-pump.sql
# 使用正则表达式 --include-databases：表示 备份以tes开头的数据库; 匹配符号包含：%任意多个字符；_任意一个字符
mysqlpump -u root -p123456 --include-databases=tes% > include-pump.sql
# 排除表
mysqlpump -u root -p123456 --include-databases=tes% --exclude-tables=table_1 > exclude-tables-pump.sql
# 备份用户
mysqlpump -u root -p123456 --exclude-databases=% --users > user.pump.sql

# 压缩备份-lz4
mysqlpump -u root -p123456 --compress-output=lz4 > lz4.pump.lz4
# 翻译压缩备份-lz4
lz4_decompress lz4.pump.lz4 lz4.pump.sql

# 压缩备份-zlib
mysqlpump -u root -p123456 --compress-output=zlib > zlib.pump.zlib
# 翻译压缩备份-zlib
zlib_decompress zlib.pump.zlib zlib.pump.sql

使用 mydumper 备份

mydumper 是一个类似于mysqlpump的逻辑备份工具

与mysqldump相比如下

并行（速度更快）和性能（避免使用复杂的字符集转换例程，因而代码总体上很高效）
一致性。mydumper维护所有线程快照，提供准确的主库和从库日志位置等。mysqlpump不保证一致性
更易于管理输出（将表个元数据文件分离，并且方便查看/解析数据）。mysqlpump将所有内容写入一个文件，这限制了加载部分数据库对象的选项
使用正则表达式包含和排除数据库对象
有用于终止阻塞备份和所有后续查询的长事务的选项

看起来很美好对吗，但是他的最大缺点就是没有在mysql中集成，需要单独安装

使用 binlog 备份

mysqlbinlog -h 27.50.161.163 -P 3306 -u admin -p123456 --ssl-cert=client-cert.pem --ssl-key=client-key.pem --read-from-remote-server --stop-never --to-last-log --raw server1.000001 --result-file=F:\linux\ &

--read-from-remote-server：读取远程日志

--to-last-log：从 server1.000001 之后的所有二进制日志

--stop-never：保持连接，不断的拉取最新的日志

恢复数据

使用 mysqldump、mysqlpump恢复

暂时关掉binlog（session级别，断开连接即失效）

set SQL_LOG_BIN=0; 
source dump.sql;

使用二进制恢复

master

在上面使用了 mysqldump --master-data 进行备份后，需要进行如下操作，获取到二进制文件以及起始位置

head -30 master.sql

此命令最终打印结果如下

-- MySQL dump 10.13  Distrib 8.0.15, for Win64 (x86_64)
--
-- Host: 27.50.161.163    Database: test
-- ------------------------------------------------------
-- Server version       8.0.27

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

--
-- Position to start replication or point-in-time recovery from
--

CHANGE MASTER TO MASTER_LOG_FILE='server1.000006', MASTER_LOG_POS=7830;

--
-- Current Database: `test`
--

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

USE `test`;

我们可以看到第21行：CHANGE MASTER TO MASTER_LOG_FILE='server1.000006', MASTER_LOG_POS=7830;

这行sql表示，本次备份的master的日志文件是从server1.000006的7830坐标开始备份，这两个值需要记住，待会要用

开始使用mysqlbinlog进行恢复

mysqlbinlog --start-position=7830 --disable-log-bin /data/binlogs/server1.000006 | mysql -u root -p123456 -f

--start-position：此参数就是上面看到的 MASTER_LOG_POS

--disable-log-bin：MASTER_LOG_FILE

-f：表示即使断开连接，mysql也会继续执行恢复操作

slave

在上面使用了 mysqldump --dump-slave 进行备份后，需要进行如下操作，获取到二进制文件以及起始位置

和master 同理

MySQL8.0-备份与恢复

备份