CODEWITHSUNDEEP
linuxshell
Alpha2k
Alpha2k

Reputation: 2241

shell save MySQL query result to a file

I have a script which runs a MySQL query, something like this:

#!/bin/sh

user="root"
pwd="test"
database="mydb"
command="long... 
         long... query in
         multiple lines" 

mysql -u $user -p$pwd << EOF 
use $database;
$command
EOF

This query does a backup from a table to another. Is it possible to save the query result in a file without using mysql INTO OUTFILE? I only want to know if the query failed or succeeded.

If it succeeded something like 1 row(s) affected or if it failed Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ...

Update

Solution 1: () act as a chain of commands, so wrapping them in a variable gets the result of those commands. Then simply output the result of that variable in a file.

output=$( mysql -u $user -p$pwd << EOF 
use $database;
$command
EOF
)
echo "$output" >> /events/mysql.log

Solution 2: use tee as system command to send the result of the commands into a file, but this needs to be done from the crontab, like this: 

*/1 * * * * root sh /events/mysql.sh |tee -a /events/mysql.log

http://forums.mysql.com/read.php?10,391070,391983#msg-391983

My working solution: 

user="root"
pwd="root12345"
database="mydb"
command="long ...long query"

mysql -u $user -p$pwd << EOF >> /events/mysql.log 2>&1
use $database;
$command;
EOF

Upvotes: 1

Views: 18771

Answers (4)

cranehuang
cranehuang

Reputation: 515

you can also do it like this:

    #!/bin/sh

    user="root"
    pwd="test"
    database="mydb"
    command="long... 
     long... query in
     multiple lines" 
    mysql -u$user -p$pwd -D$database -e "$command" > file

Upvotes: 1

Alex
Alex

Reputation: 6047

The correct way to handle error-messages is to through stderr. Use 2>&1 to catch the error.

So, add this to the end of your script:

>> install.log 2>&1

Upvotes: 1

Shahzaib Ali Khan
Shahzaib Ali Khan

Reputation: 717

It's easier to use the MySQL tee command to send output.

To get the logging process started, just use the tee command,

tee /tmp/my.out;

#!/bin/sh

user="root"
pwd="test"
database="mydb"
$pathToFile = "/tmp/my.out"
command="long... 
         long... query in
         multiple lines" 

mysql -u $user -p$pwd << EOF 
tee $pathToFile
use $database;
$command
EOF

EDIT Since tee is reportedly not working inside script, you could also log output using Tee directly when running the script.

mysql_bash.sh > >(tee -a script.log)

Upvotes: 0

Jahid
Jahid

Reputation: 22438

This should work:

output=$( mysql -u $user -p$pwd << EOF 
use $database;
$command
EOF
)
echo "$output" >> outfile

Upvotes: 4

Related Questions