ITPub博客

首页 > 数据库 > MySQL > MySQL备份与恢复——基于mysqldump 逻辑备份技术文档

MySQL备份与恢复——基于mysqldump 逻辑备份技术文档

原创 MySQL 作者:龙山游龙 时间:2021-08-03 15:20:41 0 删除 编辑

一、说明

mysqldump是mysql软件内自带的逻辑备份工具,把数据从MySQL库中以逻辑的SQL语句的形式直接输出或生成备份的文件的过程。该工具只支持单线程工作。支持远程导出。

1.1 Mysqldump导出内部流程

Eg:mysqldump –h127.0.0.1 -uroot –pmysql –R –single-transaction –master-data=2 --q –e –all-databases


二、参数列表

Format

Introduced

默认配置

--add-drop-database

文件中在创建数据库语句前添加删库语句

FALSE

--add-drop-table

文件中在创建表语句前添加删表语句

TRUE

--add-drop-trigger

文件中在创建触发器语句前添加删触发器语句

FALSE

--add-locks

数据前后添加锁表解锁表

TRUE

--all-databases

导出所有数据库

FALSE

--allow-keywords

允许创建是关键词的列名字

FALSE

--apply-slave-statements

在'CHANGE MASTER'前添加'STOP SLAVE',并且在导出的最后添加'START SLAVE'。

FALSE

--bind-address

使用指定的网络接口连接到MySQL服务器

(No default value)

--character-sets-dir

安装字符集的目录

(No default value)

--comments

添加注释

FALSE

--compact

导出更少的输出信息(用于调试)。去掉注释和头尾等结构

FALSE

--compatible

导出的数据将和其它数据库或旧版本的MySQL 相兼容

(No default value)

--complete-insert

使用完整的insert语句(包含列名称)。这么做能提高插入效率

FALSE

--compress

在客户端和服务器之间启用压缩传递所有信息

FALSE

--create-options

在CREATE TABLE语句中包括所有MySQL特性选项

FALSE

--databases

导出几个数据库

FALSE

--debug

输出debug信息,用于调试


--debug-check

输出调试信息并退出

FALSE

--debug-info

检查内存和打开文件使用说明并退出

FALSE

--default-auth

客户端插件默认使用权限。


--default-character-set

默认字符集

utf8

--defaults-extra-file

读取额外参数文件


--defaults-file

读取参数文件


--defaults-group-suffix



--delayed-insert

延时插入方式(INSERT DELAYED)导出数据

FALSE

--delete-master-logs

master备份后删除日志

FALSE

--disable-keys

插入所有行之后创建索引

TRUE

--dump-date

添加导出时间

TRUE

--dump-slave

添加主库的binlog位置

0

--enable-cleartext-plugin



--events

导出时间

FALSE

--extended-insert

使用使用具有多个VALUES列的INSERT语法

TRUE

--fields-enclosed-by

在每一个字段的前后加上char这个字符

(No default value)

--fields-escaped-by

使用char去转义特殊字符

(No default value)

--fields-optionally-enclosed-by

在每一个非数字的字段前后加上char这个字符

(No default value)

--fields-terminated-by

列与列之间分隔

(No default value)

--flush-logs

开始导出之前刷新日志。

FALSE

--flush-privileges

刷新权限

FALSE

--force

在导出过程中忽略出现的SQL错误

FALSE

--help

帮助信息


--hex-blob

使用十六进制格式导出二进制字符串字段

FALSE

--host

主机地址

(No default value)

--ignore-table

不导出指定表


--include-master-host-port

--dump-slave同时使用添加主机信息

FALSE

--insert-ignore

在插入行时使用INSERT IGNORE语句.

FALSE

--lines-terminated-by

换行

(No default value)

--lock-all-tables

锁住所有表,自动关闭--single-transaction 和--lock-tables 选项

FALSE

--lock-tables

锁住表

TRUE

--log-error

附加警告和错误信息到给定文件

(No default value)

--login-path



--master-data

主机端binlog点

0

--max_allowed_packet

服务器发送和接受的最大包长度。

25165824

--net_buffer_length

TCP/IP和socket连接的缓存大小。

1046528

--no-autocommit

使用autocommit/commit 语句包裹表

FALSE

--no-create-db

不添加create database语句

FALSE

--no-create-info

只导出数据

FALSE

--no-data

只导出格式

FALSE

--no-defaults

不阅读参数文件


--no-set-names



--no-tablespaces

不写相关语句


--opt

等于--add-drop-table,  --add-locks, --create-options, --quick, --extended-insert, --lock-tables,  --set-charset, --disable-keys


--order-by-primary

根据主键排序

FALSE

--password

密码


--pipe

使用命名管道连接mysql


--plugin-dir

客户端插件的目录,用于兼容不同的插件版本

(No default value)

--port

端口

0

--print-defaults

输出默认参数


--protocol



--quick

不缓冲查询,直接导出到标准输出

TRUE

--quote-names

使用单引号包围表和列名

TRUE

--replace

使用REPLACE INTO 取代INSERT INTO.

FALSE

--result-file

直接输出到指定文件

FALSE

--routines

导出存储过程及自定义函数

FALSE

--secure-auth


TRUE

--set-charset

添加SET NAMES  default_character_set'

TRUE

--set-gtid-purged



--shared-memory-base-name



--single-transaction

事务

FALSE

--skip-add-drop-table

不添加drop表操作


--skip-add-locks

不加锁


--skip-comments

不添加注释


--skip-compact

不减少注释


--skip-disable-keys

不允许关键字作为列名


--skip-extended-insert

关闭extended-insert


--skip-opt

禁用-opt


--skip-quick

禁用-quick


--skip-quote-names



--skip-set-charset



--skip-triggers

不导出触发器


--skip-tz-utc



--socket

套接字

/tmp/mysql.sock

--ssl


FALSE

--ssl-ca


(No default value)

--ssl-capath


(No default value)

--ssl-cert


(No default value)

--ssl-cipher


(No default value)

--ssl-crl


(No default value)

--ssl-crlpath


(No default value)

--ssl-key


(No default value)

--ssl-mode


(No default value)

--ssl-verify-server-cert


FALSE

--tab

创建tab分割文件

(No default value)

--tables


--triggers

导出触发器

TRUE

--tz-utc

导出顶部设置时区TIME_ZONE='+00:00' 

TRUE

--user

用户

root

--verbose

输出多种平台信息

FALSE

--version

版本


--where

筛选条件

(No default value)

--xml

导出xml格式


参考文档:https: // blog.csdn.net/stephenxu111/article/details/9310715

三、导出示例

导出格式
文件内容
SQL Format
导出文件是一组SQL语句集,文件内包含创建转储对象(数据库、表、存储例程等)的CREATE语句,以及将数据加载到表中的INSERT语句。
Delimited-Text Format
导出文件可以以特定分隔符分隔的一个或者多个文本文件(TXT,CSV等),可以直接对数据进行查找、替换和修改等操作。

涉及参数 secure-file-priv

Command-Line Format
--secure-file-priv=dir_name


System Variable

Name

secure_file_priv


Variable Scope

Global


Dynamic Variable

No

Permitted Values (<= 5.6.33)

Type

string


Default

empty


Valid Values

empty



dirname

Permitted Values (>= 5.6.34)

Type

string


Default

platform specific


Valid Values

empty



dirname



NULL

3.1 SQL Format

常用参数
含义
-A
全库导出的只有表、视图、触发器   
默认不导出存储函数、存储过程、事件
-E
事件
-R
存储过程及函数等定义好的MySQL程序
-d
只导出结构
-t
只导出数据
-f
忽略错误
-F
--flush-logs 导出之前刷新日志文件
--dump-slave
备库端使用,获取备份数据的Binlog位置和Binlog文件名
--master-data=(2|1)
获取备份数据的Binlog位置和Binlog文件名
--single-transaction
锁定但是可以进行写入操作,无法ddl操作,保证innodb引擎表一致性

3.1.1 全库导出

mysqldump -uroot -pmysql --single-transaction --master-data=2  -E -R --all-databases > full.sql

3.1.2 单库/多库导出

mysqldump -uroot -pmysql --single-transaction --master-data=2  -E -R --databases test [employees ceshi] > test.sql

3.1.3 单表/多表导出

mysqldump -uroot -pmysql --single-transaction --master-data=2  -E -R  test a [b c] > double.sql

注:不指定databases参数,不包含建库语句

3.1.4 按条件导出

mysqldump -uroot -pmysql --single-transaction --master-data=2 test test --where="id<6  and name='a'" > test.sql

3.1.5 只导出结构 

mysqldump -uroot -pmysql --no-data --routines --events --databases test [ceshi]> dump-defs.sql

注:可用于测试升级

3.1.6 只导出数据

mysqldump -uroot -pmysql --no-create-info --routines --events --databases test [ceshi]> dump-data.sql

3.1.7 远程导出并导入

mysqldump --host=h1 -uroot -proot  remote_db1 tablename | mysql --host=h2 -uroot -proot db2

注:db2数据库必须存在

3.2 Delimited-Text Format


常用参数
含义
0
--tab
导出位置
1
--fields-terminated-by=str  
列与列之间分隔
2
--fields-enclosed-by=char   
在每一个字段的前后加上char这个字符     
3
--fields-optionally-enclosed-by=char  
在每一个非数字的字段前后加上char这个字符
4
--fields-escaped-by=char    
使用char去转义特殊字符
5
--lines-terminated-by=str    
行与行之间分隔   #linux \n   windows \r\n


注:2与3不能同时使用

注:必须指定secure-file-priv 安全目录参数!!!否则报错无法导出

3.2.1 单库导出

mysqldump --tab=/tmp/12 --fields-terminated-by=',' --fields-optionally-enclosed-by='"' --lines-terminated-by='\n' DBNAME

3.2.2 单表/多表导出

mysqldump --tab=/tmp/12 --fields-terminated-by=',' --fields-optionally-enclosed-by='"' --lines-terminated-by='\n' DBNAME TABNAME1 [TABNAME2]

注:每张表格生成两个文件

*.sql   属于执行命令用户(root)   包含建表语句

*.txt   属于数据库用户(mysql)    包含数据

四、案例

4.1 案例一: 测试 --all-databases参数

mysqldump -uroot -pmysql --single-transaction --master-data=2  --all-databases > full_1.sql


mysqldump -uroot -pmysql --single-transaction --master-data=2  -E -R --all-databases > full.sql


注:--all-database 参数是不包含存储过程,事件等,需要格外指定参数导出。

4.2 案例二: mysqldump 期间是否可以DDL操作

测试环境:

测试语句:

mysqldump -uroot -pmysql --single-transaction --master-data=2  -E -R --all-databases > full.sql

测试目的:针对INNODB表,开启事务性备份是否会堵塞DDL操作

测试方法:根据general 通用日志,判断表是否已经完成备份。对已备份完毕,正在完成备份,未开始备份表分别做DDL操作,判断是否会堵塞DDL操作

情况一:对已完成备份表做DDL操作

1.开启全库备份

mysqldump -uroot -pmysql --single-transaction --master-data=2  -E -R --all-databases > full.sql

2.观察通用日志,发现已经employees.departments表已经完成备份

3.进行DDL操作,正常进行

4.备份正常完成

情况二:对正在备份表做DDL操作

1.开启全库备份

mysqldump -uroot -pmysql --single-transaction --master-data=2  -E -R --all-databases > full.sql

2.观察通用日志,发现正在对xh.test表进行备份

3.进行DDL操作,并堵塞

4.查看进程

5.备份正常完成

情况三:对未开始备份表做DDL操作

1.开启全库备份

mysqldump -uroot -pmysql --single-transaction --master-data=2  -E -R --all-databases > full.sql

2.观察通用日志,现在仍在备份employees数据库,还未备份到xh库

3.进行DDL操作,正常进行

4.导出报错

结论:使用 --single-transaction 参数导出数据,DDL操作只会影响正在备份的表,已经完成备份或还未备份的表不会被堵塞。但是对未完成备份的表做完DDL操作,当备份到这张被DDL之后的表,mysqldump进程被中断并报错。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30310891/viewspace-2784988/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
拥有超过9年以上的MySQL/Oracle数据库领域从业经验,TB级高并发数据库的管理经验。对于MySQL/Oracle内部原理、体系结构,数据页/块结构原理有着深刻认识;对于MySQL/ORACLE新特性、分布式高可用架构和性能调优有着丰富的的实战经验。擅长故障诊断以及数据库异常打开及数据灾难挽救。

注册时间:2015-06-28

  • 博文量
    79
  • 访问量
    115844