LOAD DATA INFILE +进度跟踪

Question

I made a PHP console script that downloads big CSV file (> 2.5m rows) and insterts it to the database. I'm using LOAD DATA INFILE query here and it works perfectly, taking ~20 seconds to complete.

I'd like to track progress of this query. I've read about several approaches here, but I don't know how to run LOAD DATA INFILE query and then run progress tracking loop. The script waits until LOAD DATA INFILE is done.

My database is MySQL and the engine is InnoDB. I'm using Laravel framework.

Answer 1

You can "split" the dump file with pt-fifo-split and print progress message after each chunk.

For example, in shell:

f=dump.sql
nlines=`cat $f | wc -l`
let chunk=$nlines/100

pt-fifo-split --lines $chunk $f
i=0
while [ -e /tmp/pt-fifo-split ] 
do 
    echo "$i% is done"
    mysql -e "LOAD DATA INFILE '/tmp/pt-fifo-split' INTO ..."
    let i=$i+1
done