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

User · Answer

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 <your_user> - p

At the mysql prompt, enter:

source file_name.sql

Hope this helps.

User · Answer

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

User · Answer

you can execute mysql statements that have been written in a text file using the following command   mysql -u yourusername -p yourpassword yourdatabase  lt  text file   if yourdatabase has not been created yet  log into your mysql first using   mysql -u yourusername -p yourpassword yourdatabase   then   mysql gt CREATE DATABASE a new database name   then   mysql -u yourusername -p yourpassword a new database name  lt  text file   that should do it   More info here  http   dev mysql com doc refman 5 0 en mysql-batch-commands html

User · Answer

instead of redirection I would do the following  mysql -h  lt hostname gt  -u  lt username gt  --password  lt password gt  -D  lt database gt  -e  source  lt path-to-sql-file gt     This will execute the file path-to-sql-file

User · Answer

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

User · Answer

Since mysql -u yourusername -p yourpassword yourdatabase  lt  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  lt  pathTofilename sql

User · Answer

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  quot USE my db  SHOW tables  quot    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

User · Answer

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  lt  query file  gt  results file   Hope this helps someone

User · Answer

Give the path of  sql file as   source c  dump SQL file name sql

User · Answer

You have quite a lot of options    use the MySQL command line client  mysql -h hostname -u user database  lt  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

User · Answer

If you   re at the MySQL command line mysql gt  you have to declare the SQL file as source   mysql gt  source  home user Desktop test sql

User · Answer

My favorite option to do that will be    mysql --user  username  --database  databasename  --password  yourpassword   lt   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  lt   filepath

User · Answer

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

User · Answer

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  lt   scripts script sql -p Enter password

User · Answer

So many ways to do it   From Workbench  File  gt  Run SQL Script -- then follow prompts  From Windows Command Line     Option 1  mysql -u usr -p              mysql gt  source file path sql    Option 2  mysql -u usr -p  -e source file path sql     Option 3  mysql -u usr -p  lt  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  lt  file path sql   If you get errors from the command line  make sure you have previously run   cd     gt  gt mysqld exe home directory here lt  lt     mysqld exe --initialize    This must be run from within the mysqld exe directory  hence the CD   Hope this is helpful and not just redundant

User · Answer

mysql -uusername -ppassword database-name  lt  file sql

User · Answer

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

User · Answer

mysql gt  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 gt  source C  fn Split sql

[mysql] How to run SQL script in MySQL?

Examples related to mysql

Examples related to command-line