Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
wow all 3 answers are gold, how do i pick? –  servermanfail Feb 18 '11 at 21:43

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer
    
Ah, pv, nice one, I'll check that out –  Gaius Feb 17 '11 at 12:50

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 !!!

share|improve this answer
    
This is one amazing query, and it works! Doesn't answer my question directly but I'm still very delighted with this answer. Any chance you could add a ROUND ( ) to the query because it uses exponents which is confusing to scan the list. –  servermanfail Feb 18 '11 at 21:44
    
I added the ROUND function as requested. I rounded it to 3 places. –  RolandoMySQLDBA Feb 20 '11 at 4:14

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.