Author: wahab Page 4 of 7

How to remove or compress huge MySQL general and query log table

On June 6, 2018

How to remove or compress huge MySQL general and query log table

If you have enabled MySQL general or slow logging, it can create quite big log, depending upon your MySQL usage/queries.
So we may have to periodically clear them to save space.

Please note that MySQL can save logs to either table or files. This document assumes you are using table as log output.

Files: slow_log.CSV and general_log.CSV (The location and the name of the file can be different)

By default, logging is to CSF file.

MYSQL supports run time clearing of these logs. So no need to restart the MySQL service.
Never delete the CSV file directly. It can crash MySQL.

Slow query log

SET GLOBAL slow_query_log='OFF';
DROP TABLE IF EXISTS slow_log2;
CREATE TABLE slow_log2 LIKE slow_log;
RENAME TABLE slow_log TO slow_log_backup, slow_log2 TO slow_log;
gzip /var/db/mysql/mysql/slow_log_backup.CSV 
DROP TABLE  slow_log_backup;
SET GLOBAL slow_query_log = 'ON';

General log

USE mysql;
SET GLOBAL general_log = 'OFF';
DROP TABLE IF EXISTS general_log2;
CREATE TABLE general_log2 LIKE general_log;
RENAME TABLE general_log TO general_log_backup, general_log2 TO general_log;
gzip /var/db/mysql/mysql/general_log_backup.CSV 
DROP TABLE  general_log_backup;

What we did is create new log table, move current log file to a backup copy and compress the backup and remove it.

Password protect phpMyAdmin through CentOS Web panel(CWP)

By wahab

On May 16, 2018

In Linux server

phpMyAdmin is installed with CentOS Web Panel. By default, it is not protected and there is only MySQL user authentication. This can put your server vulnerable. So it is recommended to add additional layer protection.

phpMyAdmin is available through the following url in a CWP based server.

http:/hostname/phpmyadmin
http:/hostname:2030/pma

CWP panel runs its core services through its own version of Nginx. So normal htaccess based password protection will not work.

Create the Password File

You can do this by using the OpenSSL utilities that may already be available on your server. Alternatively, you can use the purpose-made htpasswd utility included in the apache2-utils package(Debian/ubuntu) or httpd-tools(Redhat/Centos).

Using OpenSSL Utilities

We will create a hidden file called .pma_pass /usr/local/cwpsrv/var/services/ folder. You can use any username. I am using dbadmin here as an example

sudo sh -c "echo -n 'dbadmin:' >> /usr/local/cwpsrv/var/services/.pma_pass"

Next, add an encrypted password entry for the username by typing:

sudo sh -c "openssl passwd -apr1 >> /usr/local/cwpsrv/var/services/.pma_pass"

Using Apache Utilities

This tool is already installed and available on all CWP servers.

/usr/local/apache/bin/htpasswd -c /usr/local/cwpsrv/var/services/.pma_pass dbadmin

Configure Nginx Password Authentication

We will need to configure Nginx to read this file before serving our protected content.
CWP Service Nginx configuration file: /usr/local/cwpsrv/conf/cwp_services.conf

Open the above file add the following to the location block of phpMyAdmin.

auth_basic “Admin Login”;
auth_basic_user_file /usr/local/cwpsrv/var/services/pma_pass;

So the full block should look like this now.

location /pma {
    root /usr/local/cwpsrv/var/services;
    index  index.html index.htm index.php;
    ModSecurityEnabled off;
    ModSecurityConfig /usr/local/cwpsrv/conf/security/conf/pma_rules.conf;

    location ~ \.php$ {
        try_files $uri =404;
        fastcgi_split_path_info ^(.+\.php)(/.+)$;
        fastcgi_read_timeout 600;
        fastcgi_pass    unix:/usr/local/cwp/php71/var/sockets/cwpsvc.sock;
        fastcgi_index   index.php;
        fastcgi_param   SCRIPT_FILENAME  $document_root$fastcgi_script_name;
        fastcgi_param   SCRIPT_NAME   $fastcgi_script_name;
        include                 fastcgi_params;
    }

    location ~* \.(js|css|png|jpg|jpeg|gif|ico)$ {
        access_log    off;
        log_not_found    off;
        expires 1M;
    }

       auth_basic "Admin Login";
       auth_basic_user_file /usr/local/cwpsrv/var/services/.pma_pass;
}

Restart CWP nginx service by below commands

systemctl restart cwpsrv.service

Confirm the Password Authentication

To confirm that your content is protected, try to access your restricted content in a web browser. You should be presented with a username and password prompt

How to block Outgoing Port 25 for all VPS/Containers of SolusVM

By wahab

On December 5, 2017

In Linux server

How to block Outgoing Port 25 for all VPS/Containers of SolusVM(Both OpenVZ/KVM)

Sometimes,when providing VPS service, it is necessary to block mailing service for VPS. Otherwise your IP ranges will be blocked by RBLs and other common mail providers like Gmail, Hotmail, AOL, etc. It is recommended to open ports by case by case for your customers, after establishing trust with your client.

Normally we block the port using the “OUTPUT” chain. However, SolusVM uses the chain “FORWARD” for routing traffic from containers/VPSs.

So by adding following rules, you can block all outgoing mails ports completely for all VPSs.
iptables -I FORWARD -d 0.0.0.0/0 -p tcp -m tcp --dport 25 -j DROP iptables -I FORWARD -d 0.0.0.0/0 -p tcp -m tcp --dport 465 -j DROP iptables -I FORWARD -d 0.0.0.0/0 -p tcp -m tcp --dport 587 -j DROP

Then save the iptables by running

service iptables save

NRPE installation Ubuntu

By wahab

On March 18, 2017

In Linux server, Nagios

NRPE installation installation Ubuntu

Tested: Ubuntu 14.04 64 bit

#Install necessary packages
apt-get install gettext autoconf gcc libc6 libmcrypt-dev make libssl-dev wget automake libtool bc gawk dc build-essential snmp libnet-snmp-perl

#Add icinga user and group
groupadd -g 9000 icinga useradd -u 9000 -g icinga -d /usr/local/nagios -c "Nagios NRPE" icinga

# Install latest NRPE

cd /usr/local/src/ wget --no-check-certificate -O nrpe.tar.gz https://github.com/NagiosEnterprises/nrpe/archive/nrpe-3.2.0.tar.gz tar xvf nrpe.tar.gz cd nrpe-nrpe-3.2.0 ./tools/setup ./configure --enable-command-args --with-ssl-lib=/usr/lib/x86_64-linux-gnu/ --with-nrpe-user=icinga --with-nrpe-group=icinga --with-nagios-user=icinga --with-nagios-group=icinga #Ubuntu x86_x64 #For Ubuntu i386 #./configure --enable-command-args --with-ssl-lib=/usr/lib/i386-linux-gnu/ --with-nrpe-user=icinga --with-nrpe-group=icinga --with-nagios-user=icinga --with-nagios-group=icinga make all make install make install-config

#Update Services File
echo “Adding nrpe to running services”
echo “nrpe 5666/tcp # Nagios NRPE” >>/etc/services

#Install Service / Daemon make install-init #Ubuntu 13.x / 14.x

#systemctl enable nrpe.service #Ubuntu 15.x / 16.x / 17.x

#Open the incoming TCP port 5666 on your firewall. You will have to do this using firewall software, like firewall ufw.

#Update Configuration File The file nrpe.cfg is where the following settings will be defined. It is located:

/usr/local/nagios/etc/nrpe.cfg

allowed_hosts=

At this point NRPE will only listen to requests from itself (127.0.0.1). If you wanted your nagios server to be able to connect, add it's IP address after a comma (in this example it's 10.25.5.2):

allowed_hosts=127.0.0.1,10.25.5.2

The following commands make the configuration changes described above.

sudo sh -c "sed -i '/^allowed_hosts=/s/$/,10.25.5.2/' /usr/local/nagios/etc/nrpe.cfg" sudo sh -c "sed -i 's/^dont_blame_nrpe=.*/dont_blame_nrpe=1/g' /usr/local/nagios/etc/nrpe.cfg"

#Start Service / Daemon

Different Linux distributions have different methods of starting NRPE.

Ubuntu 13.x / 14.x

sudo start nrpe

Ubuntu 15.x / 16.x / 17.x

sudo systemctl start nrpe.service

Test NRPE

Now check that NRPE is listening and responding to requests.

/usr/local/nagios/libexec/check_nrpe -H 127.0.0.1

You should see the output similar to the following:
NRPE v3.2.0

If you get the NRPE version number (as shown above), NRPE is installed and configured correctly.

You can also test from your Nagios host by executing the same command above, but instead of 127.0.0.1 you will need to replace that with the IP Address / DNS name of the machine with NRPE running.

Service / Daemon Commands

Different Linux distributions have different methods of starting / stopping / restarting / status NRPE.

Ubuntu 13.x / 14.x

sudo start nrpe sudo stop nrpe sudo restart nrpe sudo status nrpe

Ubuntu 15.x / 16.x / 17.x

sudo systemctl start nrpe.service
sudo systemctl stop nrpe.service
sudo systemctl restart nrpe.service
sudo systemctl status nrpe.service

Installing The Nagios Plugins

NRPE needs plugins to monitor different parameters. T

#Install Latest Nagios plugins

cd /usr/local/src/ wget --no-check-certificate -O nagios-plugins.tar.gz https://github.com/nagios-plugins/nagios-plugins/archive/release-2.2.1.tar.gz tar zxf nagios-plugins.tar.gz cd nagios-plugins-release-2.2.1/ ./tools/setup ./configure --enable-perl-modules make make install

#Test NRPE + Plugins

Using the check_load command to test NRPE:
/usr/local/nagios/libexec/check_nrpe -H 127.0.0.1 -c check_load

You should see the output similar to the following: OK - load average: 0.01, 0.13, 0.12|load1=0.010;15.000;30.000;0; load5=0.130;10.000;25.000;0; load15=0.120;5.000;20.000;0;

You can also test from your Nagios host by executing the same command above, but instead of 127.0.0.1 you will need to replace that with the IP Address / DNS name of the machine with NRPE running.

Disable/suspend email service for one domain – cPanel

By wahab

On July 5, 2016

In Cpanel and WHM

Disable/suspend email service for one domain

From version 56, cPanel provides API support to disable email service for domain or email accounts. Please note this limit cannot be override by the user from their cPanel. This can be very helpful if there is any spamming activity and you just want to disable the email service feature for the domain for the time being.

You can run the below commands through SSH console

To suspend email service for a the cPanel user “aacenyor”

whmapi1 suspend_outgoing_email user=aacenyor

For unsuspending the account, please run the below command:

whmapi1 unsuspend_outgoing_email user=aacenyor

If you are not comfortable with SSH, you can also call these through browser XML.

Example:

To suspend
https://hostname.example.com:2087/cpsess##########/xml-api/suspend_outgoing_email?api.version=1&user=aacenyor

where replace hostname.example.com with your hostname
replace cpsess########## with WHM session id.

To un suspend
https://hostname.example.com:2087/cpsess##########/xml-api/unsuspend_outgoing_email?api.version=1&user=aacenyor

For older versions cPanel, you may refer to this below forum from cPanel. There is a workaround

https://forums.cpanel.net/threads/how-do-i-block-one-domain-from-sending-email-from-my-server.223731/#post920912

Problems logging into Plesk because of IP restrictions

By wahab

On June 29, 2016

In Linux server, Plesk

Hello,

If you receive following error while logging to the Plesk panel, that means there is a IP based restriction to access Plesk admin panel and your current IP is not allowed to access.

“Unable to log into Plesk: Access for administrator from address xx.xx.xx.xx is restricted in accordance with IP Access restriction policy currently applied”

Cause
Plesk IP access policy was configured in such a way so that Plesk could not be accessed from the certain IP.

Resolution

Method 1. To enable Plesk access, you need to log into the Plesk from another IP and change the IP access policy:

Tools and Settings > Restrict Administrative Access
Add your IP to the whitelist

Method 2. Updating the database directly

If you cannot login to the panel, then you can connect to the server using SSH and correct this through database queries. Plesk database records regarding the access policy need to be corrected.

To retrieve the current policy and the restricted/allowed IPs, the following commands can be used:

Linux

#MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin psa
    mysql> select * from cp_access;
    mysql> select * from misc where param='access_policy';

Windows

"%plesk_bin%\dbclient" --direct-sql --sql="select * from cp_access"
    "%plesk_bin%\dbclient" --direct-sql --sql="select * from misc where param='access_policy'";

If you wish to clear the access policy settings, remove all records from “cp_access” and set the policy to “allow”:

Linux

# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin psa
    mysql> delete from cp_access;
    mysql> update misc set val="allow" where param='access_policy';

Windows

"%plesk_bin%\dbclient" --direct-sql --sql="delete from cp_access";
    "%plesk_bin%\dbclient" --direct-sql --sql="update misc set val='allow' where param='access_policy'";

To whitelist the IP manually

bash# mysql -uadmin -p`cat /etc/psa/.psa.shadow ` psa

insert into cp_access values ("", "deny", "x.x.x.", "255.255.255.255"); //change the IP address to your public IP.

Then you should be able to connect to the Plesk control panel from the new IP address.

Email sending limits and throttling – iRedmail free edition

By wahab

On June 29, 2016

In iRedmail Mailserver

iRedMail is:
1. A ZERO COST, fully fledged, full-featured mail server solution. All components are free and open source software
2. It support unlimited domains and email accounts.
2. SpamAssassin, ClamAV, SPF, DKIM, greylisting, whitelisting, blacklisting.
3. Stores mail accounts in your favorite backend: OpenLDAP, MySQL, MariaDB, PostgreSQL.
4. Supports major Linux distros

Throttling

iRedmail supports following level of throttling for email limits.
Global, per-domain and per-user throttling based on: max size of single message, number of max inbound/outbound messages per time unit.

iRedmail uses a component named iredpad for this. With iRedmail Pro version, you can configure these settings through GUI(iRedadmin panel). However they are not available through GUI for free version. The feature is enabled though we will have to modify them through database directly.

So the database is “iredapd” and table is “throttle”.

Here are some examples

1. Allow user `user@domain.com` to send 50 mails in 5 minutes (period=300):

INSERT INTO throttle (account, kind, priority, period, msg_size, max_msgs, max_quota) VALUES (‘user@domain.com’,’outbound’,100,300,0,50,0);

2. Set global limit of 500 mails per day and a maximum single mail size of 55MB

INSERT INTO throttle (account, kind, priority, period, msg_size, max_msgs, max_quota) VALUES (‘@.’,’outbound’,0,86400,57671680,500,0);

57671680 bytes = 55MB
86400 – 1 Day

# Technical details of throttle plugin
# ————-
#
# Currently you may throttle based on:
#
# – amount of mails sent over a given period of time
# – accumulated mail size sent over a given period of time
# – size of singe message
#
# Eg: You can enforce that user@domain.com does not send more than 1000 mails
# or 1GB of mail (whichever limit is hit first) in 5 minute.
#
# Possible throttling address:
#
# *) Full email address: user@domain.com
# *) Domain name (with a prefixed ‘@’): @domain.com
# *) Sub-domain name (with a prefixed ‘@.’): @.domain.com
# *) IP address: 192.168.1.1
# *) IP network: 192.168.1.*
# *) Catch-all for email address: ‘@.’ (note, the dot is required)
# *) Catch-all for IP address: ‘@ip’
#
# Priorities of different thorttle address (larger digital number has higher priority):
#
# *) email: 100 # e.g. ‘user@domain.com’. Highest priority
# *) wildcard_addr: 90 # e.g. `user@*`. used in plugin `amavisd_wblist`
# # as wildcard sender. e.g. ‘user@*`
# *) ip: 80 # e.g. 173.254.22.21
# *) wildcard_ip: 70 # e.g. 173.254.22.*
# *) cidr: 70 # e.g. 173.254.22.0/24
# *) domain: 60 # e.g. @domain.com
# *) subdomain: 50 # e.g. @.domain.com
# *) top_level_domain: 40 # e.g. @com, @org
# *) catchall: 0 # ‘@.’. Lowest priority

Please note that priority for global setting is 0 and priority of a single user is 100.

SOGo ActiveSync not working – iRedmail

By wahab

On June 28, 2016

In iRedmail Mailserver

iRedMail is:
1. A ZERO COST, fully fledged, full-featured mail server solution. All components are free and open source software
2. It support unlimited domains and email accounts.
2. SpamAssassin, ClamAV, SPF, DKIM, greylisting, whitelisting, blacklisting.
3. Stores mail accounts in your favourte backend: OpenLDAP, MySQL, MariaDB, PostgreSQL.
4. Supports major Linux distros

You know how difficult is to configure a fully fledged email service you have tried. iRedmail do this heavy part for you. I would recommend this piece of software to everyone. I used to configure email server manually using Postfix, Postfixadmin, MySQL, SpamAssassin, etc. Even though I have done this several times, it needs 2-3 hours for setup. But iRedmail can do all of this and MORE in few minutes, thus saves lot of time.

iRedmail comes with SOGo which provides EAS Activesync support

SOGo is providing the EAS support. If you are getting an error “Unable to connect” on email client do following

1. Check https://hostname/Microsoft-Server-ActiveSync

if it gives 404, issue with SOGo httpd conf
If it gives white page, no problem with conf

SOGo installs Apache config file /etc/httpd/conf.d/SOGo.conf by default, please open it and find below lines:

#ProxyPass /Microsoft-Server-ActiveSync \
# http://127.0.0.1:20000/SOGo/Microsoft-Server-ActiveSync \
# retry=60 connectiontimeout=5 timeout=360

Remove # at the beginning to enable ActiveSync support:
ProxyPass /Microsoft-Server-ActiveSync \
http://127.0.0.1:20000/SOGo/Microsoft-Server-ActiveSync \
retry=60 connectiontimeout=5 timeout=360

Find string yourhostname in the same file, replace all yourhostname by your FQDN server hostname. (Tip: you can get your FQDN hostname with command hostname -f.)

Restart Apache

Disable greylisting completely – iRedmail

By wahab

On June 28, 2016

In iRedmail Mailserver

iRedMail is:
1. A ZERO COST, fully fledged, full-featured mail server solution. All components are free and open source software
2. It support unlimited domains and email accounts.
2. SpamAssassin, ClamAV, SPF, DKIM, greylisting, whitelisting, blacklisting.
3. Stores mail accounts in your favourte backend: OpenLDAP, MySQL, MariaDB, PostgreSQL.
4. Supports major Linux distros

How to disable greylisting completely

Greylisting is a method of defending e-mail users against spam. A mail transfer agent (MTA) using greylisting will “temporarily reject” any email from a sender it does not recognize

To disable greylisting completely, please remove plugin name greylisting in iRedAPD config file
/opt/iredapd/settings.py, parameter plugins =:
plugins = […, ‘greylisting’, …]

Restarting iRedAPD service is required.

Reject outgoing Spam – iRedmail server

By wahab

On June 28, 2016

In iRedmail Mailserver

iRedMail is:
1. A ZERO COST, fully fledged, full-featured mail server solution. All components are free and open source software
2. It support unlimited domains and email accounts.
2. SpamAssassin, ClamAV, SPF, DKIM, greylisting, whitelisting, blacklisting.
3. Stores mail accounts in your favourte backend: OpenLDAP, MySQL, MariaDB, PostgreSQL.
4. Supports major Linux distros

Reject outgoing Spam

iRedmail comes with Amavis, Spamassasin and Clamd. By default iRedmail will not block Outgoing spam message. However you can tweak Amavis configurations to reject such mails.

/etc/amavisd/amavisd.conf

Add following to $policy_bank{‘ORIGINATING’}

final_virus_destiny => D_DISCARD,
final_banned_destiny => D_REJECT,
final_spam_destiny => D_REJECT,
final_bad_header_destiny => D_REJECT,

Then restart amavis /etc/init.d/amavisd restart

This will reject all outgoing spam mails above the score

Of course tweak the spam score accordingly.