linux script to read data form database and send command on remote linux based on data

Question

I am new on scripting on Linux. On my local Mysql database which runs on a Linux machine:

mysql> SELECT * FROM folder;
+-------+---------+
| user  | folder  |
+-------+---------+
| user1 | folder1 |
| user2 | folder2 |
| user3 | folder3 |
+-------+---------+

I need to read form the database "folder1" and send command on remote Linux " mkdir folder1"
so script can create automatic folder1-3. Remote Linux password is not an issue.

Now i am doing it manually

ssh root@www1.cyberciti.biz mkdir folder1
ssh root@www1.cyberciti.biz mkdir folder2
ssh root@www1.cyberciti.biz mkdir folder3

Answer 1

sort answer like this

 #!/bin/bash
myvar=$(mysql -D database -u username -p  password -se "SELECT folder FROM folder"| xargs echo -n)
ssh root@www1.cyberciti.biz mkdir "$myvar"

Answer 2

You can do it in one line; here the test version. It's just prints out the commands. Drop the 'echo' to really execute the commands.

mysql testdb --batch --skip-column-names -p -e "select distinct(folder) from folder" | xargs -n 1 -I '{}' echo ssh root@www1.cyberciti.biz mkdir \"{}\"

Output:

ssh root@www1.cyberciti.biz mkdir "folder1"
ssh root@www1.cyberciti.biz mkdir "folder2"
ssh root@www1.cyberciti.biz mkdir "folder3"

Answer 3

while read id; do 
    ssh root@www1.cyberciti.biz "mkdir $id"
done < <(mysql -u $USER -p $PASSWORD $DATABASE -e "SELECT folder FROM folder;" --skip-column-names)

Answer 4

There are good points in various answers here. Let's mix a cocktail.

First off, as mentioned in comments, you probably want to do this in a loop rather than a single command line. While xargs is a powerful, useful command, in this case it's likely that you'll want to space things out in order to insert additional functionality in the future.

Second, as has been discussed, you need to account for column heads. Andreas quite rightly criticized other answers for leaving out the --skip-column-names option.

Third, as it stands, your script will try to recreate directories if it's run a second time. You want to mark things as done, once directories are made.

So...

#!/bin/bash

sql="SELECT folder FROM folder WHERE created=NULL"

mysql_opts=()
mysql_opts+=(--skip-column-names)      # obvious
mysql_opts+=(--host=dbhost)            # database server
mysql_opts+=(--user=username)          # mysql username
mysql_opts+=(--password=password)      # mysql password
mysql_opts+=(-D dbname)                # database name

while read name; do
  if [[ ! $name =~ ^[[:alnum:]_]+$ ]]; then
    echo "ERROR: invalid characters in '$name'" >&2
  elif ssh remotehost "mkdir '/path/to/${name}'"; then
    mysql "${mysql_opts[@]}" -e"UPDATE folder SET created=NOW() WHERE folder='$name'"
  else
    echo "ERROR: failed to create '$name'" >&2
  fi
done < <( mysql "${mysql_opts[@]}" -e"$sql" )

When programming, always consider how much effort it will be to maintain things in the future. If options are clear and well documented, then when things break, you can fix them faster.

So, we're setting mysql's options in a bash array.

Then we're running through a loop. Input to the loop is the command after done on the last line. This is called "Process Substitution".

Within the loop, we have an if that first does some input validation (which could also be done in your SELECT), and if the folder name passes, runs your ssh command. But the ssh command is run as part of another if. If it is successful, we mark the record as complete, with a timestamp. If it isn't successful, we get noisy about it. (Obviously, you'll need a created column in the folder table.)

Obviously, you can do whatever you like with your errors -- send a pager message, and email, notify your monitoring system, etc. The important thing is that you're accounting for failure, and doing something about it. (What happens if there was a temporary network failure when you were running the last mkdir?)

Let me know if any of this is confusing. I'll be happy to clarify.