Keep MySQL connection open to submit multiple queries from shell

Question

I'd like to learn more about how the MySQL command line tool works.

Suppose I would like to be able to script multiple SQL statements and use shell scripting to output a description of the statements as they are processed. Here's a generic example where del_sql, ins_sql, and qry_sql would refer to any delete, insert, and select query on the same data set.

echo "Deleting data in the range."
echo $del_sql | mysql -h "$mysql_host" -u "$mysql_user" -p"$mysql_pass" -D "$mysql_db"

echo "Inserting data into the range."
echo $ins_sql | mysql -h "$mysql_host" -u "$mysql_user" -p"$mysql_pass" -D "$mysql_db"

echo "Querying data from the range."
echo $qry_sql | mysql -h "$mysql_host" -u "$mysql_user" -p"$mysql_pass" -D "$mysql_db"

What I'm concerned about is - does this open and close three separate connections to the database, and is this more expensive than submitting all statements at once? In this example suppose that all_sql is a string containing all three statements separated by semicolons.

echo "Deleting, inserting, and querying data in the range."
echo $all_sql | mysql -h "$mysql_host" -u "$mysql_user" -p"$mysql_pass" -D "$mysql_db"

I tried the following just messing around with it, thinking it would make sense.

mysql -h "$mysql_host" -u "$mysql_user" -p"$mysql_pass" -D "$mysql_db"
echo $del_sql | mysql -e
echo $ins_sql | mysql -e
echo $qry_sql | mysql -e

But this is clearly incorrect syntax.

Not sure what the question is, but if you want to feed multiple lines to a command, why don't you use here-is input? — Mats Kindahl
– Mats Kindahl, Commented Sep 2, 2014 at 6:50
The question is: do you have to put the MySQL credentials in every time you run a MySQL command and why? Can you not just maintain an active open connection? What do you mean here-is input? — Matt Dexter
– Matt Dexter, Commented Sep 2, 2014 at 7:19

fancyPants · Accepted Answer · 2014-09-02 07:38:25Z

2

I'm no programmer, but on database side I can tell you, that MySQL handles this quite efficiently for you. It uses a thread cache / pool. Each thread is basically a connection.

You can read more about it here: How MySQL Uses Threads for Client Connections.

And you can even observe this, when you log into your database via command line client and then issue your script in another shell.

Do a

SHOW PROCESSLIST;

After your script ran through, you will see that your connection is still there with the status SLEEPING. It will be reused when you execute your script again.

answered Sep 2, 2014 at 7:38

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

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

1 Comment

Matt Dexter Over a year ago

Fantastic, this is exactly what I wanted to know. Thank you!

Collectives™ on Stack Overflow

Keep MySQL connection open to submit multiple queries from shell

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related