mysqldump 应用标准

数据库查询很重要，沒有备份数据，内容丢失只有老板跑路。因此或是搞好备份数据吧！

文件目录

• 一、专用工具详细介绍
• 二、专用工具特性
• 三、备份数据管理权限
• 四、专用工具应用限定
• 五、已经知道BUG
• 六、备份数据前常见问题
  • 6.1 必须长期备份数据或导进时，请应用 screen 对话框专用工具
  • 6.2 查验数据库查询主要参数
  • 6.3 查验数据库查询人物角色
• 7、操作方法举例说明
  • 7.1 全库备份数据
    • 开展mysql.proc 单表导出来：
    • 开展mysql.proc 单表导进：
  • 7.2 单库备份数据
  • 7.3 单表备份数据
  • 7.4 转化成独立的数据库文件（-T）
    • 转化成文档实例：
  • 7.5 依据标准备份数据（-w）
• 八、数据信息导进
  • 8.1 备份数据导进前常见问题
    • (1) 在备份数据导进前应查验下列主要参数的配备
    • (2) 查验SQL文档中所需DROP 的表是不是自身预估内的
  • 8.2 应用PV专用工具监管文档导进全过程
  • 8.3 mysql client PV 对话主要参数导进（强烈推荐）
• 九、独特难题标准
  • 9.1 情景1：
    • 涉及到主要参数：
    • 留意难题：
  • 9.2 情景2：
    • 涉及到主要参数：
    • 留意难题：
  • 由于有悔，因此披星戴月；由于有梦，因此义无反顾！ 个人网站先发：easydb.net 微信公众平台：easydb 关注我，不动丢！

一、专用工具详细介绍

mysqldump是mysql内置的逻辑性备份工具。

它的备份数据基本原理是，根据协议书联接到mysql数据库查询，将必须备份数据的数据统计出去，将查看出的数据交换成相匹配的insert句子，在我们必须复原这种数据信息时，只需实行这种insert句子，就可以将相匹配的数据还原。

二、专用工具特性

• 完全免费备份工具
• 适用远程控制备份数据
• 适用转化成CSV文件格式或XML文件格式的文档
• 能够应用文本工具立即解决相匹配的备份数据数据信息，以更灵便便捷的开展恢复工作
• 与储存模块不相干，能够在多种多样储存模块下开展备份和恢复，对innodb模块适用热备，对MyISAM模块适用温备（增加表锁）
• 当数据信息为浮点数种类时，会发生精密度遗失
• 备份数据的全过程是串行化的，不兼容并行处理备份数据

三、备份数据管理权限

备份数据未应用–single-transaction选择项时，还必须LOCK TABLES管理权限

四、专用工具应用限定

1. mysqldump数据归档时默认设置不容易备份数据INFORMATION_SCHEMA, performance_schema, sys，若有要求数据归档，必须再cmd上显式的特定她们。

2. mysqldump不容易数据归档InnoDB CREATE TABLESPACE句子。

3. mysqldump不容易备份数据 NDB cluster ndbinfo信息内容数据库查询。

4. 在开启了GTID的数据库查询中应用mysqldump备份数据时必须留意，假如备份数据中包括了GTID信息内容，则没法修复到沒有开启GTID的数据库查询中。

5. Windows根据PowerShell应用以下指令开展数据归档时，数据归档文档默认设置应用UTF-16编号，而MySQL不允许将UTF-16做为联接字段名，因此根据以下指令备份数据的数据归档文档将没法恰当载入到数据库查询中

   mysqldump [options] > dump.sql
   

6. mysqldump是并行处理，当信息量大时备份数据时间长，乃至有可能在备份数据全过程中非事务管理表长期性锁表对业务流程导致危害（SQL方式的备份数据数据修复時间也较长）。

7. 谨慎使用 –compact 主要参数，此参数会除掉文件头与文档尾的一些基本参数（例如时区时间，字段名…），造成 安全隐患。

8. 主要参数 –lock-all-tables，–flush-privileges各自会在备份数据时开展 flush tables 和 flush privileges 实际操作，会造成GTID，备份数据从库时一定要注意。

9. 在对数据库查询开展彻底备份数据前，必须搜集数据库查询有关信息（储存模块、字段名等），保证备份数据內容详细，下列为搜集句子：

   -- 查询表有关信息
   select table_schema,table_name,table_collation,engine,table_rows
   from information_schema.tables
   where table_schema NOT IN ('information_schema' , 'sys', 'mysql', 'performance_schema');
   -- 查询是不是存有sql语句、触发器原理、生产调度事情等
   select count(*) from information_schema.events;
   select count(*) from information_schema.routines;
   select count(*) from information_schema.triggers;
   -- 查询字段名信息内容
   show variables like 'character%';
   

10. mysqldump的一些选择项跟mysqlpump有一些转变，在应用中特别注意：

五、已经知道BUG

MySQL5.7.7迄今（2020/11/02）的全部5.7的小版本号，在应用mysqldump在实行–all-databases都是会清除mysql.proc造成 sys没法一切正常应用。

mysql> SELECT * FROM sys.processlist;
ERROR 1356 (HY000): View 'sys.processlist' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

在应用mysqldump在实行–all-databases不容易备份数据mysql.proc下的系统软件本身的sql语句，造成 导到新设备后，一部分sys下的主视图无法一切正常应用；这是一个BUG，而且只存有于MySQL5.7。

bug联接：

• https://bugs.mysql.com/bug.php?id=86807

• https://bugs.mysql.com/bug.php?id=92631

• https://bugs.mysql.com/bug.php?id=83259

• https://GitHub.com/mysql/mysql-server/commit/ded3155def6ba3356017c958c49ff58c2cae1830

假如由于应用–all-databases主要参数早已导致sys出现异常出错，那样做能够修补其出现异常：

mysql_upgrade --upgrade-system-tables --skip-verbose --force

-- 应用mysql_upgrade的情况下要再加上 --upgrade-system-tables。要不然会扫描仪用户库表，期内上锁且速率一般。

六、备份数据前常见问题

6.1 必须长期备份数据或导进时，请应用 screen 对话框专用工具

假如你已经做一个大表 DDL 或是大批量实行一个 sql 文档，实行一半远程桌面连接断掉了，你是否会想呕血？假如恰好实行大批量 sql 时沒有打开事务管理，想一想不良影响会如何？

应用 screen 时，即便当今远程控制终端设备被断开，也不会终断已经实行的每日任务：

## 安裝 screen
yum install -y screen
## 打开一个 screen
screen
## 查询全部打开的 screen
screen -ls
## 再次联接到特定的 screen
screen -r xxx

6.2 查验数据库查询主要参数

6.3 查验数据库查询人物角色

查验mysqldump备份数据人物角色，留意假如在从库应用下边mysqldump指令时，不必将–master-data和–flush-logs一起应用，有可能导致数据库死锁危害备份数据与拷贝。

7、操作方法举例说明

7.1 全库备份数据

留意不但必须备份数据数据信息，还必须备份数据sql语句、触发器原理、事情：

mysqldump -uops -p -h127.0.0.1 -P4444 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --hex-blob --triggers --routines --events --all-databases > /tmp/backup/all_db_with_data.sql

留意：如果是MySQL5.7版本号有BUG（敬请看五、已经知道BUG）必须多加上一步实际操作：

开展mysql.proc 单表导出来：

mysqldump -uops -p -h127.0.0.1 -P4444 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --set-gtid-purged=off --hex-blob --triggers --routines --events --tables mysql proc > /tmp/backup/mysql_proc.sql

之上导出来的SQL，务必在打开SET @@SESSION.SQL_LOG_BIN= 0;的状况下，运用mysql> source mysql_proc.sql的方法导进，不然会导致异常现象危害主从关系创建，要不是为了更好地建立主从关系，可忽视此导进方法。

开展mysql.proc 单表导进：

#不构建主从复制的状况下：
    多种多样导进方法都可以
#要构建主从复制的状况下：
    mysql> use mysql
    mysql> SET @@SESSION.SQL_LOG_BIN= 0;
    mysql> source /tmp/backup/mysql_proc.sql

7.2 单库备份数据

## 备份数据表结构和数据信息
mysqldump -uops -p -h127.0.0.1 -P4444 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --set-gtid-purged=off --hex-blob --databases employees > /tmp/backup/employees_all.sql
## 只备份数据表结构
mysqldump -uops -p -h127.0.0.1 -P4444 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --set-gtid-purged=off --hex-blob --no-data --databases employees > /tmp/backup/employees_schema.sql
## 只备份数据数据信息
mysqldump -uops -p -h127.0.0.1 -P4444 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --set-gtid-purged=off --hex-blob --no-create-info --databases employees > /tmp/backup/employees_only_data.sql

7.3 单表备份数据

## 只备份数据employees数据库查询中的salaries表
mysqldump -uops -p -h127.0.0.1 -P4444 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --set-gtid-purged=off --hex-blob --tables employees salaries > /tmp/backup/employees_salaries.sql
## 清除特定数据库查询的特定表
mysqldump -uops -p -h127.0.0.1 -P4444 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --set-gtid-purged=off --hex-blob --ignore-table=employees.departments --ignore-table=employees.employees employees > /tmp/backup/employees_exclude_departments.sql

留意

假如备份数据时要清除某一数据库查询中好几个表，要应用好几个–ignore-table句子，不可以在后面加分号做切分。

7.4 转化成独立的数据库文件（-T）

mysqldump -uops -p -h127.0.0.1 -P4444 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs -T /tmp/salaries --databases employees --tables salaries > employees_salaries.sql

常见问题

• 该选择项值特定的是转化成表备份数据的文件目录。因此要如上所显示加上–database –tables。该选择项与–database、–all-databases选择项相互独立，不可以一起应用
• 仅有当mysqldump与mysqld网络服务器在同一台设备上运作时，才可以应用该选择项
  由于在转化成备份数据时只有应用当地文件目录，而且实行备份数据的MySQL帐户还务必具备FILE管理权限。因此， secure_file_priv系统软件主要参数务必设定为空串（由于是启用 SELECT …INTO OUTFILE句子)，以促使运行mysqld过程的系统登录帐户能够将这种备份数据转化成到特定的系统文件文件目录下
• （留意，应用该选择项时特定的文件目录务必是备 份网络服务器上的文件目录，不可以远程桌面连接备份数据，由于SELECT … INTO OUTIFLE 只有写到网络服务器当地文件目录下且运行 mysqld 过程的客户必须具备访问限制

转化成文档实例：

less salaries/salaries.txt

10001   60117   1986-06-26      1987-06-26
10001   62102   1987-06-26      1988-06-25
10001   66074   1988-06-25      1989-06-25
10001   66596   1989-06-25      1990-06-25
10001   66961   1990-06-25      1991-06-25

7.5 依据标准备份数据（-w）

只数据归档由给出的WHERE标准挑选的行

###依照where标准，备份数据特定库下的特定表的数据信息
mysqldump -uops -p -h127.0.0.1 -P4444 --default-character-set=utf8mb4 --single-transaction --master-data=2 -w 'id > 10 and id < 20' --databases testdb --tables sbtest1 > /tmp/backup/sbtest1.sql
###依照where标准，备份数据特定库下的全部表的数据信息
mysqldump -uops -p -h127.0.0.1 -P4444 --default-character-set=utf8mb4 --single-transaction --master-data=2 -w 'id > 10 and id < 20' --databases testdb > /tmp/backup/sbtest1.sql

常见问题

1. 仅用–database无需–table的状况下特别注意：
   同一个库要全部的表都需要有where标准列而且列基本数据类型一致。经检测同一个库下假如有一个表不具备where标准列，这时mysqldump会出错；经检测同一个库下如果有表，具备同名的where标准列，但基本数据类型不一样，这时mysqldump不容易出错，但备份数据出不来其数据信息，总是备份数据合乎where标准的表的数据信息。
2. 当只特定–tables而不特定–databases选择项，或是在cmd选择项中沒有得出表明库名的主要参数时，mysqldump会把紧随在–tables以后的命令行参数中的第一个作为库名来分析，从第二个主要参数逐渐所有被分析为表名。
3. 假如数次特定一个备份数据目标，则不容易去重复，只是实行数次备份数据.因此，在工作环境中不建议库名与表名同样。

八、数据信息导进

8.1 备份数据导进前常见问题

(1) 在备份数据导进前应查验下列主要参数的配备

(2) 查验SQL文档中所需DROP 的表是不是自身预估内的

less all_db_with_data.sql | grep -E "^DROP TABLE IF EXISTS|^USE"

8.2 应用PV专用工具监管文档导进全过程

在一般的备份数据导进中，只有凭工作经验去估算一下备份数据具体导进的時间，这儿能够应用PV专用工具，比较精准的计算导函数剩下量及进行時间，因此提议应用此类方法，开展数据信息导进

#主要参数表明：
#-W：在必须登陆密码键入时有效，可等候登陆密码輸出进行，再打开监管时间轴
#-L:过流保护，将传送限定在每秒钟较大字节数的范畴内（尺寸可自定，企业可变性）
shell> pv -W -L 2M all_db_with_data.sql | /data/mysql/base/5.7.25/bin/mysql -uops -p -S/data/mysql/data/3333/mysqld.sock
Enter password: 
 588MiB 0:04:54 [   2MiB/s] [======================================================>] 100%

8.3 mysql client PV 对话主要参数导进（强烈推荐）

#主要参数表明：
#reset master：在导进设备有本身的GTID时，会导进不成功，因此可依据必须在echo里边加上"reset master"指令，清除GTID purge。
shell> (echo 'set @@autocommit=1; set @@interactive_timeout=28800; reset master;'; pv -W -L 5C all_db_with_data.sql) | mysql -h127.0.0.1 -P3333 -uops -p
shell> (echo 'set @@autocommit=1; set @@interactive_timeout=28800;'; pv -W -L 5C employees_all.sql) | mysql -h127.0.0.1 -P3333 -uops -p employees

九、独特难题标准

9.1 情景1：

避免SQL导进中造成过大的binlog，使主从关系延迟时间提升，想在导进时把日志文件格式改成statement，减少binlog

涉及到主要参数：

transaction_isolation

binlog_format

留意难题：

假如表有InnoDB表，而且事务管理隔离级别是READ COMMITTED或READ UNCOMMITTED，那麼只有应用根据行的日志纪录。能够将日志文件格式改成statement，但在运作时那样做会造成 不正确，由于InnoDB表不可以再实行insert句子。

mysql> select @@binlog_format;
 ----------------- 
| @@binlog_format |
 ----------------- 
| STATEMENT       |
 ----------------- 
1 row in set (0.00 sec)

mysql> show variables like '%iso%';
 ----------------------- ---------------- 
| Variable_name         | Value          |
 ----------------------- ---------------- 
| transaction_isolation | READ-COMMITTED |
| tx_isolation          | READ-COMMITTED |
 ----------------------- ---------------- 
2 rows in set (0.00 sec)

mysql> create table test(id int, name varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test values(1, 'zhou'),(2, 'wei');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test;
 ------ ------ 
| id   | name |
 ------ ------ 
|    1 | zhou |
|    2 | wei  |
 ------ ------ 
2 rows in set (0.00 sec)

mysql> insert into test values(3,'bing');
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

在实行备份数据导进时有将binlog_format临时性设定为statement要求时，必须查验transaction_isolation是不是为RR。在RC等级下，binlog_format设定为statement会造成 不可以开展insert有关实际操作。因此并不是RR等级，请不要将binlog_format改成statement。

9.2 情景2：

备份数据或导进SQL保过大，超出max_allowed_packet，备份数据或导进不成功

涉及到主要参数：

max_allowed_packet

留意难题：

备份数据或导进时明确数据库表中的标识符种类：如果有BLOB列或长字符串数组等标识符种类提议有效提升这一值。

#  mysqldump -h10.100.143.62 -umeslogin -P3306 -p --default-character-set=utf8mb4 --master-data=2 --flush-logs --single-transaction --set-gtid-purged=off --hex-blob --databases yfmes_metal_sys > yfmes_metal_sys_all.sql
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `operlog` at row: 264227

导进时也类似的基本原理。

能够在mysql、mysqldump等client指令后临时性改动此参数：

mysqldump [xxxx] --max_allowed_packet=255M > dump.sql
mysql [xxxx] --max_allowed_packet=255M < dump.sql

由于有悔，因此披星戴月；由于有梦，因此义无反顾！ 个人网站先发：easydb.net 微信公众平台：easydb 关注我，不动丢！