主页 / dba / 问题 / 17367
Accepted
qazwsx
Asked: 2012-05-03 08:54:41 +0800 CST

如何监控大型 .sql 文件的导入进度？

  • 772

我正在导入 7 GBfoobar.sql来恢复本地数据库中的表。 

$ mysql -h localhost -u root 'my_data' < foobar.sql

$ mysql --version
/usr/local/mysql/bin/mysql  Ver 14.12 Distrib 5.0.96, for apple-darwin9.8.0 (i386) using readline 5.1

我如何监控其进度？

mysql mysqldump

18 个回答

  1. Best Answer

    如果您只是从 *nix 上的 CLI 的转储文件导入，例如

    mysql -uxxx -pxxx dbname < /sqlfile.sql

    然后首先在您的操作系统上安装管道查看器，然后尝试这样的事情：

    pv sqlfile.sql | mysql -uxxx -pxxxx dbname

    这将在程序运行时显示一个进度条。

    它非常有用，您还可以使用它来估计 mysqldump 进度。

    pv 转储sqlfile.sql并将它们传递给 mysql（因为管道运算符）。在倾倒时，它显示了进展。很酷的是 mysql 只在它可以进行的情况下以最快的速度获取数据，因此 pv 可以显示导入的进度。我没有任何证据。但似乎是这样。我想有一些缓冲区使用，但在某些时候我认为mysql当它仍然忙于处理时不会再读取任何数据。

    管道查看器屏幕截图

    • 365

  2. 如果您已经开始导入，您可以在另一个窗口中执行此命令以查看数据库的当前大小。如果您知道要导入的 .sql 文件的总大小，这会很有帮助。

    SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MiB" 
FROM information_schema.TABLES GROUP BY table_schema;

    归功于： http: //forums.mysql.com/read.php?108,201578,201578

    MySQL 8.0 Reference对准确性进行了如下说明：

    DATA_LENGTH

    对于 MyISAM，DATA_LENGTH 是数据文件的长度，以字节为单位。

    对于 InnoDB，DATA_LENGTH 是为聚集索引分配的大致内存量，以字节为单位。具体来说，它是聚集索引大小（以页面为单位）乘以 InnoDB 页面大小。

     

    INDEX_LENGTH

    对于 MyISAM，INDEX_LENGTH 是索引文件的长度，以字节为单位。

    对于 InnoDB，INDEX_LENGTH 是为非聚集索引分配的近似内存量，以字节为单位。具体来说，它是非聚集索引大小的总和，以页面为单位，乘以 InnoDB 页面大小。

    • 42

  3. 当您执行单个数据库的 mysqldump 时，所有表都按字母顺序转储。

    自然，将 mysqldump 重新加载到数据库中也将按字母顺序排列。

    你可以做一个 SHOW PROCESSLIST；并找出运行 mysqldump 的数据库连接。重新加载转储时，数据库连接将消失。

    如果您想知道转储文件中有哪些表，请针对 foobar.sql 运行此命令

    cat foobar.sql | grep "^CREATE TABLE" | awk '{print $3}'

    更新 2012-05-02 13:53 EDT

    很抱歉没有注意到只有一张桌子。

    如果表是 MyISAM，则监控的唯一方法是从操作系统的角度来看。原因？该表在整个重新加载过程中都是写锁定的。你在找什么？.MYD和.MYI文件的大小。当然，您需要将其与之前在您导入的其他数据库服务器上的表大小进行比较。

    如果表是 InnoDB 并且您启用了innodb_file_per_table，则监视的唯一方法是从操作系统的角度。原因？该表在整个重新加载过程中都是写锁定的。你在找什么？文件的大小.ibd。当然，您需要将其与之前在您导入的其他数据库服务器上的表大小进行比较。

    如果表是 InnoDB 并且您禁用了innodb_file_per_table，那么即使是操作系统的观点也无济于事。

    更新 2012-05-02 13:56 EDT

    去年我解决了这样的问题：如何获得“type db.sql | mysql”的 % 进度

    更新 2012-05-02 14:09 EDT

    由于标准 mysqldump 像这样对表进行写锁定：

    LOCK TABLES `a` WRITE;
/*!40000 ALTER TABLE `a` DISABLE KEYS */;
INSERT INTO `a` VALUES (123),(451),(199),(0),(23);
/*!40000 ALTER TABLE `a` ENABLE KEYS */;
UNLOCK TABLES;

    然后，在释放表锁之前，无法从 mysql 中获得进展。

    如果您可以从转储文件中获取LOCK TABLES并UNLOCK TABLES注释掉...

    • 如果表是 MyISAM，则 SELECT COUNT(*) 将起作用
    • 如果表是 InnoDB，则 SELECT COUNT(*) 可能会减慢/停止加载，直到计数完成
    • 19

  4. 每 2 秒，您将看到正在运行的进程。

    watch 'echo "show processlist;" | mysql -uuser -ppassword';

    如果您希望它不那么频繁，请添加-n x其中x是秒数。5秒将是：

    watch -n 5 'echo "show processlist;" | mysql -uuser -ppassword';
    • 16

  5. 如果您只想检查它是否已停止，您可以查询

    show processlist;

    看看正在执行什么。

    • 15

  6. 作为无法让 pv 工作或 pv 说谎的人的解决方案。您可以监控 /var/lib/mysql 中包含数据的 ibdata1 文件的大小。这最终将与源服务器中的文件大小相同（或差不多）。

    如果有很多表，您也可以在 /var/lib/mysql/<数据库名称> 中看到它们一一出现。

    我最近碰巧使用了这个事实，当时一个长期数据库在三四年的时间里建立了一个大约 20G 的日志文件。我注意到转移需要很长时间，并使用这种技术来监控进度。

    我认为，当数据库不涉及某处或其他地方的文件时，这一天是极不可能的。同时，您可以监视文件以查看传输的进度。自从编写了第一个 sql 数据库以来，我建议的方法一直是您可以以一种或另一种形式执行的操作。我从来没有打算暗示这是手动骑师可以依靠的任何一种“官方”技术。它假定您对一般计算机和特别是 unix 具有一般的熟练程度。

    • 5

  7. Rob 的回答在大多数情况下都很好，但是 Pipe Viewer 在 tty 不可用的用例中效果不佳，例如在监视 mysql docker 容器的初始化输出时或当您想要将进度记录到文件时。

    Pipe Monitor ( github ) 是一种替代方案，旨在通过 STDERR 将更新输出到日志流。免责声明：我是作者。

    它们的基本功能非常相似：从 STDIN 或文件读取。将内容通过管道传输到 STDOUT。显示进度。然而，Pipe View 使用终端控制序列来更新单行上的可视进度条，而 Pipe Monitor 输出适合非终端应用程序的文本更新。

    Pipe Monitor 支持以下基本选项。输出可通过 --format 选项自定义：

    Usage: pm [--size SIZE] [--name NAME] [--format FORMAT] INPUT_FILE

Positional arguments:
  INPUT_FILE             Optional input file. If not provided input will be read from STDIN

Options:
  --size SIZE, -s SIZE   Size of input from STDIN. Ignored if using INPUT_FILE
  --name NAME, -n NAME   A NAME tag for this output. Will be pre-pended to default FORMAT string
  --format FORMAT, -f FORMAT
                         Output format string. Allowed keys: %name, %size, %time, %eta, %percent, %written, %buffered
  --help, -h             display this help and exit

    这是非终端环境中每个输出的比较。

    管道查看器（非终端）：

    $ pv -nf testin > testout
40
70
77
84
90
96
100

    管道监视器：

    $ pm testin > testout
Processed 0 bytes of 2456678400 (0% complete). 0 bytes buffered. Running 0s, eta: <unknown>
Processed 1750794240 bytes of 2456678400 (71% complete). 327680 bytes buffered. Running 2s, eta: 1s
Processed 2106937344 bytes of 2456678400 (85% complete). 700416 bytes buffered. Running 4s, eta: 1s
Processed 2419339264 bytes of 2456678400 (98% complete). 2871296 bytes buffered. Running 6s, eta: 0s
Processed 2456678400 bytes of 2456678400 (100% complete). 0 bytes buffered. Running 6s, eta: 0s
    • 3

  8. 我有一个 500 MB 的 SQL 文件要导入。我花了大约2个小时。在导入过程开始时，mysqld CPU 使用率接近 100%。但几分钟后，CPU 使用率下降到 15%。

    我尝试了很多调整，但只有这个对我有帮助：innodb_flush_log_at_trx_commit = 0

    应用此设置并重新启动 mysql 后，导入只需 3 分钟！CPU 利用率一直是 100%。

    如果您喜欢使用此设置，则需要编辑“/etc/mysql/my.cnf”文件并使用“sudo service mysql restart”重新启动 mysql 服务器。

    这是我的“my.conf”文件的设置：

        [mysqld]
    innodb_log_buffer_size = 256M
    innodb_fast_shutdown = 0
    innodb-doublewrite = OFF
    innodb_io_capacity = 1000
    innodb_flush_log_at_trx_commit = 0

    请注意：“innodb_flush_log_at_trx_commit = 0”只会每秒提交一次。所以它不符合 ACID，但对于批量导入是可以接受的。导入后，您可以将“innodb_flush_log_at_trx_commit”的值设置回 1 并重新启动数据库。 链接到 mySQL 文档

    • 3

  9. 对于使用您正在寻找管道查看器示例的人来说，mysqldump您只需执行以下操作：

    mysqldump -hxxx -uxxx -p dbname | pv -W > dump.sql

    该-W标志只是告诉 pv 在显示进度之前等待第一个字节到来（在提示之后）

    • 2

  10. 如果您的数据库在其他方面很安静（即没有其他用户处于活动状态）并且您只想查看读/写活动，为什么不这样做：

    mysqladmin -h<host>-uroot -p<yourpass> extended -r -i 10 |grep 'row'

    您将看到读取/写入/插入/等待/更新的数量。

    例如，如果您要插入，您将看到如下内容：

    Innodb_rows_inserted                          | 28958

    其中 28958 是在您的时间间隔内插入的行数（在我的情况下为 10 秒）。

    • 2

相关问题