mysqldump 备份详解

前言

mysqldump 是 MySQL 官方提供的一款逻辑备份工具，它将生成一组可以导入数据库中以重现原始数据库中的数据和数据库对象的SQL语句。可用于备份恢复、表结构导出、备份上云。本篇文章介绍原理和用法。

常用模版

备份整个实例

包含函数、触发器等对象。

mysqldump -uroot -p --port=3306 --single-transaction --master-data=2 --triggers --routines --events --all-databases > ./bakup_`date +"%F_%H_%M_%S"`.sql

备份单个数据库

mysqldump -uroot -p --port=3306 --single-transaction --master-data=2 --triggers --routines --events --databases db_name > ./bakup_`date +"%F_%H_%M_%S"`.sql

导出单表结构与数据

恢复表时，如果目标库有同表名，会被 drop 掉，如果想避免风险需添加--skip-add-drop-table。

mysqldump -uroot -p --port=3306 --set-gtid-purged=OFF --single-transaction --databases db_name --tables table_name > ./bakup_`date +"%F_%H_%M_%S"`.sql

仅导出单表数据

仅有数据，没有表结构。

mysqldump -uroot -p --port=3306 --single-transaction --set-gtid-purged=OFF --no-create-info --databases db_name --tables table_name > ./bakup_`date +"%F_%H_%M_%S"`.sql

仅导出单表部分数据

使用--where可以过滤数据–add-locks=0不需要添加锁表语句，恢复不影响目标库。

mysqldump -uroot -p --add-locks=0 --no-create-info --single-transaction  --set-gtid-purged=OFF db_name table_name --where="id>900" >  ./bakup_`date +"%F_%H_%M_%S"`.sql

某库下表结构导出

有时研发有将表结构迁移到另外一个DB下的需求，使用该命令可以完成。使用-d可以避免结构导出有use db语句，使用--skip-add-drop-table避免目标端有同名表被删除。

(这部分有疑问:使用--help看到-d, --no-data No row information，-d和--no-data都是同样的作用)

mysqldump -uroot -p --port=3306 --set-gtid-purged=OFF --skip-add-drop-table=ON --no-data -d db_name > ./bakup_`date +"%F_%H_%M_%S"`.sql

备份上云

DEFINER 问题

上云的数据库有触发器、函数、视图这些对象，mysqldump 导出是会有 DEFINER 直接还原到 RDS 会报错下方错误：

ERROR 1227 (42000) : Access denied; you need (at least one of) the SUPER privilege(s) for this operation

DEFINER：对象定义者，在创建对象时可以手动指定用户，不指定的话默认为当前连接用户； SQL SECURITY：指明以谁的权限来执行该对象，有两个选项，一个为 DEFINER，一个为 INVOKER，默认情况下系统指定为 DEFINER；

--视图定义
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW v_test

--函数定义
CREATE DEFINER=`root`@`%` FUNCTION `f_test()` RETURNS varchar(100) SQL SECURITY DEFINER

--存储过程定义
CREATE DEFINER=`root`@`%` PROCEDURE `p_test`() SQL SECURITY DEFINER

--触发器定义
CREATE DEFINER=`root`@`%` trigger t_test 

--事件定义
CREATE DEFINER=`root`@`%` EVENT `e_test`

如果导入账号具有 SUPER 权限，即使对象的所有者账号不存在，也可以导入成功，但是在查询对象时，如果对象的 SQL SECURITY 为 DEFINER，则会报账号不存在的报错。

ERROR 1449 (HY000): The user specified as a definer (‘root’@’%’) does not exist

然后 RDS 不提供 ROOT 账号和 SUPER 权限，我们就需要修改备份文件中的 DEFINER 规避问题，所以我们使用 mysqldump 备份上云需要进行两次导入，第一次只备份数据，第二次只备份数据库中的其它对象，然后修改 DEFINER 再重新导入。

操作过程

准备 Python 脚本 drop_definer：

import sys

content = ''
for line in sys.stdin:
    content += line.replace('DEFINER=`root`@`localhost`', '')
    
print(content)

第一次只备份数据

mysqldump -h 127.0.0.1 -u root -p --opt --default-character-set=utf8 --hex-blob db_name --skip-triggers --skip-lock-tables > ./db_name.sql

第二次只备份触发器等

mysqldump -h 127.0.0.1 -u root -p --default-character-set=utf8 --skip-add-drop-table --skip-add-drop-database  --hex-blob --set-gtid-purged=OFF --databases db_name --no-data --no-create-info --no-create-db -R | python drop_definer.py > db_name_triggers.sql

云上还原：

create database db_name CHARSET utf8;
use db_name;
source /path/backup.sql

对象数验证

通过下方 SQL 可以查询实例中对象的数量，迁移前后都可使用 SQL 查询对比验证。

select db   AS '数据库',
       type AS '对象类型',
       cnt  AS '对象数量'
from (
         select 'TABLE'      type,
                table_schema db,
                COUNT(*)     cnt
         from information_schema.`TABLES` a
         where table_type = 'BASE TABLE'
         group by table_schema
         union all
         select 'EVENTS'     type,
                event_schema db,
                count(*)     cnt
         from information_schema.`EVENTS` b
         group by event_schema
         union all
         select 'TRIGGER'      type,
                trigger_schema db,
                count(*)       cnt
         from information_schema.`TRIGGERS` c
         group by trigger_schema
         union all
         select 'PROCEDURE' type,
                db,
                count(*)    cnt
         from mysql.proc d
         where `type` = 'PROCEDURE'
         group by db
         union all
         select 'FUNCTION' type,
                db,
                count(*)   cnt
         from mysql.proc e
         where `type` = 'FUNCTION'
         group by db
         union all
         select 'VIEW'   type,
                TABLE_SCHEMA,
                count(*) cnt
         from information_schema.VIEWS f
         group by table_schema
     ) t
where db not in (
                 'sys', 'mysql', 'INFORMATION_SCHEMA',
                 'performance_schema'
    )
order by db,
         type;

参数详解

Option Name	Description	unscramble
–add-drop-database	Add DROP DATABASE statement before each CREATE DATABASE statement	【重要】默认 FALSE CREATE DATABASE IF NOT EXISTS 如果该库不存在则创建，如果是覆盖数据库的场景可以加上该参数，在创建库前面会有 DROP DATABASE IF EXISTS 需要评估风险。虽然不会删库但是会自动加上 DROP TABLE IF EXISTS所以即使恢复目标实例存在同名库，也不影响同名表恢复。
–add-drop-table	Add DROP TABLE statement before each CREATE TABLE statement	【重要】默认会加上 DROP TABLE IF EXISTS 的，恢复目标端如果有同名表需要确认风险，可以加 -add-drop-table=FALSE如果有同名表，恢复时不会删除，直接抛出异常。
–add-drop-trigger	Add DROP TRIGGER statement before each CREATE TRIGGER statement	创建触发器语句前添加 Drop 语句。
–add-locks	Surround each table dump with LOCK TABLES and UNLOCK TABLES statements	在生成的备份文件中，在进行每个表备份时，默认会在 INSERT 语句之前添加 LOCK TABLES 语句，在 INSERT 语句之后添加 UNLOCK TABLES 语句。这样当重新加载备份文件时，有助于提高导入速度。
–all-databases	Dump all tables in all databases	备份所有的数据库。
–allow-keywords	Allow creation of column names that are keywords	允许创建具有 MySQL 关键字的列名。
–bind-address	Use specified network interface to connect to MySQL Server	在具有多个网络接口的计算机上，使用此选项选择连接到 MySQL 服务器的接口地址(使用 –host 指定域名时可能解析出多个IP地址，所以可能需要使用这个选项指定一个IP地址，但其实直接使用 –host指定IP地址即可，该选项不常用)。
–character-sets-dir	Directory where character sets are installed	指定字符集的安装目录，一般默认值即可。
–comments	Add comments to dump file	默认会附加一些注射信息。
–compact	Produce more compact output	生成紧凑的备份文件。启用此选项会同时启用 –skip-add-drop-table、– skip-add-locks、–skip-comments、–skip-disable-keys 和 –skip-set-charset 选项，即跳过 DROP TABLE、LOCK TABLE、备份开头和结尾的注释语句 (如程序版本号、服务器版本号等)、关闭索引、SET NAME 等语句。
–compatible	Produce output that is more compatible with other database systems or with older MySQL servers	生成与其他数据库系统或老版本 MySQL 服务器兼容的备份文件。name 的值可以是 ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、 maxdb、no_key_options、no_table_options 或 no_field_options。要使用多个值，请用逗号分隔。这些值与服务器系统参数 sql_mode 值的对应选项具有相同的含义。
–complete-insert	Use complete INSERT statements that include column names	使用包含列名称的完整的 INSERT 语句生成备份文件。
–compress	Compress all information sent between client and server	尽可能压缩客户端和服务器之间发送的所有信息。
–create-options	Include all MySQL-specific table options in CREATE TABLE statements	如果设置为 FALSE 那么表引擎这些字符集这些都不会记录，按照目标实例默认值设置，需要确认风险。默认为 TRUE。
–databases	Interpret all name arguments as database names	【重要】用来指定备份单库或者某几个库。
–default-auth	Authentication plugin to use	关于要使用的客户端验证插件的提示选项。
–default-character-set	Specify default character set	指定默认字符集。如果不指定，则默认使用 UTF-8。
–defaults-extra-file	Read named option file in addition to usual option files	在读取全局选项文件(默认的配置文件读取路径是/etc/my.cnf、/etc/mysql/my.cnf、/usr/local/mysql/etc/my.cnf，~/.my.cnf是用户配置文件) 之后、读取用户配置文件之前(在UNIX系统上)，读取此选项指定的配置文件。如果该文件不存在或者使用其他方式无法访问，则会发生错误。如果给定的是相对路径名而不是完整路径名，则将在当前工作目录下读取该文件。
–defaults-file	Read only named option file	仅读取该选项指定的配置文件。如果该文件不存在或者使用其他方式无法访问，则会发生错误。如果给定的是相对路径名而不是完整路径名，则将在当前工作目录下读取该文件。
–defaults-group-suffix	Option group suffix value	关于读取配置文件中选项组的参数。
–delete-master-logs	On a replication source server, delete the binary logs after performing the dump operation	看官方文档的意思是，备份完成后会清理 Binlog 目测不常用。
–disable-keys	For each table, surround INSERT statements with statements to disable and enable keys	在 INSERT 语句之前先关闭非唯一索引，在 INSERT 之后再打开非唯一索引，可以加快数据导入速度。
–dump-date	Include dump date as “Dump completed on” comment if –comments is given	备份的最后一行会记录备份时间。
–dump-slave	Include CHANGE MASTER statement that lists binary log coordinates of replica’s source	如果备份的是从库，会自动生成 CHANGE MASTER TO 语句。
–enable-cleartext-plugin	Enable cleartext authentication plugin	密码验证插件相关问题。
–events	Dump events from dumped databases	备份存储 EVENT 事件。
–extended-insert	Use multiple-row INSERT syntax	TRUE（默认） INSERT INTO Course VALUES (‘01’,‘语文’,‘02’),(‘02’,‘数学’,‘01’),(‘03’,‘英语’,‘03’); FALSE： INSERT INTO Course VALUES (‘01’,‘语文’,‘02’); INSERT INTO Course VALUES (‘02’,‘数学’,‘01’); INSERT INTO Course VALUES (‘03’,‘英语’,‘03’);
–flush-logs	Flush MySQL server log files before starting dump	备份前 FLUS LOGS 刷新下 BINLOG 更优雅。
–flush-privileges	Emit a FLUSH PRIVILEGES statement after dumping mysql database	直接使用 DML 语句修改权限表，并不会生效，必须触发 MySQL 服务重新加载权限表，这个命令就是 flush privilege如果备份中有权限表，建议加上该参数。
–force	Continue even if an SQL error occurs during a table dump	忽略所有错误，强制执行。
–get-server-public-key	Request RSA public key from server	RSA 密钥验证相关参数。
–hex-blob	Dump binary columns using hexadecimal notation	使用十六进制符号备份二进制列 (例如，‘abc’ 变为 0x616263)，受影响的数据类型有 BINARY、VARBINARY、BLOB 和 BIT。
–host	Host on which MySQL server is located	MySQL 主机地址。
–ignore-error	Ignore specified errors	忽略指定异常。
–ignore-table	Do not dump given table	【过滤参数】指定不备份的表，格式：db_name.tbl_name
–include-master-host-port	Include MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with –dump-slave	上面介绍使用 –dump-slave 选项进行从库备份时会生成 CHANGE MASTER TO 语句，而该选项会为 CHANGE MASTER TO 语句添加 MASTER_HOST 和 MASTER_PORT 选项，其值为与主库对应的 TCP/IP 端口号。
–insert-ignore	Write INSERT IGNORE rather than INSERT statements	在进行备份时，将 INSERT 语句替换为 INSERT IGNORE 语句。
–lines-terminated-by	This option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA	该选项与 –tab 选项一起使用，与 LOAD DATA INFILE 的相应 LINES 子句的作用相同。
–lock-all-tables	Lock all tables across all databases	锁定所有数据库中的所有表。这是通过在整个备份期间使用 FLUSH TABLES WITH READ LOCK 语句获取全局读锁实现的，而不是为每个表都添加一条 LOCK TABLES 语句。此选项在使用 –single-transaction 和 –lock-tables 时将自动关闭。
–lock-tables	Lock all tables before dumping them	默认会锁表备份，innodb 引擎可以使用 –single-transaction避免锁表。使用 –opt 选项会自动启用 –lock-tables，如果不需要启用该选项，则使用 –skip-lock-tables 选项。
–log-error	Append warnings and errors to named file	追加警告和异常信息。
–login-path	Read login path options from .mylogin.cnf	从文件中读取登陆路径。
–master-data	Write the binary log file name and position to the output	【重要】会在备份文件中生成一条包含 binlog 位点和文件信息的 CHANGE MASTER TO 语句。设置为 1 该语句不会注释掉，执行备份文件会生效，设置为 2 该语句会注释掉，是我们经常使用的选项。使用 –master-data 选项，将自动禁用 –lock-tables 选项，同时还会启用 –lock-all-tables 选项，除非指定了 –single-transaction 选项。在指定了 –single-transaction 选项之后，只有在备份过程中获取 binlog pos 时才会加全局读锁，一旦获取到 binlog pos 之后就立即释放全局读锁。
–max-allowed-packet	Maximum packet length to send to or receive from server	向服务器发送或从服务器接收的最大数据包长度，默认为 25MB 。
–net-buffer-length	Buffer size for TCP/IP and socket communication	指定客户端和服务器通信时的数据缓冲区的初始大小。一般不修改。
–no-autocommit	Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements	默认 FALSE，在 INSERT 前加上 SET autocommit = 0 后面加上 COMMIT。不常用。
–no-create-db	Do not write CREATE DATABASE statements	备份数据库时，不会添加创建数据库语句。
–no-create-info	Do not write CREATE TABLE statements that re-create each dumped table	不添加表结构信息
–no-data	Do not dump table contents	【重要】不备份数据，仅备份表结构。一些需要迁移表结构的场景可以使用。
–no-defaults	Read no option files	不使用默认的配置。
–no-set-names	Same as –skip-set-charset	忽略。
–no-tablespaces	Do not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output	此选项禁止 mysqldump 输出 CREATE LOGFILE GROUP 和 CREATE TABLESPACE 语句。
–opt	Shorthand for –add-drop-table –add-locks –create-options –disable-keys –extended-insert –lock-tables –quick –set-charset	该选项是 –add-drop-table、–add-locks、–create-options、–disable-keys、– extended-insert、–lock-tables、–quick、–set-charset 组合的缩写，提供快速的导出操作，并生成一个可以快速重新导入MySQL服务器的备份文件。默认启用该选项，如果要禁用 –opt 选项，则可以使用 –skip-opt 关闭。
–order-by-primary	Dump each table’s rows sorted by its primary key, or by its first unique index	如果存在主键索引，则先按照主键进行排序再写入备份文件中; 如果没有主键索引，则查找表中的第一个唯一索引，按照唯一索引进行排序再写入备份文件中。这可以加快重新导入备份文件的速度，但是备份操作可能需要更长的时间。
–password	Password to use when connecting to server	MySQL 密码
–pipe	Connect to server using named pipe (Windows only)	在 Windows 系统上，使用命名管道连接到服务器。此选项仅适用于服务器支持命名管道连接的场景。
–plugin-dir	Directory where plugins are installed	要查找的插件的目录。如果使用 –default-auth 选项指定了验证插件，但是 mysqldump 却找不到它，则可以使用此选项来指定。
–port	TCP/IP port number for connection	MySQL 端口。
–print-defaults	Print default options	打印默认参数。
–protocol	Transport protocol to use	指定用于连接到数据库实例的连接协议。其中，TCP 是所有平台都支持的，SOCKET 只支持 Linux 服务器，PIPE 和 MEMORY 只支持 Windows 服务器。
–quick	Retrieve rows for a table from the server a row at a time	此选项对于备份大表非常有用。它强制 mysqldump 从服务器一次查询一行数据，而不是一次查询整个表。因为 mysqldump 的每一次查询都需要先将数据保存在本地缓冲区中，所以启用这个选项之后一次查询一行数据就可以尽量不使用缓冲区。在内存足够用的情况下(确保备份表的数据大小绝对不会超过物理内存大小)，可以关闭该选项，以加快备份速度。但是如果内存不够用，则可能会使用到 Swap，从而导致备份速度慢，还影响机器性能。在正常情况下，建议总是使用该选项来进行备份。
–replace	Write REPLACE statements rather than INSERT statements	INSERT INTO 替换为 REPLACE INTO。
–result-file	Direct output to a given file	直接将数据输出到给定的文件中。如果该文件不存在，则会生成新文件; 如果该文件存在，则会覆盖其以前的数据内容。在 Windows 系统下使用该选项，可以防止换行符“\n”被转换为“\r\n”。如果换行符被转换，后续重新加载这个文件时会发生错误。
–routines	Dump stored routines (procedures and functions) from dumped databases	在备份数据中输出包含存储过程和函数。默认为 FALSE。
–set-charset	Add SET NAMES default_character_set to output	默认会在备份前执行 SET NAMES 语句。
–single-transaction	Issue a BEGIN SQL statement before dumping data from server	将事务隔离模式设置为 REPEATABLE READ，并在备份数据之前向服务器发送 SQL 语句 START TRANSACTION 以显式开启一个事务快照。由于是在事务快照内进行备份的，所以使得备份的数据与获取事务快照时的数据是一致的，而且不会阻塞任何应用程序访问服务器。在进行单事务备份时，为确保备份文件有效(表内容和二进制日志位置正确)，其他连接不能使用ALTER TABLE、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE等DDL语句，否则会导致一致性状态被破坏，使得 mysqldump 执行 SELECT 语句检索表数据时查询不到正确的内容或者备份失败。要备份大表，建议结合使用 –single-transaction 和 –quick 选项，以加快备份速度。
–set-gtid-purged	Whether to add SET @@GLOBAL.GTID_PURGED to output	有三个选项： auto(默认)：如果备份服务器启用了 GTID，则在备份文件中添加 SET@@SESSION.SQL_LOG_BIN=0 和 SET @@GLOBAL.GTID_PURGED 语句，否则只添加 SET @@SESSION.SQL_LOG_BIN=0 语句。 OFF：在备份文件中不添加 SET@@SESSION.SQL_LOG_BIN=0 和SET @@GLOBAL.GTID_PURGED 语句。 ON：在备份文件中添加 SET@@SESSION.SQL_LOG_BIN=0 和 SET @@GLOBAL.GTID_PURGED 语句。如果在未启用 GTID 的服务器上使用该选项，则会发生错误。
–tables	Override –databases or -B option	与 –databases 或 -B 选项同时使用时，会覆盖数据库的选项，优先使用 –tables 选项。mysqldump 将该选项之后的所有名称参数视为表名，但必须还要指定一个库名(只能指定一个库名，不能指定多个库名)，才能对表级别范围进行操作，如 mysqldump –master-data -B db_name –tables tb_name1 tb_name2 > aa.sql 或者 mysqldump –master-data db_name –tables tb_name1 tb_name2 > aa.sql。
–triggers	Dump triggers for each dumped table	备份数据库中的触发器。
–user	MySQL user name to use when connecting to server	备份用户名。
–where	Dump only rows selected by given WHERE condition	【重要】导出某一张表时，可以按照某个条件过滤导出。
–xml	Produce XML output	使用 XML 格式输出。

文章抄自：Bing@DBA

前言

常用模版

备份上云

参数详解

<< DIY无泵净水器（零陈水）