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.
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
--triggers to keep the routines and triggers of original database. They are not copied by default.
A common use of mysqldump is for making a backup of an entire database:
shell> mysqldump db_name > backup-file.sql
You can load the dump file back into the server like this:
shell> mysql db_name < backup-file.sql
The same in Windows command prompt:
mysql -p -u[user] [database] < backup-file.sql
C:\> cmd.exe /c "mysql -u root -p db_name < backup-file.sql"
MySQL command line
mysql> use db_name; mysql> source backup-file.sql;
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.
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
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 ;
mysql --user=[user] --password=[password] [database] < news_ml_all.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
Go to drive:
command: c:\xampp\mysql\bin\mysql -u root -p
It will ask for pwd. Enter it:
Select the database
Provide the file name
mysql -u username -p database_name --force < file.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
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
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
mamp or other web servers.
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.
binfolder of mysql server.
source databasefilename.sqland Enter
The following steps help to upload
file.sql to the MySQL database.
Step 1: Upload
file.sql.zip to any directory and unzip there
sudo apt-get install unzip
sudo apt-get unzip file.sql.zip
Step 2: Now navigate to that directory. Example:
mysql -u username -p database-name < file.sql
Enter the password and wait till uploading is completed.
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 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
#mysqlVersion replace with your own version C:\wamp\bin\mysql\mysqlVersion\bin\mysql.exe -u root -pPassword DataBase < data.sql
C:\xampp\mysql\bin\mysql -u root -pPassword DataBase < data.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
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.
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
Export particular databases:
djimi:> 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):
djimi:> mysql --user=root --host=localhost --port=3306 --password=test < ccr_kit_local.sql
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
Among all the answers, for the problem above, this is the best one:
mysql> use db_name; mysql> source file_name.sql;
A solution that worked for me is below:
Use your_database_name; SOURCE path_to_db_sql_file_on_your_local;
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
For information I just had default root + withoutpassword, it didn't works with all above answers.
I created a new user with all privileges and a password. It works.
-ppassword WITHOUT SPACE.
To dump a database into a SQL file use the following command
mysqldump -u username -p database_name > database_name.sql
To import a 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)
mysql u -username -p database_name < database_name.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
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, hopefully it helps.
Similarly to https://stackoverflow.com/a/17666285/1888983
Key differences for me:
-pand 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.
If you already have the database use the following to import the
dump or the
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
sql file exists, use the relative path in above.
mysql -u root -p password -D database_name << import.sql
Use mysql help for details
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
brew install pv
apt-get install pv.
Others, refer http://www.ivarch.com/programs/pv.shtml
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
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
If you are importing to your local database server, you can do the following:
mysql -u database_user -p < database_file.sql
For remote database server do the follwing:
mysql -u database_user -p -h remote_server_url < database_file.sql
While most answers here just mention the simple command
mysql -u database_user -p < 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 < database_file.sql
Surley this works for other charsets too, how to show the right notation can be seen here:
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:
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.
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: