Import mysql DB with XAMPP in command LINE

Question

I have this command:

mysql -u #myusername -p #mypasswd MYBASE < c:\user\folderss\myscript.sql

But I get the following error:

Error
unknow command '\U'
unknow command '\m'
unknow command '\D'
unknow command '\L'

Dukes

Answer 1

For those using a Windows OS, I was able to import a large mysqldump file into my local XAMPP installation using this command in cmd.exe:

C:\xampp\mysql\bin>mysql -u {DB_USER} -p {DB_NAME} < path/to/file/ab.sql

Also, I just wrote a more detailed answer to another question on MySQL imports, if this is what you're after.

Anemone

with the latest version of xampp i had to use mysql.exe – Fremont

I use the command above, substituting my own user and password and file name, and I get this output:mysql Ver 15.1 Distrib 10.1.16-MariaDB, for Win32 (AMD64) Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Usage: mysql [OPTIONS] [database] Default options are read from the following files in the given order: C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf C:\xampp\mysql\my.ini C: \xampp\mysql\my.cnf C:\xampp\mysql\bin\my.ini C:\xampp\mysql\bin\my.cnf The following groups are read: mysql client client-server client-mariadb – Onetime

Works. Just a clarification: this needs to be done in the windows cmd prompt. Didn't work for me in Git Bash prompt. – Tetrad

Answer 2

Go to the xampp shell command line and run

mysql -h localhost -u username databasename < dump.sql

Airs

Execute the command above from within C:\xampp\mysql\bin` and have the dump.sql` in the same folder. Tip for big imports that take a long while: During the import process you can refresh your phpmyadmin site and see if tables are created. – Treharne

Answer 3

You may also need to specify the host. From your statement, it looks like you run on Windows. Try this:

mysql -h localhost -u #myusername -p #mypasswd MYBASE
      < c:/user/folderss/myscript.sql

Or

mysql -h localhost -u #myusername -p #mypasswd MYBASE
       < "c:\user\folderss\myscript.sql"

Blanketyblank

Ok, tx. I have now access denied problem. I can access some DBs and not the one i created via phpmyadmin. I put all privileges for a user. – Dukes

am connected as odbc user how to swith to root ? – Dukes

You can't 'switch to root', once you're logged on. There's nothing like a 'su' or 'sudo' command as in Unix. You need to log on as root right away: - mysql -u root -h localhost ... – Blanketyblank

Answer 4

mysql -h localhost -u username databasename < dump.sql

It works, try it. If password is blank no need to add the -p args.

Involutional

Answer 5

/opt/lampp/bin/./mysql -u dbusername -p dbname < sqlfilepath.sql

Epiblast

Please only add new answers to a question. This answer has been posted many times in this thread and does not add anything to it. – Mayamayakovski

This is actually the first correct answer for Linux on the page! – Sternlight

A great answer for xampp who is installed in Linux OS. – Walke

Answer 6

It works:

mysql -u root -p db_name < "C:\folder_name\db_name.sql"

C:\ is for example

Berube

Answer 7

Copy Database Dump File at (Windows PC) D:\xampp\mysql\bin

mysql -h localhost -u root Databasename <@data11.sql

Backspin

Answer 8

this command posted by Daniel works like charm

C:\xampp\mysql\bin>mysql -u {DB_USER} -p {DB_NAME} < path/to/file/ab.sql

just put the db username and db name without those backets

**NOTE: Make sure your database file is reside inside the htdocs folder, else u'll get an Access denied error

Bettyebettzel

Answer 9

No Doubt It does the trick:

C:\xampp\mysql\bin>mysql -u {DB_USER} -p {DB_NAME} < path/to/file/ab.sql

But some time you many find "MySql Server has Gone a way" For that you need to change mysql/bin/my.ini max_allowed_packet = 64M to my.ini

Selwin

Answer 10

I think the commands are OK, issue is with the spaces. Please observe the spaces between -u and #myusername also between -p and #mypasswd.

mysql -u #myusername -p #mypasswd MYBASE < c:\user\folderss\myscript.sql

Can also be written as:

mysql -u#myusername -p#mypasswd MYBASE < c:\user\folderss\myscript.sql

Also, you need not add -p and the time of command, if your password is blank, it will not ask you for the password. If you have password to your database, it will ask for the password.

Thanks

Soulful

Answer 11

The process is simple-

1. Move the database to the desired folder e.g. /xampp/mysql folder.

2. Open cmd and navigate to the above location

3. Use command to import the db - "mysql -u root -p dbpassword newDbName < dbtoimport.sql"

4. Check for the tables for verification.

Thermoelectricity

Answer 12

In Windows, place the MySQL file (i.e. example.sql) in C:\xampp\mysql\bin.

Open the XAMPP Control Panel, and launch Shell:

cd c:\xampp\mysql\bin
mysql --default-character-set=utf8 -h localhost -u username databasename < example.sql

Note: Using --default-character-set=utf8 can help to prevent encoding issues with special characters if you intend on working with UTF-8.

Afterwards, remove the MySQL file from C:\xampp\mysql\bin.

Remontant

Answer 13

Do your trouble shooting in controlled steps:

(1) Does the script looks ok?

DOS E:\trials\SoTrials\SoDbTrials\MySQLScripts
type ansi.sql
show databases

(2) Can you connect to your database (even without specified the host)?

DOS E:\trials\SoTrials\SoDbTrials\MySQLScripts
mysql -u root -p mysql
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.0.51b-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

(3) Can you source the script? (Hoping for more/better error info)

mysql> source ansi.sql
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ...                |
| test               |
+--------------------+
7 rows in set (0.01 sec)
mysql> quit
Bye

(4) Why does it (still not) work?

DOS E:\trials\SoTrials\SoDbTrials\MySQLScripts
mysql -u root -p mysql < ansi.sql
Enter password: ********
Database
information_schema
...
test

I suspected that the encoding of the script could be the culprit, but I got syntax errors for UTF8 or UTF16 encoded files:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near '´╗┐
show databases' at line 1

This could be a version thing; so I think you should make sure of the encoding of your script.

Alec

Answer 14

I think the OP's problem was not escaping the slashes. on windows a path like: "c:\user\folderss\myscript.sql" should in the command line be written like either:

 c:\\\user\\\folders\\\myscript.sql

or:

c:/user/folders/myscript.sql

therefore "\u" from "c:\user" was interpreted as a command.

see: http://dev.mysql.com/doc/refman/5.5/en/mysql-commands.html for more info

Caecilian

Answer 15

here is my try to import database from cmd in xampp server the correct command for me is this you can change it with your requirements.and i also attached Errors that i face first time importing db from command line.you can see last command that i use and i paste it here also its working fro me.

mysql -h localhost -u root ovxsolut_windhs < C:\Users\eee\Downloads\ovxsolut_windhs.sql

Eruct

Answer 16

C:\xampp\mysql\bin>mysql -u {DB_USER} -p {DB_NAME} < path/to/file/filename.sql

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

Trichiasis

Answer 17

mysql -h localhost -u username -p databasename < dump.sql

Rattan