run mysql commands through ssh

Question

I have a set up where I automate log checking on a remote server. I have created methods like this which use the exec command to tail logs like this..

Process p = Runtime.getRuntime().exec("ssh <user>@<domain> tail -f /location/logs

I print the logs to my terminal where I can run regex patterns to make sure they are correct.

Now I need to check some of the entries in the logs against some mysql tables which are also on the server. I have set up a similar method using a List to execute a series of commands to return the entries in the mysql table:

List<String> cmd = Arrays.asList("ssh user@domain mysql -u user -ppassword -h ipaddress", "use database", "SELECT column1, column2, etc FROM database");
    Process dumpcapProcess = Runtime.getRuntime().exec(cmd.toArray(new String[]{}));

    String line;
    // Reading the InputStream stream 
    BufferedReader reader = new BufferedReader(new InputStreamReader(dumpcapProcess.getInputStream()));
    while ((line = reader.readLine()) != null) {
        System.out.println(line);

    }

    // Reading the error stream for debug
    reader = new BufferedReader(new InputStreamReader(dumpcapProcess.getErrorStream()));
    while ((line = reader.readLine()) != null) {
        System.out.println(line);

But it doesnt work. I have played around with the syntax of the exec strings to look like this:

List<String> cmd = Arrays.asList("ssh", "user@domain", "mysql", "-u",  "user", "-ppassword", "-h", "ipAddress", "use databases", "SELECT columns FROM database");

and now i I get this error:

Usage: mysql [OPTIONS] [database] -?, --help Display this help and exit. -I, --help Synonym for -? --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 No automatic rehashing. One has to use 'rehash' to get table and field completion. This gives a quicker start of mysql and disables rehashing on reconnect. --auto-vertical-output Automatically switch to vertical output mode if the result is wider than the terminal width. -B, --batch Don't use history file. Disable interactive behavior. (Enables --silent.) --bind-address=name IP address to bind to. --character-sets-dir=name Directory for character set files. --column-type-info Display column type information. -c, --comments Preserve comments. Send comments to the server. The default is --skip-comments (discard comments), enable with --comments.

How can I run the mysql commands to return the result to my eclipse terminal?

steffen · Accepted Answer · 2015-05-13 13:26:32Z

1

You can pass the query directly from command line using the -e argument. Example:

 mysql -uuser -ppass -hhost -Ddatabase -e'show tables'

This is untested, but it should be something like:

List<String> cmd = Arrays.asList(
    "ssh",
    "user@domain",
    "mysql",
    "-uuser",
    "-ppassword",
    "-h10.0.0.1",
    "-Ddb",
    "-e'SELECT columns FROM table'"
);

answered May 13, 2015 at 13:26

steffen

17.4k4 gold badges47 silver badges87 bronze badges

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

3 Comments

Daniel Cohen Over a year ago

thanks. To use multiple queries can you use for example -e'use database' -e'SELECT column1 FROM database'. I tried but could only get one query to be executed

Daniel Cohen Over a year ago

what you have works great, I figured out that multiple queries can be run from the same -e command separated by a ';' ........ "-e'use database; SELECT * FROM database'"

steffen Over a year ago

@DanielCohen Note that I have selected the database with the -D argument. You can also execute queries without explicitly "using" a database: SELECT * from database.table.

Nicklas2751 · Accepted Answer · 2015-05-13 13:30:54Z

0

I think your Problem is that there is no space between the -p and the password. You should try to build your command like this:

mysql --user=user_name --password=your_password db_name

For more informations here is the documentation: http://dev.mysql.com/doc/refman/5.6/en/mysql.html

answered May 13, 2015 at 13:30

Nicklas2751

8501 gold badge9 silver badges24 bronze badges

Collectives™ on Stack Overflow

run mysql commands through ssh

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related