The overall picture is like this, we have 2 servers Unix / BSD / Linux OS Server 1 and Server 2 and both has up and running recent version of OpenSSH service. Server 1 has to login without password to Server 2 and be able to run only a specific lets say privileged command for the sake of security only this restricted command should be runnable.

Allowing a server (LDAP or Local User account) to authorize without password to a remote server (and being able to execute) multiple set of commands poses a security risk, thus it is a good idea to Restict OpenSSH connection to remote server to be able to run only one single command.

This is luckily possible by by default in any modern OpenSSH (which allows authenticated remote users to be limited to rights for running just single sys admin scripts or a just a set of defined scripts only to remote system, this has been a very good alternative to using a complicated sudo /etc/sudoers rules files which anyways could almost always be compromised relatively easy.

Here is the task lets say a username 'sync' has to be able to run commands on your Linux server Server 1 (with a hostname – remote-server.com) to execute a remote script mysqlbackupper.sh that does a daily Linux MySQL backupvia a predefined cronjob scheduled task that is triggered daily.

1. DSA or RSA SSH encryption pros and cons which one to choose?

Note this few interesting tihngs on which one to choose:

DSA (Digital Signature Algorithm) is faster to generate keys where in terms of encryption RSA is faster thanDSA

RSA is faster than DSA in verifying digital signature

DSA is faster than RSA generating digital signature

In Data Decryption DSA is faster than RSA

Due to fact encryption is faster in RSA and decryption faster in DSA, if performance on client side is targetted a better one to use will be RSA but if it is targetted to offload remote sshd server (lets say it is old hardware or busy machine where you don't want to put extra load on it then DSA is better choice. id_rsa and id_rsa.pub (public and private keys) are used to encrypt and decrypt the ssh (tunnel) session between the client and the server so most people would be curious which one is more secure RSA or DSA encryption. Though there are some claims that in terms of security they're more or less the same RSA is generally preferred, because encryption can be up to 4096 bits, where DSA has to be exactly 1024 bits (in the opinion of ssh-keygen). 2048 bits is ssh-keygen's default length for RSA keys, and I don't see any particular reason to use shorter ones. (The minimum possible is 768 bits; whether that's "acceptable" is situational and not recommended).

2. Generating the SSH restricted PRIV and .PUB user key pair

The authorized_key added key string value is to be generated earlier by ssh-keygen command with which we generate a key pair files:

id_dsa and id_dsa.pub

or

id_rsaid_rsa.pub

What kind of files will be generated depends on the type of encryption strength choosen be it DSA or RSA etc. the full list of available ones you can read manual (man ssh-keygen)

$ ssh Remote-Server-1 -v

$ cd /home/postgresqlback

$ ssh-keygen -t dsa

or

$ ssh-keygen -t rsa

Provide a filename and passphrase, the output files will be id_dsa / id_dsa.or id_rsa / id_rsa.pub pub key-pair and stored in ~/.ssh ofusername with which the command was run.

3. Set up /home/username/authorized_keys on Server 2

$ ssh Remote-server2 -v

Next you will have to create the authorized_keys file on the remote server wherehy you will be accessing without password and copy the content of id_rsa.pub / id_dsa.pub key to /home/username/authorized_keys (in that case it will be /home/postgresqlback/.ssh/authorized_keys) the postgresqlback was previously created with adduser on Server 2 $ chmod 600 /home/postgreback/authorized_keys

There is no special need to do anything too much special to make the SSH command restriction functionality available but just, the right record in /homeusername/.ssh/authorized_keys or if supposed to be run as root user in /home/postgreback/.ssh/authorized_keys on the server where you want to place the restriction following about a file syntax like OPTIONS-1 KEY_TYPE / OPTIONS-2 KEY_TYPE / OPTIONS-3 KEY_TYPE.

To authenticate with a key from a remote PC using the sshd service on Server 1 you need to have copied the key to /home/user/.ssh/authorized_keys (with a favourite text editor lets say vim from Server 1 to Server 2 so the file should contains the ssh public keys and users (list) allowed to passwordless login to server.

authorized_keys's data ordering (as mentioned above) is in form:

OPTIONS-1 KEY_TYPE PUBLIC_KEY_STRING COMMENT-1

… OPTIONS-2 KEY_TYPE PUBLIC_KEY_STRING COMMENT-2

…

OPTIONS-3 KEY_TYPE PUBLIC_KEY_STRING COMMENT-3

…

I've placed this example for more clarity the 4 fields of a public key string are marked with 1,2,3,4.

the reason for them is as we want to restrict port forwarding and agent-forwarding (No X will be used at all) and we don't want to have SSH local / remote orDynamic SSH tunneling enabled because of obvious (improve) security reasons.

In case if no interactive terminal will be used by the script as is the case it is also a good idea to put the no-pty next to the OPTIONS string.

If you wonder ssh-dss is not ssh-dsa, its actually a naming convention as the Digital Signature Algorithm (DSA) is published in the Digital Signature Standard (DSS) by the NIST in FIPS 186.

3. Testing restricted SSH command user run set-up

From Server-1 (after lets say logging in via ssh to it) issue:

$ ssh -i FILENAME_with_private_auth_key username@Linux-Server2 -v

Here note that the FILENAME_with_private_auth_key (is your earlier generated id_rsa / id_dsa) as this file will let anyone who have it at hands able to login to Linux-Server2 without any password authentication prompt you have to make sure this file permissions are good restricted readable only for its user owner or if run with root by root (chmod 600) might a be very good idea here.

For further executing the script via a simple user ssh to Linux-Server2 you might want to use in your trigger script or cronjob (situated on Linux Serve 1) also the -q (ssh quiet output) cmd argument:

This will make the remote script understand SHELL variables might contain anything which the remote script postgresqlbackup.sh (on Server 2) will accept as pipeline input from Server 1. Be aware that passing string arguments with has spaces or special characters inside might be problematic so always try to use a straightforward SHELL variables such as PATH, TEMP, PWD etc.

If not only predefined strings should be accepted as arguments but any arbitrary argument should be allowed to be passed to the command there is a special variable understood by sshd daemon

$SSH_ORIGINAL_COMMAND

The $SSH_ORIGINAL_COMMAND variable used in authorized_keys is a very interesting one and it really puzzled me the first time I've seen it in a Bash Shell script as I couldn't fully grasp the meaning but it turned out to be very simple as it can be used inside /usr/sbin/postgresqlbackup.sh to return any number of passed arguments lets say backup locations directories ( /usr/local /var/log /usr/bin /bin …) to the and that would be red and processed by the script

!!! AGAIN BE CAUTIOUS AND BE WARNED that without a properly crafted script anyminor error in it might be fatal, for example if the script is running with superuser credentials (root) on remote machine, some local user or a malicious attacker that gets access to the server might decide to run something likea bove's rm -rf /* might destroy your server !!!

Instead for a /usr/sbin/run-script.sh you might Contain something like:

If you're a Linux server administrator running MySQL server, you need to troubleshoot performance and bottleneck issues with the SQL database every now and then. In this article, I will pinpoint few methods to debug basic issues with MySQL database servers.

1. Troubleshooting MySQL database queries with native SQL commands

a)One way to debug errors and get general statistics is by logging in with mysql cli and check the mysql server status:

SHOW STATUS; command gives plenty of useful info, however it is not showing the exact list of queries currently processed by the SQL server. Therefore sometimes it is exactly a stucked (slow queries) execution, you need to debug in order to fix a lagging SQL. One way to track this slow queries is via enabling mysql slow-query.log. Anyways enabling the slow-query requires a MySQL server restart and some critical productive database servers are not so easy to restart and the SQL slow queries have to be tracked "on the fly" so to say. Therefore, to check the exact (slow) queries processed by the SQL server (without restarting it), do

Though this "hack" is one of the possible ways to get some interactivity on what is happening inside SQL server databases and tables table. for administering hundred or thousand SQL servers running dozens of queries per second – monitor their behaviour few times aday using mytop or mtop is times easier.

Though, the names of the two tools are quite similar and I used to think both tools are one and the same, actually they're not but both are suitable for monitoring sql database execution in real time.

As a sys admin, I've used mytop and mtop, on almost each Linux server with MySQL server installed. Both tools has helped me many times in debugging oddities with sql servers. Therefore my personal view is mytop and mtop should be along with the Linux sysadmin most useful command tools outfit, still I'm sure many administrators still haven't heard about this nice goodies.

1. Installing mytop on Debian, Ubuntu and other deb based GNU / Linux-es

mytop is available for easy install on Debian and across all debian / ubuntu and deb derivative distributions via apt.

Here is info obtained with apt-cache show

debian:~# apt-cache show mytop|grep -i description -A 3 Description: top like query monitor for MySQL Mytop is a console-based tool for monitoring queries and the performance of MySQL. It supports version 3.22.x, 3.23.x, 4.x and 5.x servers. It's written in Perl and support connections using TCP/IP and UNIX sockets.

Installing the tool is done with the trivial:

debian:~# apt-get --yes install mytop ....

mtop used to be available for apt-get-ting in Debian Lenny and prior Debian releases but in Squeeze onwards, only mytop is included (probably due to some licensing incompitabilities with mtop??).

For those curious on how mtop / mytop works – both are perl scripts written to periodically connects to the SQL server and run commands similar to SHOW FULL PROCESSLIST;. Then, the output is parsed and displayed to the user.

Here how mytop running, looks like:

2. Installing mytop on RHEL and CentOS

By default in RHEL and CentOS and probably other RedHat based Linux-es, there is neither mtop nor mytop available in package repositories. Hence installing the tools on those is only available from 3rd parties. As of time of writting an rpm builds for RHEL and CentOS, as well as (universal rpm distros) src.rpm package is available on http://pkgs.repoforge.org/mytop/. For the sake of preservation – if in future those RPMs disappear, I made a mirror of mytop rpm's here

Mytop rpm builds depend on a package perl(Term::ReadKey), my attempt to install it on CentOS 5.6, returned following err:

The perl(Term::ReadKey package is not available in CentOS 5.6 and (probably other centos releases default repositories so I had to google perl(Term::ReadKey) I found it on http://rpm.pbone.net/ package repository, the exact url to the rpm dependency as of time of writting this post is:

I personally prefer to use mtop on FreeBSD, because once run it runs prompts the user to interactively type in the user/pass

freebsd# mtop

Then mtop prompts the user with "interactive" dialog screen to type in user and pass:

It is pretty annoying, same mtop like syntax don't show user/pass prompt:

freebsd# mytop Cannot connect to MySQL server. Please check the:

* database you specified "test" (default is "test") * username you specified "root" (default is "root") * password you specified "" (default is "") * hostname you specified "localhost" (default is "localhost") * port you specified "3306" (default is 3306) * socket you specified "" (default is "") The options my be specified on the command-line or in a ~/.mytop config file. See the manual (perldoc mytop) for details. Here's the exact error from DBI. It might help you debug: Unknown database 'test'

The correct syntax to run mytop instead is:

freebsd# mytop -u root -p 'secret_password' -d 'blog'

Or the longer more descriptive:

freebsd# mytop --user root --pass 'secret_password' --database 'blog'

By the way if you take a look at mytop's manual you will notice a tiny error in documentation, where the three options –user, –pass and –database are wrongly said to be used as -user, -pass, -database:

Many of us have already taken advantage of the powerful Rsync proggie, however I'm quite sure there are still people who never used rsync to transfer files between servers.. That's why I came with this small post to possibly introduce rsync to my blog readers. Why Rsync and not Scp or SFTP? Well Rsync is designed from the start for large files transfer and optimized to do the file copying job really efficient. Some tests with scp against rsync will clearly show rsync's superiority. Rsync is also handy to contiue copying of half copied files or backups and thus in many cases saves bandwidth and machine hdd i/o operations.

Where remoteuser@remotehost — is the the username and hostname of remote server to copy files to. /remote/directory — is the directory where the rsync copied files should be stored /local/directory — is the local directory from which files will be copied to remote directory

If not a preliminary passwordless ssh key (RSA / DSA) authentication is configured on remote server, the above command will prompt for a password otherwise the rsync will start doing the transfer.

These days, I’m managing many, many servers. The servers are ordered in few groups. Each of the servers in the server groups contains identical hardware, identical Linux distribution as well as identical configuration.

Since managing multiple servers normally, takes a lot of time, applying changes to every single host loosing time in looking for the password is not a a good idea.

Thus I was forced to start managing the servers in a cluster like fashion, by executing commands on a server group using a simple for bash loop etc. To be able to use this mass execution of course I needed away either to pass on the server group password just once and issue a command on the whole server group or use a passwordless authentication ssh key pair.

Before I switched to using SSH keys to authenticate passwordless, I first tried to use a bit of tools which were claimed to be helpful in the task of executing the same commands to a group of servers. I have tested the tools pssh, sudossh and dsh but none of them was capable of logging in and executing a bunch of commands to the group of remote servers.

I gave my best to make pssh work on Debian and CentOS distributions, but even though all my experiemnts and efforts to make the so much talked about pssh to work were bad!

I’ve seen also another tool called Cluster SSH which was said to be capable of issuing commands simultaneously on a number of hosts.

Cluster SSH looked promising, however the only problem was it’s supposed to run under xterm or some kind of X graphics based terminal and therefore it did not matched my desired.

Finally I got pissed of trying these mostly useless mass command linux server administration tools and decided to come COME BACK TO THE PRIMITIVE 😉 and use the good all known, well established method of Passwordless SSH server login with ssh public/private DSA key auth.

Therefore here the problem come to this to generate one single DSA ssh authenticatoin key and replicate/copy it to the whole group of 50 servers.

These task initially seemed quite complex, but with the help of a one liner bash shell script, it seemed to be a piece of cake 😉

To achieve this task, all I had to do is: a. Generate an SSH key with ssh-keygen command and b. Use a one liner shell script to copy the generated id_rsa.pub file, to each server. and c. Create a file containig all server IP addresses to pass to the shell script.

Here are the two lines of code you will have to use to achieve these tasks:

1. Generate a DSA ssh key

linux:~# ssh-keygen -t dsa

Generating public/private dsa key pair. Enter file in which to save the key (/home/hipo/.ssh/id_dsa): y Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in y. Your public key has been saved in y.pub. The key fingerprint is: b0:28:48:a2:60:65:5a:ed:1b:9d:6c:ff:5f:37:03:e3 hipo@pc-freak.net

Here press enter few times and be sure not to fill in any passphrase when asked ’bout it.

2. Create a file containing all server IPs

Just create a file let’s say server-list.txt and make sure you include all the server IPs, one per line.

3. Use the following little script to upload the newly generated id_dsa.pub to the server list

Now you will have to paste the server password for about 50 times (if you have a file with 50 servers), however the good news is it will be just a wait and paste 50 times, if the servers are all configured to have the same root administrator pass (which was the case with me).

So until you do the boring pasting part, you can start up a nice music and enjoy 😉 Cheers 😉