主页 / dba / 问题 / 48231
Accepted
HTF
Asked: 2013-08-17 02:43:58 +0800 CST

MySQL：错误 126 (HY000)：表的密钥文件不正确 + 逻辑备份慢

  • 772

我已经用 'tmpfs' 挂载了 '/tmp' 目录，由于某种原因，这导致了以下错误：

mysql> SELECT DISTINCT table_schema FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')
    -> ;
ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_29ef_0.MYI'; try to repair it

-

# df -h /tmp/
Filesystem            Size  Used Avail Use% Mounted on
tmpfs                 2.0G   12K  2.0G   1% /tmp
# df -i /tmp/
Filesystem            Inodes   IUsed   IFree IUse% Mounted on
tmpfs                2041621       7 2041614    1% /tmp
# mount | grep /tmp
tmpfs on /tmp type tmpfs (rw,size=2048M)

请注意，当 '/tmp' 目录与 ext4 文件系统一起挂载时，相同的查询可以正常工作。

编辑：

服务器_01

# cd /var/lib/mysql ; ls -lR | grep -c "\.frm$"
1876765

但这也发生在表少得多的服务器上：

服务器_02

# cd /var/lib/mysql ; ls -lR | grep -c "\.frm$"
49514

我正在使用此查询列出所有数据库，但由于它不起作用，tmpfs我只是将其替换为更简单的一个（显示数据库...）。

我正在查看/tmpmount with上的磁盘空间tmpfs，但仍然有足够的空间，所以我不确定它是如何耗尽空间的？

基本上，我在服务器上使用约 8000 个数据库进行逻辑备份时遇到问题 - 完成此任务需要很多小时（约 24 小时）。我创建了一个简单的 BASH 脚本（请参见下文），而不是mysqldump我使用mydumper的初始测试表明它要快得多。

备份最初运行非常快，然后显着减慢：

# ./backup.sh
Backing up database number: 1
Completed in: 0.016
Backing up database number: 2
Completed in: 0.078
Backing up database number: 3
Completed in: 0.074
Backing up database number: 4
Completed in: 0.068
Backing up database number: 5
Completed in: 0.071
Backing up database number: 6
Completed in: 0.060
Backing up database number: 7
Completed in: 0.067
Backing up database number: 8
Completed in: 0.070
Backing up database number: 9
Completed in: 0.065
.....
Backing up database number: 107
Completed in: 10.749
Backing up database number: 108
Completed in: 12.125
Backing up database number: 109
Completed in: 11.313
Backing up database number: 110
Completed in: 11.572
Backing up database number: 111
Completed in: 11.371
.....

脚本：

#!/usr/bin/env bash

DATA_DIR="/tmp/mysqlbackup"
LOCKFILE=/tmp/backup.lock
NOW=$(date +%Y%m%d)
COUNT=1

if [ -f "$LOCKFILE" ]; then
        echo "$(basename $0) is already running: PID $(cat $LOCKFILE)"
        exit 0

else

        echo $$ > $LOCKFILE

        if [ ! -d $DATA_DIR/$NOW ]; then
                mkdir -m 700 -p $DATA_DIR/$NOW
        fi

        while read DB; do

                (( COUNT++ ))

                echo "Backing up database number: $COUNT"

                START=$(date +%s.%N)

                mydumper -e -o $DATA_DIR/$NOW/$DB -B "$DB"

                ELAPSED=$(date +%s.%N)  

                printf "Completed in: %.3F\n" $(echo $ELAPSED - $START | bc)

        done <<< "$(mysql -A -B -N -e "SHOW DATABASES" | egrep -v '(mysql|*_schema|log)')"

        echo "Removing backup dir...";

        rm -rf $DATA_DIR/$NOW

        rm -f $LOCKFILE

        exit 0

fi
mysql backup

1 个回答

  1. Best Answer

    方面 #1：错误文件

    有些东西告诉我，你不知何故用完了/tmpfs.

    每当 MySQL 创建一个临时表时

    • 它被创建为 MyISAM 表
    • 没有.frm创建
    • 初始.MYD为 0 字节
    • 初始.MYI为 1024 字节 (1K)
    • 从来没有任何索引.MYI

    我发现 MySQL 无法创建 1K 很奇怪.MYI。

    由于ext3默认块大小为 1K，而ext4具有 4K，也许 4K 块大小可能允许更多数据。这本身并不是真正的问题。

    方面 #2：您的问题

    如果您有数以万计的表，那么您正在运行的查询看起来要求很高。事实上，查询说“给我所有包含 1 个或多个表的数据库”。如果您有大量表，您可能会查看一个相当大的临时表，以便table_schema在执行DISTINCT.

    有时候很容易忘记

    有两个 Stackoverflow 帖子提到了这一点

    分析

    我的猜测是异常的组合

    异常 #1：您有大量的表

    异常 #2：正在制作的临时表必须空间不足才会损坏.MYI

    异常 #3：查询不使用索引。

    看表

    mysql> show create table information_schema.tables\G
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) 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,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

    需要进行全表扫描，因此需要具有内存排序的临时表。

    建议

    建议 #1：使用ext4. 您已经说过查询在ext4.

    建议#2：tmpfs增加到16G

    SUGGESTION #3：查询 information_schema 使其不使用太多内存

    DROP TABLE IF EXISTS mysql.dbtbcount;
CREATE TABLE mysql.dbtbcount
(
    table_schema VARCHAR(64) NOT NULL,
    table_count INT NOT NULL DEFAULT 0,
    PRIMARY KEY (table_schema)
) ENGINE=MEMORY;
INSERT INTO mysql.dbtbcount (table_schema)
SELECT schema_name FROM information_schema.schemata
WHERE schema_name NOT IN ('information_schema','mysql'); 
INSERT INTO mysql.dbtbcount (table_schema)
SELECT table_schema FROM information_schema.tables
ON DUPLICATE KEY UPDATE table_count = table_count + 1;
SELECT * FROM mysql.dbtbcount;
SELECT COUNT(1) DistinctDBs FROM mysql.dbtbcount WHERE table_count > 0;

    这会计算所有数据库中的所有表

    • 当任何行的 table_count 为零时，该数据库为空。
    • 计算所有非零的行table_count以获得 DISTINCT 计数。
    • 完成后，运行DROP TABLE mysql.dbtbcount;

    试试看 ！！！

    更新 2013-08-19 17:40 EDT

    您的问题应该作为单独的问题发布。尽管如此，让我们看看我们可以从中推测出什么。

    您不应将其/tmp用作备份位置。为什么？

    • 操作系统需要在那里写入临时文件。在操作系统重新启动时，所有文件都会从中消失。因此，临时文件夹不应该是备份的地方。
    • mysqld 已/tmp定义为临时文件的默认文件夹

    您需要重新组织脚本以执行并行转储

    建议

    您应该一次收集数据库和并行 mysqldump 25 个数据库，从最大的数据库开始到最小的数据库。每下一组 25 组它会变得更快......

    BACKUP_FOLDER=/any/path/but/tmp

MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS}"

SQLSTMT="SELECT db FROM (SELECT table_schema db,SUM(data_length) datasize"
SQLSTMT-"${SQLSTMT} FROM information_schema.tables"
SQLSTMT-"${SQLSTMT} WHERE table_schema NOT ('mysql','log')"
SQLSTMT-"${SQLSTMT} AND RIGHT(table_schema,7)<>'_schema'"
SQLSTMT-"${SQLSTMT} GROUP BY table_schema) A"
SQLSTMT-"${SQLSTMT} ORDER BY datasize DESC"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > /tmp/ListOfDatabases.txt

COMMIT_COUNT=0
COMMIT_LIMIT=25
MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers"
for DB in `cat /tmp/ListOfDatabases.txt`
do
    BACKUP_FILE=${BACKUP_FOLDER}/${DB}.sql.gz
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB} | gzip > ${BACKUP_FILE} &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

    更新 2013-08-20 10:03 EDT

    如果您不关心数据库的大小，这里有一个替代方法可以更快地启动 mysqldump 进程：

    BACKUP_FOLDER=/any/path/but/tmp

MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS}"

SQLSTMT-"${SQLSTMT} SELECT schema_name FROM information_schema.schemata"
SQLSTMT-"${SQLSTMT} WHERE schema_name NOT ('mysql','log')"
SQLSTMT-"${SQLSTMT} AND RIGHT(schema_name ,7)<>'_schema'"

COMMIT_COUNT=0
COMMIT_LIMIT=25
MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers"
for DB in `cat /tmp/ListOfDatabases.txt`
do
    BACKUP_FILE=${BACKUP_FOLDER}/${DB}.sql.gz
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB} | gzip > ${BACKUP_FILE} &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

    我之前写过关于并行数据库和表转储的文章：How can I optimize a mysqldump of a large database?

    如果您无法mydumper根据数据库大小进行节流，这就是要走的路。

    试试看 ！！！

    • 2

相关问题