CODEWITHSUNDEEP
mysqldatabaseimportcommand-linedump
Jaylen
Jaylen

Reputation: 40381

How to import an SQL file using the command line in MySQL?

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.

Upvotes: 2814

Views: 5321703

Answers (30)

Teodor Mihail
Teodor Mihail

Reputation: 906

Duplicate answer

Many of the aforementioned solutions are OS specific and require multiple steps. The best solution will be to use a one line command to create the database, select the database, and import the database, by using the command line parameter -e, which stands for execute, and it allows the user to execute application console related and database management system related commands.

[PATH_TO_MYSQL]\mysql -u YOUR_USER_ID -p -e 'CREATE DATABASE [DATABASE_NAME];USE [DATABASE_NAME];SOURCE [DATABASE_NAME_PATH];'

# USE '/' AS THE PATH SEPARATOR CHARACTER WITHIN THE SOURCE QUERY ON ALL OPERATING SYSTEMS

# (e.g.) 
mysql -u root -p -e "CREATE DATABASE Theta_FTP;USE Theta_FTP;SOURCE C:/Users/teodo/Desktop/Theta_FTP.sql;"

Upvotes: 0

Hashim Aziz
Hashim Aziz

Reputation: 6155

The fastest way

I've used the solution below to import a 100GB database (not a typo) of almost 300 million records in less than 10 hours, so it's safe to say it will rip through the average SQL dump in no time.

First, you should make sure your database is making the most of your hardware - most out-of-the-box SQL installations are set up to be as lightweight (and therefore inefficient) as possible. Assuming your database is using the InnoDB engine (which it is if you are using XAMPP), edit the following variables in your my.ini file:

innodb_buffer_pool_size=8G // Change to 50-75% of your system's RAM, depending on how much you need to keep free
innodb_log_file_size=2G // Change to 25% of the above value
innodb_log_buffer_size=8M

If you're using another database engine then you'll need to find the equivalent configuration options and tune them accordingly.

Once you've made those configuration changes, restart your server and run the following in the command-line:

mysql -u root -e "CREATE DATABASE IF NOT EXISTS <database_name>; USE <database_name>; \
SET FOREIGN_KEY_CHECKS = 0; SET UNIQUE_CHECKS = 0; SET AUTOCOMMIT = 0; \
source <file_name>; SET UNIQUE_CHECKS = 1; SET FOREIGN_KEY_CHECKS = 1; COMMIT;"

Although this uses the mysql CLI tool it's just executing plain MySQL so you can alternatively copy-paste the SQL code from it directly into your database tool's SQL tab (eg. PHPMyAdmin).

Upvotes: 3

Mostafa Mahmoud
Mostafa Mahmoud

Reputation: 302

  1. copy the database file to C:\xampp\mysql\bin

  2. open a terminal from the same path

  3. type in the terminal

     .\mysql.exe -uroot

  4. type use DB_NAME; // Change to your DB Name

  5. type source DB_FILE.sql;

Upvotes: -1

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

For example, you export the schema and data or only the schema of the tables of apple database to backup.sql with mysqldump as shown below. *My answer explains how to export the schema and data of the tables of a database:

mysqldump -u john -p apple > backup.sql

Or:

mysqldump -u john -p -d apple > backup.sql

Or, you export only the data of the specific tables person and animal of apple database to backup.sql with INSERT statement which has column names as shown below. *By default, INSERT statement doesn't have column names and my answer explains how to export only data more:

mysqldump -u john -p -t -c apple person animal > backup.sql

Then, you need to input a password after running the command above:

Enter password:

Now, you can import backup.sql into orange database with MySQL as shown below. *When importing the schema and data or only the schema, selected orange database must exist and when importing only the data, selected orange database and the tables must exist otherwise there is error and when importing only the data, you need to delete all the data from apple database otherwise there will be error. The documentation explains how to import databases and my answer explains how to create a database:

mysql -u john -p orange < backup.sql

Or:

mysql -u john -p -D orange < backup.sql

Or:

mysql -u john -p --database orange < backup.sql

Then, you need to input a password after running the command above:

Enter password:

Or, after login, you can import backup.sql into orange database with \. or source selecting orange database as shown below:

mysql -u john -p
...
mysql> USE orange;
mysql> \. backup.sql

Or:

mysql -u john -p
...
mysql> USE orange;
mysql> source backup.sql

Be careful, you cannot import backup.sql into orange database not selecting orange database as shown below:

So, this below gets error:

mysql -u john -p < backup.sql

ERROR 1046 (3D000) at line 22: No database selected

And, these below get error:

mysql -u john -p
...
mysql> \. backup.sql

Or:

mysql -u john -p
...
mysql> source backup.sql

ERROR 1046 (3D000): No database selected

In addition, you can import backup.sql into orange database without a password prompt by setting a password(e.g., banana) to -p(--password=) as shown below. *Don't put any space just after -p(--password=) because there is error and my answer explains how to import a database without a password prompt in detail:

mysql -u john -pbanana orange < backup.sql

Upvotes: 10

Pritam Chaudhari
Pritam Chaudhari

Reputation: 819

USE yourdb; // Database name  

SOURCE D:/your folder path/Folder/filetoimport.sql;

Upvotes: 0

UTHAYAKUMAR M
UTHAYAKUMAR M

Reputation: 51

mysql -u myuser -p mydatabase < mydata.sql

Replace myuser with your MySQL username, mydatabase with the name of your MySQL database, and mydata.sql with the name of your SQL file.
Make sure that the SQL file is properly formatted and does not contain any syntax errors that could cause issues during import.

Upvotes: 4

Shrm
Shrm

Reputation: 453

Most of the answers include > or < characters which is not a proper method for all the cases. I recommend using mysqlimport while you may make the dump file using mysqldump.

These tools will be installed with the mysql service and both are available for backup and restore in a database or multiple databases in MySQL.

Here is the way you could leverage it for importing to the mysql

mysqlimport -u database_admin -p database_name ~/path/to/dump_file.sql

In case you do not have it, please install it via:

sudo apt update sudo apt install mysql-client

In the same way, you make a backup to a dump file as follows:

mysqldump [options] --result-file=dump_file.sql

Upvotes: 0

bansi
bansi

Reputation: 57052

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 with mysqldump to keep the routines and triggers of the 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 doesn't contain CREATE DATABASE (exported with --no-create-db or -n option) before you can import it.

Upvotes: 5007

David
David

Reputation: 6084

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

Upvotes: 51

David Silva Smith
David Silva Smith

Reputation: 11716

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

Upvotes: 23

Musa
Musa

Reputation: 2662

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

Upvotes: 1

Chanuka
Chanuka

Reputation: 167

You can use these steps as easily.

  1. Download the SQL file into your "mysql/bin" folder.

  2. Open the "mysql/bin" folder using CMD.

  3. If not exists required database, then create the database first.

  4. 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

  5. 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" as a single word without any spaces.

Upvotes: 0

Madhusanka Edirimanna
Madhusanka Edirimanna

Reputation: 1428

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

Upvotes: 41

BugsCreator
BugsCreator

Reputation: 519

Simple. Just use this command in cmd:

use databasename
\. C:/test/data.mysql

Upvotes: 3

nafischonchol
nafischonchol

Reputation: 88

  • 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

Upvotes: 2

Abid Shah
Abid Shah

Reputation: 475

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

Upvotes: 0

GabrieleMartini
GabrieleMartini

Reputation: 1721

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).

Upvotes: 5

Naveen Gaur
Naveen Gaur

Reputation: 461

  1. Go to your wamp or xampp directory

    Example

    cd d:/wamp/bin/mysql/mysql5.7.24/bin

  2. mysql -u root -p DATABASENAME < PATHYOUDATABASE_FILE

Upvotes: -4

Amarat
Amarat

Reputation: 630

Try this:

cd C:\xampp\mysql\bin
mysql -u root -p database_name --force < C:\file.sql

Upvotes: -3

Abdul Rehman Janjua
Abdul Rehman Janjua

Reputation: 1571

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

Upvotes: 20

Pritam Chaudhari
Pritam Chaudhari

Reputation: 819

Import a database

  1. Go to drive:

     d:

  2. MySQL login

     c:\xampp\mysql\bin\mysql -u root -p

  3. It will ask for pwd. Enter it:

     pwd

  4. Select the database

     use DbName;

  5. Provide the file name

     \.DbName.sql

Upvotes: 17

user3546602
user3546602

Reputation: 241

mysql --user=[user] --password=[password] [database] < news_ml_all.sql

Upvotes: 24

Paresh Behede
Paresh Behede

Reputation: 6295

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 ;

Upvotes: 517

Amrit Dhungana
Amrit Dhungana

Reputation: 4485

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

Upvotes: 98

vladkras
vladkras

Reputation: 17227

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

PowerShell

cmd.exe /c "mysql -u root -p db_name < backup-file.sql"

MySQL command line

mysql> use db_name;
mysql> source backup-file.sql;

Upvotes: 1072

JohnSharath
JohnSharath

Reputation: 101

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

Upvotes: 8

Mahdi Bashirpour
Mahdi Bashirpour

Reputation: 18833

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

Upvotes: 3

skoll
skoll

Reputation: 2452

Using MySQL Secure Shell:

mysqlsh -u <username> -p -h <host> -D <database name> -f dump.sql

Upvotes: 4

user777388
user777388

Reputation: 311

For exporting a database:

mysqldump -u username -p database_name > file.sql

For importing a database:

mysql -u username -p database_name < file.sql

Upvotes: 20

Paul Leclerc
Paul Leclerc

Reputation: 1137

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.

Upvotes: 7

Related Questions