How do I get % progress for “type db.sql | mysql”

Question

I am doing:

type db.sql | mysql

and the console output is nothing. I see the ibdata1 file growing, but I don't know how many GB it will grow to.

I would like to see a % progress indicator of some kind. Any ideas?

Answer 1

Under unix-a-like systems like Linux there are a number of options. Pipe Viewer (pv, available here) is the one I tend to use, and it is conveniently present in most distro's standard package repositories (aptitude install pv under Ubuntu).

But your use of type rather than cat implies that you are using Windows and I'm not aware of any such tools for that environment, though no doubt one of pv or one of the tools Gaius mentioned can be compiled easily under cygwin.

Be aware that this is metering the amount of the file sent to mysql, not the amount of work mysql has actually done, and the amount of the file sent to mysql may not accurately map to the amount of work mysql has done and is yet to do. A huge multi-row INSERT (like INSERT <table> VALUES(<row 1 content>), (<row 2 content>), ..., (<row X content>); rather than INSERT <table> VALUES(<row 1 content>); INSERT <table> VALUES (<row 2 content>); ...) wont be processed until it is completely received so if you are running the script over a slow network link you see some progress as the data is sent then a pause while it is actually processed (so if the entire script is one long multi-row insert statement, the progress meter might head straight to 100%, or close to, and sit there for ages).

As another suggestion, you could add output statements around each part of the script like:

SELECT "About to create table some_table" AS action;
<create table code>
SELECT "Table some_table created" AS action;

and break bit INSERTs up with messages every now and then as well as messages at before and/or after:

SELECT "About to add rows to some_table" AS action, COUNT(*) AS current_rowcount FROM some_table;
<some insert statements, or a multi-row insert>
SELECT "adding rows to some_table" AS action, COUNT(*) AS current_rowcount FROM some_table;
<some more insert statements, or a multi-row insert>
SELECT "Done adding rows to some_table" AS action, COUNT(*) AS current_rowcount FROM some_table;

the result of these extra SELECT statements should be written to stdout (i.e. your console) as the script is processed so you will have a clue how far it has gone.

Answer 2

Have I got a crazy query for you !!!

This will report on the size of every InnoDB table.

SELECT CONCAT(db,'.',tb) table_name,CONCAT(ROUND(
tbsz/POWER(1024,IF(pw<0,0,IF(pw>4,4,pw))),3),' ',
SUBSTR(' KMGT',IF(pw<0,0,IF(pw>4,4,pw))+1,1)) table_size
FROM (SELECT data_length+index_length tbsz,table_schema db,table_name tb
FROM information_schema.tables WHERE engine='InnoDB') A,
(SELECT 3 pw) B;

• (SELECT 0 pw) give Bytes
• (SELECT 1 pw) give KB
• (SELECT 2 pw) give MB
• (SELECT 3 pw) give GB
• (SELECT 4 pw) give TB

Just run this periodically during the loading of the SQL script. If the SQL script table locks the table during the load, then you will not see numbers until the table is fully loaded.

Give it a Try !!!

Answer 3

I believe you should be able to do this with command line progress bar, e.g.:

$ cat db.sql | bar | mysql

Or alternatively catentate with progress, e.g.:

$ cwp db.sql |mysql