I've come across a situation where I had to create a utilitary shell script that would be executed in many servers from different regions. The objective is to dump the local mysql database, copy it to another server and export the mysql to the remote server's database.

The script is going to read input from the user for this objective.

The problem is that I must avoid opening more than one SSH connection for the same server, in other words, the script should not ask more than once for a SSH password and the the solution I had in mind would end up making requiring it at least twice.

I also have some limitations: I can't change ssh's controlmaster and the server can't have sshpass installed. Also, some servers are not our own and I can't add keys for all of them as it was required that my script should be a stand alone approach with no further changes in any external server.

The steps I'm following are these:

  1. I export the database using mysqldump.
  2. Then I scp the .sql file to destination machine.
  3. I generate a new .sh file with the mysql import routine.
  4. Then open a new ssh connection to the destination server where I pass the .sh file and execute it.
  5. I clean up sql file and temporary sh file.

Does anyone know any better approach to execute this routine which can allow me to use only one session to accomplish all this work?

  • Is the number of actual connections limited? Or is this one connection thing a pure user experience thing? If the latter then you could just use authorized_keys and avoid passwords fully, then it would not matter how many connections are needed. – Foo Bar Jun 27 '15 at 15:37
  • It's more about of user experience, and the number of connections are also limited. We work with web development and many databases are exported from server to server, from region to region a lot of times. Edit - Also, some servers are not our own and so I can't add keys to all of them because most of them are different from time to time – Kind Jason Jun 27 '15 at 15:44

As it seems that the limitation is more on the user experience side (don't have to enter password again and again), the best choice here would be to enter your public key (the .pub file in your ~/.ssh folder) in the destinations ~/.ssh/authorized_keys file.

Here the ~ folder stands for the home of the users that are involved on the source and destination machines accordingly.

If userA@machine1 is authorized on userB@machine2, then (and only then) no password is needed when userA@machine1 opens the SSH connection to userB@machine2. The authorization is done purely by assymetric encryption (only the authorized user should have his own private key), that's why no password is needed.

This way the scp and ssh commands would still be 2 connections (but always only one at a time, not at the same time!), but both would be fully password-less.

If you don't have access to server, you have to ask the admin there to add your public key file. All machines involved just need your single public key file.

Actually, I have a cronjob running that almost does the exact same task myself (ssh to trigger dump, followed by scp to copy, even with a MySQL database), funny coincidence. ;)

  • Thanks, I guess I'm gonna have to stick with SSH keys as far as possible for now. Sorry for not voting, not enough reputation yet. – Kind Jason Jun 27 '15 at 17:49

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.