How to See Progress of a Large MySQL Import (and Where pv Lies)

You started a database restore forty minutes ago. mysql < dump.sql. The cursor is blinking. There is no percentage, no ETA, no spinner, nothing. You have no idea if it is 10% done or 90% done, whether it will finish before the maintenance window closes, or whether it quietly stalled twenty minutes ago and you are watching a dead terminal. The MySQL client tells you exactly nothing.

This is not your fault and there is no hidden flag you missed. The mysql command-line client has no built-in progress reporting for importing a SQL file. It reads statements and executes them until the file ends. That is the whole feature set. So the community reached for pv, the pipe viewer, to measure progress by counting bytes as they flow into the client. It works, it is the right first move, and it also lies to you at the worst possible moment. Let me show you both halves of that.

pv dump.sql | mysql -u root -p mydb

Why mysql gives you no progress bar

When you run mysql < dump.sql, the client is just streaming SQL text from stdin and sending each statement to the server. It does not know how big the file is (stdin has no length), it does not know how many rows each INSERT contains, and it has no concept of "percent complete". A mysqldump file is typically one giant sequence of INSERT statements per table, often batched, followed by index and constraint creation. From the client's point of view it is an opaque stream. There is nothing to report a percentage against.

That is why every real answer is a workaround that measures something the client can see: the bytes going in. pv sits in the pipe, knows the file's total size (when you give it a real file, not stdin), counts bytes as they pass, and draws a bar. It is measuring the input, not the work.

Step 1: The pv one-liner

Install pv if you do not have it (apt install pv or brew install pv), then:

pv dump.sql | mysql -u root -p mydb

You get a live line like this:

 3.42GiB 0:04:11 [ 13.9MiB/s] [====================>         ] 62% ETA 0:02:31

Because you passed pv the filename directly, it knows the total size (here the dump is about 5.5GiB), so it can show a percentage and an ETA. That is far better than a blinking cursor.

Step 2: Handle a compressed dump

Most large dumps are gzipped. If you decompress in the pipe, put pv on the compressed file so the percentage tracks the file you actually have on disk:

pv dump.sql.gz | gunzip | mysql -u root -p mydb

Here the percentage tracks compressed bytes read, which is still a perfectly good progress signal. Do not put pv after gunzip unless you know the uncompressed size, because then it has no total to compare against and can only show throughput, not a percentage.

Step 3: See what the server is actually doing

Byte progress tells you how much of the file has been sent. It does not tell you which table is loading or whether the server is stuck on one enormous statement. For that, open a second MySQL connection and ask:

SHOW PROCESSLIST;
-- or, more detail on a modern server:
SELECT id, state, LEFT(info, 80) AS statement, time
FROM performance_schema.processlist
WHERE command <> 'Sleep'\G

You will see the import connection with a State like update and the current statement. If the time column keeps climbing on the same statement for minutes, that is the server chewing through one big table or rebuilding an index, not a stall.

Why pv hits 100% and then keeps going

This is the trap. pv measures bytes flowing through the pipe. But a mysqldump ends with the expensive part: creating secondary indexes, re-enabling keys, and checking foreign-key constraints on tables that are now full of data. Reading those final SQL statements takes milliseconds, so pv races to 100% and the ETA hits zero. Then the server spends another twenty minutes rebuilding indexes on a large table, with pv long since exited. Your progress bar said "done" and the database is still working hard.

So the honest reading of pv is: it accurately tells you how much of the file has been sent, and it is a good relative signal for the bulk data load, but it systematically under-estimates total time because the last chunk of bytes represents a disproportionate chunk of work. Trust it for "am I roughly halfway", never for "it will be done in exactly 2 minutes".

Step 4: Make the import faster (so progress matters less)

The best way to stop worrying about progress is to shorten the import. The single biggest win on InnoDB is loading data before building secondary indexes, which is what mysqldump --disable-keys arranges for MyISAM and what happens naturally if you structure the dump well. For a fresh restore into an empty schema you can also relax durability for the duration:

-- Run in the import session, before loading. Restore afterwards.
SET SESSION foreign_key_checks = 0;
SET SESSION unique_checks = 0;
SET SESSION autocommit = 0;
-- ... source the dump ...
COMMIT;
SET SESSION foreign_key_checks = 1;
SET SESSION unique_checks = 1;

For very large dumps, splitting the file per table lets you load tables in parallel across several connections, turning one long serial import into several shorter ones. If your dump keeps failing partway on a giant single row, that is a different problem, see MySQL "got a packet bigger than max_allowed_packet".

Verification

The import is genuinely finished when the mysql process exits and your shell prompt returns. Confirm the data is all there rather than trusting the bar:

mysql -u root -p -e "SELECT table_name, table_rows
  FROM information_schema.tables WHERE table_schema='mydb'
  ORDER BY table_rows DESC LIMIT 10;"

Compare a couple of large tables' row counts to the source. If they match and the shell prompt has returned, it is done, regardless of what pv claimed five minutes earlier.

What people get wrong

"pv said 100%, so it hung, let me kill it." This is how people corrupt a restore. When pv exits at 100% but your prompt has not returned, the server is still applying the tail (indexes, constraints). Killing mysql mid-index-build leaves you with a half-restored database. Wait for the prompt, not the bar.

"Wrap it in a fancy progress script." Any script that estimates completion from bytes has the same blind spot as pv, because the information simply is not in the byte stream. There is no clever parsing that recovers the index-rebuild time in advance.

"Just add more RAM / a bigger buffer pool and it'll show progress." Buffer pool size affects speed, not visibility. You will still have no native progress bar; you will just wait less.

When it is still broken

• It looks stuck at one statement for a long time. Check SHOW PROCESSLIST. A State of Waiting for table metadata lock means another session is blocking you, not a slow import, see the metadata lock playbook.
• You need progress on a restore you did not start with pv. You cannot attach pv after the fact, but you can watch the destination grow: watch -n5 "du -sh /var/lib/mysql/mydb" gives a rough sense of movement.
• The import dies partway with a duplicate-key or constraint error. That is a data or ordering problem, not a progress problem. Re-run with foreign_key_checks=0 during load, or fix the offending statement.
• You are importing routinely and want real observability. Consider mysqlsh's parallel import utility, which does report progress natively for its own load path, rather than fighting the plain client.

The takeaway: pv dump.sql | mysql is the right tool and you should use it every time, as long as you read it for what it is, a byte meter, not a clock. When you need to know what the server is truly doing right now, the answer is always a second connection and SHOW PROCESSLIST.