I have a SQL dump file that I exported using Workbench from a server that is being decommissioned. The host and target OS is Microsoft Windows Server 2008 R2 (therefore is x64 natively). There seems to be no way in hell to import this SQL dump file as it will hang no matter what. File size is less than 500Mb. I did look at the database schema itself and tables seem to have overwritten the initially created DB with the same name, however the import will not do anything but hang. I'm not a DBA, just a network/sysadmin so I am most frustrated with getting this thing to import. Any suggestions anyone?
I ran into similar problem importing a 1.2G SQL File and it took about 10 hours on my Laptop (Core i-5 x64 8G RAM) Of course I was multitasking.
To find out the progress, I'd query every hour using this query
select table_name as "Table", round(((data_length + index_length) / 1024 / 1024), 2) AS "SIZE" from information_schema.TABLES where table_schema = "Name_of_Database_You_Import";
Load it in using the mysql commandline took instead of Workbench.
I assume the actual problem is you don't know the progress percentage of import process. That's why it appears to be hanging while import is progressing in the background.
Following details were copied from this how-can-i-monitor-the-progress-of-an-import-of-a-large-sql-file accepted answer
If you're just importing from a dump file from the CLI on *nix, e.g.
mysql -uxxx -pxxx dbname < /sqlfile.sql
then first install pipe viewer on your OS then try something like this:
pv sqlfile.sql | mysql -uxxx -pxxxx dbname
which will show a progress bar as the program runs.
It's very useful and you can also use it to get an estimate for mysqldump progress.
pv dumps the
sqlfile.sql and passes them to mysql (because of the pipe operator). While it is dumping, it shows the progress. The cool thing is that mysql takes the data only as fast as it can progress it, so pv can show the progress of the import. I do not have any proof. But it seems so. I guess there is some buffer used, but at some point I think
mysql does not read any more data when it is still busy processing.