How do I import an SQL file using the command line in MySQL

Question

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  lt  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

User · Answer

Sometimes the port defined as well as the server IP address of that database also matters     mysql -u user -p user -h  lt Server IP gt  -P lt port gt   DBNAME   lt  DB sql

User · Answer

Try   mysql -u username -p database name  lt  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

User · Answer

To import a database  use the following command    mysql gt  create new database    mysql gt  use new database    mysql gt  source  Here you need to import the path of the sql file    eg    mysql gt  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

User · Answer

1  Go to your wamp or xampp directory  Example  cd d  wamp bin mysql mysql5 7 24 bin  2  mysql -u root -p  DATABASENAME  lt  PATHYOUDATABASE FILE

User · Answer

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 lt   -p lt   DBName  lt  filelocation query sql   Where -u is the username  and -p is the MySQL password  Then enter your password and wait for data to import

User · Answer

simple just use this command in cmd use databasename    C  test data mysql

User · Answer

If you are using MAMP on Mac nbsp OS nbsp X  this may be helpful    applications MAMP library bin mysql -u MYSQL USER -p DATABASE NAME  lt  path to database sql FILE sql   MYSQL USER is root by default

User · Answer

Go to the directory where you have MySQL    c  mysql bin  gt  mysql -u username -p password database name  lt   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  gt  dump sql   Or you can use some GUI clients like SQLyog to do this

User · Answer

Easiest way to import into your schema   Login to mysql and issue below mention commands    mysql gt  use your db name   mysql gt  source  opt file sql

User · Answer

To dump a database into an SQL file use the following command   mysqldump -u username -p database name  gt  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  lt  database name sql

User · Answer

Among all the answers  for the problem above  this is the best one    mysql gt  use db name   mysql gt  source file name sql

User · Answer

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  lt  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

User · Answer

While most answers here just mention the simple command  mysql -u database user -p  db name   lt  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   lt  database file sql  Surley 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 separated 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 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

User · Answer

If you are using xampp then go to xampp  gt  mysql  gt  bin and open cmd here and paste this  mysql -u root -p dbname  lt  dbfilename sql

User · Answer

To import a single database  use the following command   mysql -u username -p password dbname  lt  dump sql   To import multiple database dumps  use the following command   mysql -u username -p password  lt  dump sql

User · Answer

A solution that worked for me is below   Use your database name  SOURCE path to db sql file on your local

User · Answer

I m using Windows 10 with PowerShell 5 and I found almost all  Unix-like  solutions not working for me    gt  mysql -u username   database-name   lt  my-database sql At line 1 char 31   mysql -u username   database-name   lt  my-database sql                                   The   lt   operator is reserved for future use        CategoryInfo            ParserError          ParentContainsErrorRecordException       FullyQualifiedErrorId   RedirectionNotSupported   I ends up using this command    gt  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

User · Answer

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  lt  database sql

User · Answer

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  lt  import script sql

User · Answer

You can use   mysql -u lt user gt  -p lt pass gt   lt db gt   lt  db sql   Example   mysql -uroot -proot db  lt  db sql

User · Answer

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 gt  mysql -u root -ppassword  note  no space between -p and password mysql gt  CREATE DATABASE databasename  mysql gt  using databasename  mysql gt  source  path to backup sql   I am running Fedora 26 with MariaDB

User · Answer

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 gt  mysql -u username -p database name  lt  file sql

User · Answer

Go to the directory where you have the MySQL executable  -u for username and -p to prompt for the password   C  xampp mysql bin gt mysql -u username -ppassword databasename  lt  C  file sql

User · Answer

You can try this query   Export   mysqldump -u username    -password your password database name  gt  file sql   Import   mysql -u username    -password your password database name  lt  file sql   and detail following this link   https   chartio com resources tutorials importing-from-and-exporting-to-files-using-the-mysql-command-line

User · Answer

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

User · Answer

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  lt  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

User · Answer

Try this -  C  xampp mysql bin gt mysql -u root -p database name --force  lt  C  file sql

User · Answer

Import into the database      mysql -u username -p database name   lt    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

User · Answer

I thought it could be useful for those who are using Mac nbsp OS nbsp X    Applications xampp xamppfiles bin mysql -u root -p database  lt  database sql   Replace xampp with mamp or other web servers

User · Answer

mysql --user  user  --password  password   database   lt  news ml all sql

User · Answer

If you already have the database  use the following to import the dump or the sql file   mysql -u username -p database name  lt  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 gt create database yourDatabaseName  mysql gt use yourDatabaseName    Then import the sql or the dump file to the database from  mysql gt  source pathToYourSQLFile    Note  if your terminal is not in the location where the dump or sql file exists  use the relative path in above

User · Answer

A common use of mysqldump is for making a backup of an entire database   shell gt  mysqldump db name  gt  backup-file sql   You can load the dump file back into the server like this   UNIX  shell gt  mysql db name  lt  backup-file sql   The same in Windows command prompt   mysql -p -u  user   database   lt  backup-file sql   PowerShell  C   gt  cmd exe  c  mysql -u root -p db name  lt  backup-file sql    MySQL command line  mysql gt  use db name  mysql gt  source backup-file sql

User · Answer

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 gt C   DATE DAY   DATE TIME  database sql

User · Answer

Import a database  Go to drive   command  d    MySQL login  command  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

User · Answer

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

User · Answer

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 gt  use db name   mysql gt  SET autocommit 0   source the sql file sql   COMMIT

User · Answer

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

User · Answer

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  lt  sample dump sql

User · Answer

For importing multiple SQL files at one time  use this     Unix-based solution for i in   sql do mysql -u root -pPassword DataBase  lt   i done   For simple importing     Unix-based solution mysql -u root -pPassword DataBase  lt  data sql   For WAMP    mysqlVersion replace with your own version C  wamp bin mysql mysqlVersion bin mysql exe -u root -pPassword DataBase  lt  data sql   For XAMPP   C  xampp mysql bin mysql -u root -pPassword DataBase  lt  data sql

User · Answer

Export particular databases    djimi  gt  mysqldump --user root --host localhost --port 3306 --password test -B CCR KIT  gt  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      djimi  gt  mysql --user root --host localhost --port 3306 --password test  lt  ccr kit local sql

User · Answer

- gt  Create a database in MySQL  - gt  then go to your computer directory C  xampp gt mysql gt bin   and write cmd on address bar  then hit enter  - gt  Unzip your sql file - gt  then wirte - mysql -u root -p dbname  then press enter  - gt  write - source sql file  Like Source C  xampp htdocs amarbazarltd st1159 sql Done

User · Answer

Add the --force option   mysql -u username -p database name --force  lt  file sql

User · Answer

Use   mysql -u root -p password -D database name  lt  lt  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                   gt         14  ETA 11 09 36 1 46GiB 1 50 14   246KiB s                     gt         14  ETA 11 09 15 1 47GiB 1 53 00   385KiB s                     gt         14  ETA 11 05 36

User · Answer

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

User · Answer

This line import the dump file in local database  under linux   mysql -u dbuser -p password including spaces  dbname  lt  path to dump file sql   This line import the dump file in remote database  under linux  Note  -P is for the port  and is required if mysql port is different than default   mysql -h dbhost -u dbuser -p password including spaces  -P 3306 dbname  lt  path to dump file sql   Note  the password include spaces and this is the reason of single quotes  Just change path style for using the command under windows  C  windows path dump file sql

User · Answer

If you are importing to your local database server  you can do the following   mysql -u database user -p  lt  database file sql   For a remote database server do the follwing   mysql -u database user -p -h remote server url  lt  database file sql

User · Answer

If you use XAMPP on windows  My XAMPP installation location is C  xampp and my file is at address C  Users file sql Just set username  and database name Note  First  you need to create the database database name  manually Run this command  cd C  xampp mysql bin  amp  amp  mysql -u username -p database name  lt  C  Users file sql  And then enter the password

User · Answer

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  lt  file sql           Enter the password and wait till uploading is completed

User · Answer

For exporting a database   mysqldump -u username -p database name  gt  file sql   For importing a database   mysql -u username -p database name  lt  file sql

User · Answer

Using MySQL Secure Shell  mysqlsh -u  lt username gt  -p -h  lt host gt  -D  lt database name gt  -f dump sql

User · Answer

We can use this command to import SQL from command line   mysql -u username -p password db name  lt  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  lt  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  lt  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  lt    Desktop bank sql

User · Answer

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

[mysql] How do I import an SQL file using the command line in MySQL?

Examples related to mysql

Examples related to sql

Examples related to command-line

Examples related to import