TechniTip.Net

MySQL Optimize Script

A nice small PHP script which simple connects to a given MySQL server and optimizes all databases and included tables. Only the internal table “information_schema” is skipped because it will show an error.

Working with MySQL version 5.0.32 and PHP version 5.2.0 on Debian Etch.

Script is designed to run from command line “php optimize.php” maybe from a cron job. Be careful: avoid running this script during your server “rush hour”, it may slow down your server.

<?php
/***********************************************************
 optimimze.php - optimizes all databases and tables of the
                 given mysql host.

 2008 - technitip.net
 ***********************************************************/

$mysqlhost = "localhost"; // enter MySQL host
$mysqluser = "user";      // enter MySQL user
$mysqlpwd  = "password";  // enter password

###########################################

$connection = mysql_connect($mysqlhost, $mysqluser, $mysqlpwd);
if (mysql_error())
{
  echo "Could not connect to database server! " . mysql_error() . "\n";
  exit;
}

$db_list = mysql_list_dbs();
$i = 0;
$cnt = mysql_num_rows($db_list);

while ($i < $cnt)
{
  $db = mysql_db_name($db_list, $i);
  ###########################################
  mysql_select_db($db, $connection);
  $result = mysql_list_tables($db);

  while ($row = mysql_fetch_row($result))
  {
    if ( $db == "information_schema" )
      continue;

    echo $db . " : `" . $row[0] . "`";

    $sql = "OPTIMIZE TABLE `".$row[0]."`";
    $erg = mysql_query($sql, $connection) or die(mysql_error());
    $data= mysql_fetch_array($erg, MYSQL_ASSOC);

    if($data)
    {
      echo " - " . $data['Msg_text'] . "\n";
    }
  }
  ###########################################
  $i++;
}

?>

Annoyed from Caps Lock?

EVERYONE KNOW’S IT… hups… entering a password doesn’t work and so on. Why? Caps Lock is activated.

Have a look at CAPSoff.org I agree, no one needs this useless and annoying key.

There is a good workaround from heise.de available to disable this key. Using this direct download link from heise.de you can download a Windows registry key which disables your caps lock key. To install double click on the CapsLockOff.reg and choose “yes”. Afterwards reboot your PC and caps lock will be disabled, how nice!

Free iPhone Game Solitaire City

I could survive several years now without playing Solitaire, good luck. But after obtaining a iPhone things got worse and I’m addicted again…

A must for Solitaire addicts! Many options, nice graphics and cute sound effects.

The lite version is free and fulfills my whishes absolutely. Get it from SolitaireCity or directly from your iTunes shop under free applications.

Update: The latest lite version includes less games, unfortunalty my favorite is missing now. Therefore I’ve purchased the full version now. According to feedback in the iTunes store the new version is a little bit unstable. I can confirm that the app does sometimes terminate when choosing games, but playing itself seems stable. For me still worth paying for the app.

Simple MySql Backup Script

The following example scripts performs a simple backup of all MySQL databases. The resulting .sql file is automatically zipped. Using “find” backups older than 3 days are deleted, so you will get complete backups of the last 3 days.

This script is intended to be called periodically (e.g. every day) from cron:

2 2       * * *   root /root/scripts/mysql_backup.sh

#/bin/sh
now=`date "+%Y-%m-%d"`
user="mysql_user"
password="mysql_password"
path="/home/backup/"

cd $path

mysqldump -u $user -p$password  --all-databases | gzip -c > backup_all_$now.sql.gz

# delete files older than 3 days
find . -name "*.gz" -type f -mtime +3 -exec rm {} ";"

Search and Replace over Multiple Files

Sometimes you need to search and replace strings over multiple files. This can easily be done using perl. I found this tip from www.liamdelahunty.com/tips

perl -pi -w -e 's/search/replace/g;' *.php

-e means execute the following line of code.
-i means edit in-place
-w write warnings
-p loop

Linux Network Traffic Monitoring

You are running a Linux server and have no possibility to monitor your traffic monthly, daily, weekly etc? Then you really should try vnstat. I like it because it’s:

• easy to install
• easy to use
• collects long term traffic statistics
• doesn’t generate much CPU load

For people who don’t like the shell check out the vnstat PHP frontend.

Installation is esay using Debian Etch:

apt-get install vnstat
vnstat -u -i eth0

For certain virtual servers you have to use a different ethernet interface than “eth0″, e.g. “venet0″:

vnstat -u -i venet0

That’s all. vnstat will now be executed periodically from cron. Some time passed you can check your traffic, like in the example below.

host:~# vnstat -m

eth0  /  monthly

 month         rx      |      tx      |   total
-----------------------+--------------+--------------------------------------
Jan '08     252.40 GB  |     4.17 TB  |     4.41 TB   %:::::::::::
Feb '08     140.07 GB  |     3.96 TB  |     4.10 TB   :::::::::::
Mar '08     152.46 GB  |     4.21 TB  |     4.36 TB   ::::::::::::
Apr '08     150.35 GB  |     4.11 TB  |     4.26 TB   ::::::::::::
May '08     118.78 GB  |     3.53 TB  |     3.65 TB   ::::::::::
Jun '08     144.95 GB  |     4.54 TB  |     4.68 TB   :::::::::::::
Jul '08     157.09 GB  |     4.91 TB  |     5.06 TB   ::::::::::::::
Aug '08     160.96 GB  |     5.01 TB  |     5.17 TB   :::::::::::::::
Sep '08     172.33 GB  |     5.43 TB  |     5.60 TB   ::::::::::::::::
Oct '08     189.69 GB  |     6.12 TB  |     6.31 TB   %:::::::::::::::::
Nov '08     190.89 GB  |     6.52 TB  |     6.71 TB   %::::::::::::::::::
Dec '08     209.59 GB  |     7.35 TB  |     7.55 TB   %:::::::::::::::::::::
-----------------------+--------------+--------------------------------------
estimated   219.94 GB  |     7.71 TB  |     7.93 TB

Defending Againt SSH Attacks

As soon as you put a server online you will recognize many many attempts accessing the SSH port like these one:
Dec 28 06:48:11 hostname sshd[12258]: (pam_unix) authentication failure; logname= uid=0 euid=0 tty=ssh ruser= rhost=somehost.somewhere
Dec 28 06:48:12 hostname sshd[12256]: error: PAM: User not known to the underlying authentication module for illegal user saman from somehost.somewhere
Dec 28 06:48:13 hostname sshd[12256]: Failed keyboard-interactive/pam for invalid user saman from xx.xxx.xx.xxx port 27751 ssh2
Annoying script kids… An easy method to protect at least a little bit against such attacks is a tool called denyhosts. I use it on all my servers because it’s easy to install. denyhosts automatically blocks ssh attacks by adding entries to /etc/hosts.deny.

Using debian Etch installation is really simple:

apt-get install denyhosts

Next edit /etc/denyhosts.conf and change:

PURGE_DENY = 1w
PURGE_THRESHOLD = 0

and start the daemon:

/etc/init.d/denyhosts start

That’s all. Sure it’s not a perfect solution which helps for everything, but nevertheless it will increase your security.

All you have to to now is wait and check the /etc/hosts.deny Depending on your configuration denyhosts will automatically delete expired entries.

Apache Tuning Part 3

Some further settings which worked well for a server (1GB RAM, single CPU) with max. 120 users simultaneously and doing some video streaming. Settings are found in /etc/apache2/apache.conf

Timeout 30

KeepAlive On

MaxKeepAliveRequests 100

KeepAliveTimeout 5

<IfModule mpm_prefork_module>
StartServers         16
ServerLimit         512
MinSpareServers      16
MaxSpareServers      128
MaxClients          256
MaxRequestsPerChild 55555
</IfModule>

HostnameLookups Off

ServerTokens Prodme
ServerSignature Off
UseCanonicalName Off
TraceEnable Off

HostNameLookups Off are recommended in any case.

Apache Tuning Part 2

Let’s assume your web server has high disc IO. Every access to a .PHP, .HTML, .JPG, .GIF, etc wil cause a log entry which will be stored on hard disc. In such a case it helps to disable all apache loggings.

You can check the opened log file from apache using the command:

lsof |grep apache|grep log

Oh yes, if you serve lot’s of domains on your server this list will be quite long. To disable logging just uncomment the corresponding lines e.g. in your virtual host definition:

#ErrorLog /var/log/apachd2/mydonain.de-error_log

#CustomLog /var/log/apache2/mydomain.de-access_log combined

Afterwards check your apace configuration for errors:

apache2ctl -t

In case of syntax OK just restart your apache server:

apache2ctl -k graceful

This command is qute because it does a “graceful” restart without anoying users accesing your server right now.

Hint

How to check if my disc IO is high?

Use the command “vmstat 1″ and check the section “IO” (what else…). You will see bi and bo which means blocks received and blocks sent. In case the numbers are high, you will have high disc IO.

procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
0  0 659476  41604   8732 347304    3    3    37    41   20   14 19  7 58 16
3  0 659476  41604   8732 347336    0    0     0     0  410  314  7  0 93  0
0  0 659476  41464   8740 347328    0    0     0   424  522  321 11  1 86  2

Apache Tuning Part 1

This article applies to apache version 2.2.3 also it will work for other versions.

Apache needs a lot of memory, this will decrease performance on a machine with e.g. 1 GByte memory and about 60 users accessing the server simultaneously a lot. There is a simply method to save memory: disable all unused modules. Yes, it really helps!

Why does it help? Because every apache process started – and a lot will be started when several users are accessing the server – will load all enabled modules.

To get an overview how much memory is consumed by the modules use the command

lsof | grep apache | grep modules

Using this command you will also see which modules consumes much memory.

In my case only the following modules are used:

• actions.load
• authz_default.load
• authn_file.load
• auth_basic.load
• authz_user.load
• env.load
• mime.load
• rewrite.load
• userdir.load
• alias.load
• authz_host.load
• deflate.load
• expires.load
• negotiation.load
• setenvif.load
• headers.load
• speling.load
• cgi.load
• dir.load
• include.load
• php5.load

Modules which are not needed are disabled using the command

a2dismod <module_name>