有什么方法可以在“gunzip

Once a week I need to run a giant database update into my local development environment like so:

每周我需要对本地开发环境进行一次大型数据库更新，如下所示:

$ gunzip < /path/to/database1.sql.gz | mysql -uUSER -p database1 &
$ gunzip < /path/to/database2.sql.gz | mysql -uUSER -p database2 &
$ gunzip < /path/to/database3.sql.gz | mysql -uUSER -p database3 &

I try to run these overnight since it can take several hours to complete.

我试着在晚上运行这些程序，因为可能需要几个小时才能完成。

Can you help me come up with a way to show progress on these tasks?

你能帮我想出一个方法来展示这些任务的进展吗?

Here are some guesses:

这里有一些猜测:

Get the uncompressed filesize of the db and compare with my local db size
获取db的未压缩文件，并与我的本地db大小进行比较。
Run show processlist in mysql to see what table it's currently inserting (my current method, but some tables are huge, and at least one db of mine only has one giant table so the bulk of the process is stuck in this table, leaving this option less than helpful)
在mysql中运行show processlist以查看它当前插入的表(我的当前方法，但是有些表很大，而且至少有一个db只有一个大表，所以大部分进程都被卡在这个表中，所以这个选项没有多大用处)

All of the db.sql.gz files are standard gzipped mysqldumps, so I don't think I can build anything into the dumps to give me an update. (But I'm open to that if I'm missing something)

所有的db.sql。gz文件是标准的gzipped mysqldump文件，所以我不认为我可以在转储文件中构建任何内容来更新。(但如果我遗漏了什么，我愿意接受)

Bounty Rules

赏金规则

Answers must:

答案必须:

Provide useful and reasonably accurate progress (either visual like scp (preferred!) or through a simple progress database table that could be accessed easily).
提供有用且相当准确的进度(可以是可视化的scp(首选!)，也可以通过简单的可以轻松访问的进度数据库表)。
Not break regular mysqldump export or regular gunzip ... | mysql import (for other engineers who may not use whatever you come up with)
不破坏常规mysqldump导出或常规gunzip…| mysql导入(适用于其他可能不使用您提供的任何东西的工程师)
Not give my DBA a heart attack — so stay easy on special mysqldump or alternative mysql branch requests.
不要让我的DBA心脏病发作——所以请在特殊的mysqldump或其他mysql分支请求上保持轻松。

2 个解决方案

#1

You may use -v : Verbose mode (show progress) in your command, or there's another method using Pipe Viewer (pv) which shows the progress of the gzip, gunzip command as follows:

您可以使用-v: Verbose模式(显示进度)，或者使用Pipe Viewer (pv)的另一种方法显示gzip的进度，gunzip命令如下:

$ pv database1.sql.gz | gunzip | mysql -u root -p database1

This will output progress similar to scp:

这将输出与scp类似的进展:

$ pv database1.sql.gz | gunzip | mysql -uroot -p database1
  593MiB 1:00:33 [ 225kiB/s] [====================>              ] 58% ETA 0:42:25

You can also use Pipe Viewer to monitor mysqldump:

您还可以使用管道查看器监视mysqldump:

mysqldump -uroot -p database1 | pv | gzip -9 > database1.sql.gz

If you don't already have pv, you can install it with:

如果你还没有pv，你可以用以下方法安装:

yum install pv

or with macports

或使用macports

sudo port install pv

#2

I think I would perform a drop database before loading up the data, and after it happened I could run a continuous "du -sh" in the databases directory. If I know the size the original database directory (Why wouldn't I?) then I can use it as a progress monitor. Are the db's are droppable?

我想在加载数据之前，我应该执行一个drop数据库，在加载数据之后，我可以在数据库目录中运行一个连续的du -sh。如果我知道原始数据库目录的大小(为什么我不知道?)，那么我可以将它用作进度监视器。db是可删除的吗?

#1