mysql - into - mariadb import sql




How to import an SQL file using the command line in MySQL? (20)

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
    

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?

  1. Open the MySQL command line
  2. Type the path of your mysql bin directory and press Enter
  3. Paste your SQL file inside the bin folder of mysql server.
  4. Create a database in MySQL.
  5. Use that particular database where you want to import the SQL file.
  6. Type source databasefilename.sql and Enter
  7. Your SQL file upload successfully.

A solution that worked for me is below:

Use your_database_name;
SOURCE path_to_db_sql_file_on_your_local;

Add the --force option:

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

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

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.


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

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

Replace xampp with mamp or other web servers.


If you already have the database use the following to import the dump or the sql file

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 dump or sql file exists, use the relative path in above.


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.


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 

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


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

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.


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

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 --user=[user] --password=[password] [database] < news_ml_all.sql




import