mysqldump注意问题一则

文章由LinuxBoy分享于2019-03-31 05:03:02热评（465）

mysqldump注意问题一则

近期有同学使用mysqldump导出数据的时候，发现线下环境导出数据卡住，且导致mysql比较繁忙！同时dump的报错如下：

mysqldump: Couldn't execute 'SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME,
EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE'
AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA IN ('dbname')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME

于是对这条语句进行了explain，发现explain信息如下：

+----+-------------+------------+------+---------------+--------------+---------+------+------+----------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+--------------+---------+------+------+----------------------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | FILES | ALL | NULL | NULL | NULL | NULL | NULL | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | PARTITIONS | ALL | NULL | TABLE_SCHEMA | NULL | NULL | NULL | Using where; Open_full_table; Scanned 1 database; Distinct; FirstMatch(FILES); Using join buffer (Block Nested Loop) |
+----+-------------+------------+------+---------------+--------------+---------+------+------+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

可以看到该sql会打开一个库，然后打开所有的表。我们知道，information.files表是memory engine，表的数据其实都是不存在的，所以这些表的信息需要通过遍历所有的物理文件获取。

mysql> show create table INFORMATION_SCHEMA.FILES\G
*************************** 1. row ***************************
Table: FILES
Create Table: CREATE TEMPORARY TABLE `FILES` (
`FILE_ID` bigint(4) NOT NULL DEFAULT '0',
`FILE_NAME` varchar(64) DEFAULT NULL,
`FILE_TYPE` varchar(20) NOT NULL DEFAULT '',
`TABLESPACE_NAME` varchar(64) DEFAULT NULL,
`TABLE_CATALOG` varchar(64) NOT NULL DEFAULT '',
`TABLE_SCHEMA` varchar(64) DEFAULT NULL,
`TABLE_NAME` varchar(64) DEFAULT NULL,
`LOGFILE_GROUP_NAME` varchar(64) DEFAULT NULL,
`LOGFILE_GROUP_NUMBER` bigint(4) DEFAULT NULL,
`ENGINE` varchar(64) NOT NULL DEFAULT '',
`FULLTEXT_KEYS` varchar(64) DEFAULT NULL,
`DELETED_ROWS` bigint(4) DEFAULT NULL,
`UPDATE_COUNT` bigint(4) DEFAULT NULL,
`FREE_EXTENTS` bigint(4) DEFAULT NULL,
`TOTAL_EXTENTS` bigint(4) DEFAULT NULL,
`EXTENT_SIZE` bigint(4) NOT NULL DEFAULT '0',
`INITIAL_SIZE` bigint(21) unsigned DEFAULT NULL,
`MAXIMUM_SIZE` bigint(21) unsigned DEFAULT NULL,
`AUTOEXTEND_SIZE` bigint(21) unsigned DEFAULT NULL,
`CREATION_TIME` datetime DEFAULT NULL,
`LAST_UPDATE_TIME` datetime DEFAULT NULL,
`LAST_ACCESS_TIME` datetime DEFAULT NULL,
`RECOVER_TIME` bigint(4) DEFAULT NULL,
`TRANSACTION_COUNTER` bigint(4) DEFAULT NULL,
`VERSION` bigint(21) unsigned DEFAULT NULL,
`ROW_FORMAT` varchar(20) DEFAULT NULL,
`TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
`AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
`DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
`MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
`INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
`DATA_FREE` bigint(21) unsigned DEFAULT NULL,
`CREATE_TIME` datetime DEFAULT NULL,
`UPDATE_TIME` datetime DEFAULT NULL,
`CHECK_TIME` datetime DEFAULT NULL,
`CHECKSUM` bigint(21) unsigned DEFAULT NULL,
`STATUS` varchar(20) NOT NULL DEFAULT '',
`EXTRA` varchar(255) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8

深入源码，发现mysqldump这个sql的作用是dump tablespace，来看看源码的部分

{"all-tablespaces", 'Y',
"Dump all the tablespaces.",
&opt_alltspcs, &opt_alltspcs, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0,
0, 0},
{"no-tablespaces", 'y',
"Do not dump any tablespace information.",
&opt_notspcs, &opt_notspcs, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0,
0, 0},
if (opt_alltspcs) //如果选择dump 所有的tablespace 信息
dump_all_tablespaces();
if (opt_alldbs) //如果dump所有的db
{
if (!opt_alltspcs && !opt_notspcs) //如果没有指定dump 所有的tablesapce，也没有指定不dump所有的tablespce
dump_all_tablespaces();
dump_all_databases();
}
else
{
// Check all arguments meet length condition. Currently database and table
// names are limited to NAME_LEN bytes and stack-based buffers assumes
// that escaped name will be not longer than NAME_LEN*2 + 2 bytes long.
int argument;
for (argument= 0; argument < argc; argument++)
{
size_t argument_length= strlen(argv[argument]);
if (argument_length > NAME_LEN)
{
die(EX_CONSCHECK, "[ERROR] Argument '%s' is too long, it cannot be "
"name for any table or database.\n", argv[argument]);
}
}
if (argc > 1 && !opt_databases)
{
/* Only one database and selected table(s) */
if (!opt_alltspcs && !opt_notspcs)
dump_tablespaces_for_tables(*argv, (argv + 1), (argc - 1));
dump_selected_tables(*argv, (argv + 1), (argc - 1));
}
else
{
/* One or more databases, all tables */
if (!opt_alltspcs && !opt_notspcs)
dump_tablespaces_for_databases(argv);
dump_databases(argv);
}
}

我们看到，如果没有指定dump所有的tablespace，也没有指定不dump所有的tablespace信息，默认会dump所有的tablespace信息！！！
我们继续看看文档，关于tablespace的描述:

--all-tablespaces, -Y
Adds to a table dump all SQL statements needed to create any tablespaces used by an NDB table. This information is not otherwise included in the output from mysqldump. This option is currently relevant only to MySQL Cluster tables, which are not supported in MySQL 5.7.

可以看到这个tablespace的信息只在NDB cluster才存在，而我们线上都是非NDB的，所以我们dump数据的时候不需要这些信息。
因此显示的关闭一切就ok了！

-y, --no-tablespaces
Do not dump any tablespace information.

使用mysqldump进行MariaDB 的备份

使用mysqldump导出数据库

基于mysqldump快速搭建从库

恢复mysqldump创建的备份集

使用mysqldump命令行工具创建逻辑备份

mysqldump实现数据库逻辑备份

本文永久更新链接地址：

推荐文章：

mysqldump注意问题一则