Activities

September 2020
M T W T F S S
« Apr    
 123456
78910111213
14151617181920
21222324252627
282930  

Renaming database having innodb tables/Moving inoodb database

While you are working with Huge MySQL data, you will need to switch your tables to innodb storage engine to get fast transactions performance. Suppose if you have a requirement to restore the database which having +>400G volume and the target server having the same database name exist. So if you restore the database, the old database will become wiped out. In this scenario, you would think about renaming database having innodb tables. Innodb tables/database and other index entries stored in idbata file inside mysql data directory. The following commands will re-arrange the ibdata file entries rather moving files over the disk. So this will not consume much time and become easier.

How to move database having innoDB tables,

1. create a new database
2. Execute “ALTER TABLE old_db.tablename RENAME new_db.tablename;”

eg. ALTER TABLE hugedb.PlanData5500Raw RENAME bigdb.PlanData5500Raw;

alter_table

Suppose if you having more than 300 tables in your database, execution of this statement would become hard. So I created a script to rename the above statements.

#!/bin/bash
# this script will move all the innodb tables from old_db to new_db. Pls update the database name in db variables.
touch table_names.txt
> table_names.txt
old_db="bigdb"
new_db="hugedb"
mysql -uroot  -hlocalhost $old_db  --skip-column-names -e "SHOW TABLES" >>table_names.txt
while read table;
do
total=0
echo " Moving ${table} to $new_db now...... "
mysql -uroot  << EOF
ALTER TABLE $old_db.$table  RENAME $new_db.$table;
EOF

#mysql -e 'ALTER TABLE $old_db.$table   RENAME $new_db.$table;'
done < table_names.txt

Your account has expired; please contact your system administrator

I had setup a normal user account on one of local Linux server few years before. When I’m trying to ssh to that server, I’m getting “Your account has expired; please contact your system administrator”. I did not set any password expiration when I’m creating this user account. So I think system using default account expiration settings.

password_expiration

How do I enable my user account.

a. Modify the expiration date to never (chage -M -1 username)
b. Reset the current expiration date to higher than current date(usermod -e 2018-01-01 username).

Also I’m seeing “/var/log/secure” showing following error,

Nov 20 12:04:39 rc-025 sshd[17281]: pam_unix(sshd:account): account liju has expired (account expired)
Nov 20 12:04:39 rc-025 sshd[17281]: Failed password for liju from 192.168.0.14 port 63664 ssh2
Nov 20 12:04:39 rc-025 sshd[17282]: fatal: Access denied for user liju by PAM account configuration

a. Modifying expiration date to never

[root@025 jigie]# chage -l liju
Last password change                                    : Jul 03, 2015
Password expires                                        : Nov 30, 2015
Password inactive                                       : never
Account expires                                         : Aug 25, 2015
Minimum number of days between password change          : 60
Maximum number of days between password change          : 150
Number of days of warning before password expires       : 7
[root@025 jigie]#  chage -M -1 liju
[root@025 jigie]# chage -l liju
Last password change                                    : Jul 03, 2015
Password expires                                        : never
Password inactive                                       : never
Account expires                                         : Aug 25, 2015
Minimum number of days between password change          : 60
Maximum number of days between password change          : -1
Number of days of warning before password expires       : 7

b. Setting expiration date is to higher

[root@025 jigie]# usermod -e 2018-01-01  liju
[root@025 jigie]# chage -l liju
Last password change                                    : Jul 03, 2015
Password expires                                        : never
Password inactive                                       : never
Account expires                                         : Jan 01, 2018
Minimum number of days between password change          : 60
Maximum number of days between password change          : -1
Number of days of warning before password expires       : 7
[root@025 jigie]#

MySQL : Managing Sleeping / Active connections to avoid “too many connections” error

One the busiest MySQL server recently had an issue with “SQL Error 1040: Too Many Connections”. This server has already set 1000 connections limit which I believe quite enough to handle our platform. But we were getting frequent MySQL outage due to this kind of errors. We know that it’s a application leakage issue and can’t be find a permanent solution at MySQL server end.

This is directly cause to the value setup of these variables wait_timeout and inactive_timeout

variable represents the amount of time that MySQL will wait before killing an idle connection. The default wait_timeout variable is 28800 seconds, which is 8 hours

2. interactive_timeout : The number of seconds the server waits for activity on an interactive connection before closing it.

We will lost mysql connectivity when no. connection exceed the “max_connections” limit. So I have set a script for
1. Terminating all the sleeping process those are either running for a time period or flush out all the sleeping process when connection limits are getting reached.

#!/bin/bash
# This script will terminate all the sleeping process which consuming more than 30 minutes. Also whitelising a host which is used for critical operations
# Sleeping time is set to 30 minutes and Web01 connections are excluded
> sleep.txt
mysql -e "show processlist" | grep -E "Sleep" | awk '{print $6,"  " $1,"  "  $3} '| cut -d ':' -f1 | sort  -nr  >> sleep.txt
total=0
while read sleep_array;
do
name=$sleep_array

#echo " $name"
sleep_time=$(echo $name |awk '{print $1}')
pid=$(echo $name |awk '{print $2}')
host=$(echo $name |awk '{print $3}')

    if [ $sleep_time -ge 3600 ] && [ $host != "10.10.10.20" ]; then
    #        echo "The process id to be killed $pid, it's time is $sleep_time, host name $host "
            mysqladmin kill $pid
    else
            echo " "
    fi

done <sleep.txt

The following script will terminate all the connections when no. connections are reaching maximum connection limit.

Here is the download link. Set this script to run on every 2 minutes. This script will do the following items,
1. Whether the connection is meet the threshold limit and terminate all the process from MySQL memory.
2. Whitelist the connections from certain hosts
3. Provide the the report of Connections details of Sleeping and active sorted by host ip.
4. Send us the report via an email.

Cheers 🙂

WHM/Cpanel : Configure SMTP relay server on EXIM

Yesterday one of my friend was called me and asked for a support to fix email sending issue from Godaddy VPS server. As I know Godaddy put very great efforts to keep their ip reputation over the web. So they used to block sending out emails from their network even it for dedicated hosting or shared hosting.

I’m okay if the rescriction put only for shared hosting. But they do even purposefully blocked emails in network level in dedicated hosting network. This was the core issue which my friend is facing. I had tried,

1. To send emails from different users in the same cpanel accounts. But email did not go. –> failed
2. sending email from mail function –> failed
3. Using 3rd party smtp program (sendEmail) to send email — > failed.

Then I login to customer Godaddy account and finding an entry of relay smtp within the VPS account. But I did not see it and so I has to call them. They provided the smtp relay server name (dedrelay.where.secureserver.net –> 208.109.80.210) and I could send emails from command line now. God bless 🙂 But neither of EXIM mails were not going yet … Sucks!!! 🙁

Then I’m concentrate on EXIM config. to set smarthost(relay) server setup. Godaddy Provided a link which is absolutely a nonsense after following it. Nothing works.

Godady_advise

So usual.. Googling . it is come to find a nice tutorial link which I found works pretty well.

Solution

Edit the file (/etc/exim.conf) and modify the mode of localhost router section line number 1295. It will look like this

exim_config

Now you need to change the “driver” to manualroute and append the 3 lines under “localhost” route section area. Also You may need to find the smtp relay server IP. Here Godaddy given dedrelay.where.secureserver.net which resolves to 208.109.80.210 IP. So entry will look like below,

transport = remote_smtp
route_list = * 208.109.80.210
host_find_failed = defer
no_more

exim_config2

Then you need to restart exim service

root@s192-sd [~]# /etc/init.d/exim restart
Shutting down exim:                                        [  OK  ]
Shutting down spamd:                                       [  OK  ]
Starting exim:                                             [  OK  ]
0 processes (antirelayd) sent signal 9
Starting spamd:                                            [  OK  ]
root@s19s [~]#

You may see the mail delivery statistics by opening “/var/log/exim_mainlog” file. You could see the relay smtp IP (208.109.80.210) listed in log files.

#tail -f /var/log/exim_mainlog
2015-11-12 23:57:18 1Zx8IQ-0006Qg-CL SMTP connection outbound 1447397838 1Zx8IQ-0006Qg-CL inesdsd.info liju@serveridol.com
2015-11-12 23:57:18 1Zx8IQ-0006Qg-CL => liju@serveridol.com R=lookuphost T=remote_smtp H=208.109.80.210 [208.109.80.210] C="250 2.0.0 guxJ1r02h3mL6bk01 mail accepted for delivery"
2015-11-12 23:57:18 1Zx8IQ-0006Qg-CL Completed

Note: Don’t forget to add your smtp relay server IP in your domain SPF record. Otherwise those email will be treated as spam

The easiest way is to edit the configuration changes over the WHM exim editor.

The above custom edit would be over written when WHM/Cpanel updates happen.

1. Log into WHM
2. Click on Exim Configuration Editor > Advanced Editor
3. Find this section:

######################################################################
# ROUTERS CONFIGURATION #
# Specifies how remote addresses are handled #
######################################################################
# ORDER DOES MATTER #
# A remote address is passed to each in turn until it is accepted. #
######################################################################

# Remote addresses are those with a domain that does not match any item
# in the "local_domains" setting above.

#
# Demo Safety Router
#

Eximi_SMTP_rely

Amazon EC2 : Enforcing HIPPA compliance hosting

HIPPA (Health Insurance Portability and Accountability Act) which directs the U.S. Department of Health and Human Services to establish national standards for processing electronic healthcare transactions. It also requires healthcare organizations to implement secure electronic access to health data and to remain in compliance with privacy regulations set by HHS. So any data which carry medical related information should be meet this HIPPA policy. So the data they are sharing over the web (on fly data) and the data those are stored in disk (on rest data) should be encrypted and secured.

These are the major aspects of HIPPA compliance which I’m explaining about.

1. Security of on fly data
2. Encryption of On Rest data
3. Backup data

my projects uses Linux platform and having 3 web servers, MySQL RDS and amazon S3 storage. I’ve configured one of the web server as a NFS file server for sharing common files with other node web servers. Prior to HIPPA compliance,

1. Migrate instance to dedicated instances

Most the customers are using On Demand instance type and default tenancy which is using a shared resources from Amazon cloud network.

Hipp-1

There is no any option to convert On Demand instance to Dedicated one. First we need to create EBS AMI of on demand instance and choose the option “Dedicated tenancy(single tenant hardware)” which configuring replacement instance.

Hipp-2

Now your instance become HIPPA compliance.

2. Using encrypted EBS volume (on Rest rule).
All the customer/medical related information should be stored in a encrypted disk/drive. So that no one can read the data if your storage was stolen. Windows 8 and Linux Operating systems have this kind of mechanism to set a password to unlock the drive. We need to set the password along with mount point when disk is attached to OS. So OS will not ask password. Back to Amazon EC2, we need to migrate existing volume to encrypted volume. So create new volume and enable encryption on it while you setting up the storage. You can easily create KIM

Hipp-3

Or you can create snapshot of existing volume and set encryption when you create disk from it.

Note : We can not encrypt the EBS boot volume. So do not store any PHI (Patient Health Information) date on non-encrypted EBS volume.

3. Backup EBS volume should be encrypted.

You would need to encrypt the disk volume where you keep the backup and ensure that only authorized people can access it and logins/access are audited.

4. PHI data should be transmitted over SSL (https)
If you are using any web servers for populating medical data, you need to install SSL certificate and all the pages can be accessible only over https. So web server has set to force all the pages securely.

5.Securing Amazon S3 content
Amazon offers secure communication on the objects those are stored in the S3 storage. Pls be ensure that you are serving the s3 files only over https tunnel. Or you can encrypt and decrypt the files through your application if you have a need of it. But this may cost you more CPU cycles and delay which will be taken for this end to end encryption and decryption.

Once you’ve done the above 5 steps, your platform will be HIPPA compliance hopefully and you may go with BAA signing.

Magento install : Unknown cipher in list: TLSv1

While I’m trying to install a Marketplace extension in freshly installed Magento (1.9.2) I got the following error,

magento_extension_error

This is due to this package installer always trying to connect it’s package store over ssl area where tls encryption is enabled. So this is not work with our marketplace store which does not support this. So we have to disable this feature by editing magento config. files,

Solution

1. Edit the file downloader/lib/Mage/HTTP/Client/Curl.php in your Magento installation and replace the line ($this->curlOption(CURLOPT_SSL_CIPHER_LIST, ‘TLSv1’);) with following lines

if(isset($var)){
$this->curlOption(CURLOPT_SSL_CIPHER_LIST, 'TLSv1');
}

2. Set ftp server details in settings tab

Also you need to configure “ftp” details in settings tab. Otherwise you can not proceed with the installation, This area is also confusing me as I given server public IP as ftp host name. But it will not take even though credentials are same. So you need to use “localhost” as ftp server name and set “public_html” or Magento ftp installation folder as “Installation path”

magento_extension_error1