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

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 2

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 3

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 4

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 5

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 6

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