Saturday 5 October 2013

Passwordless ssh login

You can login to a remote Linux server without entering password in 3 simple steps using ssky-keygen and ssh-copy-id as explained in this article.

ssh-keygen creates the public and private keys. ssh-copy-id copies the local-host’s public key to the remote-host’s authorized_keys file. ssh-copy-id also assigns proper permission to the remote-host’s home, ~/.ssh, and ~/.ssh/authorized_keys.

This article also explains 3 minor annoyances of using ssh-copy-id and how to use ssh-copy-id along with ssh-agent.

Step 1: Create public and private keys using ssh-key-gen on local-host

jsmith@local-host$ [Note: You are on local-host here]

jsmith@local-host$ ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/home/jsmith/.ssh/id_rsa):[Enter key]
Enter passphrase (empty for no passphrase): [Press enter key]
Enter same passphrase again: [Pess enter key]
Your identification has been saved in /home/jsmith/.ssh/id_rsa.
Your public key has been saved in /home/jsmith/.ssh/id_rsa.pub.
The key fingerprint is:
33:b3:fe:af:95:95:18:11:31:d5:de:96:2f:f2:35:f9 jsmith@local-host

Step 2: Copy the public key to remote-host using ssh-copy-id

jsmith@local-host$ ssh-copy-id -i ~/.ssh/id_rsa.pub remote-host
jsmith@remote-host's password:
Now try logging into the machine, with "ssh 'remote-host'", and check in:

.ssh/authorized_keys

to make sure we haven't added extra keys that you weren't expecting.
Note: ssh-copy-id appends the keys to the remote-host’s .ssh/authorized_key.

Step 3: Login to remote-host without entering the password

jsmith@local-host$ ssh remote-host
Last login: Sun Nov 16 17:22:33 2008 from 192.168.1.2
[Note: SSH did not ask for password.]

jsmith@remote-host$ [Note: You are on remote-host here]

The above 3 simple steps should get the job done in most cases.

Note:
Recently I started to get  Agent admitted failure to sign using the key on ubuntu 12.04, but this solved the problem:

# start the ssh-agent in the background
eval "$(ssh-agent -s)

After setting that up refer to "Disable root login" to disable root login for extra security. 

Friday 4 October 2013

Install CSF proxy on Debian 6

Step 1: Downloading


Config Server Firewall is not currently available in Debian or Ubuntu repositories, and has to be downloaded from the ConfigServer's website.
wget http://www.configserver.com/free/csf.tgz
This will download CSF to your current working directory.

Step 2: Uncompressing


The downloaded file is a compressed from of tar package, and has to be uncompressed and extracted before it can be used.
tar -xzf csf.tgz

Step 3: Installing



Now it is time to execute the CSF's installer script.
cd csf
sh install.sh

The firewall is now installed, but you should check if the required iptables modules are available.
perl /usr/local/csf/bin/csftest.pl

The firewall will work if no fatal errors are reported.

Note: Your IP address was added to the whitelist if possible. In addition, the SSH port has been opened automatically, even if it uses custom port. The firewall was also configured to have testing mode enabled, which means that the iptables rules will be automatically removed five minutes after starting CSF. This should be disabled once you know that your configuration works, and you will not be locked out.

Basic Configuration


CSF can be configured by editing its configuration file csf.conf in /etc/csf:
nano /etc/csf/csf.conf
The changes can be applied with command:
csf -r

Step 1: Configuring ports


The less access there is to your VPS, the more secure your server is. However, not all ports can be closed as the clients must be able to use your services. 

The ports opened by default are the following:
TCP_IN = "20,21,22,25,53,80,110,143,443,465,587,993,995"

TCP_OUT = "20,21,22,25,53,80,110,113,443"

UDP_IN = "20,21,53"

UDP_OUT = "20,21,53,113,123"
Services using the open ports:

  • Port 20: FTP data transfer
  • Port 21: FTP control
  • Port 22: Secure shell (SSH)
  • Port 25: Simple mail transfer protocol (SMTP)
  • Port 53: Domain name system (DNS)
  • Port 80: Hypertext transfer protocol (HTTP)
  • Port 110: Post office protocol v3 (POP3)
  • Port 113: Authentication service/identification protocol
  • Port 123: Network time protocol (NTP)
  • Port 143: Internet message access protocol (IMAP)
  • Port 443: Hypertext transfer protocol over SSL/TLS (HTTPS)
  • Port 465: URL Rendesvous Directory for SSM (Cisco)
  • Port 587: E-mail message submission (SMTP)
  • Port 993: Internet message access protocol over SSL (IMAPS)
  • Port 995: Post office protocol 3 over TLS/SSL (POP3S)
It is possible that you are not using all of these services, so you can close the ports that are not used. I would recommend closing all ports (removing port number form the list), and then adding the ports you need.

Below are port sets that should be opened if you are running the listed service:

On any server:
TCP_IN: 22,53
TCP_OUT: 22,53,80,113,443
UPD_IN: 53
UPD_OUT: 53,113,123
Apache:
TCP_IN: 80,443
FTP server:
TCP_IN: 20,21
TCP_OUT: 20,21
UPD_IN: 20,21
UPD_OUT:20,21
Mail server:
TCP_IN: 25,110,143,587,993,995
TCP_OUT: 25,110
MySQL server (if remote access is required)
TCP_IN: 3306
TCP_OUT: 3306
Note: If you are using IPv6 for your services, you should also configure TCP6_IN, TCP6_OUT, UPD6_IN, and UPD6_OUT similarly to how IPv4 ports were configured earlier.

You can find a comprehensive list of TCP and UDP ports on Wikipedia. You should open the ports of all the services you use.

Step 2: Additional settings


CSF offers a vast number of different options in its configuration files. Some of the most commonly used settings are explained below.

ICMP_IN
Setting ICMP_IN to 1 allows ping to your server and 0 refuses are such requests. If you are hosting any public services, it is recommended to allow ICMP requests, as these can be used to determine whether or not your service is available.

ICMP_IN_LIMIT
Sets the number of ICMP (ping) requests allowed from one IP address within a specified amount of time. There is usually no need to change the default value (1/s)

DENY_IP_LIMIT
Sets the number of blocked IP addresses CSF keeps track of. It is recommended to limit the number of denied IP addresses as having too many blocks may slow down the server performance.

DENY_TEMP_IP_LIMIT
Same as above, but for temporary IP address blocks.

PACKET_FILTER
Filter invalid, unwanted and illegal packets.

SYNFLOOD, SUNFLOOD_RATE and SYNFLOOD_BURST
This offers protection against SYN flood attacks. This slows down the initialization of every connection, so you should enable this only if you know that your server is under attack.

CONNLIMIT
Limits the number of concurrent active connections on port.

Value:
22;5;443;20
would allow 5 concurrent connections on port 22 and 20 concurrent connections on port 443.

PORTFLOOD
Limits the number of connections per time interval that new connections can be made to specific ports. Value:
22;tcp;5;250
would limit block the IP address if more than 5 connections are established on port 22 using TCP protocol within 250 seconds. The block is removed once 250 seconds have passed after the last packet sent by the client to this port. You may add more ports by separating them by commas like described below.
port1;protocol1;connection_count1;time1,port2;protocol2;connection_count2;time2

More settings
CSF offers a wide range of settings which are not covered in this tutorial. The default values are generally good, and can be used on almost any server. The default settings are configured to prevent most flood attacks, port scans and unauthorized access attempts.

If you would, however, like to adjust the configuration in more detail, please read the comments in /etc/csf/csf.conf and edit them as you like. 

Step 3: Applying the Changes


Whenever you are altering the settings in csf.conf, you should save the files and restart CSF in order for the changes to take effect. Once you are ready with the configuration, close the file by pressing Ctrl + X. When you are asked whether to save the changes or not, press Y to save the changes.

After this, you should apply the changes by restarting CSF with command:
csf -r

If everything went like planned, and you are still able to access the server, open the configuration file once more:
nano /etc/csf/csf.conf
and change setting TESTING at the beginning of the configuration file to 0 as shown below:
TESTING = "0"

Save the file, and apply the changes with command:
csf -r

Blocking and Allowing IP Addresses


One of the most basic features of a firewall is the ability to block certain IP addresses. You may deny (blacklist), allow (whitelist) or ignore IP addresses by editing the configuration files csf.deny, csf.allow and csf.ignore.

Blocking IP addresses
If you would like to block an IP address or range, open csf.deny.
nano /etc/csf/csf.deny
Blocked IP addresses or ranges all reserve one line in csf.deny file. If you would like to block IP address 1.2.3.4 as well as IP range 2.3.*.*, you should add the following lines to the file:
1.2.3.4
2.3.0.0/16
IP ranges are represented using the CIDR notation

Allowing IP addresses
If you would like an IP address or range to be excluded from all blocks and filters, you may add them to csf.allow file. Please note that allowed IP addresses are allowed even if they are explicitly blocked in csf.deny file.

Allowing IP addresses works similarly to blocking them. The only difference is that you should edit /etc/csf/csf.allow instead of csf.deny.
nano /etc/csf/csf.allow
Ignoring IP addresses
CSF also offers ability to exclude IP addresses from the firewall filters. IP addresses in csf.ignore will bypass the firewall filters, and can only be blocked if listed in csf.deny file.
nano /etc/csf/csf.ignore
In order to changes take effect, you should restart CSF after editing any of the files described above with command:
csf -r

Find 10 largest files and directories in Linux

To find the largest 10 files (linux/bash):
find . -type f -print0 | xargs -0 du | sort -n | tail -10 | cut -f2 | xargs -I{} du -sh {}
To find the largest 10 directories:
find . -type d -print0 | xargs -0 du | sort -n | tail -10 | cut -f2 | xargs -I{} du -sh {}
Only difference is -type {d:f}.

Sunday 9 June 2013

mysql indexing notes

Show indexes of a table
SHOW INDEX FROM table_name;
Create index
CREATE INDEX index_name ON table_name(col_ame)

  Drop index
DROP INDEX index_name ON table_name


Detail explaination of all fields
id
The SELECT identifier. This is the sequential number of the SELECT within the query.
select_type
The type of SELECT, which can be any of those shown in the following table:
SIMPLESimple SELECT (no UNION / subqueries)
PRIMARYOutermost SELECT
UNIONSecond or later SELECT statement in a UNION
DEPENDENT UNIONSecond or later SELECT statement in a UNION, dependent on outer query
UNION RESULTResult of a UNION.
SUBQUERYFirst SELECT in subquery
DEPENDENT SUBQUERYFirst SELECT in subquery, dependent on outer query
DERIVEDDerived table SELECT (subquery in FROM clause)
table
The table to which the row of output refers.
type
The join type
possible_keys
The possible_keys column indicates which indexes MySQL can choose from use to find the rows in this table.
Key
The key column indicates the key (index) that MySQL actually decided to use. The key is NULL if no index was chosen. To force MySQL to use or ignore an index listed in the possible_keys column, use FORCE INDEX, USE INDEX, or IGNORE INDEX in your query
key_len
The key_len column indicates the length of the key that MySQL decided to use. The length is NULL if the key column says NULL
ref
The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table.
rows
The rows column indicates the number of rows MySQL believes it must examine to execute the query.
Extra
This column contains additional information about how MySQL resolves the query

More information about EXPLAIN

Saturday 8 June 2013

Slow query log parser

A very nice MySQL log analyzer tool from Percona



#!/usr/bin/perl 
#
# Nathanial Hendler
# http://retards.org/
#
# 2001-06-26 v1.0
#
# This perl script parses a MySQL slow_queries log file
# ignoring all queries less than $min_time and prints
# out how many times a query was greater than $min_time
# with the seconds it took each time to run.  The queries
# are sorted by number of times it took; the most often
# query appearing at the bottom of the output.
#
# Usage: mysql_slow_log_parser logfile
#
# ------------------------
# SOMETHING TO THINK ABOUT (aka: how to read output)
# ------------------------
#
# Also, it does to regex substitutions to normalize
# the queries...
#
#   $query_string =~ s/\d+/XXX/g;
#   $query_string =~ s/([\'\"]).+?([\'\"])/$1XXX$2/g;
#
# These replace numbers with XXX and strings found in
# quotes with XXX so that the same select statement
# with different WHERE clauses will be considered
# as the same query.
#
# so these...
#
#   SELECT * FROM offices WHERE office_id = 3;
#   SELECT * FROM offices WHERE office_id = 19;
#
# become...
#
#   SELECT * FROM offices WHERE office_id = XXX;
#
#
# And these...
#
#   SELECT * FROM photos WHERE camera_model LIKE 'Nikon%';
#   SELECT * FROM photos WHERE camera_model LIKE '%Olympus';
#
# become...
#
#   SELECT * FROM photos WHERE camera_model LIKE 'XXX';
#
#
# ---------------------
# THIS MAY BE IMPORTANT (aka: Probably Not)
# --------------------- 
#
# *SO* if you use numbers in your table names, or column
# names, you might get some oddities, but I doubt it.
# I mean, how different should the following queries be
# considered?
#
#   SELECT car1 FROM autos_10;
#   SELECT car54 FROM autos_11;
#
# I don't think so.
#


$min_time       = 0; # Skip queries less than $min_time
$min_rows = 0;
$max_display    = 10; # Truncate display if more than $max_display occurances of a query

print "\n Starting... \n";

$query_string   = '';
$time           = 0;
$new_sql        = 0;


##############################################
# Loop Through The Logfile
##############################################

while (<>) {

 # Skip Bogus Lines

 next if ( m|/.*mysqld, Version:.+ started with:| );
 next if ( m|Tcp port: \d+  Unix socket: .*mysql.sock| );
 next if ( m|Time\s+Id\s+Command\s+Argument| );
 next if ( m|administrator\s+command:| );


 # print $_;
 # if ( /Query_time:\s+(.*)\s+Lock_time:\s+(.*)\s/ ) {
 #if ( /Query_time:\s+(.*)\s+Lock_time:\s+(.*)\s+Rows_examined:\s+(\d+)/ ) {
 if ( /Query_time:\s+(.*)\s+Lock_time:\s+(.*)\s+Rows_examined:\s+(.*)/ ) {

  $time    = $1;
  $rows  = $3;
  $new_sql = 1;
  # print "found $1 $3\n";
  next;

 }


 if ( /^\#/ && $query_string ) {

   if (($time > $min_time) && ($rows >= $min_rows)) {
    $orig_query = $query_string;

    $query_string =~ s/\d+/XXX/g;
    $query_string =~ s/'([^'\\]*(\\.[^'\\]*)*)'/'XXX'/g;
    $query_string =~ s/"([^"\\]*(\\.[^"\\]*)*)"/"XXX"/g;
    #$query_string =~ s/([\'\"]).+?([\'\"])/$1XXX$2/g;
    #$query_string =~ s/\s+/ /g;
    #$query_string =~ s/\n+/\n/g;

    push @{$queries{$query_string}}, $time;
    push @{$queries_rows{$query_string}}, $rows;
    $queries_tot{$query_string} += $time;
    $queries_orig{$query_string} = $orig_query;
    $query_string = '';

   }

 } else {
  
  if ($new_sql) {
   $query_string = $_;
   $new_sql = 0;
  } else {
   $query_string .= $_;
  }
 }

}


##############################################
# Display Output
##############################################

foreach my $query ( sort { $queries_tot{$b} <=> $queries_tot{$a} } keys %queries_tot )  {
 my $total = 0; 
 my $cnt = 0;
 my @seconds = sort { $a <=> $b } @{$queries{$query}};
 my @rows    = sort { $a <=> $b } @{$queries_rows{$query}};
 ($total+=$_) for @seconds;
 ($cnt++) for @seconds;

 print "### " . @{$queries{$query}} . " Quer" . ((@{$queries{$query}} > 1)?"ies ":"y ") . "\n";
 print "### Total time: " . $total .", Average time: ".($total/$cnt)."\n";
 print "### Taking ";
 print @seconds > $max_display ? "$seconds[0] to $seconds[-1]" : sec_joiner(\@seconds);
 print " seconds to complete\n";
 print "### Rows analyzed ";
        print @rows > $max_display ? "$rows[0] - $rows[-1]": sec_joiner(\@rows);
 print "\n";
 
 print "$query\n";
 print $queries_orig{$query}."\n\n";
}


sub sec_joiner {
 my ($seconds) = @_;
 $string = join(", ", @{$seconds});
 $string =~ s/, (\d+)$/ and $1/;
 return $string;
}

exit(0);

Friday 31 May 2013

MariaDB master/slave Replication tutorial

{Note: There are no shortage of replication tutorials but I found this one particularly strightforward to follwo. It is originally written for MySQL but happily applied to MariaDB 10}



MySQL replication is a process that allows you to easily maintain multiple copies of a MySQL data by having them copied automatically from a master to a slave database. This can helpful for many reasons including facilating a backup for the data,a way to analyze it without using the main database, or simply as a means to scale out. 

This tutorial will cover a very simple example of mysql replication—one master will send information to a single slave. For the process to work you will need two IP addresses: one of the master server and and one of the slave. 

This tutorial will use the following IP addresses:

12.34.56.789- Master Database

12.23.34.456- Slave Database

Setup


This article assumes that you have user with sudo privileges and have MySQL installed. If you do not have mysql, you can install it with this command:
sudo apt-get install mysql-server mysql-client

Step One—Configure the Master Database


Open up the mysql configuration file on the master server.
sudo nano /etc/mysql/my.cnf

Once inside that file, we need to make a few changes.

The first step is to find the section that looks like this, binding the server to the local host:
bind-address            = 127.0.0.1

Replace the standard IP address with the IP address of server.
bind-address            = 12.34.56.789 

The next configuration change refers to the server-id, located in the [mysqld] section. You can choose any number for this spot (it may just be easier to start with 1), but the number must be unique and cannot match any other server-id in your replication group. I’m going to go ahead and call this one 1. Make sure this line is uncommented.
server-id               = 1

Move on to the log_bin line. This is where the real details of the replication are kept. The slave is going to copy all of the changes that are registered in the log. For this step we simply need to uncomment the line that refers to log_bin:
log_bin                 = /var/log/mysql/mysql-bin.log

Finally, we need to designate the database that will be replicated on the slave server. You can include more than one database by repeating this line for all of the databases you will need.
binlog_do_db            = newdatabase

After you make all of the changes, go ahead and save and exit out of the configuration file.

Refresh MySQL.
sudo service mysql restart

The next steps will take place in the MySQL shell, itself. 

Open up the MySQL shell.
mysql -u root -p

We need to grant privileges to the slave. You can use this line to name your slave and set up their password. The command should be in this format:
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';

Follow up with:
FLUSH PRIVILEGES;

The next part is a bit finicky. To accomplish the task you will need to open a new window or tab in addition to the one that you are already using a few steps down the line. 

In your current tab switch to “newdatabase”.
USE newdatabase;
Following that, lock the database to prevent any new changes:
FLUSH TABLES WITH READ LOCK;

Then type in:
SHOW MASTER STATUS;

You will see a table that should look something like this:
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | newdatabase  |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

This is the position from which the slave database will start replicating. Record these numbers, they will come in useful later.

If you make any new changes in the same window, the database will automatically unlock. For this reason, you should open the new tab or window and continue with the next steps there.

Proceeding the with the database still locked, export your database using mysqldump in the new window (make sure you are typing this command in the bash shell, not in MySQL).
mysqldump -u root -p --opt newdatabase > newdatabase.sql

Now, returning to your your original window, unlock the databases (making them writeable again). Finish up by exiting the shell.
UNLOCK TABLES;
QUIT;

Now you are all done with the configuration of the the master database.

Step Two—Configure the Slave Database


Once you have configured the master database. You can put it aside for a while, and we will now begin to configure the slave database. 

Log into your slave server, open up the MySQL shell and create the new database that you will be replicating from the master (then exit):
CREATE DATABASE newdatabase;
EXIT;

Import the database that you previously exported from the master database.
mysql -u root -p newdatabase < /path/to/newdatabase.sql

Now we need to configure the slave configuration in the same way as we did the master:
sudo nano /etc/mysql/my.cnf

We have to make sure that we have a few things set up in this configuration. The first is the server-id. This number, as mentioned before needs to be unique. Since it is set on the default (still 1), be sure to change it’s something different.
server-id               = 2

Following that, make sure that your have the following three criteria appropriately filled out:
relay-log               = /var/log/mysql/mysql-relay-bin.log
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = newdatabase
You will need to add in the relay-log line: it is not there by default.

Once you have made all of the necessary changes, save and exit out of the slave configuration file. 

Restart MySQL once again:
sudo service mysql restart

The next step is to enable the replication from within the MySQL shell.

Open up the the MySQL shell once again and type in the following details, replacing the values to match your information:
CHANGE MASTER TO MASTER_HOST='12.34.56.789',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  107;
This command accomplishes several things at the same time:

  1. It designates the current server as the slave of our master server.

  2. It provides the server the correct login credentials

  3. Last of all, it lets the slave server know where to start replicating from; the master log file and log position come from the numbers we wrote down previously.


With that—you have configured a master and slave server. Activate the slave server:
START SLAVE;

You be able to see the details of the slave replication by typing in this command. The \G rearranges the text to make it more readable.
SHOW SLAVE STATUS\G

Note that you should have both

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

If either of values is NO, you can try starting slave with a command to skip over it:
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START; 

Also

grep mysql /var/log/syslog

May show some hints about the cause of problem.

All done. 

Source
============================== Troubleshootings: 
http://dev.mysql.com/doc/refman/5.0/en/replication-problems.html