scripting MySQL from BASH over SSH

mysql bash command-line ssh term
Jasmine picture Jasmine · Mar 31, 2012 · Viewed 17k times · Source

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?

Answer

K. Norbert picture K. Norbert · Mar 31, 2012

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 it's sshd_config.

Related questions

Suppress warning messages using mysql from within Terminal, but password written in bash script

When I tried running the following command on MySQL from within Terminal: mysql -u $user -p$password -e "statement" The execution works as expected, but it always issues a warning: Warning: Using a password on the command line interface can …

Read More
Install MySQL on Ubuntu without a password prompt

How do I write a script to install MySQL server on Ubuntu? sudo apt-get install mysql will install, but it will also ask for a password to be entered in the console. How do I do this in a non-interactive …

Read More
How to run mysql command on bash?

The following code works on the command line mysql --user='myusername' --password='mypassword' --database='mydatabase' --execute='DROP DATABASE myusername; CREATE DATABASE mydatabase;' However, it doesnt work on bash file on excecution #!/bin/bash user=myusername password=mypassword database=mydatabase …

Read More