Ssh – Executing a remote MySQL query over SSH

bashMySQLssh

I am trying to execute a MySQL query over ssh using the following command:

ssh -p 2020 mysql@mysql1.local.mydomain.com "mysql --verbose --compress --secure-auth --database ops --execute \
'INSERT INTO \`ops\`.\`accounts\` (\`alias\`, \`id\`, \`web_server\`, \`mysql_server\`) VALUES ('foobar', 'foobar', 'web2', 'mysql1')'"

The problem is that when this is executed, the single quotes around foobar, foobar, web2, and mysql1 are removed. Here is the error response from MySQL:

ERROR 1054 (42S22) at line 1: Unknown column 'foobar' in 'field list'
--------------
INSERT INTO `ops`.`accounts` (`alias`, `id`, `web_server`, `mysql_server`) VALUES (foobar, foobar, web2, mysql1)
--------------

How can I fix this? Thanks.

Best Answer

The solution was added another layer of escaped single quotes around the values.

ssh -p 2020 mysql@mysql1.local.mydomain.com "mysql --verbose --compress --secure-auth --database ops --execute \
'INSERT INTO \`ops\`.\`accounts\` (\`alias\`, \`id\`, \`web_server\`, \`mysql_server\`) VALUES ('\'foobar\'', '\'foobar\'', '\'web2\'', '\'mysql1\'')'"

Related Solutions

SSH – How to Perform Diff Over SSH

You can do it with Bash's process substitution:

 diff foo <(ssh myServer 'cat foo')

Or, if both are on remote servers:

diff <(ssh myServer1 'cat foo') <(ssh myServer2 'cat foo')

Ssh – Access remote thesql server over ssh tunnel

You'll need to have some kind of IP method to access that mysql server remotely, using only sockets will give you access to that mysql server from the local machine.

Your best option here would be activating networking but firewalling the mysql server so it's only accessible from localhost, then once you get your tunnel working it should be fairly straightforward to connect remotely through the ssh tunnel either directly from the command line ssh -L 3306:localhost:3306 user@server or using .ssh/config LocalForward 3306 localhost:3306

Best Answer

Related Solutions

SSH – How to Perform Diff Over SSH

Ssh – Access remote thesql server over ssh tunnel

Related Topic