Posts Tagged ‘Temporary’

How to log every Linux executed command by every running system program to separte log via rsyslog for better server Security and audit trails

Wednesday, March 15th, 2023

snoopy-log-all-commands-on-linux-server-tux-logo

To keep a good eye on installed Debian Linux server security if you have to be PCI compliant (e.g. follow a high security) standards or you work in a company, where system security is crucial and any kind of security breach is untorrelated and in case of unexpected security holes exploited on running system processess listening on network peripherals (that malicious crackers) does to be able to easily identify what really happened e.g. do a Security RCA (Root Cause Analysis) for how this hack happened in order to mitigate it for future if possible capture the crackers and close the security hole the better, some kind of paranoid running program logging is required.

For such higher security systems, Linux / BSD / UNIX sysadmins can benefit from;

Snoopy command logger – a small library that logs all program executions on your Linux/BSD system.

Embedding snoopy into a running uptodate system is relatively easy, you either have to download the respective distribution package (in this particular article that would be Debian GNU / Linux) or for Linux distributions, that doesn't have the package integrated into the existing package repositories or externally available package repos, the code can be easily git cloned and installed from github snoopy program page following the README.md


However consider that snoopy run and logging the executed commands, make sure that if you use it you have rsyslogd configured to log to external logging server to make sure (someone did not manipulate the running system to avoid their actions being logged by snoopy, this is pointed by snoopy security disclaimer on the FAQ of official github snoopy project page, the page reads as so:

Security disclaimer
WARNING: Snoopy is not a reliable auditing solution.
Rogue users can easily manipulate environment to avoid their actions being logged by Snoopy. Consult this FAQ entry for more information.                


Most likely this warning is pointed out by the tool authors, in order to set the logging Tool creators free for any liability in case if someone uses the snoopy tool for some unauthorized logging
and sniffing of systems etc.

Before we proceed with the tool, install first for some clarity it is a good idea to know on what kind of Debian Linux you're about to install Snoopy command logger.

root@linux:~ # cat /etc/os-release
PRETTY_NAME="Debian GNU/Linux 11 (bullseye)"
NAME="Debian GNU/Linux"
VERSION_ID="11"
VERSION="11 (bullseye)"
VERSION_CODENAME=bullseye
ID=debian
HOME_URL="https://www.debian.org/"
SUPPORT_URL="https://www.debian.org/support"
BUG_REPORT_URL="https://bugs.debian.org/"


1. Prepare separate log file for snoopy that will keep log of every system command run by running processes visible by (ps -ef)

Next check the permissions user / group and read / write / executable flags with which the default generated rsyslog will be writting and set snoopy to whatever you would like it to write with

root@linux:~ # cat /etc/rsyslog.conf | grep "^\$File\|\$Umask"~
$FileOwner root
$FileGroup adm
$FileCreateMode 0640


Create Rsyslog configuration for snoopy.log

root@linux:~ # cat << EOF | sudo tee /etc/rsyslog.d/01-snoopy.conf
# Send snoopy messages to a dedicated logfile
if (\$programname startswith "snoopy") then {
  action(type="omfile" fileOwner="root" fileGroup="root" fileCreateMode="0600" file="/var/log/snoopy.log")
  stop
}
EOF


To make sure that snoopy library will be preloaded after installation on next boot:

root@linux:~ # cat << EOF | sudo debconf-set-selections
snoopy snoopy/install-ld-preload boolean true
EOF

 

root@linux:~ # systemctl restart rsyslog

 

root@linux:~ # systemctl status rsyslog
● rsyslog.service – System Logging Service
     Loaded: loaded (/lib/systemd/system/rsyslog.service; enabled; vendor preset: enabled)
     Active: active (running) since Tue 2023-03-14 12:59:05 EET; 59min ago
TriggeredBy: ● syslog.socket
       Docs: man:rsyslogd(8)
             man:rsyslog.conf(5)
             https://www.rsyslog.com/doc/
   Main PID: 713745 (rsyslogd)
      Tasks: 6 (limit: 4654)
     Memory: 1.1M
        CPU: 548ms
     CGroup: /system.slice/rsyslog.service
             └─713745 /usr/sbin/rsyslogd -n -iNONE

мар 14 12:59:05 haproxy2 systemd[1]: Started System Logging Service.
мар 14 12:59:05 haproxy2 rsyslogd[713745]: warning: ~ action is deprecated, consider using the 'stop' statement instead [v8.210>
мар 14 12:59:05 haproxy2 rsyslogd[713745]: [198B blob data]
мар 14 12:59:05 haproxy2 rsyslogd[713745]: [198B blob data]
мар 14 12:59:05 haproxy2 rsyslogd[713745]: [198B blob data]
мар 14 12:59:05 haproxy2 rsyslogd[713745]: [198B blob data]
мар 14 12:59:05 haproxy2 rsyslogd[713745]: imuxsock: Acquired UNIX socket '/run/systemd/journal/syslog' (fd 3) from systemd.  [>
мар 14 12:59:05 haproxy2 rsyslogd[713745]: [origin software="rsyslogd" swVersion="8.2102.0" x-pid="713745" x-info="https://www.>
мар 14 13:19:05 haproxy2 rsyslogd[713745]: — MARK —
мар 14 13:39:05 haproxy2 rsyslogd[713745]: — MARK —


2. Install snoopy deb package and configure it

root@linux:~ # apt install snoopy
Четене на списъците с пакети… Готово
Изграждане на дървото със зависимости… Готово
Четене на информацията за състоянието… Готово
Следните пакети са били инсталирани автоматично и вече не са необходими:
  bsdmainutils cpp-8 geoip-database libasan5 libbind9-161 libcroco3 libdns1104 libdns1110 libevent-core-2.1-6
  libevent-pthreads-2.1-6 libgdk-pixbuf-xlib-2.0-0 libgdk-pixbuf2.0-0 libgeoip1 libicu63 libisc1100 libisc1105 libisccc161
  libisccfg163 libisl19 liblwres161 libmpdec2 libmpx2 libperl5.28 libpython2-stdlib libpython2.7-minimal libpython2.7-stdlib
  libpython3.7-minimal libpython3.7-stdlib libreadline7 netcat-traditional node-ansi-align node-arrify node-bluebird
  node-boxen node-builtin-modules node-call-limit node-camelcase node-cli-boxes node-cliui node-co node-concat-stream
  node-config-chain node-cross-spawn node-cyclist node-decamelize node-decompress-response node-deep-extend node-detect-indent
  node-detect-newline node-duplexer3 node-duplexify node-editor node-end-of-stream node-errno node-execa node-find-up
  node-flush-write-stream node-from2 node-fs-vacuum node-get-caller-file node-get-stream node-got node-has-symbol-support-x
  node-has-to-string-tag-x node-import-lazy node-invert-kv node-is-buffer node-is-builtin-module node-is-npm node-is-object
  node-is-plain-obj node-is-retry-allowed node-is-stream node-isurl node-json-buffer node-kind-of node-latest-version
  node-lazy-property node-lcid node-libnpx node-locate-path node-lowercase-keys node-mem node-merge-stream node-mimic-fn
  node-mimic-response node-minimist node-mississippi node-node-uuid node-npm-run-path node-os-locale node-p-cancelable
  node-p-finally node-p-limit node-p-locate node-p-timeout node-package-json node-parallel-transform node-path-exists
  node-path-is-inside node-prepend-http node-proto-list node-prr node-pump node-pumpify node-qw node-rc
  node-registry-auth-token node-registry-url node-require-directory node-require-main-filename node-semver-diff node-sha
  node-shebang-command node-shebang-regex node-slide node-sorted-object node-stream-each node-stream-iterate node-stream-shift
  node-strip-eof node-strip-json-comments node-term-size node-through2 node-timed-out node-typedarray node-uid-number
  node-unpipe node-url-parse-lax node-url-to-options node-which-module node-widest-line node-wrap-ansi node-xdg-basedir
  node-xtend node-y18n node-yargs node-yargs-parser perl-modules-5.28 python-pkg-resources python2 python2-minimal python2.7
  python2.7-minimal python3.7-minimal
Използвайте „apt autoremove“ за да ги премахнете.
Следните НОВИ пакети ще бъдат инсталирани:
  snoopy
0 актуализирани, 1 нови инсталирани, 0 за премахване и 1 без промяна.
Необходимо е да се изтеглят 46,0 kB архиви.
След тази операция ще бъде използвано 124 kB допълнително дисково пространство.
Изт:1 http://deb.debian.org/debian bullseye/main amd64 snoopy amd64 2.4.12-1 [46,0 kB]
Изтеглени 46,0 kB за 0с (93,2 kB/сек)
Предварително настройване на пакети …


Selecting previously unselected package snoopy.
(Reading database … 56067 files and directories currently installed.)
Preparing to unpack …/snoopy_2.4.12-1_amd64.deb ...
Unpacking snoopy (2.4.12-1) …
Setting up snoopy (2.4.12-1) …
Processing triggers for libc-bin (2.31-13+deb11u5) …

root@linux:/etc# ls -al /var/log/snoopy.log
-rw——- 1 root root 14472 14 мар 13:40 /var/log/snoopy.log

Any specific configuration for snoopy can be tuned through /etc/snoopy.ini

Now you will find all the commands executed by all monitored running processes in /var/log/snoopy.

root@linux:/etc# tail -30 /var/log/snoopy.log
Mar 14 12:59:32 haproxy2 snoopy[713804]: [login:root ssh:(192.168.0.1 62796 192.168.0.210 22) sid:713792 tty:/dev/pts/2 (0/root) uid:root(0)/root(0) cwd:/]: ldconfig
Mar 14 12:59:32 haproxy2 snoopy[713806]: [login:zabbix ssh:((undefined)) sid:682168 tty:(none) ((none)/(none)) uid:zabbix(108)/zabbix(108) cwd:/]: who
Mar 14 12:59:32 haproxy2 snoopy[713807]: [login:zabbix ssh:((undefined)) sid:682168 tty:(none) ((none)/(none)) uid:zabbix(108)/zabbix(108) cwd:/]: wc -l
Mar 14 13:00:07 haproxy2 snoopy[713815]: [login:root ssh:((undefined)) sid:713815 tty:(none) ((none)/(none)) uid:root(0)/root(0) cwd:/usr/lib/sysstat]: /usr/lib/sysstat/sadc -F -L -S DISK 1 1 /var/log/sysstat
Mar 14 13:00:32 haproxy2 snoopy[713823]: [login:zabbix ssh:((undefined)) sid:682168 tty:(none) ((none)/(none)) uid:zabbix(108)/zabbix(108) cwd:/]: who
Mar 14 13:00:32 haproxy2 snoopy[713824]: [login:zabbix ssh:((undefined)) sid:682168 tty:(none) ((none)/(none)) uid:zabbix(108)/zabbix(108) cwd:/]: wc -l
Mar 14 13:01:32 haproxy2 snoopy[713834]: [login:zabbix ssh:((undefined)) sid:682168 tty:(none) ((none)/(none)) uid:zabbix(108)/zabbix(108) cwd:/]: who
Mar 14 13:01:32 haproxy2 snoopy[713835]: [login:zabbix ssh:((undefined)) sid:682168 tty:(none) ((none)/(none)) uid:zabbix(108)/zabbix(108) cwd:/]: wc -l
Mar 14 13:02:32 haproxy2 snoopy[713843]: [login:zabbix ssh:((undefined)) sid:682168 tty:(none) ((none)/(none)) uid:zabbix(108)/zabbix(108) cwd:/]: who
Mar 14 13:02:32 haproxy2 snoopy[713844]: [login:zabbix ssh:((undefined)) sid:682168 tty:(none) ((none)/(none)) uid:zabbix(108)/zabbix(108) cwd:/]: wc -l
Mar 14 13:03:32 haproxy2 snoopy[713855]: [login:zabbix ssh:((undefined)) sid:682168 tty:(none) ((none)/(none)) uid:zabbix(108)/zabbix(108) cwd:/]: who
Mar 14 13:03:32 haproxy2 snoopy[713856]: [login:zabbix ssh:((undefined)) sid:682168 tty:(none) ((none)/(none)) uid:zabbix(108)/zabbix(108) cwd:/]: wc -l
Mar 14 13:04:32 haproxy2 snoopy[713868]: [login:zabbix ssh:((undefined)) sid:682168 tty:(none) ((none)/(none)) uid:zabbix(108)/zabbix(108) cwd:/]: who


3. Set up logrotation (archiving) for snoopy logs

root@linux:/etc# vim /etc/logrotate.d/snoopy    


/var/log/snoopy.log {
        daily
        rotate 30
        compress
        delaycompress
        notifempty
        create 640 root adm

}
 

If you want to test logrotation without actually rotating the file:               

root@linux:/etc# logrotate –debug –force /etc/logrotate.d/snoopy   
  log needs rotating
rotating log /var/log/snoopy.log, log->rotateCount is 30
dateext suffix '-20230314'
glob pattern '-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
previous log /var/log/snoopy.log.1 does not exist
renaming /var/log/snoopy.log.30.gz to /var/log/snoopy.log.31.gz (rotatecount 30, logstart 1, i 30),
…
renaming /var/log/snoopy.log.1.gz to /var/log/snoopy.log.2.gz (rotatecount 30, logstart 1, i 1),
renaming /var/log/snoopy.log.0.gz to /var/log/snoopy.log.1.gz (rotatecount 30, logstart 1, i 0),
log /var/log/snoopy.log.31.gz doesn't exist — won't try to dispose of it
renaming /var/log/snoopy.log to /var/log/snoopy.log.1
creating new /var/log/snoopy.log mode = 0640 uid = 0 gid = 4


4. Monitoring only selected applications  executed commands with snoopy                                                                             

By default snoopy after installed will set itself to monitor all kind of running processes on the system is done by preloading the ldconfig's (libc) ld.so.preload

root@haproxy2:/etc# cat /etc/ld.so.preload
/lib/x86_64-linux-gnu/libsnoopy.so

If you want to monitor a concrete application and not log everything from the running processes in process list, comment this out this line run ldconfig command

Then to any concrete application you would like to monitor with snoopy add to its init script either /etc/init.d/app_init_script or to systemctl's start script before the application binary program run:

export LD_PRELOAD=/lib/snoopy.so


  As per the README states


 Snoopy is placed in /etc/ld.so.preload to trap all occurrences of exec, if 
 you wish to monitor only certain applications you can do so through the    
 LD_PRELOAD environment variable.
Simply set it to /lib/snoopy.so before  loading the application.

For example

 # export LD_PRELOAD=/lib/snoopy.so                                           
 # lynx http://example.com/                           

 

Tags: , , , , , , , , , , , , , , ,
Posted in Linux, Security, System Administration, System Optimization | 1 Comment »

How to solve qmail-inject: fatal: qq temporary problem (#4.3.0) Qmail and Qmail Scanner problems on Debian Linux Wheezy

Monday, October 16th, 2017

fix-qq-error-qmail-inject-qq-temporary-problem-4.3.0-PoweredBy-qmail-Logo

Below QMAIL error
 

qmail-inject: fatal: qq temporary problem (#4.3.0)


occured to me right after upgraded from Debian Linux Squeeze 6 to Debian 7 Wheezy,

qmail-inject: fatal: qq temporary problem (#4.3.0) is really terrible error and I only experienced that error in my Thunderbird during sending mails, mail receiving doesn't work either, so as normally when there are problems with Qmail its a lot of puzzling until you get it.

There is no even trace in logs on what might be causing it, strangely enough nothing in qmail-smtpd, qmail-send logs, the mail server and all components seemed to work perfectly fine I checked whether there are libraries that are missing with a small loop line as follows:
 

 

root@pcfreak:/var/log/qmail/qmail-smtpd# for i in $(ls -1 /var/qmail/bin/*); do ldd $i |grep -i "not found"; done
root@pcfreak:/var/log/qmail/qmail-smtpd# 

 


The absence of result indicates, all binaries are properly linked and no found mmissing libraries.

After investigating closely what might be wrong and reading comments on Thibs QmailRocks Install Qmail-Scanner page, I realied
the error might be caused, because of problems with suid perl, as I already checked my earlier post in which I seemed to have faced the same qmail-inject: fatal: qq temporary problem (#4.3.0) error on Debian Wheezy and explained the possible reasons what might be causing the qq qmail error  here as well

 

and a related issue I experienced earlier with qmail scanner unable to create files in previous article here Suid Perl no longer available as a package and therefore because of the inability of perl to run as root anymore in Debian Wheezy, qmail-scanner-queue.pl script did not work either.
 

root@pcfreak:/downloads/simscan-1.4.0# 320  echo "hi, testing." > /tmp/mailtest.txt
root@pcfreak:/downloads/simscan-1.4.0# env QMAILQUEUE=/var/qmail/bin/qmail-scanner-queue /var/qmail/bin/qmail-inject you@yourdomain.com < /tmp/mailtest.txt
YOU HAVEN'T DISABLED SET-ID SCRIPTS IN THE KERNEL YET!
FIX YOUR KERNEL, PUT A C WRAPPER AROUND THIS SCRIPT, OR USE -u AND UNDUMP!
qmail-inject: fatal: qq permanent problem (#5.3.0)

root@pcfreak:/downloads/simscan-1.4.0# /var/qmail/bin/qmail-scanner-queue
YOU HAVEN'T DISABLED SET-ID SCRIPTS IN THE KERNEL YET!
FIX YOUR KERNEL, PUT A C WRAPPER AROUND THIS SCRIPT, OR USE -u AND UNDUMP!
 

 

A short note to make here is qmail-scanner-queue and qmail-scanner-queue.pl are set with suid bit set as follows:
 

root@pcfreak:/home/hipo/info# ls -al /var/qmail/bin/{qmail-scanner-queue,qmail-scanner-queue.pl}
-rwsr-sr-x 1 qscand qscand   6814 окт 14 17:22 /var/qmail/bin/qmail-scanner-queue*
-rwsr-sr-x 1 qscand qscand 158880 окт 14 23:52 /var/qmail/bin/qmail-scanner-queue.pl*

Good to say here is qmail-scanner-queue is a suid wrapper binary that actually invokes qmail-scanner-queue.pl
 

root@pcfreak:/downloads/simscan-1.4.0# su hipo
hipo@pcfreak:/downloads/simscan-1.4.0$ /var/qmail/bin/qmail-scanner-queue.pl -g
perlscanner: generate new DB file from /var/spool/qscan/quarantine-events.txt
hipo@pcfreak:/downloads/simscan-1.4.0$ exit

root@pcfreak:/downloads/simscan-1.4.0# cp /downloads/qmail-scanner-2.11st/contrib/logrotate.qmail-scanner /etc/logrotate.d/qmail-scanner
root@pcfreak:/downloads/simscan-1.4.0# chmod 644 /etc/logrotate.d/qmail-scanner
root@pcfreak:/downloads/simscan-1.4.0# cd /downloads/qmail-scanner-2.11st/contrib
root@pcfreak:/downloads/qmail-scanner-2.11st/contrib# chmod 755 test_installation.sh
root@pcfreak:/downloads/qmail-scanner-2.11st/contrib# ./test_installation.sh -doit
Sending standard test message – no viruses… 1/4
qmail-inject: fatal: qq temporary problem (#4.3.0)
Bad error. qmail-inject died


This are the other things, I've done to fix possible permission issues

root@pcfreak:/downloads/qmail-scanner-2.11st/contrib#  sudo -u qscand /var/qmail/bin/qmail-scanner-queue.pl -z
root@pcfreak:/downloads/qmail-scanner-2.11st/contrib# chown qscand:qscand /var/spool/qscan/qmail-scanner-queue-version.txt

In /etc/sudoers add following lines:

root@pcfreak:~# vim /etc/sudoers

ALL ALL=(qscand) NOPASSWD: /var/qmail/bin/qmail-scanner-queue.pl
##necroleak ALL=(ALL) ALL
Defaults        env_keep +="TCPREMOTEIP TCPREMOTEHOST TCPREMOTEINFO REMOTE_OS REMOTE_COUNTRY QS_SPAMASSASSIN SA_ONLYDELETE_HOST SA_WHITELIST"


root@pcfreak:/downloads/qmail-scanner-2.11st/contrib# cat /etc/sudoers

# /etc/sudoers
#
# This file MUST be edited with the 'visudo' command as root.
#
# See the man page for details on how to write a sudoers file.
#

Defaults    env_reset

# Host alias specification

# User alias specification

# Cmnd alias specification

# User privilege specification
root    ALL=(ALL) ALL
hipo    ALL=(ALL) ALL
ALL ALL=(qscand) NOPASSWD: /var/qmail/bin/qmail-scanner-queue.pl
##necroleak ALL=(ALL) ALL
Defaults        env_keep +="TCPREMOTEIP TCPREMOTEHOST TCPREMOTEINFO REMOTE_OS REMOTE_COUNTRY QS_SPAMASSASSIN SA_ONLYDELETE_HOST SA_WHITELIST"
# Allow members of group sudo to execute any command
# (Note that later entries override this, so you might need to move
# it further down)
%sudo ALL=(ALL) ALL
#
#includedir /etc/sudoers.d

In case you wonder why we put the line in /etc/sudoers:
 

Defaults        env_keep +="TCPREMOTEIP TCPREMOTEHOST TCPREMOTEINFO REMOTE_OS REMOTE_COUNTRY QS_SPAMASSASSIN SA_ONLYDELETE_HOST SA_WHITELIST"


The reason behind this is that by default sudo resets the environment variables when executing the command. Thus qmail-scanner cannot recognize the important info regarding the incoming mail and treats everything as coming from localhost, which leads to passing everything without scanning. The above line preserves the important ENV variables for qmail-scanner.

In /etc/sudoers add following lines:
 

root@pcfreak:/downloads/qmail-scanner-2.11st/contrib# vim /var/qmail/bin/qmail-scanner-queue.pl


Right after comments or in Line 1  ADD
 

my $real_uid="qscand";

my $effective_uid="qscand";


Also somewhere in the beginning of scripts lets say after above two variable declarations add:
 

$ENV{'PATH'}='/bin:/usr/bin';
$whoami = getpwuid($<) || "unknown";
if($whoami ne "qscand") {
    exec("/usr/bin/sudo -u qscand /var/qmail/bin/qmail-scanner-queue.pl") || die;
}


To prevent your users logged in on physical console and via SSH it is necessery to disable emergency logs for users in syslog / rsyslog, otherwise due to bug, users will logged in will get flooded with messages such as:
 

Message from syslogd@pcfreak at Oct 15 16:43:04 …
 qmail-scanner-queue.pl: qmail-scanner[6834]: Clear:RC:0(95.142.194.223):SA:0(-2.0/2.0): 2.959242 10574 admin@notify.vk.com hipo@www.pc-freak.net Светлана_Георгиева_оставила_Вам_личное_соо� <36b63ec9a0ce7ecc570de2fcbba6ed73@localhost.localdomain> 1508074981.6836-1.pcfreak:6219 1508074981.6836-0.pcfreak:545

 

open /etc/rsyslog.conf and find the line starting with:
 

root@pcfreak:~# vim /etc/rsyslog.conf

*.emerg

add
;user.none

right after it so it reads like:

*.emerg;user.none

Tags: , , , , ,
Posted in Qmail, System Administration | 1 Comment »

How to tune MySQL Server to increase MySQL performance using mysqltuner.pl and Tuning-primer.sh

Tuesday, August 9th, 2011

MySQL Easy performance tuning with mysqltuner.pl and Tuning-primer.sh scripts

Improving MySQL performance is crucial for improving a website responce times, reduce server load and improve overall work efficiency of a mysql database server.

I’ve seen however many Linux System administrators who does belittle or completely miss the significance of tuning a newly installed MySQL server installation.
The reason behind that is probably caused by fact that many people think MySQL config variables, would not significantly improve performance and does not pay back for optimization efforts. Moreover there are a bunch of system admins who has to take care for numerous services so they don’t have time to get good knowledge to optimize MySQL servers.
Thus many admins and webmasters nowdays, think optimizations depend mostly on the side of the website programmers.
It’s also sometimes falsely believed that optimizing a MySQL server could reduce the overall server stability.

With the boom of Internet website building and internet marketing, many webmasters emerged and almost anybody with almost no knowledge on GNU/Linux or minimal or no knowledge on PHP can start his Online store, open a blog or create a website powered by some CMS like joomla.
Thus nowdays many servers even doesn’t have a hired system administrators but are managed by people whose knowledge on *Nix is almost next to zero, this is another reason why dozens of MySQL installations online are a default ones and are not taking a good advantage of the server hardware.

The incrase of website visitors leads people servers expectations for hardware also to grow, thus many companies simply buy a new hardware instead of taking the few time to investigate on how current server hardware can be utilized better.
In that manner of thought I though it will be a good idea to write this small article on Tuning mysql servers with two scripts Tuning-primer.sh and mysqltuner.pl.
The scripts are ultra easy to use and does not require only a minimal knowledge on MySQL, Linux or (*BSD *nix if sql is running on BSD).
Tuning-primer.sh and mysqltuner.pl are therefore suitable for a quick MySQL server optimizations to even people who are no computer experts.

I use this two scripts for MySQL server optimizations on almost every new configured GNU/Linux with a MySQL backend.
Use of the script comes to simply download with wget, lynx, curl or some other web client and execute it on the server host which is already running the MySQL server.

Here is an example of how simple it is to run the scripts to Optimize MySQL:

debian:~# perl mysqltuner.pl
>> MySQLTuner 1.2.0 - Major Hayden >major@mhtx.net<
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

——– General Statistics ————————————————–
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.49-3
[OK] Operating on 64-bit architecture

——– Storage Engine Statistics ——————————————-
[–] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 6G (Tables: 952)
[!!] InnoDB is enabled but isn’t being used
[!!] Total fragmented tables: 12

——– Security Recommendations ——————————————-
[OK] All database users have passwords assigned

——– Performance Metrics ————————————————-
[–] Up for: 1d 2h 3m 35s (68M q [732.193 qps], 610K conn, TX: 49B, RX: 11B)
[–] Reads / Writes: 76% / 24%
[–] Total buffers: 512.0M global + 2.8M per thread (2000 max threads)
[OK] Maximum possible memory usage: 6.0G (25% of installed RAM)
[OK] Slow queries: 0% (3K/68M)
[OK] Highest usage of available connections: 7% (159/2000)
[OK] Key buffer size / total MyISAM indexes: 230.0M/1.7G
[OK] Key buffer hit rate: 97.8% (11B cached / 257M reads)
[OK] Query cache efficiency: 76.6% (46M cached / 61M selects)
[!!] Query cache prunes per day: 1822075
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 2M sorts)
[!!] Joins performed without indexes: 63635
[OK] Temporary tables created on disk: 1% (26K on disk / 2M total)
[OK] Thread cache hit rate: 99% (159 created / 610K connections)
[!!] Table cache hit rate: 4% (1K open / 43K opened)
[OK] Open file limit used: 17% (2K/16K)
[OK] Table locks acquired immediately: 99% (36M immediate / 36M locks)

——– Recommendations —————————————————–
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Increasing the query_cache size over 128M may reduce performance
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 256M) [see warning above] join_buffer_size (> 256.0K, or always use indexes with joins) table_cache (> 7200)

You see there are plenty of things, the script reports, for the unexperienced most of the information can be happily skipped without need to know the cryptic output, the section of importance here is Recommendations for some clarity, I’ve made this section to show up bold.

The most imporant things from the Recommendations script output is actually the lines who give suggestions for incrase of certain variables for MySQL.In this example case this are the last three variables:
query_cache_size,
join_buffer_size and
table_cache

All of these variables are tuned from /etc/mysql/my.cnf (on Debian) and derivatives distros and from /etc/my.cnf on RHEL, CentOS, Fedora and the other RPM based Linux distributions.

On some custom server installs my.cnf is also located in /usr/local/mysql/etc/ or some other a bit more unstandard location 😉

Anyways now having the Recommendation from the script, it’s necessery to edit my.cnf and try to double the values for the suggested variables.

First, I check if all the suggested variables are existent in my config with grep , if they’re not then I’ll simply add the variable with doubled size of the suggested values.
P.S: One note here is sometimes some values which are configured, are the default value for the MySQL server and does not have a record in my.cnf

debian:~# grep -E 'query_cache_size|join_buffer_size|table_cache' /etc/mysql/my.cnf table_cache = 7200
query_cache_size = 256M
join_buffer_size = 262144
All of my variables are in the config so, now edit my.cnf and set values to:
table_cache = 14400
query_cache_size = 512M
join_buffer_size = 524288

I always, however preserve the old variable’s value, because sometimes raising the value might create problem and the MySql server might be unable to restart properly.
Thus before going with adding the new values make sure the old ones are commented with # , e.g.:
#table_cache = 7200
#query_cache_size = 256M
#join_buffer_size = 262144

I would recommend vim as editor of choice while editing my.cnf as vim completely rox 😉 If you’re not acquainted to vim use nano or mcedit or your editor of choice 😉 :

debian:~# vim /etc/mysql/my.cnf
...

Assuming that the changes are made, it’s time to restart MySQL to make sure the new values are read by the SQL server.

debian:~# /etc/init.d/mysql restart
* Stopping MySQL database server mysqld [ OK ]
* Starting MySQL database server mysqld [ OK ]
Checking for tables which need an upgrade, are corrupt or were not closed cleanly.

If mysql server fails, however to restart, make sure immediately you reverse back the changed variables to the commented values and restart once again via mysql init script to make server load.

Afterwards start adding the values one by one until find out which one is causing the mysqld to fail.

Now the second script (Tuning-primer.sh) is also really nice for MySQL performance optimizations are necessery. However it’s less portable (as it’s written in bash scripting language).
Consider running this script among different GNU/Linux distributious (especially the newer ones) might produce errors.
Tuning-primer.sh requires some minor code changes to be able to run on FreeBSD, NetBSD and OpenBSD *nices.

The way Tuning-primer.sh works is precisely like mysqltuner.pl , one runs it it gives some info about current running MySQL server and based on certain factors gives suggestions on how increasing or decresing certain my.cnf variables could reduce sql query bottlenecks, solve table locking issues as well as generally improve INSERT, UPDATE query times.

Here is an example output from tuning-primer.sh run on another server:

server:~# wget https://www.pc-freak.net/files/Tuning-primer.sh
...
server:~# sh Tuning-primer.sh
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

MySQL Version 5.0.51a-24+lenny5 x86_64

Uptime = 8 days 10 hrs 19 min 8 sec
Avg. qps = 179
Total Questions = 130851322
Threads Connected = 1

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

SLOW QUERIES
Current long_query_time = 1 sec.
You have 16498 out of 130851322 that take longer than 1 sec. to complete
The slow query log is NOT enabled.
Your long_query_time seems to be fine

MAX CONNECTIONS
Current max_connections = 2000
Current threads_connected = 1
Historic max_used_connections = 85
The number of used connections is 4% of the configured maximum.
Your max_connections variable seems to be fine.

WORKER THREADS
Current thread_cache_size = 128
Current threads_cached = 84
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MEMORY USAGE
Tuning-primer.sh: line 994: let: expression expected
Max Memory Ever Allocated : 741 M
Configured Max Memory Limit : 5049 M
Total System Memory : 23640 M

KEY BUFFER
Current MyISAM index space = 1646 M
Current key_buffer_size = 476 M
Key cache miss rate is 1 / 56
Key buffer fill ratio = 90.00 %
You could increase key_buffer_size
It is safe to raise this up to 1/4 of total system memory;
assuming this is a dedicated database server.

QUERY CACHE
Query cache is enabled
Current query_cache_size = 64 M
Current query_cache_used = 38 M
Current Query cache fill ratio = 59.90 %

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current record/read_rnd_buffer_size = 256.00 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 128.00 K
You have had 111560 queries where a join could not use an index properly
You have had 91 joins without keys that check for key usage after each row
You should enable “log-queries-not-using-indexes”
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

TABLE CACHE
Current table_cache value = 3600 tables
You have a total of 798 tables
You have 1904 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current tmp_table_size = 128 M
1% of tmp tables created were disk based
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 128.00 K
Current table scan ratio = 797 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 1782
You may benefit from selective use of InnoDB.

As seen from script output, there are certain variables which might be increased a bit for better SQL performance, one such variable as suggested is key_buffer_size– (You could increase key_buffer_size)

Now the steps to make the tunings to my.cnf are precisely the same as with mysqltuner.pl, e.g.:
1. Preserve old config variables which will be changed by commenting them
2. Double value of current variables in my.cnf suggested by script
3. Restart Mysql server via /etc/init.d/mysql restart cmd.
4. If mysql runs fine monitor mysql performance with mtop or mytop for at least 15 mins / half an hour.

if all is fine run once again the tuning scripts to see if there are no further improvement suggestions, if there are more follow the 4 steps described procedure once again.

It’s also a good idea that these scripts are periodically re-run on the server like once per few months as changes in SQL queries amounts and types will require changes in MySQL operational variables.
The authors of these nice scripts has done great job and have saved us a tons of nerves time, downtimes and money spend on meaningless hardware. So big thanks for the awesome scripts guys 😉
Finally after hopefully succesful deployment of changes, enjoy the incresed SQL server performance 😉

Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,
Posted in FreeBSD, Linux, MySQL, System Administration, Web and CMS | 2 Comments »

How to Delete Windows XP temporary files from command line / Batch script to Delete Windows temp files on every system restart

Thursday, August 23rd, 2012

In case you need to DELete Windows temporary files directory to save some free space on an old PC or (group of PCs) in a raw you might prefer to use this CLI command lines:

DEL /F /S /Q %TEMP%
DEL /f /q /s "%SYSTEMDRIVE%\Documents and Settings\LocalService\Cookies\*.*"
DEL /f /q /s "%SYSTEMDRIVE%%HOMEPATH%\Cookies\*.*"
DEL /f /q /s "%SYSTEMDRIVE%\Documents and Settings\LocalService\Local Settings\Temp\*.*"
DEL /f /q /s "%SYSTEMDRIVE%\Documents and Settings\NetworkService\Local Settings\Temp\*.*"
DEL /f /q /s "%SYSTEMDRIVE%\Documents and Settings\Default User\Local Settings\Temp*.*"
DEL /f /q /s "%SYSTEMDRIVE%%HOMEPATH%\Local Settings\Temp\*.*"
DEL /f /q /s "%WINDIR%\Temp\*.*"
DEL /f /q /s "%TEMP%\*.*"
DEL /f /q /s "%SYSTEMDRIVE%\Documents and Settings\LocalService\Local Settings\Temporary Internet Files\*.*"
DEL /f /q /s "%SYSTEMDRIVE%%HOMEPATH%\Local Settings\Temporary Internet Files\*.*"
RD /q /s %TEMP%
RD /q /s %WINDIR%\Temp
RD /q /s "%SYSTEMDRIVE%\Documents and Settings\Default User\Local Settings\Temp"
RD /q /s "%SYSTEMDRIVE%\Documents and Settings\LocalService\Local Settings\Temp"
RD /q /s "%SYSTEMDRIVE%\Documents and Settings\Default User\Local Settings\Temp"
RD /q /s "%SYSTEMDRIVE%%HOMEPATH%\Local Settings\Temp"

Another helpful thing for MS Windows users is cleaning up Windows Tempory Files on every system restart (reboot); doing so is possible by setting below’s short batch script to exec on every system boot:

@ECHO OFF
IF NOT %temp% == %tmp% GOTO both_
GOTO single
:both
DEL %temp%\*.* /F /S /Q
DEL %tmp%\*.* /F /S /Q
CLS
ECHO Deleted all files in the TEMP folder: %temp%
ECHO Deleted all files in the TMP folder: %tmp%
GOTO end
:single
DEL %temp%\*.* /F /S /Q
CLS
ECHO Deleted all files in the TEMP folder: %temp%
:end

You can download the script clean_windows_temp_files_on_win_start.com here . The script is great tool for Windows administrators of Win Domain Controllers or University / educational M$ Windows based networks, where PC security is at high risk. Setting the script to run on even “non-critical” home PCs is a great idea as it can save you a lot of troubles with SpyWare, Malware Viruses and other Windows targetted “Bad-Wares” 🙂

Cheers 🙂

Tags: , , , , ,
Posted in Windows | No Comments »