How do I import an SQL file using the command line in MySQL?
MysqlSqlCommand LineImportMysql Problem Overview
I have a .sql
file with an export from phpMyAdmin
. I want to import it into a different server using the command line.
I have a Windows Server 2008 R2 installation. I placed the .sql
file on the C drive, and I tried this command
database_name < file.sql
It is not working. I get syntax errors.
- How can I import this file without a problem?
- Do I need to create a database first?
Mysql Solutions
Solution 1 - Mysql
Try:
mysql -u username -p database_name < file.sql
Check MySQL Options.
Note 1: It is better to use the full path of the SQL file file.sql
.
Note 2: Use -R
and --triggers
to keep the routines and triggers of original database. They are not copied by default.
Note 3 You may have to create the (empty) database from MySQL if it doesn't exist already and the exported SQL don't contain CREATE DATABASE
(exported with --no-create-db
or -n
option), before you can import it.
Solution 2 - Mysql
A common use of mysqldump is for making a backup of an entire database:
mysqldump db_name > backup-file.sql
You can load the dump file back into the server like this:
Unix
mysql db_name < backup-file.sql
The same in the Windows command prompt:
mysql -p -u [user] [database] < backup-file.sql
cmd.exe /c "mysql -u root -p db_name < backup-file.sql"
MySQL command line
mysql> use db_name;
mysql> source backup-file.sql;
Solution 3 - Mysql
Regarding the time taken for importing huge files: most importantly, it takes more time because the default setting of MySQL is autocommit = true
. You must set that off before importing your file and then check how import works like a gem.
You just need to do the following thing:
mysql> use db_name;
mysql> SET autocommit=0 ; source the_sql_file.sql ; COMMIT ;
Solution 4 - Mysql
Among all the answers, for the problem above, this is the best one:
mysql> use db_name;
mysql> source file_name.sql;
Solution 5 - Mysql
Easiest way to import into your schema:
Login to mysql and issue below mention commands.
mysql> use your_db_name;
mysql> source /opt/file.sql;
Solution 6 - Mysql
We can use this command to import SQL from the command line:
mysql -u username -p password db_name < file.sql
For example, if the username is root
and password is password
. And you have a database name as bank
and the SQL file is bank.sql
. Then, simply do like this:
mysql -u root -p password bank < bank.sql
Remember where your SQL file is. If your SQL file is in the Desktop
folder/directory then go the desktop directory and enter the command like this:
cd ~/Desktop
mysql -u root -p password bank < bank.sql
And if you are in the Project
directory and your SQL file is in the Desktop
directory. If you want to access it from the Project
directory then you can do like this:
cd ~/Project
mysql -u root -p password bank < ~/Desktop/bank.sql
Solution 7 - Mysql
If you already have the database, use the following to import the dump
or the sql
file:
mysql -u username -p database_name < file.sql
if you don't you need to create the relevant database(empty) in MySQL, for that first log on to the MySQL
console by running the following command in terminal or in cmd
mysql -u userName -p;
And when prompted provide the password.
Next, create a database and use it:
mysql>create database yourDatabaseName;
mysql>use yourDatabaseName;
Then import the sql
or the dump
file to the database from
mysql> source pathToYourSQLFile;
Note: if your terminal is not in the location where the dump
or sql
file exists, use the relative path in above.
Solution 8 - Mysql
- Open the MySQL command line
- Type the path of your mysql bin directory and press Enter
- Paste your SQL file inside the
bin
folder of mysql server. - Create a database in MySQL.
- Use that particular database where you want to import the SQL file.
- Type
source databasefilename.sql
and Enter - Your SQL file upload successfully.
Solution 9 - Mysql
A solution that worked for me is below:
Use your_database_name;
SOURCE path_to_db_sql_file_on_your_local;
Solution 10 - Mysql
To dump a database into an SQL file use the following command.
mysqldump -u username -p database_name > database_name.sql
To import an SQL file into a database (make sure you are in the same directory as the SQL file or supply the full path to the file), do:
mysql -u username -p database_name < database_name.sql
Solution 11 - Mysql
I think it's worth mentioning that you can also load a gzipped (compressed) file with zcat
like shown below:
zcat database_file.sql.gz | mysql -u username -p -h localhost database_name
Solution 12 - Mysql
Go to the directory where you have the MySQL executable. -u
for username and -p
to prompt for the password:
C:\xampp\mysql\bin>mysql -u username -ppassword databasename < C:\file.sql
Solution 13 - Mysql
To import a single database, use the following command.
mysql -u username -p password dbname < dump.sql
To import multiple database dumps, use the following command.
mysql -u username -p password < dump.sql
Solution 14 - Mysql
To import a database, use the following command.
mysql> create new_database;
mysql> use new_database;
mysql> source (Here you need to import the path of the SQL file);
E.g.:
mysql> source E:/test/dump.sql;
You need to use forward slashes (/) even on Windows, e.g., E:/test/dump.sql instead of E:\test\dump.sql
Or double backslashes (\\) because of escaping, i.e., E:\\test\\dump.sql
Solution 15 - Mysql
While most answers here just mention the simple command
> mysql -u database_user -p [db_name] < database_file.sql
today it's quite common that databases and tables have utf8-collation where this command is not sufficient.
Having utf8-collation in the exported tables it's required to use this command:
mysql -u database_user -p --default-character-set=utf8 [db_name] < database_file.sql
An according export can be done with
mysqldump -u database_user -p --default-character-set=utf8 [db_name] > database_file.sql
Surely this works for other charsets too, how to show the right notation can be seen here: > https://dev.mysql.com/doc/refman/5.7/en/show-collation.html
One comment mentioned also that if a database never exists an empty database had to be created first. This might be right in some cases but depends on the export file. If the exported file includes already the command to create the database then the database never has to be created in a separate step, which even could cause an error on import. So on import, it's advisable to have a look first in the file to know which commands are included there, on export, it's advisable to note the settings, especially if the file is very large and hard to read in an editor.
There are still more parameters for the command which are listed and explained here: > https://dev.mysql.com/doc/refman/5.7/en/mysql-command-options.html
If you use another database version consider searching for the corresponding version of the manual too. The mentioned links refer to MySQL version 5.7.
EDIT:
The same parameters are working for mysqldump
too. So while the commands for export and import are different, the mentioned parameters are not.
Nevertheless there exists a special site in the manual that describes the options for mysqldump
: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
Solution 16 - Mysql
mysql --user=[user] --password=[password] [database] < news_ml_all.sql
Solution 17 - Mysql
For exporting a database:
mysqldump -u username -p database_name > file.sql
For importing a database:
mysql -u username -p database_name < file.sql
Solution 18 - Mysql
For importing multiple SQL files at one time, use this:
# Unix-based solution
for i in *.sql ; do mysql -u root -pPassword DataBase < $i ; done
For simple importing:
# Unix-based solution
mysql -u root -pPassword DataBase < data.sql
For WAMP:
REM mysqlVersion - replace with your own version
C:\wamp\bin\mysql\mysqlVersion\bin\mysql.exe -u root -pPassword DataBase < data.sql
For XAMPP:
C:\xampp\mysql\bin\mysql -u root -pPassword DataBase < data.sql
Solution 19 - Mysql
You do not need to specify the name of the database on the command line if the .sql file contains CREATE DATABASE IF NOT EXISTS db_name
and USE db_name
statements.
Just make sure you are connecting with a user that has the permissions to create the database, if the database mentioned in the .sql file does not exist.
Solution 20 - Mysql
Import a database
-
Go to drive:
d:
-
MySQL login
c:\xampp\mysql\bin\mysql -u root -p
-
It will ask for pwd. Enter it:
pwd
-
Select the database
use DbName;
-
Provide the file name
\.DbName.sql
Solution 21 - Mysql
I kept running into the problem where the database wasn't created.
I fixed it like this:
mysql -u root -e "CREATE DATABASE db_name"
mysql db_name --force < import_script.sql
Solution 22 - Mysql
Use:
mysql -u root -p password -D database_name << import.sql
Use the MySQL help for details - mysql --help
.
I think these will be useful options in our context:
[~]$ mysql --help
mysql Ver 14.14 Distrib 5.7.20, for osx10.12 (x86_64) using EditLine wrapper
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Usage: mysql [OPTIONS] [database]
-?, --help Display this help and exit.
-I, --help Synonym for -?
--bind-address=name IP address to bind to.
-D, --database=name Database to use.
--delimiter=name Delimiter to be used.
--default-character-set=name Set the default character set.
-f, --force Continue even if we get an SQL error.
-p, --password[=name] Password to use when connecting to server.
-h, --host=name Connect to host.
-P, --port=# Port number to use for connection or 0 for default to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306).
--protocol=name The protocol to use for connection (tcp, socket, pipe,
-s, --silent Be more silent. Print results with a tab as separator, each row on new line.
-v, --verbose Write more. (-v -v -v gives the table output format).
-V, --version Output version information and exit.
-w, --wait Wait and retry if connection is down.
What is fun, if we are importing a large database and not having a progress bar. Use Pipe Viewer and see the data transfer through the pipe
For Mac, brew install pv
For Debian/Ubuntu, apt-get install pv
.
For others, refer to pv - Pipe Viewer
pv import.sql | mysql -u root -p password -D database_name
1.45GiB 1:50:07 [339.0KiB/s] [=============> ] 14% ETA 11:09:36
1.46GiB 1:50:14 [ 246KiB/s] [=============> ] 14% ETA 11:09:15
1.47GiB 1:53:00 [ 385KiB/s] [=============> ] 14% ETA 11:05:36
Solution 23 - Mysql
Go to the directory where you have MySQL.
c:\mysql\bin\> mysql -u username -p password database_name <
filename.sql
Also to dump all databases, use the -all-databases
option, and no databases’ name needs to be specified anymore.
mysqldump -u username -ppassword –all-databases > dump.sql
Or you can use some GUI clients like SQLyog to do this.
Solution 24 - Mysql
Try this code.
mysql -u username -p database_name < file.sql
For more information: How to import an SQL file using the command line in MySQL
Solution 25 - Mysql
You can try this query.
Export:
mysqldump -u username –-password=your_password database_name > file.sql
Import:
mysql -u username –-password=your_password database_name < file.sql
and detail following this link:
Solution 26 - Mysql
Add the --force
option:
mysql -u username -p database_name --force < file.sql
Solution 27 - Mysql
The following command works for me from the command line (cmd) on Windows 7 on WAMP.
d:/wamp/bin/mysql/mysql5.6.17/bin/mysql.exe -u root -p db_name < database.sql
Solution 28 - Mysql
Providing credentials on the command line is not a good idea. The above answers are great, but neglect to mention
mysql --defaults-extra-file=etc/myhost.cnf database_name < file.sql
Where etc/myhost.cnf is a file that contains host, user, password, and you avoid exposing the password on the command line. Here is a sample,
[client]
host=hostname.domainname
user=dbusername
password=dbpassword
Solution 29 - Mysql
Similarly to vladkras's answer to How do import an SQL file using the command line in MySQL?.
Key differences for me:
- The database has to exist first
- No space between
-p
and the password
shell> mysql -u root -ppassword #note: no space between -p and password
mysql> CREATE DATABASE databasename;
mysql> using databasename;
mysql> source /path/to/backup.sql
I am running Fedora 26 with MariaDB.
Solution 30 - Mysql
Import into the database:
> mysql -u username -p database_name < /file path/file_name.sql
Export from the database:
> mysqldump -u username -p database_name > /file path/file_name.sql
After these commands, a prompt will ask for your MySQL password.
Solution 31 - Mysql
Sometimes the port defined as well as the server IP address of that database also matters...
mysql -u user -p user -h <Server IP address> -P<port> (DBNAME) < DB.sql
Solution 32 - Mysql
I thought it could be useful for those who are using Mac OS X:
/Applications/xampp/xamppfiles/bin/mysql -u root -p database < database.sql
Replace xampp
with mamp
or other web servers.
Solution 33 - Mysql
For information, I just had the default root + without password. It didn't work with all previous answers.
-
I created a new user with all privileges and a password. It worked.
-
-ppassword WITHOUT SPACE.
Solution 34 - Mysql
For backup purposes, make a BAT file and run this BAT file using Task Scheduler. It will take a backup of the database; just copy the following line and paste in Notepad and then save the .bat file, and run it on your system.
@echo off
for /f "tokens=1" %%i in ('date /t') do set DATE_DOW=%%i
for /f "tokens=2" %%i in ('date /t') do set DATE_DAY=%%i
for /f %%i in ('echo %date_day:/=-%') do set DATE_DAY=%%i
for /f %%i in ('time /t') do set DATE_TIME=%%i
for /f %%i in ('echo %date_time::=-%') do set DATE_TIME=%%i
"C:\Program Files\MySQL\mysql server 5.5\bin\mysqldump" -u username -ppassword mysql>C:/%DATE_DAY%_%DATE_TIME%_database.sql
Solution 35 - Mysql
I'm using Windows 10 with PowerShell 5 and I found almost all "Unix-like" solutions not working for me.
> mysql -u[username] [database-name] < my-database.sql
At line:1 char:31
+ mysql -u[username] [database-name] < my-database.sql
+ ~
The '<' operator is reserved for future use.
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : RedirectionNotSupported
I ends up using this command:
> type my-database.sql | mysql -u[username] -h[localhost] -p [database-name]
And it works perfectly, and hopefully it helps.
Thanks to @Francesco Casula's answer, BTW.
Solution 36 - Mysql
The following steps help to upload file.sql
to the MySQL database.
Step 1: Upload file.sql.zip
to any directory and unzip there
Note: sudo apt-get install unzip
: sudo apt-get unzip file.sql.zip
Step 2: Now navigate to that directory. Example: cd /var/www/html
Step 3: mysql -u username -p database-name < file.sql
Enter the password and wait till uploading is completed.
Solution 37 - Mysql
If importing data into a Docker container use the following command. Adjust user(-u), database(-D), port(-P) and host(-h) to fit your configuration.
mysql -u root -D database_name -P 4406 -h localhost --protocol=tcp -p < sample_dump.sql
Solution 38 - Mysql
To import a database via the terminal
Navigate to folder where the .sql file is located
Then run the below command:
mysql -u database_user_name -p database_name < sql_file_name.sql
It will ask for a password. Enter the database password. It will take a few seconds to import the data into the database.
Solution 39 - Mysql
This line imports the dump file in the local database, under Linux.
mysql -u dbuser -p'password including spaces' dbname < path/to/dump_file.sql
This line imports the dump file in the remote database, under Linux. Note: -P
is for the port and is required if the MySQL port is different than the default.
mysql -h dbhost -u dbuser -p'password including spaces' -P 3306 dbname < path/to/dump_file.sql
Note: the password includes spaces and this is the reason of the single quotes. Just change the path style for using the command under Windows (C:\windows\path\dump_file.sql
).
Solution 40 - Mysql
Using MySQL Secure Shell:
mysqlsh -u <username> -p -h <host> -D <database name> -f dump.sql
Solution 41 - Mysql
Simple. Just use this command in cmd:
use databasename
\. C:/test/data.mysql
Solution 42 - Mysql
If you use XAMPP on the windows, first, you must manually create the database and then run the following commands:
cd C:\xampp\mysql\bin &&
mysql -u YOUR_USERNAME -p YOUR_DATABASE_NAME < PATH_TO_YOUR_SQL_FILE\YOUR_SQL_FILE.sql
And then enter the password
Solution 43 - Mysql
For Windows OS, you can use the below command to import data from an SQL dump.
> C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -u<
Where -u
is the username, and -p
is the MySQL password. Then enter your password and wait for data to import.
Solution 44 - Mysql
You can use:
mysql -u<user> -p<pass> <db> < db.sql
Example:
mysql -uroot -proot db < db.sql
Solution 45 - Mysql
If you are using MAMP on Mac OS X, this may be helpful:
/applications/MAMP/library/bin/mysql -u MYSQL_USER -p DATABASE_NAME < path/to/database_sql/FILE.sql
MYSQL_USER is root by default.
Solution 46 - Mysql
If your folder has multiple SQL files, and you've installed Git Bash you can use this command to import multiple files:
cd /my-project/data
cat *.sql | /c/xampp/mysql/bin/mysql -u root -p 1234 myProjectDbName
Solution 47 - Mysql
Export particular databases:
mysqldump --user=root --host=localhost --port=3306 --password=test -B CCR KIT > ccr_kit_local.sql
This will export CCR and KIT databases...
Import all exported databases to a particular MySQL instance (you have to be where your dump file is):
mysql --user=root --host=localhost --port=3306 --password=test < ccr_kit_local.sql
Solution 48 - Mysql
If you are importing to your local database server, you can do the following:
mysql -u database_user -p < database_file.sql
For a remote database server do the follwing:
mysql -u database_user -p -h remote_server_url < database_file.sql
Solution 49 - Mysql
In Ubuntu
mysql -u root -p
CREATE database dbname;
use dbname;
source /home/computername/Downloads/merchantapp.sql
exit;
In Windows
Download the SQL file and save it in C:\xampp\mysql\bin
.
After that, open the command prompt with C:\xampp\mysql\bin
:
C:\xampp\mysql\bin> mysql -u username -p database_name < file.sql
Solution 50 - Mysql
-
Create a database in MySQL.
-
Then go to your computer directory C:\xampp\mysql\bin, write
cmd
in the address bar, and hit Enter. -
Unzip your SQL file
-
Then write:
mysql -u root -p dbname
and press Enter. -
Write: source sql.file. Like
Source C:\xampp\htdocs\amarbazarltd\st1159.sql
-
Done
Solution 51 - Mysql
If you are using XAMPP then go to folder xappp
→ mysql
→ bin
. Open cmd here and paste this:
mysql -u root -p dbname < dbfilename.sql
Solution 52 - Mysql
You can use these steps as easily.
-
Download the SQL file into your "mysql/bin" folder.
-
Open the "mysql/bin" folder using CMD.
-
If not exists required database, then create the database first.
-
Type this in the CMD and run:
mysql -u <user> -p<password> <dbname> < file.sql
"file.sql" is an SQL file that you want to insert into the target database. examples: If your "password" is "1234", "user" is "root", and "dbname" is "test":
mysql -u root -p1234 test < file.sql
If your "password" is null & "user" is "root" & "dbname" is "test"
mysql -u root test < file.sql
-
Check the target data successfully uploaded or not.
This method can be used to upload the large size data using SQL files in the CMD.
Make sure in step 4, if you use that password, insert "-p
Solution 53 - Mysql
Try this:
cd C:\xampp\mysql\bin
mysql -u root -p database_name --force < C:\file.sql
Solution 54 - Mysql
-
Go to your
wamp
orxampp
directoryExample
cd d:/wamp/bin/mysql/mysql5.7.24/bin
-
mysql -u root -p DATABASENAME < PATHYOUDATABASE_FILE