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.
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.
If you are getting following error while FTP directory listing, follow the solution provided below
———- ftp> ls 227 Entering Passive Mode (108,61,169,245,167,161). ftp: connect: No route to host ———-
Solution
Edit /etc/sysconfig/iptables-config and add this line:
IPTABLES_MODULES=”ip_conntrack_ftp”
Save it and restart iptables. That’s because passive mode use non standard ports to communicate, so you need to keep trak of the ftp connections and iptables will allow them when necessary.
However, you will need to do this every time you reboot your RedHat server. Thus as a more permanent solution you can persistently load this module after each reboot by creating executable shell script within /etc/sysconfig/modules/ directory. Create file /etc/sysconfig/modules/iptables.modules with the following content:
If you are getting the below error while updating the Plesk versions or installing the microupdates
—- ERROR: Unable to download the MD5 sum for the new Parallels Installer binary. Not all packages were installed. Please, contact product technical support. —-
Solution —– Remove cache from /var/cache/parallels_installer/ and start autoinstaller again. /usr/local/psa/admin/sbin/autoinstaller –select-product-id plesk –select-release-current –reinstall-patch –install-component base —–
Unified Communications (UC) Certificates (also called SAN Certificates) use Subject Alternative Names o secure multiple sites (e.g. fully qualified domain names) with one certificate. Four SANs are included in the base price of the UC Certificate, but you can purchase additional names at any time during the lifetime of the certificate.
The CSR generation process is little different for creating an UCC certificates. We will have to create a Openssl based configuration file and then create private key and CSR from it.
Step 1: Create a custom OpenSSL Conf file.
The following is an example conf file that can be used for creation of a SAN/UCC cert. Save it as multissl.conf
———– [ req ] default_bits = 2048 default_keyfile = privkey.pem distinguished_name = req_distinguished_name req_extensions = req_ext # The extentions to add to the self signed cert
[ req_distinguished_name ] countryName = Country Name (2 letter code) countryName_default = US stateOrProvinceName = State or Province Name (full name) stateOrProvinceName_default = Iowa localityName = Locality Name (eg, city) localityName_default = Iowa City organizationName = Organization Name (eg, company) organizationName_default = The University of Iowa organizationalUnitName = Organizational Unit Name (eg, section) organizationalUnitName_default = Domain Control Validated commonName = Common Name (eg, YOUR SSL domain name) commonName_max = 64
The alt_names section (DNS.1, DNS.2, ….) are the list of all other domain names you wish to secure with this cert. Additional can be added such as DNS.4, etc. The following examples assume that you name the above config file file multissl.conf (if it is named differently you must adjust the filename in the below examples accordingly. Step 2: Generate the Private key and CSR with OpenSSL
* Replace “serverfqdn” with the fully qualified domain name of the server (ie: sample.server.uiowa.edu). Note: it may also be helpful to add a year to the filename.
You will then see output and be prompted for configuration as seen in the following example. Enter your details accordingly.
—————————————— $ openssl req -nodes -newkey rsa:2048 -keyout serverfqdn.key -out multidomain.csr -config multissl.conf Generating a 2048 bit RSA private key ………………………………….+++ …………………………………………………………+++ writing new private key to ‘serverfqdn.key’ —– You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter ‘.’, the field will be left blank. —– Country Name (2 letter code) [US]:US State or Province Name (full name) [Iowa]:Iowa Locality Name (eg, city) [Iowa City]:Iowa City Organization Name (eg, company) [The University of Iowa]:My Company name Organizational Unit Name (eg, section) [Domain Control Validated]:IT SUPPORT Common Name (eg, YOUR SSL domain name) []:www.linuxwebhostingsupport.in ——————————————
Note: Replace www.linuxwebhostingsupport.in with the “primary” domain name you want secured with this certificate (likely, but not necessarily the hostname of the machine).
At this point you should have the new key file, and CSR. Save the key file in a secure place, it will be needed to apply the new certificate. The CSR can now be submitted to request the SSL Cert.
Let’s start with your digital certificate, which is at the core of HTTPS. The certificate enables clients to verify the identity of servers, through a chain of trust from your server’s certificate through intermediate certificates and up to a root certificate trusted by users’ browsers. Your server certificate should be 2048 bits in length. Using 4096 bit certificate is more secure however it require more computation times and hence slow compared to 2048 bit certs.
Basic HTTPS Setup
Here are basic SSL configurations, first for Apache:
In Nginx, the ssl_certificate parameter is confusing. It expects your certificate plus any necessary intermediate certificates, concatenated together.
Make sure all of these files are at least mode 0444, except your private key, which should be 0400.
Software versions
On the server side you should update your OpenSSL to 1.0.1c+ so you can support TLS 1.2, GCM, and ECDHE as soon as possible. Fortunately that’s already the case in Ubuntu 12.04 and later.
On the client side the browser vendors are starting to catch up. As of now, Chrome 30, Internet Explorer 11 on Windows 8, Safari 7 on OS X 10.9, and Firefox 26 all support TLS 1.2.
Cipher Suite Configuration
The recommended cipher suites for Apache are follows
SSLCipherSuite EECDH+AESGCM:EDH+AESGCM:AES256+EECDH:AES256+EDH
SSLHonorCipherOrder on
The recommended cipher suite for backwards compatibility (IE6/WinXP):
SSLCipherSuite EECDH+AESGCM:EDH+AESGCM:AES256+EECDH:AES256+EDH:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-SHA384:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES256-SHA:ECDHE-RSA-AES128-SHA:DHE-RSA-AES256-SHA256:DHE-RSA-AES128-SHA256:DHE-RSA-AES256-SHA:DHE-RSA-AES128-SHA:ECDHE-RSA-DES-CBC3-SHA:EDH-RSA-DES-CBC3-SHA:AES256-GCM-SHA384:AES128-GCM-SHA256:AES256-SHA256:AES128-SHA256:AES256-SHA:AES128-SHA:DES-CBC3-SHA:HIGH:!aNULL:!eNULL:!EXPORT:!DES:!MD5:!PSK:!RC4
SSLHonorCipherOrder on
If your version of OpenSSL is old, unavailable ciphers will be discarded automatically. Always use the full ciphersuite above and let OpenSSL pick the ones it supports.
The ordering of a ciphersuite is very important because it decides which algorithms are going to be selected in priority. The recommendation above prioritizes algorithms that provide perfect forward secrecy.
Prioritization logic
ECDHE+AESGCM ciphers are selected first. These are TLS 1.2 ciphers and not widely supported at the moment. No known attack currently target these ciphers. PFS ciphersuites are preferred, with ECDHE first, then DHE. AES 128 is preferred to AES 256. At the moment, AES128 is preferred, because it provides good security, is really fast, and seems to be more resistant to timing attacks. In the backward compatible ciphersuite, AES is preferred to 3DES. BEAST attacks on AES are mitigated in TLS 1.1 and above, and difficult to achieve in TLS 1.0. In the non-backward compatible ciphersuite, 3DES is not present. RC4 is removed entirely. 3DES is used for backward compatibility
Protocol Support: SSL or no SSL
To prevent downgrade attacks and poodle attack, we will also disable old SSL protocols
For Apache:
SSLProtocol all -SSLv2 -SSLv3
For Nginx:
ssl_protocols TLSv1 TLSv1.1 TLSv1.2;
This disables all versions of SSL, enabling only TLS 1.0 and up. All versions of Chrome and Firefox support at least TLS 1.0.
To fix the bug, disable SSLv3 and use a secure cipherlist. SSL v2 is also insecure, so we need to disable it too.
So edit the Apache config file and add following
SSLProtocol All -SSLv2 -SSLv3
All is a shortcut for +SSLv2 +SSLv3 +TLSv1 or – when using OpenSSL 1.0.1 and later – +SSLv2 +SSLv3 +TLSv1 +TLSv1.1 +TLSv1.2, respectively. The above line enables everything except SSLv2 and SSLv3
And then restart the Apache service
service httpd restart
cPanel/WHM
If you have a cPanel server, you should not edit Apache configurations directly, instead you can do this from WHM.
1. Visit your server’s WHM Panel ( https://<yourserversip>:2087 ) 2. Navigate to the Apache Configuration Panel of WHM. 3. Scroll down to the ‘Include Editor’ Section of the Apache Configuration. 4. Click ‘Pre Main Include’, which will jump to the corresponding section. Via the drop-down selector, choose ‘All Versions’. 5. An empty dialogue box will appear allowing you to input the needed configuration updates. In this box, copy and paste the following:
SSLProtocol All -SSLv2 -SSLv3 SSLHonorCipherOrder On
For Nginx
If you’re running an NGINX web server that currently uses SSLv3, you need to edit the NGINX configuration (nginx.conf). You will need to add the following line to your server directive:
The POODLE attack requires the client to retry connecting several times in order to downgrade to SSLv3, and typically only browsers will do this. Mail Clients are not as susceptible to POODLE. However, users who want better security should switch to Dovecot until we upgrade Courier to a newer version.
For cpsrvd:
1. Go to WHM => Service Configuration => cPanel Web Services Configuration 2. Make sure that the “TLS/SSL Protocols” field contains “SSLv23:!SSLv2:!SSLv3”. 3. Select the “Save” button at the bottom.
For cpdavd:
1. Go to WHM => Service Configuration => cPanel Web Disk Configuration 2. Make sure that the “TLS/SSL Protocols” field contains “SSLv23:!SSLv2:!SSLv3”. 3. Select the “Save” button at the bottom.
For Dovecot:
1. Go to WHM => Service Configuration => Mailserver Configuration. 2. SSL Protocols should contain “!SSLv2 !SSLv3”. If it does not, replace the text in this field. 3. Go to the bottom of the page, and select the Save button to restart the service.
For Courier:
Courier has released a new version to mitigate this as of 10/22, until we have an opportunity to review, test, and publish the new version of Courier please switch to Dovecot for enhanced security.
For Exim:
1. Go to Home » Service Configuration » Exim Configuration Manager 2. Under Advanced Editor, look for ‘openssl_options’. 3. Make sure the field contains “+no_sslv2 +no_sslv3”. 4.Go to the bottom of the page, and select the Save button to restart the service.
For Lighttpd:
Lighttpd releases before 1.4.28 allow you to disable SSLv2 only.
If you are running at least 1.4.29, put the following lines in your configuration file:
To make sure services on your server are not accepting SSLv3 connections, you can run the openssl client on your server against the SSL ports. This command is run as follows:
A new vulnerability has been found that potentially affects most versions of the Linux and Unix operating systems, in addition to Mac OS X. Known as the “Bash Bug” or “ShellShock,” the GNU Bash Remote Code Execution Vulnerability could allow an attacker to gain control over a targeted computer if exploited successfully. And because Bash is everywhere on Linux and UNix-like machines and interacts with all parts of the operating system, everyone anticipates that it will have lot of repercussions.
How does Shellshock work?
Shellshock exploits a flaw in how Bash parses environment variables; Bash allows functions to be stored in environment variables, but the issue is Bash will execute any code placed after the function in the environment variable value.
For example, an environment variable setting of VAR=() { ignored; }; /bin/id will execute /bin/id when the environment is imported into the bash process.
I am vulnerable?
You can check if you’re vulnerable by running the following lines in your default shell.
If you see the word “vulnerable” echo’d back , then you’re at risk.
How Shellshock is Impacting the Web
The most likely route of attack is through Web servers utilizing CGI (Common Gateway Interface), the widely-used system for generating dynamic Web content. An attacker can potentially use CGI to send a malformed environment variable to a vulnerable Web server. The attacker is able to inject environment variables inside all bash process spawned by a web server under the CGI specification. This will occur directly if the CGI script is programmed in bash or indirectly by system calls inside other types of CGI scripts since the environment will propagate to the sub-shell. The vulnerability will automatically be triggered at the shell process instantiation. Furthermore if specific headers are used as attack points, the payload may not appear in the webserver logs, letting a compromise occur with virtually no trace of the intrusion.
Example:
CGI stores the HTTP headers in environment variables. Let’s say the example.com is running a CGI application written in Bash script.
We can modify the HTTP headers such that it will exploit the shellshock vulnerability in the target server and executes our code.
Here, the curl is sending request to the target website with the User-Agent containing the exploit code. This code will create a file “Hacked.txt” in the “/tmp” directory of the server.
What can I do to protect myself?
Major operating software vendors including RedHaT, CentOS, etc are already released a initial patch for this bug.
If a patch is unavailable for a specific distribution of Linux or Unix, it is recommended that users switch to an alternative shell until one becomes available.
Need expert assistanace?
I can help you to patch your server against this bug and make sure you and your customers are secure. Mail me at therealfreelancer[at]gmail[dot]com.
If you use the rsync utility to keep your backups synchronized between your servers or with a local machine, you might want to prevent the script from using too much bandwidth. However, rsync makes a lots of network I/O. The point of limiting bandwidth is to make sure your backup scripts don’t clog up the network connection.
Naturally, limiting the amount of bandwidth your backups are using is going to make them happen more slowly, but if you can deal with that, this is the way to do it.
Apache: Multiple SSL websites on a single IP address
Update: This is a new update from a cPanel Tech “There is nothing to enable. As long as you are using cPanel & WHM version 11.38 on CentOS, RHEL, or CloudLinux version 6 or newer, SNI works out of the box”.
One of the frustrating limitations in supporting secure websites has been the inability to share IP addresses among SSL websites. When website administrators and IT personnel are restricted to use a single SSL Certificate per socket (combination of IP Address and socket) it can cost a lot of money. Well we can actually share IP addresses for multiple secure websites. Solving this limitation required an extension to the Transport Layer Security (TLS) protocol that includes the addition of what hostname a client is connecting to when a handshake is initiated with a web server. The name of the extension is Server Name Indication (SNI). SNI is supported in Apache v2.2.12 , and OpenSSL v0.9.8j or later.
With SNI, you can have many virtual hosts sharing the same IP address and port, and each one can have its own unique certificate
Prerequisites to use SNI
Use OpenSSL 0.9.8f or later Build OpenSSL with the TLS Extensions option enabled (option enable-tlsext; OpenSSL 0.9.8k and later has this enabled by default). Apache must have been built with that OpenSSL (./configure –with-ssl=/path/to/your/openssl). In that case, mod_ssl will automatically detect the availability of the TLS extensions and support SNI. Apache must use that OpenSSL at run-time, which might require setting LD_LIBRARY_PATH or equivalent to point to that OpenSSL, maybe in bin/envvars. (You’ll get unresolved symbol errors at Apache startup if Apache was built with SNI but isn’t finding the right openssl libraries at run-time.)
Setting up SNI with Apache
The configuration is pretty simple and straight forward, though I recommend making a backup of your existing httpd.conf file before proceeding.
# Ensure that Apache listens on port 443
Listen 443
# Listen for virtual host requests on all IP addresses
NameVirtualHost *:443
# Go ahead and accept connections for these vhosts
# from non-SNI clients
SSLStrictSNIVHostCheck off
# Because this virtual host is defined first, it will
# be used as the default if the hostname is not received
# in the SSL handshake, e.g. if the browser doesn't support
# SNI.
DocumentRoot /www/example2
ServerName www.linuxwebhostingsupport.in
# Other directives here
SSLEngine On
SSLCertificateFile /path/to/linuxwebhostingsupport.in.crt
SSLCertificateKeyFile /path/to/linuxwebhostingsupport.in.key
SSLCertificateChainFile /path/to/CA.crt
DocumentRoot /www/example2
ServerName www.abdulwahabmp.co.in
# Other directives here
SSLEngine On
SSLCertificateFile /path/to/abdulwahabmp.co.in.crt
SSLCertificateKeyFile /path/to/abdulwahabmp.co.in.key
SSLCertificateChainFile /path/to/CA.crt
That it!!!. Just restart APache service. Now go and check your Websites using https. That should be working.
Plesk support SNI from 10.2.x version onwards.
SNI will work on following Operating systems out of box
OpenSuSE Linux 11.3 or later. Ubuntu Linux 10.4 or later. Debian Linux 6.0 or later. RedHat Linux 6.0 or later. CentOS Linux 60.0 or later
Supported Desktop Browsers Internet Explorer 7 and later Firefox 2 and later Opera 8 with TLS 1.1 enabled Google Chrome: Supported on Windows XP on Chrome 6 and later Supported on Vista and later by default OS X 10.5.7 in Chrome Version 5.0.342.0 and later Chromium 11.0.696.28 and later Safari 2.1 and later (requires OS X 10.5.6 and later or Windows Vista and later). Note: No versions of Internet Explorer on Windows XP support SNI
