How to redirect mysql output to file?

Question

When I run any select or update query in mysql workbench, it shows either

or number of rows returned.

In my script, I use mysql -u user -h ip db -se"select * from.."

I have tried redirecting mysql output:

./script.sh >> script.log 2>&1

but it shows message only for error not when successfully run.

It does not show 27 row(s) returned. So in that case, I could not check if any update statement, select or procedure run successfully.

How can I get output which runs successfully?

Answer 1

I found a solution, in the options of mysql.

Run it like this:

mysql -u USER -h HOST -p PORT --password -vv -se "YOUR QUERY" >output.txt 2>errors.txt

The addition of the -vv parameter will give you the number of affected rows. -vvv will also tell you how much time it took to run the query.

Ex: I ran this:

mysql -u Nic3500 -h localhost -P 3306 --password -vv -se "INSERT INTO stackoverflow.activity (activity_id, activity_name) VALUES ('10', 'testtest');" >output.txt 2>&1

And output.txt is:

-------------- 
INSERT INTO stackoverflow.activity (activity_id, activity_name) VALUES ('10', 'testtest')
--------------

Query OK, 1 row affected

Bye

Answer 2

You can do this with a query using INTO OUTFILE like shown in the mysql documentation

Example:
SELECT DISTINCT name INTO OUTFILE '/tmp/out.txt' FROM people ORDER BY name ASC;