How to import an SQL file using the command line i

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

Answer 1

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 ;

Answer 2

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

Answer 3

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.

Answer 4

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
   

Answer 5

Similarly to https://stackoverflow.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.

Answer 6

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