How can I monitor the import progress of a large CSV File in mysql?

Question

I am trying to import an existing CSV file into my database and I want to see the progress. I usually use pipe viewer when I do my sql files but for csv files I cannot figure out how to do it.

I use macOX 10.15 and mysql 8.02 (not home-brew).

My idea was:

pv users.csv | mysql -u user -p -db < File/to/users.csv

Without using PV I just connect to my mysql change to my db and use this command:

LOAD DATA INFILE '/File/to/users.csv' INTO TABLE users FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n';

but I do not the see process.

Answer 1

Are you looking for something like this? https://dev.mysql.com/doc/refman/5.7/en/event-scheduler.html

    create table watcher_tbl(time datetime, num_rows int);

    create event watch_event
    on schedule every 2 second
    do
    insert into watcher_tbl(time, num_rows) values (current_timestamp,
                                                    (select count(*) from watched_table));


    -- to see the progress.
    select * from watcher_tbl;

    -- to stop the event after loading is done.
    drop event if exists watch_event;