How to write MySQL query in text file and run in Linux?

I would like to call a .sql file from a .sh file in linux. The .sql file will contain queries to MySQL database.

I am able to call the .sql file by running .sh file in linux, but the sql query is not working. Below is my syntax. The select statement is throwing below error.

./sample.sql: line 4: syntax error near unexpected token `from'

sample.sh file:

echo "this is shellscript calling sql"
./sample.sql

sample.sql file:

set pages 999
set lines 999
set colsep |
select * from sampletable;

expected result should be similar to a normal select * from xxx statement

To call the mysql process from the shell, you need to invoke the mysql process.

Your script as SQL statements but does not invoke mysql.

For example, your script should have something like this:

mysql -u myuserid -p  mydatabase < mysqlfile.sql

(just as an example)

For future readers, one easy way is as follows,

akshay@ideapad:/tmp$ mysql -u someuser -p test -e "select * from offices"
Enter password: 
+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+
| officeCode | city          | phone            | addressLine1             | addressLine2 | state      | country   | postalCode | territory |
+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+
| 1          | San Francisco | +1 650 219 4782  | 100 Market Street        | Suite 300    | CA         | USA       | 94080      | NA        |
| 2          | Boston        | +1 215 837 0825  | 1550 Court Place         | Suite 102    | MA         | USA       | 02107      | NA        |
| 3          | NYC           | +1 212 555 3000  | 523 East 53rd Street     | apt. 5A      | NY         | USA       | 10022      | NA        |
| 4          | Paris         | +33 14 723 4404  | 43 Rue Jouffroy D'abbans | NULL         | NULL       | France    | 75017      | EMEA      |
| 5          | Tokyo         | +81 33 224 5000  | 4-1 Kioicho              | NULL         | Chiyoda-Ku | Japan     | 102-8578   | Japan     |
| 6          | Sydney        | +61 2 9264 2451  | 5-11 Wentworth Avenue    | Floor #2     | NULL       | Australia | NSW 2010   | APAC      |
| 7          | London        | +44 20 7877 2041 | 25 Old Broad Street      | Level 7      | NULL       | UK        | EC2N 1HN   | EMEA      |
+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+

If you're exporting by non-root user then set permission like below

root@ideapad:/tmp# mysql -u root -p
MariaDB[(none)]> UPDATE mysql.user SET File_priv = 'Y' WHERE user='someuser' AND host='localhost';

Restart or Reload mysqld

akshay@ideapad:/tmp$ sudo su
root@ideapad:/tmp#  systemctl restart mariadb

Sample code snippet

akshay@ideapad:/tmp$ cat test.sh 
#!/usr/bin/env bash

user="someuser"
password="password"
database="test"

mysql -u"$user" -p"$password" "$database" <<EOF
SELECT * 
INTO OUTFILE '/tmp/csvs/offices.csv' 
FIELDS TERMINATED BY '|' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n'
FROM offices;
EOF

Execute

akshay@ideapad:/tmp$ mkdir -p /tmp/csvs
akshay@ideapad:/tmp$ chmod +x test.sh
akshay@ideapad:/tmp$ ./test.sh 
akshay@ideapad:/tmp$ cat /tmp/csvs/offices.csv 
"1"|"San Francisco"|"+1 650 219 4782"|"100 Market Street"|"Suite 300"|"CA"|"USA"|"94080"|"NA"
"2"|"Boston"|"+1 215 837 0825"|"1550 Court Place"|"Suite 102"|"MA"|"USA"|"02107"|"NA"
"3"|"NYC"|"+1 212 555 3000"|"523 East 53rd Street"|"apt. 5A"|"NY"|"USA"|"10022"|"NA"
"4"|"Paris"|"+33 14 723 4404"|"43 Rue Jouffroy D'abbans"|\N|\N|"France"|"75017"|"EMEA"
"5"|"Tokyo"|"+81 33 224 5000"|"4-1 Kioicho"|\N|"Chiyoda-Ku"|"Japan"|"102-8578"|"Japan"
"6"|"Sydney"|"+61 2 9264 2451"|"5-11 Wentworth Avenue"|"Floor #2"|\N|"Australia"|"NSW 2010"|"APAC"
"7"|"London"|"+44 20 7877 2041"|"25 Old Broad Street"|"Level 7"|\N|"UK"|"EC2N 1HN"|"EMEA"

This topic was automatically closed 58 days after the last reply. New replies are no longer allowed.