How can I import a database with MySQL from terminal?

Question

How can I import a database with mysql from terminal?

I cannot find the exact syntax.

Revareval

Answer 1

Assuming you're on a Linux or Windows console:

Prompt for password:

mysql -u <username> -p <databasename> < <filename.sql>

Enter password directly (not secure):

mysql -u <username> -p<PlainPassword> <databasename> < <filename.sql>

Example:

mysql -u root -p wp_users < wp_users.sql

mysql -u root -pPassword123 wp_users < wp_users.sql

See also:

4.5.1.5. Executing SQL Statements from a Text File

---

Note: If you are on windows then you will have to cd (change directory) to your MySQL/bin directory inside the CMD before executing the command.

Exobiology

+1 except for you shouldn't pass your password as CLI parameter. Rather just specify -p option and enter it manually after prompt. – Belldame

There is no space between -p and the password if you want to enter it by shell. – Vanvanadate

mysql -u username -h hostname -ppassword databasename < filename.sql for remote host from the terminal – Meshach

if you are already inside mysql command prompt (mysql>), then simply type source full_path_of_file (note: select database by command USE DATABASE_NAME before import). – Segno

@Pekka웃, If we use source, we can see the informational messages ("rows affected") and error messages. But using your solution, How do we see the messages using < filename.sql ? – Levator

Any ideas why the one liner mysql -u username -p databasename < filename.sqlis painly slow compared to source filename.sql? (using XAMPP on Windows) – Implacable

too much mambo, jambo. All hail source. – Pie

add full path to your file /var/www/... whatever, or cd into it, otherwise it won't be found. – Phonetics

how to verbose because my sql doesnt on import , but the process has alr done . – Analeptic

Answer 2

Preferable way for windows:

1. Open the console and start the interactive MySQL mode

2. use <name_of_your_database>;

3. source <path_of_your_.sql>

Blowup

why don't we need a ; after file name? – Pacifist

@NabeelKhan Most SQL statements require a ; at the end of the statement, but there are a few exceptions, including USE and SOURCE. – Resource

I wonder if this performs faster than piping the file in (like in the accepted answer). My suspicion is yes. – Resource

This is so much faster than importing through HeidiSQL...3GB in 20 minutes! – Ambie

there's no need to use "use <name_of_your_database>", right? dev.mysql.com/doc/refman/5.7/en/mysql-batch-commands.html – Housecoat

@Martian2049, When I omited USE <database>, it didn't import the database (Mysql version 14.14) – Whensoever

@Housecoat You need to tell the mysql utility which database to use (=import into). Either you specify the database name on the command line when running mysql -u <user> -p <database>, or in the console with the USE <database> command. If you do neither, MySQL doesn't know which DB to target. – Hanks

Answer 3

mysql -u <USERNAME> -p <DB NAME> < <dump file path>

-u - for Username

-p - to prompt the Password

Eg. mysql -u root -p mydb < /home/db_backup.sql

You can also provide password preceded by -p but for the security reasons it is not suggestible. The password will appear on the command itself rather masked.

Heidy

This is the answer i am looking for. " < " this is the sign which I left. – Homesteader

If mydb does not exists then how to create it dynamically when we fire mysqldump command? – Crat

Answer 4

Directly from var/www/html

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

From within mysql:

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

Morbidezza

Answer 5

Open Terminal Then

 mysql -u root -p

 eg:- mysql -u shabeer -p

After That Create a Database

 mysql> create database "Name";

 eg:- create database INVESTOR;

Then Select That New Database "INVESTOR"

 mysql> USE INVESTOR;

Select the path of sql file from machine

 mysql> source /home/shabeer/Desktop/new_file.sql;

Then press enter and wait for some times if it's all executed then

 mysql> exit

Seta

Answer 6

Below command is working on ubuntu 16.04, I am not sure it is working or not other Linux platforms.

Export SQL file:

$ mysqldump -u [user_name] -p [database_name] > [database_name.sql]  

Example : mysqldump -u root -p max_development > max_development.sql

Import SQL file:

$ mysql -u[user_name] -p [database_name] < [file_name.sql]

Example: mysqldump -u root -p max_production < max_development.sql

Note SQL file should exist same directory

Graycegrayheaded

I think your Import command is wrong. Importing is done with "mysql" cmd. "mysqldump" is for export. – Synsepalous

Answer 7

From Terminal:

mysql -uroot -p --default-character-set=utf8 database_name </database_path/database.sql

Macy

Answer 8

in the terminal type

mysql -uroot -p1234; use databasename; source /path/filename.sql

Lapin

This allowed to see the import log – Bulla

Answer 9

I usually use this command to load my SQL data when divided in files with names : 000-tableA.sql, 001-tableB.sql, 002-tableC.sql.

for anyvar in *.sql; do <path to your bin>/mysql -u<username> -p<password>  <database name> < $anyvar; done

Works well on OSX shell.

Buffer

Answer 10

Explanation:

1. First create a database or use an existing database. In my case, I am using an existing database

2. Load the database by giving <name of database> = ClassicModels in my case and using the operator < give the path to the database = sakila-data.sql

3. By running show tables, I get the list of tables as you can see.

Note : In my case I got an error 1062, because I am trying to load the same thing again.

Engedus

Answer 11

mysql -u username -ppassword dbname < /path/file-name.sql

example

mysql -u root -proot product < /home/myPC/Downloads/tbl_product.sql

Use this from terminal

Reject

Answer 12

After struggling for sometime I found the information in https://tommcfarlin.com/importing-a-large-database/

1. Connect to Mysql (let's use root for both username and password):

   mysql -uroot -proot
   

2. Connect to the database (let's say it is called emptyDatabase (your should get a confirmation message):

   connect emptyDatabase
   

3 Import the source code, lets say the file is called mySource.sql and it is in a folder called mySoureDb under the profile of a user called myUser:

source /Users/myUser/mySourceDB/mySource.sql

Socialism

Answer 13

1. Open the MySQL Command Line Client and type in your password

2. Change to the database you want to use for importing the .sql file data into. Do this by typing:

   USE your_database_name
   

3. Now locate the .sql file you want to execute.
   If the file is located in the main local C: drive directory and the .sql script file name is currentSqlTable.sql, you would type the following:

   \. C:\currentSqlTable.sql
   

   and press Enter to execute the SQL script file.

Trochee

What is Figure 1/2/3? – Phrase

Answer 14

If you are using sakila-db from mysql website, It's very easy on the Linux platform just follow the below-mentioned steps, After downloading the zip file of sakila-db, extract it. Now you will have two files, one is sakila-schema.sql and the other one is sakila-data.sql.

---

1. Open terminal
2. Enter command mysql -u root -p < sakila-schema.sql
3. Enter command mysql -u root -p < sakila-data.sql
4. Now enter command mysql -u root -p and enter your password, now you have entered into mysql system with default database.
5. To use sakila database, use this command use sakila;
6. To see tables in sakila-db, use show tables command

---

Please take care that extracted files are present in home directory.

Heflin

Answer 15

First connect to mysql via command line

mysql -u root -p

Enter MySQL PW

Select target DB name

use <db_name>

Select your db file for import

SET autocommit=0; source /root/<db_file>;

commit;

This should do it. (thanks for clearing) This will work even 10GB DB can be imported successfully this way. :)

Zambia

Answer 16

For Ubuntu/Linux users, Extract the SQL file and paste it somewhere

e.g you pasted on desktop

1. open the terminal
2. go to your database and create a database name
3. Create database db_name;
4. Exit Mysql from your terminal
5. cd DESKTOP
6. mysql -u root -p db_name < /cd/to/mysql.sql
7. Enter the password:....

Formosa

Answer 17

In Ubuntu, from MySQL monitor, you have already used this syntax:

mysql> use <dbname> -> The USE statement tells MySQL to use dbname as the default database for subsequent statements

mysql> source <file-path>

for example:

mysql> use phonebook;

mysql> source /tmp/phonebook.sql;

Important: make sure the sql file is in a directory that mysql can access to like /tmp

Northing

Answer 18

If you want to import a database from a SQL dump which might have "use" statements in it, I recommend to use the "-o" option as a safeguard to not accidentially import to a wrong database.

   •   --one-database, -o

       Ignore statements except those those that occur while the default
       database is the one named on the command line. This filtering is
       limited, and based only on USE statements. This is useful for
       skipping updates to other databases in the binary log.

Full command:

mysql -u <username> -p -o <databasename> < <filename.sql>

Villalba

Answer 19

Before running the commands on the terminal you have to make sure that you have MySQL installed on your terminal.

You can use the following command to install it:

sudo apt-get update
sudo apt-get install mysql-server

Refrence here.

After that you can use the following commands to import a database:

mysql -u <username> -p <databasename> < <filename.sql>

Merrymerryandrew

Answer 20

The simplest way to import a database in your MYSQL from the terminal is done by the below-mentioned process -

mysql -u root -p root database_name < path to your .sql file

What I'm doing above is:

1. Entering to mysql with my username and password (here it is root & root)
2. After entering the password I'm giving the name of database where I want to import my .sql file. Please make sure the database already exists in your MYSQL
3. The database name is followed by < and then path to your .sql file. For example, if my file is stored in Desktop, the path will be /home/Desktop/db.sql

That's it. Once you've done all this, press enter and wait for your .sql file to get uploaded to the respective database

Cutlor

Answer 21

There has to be no space between -p and password

mysql -u [dbusername] -p[dbpassword] [databasename] < /home/serverusername/public_html/restore_db/database_file.sql

I always use it, it works perfectly. Thanks to ask this question. Have a great day. Njoy :)

Czerny

we can also set this settings in cron job (schedule task) on shared, dedicated and cloud servers to reset or import database time to time automatically. – Czerny