mysql全量备份与数据迁移

日常数据备份主要分为：全量备份、增量备份、混合备份。

本文主要介绍全量备份
一、mysqldump
mysql自身提供了一个专用于数据库备份客户端工具：mysqldump。
mysqldump是一个客户端程序，用于生成一组可执行并重现原始数据库对象和表数据；注意：使用该工具链接服务器的用户必须要具有select的权限；

主要参数：

服务器连接配置：

	--host
	--user
	--password
	--port
1
2
3
4

数据库选项配置

mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases
1
2
3

对备份数据库的指定主要分为三种：
db_name [tbl_name 指定库的表。可以指定多个表，以空格分隔。
–databases db_name 指定某个库
–all-databases 对服务器下所有库进行备份

操作用法：

mysqldump [arguments] > file_name
1

 mysqldump --databases MyDB --host=127.0.0.1 --port=3306 --user=root --password=123456  >/app/mysql/dump/20230227.sql
1

执行此操作后将在/app/mysql/dump/ 目录下生成20230227.sql文件；
文件中 默认 包含：
MyDB库创建语句
MyDB库使用语句
各表删除语句
各表创建语句
各表数据INSERT语句

在做数据转储的时候；应当避免其他事务对数据的影响，所以在备份时还应该添加 --single-transaction 参数，此备份操作在转储开始时获取所有表的全局读锁定。 若服务器还开启了二进制日志则需要添加一个–master-data 参数； 此参数会将当前二进制日志最后偏移地址及二进制文件写入到全量备份文件中。

 mysqldump --databases MyDB --host=127.0.0.1 --port=3306 --user=root --password=123456  --master-data --single-transaction > /app/mysql/dump/20230227.sql
1

20230227.sql

-- MySQL dump 10.13  Distrib 5.7.40, for linux-glibc2.12 (x86_64)
--
-- Host: 127.0.0.1    Database: MyDB
-- ------------------------------------------------------
-- Server version       5.7.40-log

/*!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 */;
/*!40101 SET NAMES utf8 */;
/*!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='binlog_mysql.000001', MASTER_LOG_POS=394;   
--binlog_mysql.000001 当前二进制日志文件
--MASTER_LOG_POS=394 当前二进制日志文件语句偏移量

--
-- Current Database: `MyDB`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `MyDB` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `MyDB`;

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

二、备份文件导入
由于我们这里备份文件中包含数据库的创建与使用语句；所以在执行该文件时可以直接使用
$> mysql < dump.sql
或者
mysql> source dump.sql
两种方式直接导入。在数据量不大的情况下甚至可以直接复制出备份文件中的sql脚本到第三方客户端直接执行。

注：该备份操作还可以对数据库的 存储过程、触发器 等做备份。并且对是否在备份文件中添加 创建/删除表语句、 创建/删除库语句等做单独配置。