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