1

I have the following instruction and it works.

ssh -i /e/Data/openssh.ppk root@172.25.4.20 ssh cos-server "pwd  "

But when I execute the instruction below:

ssh -i /e/Data/openssh.ppk root@172.25.4.20 ssh cos-server "mysql -u bduser --host=tdb --password=pass -e \"SELECT 1 AS Quantity;\"" 

It asks for my passphrase. I entered it but the command appears to fail and the output shows mysql options. I don't know if another quotation mark is required.

This is the output:

Warning: Permanently added 'cos-server_avm,100.69.35.5' (ECDSA) to the list of 
known hosts.
mysql  Ver 15.1 Distrib 10.2.13-MariaDB, for Linux (x86_64) using readline 5.1
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Usage: mysql [OPTIONS] [database]

Default options are read from the following files in the given order:
/etc/my.cnf ~/.my.cnf
The following groups are read: mysql client client-server client-mariadb
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.

  -?, --help          Display this help and exit.
  -I, --help          Synonym for -?
  --abort-source-on-error
                  Abort 'source filename' operations in case of errors
  --auto-rehash       Enable automatic rehashing. One doesn't need to use
                  'rehash' to get table and field completion, but startup
                  and reconnecting may take a longer time. Disable with
                  --disable-auto-rehash.
                  (Defaults to on; use --skip-auto-rehash to disable.)
  -A, --no-auto-rehash
  ...
  ...

Instruction:

ssh -i /e/Data/openssh.ppk root@172.25.4.20 ssh cos-server 'mysql --user=bduser --host=tdb --password=pass -e "SELECT VERSION();SELECT NOW()"'

Output:

Warning: Permanently added 'cos-server,100.69.35.5' (ECDSA) to the list of known 
hosts.
bash: -c: line 0: syntax error near unexpected token `('
bash: -c: line 0: `mysql -u bduser --host=tdb --password=pass -e SELECT 
VERSION();SELECT NOW();'

What is missing in this case?

1

MySQL says:

Usage: mysql [OPTIONS] [database]

because you didn't specify a database. Your command should look like this:

ssh -i /e/Data/openssh.ppk root@172.25.4.20 \
  ssh cos-server \
  'mysql -u bduser --host=tdb --password=pass -e \"SELECT 1 AS Quantity\;\" the_database' 

Edit: The answer originally had a command in which escaping was off, this way, the ; character was interpreted as the end of the command. I've updated the answer, but clearly, this will become more and more confusing as the SQL command becomes more complex. For example, a simple select count(*) would require escaping the parentheses, and would become select count\(*\). If the query contains a string, then you would have to write something like that as the mysql command:

'mysql -u bduser --host=tdb --password=pass -e \"SELECT \\\"'this_string'\\\" AS Quantity\;\" the_database'

which is not straightforward to say the least.

If you plan to use this database regularly this way, then you might want to consider other options, like doing an SSH tunnel and connecting to the tunneled port on your local computer.

3
  • It didn't work. The ouput is the of Usage: mysql [OPTIONS] [database] . At the end of the output says: bash: mydb: command not found. mydb is database name Jan 7 '20 at 16:13
  • 1
    I've updated the answer, and corrected the command.
    – Lacek
    Jan 8 '20 at 10:24
  • Great Lacek. You did it!!. It was the semicolon as you mentioned. Jan 9 '20 at 4:11
0

'mybd' isn't a valid flag, so it is showing you what the valid options are. Remove that and it should work:

ssh -i /e/Data/openssh.ppk root@172.25.4.20 ssh cos-server "mysql -u bduser --host=tdb --password=pass -e 'SELECT 1 AS Quantity;'"

11
  • I removed the database mybd, but it's not working either. It shows all of mysql options Jan 7 '20 at 3:30
  • You removed the database? Can you post the new command you're using if it is differnet to that above, as well as your exact output, and edit your original question to reflect this information?
    – LTPCGO
    Jan 7 '20 at 3:31
  • Yes. I just removed the database name. Ok I'm goint to edit it. Jan 7 '20 at 3:49
  • -u USERNAME or --user=USERNAME, not -uUSERNAME. Missed that first time and edited as required, see answer as it is currently
    – LTPCGO
    Jan 7 '20 at 3:56
  • 1
    @LTPCGO+ it's not the terminal, it's the nested ssh's. The shell on the first system removes the outer quotes and passes the correct value to the first ssh which passes it to the shell on the second system; that removes the inner quotes and passes the now-wrong value to the second ssh which passes it to the destination system, where it fails. Jan 8 '20 at 12:24
0

I do it changing the way.

Steps:

On server:

1) I created a directory impsql into /etc 2) I created a batch file. Let's say: impsql.sh (chmod +x included). This file contains:

ssh cos-server mysql -u bduser --host=tdb --password=pass mydb < /etc/impsql/impsql.sql

3) I created a file impsql.sql into /etc. This file contains:

SELECT 1 AS Quantity;

On PC client:

4) I executed:

ssh -i /e/Data/openssh.ppk root@172.25.4.20 /etc/impsql/impsql.sh

So the quotation mark is not involved.

1
  • You can do this without the intermediate files: echo 'select blah blah;' | ssh -i file user@host ssh host mysql -u u --host=h --password=p db . The key is to pass as stdin, not as arguments, the data that needs quoting, thus avoiding 'ssh quote hell'. Jan 8 '20 at 12:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.