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

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 2

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 3

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.

Answer 4

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 5

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: