Export mysql to remote server using one SSH session

Question

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?

Answer 1

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. ;)