MySQL Database import into 5.7 hangs with CLI or with Workbench 6.3 CE

importMySQLmysql-5.7mysqldump

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?

Best Answer

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";

Thanks

Related Solutions

MySQL, import SQL dump with another database

By reading the official documentation, I'll say that your guess is right, the dump script will override the DB1 database.

MySQL Use db syntax

The USE db_name statement tells MySQL to use the db_name database as the default (current) database for subsequent statements. The database remains the default until the end of the session or another USE statement is issued.

The solution is the one proposed by Stuart, use a grep-like tool to find and replace "use db1" with "use db2" inside the dump file. Maybe even other references to db1 inside the dump script (procedures that use complete table names..). There are solutions for both Linux or Windows variants to edit that big text file.

MySQL – Troubleshooting Database Import Issues

I've see at least two problems: 1. fields list doesn't match values. 2. serialized data doesn't quoted properly.

May be you have try native mysqldump instead of "Backup wizard"?

mysqldump \
-u USERNAME -pPASSWORD \
--opt                  \
--no-create-db         \
--comments             \
--complete-insert      \
--order-by-primary     \
DATABASENAME  > ./dbdump.sql

Keep in mind that there is no space between -p and PASSWORD.

Best Answer

Related Solutions

MySQL, import SQL dump with another database

MySQL – Troubleshooting Database Import Issues

Related Question