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.

Answers:

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.

---

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 ;

---

We can use this command to import SQL from 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
~/Desktop ? mysql -u root -p password bank < bank.sql

And if your 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:

~/Project ? mysql -u root -p password bank < ~/Desktop/bank.sql

---

A solution that worked for me is below:

Use your_database_name;
SOURCE path_to_db_sql_file_on_your_local;

---

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

---

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

---

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

---

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:

#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

---

Import a database

1. Go to drive:

   command: d:
   

2. MySQL login

   command: 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
   

---

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

---

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

---

Sometimes the port defined as well as the server IP address of that database also matters...

mysql -u user -p user -h <Server IP> -P<port> (DBNAME) < DB.sql 

---

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

---

Similarly to https://.com/a/17666285/1888983
Key differences for me:

1. The database has to exist first
2. 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

Running fedora 26 with MariaDB.

---

Import into database:-

mysql -u username -p database_name < /file path/file_name.sql

Export from database :-

mysqldump -u username -p database_name > /file path/file_name.sql

after these commands prompt will ask for your mysql password