How to run SQL script in MySQL?

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?

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;

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

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 yourdatabase has not been created yet, log into your mysql first using:

mysql -u yourusername -p yourpassword yourdatabase

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

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"

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.