IPTables rules for MySQL

If iptables locks all incoming connections (INPUT DROP) and to add external access to MySQL, you need to add rules:

iptables -A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT

To access only a particular network, for example 10.0.0.0/24:

iptables -A INPUT -s 10.0.0.0/24 -p tcp -m tcp --dport 3306 -j ACCEPT

To remove a rule, we’ll specify the same command, replacing -A with -D, for example:

iptables -D INPUT -p tcp -m tcp --dport 3306 -j ACCEPT

To view the list of rules, use the command:

sudo iptables -nvL

I note that in order to open external access, you also need to comment out the line “bind-address = 127.0.0.1” in the my.cnf configuration file.

If by default INPUT ACCEPT, we first specify which IPs are allowed access, and only the last rule is blocked by all the others:

/sbin/iptables -A INPUT -s 127.0.0.1 -p tcp --destination-port 3306 -j ACCEPT
/sbin/iptables -A INPUT -s 192.168.1.5 -p tcp --destination-port 3306 -j ACCEPT
/sbin/iptables -A INPUT -p tcp --dport 3306 -j DROP

For example, using nmap, you can check locally and externally whether the access is filtered:

nmap -p 3306 localhost
nmap -p 3306 192.168.1.5

See also:
Configuring IPTables
Other my articles about MySQL

Diagnostics HDD using smartmontools

smartmontools – (S.M.A.R.T. Monitoring Tools) console utility for diagnosing the status of hard drives supporting S.M.A.R.T. technology.
smartmontools has two utilities, smartctl for monitoring S.M.A.R.T. and a smartd background process that automatically polls devices and records errors.

Installation in Linux Ubuntu/Debian:

sudo apt-get install smartmontools

Help about the utility:

smartctl -h

Examples:
Verification of the support of S.M.A.R.T. and SCT ERC:

smartctl -i -d sat /dev/sda
sudo smartctl -a /dev/sda  | grep SCT

Full information about HDD:

smartctl --all /dev/sda

HDD status evaluation:

smartctl -H /dev/sda

HDD error log:

smartctl -l error /dev/sda

Drive integrity test:

smartctl --test long /dev/sda

See also:
Description of SMART attributes
Linux disk test for errors and broken sectors

The solution to the error “md: kicking non-fresh sda1 from array”

There was once a case, one disk dropped out of the raid and when the server was loaded in the logs a message was displayed:

md: kicking non-fresh sda1 from array

Since the disk was not in the raid, the data on it was outdated.
First of all, we’ll check the disk for errors, for example, as I wrote in the article below, and try to determine why he was excluded from the raid.

In my case, the disk was completely working, so looking at the information about the raid:

cat /proc/mdstat
mdadm --detail /dev/md0

Returned it back to the raid:

mdadm /dev/md0 -a /dev/sda1

After some time, the data was synchronized to disk and the error did not appear any more.

See also:
Diagnostics HDD using smartmontools
mdadm – utility for managing software RAID arrays
How to fix the problem with mdadm disks

Configuring NTP Client and NTP Server in Linux

NTP (Network Time Protocol)
Uses UDP connections and port 123.

Example installation in Ubuntu/Debian:

sudo apt-get install ntp

The current date can be viewed by the command:

date

Example of setting the time zone:

cp /usr/share/zoneinfo/Europe/Kiev /etc/localtime

Setting hours and minutes:

date -s 10:18
date --set 10:18

Example of full time setting (without spaces, in digital form):

date monthdayhourminuteyear.second

Checking the time server:

ntpdate -q SERVER

View status:

sudo ntpq -p
ntpq -c peers 

Let’s start editing the configuration file /etc/ntp.conf.

A few lines:
Specify the server with which to compare the time:

server 0.ubuntu.pool.ntp.org
server 1.ubuntu.pool.ntp.org
...

We prohibit all access to the server:

restrict default ignore

Or we allow access from the internal network:

restrict 192.168.0.0 mask 255.255.255.0 nomodify notrap

Restart ntp to make the changes in the configuration file take effect:

sudo service ntp restart

An example of time synchronization from the Windows command line with a Linux server:

net time \\SERVER /set /yes

See also:
IPTables rules for NTP and SNTP

Installing and using jnettop

jnettop – a utility for viewing traffic statistics in real time, sorted by hosts / ports.

The installation command in Ubuntu/Debian:

sudo apt-get install jnettop

The installation command in CentOS:

sudo yum install jnettop

An example of a simple start:

sudo jnettop

An example of a start with a network interface:

sudo jnettop -i eth0

I’ll describe the possible startup options:
-h (help)
-v (view version)
-c (disable content filtering)
-d (write debugging information to a file)
-f (reading the configuration from the file, if not specified, then the file is searched for ~/.jnettop)
-i (to capture packets from the specified interface)
–local-aggr [none|host|port|host+port] (set local aggregation to the specified value)
–remote-aggr [none|host|port|host+port] (set remote aggregation to the specified value)
-n (do not resolving IP to DNS names)
-p (inclusion of promiscuous mode to receive all packets that come to the network interface)
-s (selects one of the rules defined in the .jnettop configuration file (by its name))
-x (allows you to specify a custom filter rule. this allows you to use the syntax of the tcpdump style. do not forget to include the filter in quotation marks when starting from the shell)

Email notification about each SSH connection

Here are a few ways to receive e-mail notifications about someone connecting to the server via SSH.

FIRST METHOD:
With a text editor, for example nano, open the file /etc/ssh/sshrc (in the nano editor CTRL+X to exit, y/n and Enter to save or discard changes):

sudo nano /etc/ssh/sshrc

And add the following code to it:

ip=`echo $SSH_CONNECTION | cut -d " " -f 1`
logger -t ssh-wrapper $USER login from $ip
(echo "Subject:login($ip) on server"; echo "User $USER just logged in from $ip";) | sendmail -f server@example.com -t your-email@example.com &

You do not need to restart SSH, the notifications should already come in when connecting.

SECOND METHOD:
Add the specified lines to the config /etc/rsyslog.conf (before each line commented the essence, this code will send messages about failed connections):

# Connect the messaging module
$ModLoad ommail
# Specify the address of the mail server
$ActionMailSMTPServer mail.domain.com
# Specify the email from which messages will be sent
$ActionMailFrom rsyslog@domain.com
# Specify the email to which messages will be sent
$ActionMailTo test@domain.com
# Specify the subject of the message
$template mailSubject,"SSH Invalid User %hostname%"
# Specify the content of the message
$template mailBody,"RSYSLOG\r\nmsg='%msg%'"
$ActionMailSubject mailSubject
# Specify in seconds how often messages can be sent
$ActionExecOnlyOnceEveryInterval 10
# If the log contains the characters in parentheses, then we send a message
if $msg contains 'Invalid user' then :ommail:;mailBody

The same way of sending via rsyslog, but notifications of successful connections are sent (code without comments as above):

$ActionMailSMTPServer mail.domain.com
$ActionMailFrom rsyslog@domain.com
$ActionMailTo test@domain.com
$template mailSubject,"SSH Accepted pass %hostname%"
$template mailBody,"RSYSLOG\r\nmsg='%msg%'"
$ActionMailSubject mailSubject
$ActionExecOnlyOnceEveryInterval 10
if $msg contains 'Accepted password' then :ommail:;mailBody

As a result, if the connection to the SSH server is successful or not successful, messages will be sent to the e-mail. In a similar way, you can announce to email and other events that are logged via rsyslog.