mysqldump 备份详解

前言

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

2. 备份单个数据库

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

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
1

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
1

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
1

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

备份上云

1. DEFINER 问题

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

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

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`
1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

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

然后 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)
1
2
3
4
5
6
7

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
1

第二次只备份触发器等

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
1

云上还原：

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

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

参数详解