How to import a mysql database using cmd prompt in XAMPP for Windows

There are a lot of developers who claim that visual editors (like in this case to import the database phpmyadmin) are not as trustworthy as the command line. Besides it isn't fancy, however there are a lot of reasons why some people prefer the command line instead of an UI.

One of the most known reasons, in this case is the task of import a database, because as everybody knows there's a limitation with the filesize of a database with phpmyadmin (Max: 2,048KiB with the default configuration of xampp). This in turn, isn't a big limitation as you can increase the allowed filesize of the database however it will take time to configure it and it won't be so fast as with the command line (at less for people with a database of more than 100MB).

In this article you will learn how to import a mysql database with the command prompt in XAMPP for Windows.

1. Locate the mysql executable

The mysql executable is a simple SQL shell with input line editing capabilities. It supports interactive and noninteractive use. When used interactively, query results are presented in an ASCII-table format. When used noninteractively (for example, as a filter), the result is presented in tab-separated format. The output format can be changed using command options.

For our task, that is importing the database with the command line, this will be the required tool, therefore the first you need to do is to locate the executable of mysql in your xampp distribution. Open the xampp folder and search for the executable inside the mysql/bin folder, in this example the executable will be located in the E:\Programs\xampp\mysql\bin folder:

Copy the path in the clipboard as you will need it in the next step.

2. Import the database with the command prompt

Using mysql from the command line interface is very easy. For example, to import a database we just need to execute the following command with the following arguments:

mysql -u {DATABASE_USERNAME} -p {DATABASE_NAME} < "path/to/file/database-to-import.sql"

That with some real values, should look like (importing the ourcodeworld.sql database located in the desktop into the ourcodeworld database):

mysql -u root -p ourcodeworld < "C:\Users\sdkca\Desktop\ourcodeworld.sql"

After the execution of the commands with the required values, the console will prompt for the password (that you should let empty in case there isn't any and just press Enter) and will start importing the database. It seems pretty easy, however you may probably don't know from where you need to execute the previous command, isn't ? With windows there are 2 ways to do it:

A. With the entire executable path

In this way, you wont need to switch from path with the command prompt, therefore you can execute the command from wherever you are with the console (even with a new command prompt window). From the example command, replace mysql with the absolute path to the executable and you're ready to go:

E:\Programs\xampp\mysql\bin\mysql.exe -u root -p ourcodeworld < "C:\Users\sdkca\Desktop\ourcodeworld.sql"

In the command prompt, the execution of the previous steps will show as result:

B. Changing of directory

The other way is simple too, you only need to change from directory with the command prompt to the path where the mysql executable is located (step 1), in this case mysql/bin:

cd E:\Programs\xampp\mysql\bin

And then execute the command again (replace mysql with mysql.exe if the command isn't recognized):

mysql -u root -p ourcodeworld < "C:\Users\sdkca\Desktop\ourcodeworld.sql"

In the command prompt, the execution of the previous steps will show as result:

With any of the previous step, there should not be any kind of output if the database was succesfully imported, otherwise something went wrong.

Happy coding !