前言

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

常用模版

  1. 备份整个实例

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

mysqldump -uroot -p --port=3306 --single-transaction --master-data=2 --triggers --routines --events --all-databases > ./bakup_`date +"%F_%H_%M_%S"`.sql
  1. 备份单个数据库
mysqldump -uroot -p --port=3306 --single-transaction --master-data=2 --triggers --routines --events --databases db_name > ./bakup_`date +"%F_%H_%M_%S"`.sql
  1. 导出单表结构与数据

恢复表时,如果目标库有同表名,会被 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
  1. 仅导出单表数据

仅有数据,没有表结构。

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
  1. 仅导出单表部分数据

使用--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
  1. 某库下表结构导出

有时研发有将表结构迁移到另外一个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

备份上云

  1. 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 再重新导入。

  1. 操作过程

准备 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
  1. 对象数验证

通过下方 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