scripting MySQL from BASH over SSH

Question

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

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.

any help?

Solution 1

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.

Solution 2

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.

Solution 3

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'

Comments

[Man Man Yu]

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

[K. Norbert]

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