scripting MySQL from BASH over SSH

Question

I have a CentOS server that I want to script from BASH running it on my laptop.

I want to run a script locally that:

logs into the server over ssh and executes some MySQL statements
copies files that I need to where I need them

The file copying is easy. I can do that.

But how can one connect to a MySQL server over the SSH port and execute statements? I think I am just stuck on the connect part. executing the statements I can build up on a variable and batch execute.

I have an SSH pub/priv keypair from my laptop to this server as well.

Is this what you're looking for? wallpaperama.com/forums/_zwxwzy.html — mikevoermans
– mikevoermans, Commented Mar 30, 2012 at 22:18

K. Norbert · Accepted Answer · 2022-01-30 22:42:45Z

9

You can specify commands to run on the remote machine, as the last argument to ssh:

ssh user@remote 'mysql -u user ...'

The problem with this is that it will be a hassle to deal with the various '" escaping in the mysql command(s).

A better way, in my opinion, is to open an SSH tunnel to the remote machine when you connect:

ssh -L 12341:127.0.0.1:3306 user@server &

This would connect your local 12341 port, to the remote machine's 3306 (mysqld) port. After the connection is done, you can connect to it from your local machine like:

mysql -h 127.0.0.1 -p 12341

So you can place your SQL statements into a file, and cat it into mysql:

cat commands | mysql -h 127.0.0.1 -p 12341

Don't forget to kill the SSH connection after you are done.

Note that tunneling requires the remote server to have PermitTunnel "yes" in its sshd_config.

edited Jan 30, 2022 at 22:42

answered Mar 30, 2012 at 23:33

K. Norbert

10.7k5 gold badges52 silver badges48 bronze badges

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

2 Comments

Man Man Yu Over a year ago

may i ask how to kill the SSH connection in bash ?

K. Norbert Over a year ago

Executing "jobs" will show you backgrounded tasks, with their IDs that you can use together with kill, eg. kill %1

undef · Accepted Answer · 2012-03-30 22:19:32Z

7

just use ssh to run mysql on the remote server. For example

ssh user@server 'mysql -uimauser -p imadb -e "select * from table"'.

Everything in quotes will be run remotely by ssh.

answered Mar 30, 2012 at 22:19

undef

4452 silver badges8 bronze badges

Comments

Álvaro Justen · Accepted Answer · 2012-04-02 16:14:02Z

6

You can do what @WishCow said or you can put all MySQL statements in a .sql file, copy this file to server and then call mysql to execute these statements, something like this:

echo "show databases;" > test.sql
echo "use some_database;" >> test.sql
echo "show tables;" >> test.sql
scp test.sql remote-user@remote-server:
ssh remote-user@remote-server 'mysql -u mysql-user -pmysql-pass < test.sql'

answered Apr 2, 2012 at 16:14

Álvaro Justen

2,0691 gold badge20 silver badges20 bronze badges

Collectives™ on Stack Overflow

scripting MySQL from BASH over SSH

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related