Mysql output to file

Shell Programming and Scripting
1

Hi,

Below is my code to execute mysql statement , how to save the output to a file ?

#!/usr/bin/bash

#Script to run automated sql queries

#Declaring mysql DB connection 

MASTER_DB_USER='root'
MASTER_DB_PASSWD='root'
MASTER_DB_PORT=3306
MASTER_DB_HOST='localhost'
MASTER_DB_NAME='test1'

#Prepare sql query

SQL_Query='select source_table_id from test1.batch'
echo $SQL_Query
#mysql command to connect to database

MYSQL -u$MASTER_DB_USER -p$MASTER_DB_PASSWD -P$MASTER_DB_PORT -h$MASTER_DB_HOST -D$MASTER_DB_NAME <<EOF 
$SQL_Query
EOF
echo "End of script"
2

You have a few choices:-

  • Embed a write-to-file command in your SQL
  • Redirect the output to a file.

The SQL write to file for mysql is documented here MySQL :: MySQL 5.7 Reference Manual :: 13.2.9.1 SELECT ... INTO Syntax The gotcha is that the file is local to the database, so if you are connecting to a remote database, it doesn't write the file to your local client disk, so that may not be acceptable. You seem to be connecting to the localhost, so it should work just fine.

In the latter, option would be to add > /path/to/my_output_file into your script. You would put it in place to capture the output from the MYSQL command, although I would have expected this to be actually a mysql command.

You should get away with this:-

MYSQL -u$MASTER_DB_USER -p$MASTER_DB_PASSWD -P$MASTER_DB_PORT -h$MASTER_DB_HOST -D$MASTER_DB_NAME <<EOF > /path/to/my_output_file
$SQL_Query
EOF
echo "End of script"

I hope that this helps,
Robin