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?
PiJei
5644 silver badges17 bronze badges
asked Jan 20, 2012 at 10:51
2
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;
answered Jan 20, 2012 at 11:04
Zoe EdwardsZoe Edwards
12.2k3 gold badges21 silver badges41 bronze badges
12
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
Sebas
20.7k9 gold badges53 silver badges108 bronze badges
answered Jan 20, 2012 at 10:55
Eugen RieckEugen Rieck
63k10 gold badges69 silver badges92 bronze badges
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: //dev.mysql.com/doc/refman/5.0/en/mysql-batch-commands.html
answered Apr 26, 2013 at 4:22
Paul PreibischPaul Preibisch
3,8292 gold badges26 silver badges30 bronze badges
3
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"
answered Dec 17, 2014 at 7:34
talsibonytalsibony
8,1285 gold badges46 silver badges44 bronze badges
9
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.
answered Apr 1, 2015 at 18:15
BhushanBhushan
17.7k30 gold badges102 silver badges136 bronze badges
2
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
- Access mysql using:
mysql -u - p
- At the mysql prompt, enter:
source file_name.sql
Hope this helps.
thclpr
5,5478 gold badges49 silver badges83 bronze badges
answered Sep 7, 2016 at 1:59
Zac SmithZac Smith
1,8191 gold badge12 silver badges16 bronze badges
2
Give the path of .sql file as:
source c:/dump/SQL/file_name.sql;
answered Jan 5, 2017 at 17:17
Shubham VermaShubham Verma
7,9146 gold badges51 silver badges75 bronze badges
1
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
Machavity♦
30.1k26 gold badges87 silver badges98 bronze badges
answered Mar 9, 2015 at 6:52
instead of redirection I would do the following
mysql -h -u --password= -D -e 'source '
This will execute the file path-to-sql-file
answered Apr 24, 2019 at 8:15
DataGuruDataGuru
6877 silver badges16 bronze badges
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:
answered Feb 3, 2017 at 5:23
mysql -uusername -ppassword database-name < file.sql
azro
49.1k7 gold badges31 silver badges66 bronze badges
answered Feb 8, 2017 at 16:26
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 hereThis must be run from within the mysqld.exe directory, hence the CD.
Hope this is helpful and not just redundant.
answered Jul 19, 2019 at 19:37
DonkeyKongDonkeyKong
95513 silver badges18 bronze badges
1
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.
Dharman♦
27.9k21 gold badges75 silver badges127 bronze badges
answered Oct 12, 2020 at 17:32
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
answered Sep 3, 2013 at 22:30
Ben LinBen Lin
7098 silver badges14 bronze badges
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.
answered Sep 28, 2013 at 16:46
Rajesh PaulRajesh Paul
6,5306 gold badges38 silver badges55 bronze badges
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
answered Aug 7, 2014 at 14:20
3xCh2_233xCh2_23
1,4391 gold badge20 silver
badges39 bronze badges
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.
answered Jun 25, 2015 at 2:31
Chad MxChad Mx
1,17611 silver badges17 bronze badges
If you are here LOOKING FOR A DRUPAL ENVIRONMENTYou can run with drush command on your project directory
drush sqlc
answered Apr 9 at 20:41
Xab IonXab Ion
6471 gold badge7 silver badges15 bronze badges
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.
answered Aug 7, 2015 at 15:31