文章目录

  • 前言
  • 常用模版
  • 1. 备份整个实例
  • 2. 备份单个数据库
  • 3. 导出单表结构与数据
  • 4. 仅导出单表数据
  • 5. 仅导出单表部分数据
  • 6. 某库下表结构导出
  • 备份上云
  • 1. DEFINER 问题
  • 2. 操作过程
  • 3. 对象数验证
  • 参数详解
  • 后台备份


前言

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

2. 备份单个数据库

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

3. 导出单表结构与数据

恢复表时,如果目标库有同表名,会被 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

4. 仅导出单表数据

仅有数据,没有表结构。

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

5. 仅导出单表部分数据

使用 --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

6. 某库下表结构导出

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

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

2. 操作过程

准备 Python 脚本 drop_definer

import sys

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

PS:如何删除 DEFINER 定义 这篇文章也介绍许多方法。

第一次只备份数据

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

3. 对象数验证

通过下方 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 格式输出。

后台备份

使用 nohup 命令:

nohup mysqldump -uroot -pYouPassword --port=3306 --single-transaction --master-data=2 --triggers --routines --events --all-databases --result-file=/data/backup/all.sql >> /data/backup/backup.log  2>&1 &