Importing a SQL Dump File through XAMPP's Command Line

Question

A few days ago, I exported a .SQL dump file with all my databases, which is about 150MB. The thing is I cannot import it through phpMyAdmin since the file is way to big.

I remembered there was BigDump and I tried to use it, since it's ideal for such big imports as mine. The problem is that I need to set a database to import the dump file on, which is something I cannot do, since the dump file contains some databases I dumped, meaning that BigDump needs a database to be configured with so it can import the file.

I've been searching around and found out that one option could be using XAMPP's mysql.exe command line, which I already tried to, but since the import command requires a database to make the import, it makes it impossible.

Do you have any another idea or know how to make an import without the need to provide a database?

Regards and thanks in advance!

Answer 1

Depending on the tool and parameters used to create the .sql dump file of multiple databases, the file will normally have

CREATE DATABASE DBn...;

and

USE DBn;

statements that will allow your import to proceed without hiccups. For example, both the mysqldump command mysqldump command and phpMyAdmin's Export function for multiple database insert them.

Assuming you have exported with a sensible tool like those above, then you can import the database with a command line like this:

mysql -u username -p < dumpfile.sql

Your mysql username account needs to have appropriate privileges to, for example, create databases.

You can even run this command in a more familiar way by naming the startingDB database to use before running the commands in dumpfile.sql:

mysql -u username -p startingDB < dumpfile.sql

As the dumpfile will have a CREATE DATABASE and USE before any CREATE TABLE or INSERT statements, everything will work fine.

Answer 2

Alternative way:

Open XAMPP, click on the "Shell" button on the right.

The shell start with command prompt, now enter:

mysql -u root -p <DB_NAME> < <your-file-location 

Example:

mysql -u root -p -v db_dummy < "D:\backups\hugedb.sql"

If you are asked for Password: just hit enter.

The -v will output the entire import processes to your screen. Remove it if you don't need it.