How I do mysql table aggregation.

Posted by on September 5, 2012 No comments

We have one huge old project with intensive mysql usage.
Due to some architectural errors, (sometimes development people do very strange things…) application continuously writes to several tables, partitioned by date. And they didn`t think about aggregation at all… Database growth very fast + 30 Gb per day. And it becomes real problem when we migrate data to SSD raid 10. (BTW it gives us 50 times performance boost!)
Our Application and reporting service doesn`t need such huge amount of data. Development of this project currently stalled, so only Operation team can do modifications.
I create archive database at slow SAS raid, and create aggregation sql. Main problem was – unknown partition number for data. There are 2 ways how to get it.

Use EXPLAIN PARTITIONS, and parse output, or just calculate number 🙂

How table was partitioned:

PARTITION BY HASH (TO_DAYS(`create_date`)) PARTITIONS 10

For Mysql 5.5
Read more »

Huge ammount 400 at nginx access.log

Posted by on August 24, 2012 2 comments

I observe alot of 400 at nginx access.log.
After investigation found, that modern browsers open 2+ simultanious connections, but some of them frequently not used for data transfer, so nginx close them by timeout with 400 code at access.log

hereis my session from chrome:

root@ads1-1:~# tail -f /var/log/nginx/access.log | grep 77.232.X.X
77.232.X.X – – [24/Aug/2012:22:40:36 +0400] “-” 400 0 “-” “-”
77.232.X.X – – [24/Aug/2012:22:40:37 +0400] “GET /adnet/css/adpreview/20120621.css HTTP/1.1” 200 7644 “http://smi2.ru/data/pop/gen.inner.php?bl=32380” “Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.1 (KHTML, like Gecko) Chrome/21.0.1180.79 Safari/537.1”

So, don`t panic if your found a huge ammount of 400 at access.log

Tomcat issues

Posted by on August 10, 2012 No comments

How to disable ipv6 at tomcat
-Djava.net.preferIPv4Stack=true

How to create ramdrive linux

Posted by on July 30, 2012 No comments

Sometimes OPS need RAM drives.

I use one for nginx cache at my CDN servers

mount -t tmpfs -o size=sizeM tmpfs /mnt/ramdrive/

Add required lines to PH host fstab and to LXC fstab

tmpfs /mnt/ramdrive tmpfs size=2000M,mode=0777

How to resize ram drive:

mount -o remount,size=new-sizeM /mnt/ramdrive/

phpmyadmin #1045 Cannot log in to the MySQL server

Posted by on July 17, 2012 No comments

Our developers really crazy about phpmyadmin 🙂 and ask me to setup one.
I really prefer console…
Setup in ubuntu is easy just use aptitude, Luke…
Only change I did is to add Basic Auth to phpmyadmin url, to prevent ugly internet bots from trying to bruteforce my databases.

htpasswd /etc/phpmyadmin/htpasswd.setup developers

But when I try to login to my DB interface I`v got this damned error.
#1045 Cannot log in to the MySQL server
Very very strange! I can login via console with this username…
I create user with following credentials:

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,CREATE ROUTINE,
       CREATE TEMPORARY TABLES,CREATE VIEW,EXECUTE,INDEX,SHOW VIEW, ALTER ROUTINE
       on *.* to 'developer'@'%' IDENTIFIED by 'MEGASECUREPASSWORD';

phpmyadmin refuse this user. I turn on mysql debugging.
Read more »

How to mount disk to LXC container

Posted by on July 16, 2012 No comments

It`s rather simple.
just mount disk via fstab to host system:

root@sfa2 ~ # cat /etc/fstab | grep sdc1
/dev/sdc1       /mnt/sdc1   ext4     noatime,nodiratime      0 0

and add mount with bind to your LXC fstab, usually located at

root@sfa2 ~ # cat /var/lib/lxc/dbslave1-1/config | grep fstab
lxc.mount
.entry
= /var/lib/lxc/dbslave1-1/fstab

Now restart container and enjoy.

root@sfa2 ~ # cat /var/lib/lxc/dbslave1-1/fstab
proc            proc         proc    nodev,noexec,nosuid 0 0
sysfs           sys          sysfs defaults  0 0
/mnt/sdc1       /var/lib/lxc/dbslave1-1/rootfs/sdc1  none bind     0 0

BTW: Your cannot see remained space via df -h. I don`t know how to solve this issue 🙁 🙁 🙁

My mysq server databases backup script with Percona

Posted by on July 10, 2012 No comments

Simple, but working o.k.

#!/bin/bash
BDIR="/home/backup/mysql"
#DATAPATH=`date +%m%d%Y%H%M%S`

# Run backup
/usr/bin/innobackupex --user=root --password=MEGASECUREPASSWORD --slave-info $BDIR

LASTBACKUP=`ls -1 -t  $BDIR | head -n 1`

/usr/bin/innobackupex --user=root --password=MEGASECUREPASSWORD  --apply-log $BDIR/$LASTBACKUP

How to Install Percona XtraBackup and innobackupex Ubuntu 12

Posted by on July 9, 2012 No comments

Percona has it`s own repo.
What is XtraBackup – damn, it`s very powerful backup tool for mysql from percona.
http://www.percona.com/doc/percona-xtrabackup/index.html
innobackupex – is a part of percona-xtrabackup package

  apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
  echo "deb http://repo.percona.com/apt precise main" >> /etc/apt/sources.list
  cat /etc/apt/sources.list
  vi /etc/apt/sources.list
  aptitude update
  aptitude install percona-xtrabackup

How to reset qmailadmin password. And do bulk user deletion.

Posted by on July 5, 2012 No comments

Today nobody can revise password to our corporate mail server Control Pannel. Powered by qmail. With qmailadmin as WEB frontend.

How to change password to postmaster@randomthemes.com
/usr/local/vpopmail/bin/vpasswd postmaster@randomthemes.com

And other issue:
Where to write rcpthosts – /usr/local/qmail/control/rcpthosts

How to make a bulk user deletion:
issue was to delete 80 000 users 🙂

mysql> select pw_name from vpopmail_randomthemes.vpopmail where pw_domain='randomthemes.com';
add users to file vpusers.txt and delete using vdeluser
# for i in `cat vpusers.txt | awk {'print $1 "@randomthemes.com"'}`; do /usr/local/vpopmail/bin/vdeluser $i; done;

How can I run dnsmasq DHCP/TFTP-only.

Posted by on July 2, 2012 No comments

I mean disable DNS caching feature.
dnsmasq required by lxc to configure network, but I prefer to use bind, to provide DHCP resolver.
So use force, Luke! I mean read man.

-p, --port=<port>

Listen on <port> instead of the standard DNS port (53). Setting this to zero completely disables DNS function, leaving only DHCP and/or TFTP.

vi /etc/dnsmasq.d/lxc
port=0