Shrink ibdata1 file in MySQL

The ibdata1 file contains the table data of your InnoDB tables. In large servers, this file can grow up to a very large size. When you delete innodb tables, MySQL does not free the space inside the ibdata1 file, that’s why it keeps growing. This file cannot be shrunk unless you delete all databases, remove the files and reload a MySQL dump.

In order to setup your server to use separate files for each table, you need to add the following parameter in my.cnf file.

[mysqld]

innodb_file_per_table

In order to shrink ibdata1, you actually need to delete the file.

Do a mysqldump of all databases, except the mysql and performance_schema

Drop all databases except the above 2 databases

Stop mysql

Delete ibdata1 and ib_log files

Start mysql

Restore from dump

When you start MySQL in step 5 the ibdata1 and ib_log files will be recreated.

Advertisements

Limit autoreply in Plesk Server

If I enable auto-reply in Plesk Panel and send a test email to that account, I get an auto-response. However, if sending another test email, I do not get an autoresponse.
There is a parameter in the “psa” database — mail_resp.ans_freq — that defines the daily auto-reply limit for each mailbox, and it is set to “1” by default.

Resolution
You can manage the amount of auto-replies in mailbox settings:

In the mail_resp table of the psa database, modify the ans_freq field.

1. Log in to the “psa” database:

mysql –uadmin –p`cat /etc/psa/.psa.shadow` psa

2. Check the current value of the parameter:

mysql> select concat(m.mail_name , ‘@’ , d.name) as mailname, mn_id, r.ans_freq from mail m, domains d , mail_resp r where m.dom_id = d.id and r.mn_id = m.id;
+——————————-+——-+———-+
| mailname | mn_id | ans_freq |
+——————————-+——-+———-+
| test@example.com | 5 | 1 |
+——————————-+——-+———-+

3. To set the value for all mailnames, run:

mysql> update mail_resp set ans_freq=XX;

(where “XX” – is the desired limit).

4. To set the value for a particular mailbox, you may refer to it by its mn_id, for example:

mysql> update mail_resp set ans_freq=XX where mn_id=5;

Reading contents of gz compressed file

In some cases, we do not have free disk space in server, but still needs to read a .gz compressed file.
Normal “cat” function will not be helpfull in this case as the file is compressed.

Here, we are going to use another tool “z*”

These command allows you to read gzip compressed text files using zless, zcat, zmore commands.

Usage
To display a file

zcat file.gz
zmore file.gz
zless file.gz

To compare two files

zdiff file1.gz file2.gz
zcmp file1.gz file2.gz

zgrep command to search a word from file

zgrep ‘Searchword’ file.gz

List the contents of a tar file

There may be some scenarios where we have a tar compressed file and we need to view the contents of file before extracting it. For example, if server disk space is almost full and do not have enough space to extract.

Inorder to view the contents of tar file we can use the following command

tar -tvf file.tar

Inorder to view the contents of tar.gz file we can use the following command

tar -ztvf file.tar.gz

Inorder to view the contents of tar.bz2 file we can use the following command

tar -jtvf file.tar.bz2

Where,

t = List contents

z = extract .gz file

j = extract .bz2 file

v = for providing detail description

f = file name

 

Install Varnish in Ubuntu

Varnish is a very useful application to speed up your website during heavy traffic.

Installing Varnish

We need to add Varnish packages to our server repository to install it.

sudo curl http://repo.varnish-cache.org/debian/GPG-key.txt | sudo apt-key add –

Add the repo to our apt sources

cat /etc/apt/sources.list

deb http://repo.varnish-cache.org/ubuntu/ lucid varnish-3.0

Do an apt-get update and install Varnish

sudo apt-get update

sudo apt-get install varnish

Configure Varnish with apache2

We need to make Varnish to LISTEN to port 80 and apache to some other port say 8008

Open up the file “/etc/default/varnish” and add the following lines

cat /etc/default/varnish

DAEMON_OPTS=”-a :80

-T localhost:6082

-f /etc/varnish/default.vcl

-S /etc/varnish/secret

-s malloc,256m”

Now open the file “/etc/varnish/default.vcl” and the content as below.

cat /etc/varnish/default.vcl

backend default {

.host = “IP”;

.port = “8008”;

}

Now all set for Varnish, we need to configure apache to LISTEN to port 8008.

Edit “/etc/apache2/ports.conf” and add the following contents.

cat /etc/apache2/ports.conf

Listen *:8008

Now we need to edit all virtual host files and change apache conf port from 80 to 8008.

cat /etc/apache2/sites-available/default

<VirtualHost *:8008>

Restart apache and and varnish and test sites.

sudo service apache2 restart

sudo service varnish restart

If you have a large server having too many sites, try to make varnish to listen to some other port and keep apache to same port to test varnish working. Later then make the changes, keep in mind downtime!!

Install AVG Antivirus in Centos

Download rpm from http://free.avg.com/download.prd-afl

wget http://download.avgfree.com/filedir/inst/avg2013flx-r3115-a6155.i386.rpm

rpm -ivh avg2013flx-r3115-a6155.i386.rpm

You’re on a 64-bit system, and don’t have 32-bit library support installed, you will get error

>>>>>>/lib/ld-linux.so.2: bad ELF interpreter: No such file or directory<<<<<<<<

fix
#sudo yum install glibc.i686

then start the avg service

#/etc/init.d/avgd start

To update virun definition
#avgupdate

Cannot add new email address in Plesk email blacklist

This article is to explain how to increase the maximum number of addresses that can be added to the Spamassassin White or Black list.

==================================

The following command can be used to connect to the Plesk database:

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

 

As of Plesk Control Panel version 8.4.0, you may configure the maximum number of addresses that can be added to the Spamassassin White or Black list through the Control Panel.

Per the variable psa.misc.spamfilter_max_addr_list_length in the Plesk database, 100 addresses are used by default.

Use the following SQL query to set the maximum number of addresses to 150:

mysql> replace into misc values(‘spamfilter_max_addr_list_length’,150);
Query OK, 1 row affected (0.00 sec)

mysql>

If a row with the spamfilter_max_addr_list_length parameter is absent in the misc table, it is possible to add the row with the following query:
mysql> insert into misc (param,val) values (‘spamfilter_max_addr_list_length’,150);

==================================

Create Container (OpenVZ)

To create an OpenVZ Container, a container ID and an OS Template is needed.

 

OS Templates are stored in directory “/vz/template/cache/” . We can see which OS templates are available and also can download OS Templates here.

 

We can choose a Container ID which is not present in Hardware node.

The command “vzlist -a” will give you list of containers and their corresponding container ID available.

 

To create a container use the following commands

vzctl create 101 --ostemplate centos-5-x86_64

 

Where 101 is the Container ID, centos is the OS template selected for the container.

 

We can also set additional parameters like setting IP address, nameservers etc.

vzctl set CTID --ipadd a.b.c.d --save
vzctl set CTID --nameserver a.b.c.d --save

 

To start a container use the following commands

vzctl start CTID

 

To enter into a container

vzctl enter CTID

 

To stop a container

vzctl stop CTID

 

To destroy a container

vzctl destroy CTID

Create and secure /tmp

This article explains how to create a secure /tmp partition even while your server is already up and running.

We are doing here is to give /tmp it’s own partition and mount it using noexec- This would protect your system from many local and remote exploits of rootkits being run from your /tmp folder.

Login to your server as root and run following commands.

==============================================

cd /dev

Create 100MB file for our /tmp partition. If you need more space, make count size larger.

dd if=/dev/zero of=tmpMnt bs=1024 count=100000

Make an extended filesystem for our tmpMnt file

/sbin/mke2fs /dev/tmpMnt

Backup your /tmp dir- I had mysql.sock file that I needed to recreate the symbolic link for. Other programs may use it to store cache files or whatever.

cd /

cp -R /tmp /tmp_backup

Make sure unmount current /tmp

Mount the new /tmp filesystem with noexec

mount -o loop,noexec,nosuid,rw /dev/tmpMnt /tmp

chmod 1777 /tmp

Copy everything back to new /tmp and remove backup

code:
cp -R /tmp_backup/* /tmp/

code:
rm -rf /tmp_backup

Now we need to add this to fstab so it mounts automatically on reboots.

pico -w /etc/fstab

You should see something like this:

/dev/hda3               /                       ext3    defaults,usrquota        1 1
/dev/hda1               /boot                   ext3    defaults        1 2
none                    /dev/pts                devpts  gid=5,mode=620  0 0
none                    /proc                   proc    defaults        0 0
none                    /dev/shm                tmpfs   defaults        0 0
/dev/hda2               swap                    swap    defaults        0 0

At the bottom add

/dev/tmpMnt             /tmp                    ext2    loop,noexec,nosuid,rw  0 0

(Each space is a tab)
Save it!
Ctrl + X and Y

==============================================

Now /tmp is now mounted as noexec.

Dedicated IP to subdomain

Sometime you might want to add a dedicated IP to subdomain alone, such as for applying SSL for subdomain.

Here we are discussing the basic steps for assigning a dedicated IP for subdomain in a cPanel server.

Scenario,
– You have domain domain.com and subdomain forum.domain.com.
– You wish to set dedicated IP for forum.domain.com which originally 100.100.100.100 and result as forum.domain.com resolve to 100.100.100.101.

1. Log into SSH, go to the folder of /var/cpanel/userdata,
cd /var/cpanel/userdata
2. Select the cPanel username folder such as test.
cd /var/cpanel/userdata/test
3. You will now see the following file,
domain.com
domain.com.cache
forum.domain.com
forum.domain.com.cache
main
main.cache

4. Open the file named forum.domain.com,
vi forum.domain.com
5. You should find the content as below,
ip: 100.100.100.100
6. Change it to detail as below,
ip: 100.100.100.101
7. Now, you have done but you will need to rebuilt the Apache,
/usr/local/cpanel/scripts/rebuildhttpdconf
8. Restart the Apache services,
/usr/local/cpanel/scripts/restartsrv_apache