Catching mysql output in shell script

Question

I am new to shell scripting. I am trying to write a shell script in which i can run mysql commands after reading from the file. I have to capture the output of the sql commands in a separate file. Following is the code :

mysql -h ${DB_SERVER} -P ${DB_PORT} -u ${USER} -p${PASS} -f < createUsers.sql >> install.log

Shell script takes a few inputs from the user. When i run the shell script and if i specify a wrong password, the error shows up on the console but not in the log file.

Please suggest how can i redirect the error to the log file as well.

John Kugelman · Accepted Answer · 2011-04-02 17:05:50Z

8

Add 2>&1 on to the end to also capture stderr. Standard out is file descriptor 1 and standard error is 2, so this redirects stderr to wherever stdout 1 is going, i.e. install.log.

mysql [options] < createUsers.sql >> install.log 2>&1

Note: The order of redirections matters. Make sure you put it after the >>, not before it.

answered Apr 2, 2011 at 17:05

John Kugelman

364k70 gold badges555 silver badges600 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

delliottg · Accepted Answer · 2015-01-28 22:38:47Z

0

When running MySQL commands in a batch file I wasn't able to use the: %logfile% 2>&1 command because it would log everything that MySQL was doing, and I ended up with log files the same size as my backup files (nearly 500mb). Not very useful.

Instead I used 2>> %logfile% which will only log errors to my log file.

set tempFile1=dropAndCreate.txt
echo DROP DATABASE IF EXISTS %testDatabase%; > %tempFile1%
echo CREATE DATABASE IF NOT EXISTS %testDatabase%; >> %tempFile1%
mysqldump -e -u %user% -p%pwd% %productionDatabase% > %backupFile% 2>> %logFile%
mysql -u %user% -p%pwd% < %tempFile1% 2>> %logFile%
mysqldump -u %user% -p%pwd% %productionDatabase% | mysql -u %user% -p%pwd% %testDatabase% 2>> %logFile%

answered Jan 28, 2015 at 22:38

delliottg

4,2004 gold badges41 silver badges54 bronze badges

Comments

J. Steen · Accepted Answer · 2014-05-23 12:48:27Z

-1

 #!/bin/sh

./mysql -u<user_name> -p<password>  database_name  < db.sql  > output.txt

**#db.sql contains all mysql queries(input file),output is redirected to output.txt.
# # replace user name and password fields with appropriate name and password
# # this works provided your working directory and mysql directory are same otherwise provide required directories to    input #file and output file
# # otherwise it throws an error "no file or directory"
# #whatever db.sql output is ,it will be in output.txt**

edited May 23, 2014 at 12:48

J. Steen

15.6k15 gold badges59 silver badges64 bronze badges

answered May 23, 2014 at 12:19

Sasanka

11 bronze badge

Collectives™ on Stack Overflow

Catching mysql output in shell script

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related