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.