export mysql table data to csv using bash script

Question

I am trying to export my sql table data to csv but have these errors. I am really confused with double quotes and single quotes.

#!/bin/bash
mysql -u root -pH0tjava1 -B -e 'SELECT CONCAT("sshpass -p ""Password"" rsync -avvtzh -e ""ssh -o StrictHostKeyChecking=no"" --log-file=""/home/toor/rsync2.log""", login,"@", ftp_addr, " :", camera_name,"/", "/",`\ 'home`',"/",login, "/", camera_name) INTO OUTFILE '/tmp/rsynctest3.csv' lines terminated by '\r\n' from inteliviz.cameras;"


Errors:
/usr/local/bin/rsync.sh: line 8: syntax error near unexpected token `)'
/usr/local/bin/rsync.sh: line 8: `  mysql -u root -pH0tjava1 -B -e "select  CONCAT ("sshpass -p "Pa55word"  rsync   -avvtzh -e  "ssh -o StrictHostKeyChecking=no"  --log-file= "/home/toor/rsync2.log", login,camera_name,ftp_addr)  INTO OUTFILE '/tmp/rsynctest3.csv' lines terminated by '\r\n'  from inteliviz.cameras;"'

/usr/local/bin/rsync.sh: line 8: unexpected EOF while looking for matching ``'
/usr/local/bin/rsync.sh: line 11: syntax error: unexpected end of file

[How to output MySQL query results in CSV format?](http://stackoverflow.com/q/356578/3776858) — Cyrus, Dec 08 '15 at 14:51
Start by working your query in the client interface. Once you have a working query, you can fiddle with the quotes and single quotes to run it from a script. — Richard St-Cyr, Dec 08 '15 at 14:55
Thanks Cyrus, I have gone through that question but still failed on this script to get it working. — Kiran V, Dec 08 '15 at 14:58
this workls fine on mysql workbench but failing on bash script running through cron — Kiran V, Dec 08 '15 at 14:58

score 0 · Answer 1 · answered Sep 08 '17 at 13:27

I am really confused with double quotes and single quotes.

You have not only double and single quotes, but also backticks in your statement. The mismatches are:

You begin the SQL statement with 'SELECT… and end it with …inteliviz.cameras;". To switch the quotes from ' in the first part to " in the last part, you must insert a closing ' and an opening " betwixt, e. g. …camera_name)'" INTO OUTFILE…
The expression `\ 'home`' is messed - the first ` is inside the outer single quotes and thus has no special function, the ' before home closes the single quoted part, the second ` opens an unclosed command substitution, inside of which the final ' is. You have to reconsider what you want to have there and get the quote nesting straight.

score 0 · Answer 2 · edited Jul 08 '20 at 17:36

0

Try with the below script, which works for me:

# Read query into a variable
sql="$(cat query.sql)"

# If sqlplus is not installed, then exit
if ! command -v sqlplus > /dev/null; then 
  echo "SQL*Plus is required..."
  exit 1 
fi 

# Connect to the database, run the query, then disconnect
echo -e "SET PAGESIZE 0\n SET FEEDBACK OFF\n $sql" | \
sqlplus -S -L "$USERNAME/$PASSWORD@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$HOST)(PORT=$PORT))(CONNECT_DATA=(SERVICE_NAME=$DATABASE)))"

edited Jul 08 '20 at 17:36

Dom

1,687
6
27
37

answered Jul 08 '20 at 14:19

Sreenivas Ponnadi

109
1
8

maybe elaborate a bit on what sqlplus does here – beppe9000 Jul 29 '21 at 18:06

export mysql table data to csv using bash script

2 Answers2