如何用 mysqldump 备份数据

辰星3个月前技术文章81

前言

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

备份开始前一定要了解数据量大小,可以使用下方查询:

SELECT TABLE_SCHEMA,
       round(SUM(data_length + index_length + DATA_FREE) / 1024 / 1024, 2) AS TOTAL_MB,
       round(SUM(data_length) / 1024 / 1024, 2)                            AS DATA_MB,
       round(SUM(index_length) / 1024 / 1024, 2)                           AS INDEX_MB,
       round(SUM(DATA_FREE) / 1024 / 1024, 2)                              AS FREE_MB,
       COUNT(*)                                                            AS TABLES
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql', 'INFORMATION_SCHEMA', 'performance_schema')
GROUP BY TABLE_SCHEMA
ORDER BY 2 DESC;

常用模版

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 SECURITYDEFINER,则会报账号不存在的报错。

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 格式输出。


相关文章

linux中的buffer和cache

linux中的buffer和cache

linux中的buffer和cache一、buffer和cache的来源及应用1、来源Buffer 是缓冲区,而 Cache 是缓存,两者都是数据在内存中的临时存储。那么,这两种“临时存储”有什么区别...

ES模糊查询(Wildcard Query)导致CPU打满问题

ES模糊查询(Wildcard Query)导致CPU打满问题

一、概述    Wildcard Query是es中实现模糊查询的一种方式,尤其对有SQL经验的人,会常常习惯于它,毕竟这是和SQL里like操作最相似的查询方式,最近一个客户的es集群就在这上面踩了...

MySQL运维实战(3.1) MySQL官方客户端使用介绍

mysql是mysql安装包默认的客户端。位于二进制安装包的bin目录。或者通过rpm安装包安装mysql-community-client。使用mysql程序linux终端下,输入mysql命令登陆...

Kubernetes 认证授权

Kubernetes 认证授权

1、认证所有 Kubernetes 集群都有两类用户:由 Kubernetes 管理的服务账号和普通用户。任何客户端访问之前,经由 kubernetes 时,需经过:认证(token, ssl)、授权...

gin框架连接mysql数据库连接池泄露

gin框架连接mysql数据库连接池泄露

1、故障爆发12月1号上午10点出头,我们收到阿里云监控告警:客户官网探测异常,如图所示:然后我们DBA查看了后端数据库实例,发现数据库连接已经被用尽了,导致服务出现异常,如图所示:当时我们和客户协商...

通过Nodeport方式暴露集群

通过Nodeport方式暴露集群

一、原理图二、通过deployment部署双副本nginx,两个Pod[root@172-16-121-211 ~]# cat nginx-delpayment01.yml apiVersion: a...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。