Execute bash command in batch with mysql results

Question

I maintain several servers having the same web app through bash scripts. Commands include basically ssh and scp, and they are the same for every server, just the IP, the user and the port are different between servers. So far I wrote as many commands as servers to maintain in same script. This works well but as I start to have many servers to maintain, I would prefer to list these servers in a MySQL table, and then use this list in my bash scripts, thus I I would not need to keep all of them updated when I have new servers to maintain.

Currently I have problems to extract data from MySQL in a proper way that could be then executed in bash script.

My current approach is to use CONCAT function in the query as follow:

outputofquery=$($MYSQL -u"$USER" --batch -p"$PASSWORD" --skip-column-names -h"$HOST" -e "SELECT CONCAT('scp -P ', server_port, ' $file ', server_user, '@', server_ip, ':/var/www/html/site/') FROM server_ssh;" $DBNAME)
echo ${outputofquery%$'\t;'*}

Giving the following result:

scp -P 22 text.php user1@1.1.1.1:/var/www/html/site/ scp -P 12345 text.php user2@2.2.2.2:/var/www/html/site/

Every command resulting from the MySQL query is put on the same line, meaning that this cannot be executed..

I though to add a semicolon just after site/ in the query, so that even if every command is on the same line, they could be executed independently but it happens that only the last scp command gets executed and those before are ignored.

Could you please let me know how I could execute ssh and scp commands in batch with data coming from a MySQL table?

score 1 · Accepted Answer · answered Apr 20 '19 at 10:35

Finally I succeed to execute batch commands with MySQL data. I build the commands to execute with concatenate function, depending on the line number extracted from MySQL. Below is my script

#!/bin/sh

HOST="localhost"
USER="root"
DBNAME="mydb"
PASSWORD="mypassord"
MYSQL="/Applications/XAMPP/bin/mysql"

file='text.php'
i=0;
command='';

ips_and_ports=$($MYSQL -u"$USER" -p"$PASSWORD" -h"$HOST" -BNe "SELECT server_port,server_user,server_ip FROM server_ssh;" $DBNAME)
for line in $ips_and_ports
do
    (( i++ )); #increment the line number

    if [ $(($i % 3)) -eq 1 ] #check if it is the first element of a line extracted from MySQL (server_port)
    then
        command="scp -P $line"; #initialize a new command to execute
    elif [ $(($i % 3)) -eq 2 ] #check if it is the second element of a line extracted from MySQL (server_user)
    then
        command+=" $file $line@"; #concatenate
    elif [ $(($i % 3)) -eq 0 ] #check if it is the third element of a line extracted from MySQL (server_ip)
    then
        command+="$line:/var/www/html/my_web_app/;"; #concatenate
        eval $command; #execute the command 
    fi
done;

score 0 · Answer 2 · answered Apr 19 '19 at 09:28

0

I can't put a comment because I'm new.

Have you tried to put a \n or && at the end of your mysql concatenation ?

You can also change the approach and assign the server and the port to variables and loop over these variables to execute the ssh and the scp command. For example

ips_and_ports=$($MYSQL -u"$USER" --batch -p"$PASSWORD" --skip-column-names -h"$HOST" -e "SELECT server_port, server_ip FROM extranet.server_ssh;" $DBNAME)
for $line in $ips_and_ports
do
    OLDIFS=$IFS;
    IFS="    "; # set the separator to the tab character (PS: this is 4 spaces, change accordingly )
    for $el in $line
    do
        set -- $el; # separate each line by tab and assign the variables to $1, $2 ..
        port=$1;
        ip=$2;
        scp -P 22 text.php user1@$ip:/var/www/html/site/ scp -P $port
    done;
    IFS=$OLDIFS; # put the separator back to it's original value
done;

I haven't tested the code but I hope you got my idea !

Good luck

more infos about the IFS and OLDIFS in link

answered Apr 19 '19 at 09:28

MrMaxPayne

173
5

Thanks a lot for your feedback. Unfortunately adding \n or && does not change anything. The approach you describe below is interesting, unfortunately the value in $line includes only one field, not all fields from the SELECT – Pierre Apr 19 '19 at 09:45
I found this [link](https://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format) it makes the separator `,` in the query results. the first `for` then loops correctly over lines (you should change $IFS to "," too). The problem I think is that the separator by default is whitespaces of all kind and not `\n` solely – MrMaxPayne Apr 19 '19 at 09:57
1

Thanks, but not better. Anyway, I found a solution by concatenating the command to execute (I put my script in a separate answer) in this thread – Pierre Apr 20 '19 at 10:37

Execute bash command in batch with mysql results

2 Answers2