How to run SQL script in MySQL?

Question

I want to execute a text file containing SQL queries, in MySQL.

I tried to run source /Desktop/test.sql and received the error:

mysql> . \home\sivakumar\Desktop\test.sql ERROR: Failed to open file '\home\sivakumar\Desktop\test.sql', error: 2

Any idea on what I am doing wrong?

Answer 1

If you’re at the MySQL command line mysql> you have to declare the SQL file as source.

mysql> source \home\user\Desktop\test.sql;

Answer 2

You have quite a lot of options:

• use the MySQL command line client: mysql -h hostname -u user database < path/to/test.sql
• Install the MySQL GUI tools and open your SQL file, then execute it
• Use phpmysql if the database is available via your webserver

Answer 3

you can execute mysql statements that have been written in a text file using the following command:

mysql -u yourusername -p yourpassword yourdatabase < text_file

if your database has not been created yet, log into your mysql first using:

mysql -u yourusername -p yourpassword

then:

mysql>CREATE DATABASE a_new_database_name

then:

mysql -u yourusername -p yourpassword a_new_database_name < text_file

that should do it!

More info here: http://dev.mysql.com/doc/refman/5.0/en/mysql-batch-commands.html

Answer 4

My favorite option to do that will be:

 mysql --user="username" --database="databasename" --password="yourpassword" < "filepath"

I use it this way because when you string it with "" you avoiding wrong path and mistakes with spaces and - and probably more problems with chars that I did not encounter with.

---

With @elcuco comment I suggest using this command with [space] before so it tell bash to ignore saving it in history, this will work out of the box in most bash.

in case it still saving your command in history please view the following solutions:

Execute command without keeping it in history

---

extra security edit

Just in case you want to be extra safe you can use the following command and enter the password in the command line input:

mysql --user="username" --database="databasename" -p < "filepath"

Answer 5

All the top answers are good. But just in case someone wants to run the query from a text file on a remote server AND save results to a file (instead of showing on console), you can do this:

mysql -u yourusername -p yourpassword yourdatabase < query_file > results_file

Hope this helps someone.

Answer 6

I came here searching for this answer as well, and here is what I found works the best for me: Note I am using Ubuntu 16.x.x

1. Access mysql using:

mysql -u <your_user> - p

2. At the mysql prompt, enter:

source file_name.sql

Hope this helps.

Answer 7

Give the path of .sql file as:

source c:/dump/SQL/file_name.sql;

Answer 8

instead of redirection I would do the following

mysql -h <hostname> -u <username> --password=<password> -D <database> -e 'source <path-to-sql-file>'

This will execute the file path-to-sql-file

Answer 9

mysql> source C:\Users\admin\Desktop\fn_Split.sql

Do not specify single quotes.

If the above command is not working, copy the file to c: drive and try again. as shown below,

mysql> source C:\fn_Split.sql

Answer 10

mysql -u your-username database-name < file.sql -p

Answer 11

Never is a good practice to pass the password argument directly from the command line, it is saved in the ~/.bash_history file and can be accessible from other applications.

Use this instead:

mysql -u user --host host --port 9999 database_name < /scripts/script.sql -p
Enter password:

Answer 12

So many ways to do it.

From Workbench: File > Run SQL Script -- then follow prompts

From Windows Command Line:
   Option 1: mysql -u usr -p
             mysql> source file_path.sql
   Option 2: mysql -u usr -p '-e source file_path.sql'
   Option 3: mysql -u usr -p < file_path.sql
   Option 4: put multiple 'source' statements inside of file_path.sql (I do this to drop and recreate schemas/databases which requires multiple files to be run)
             mysql -u usr -p < file_path.sql

If you get errors from the command line, make sure you have previously run

cd {!!>>mysqld.exe home directory here<<!!}
mysqld.exe --initialize 

This must be run from within the mysqld.exe directory, hence the CD.

Hope this is helpful and not just redundant.

Answer 13

From linux 14.04 to MySql 5.7, using cat command piped with mysql login:

cat /Desktop/test.sql | sudo mysql -uroot -p 

You can use this method for many MySQL commands to execute directly from Shell. Eg:

echo "USE my_db; SHOW tables;" | sudo mysql -uroot -p 

Make sure you separate your commands with semicolon (';').

I didn't see this approach in the answers above and thought it is a good contribution.

Answer 14

Very likely, you just need to change the slash/blackslash: from

 \home\sivakumar\Desktop\test.sql

to

 /home/sivakumar/Desktop/test.sql

So the command would be:

source /home/sivakumar/Desktop/test.sql

Answer 15

If you are trying this command :

mysql -u root -proot -D database < /path/to/script.sql

You may get an error like this : if you have special characters, mainly '`'

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/path/to/script.sql' at line 1

So I would suggest to use a command like this :

echo "source /path/to/script.sql" | mysql -u root -proot -D database
# OR
mysql -u root -proot -D database -e "source /path/to/script.sql"

This command will execute source /path/to/script.sql once connected to the server, which execute your script.

Answer 16

use the following from mysql command prompt-

source \\home\\user\\Desktop\\test.sql;

Use no quotation. Even if the path contains space(' ') use no quotation at all.

Answer 17

Since mysql -u yourusername -p yourpassword yourdatabase < text_file did not work on a remote server (Amazon's EC2)...

Make sure that the Database is created first.

Then:

mysql --host=localhost --user=your_username --password=your_password your_database_name < pathTofilename.sql

Answer 18

For future reference, I've found this to work vs the aforementioned methods, under Windows in your msql console:

mysql>>source c://path_to_file//path_to_file//file_name.sql;

If your root drive isn't called "c" then just interchange with what your drive is called. First try backslashes, if they dont work, try the forward slash. If they also don't work, ensure you have your full file path, the .sql extension on the file name, and if your version insists on semi-colons, ensure it's there and try again.

Answer 19

In your terminal follow the steps below. Note replace my_user, my_database and file_name with your details.

mysql -u my_user -p

use my_database;

source file_name.sql

Answer 20

In powershell, when using > C:\backup\backup.sql we kept getting

The '<' operator is reserved for future use.

So if someone else is thrashing on this on a windows box and using cmd or powershell, this did it for us.

.\mysql.exe -u root -p -D db_prod -e "source C:\\backup\\yesterday.sql" 

Answer 21

I use Bash's Here Strings for an instant SQL execution:

mysql -uroot -p <<<"select date(now())"

https://www.gnu.org/software/bash/manual/html_node/Redirections.html#Here-Strings

Answer 22

If you are here LOOKING FOR A DRUPAL ENVIRONMENT

You can run with drush command on your project directory

drush sqlc

Answer 23

I had this error, and tried all the advice i could get to no avail.

Finally, the problem was that my folder had a space in the folder name which appearing as a forward-slash in the folder path, once i found and removed it, it worked fine.