Top 20 OpenSSH Server Best Security Practices

OpenSSH is the implementation of the SSH protocol. OpenSSH is recommended for remote login, making backups, remote file transfer via scp or sftp, and much more. SSH is perfect to keep confidentiality and integrity for data exchanged between two networks and systems. However, the main advantage is server authentication, through the use of public key cryptography. From time to time there are rumors about OpenSSH zero day exploit. Here are a few things you need to tweak in order to improve OpenSSH server security.

Default Config Files and SSH Port

  • /etc/ssh/sshd_config – OpenSSH server configuration file.
  • /etc/ssh/ssh_config – OpenSSH client configuration file.
  • ~/.ssh/ – Users ssh configuration directory.
  • ~/.ssh/authorized_keys or ~/.ssh/authorized_keys – Lists the public keys (RSA or DSA) that can be used to log into the user’s account
  • /etc/nologin – If this file exists, sshd refuses to let anyone except root log in.
  • /etc/hosts.allow and /etc/hosts.deny : Access controls lists that should be enforced by tcp-wrappers are defined here.
  • SSH default port : TCP 22

SSH Session in Action

SSH Session in Action

#1: Disable OpenSSH Server

Workstations and laptop can work without OpenSSH server. If you need not to provide the remote login and file transfer capabilities of SSH, disable and remove the SSHD server. CentOS / RHEL / Fedora Linux user can disable and remove openssh-server with yum command:
# chkconfig sshd off
# yum erase openssh-server

Debian / Ubuntu Linux user can disable and remove the same with apt-get command:
# apt-get remove openssh-server
You may need to update your iptables script to remove ssh exception rule. Under CentOS / RHEL / Fedora edit the files /etc/sysconfig/iptables and /etc/sysconfig/ip6tables. Once donerestart iptables service:
# service iptables restart
# service ip6tables restart

#2: Only Use SSH Protocol 2

SSH protocol version 1 (SSH-1) has man-in-the-middle attacks problems and security vulnerabilities. SSH-1 is obsolete and should be avoided at all cost. Open sshd_config file and make sure the following line exists:

Protocol 2

#3: Limit Users’ SSH Access

By default all systems user can login via SSH using their password or public key. Sometime you create UNIX / Linux user account for ftp or email purpose. However, those user can login to system using ssh. They will have full access to system tools including compilers and scripting languages such as Perl, Python which can open network ports and do many other fancy things. One of my client has really outdated php script and an attacker was able to create a new account on the system via a php script. However, attacker failed to get into box via ssh because it wasn’t in AllowUsers.

Only allow root, vivek and jerry user to use the system via SSH, add the following to sshd_config:

AllowUsers root vivek jerry

Alternatively, you can allow all users to login via SSH but deny only a few users, with the following line:

DenyUsers saroj anjali foo

You can also configure Linux PAM allows or deny login via the sshd server. You can allow list of group name to access or deny access to the ssh.

#4: Configure Idle Log Out Timeout Interval

User can login to server via ssh and you can set an idel timeout interval to avoid unattended ssh session. Open sshd_config and make sure following values are configured:

ClientAliveInterval 300
ClientAliveCountMax 0

You are setting an idle timeout interval in seconds (300 secs = 5 minutes). After this interval has passed, the idle user will be automatically kicked out (read as logged out). See how to automatically log BASH / TCSH / SSH users out after a period of inactivity for more details.

#5: Disable .rhosts Files

Don’t read the user’s ~/.rhosts and ~/.shosts files. Update sshd_config with the following settings:

IgnoreRhosts yes

SSH can emulate the behavior of the obsolete rsh command, just disable insecure access via RSH.

#6: Disable Host-Based Authentication

To disable host-based authentication, update sshd_config with the following option:

HostbasedAuthentication no

#7: Disable root Login via SSH

There is no need to login as root via ssh over a network. Normal users can use su or sudo (recommended) to gain root level access. This also make sure you get full auditing information about who ran privileged commands on the system via sudo. To disable root login via SSH, update sshd_config with the following line:

PermitRootLogin no

However, bob made excellent point:

Saying “don’t login as root” is h******t. It stems from the days when people sniffed the first packets of sessions so logging in as yourself and su-ing decreased the chance an attacker would see the root pw, and decreast the chance you got spoofed as to your telnet host target, You’d get your password spoofed but not root’s pw. Gimme a break. this is 2005 – We have ssh, used properly it’s secure. used improperly none of this 1989 will make a damn bit of difference. -Bob

#8: Enable a Warning Banner

Set a warning banner by updating sshd_config with the following line:

Banner /etc/issue

Sample /etc/issue file:

You are accessing a XYZ Government (XYZG) Information System (IS) that is provided for authorized use only.
By using this IS (which includes any device attached to this IS), you consent to the following conditions:
+ The XYZG routinely intercepts and monitors communications on this IS for purposes including, but not limited to,
penetration testing, COMSEC monitoring, network operations and defense, personnel misconduct (PM),
law enforcement (LE), and counterintelligence (CI) investigations.
+ At any time, the XYZG may inspect and seize data stored on this IS.
+ Communications using, or data stored on, this IS are not private, are subject to routine monitoring,
interception, and search, and may be disclosed or used for any XYZG authorized purpose.
+ This IS includes security measures (e.g., authentication and access controls) to protect XYZG interests--not
for your personal benefit or privacy.
+ Notwithstanding the above, using this IS does not constitute consent to PM, LE or CI investigative searching
or monitoring of the content of privileged communications, or work product, related to personal representation
or services by attorneys, psychotherapists, or clergy, and their assistants. Such communications and work
product are private and confidential. See User Agreement for details.

Above is standard sample, consult your legal team for exact user agreement and legal notice details.

#8: Firewall SSH Port # 22

You need to firewall ssh port # 22 by updating iptables or pf firewall configurations. Usually, OpenSSH server must only accept connections from your LAN or other remote WAN sites only.

Netfilter (Iptables) Configuration

Update /etc/sysconfig/iptables (Redhat and friends specific file) to accept connection only from and, enter:

-A RH-Firewall-1-INPUT -s -m state --state NEW -p tcp --dport 22 -j ACCEPT
-A RH-Firewall-1-INPUT -s -m state --state NEW -p tcp --dport 22 -j ACCEPT

If you’ve dual stacked sshd with IPv6, edit /etc/sysconfig/ip6tables (Redhat and friends specific file), enter:

 -A RH-Firewall-1-INPUT -s ipv6network::/ipv6mask -m tcp -p tcp --dport 22 -j ACCEPT

Replace ipv6network::/ipv6mask with actual IPv6 ranges.

*BSD PF Firewall Configuration

If you are using PF firewall update /etc/pf.conf as follows:

pass in on $ext_if inet proto tcp from {,} to $ssh_server_ip port ssh flags S/SA synproxy state

#9: Change SSH Port and Limit IP Binding

By default SSH listen to all available interfaces and IP address on the system. Limit ssh port binding and change ssh port (by default brute forcing scripts only try to connects to port # 22). To bind to and IPs and to port 300, add or correct the following line:

Port 300

A better approach to use proactive approaches scripts such as fail2ban or denyhosts (see below).

#10: Use Strong SSH Passwords and Passphrase

It cannot be stressed enough how important it is to use strong user passwords and passphrase for your keys. Brute force attack works because you use dictionary based passwords. You can force users to avoid passwords against a dictionary attack and use john the ripper tool to find out existing weak passwords. Here is a sample random password generator (put in your ~/.bashrc):

genpasswd() {
	local l=$1
       	[ "$l" == "" ] && l=20
      	tr -dc A-Za-z0-9_ < /dev/urandom | head -c ${l} | xargs

Run it:
genpasswd 16


#11: Use Public Key Based Authentication

Use public/private key pair with password protection for the private key. See how to use RSAand DSA key based authentication. Never ever use passphrase free key (passphrase key less) login.

#12: Use Keychain Based Authentication

keychain is a special bash script designed to make key-based authentication incredibly convenient and flexible. It offers various security benefits over passphrase-free keys. See how to setup and use keychain software.

#13: Chroot SSHD (Lock Down Users To Their Home Directories)

By default users are allowed to browse the server directories such as /etc/, /bin and so on. You can protect ssh, using os based chroot or use special tools such as rssh. With the release of OpenSSH 4.8p1 or 4.9p1, you no longer have to rely on third-party hacks such as rssh or complicated chroot(1) setups to lock users to their home directories. See this blog post about new ChrootDirectory directive to lock down users to their home directories.

#14: Use TCP Wrappers

TCP Wrapper is a host-based Networking ACL system, used to filter network access to Internet. OpenSSH does supports TCP wrappers. Just update your /etc/hosts.allow file as follows to allow SSH only from :

sshd :

See this FAQ about setting and using TCP wrappers under Linux / Mac OS X and UNIX like operating systems.

#15: Disable Empty Passwords

You need to explicitly disallow remote login from accounts with empty passwords, update sshd_config with the following line:

PermitEmptyPasswords no

#16: Thwart SSH Crackers (Brute Force Attack)

Brute force is a method of defeating a cryptographic scheme by trying a large number of possibilities using a single or distributed computer network. To prevents brute force attacks against SSH, use the following softwares:

  • DenyHosts is a Python based security tool for SSH servers. It is intended to prevent brute force attacks on SSH servers by monitoring invalid login attempts in the authentication log and blocking the originating IP addresses.
  • Explains how to setup DenyHosts under RHEL / Fedora and CentOS Linux.
  • Fail2ban is a similar program that prevents brute force attacks against SSH.
  • security/sshguard-pf protect hosts from brute force attacks against ssh and other services using pf.
  • security/sshguard-ipfw protect hosts from brute force attacks against ssh and other services using ipfw.
  • security/sshguard-ipfilter protect hosts from brute force attacks against ssh and other services using ipfilter.
  • security/sshblock block abusive SSH login attempts.
  • security/sshit checks for SSH/FTP bruteforce and blocks given IPs.
  • BlockHosts Automatic blocking of abusive IP hosts.
  • Blacklist Get rid of those bruteforce attempts.
  • Brute Force Detection A modular shell script for parsing application logs and checking for authentication failures. It does this using a rules system where application specific options are stored including regular expressions for each unique auth format.
  • IPQ BDB filter May be considered as a fail2ban lite.

#17: Rate-limit Incoming Port # 22 Connections

Both netfilter and pf provides rate-limit option to perform simple throttling on incoming connections on port # 22.

Iptables Example

The following example will drop incoming connections which make more than 5 connection attempts upon port 22 within 60 seconds:

$IPT -I INPUT -p tcp --dport ${ssh_port} -i ${inet_if} -m state --state NEW -m recent  --set
$IPT -I INPUT -p tcp --dport ${ssh_port} -i ${inet_if} -m state --state NEW -m recent  --update --seconds 60 --hitcount 5 -j DROP

Call above script from your iptables scripts. Another config option:

$IPT -A INPUT  -i ${inet_if} -p tcp --dport ${ssh_port} -m state --state NEW -m limit --limit 3/min --limit-burst 3 -j ACCEPT
$IPT -A INPUT  -i ${inet_if} -p tcp --dport ${ssh_port} -m state --state ESTABLISHED -j ACCEPT
$IPT -A OUTPUT -o ${inet_if} -p tcp --sport ${ssh_port} -m state --state ESTABLISHED -j ACCEPT
# another one line example
# $IPT -A INPUT -i ${inet_if} -m state --state NEW,ESTABLISHED,RELATED -p tcp --dport 22 -m limit --limit 5/minute --limit-burst 5-j ACCEPT

See iptables man page for more details.

*BSD PF Example

The following will limits the maximum number of connections per source to 20 and rate limit the number of connections to 15 in a 5 second span. If anyone breaks our rules add them to our abusive_ips table and block them for making any further connections. Finally, flush keyword kills all states created by the matching rule which originate from the host which exceeds these limits.

table <abusive_ips> persist
block in quick from <abusive_ips>
pass in on $ext_if proto tcp to $sshd_server_ip port ssh flags S/SA keep state (max-src-conn 20, max-src-conn-rate 15/5, overload <abusive_ips> flush)

#18: Use Port Knocking

Port knocking is a method of externally opening ports on a firewall by generating a connection attempt on a set of prespecified closed ports. Once a correct sequence of connection attempts is received, the firewall rules are dynamically modified to allow the host which sent the connection attempts to connect over specific port(s). A sample port Knocking example for ssh using iptables:

$IPT -N stage1
$IPT -A stage1 -m recent --remove --name knock
$IPT -A stage1 -p tcp --dport 3456 -m recent --set --name knock2

$IPT -N stage2
$IPT -A stage2 -m recent --remove --name knock2
$IPT -A stage2 -p tcp --dport 2345 -m recent --set --name heaven

$IPT -N door
$IPT -A door -m recent --rcheck --seconds 5 --name knock2 -j stage2
$IPT -A door -m recent --rcheck --seconds 5 --name knock -j stage1
$IPT -A door -p tcp --dport 1234 -m recent --set --name knock

$IPT -A INPUT -p tcp --dport 22 -m recent --rcheck --seconds 5 --name heaven -j ACCEPT
$IPT -A INPUT -p tcp --syn -j doo
  • fwknop is an implementation that combines port knocking and passive OS fingerprinting.
  • Multiple-port knocking Netfilter/IPtables only implementation.

#19: Use Log Analyzer

Read your logs using logwatch or logcheck. These tools make your log reading life easier. It will go through your logs for a given period of time and make a report in the areas that you wish with the detail that you wish. Make sure LogLevel is set to INFO or DEBUG in sshd_config:

LogLevel INFO

#20: Patch OpenSSH and Operating Systems

It is recommended that you use tools such as yumapt-getfreebsd-update and others to keep systems up to date with the latest security patches.

Other Options

To hide openssh version, you need to update source code and compile openssh again. Make sure following options are enabled in sshd_config:

#  Turn on privilege separation
UsePrivilegeSeparation yes
# Prevent the use of insecure home directory and key file permissions
StrictModes yes
# Turn on  reverse name checking
VerifyReverseMapping yes
# Do you need port forwarding?
AllowTcpForwarding no
X11Forwarding no
#  Specifies whether password authentication is allowed.  The default is yes.
PasswordAuthentication no

Verify your sshd_config file before restarting / reloading changes:
# /usr/sbin/sshd -t

Tighter SSH security with two-factor or three-factor (or more) authentication.


Copying a Disk Image Over SSH

  1. Enter the following command on the receiving machine. Replace with the Linode’s IP address and /home/archive/linode.img with the path where you want to store the disk image:
    ssh root@ "dd if=/dev/xvda " | dd of=/home/archive/linode.img
  2. The receiving machine will connect to the Linode. Type yes and press Enter to continue connecting:
    The authenticity of host ' (' can't be established.
    RSA key fingerprint is 39:6b:eb:05:f1:28:95:f0:da:63:17:9e:6b:6b:11:4a.
    Are you sure you want to continue connecting (yes/no)? yes
  3. Enter the root password for the Linode, as shown below
    Warning: Permanently added '' (RSA) to the list of known hosts.
    root@'s password:
  4. The transfer starts. You’ll see output similar to the following.
    4096000+0 records in
    4096000+0 records out
    2097152000 bytes (2.1 GB) copied, 371.632 seconds, 5.6 MB/s
    4096000+0 records in
    4096000+0 records out
    2097152000 bytes (2.1 GB) copied, 364.002 s, 5.8 MB/s

Note that copying your disk image can take a while. Be patient!

Verifying the Disk Image

Once the copy has completed, you can verify it by mounting the image on the receiving machine. Here’s how:

  1. Log in to the receiving machine as root by entering the following command and entering the root user’s password:
  2. Make a directory on the receiving machine by entering the following command:
    mkdir linode
  3. Mount the disk image by entering the following command, replacing linode.img with the name of the disk image:
    mount -o loop linode.img linode
  4. View the directories stored on the disk image by entering the following command:
    ls linode/
  5. You should see the directories on the disk images, similar to the ones shown below:
    bin   dev  home  lost+found  mnt  proc  sbin     srv  tmp  var
    boot  etc  lib   media       opt  root  selinux  sys  usr

Repartitioning a Linux Cloud Server


A standard Rackspace Linux Cloud Server image is set up with two partitions: one for the root file system and the other for the swap partition. This partition layout is sufficient for most servers and you usually won’t need to change it. However, there are circumstances where customised partitioning is required. If you need custom partitions, read on.

One of the common Rackspace Cloud hypervisors (the backend software running everything) is Citrix Xen Server 5.x, the default for our newer stock images. If your Cloud server is running on this platform it will have two block devices defined, /dev/xvda and /dev/xvdc. The /dev/xvda1 device houses the root partition with all your data and operating system, while /dev/xvdc1 operates as the swap partition.

Enter Rescue Mode

The root partition contains the active operating system, so we have to put the file system off-line before we can change its partition size. We’ll do this by putting the Cloud Server in rescue mode.

All commands used in this article are based on a 256MB CentOS Cloud Server but should apply to Cloud Servers on other Linux distributions as well.

Locate Root Partition

Once you’ve put your server into rescue mode, log on via ssh and run this command at the command prompt:

fdisk -l | grep -B1 xvd

Disk /dev/xvdc: 536 MB, 536870912 bytes

Device Boot Start End Blocks Id System
/dev/xvdc1 1 65 522112 82 Linux swap / Solaris

Disk /dev/xvdd: 536 MB, 536870912 bytes

Device Boot Start End Blocks Id System
/dev/xvdd1 1 65 522112 82 Linux swap / Solaris

Disk /dev/xvda: 10.2 GB, 10200547328 bytes

Device Boot Start End Blocks Id System
/dev/xvda1 * 1 1241 9960448 83 Linux

Disk /dev/xvdb: 10.2 GB, 10200547328 bytes

Device Boot Start End Blocks Id System
/dev/xvdb1 * 1 1241 9960448 83 Linux
The output shows 4 block devices. The smallest 2 are swap partitions; one is for your Cloud Server, the other is for the rescue mode live Linux system. Similarly, /dev/xvda and /dev/xvdb are block devices for the root partitions. Looks confusing, but if you run:

mount | grep xvd
You’ll see the output:

/dev/xvda1 on / type ext3 (rw,noatime,barrier=0)
From the output we see that /dev/xvda1 is the mounted root filesystem for the Live Linux instance, so /dev/xvdb is your server’s root filesystem.

Shrink File System

Let’s make sure the file system is good before we modify it by running:

fsck.ext3 -fy /dev/xvdb1

e2fsck 1.41.12 (17-May-2010)
/dev/xvdb1: recovering journal
Pass 1: Checking inodes, blocks, and sizes
Pass 2: Checking directory structure
Pass 3: Checking directory connectivity
Pass 4: Checking reference counts
Pass 5: Checking group summary information

/dev/xvdb1: ***** FILE SYSTEM WAS MODIFIED *****
/dev/xvdb1: 22221/622592 files (0.3% non-contiguous), 237966/2490112 blocks
Now we’ll remove journaling from the file system:

tune2fs -O ^has_journal /dev/xvdb1
Next check the current data size on the partition:

mount -oro /dev/xvdb1 /mnt; df -h /dev/xvdb1; umount /mnt

Filesystem Size Used Avail Use% Mounted on
/dev/xvdb1 9.4G 649M 8.3G 8% /mnt
In the example above 649MB of 9.4GB is used. We need to account for the space used by the operating system when we change the partition size. For our example we’ll plan to shrink it to 4GB.

To be on the safe side we’ll actually start by shrinking the root file system to a bit under our target (in this case, 3GB) to make sure it will fit the new partition. We’ll expand it to 4GB once we have our partition sizes set.

Shrink the file system to 3GB with the command:

resize2fs /dev/xvdb1 3G
If successful the output will look like this:

resize2fs 1.41.12 (17-May-2010)
Resizing the filesystem on /dev/xvdb1 to 786432 (4k) blocks.
The filesystem on /dev/xvdb1 is now 786432 blocks long.
If we check the size again:

mount -oro /dev/xvdb1 /mnt; df -h /dev/xvdb1; umount /mnt
We see:

Filesystem Size Used Avail Use% Mounted on
/dev/xvdb1 3.0G 642M 2.2G 23% /mnt
Hooray, the root file system is changed! The new file system is much smaller than the partition it resides. You may notice the used size value has changed from before. That reflects the file system storing metadata that describes the partition – a smaller partition needs less metadata space. There is no data lost.

Shrink Partition

This is the most dangerous part of the whole task. The file system will be damaged if the disk space it requires for files is larger than partition where it resides. Make sure you have a backup of your data.

Before we make any changes we’ll check the current partition layout with this command:

sfdisk -uS -l /dev/xvdb

Disk /dev/xvdb: 1240 cylinders, 255 heads, 63 sectors/track
Units = sectors of 512 bytes, counting from 0
Device Boot Start End #sectors Id System
/dev/xvdb1 * 2048 19922943 19920896 83 Linux
/dev/xvdb2 0 – 0 0 Empty
/dev/xvdb3 0 – 0 0 Empty
/dev/xvdb4 0 – 0 0 Empty
As you can see from the output, there is only one partition covering the whole disk. Let’s save the current partition layout to a file called xvdb.out like this:

sfdisk -d /dev/xvdb > /root/xvdb.out
And use a text editor such as nano to open xvdb.out:

nano /root/xvdb.out
You will see the size of xvdb1 is 19920896, which is the maximum size for a partition on a 256MB Cloud Server. You may have a bigger Cloud Server and see a bigger partition size. The unit used in the size measurement is the “sector”; one sector is 512 bytes as you can see from the output of “sfdisk -uS -l /dev/xvdb”. That means the size value for 4GB is:

4GB / unit size = 4*1024*1024*1024 bytes / 512 bytes = 8388608
Edit the /root/xvbd.out file to change the size value for the /dev/xvdb1 partition to what we want. For our example server the end result will look like this:

# partition table of /dev/xvdb
unit: sectors

/dev/xvdb1 : start= 2048, size= 8388608, Id=83, bootable
/dev/xvdb2 : start= 0, size= 0, Id= 0
/dev/xvdb3 : start= 0, size= 0, Id= 0
/dev/xvdb4 : start= 0, size= 0, Id= 0
We save and exit. Then apply the new partition layout back to /dev/xvdb:

sfdisk –force /dev/xvdb < /root/xvdb.out

Checking that no-one is using this disk right now …

Disk /dev/xvdb: 1240 cylinders, 255 heads, 63 sectors/track
Old situation:
Units = cylinders of 8225280 bytes, blocks of 1024 bytes, counting from 0

Device Boot Start End #cyls #blocks Id System
/dev/xvdb1 * 0+ 1240- 1241- 9960448 83 Linux
/dev/xvdb2 0 – 0 0 0 Empty
/dev/xvdb3 0 – 0 0 0 Empty
/dev/xvdb4 0 – 0 0 0 Empty
New situation:
Warning: The partition table looks like it was made
for C/H/S=*/76/1 (instead of 1240/255/63).
For this listing I’ll assume that geometry.
Units = sectors of 512 bytes, counting from 0

Device Boot Start End #sectors Id System
/dev/xvdb1 * 2048 8390655 8388608 83 Linux
start: (c,h,s) expected (26,72,1) found (0,32,33)
end: (c,h,s) expected (1023,75,1) found (522,75,1)
/dev/xvdb2 0 – 0 0 Empty
/dev/xvdb3 0 – 0 0 Empty
/dev/xvdb4 0 – 0 0 Empty
Warning: partition 1 does not end at a cylinder boundary
Successfully wrote the new partition table

Re-reading the partition table …

If you created or changed a DOS partition, /dev/foo7, say, then use dd(1)
to zero the first 512 bytes: dd if=/dev/zero of=/dev/foo7 bs=512 count=1
(See fdisk(8).)
The /dev/xvdb1 partition is now changed to 4GB.

Align File System Size

Now we are going to expand the file system to fill the new partition, changing the file system from 3GB to 4GB.

resize2fs /dev/xvdb1

resize2fs 1.41.12 (17-May-2010)
Resizing the filesystem on /dev/xvdb1 to 1048576 (4k) blocks.
The filesystem on /dev/xvdb1 is now 1048576 blocks long.
Verify what we did by running:

mount -oro /dev/xvdb1 /mnt; df -h /dev/xvdb1; umount /mnt

Filesystem Size Used Avail Use% Mounted on
/dev/xvdb1 4.0G 647M 3.2G 17% /mnt
All good, the new size of 4.0G is what we expected.

Enable Journaling

Before we finish, don’t forget to enable journaling for the file system, since we disabled it earlier. An EXT3 file system without journaling is EXT2 – that will still work, but it wouldn’t have the safeguards of EXT3.

Enabling journaling is easy:

tune2fs -j /dev/xvdb1

tune2fs 1.41.12 (17-May-2010)

Creating journal inode: done
This filesystem will be automatically checked every -1 mounts or
0 days, whichever comes first. Use tune2fs -c or -i to override.
Exit Rescue Mode

By now we’re all done shrinking the old root partition.

Go back to the Cloud Control Panel and have your server exit rescue mode. When it comes back up, connect to the server via SSH or the console.

Create New Partition

Assuming there are no problems, everything is ready. You can create new partitions from the free space you extracted from the root partition /dev/xvda1.

Before the change:

fdisk -l /dev/xvda | grep -B1 xvd

Disk /dev/xvda: 10.2 GB, 10200547328 bytes

Device Boot Start End Blocks Id System
/dev/xvda1 27 110404 4194304 83 Linux
Let us create a new partition as an example. The single English letters in bold are what you need to key-in to respond to prompts:

fdisk -cu /dev/xvda
Command (m for help): n
Command action
e extended
p primary partition (1-4)
Partition number (1-4): 2
First sector (8390656-19922943, default 8390656):
Using default value 8390656
Last sector, +sectors or +size{K,M,G} (8390656-19922943, default 19922943):
Using default value 19922943

Command (m for help): wq
The partition table has been altered!

Calling ioctl() to re-read partition table.

WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table. The new table will be used at
the next reboot or after you run partprobe(8) or kpartx(8)
Syncing disks.
Let’s check the partitions again:

fdisk -l /dev/xvda | grep -B1 xvd

Disk /dev/xvda: 10.2 GB, 10200547328 bytes

Device Boot Start End Blocks Id System
/dev/xvda1 27 110404 4194304 83 Linux
/dev/xvda2 110404 262144 5766144 83 Linux
As you can see from the output above, you have a new partition /dev/xvda2. You may reboot your server:

Then log back in and format the new partition:

mkfs -t ext3 /dev/xvda2
Finally, mount it to wherever you want such as /mnt like this:

mount /dev/xvda2 /mnt
All done, let’s check:

df -h

Filesystem Size Used Avail Use% Mounted on
/dev/xvda1 4.0G 775M 3.0G 21% /
tmpfs 120M 0 120M 0% /dev/shm
/dev/xvda2 5.5G 140M 5.1G 3% /mnt
I hope you enjoyed this adventure. Partitioning your server can give you a lot of freedom with regard to how you allocate your space and what file systems your server uses.

Remember that if you grow your instance later, the system will not repartition for you – you’ll need to manually adjust your partitions to use any new space allocated to your virtual disk.

How to remove MySQL completely with config and library files

With the command:

sudo apt-get remove --purge mysql\*

you can delete anything related to packages named mysql. Those commands is only valid on debian / debian based linux distributions (for eg. ubuntu).

You can list all mysql packages installed with:

sudo dpkg -l | grep -i mysql

For more cleanup for package cache you can:

sudo apt-get clean

Remember to:

sudo updatedb

Otherwise the “locate” command will display old data.

To install mysql again, use the following command:

sudo apt-get install libmysqlclient-dev mysql-client

This will install mysql client, libmysql and its headers files.

To install the server, run this one:

sudo apt-get install mysql-server

MySQL Commands

To login (from unix shell) use -h only if needed.

# [mysql dir]/bin/mysql -h hostname -u root -p

Create a database on the sql server.

mysql> create database [databasename];

List all databases on the sql server.

mysql> show databases;

Switch to a database.

mysql> use [db name];

To see all the tables in the db.

mysql> show tables;

To see database’s field formats.

mysql> describe [table name];

To delete a db.

mysql> drop database [database name];

To delete a table.

mysql> drop table [table name];

Show all data in a table.

mysql> SELECT * FROM [table name];

Returns the columns and column information pertaining to the designated table.

mysql> show columns from [table name];

Show certain selected rows with the value “whatever”.

mysql> SELECT * FROM [table name] WHERE [field name] = “whatever”;

Show all records containing the name “Bob” AND the phone number ‘3444444’.

mysql> SELECT * FROM [table name] WHERE name = “Bob” AND phone_number = ‘3444444’;

Show all records not containing the name “Bob” AND the phone number ‘3444444’ order by the phone_number field.

mysql> SELECT * FROM [table name] WHERE name != “Bob” AND phone_number = ‘3444444’ order by phone_number;

Show all records starting with the letters ‘bob’ AND the phone number ‘3444444’.

mysql> SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ‘3444444’;

Show all records starting with the letters ‘bob’ AND the phone number ‘3444444’ limit to records 1 through 5.

mysql> SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ‘3444444’ limit 1,5;

Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with a.

mysql> SELECT * FROM [table name] WHERE rec RLIKE “^a”;

Show unique records.

mysql> SELECT DISTINCT [column name] FROM [table name];

Show selected records sorted in an ascending (asc) or descending (desc).

mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

Return number of rows.

mysql> SELECT COUNT(*) FROM [table name];

Sum column.

mysql> SELECT SUM(*) FROM [table name];

Join tables on common columns.

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES(‘%’,’username’,PASSWORD(‘password’));
mysql> flush privileges;

Change a users password from unix shell.

# [mysql dir]/bin/mysqladmin -u username -h -p password ‘new-password’

Change a users password from MySQL prompt. Login as root. Set the password. Update privs.

# mysql -u root -p
mysql> SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘passwordhere’);
mysql> flush privileges;

Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.

# /etc/init.d/mysql stop
# mysqld_safe –skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD(“newrootpassword”) where User=’root’;
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

Set a root password if there is on root password.

# mysqladmin -u root password newpassword

Update a root password.

# mysqladmin -u root -p oldpassword newpassword

Allow the user “bob” to connect to the server from localhost using the password “passwd”. Login as root. Switch to the MySQL db. Give privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by ‘passwd’;
mysql> flush privileges;

Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (‘%’,’databasename’,’username’,’Y’,’Y’,’Y’,’Y’,’Y’,’N’);
mysql> flush privileges;


mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

To update info already in a table.

mysql> UPDATE [table name] SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where [field name] = ‘user’;

Delete a row(s) from a table.

mysql> DELETE from [table name] where [field name] = ‘whatever’;

Update database permissions/privilages.

mysql> flush privileges;

Delete a column.

mysql> alter table [table name] drop column [column name];

Add a new column to db.

mysql> alter table [table name] add column [new column name] varchar (20);

Change column name.

mysql> alter table [table name] change [old column name] [new column name] varchar (50);

Make a unique column so you get no dupes.

mysql> alter table [table name] add unique ([column name]);

Make a column bigger.

mysql> alter table [table name] modify [column name] VARCHAR(3);

Delete unique from table.

mysql> alter table [table name] drop index [colmn name];

Load a CSV file into a table.

mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3);

Dump all databases for backup. Backup file is sql commands to recreate all db’s.

# [mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql

Dump one database for backup.

# [mysql dir]/bin/mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql

Dump a table from a database.

# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.

# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

Create Table Example 1.

mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

Create Table Example 2.

mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default ‘bato’);

CentOS / RHEL Linux Install Postgresql Database Server

The postgresql-server package includes the programs needed to create and run a PostgreSQL server, which will in turn allow you to create
and maintain PostgreSQL databases. PostgreSQL is an advanced Object-Relational database management system (DBMS) that supports almost all SQL constructs (including transactions, subselects and user-defined types and functions). You should install postgresql-server if you want to create and maintain your own PostgreSQL databases and/or your own PostgreSQL server. You also need to install the postgresql package.

You also need to install client package called postgrsql. This package contains the docs in HTML for the whole package, as well as command-line utilities for managing PostgreSQL databases on a PostgreSQL server.

Install Postgrsql Server

Login as the root user and enter the command:
# yum install postgresql postgresql-server


Loading “installonlyn” plugin
Setting up Install Process
Setting up repositories
Reading repository metadata in from local files
Parsing package install arguments
Resolving Dependencies
–> Populating transaction set with selected packages. Please wait.
—> Downloading header for postgresql-server to pack into transaction set.
postgresql-server-8.1.9-1 100% |=========================| 87 kB 00:00
—> Package postgresql-server.i386 0:8.1.9-1.el5 set to be updated
–> Running transaction check
–> Processing Dependency: postgresql = 8.1.9-1.el5 for package: postgresql-server
–> Restarting Dependency Resolution with new changes.
–> Populating transaction set with selected packages. Please wait.
—> Downloading header for postgresql to pack into transaction set.
postgresql-8.1.9-1.el5.i3 100% |=========================| 119 kB 00:00
—> Package postgresql.i386 0:8.1.9-1.el5 set to be updated
–> Running transaction check
Dependencies Resolved
Package Arch Version Repository Size
postgresql-server i386 8.1.9-1.el5 updates 4.0 M
Installing for dependencies:
postgresql i386 8.1.9-1.el5 updates 2.8 M
Transaction Summary
Install 2 Package(s)
Update 0 Package(s)
Remove 0 Package(s)
Total download size: 6.8 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): postgresql-server- 100% |=========================| 4.0 MB 00:05
(2/2): postgresql-8.1.9-1 100% |=========================| 2.8 MB 00:04
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing: postgresql ######################### [1/2]
Installing: postgresql-server ######################### [2/2]
Installed: postgresql-server.i386 0:8.1.9-1.el5
Dependency Installed: postgresql.i386 0:8.1.9-1.el5
Start Postgresql

Type the following two command:
# chkconfig postgresql on
# service postgresql start


Initializing database: [ OK ]
Starting postgresql service: [ OK ]
Connect to Postgresql Server

Connect to server using
# su – postgres

Connect using psql command line tool:
$ psql -d template1 -U postgres


Welcome to psql 8.1.9, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
Open TCP port 5432

Finally make sure iptables allows remote access to Postgresql database server:
Open /etc/sysconfig/iptables file:
# vi /etc/sysconfig/iptables

Append following line before COMMIT line to open port 5432:

-A RH-Firewall-1-INPUT -m state –state NEW -m tcp -p tcp –dport 5432 -j ACCEPT
Save and close the file. Restart the firewall:
# service iptables restart