Category Archives: Linux - Page 4

Mysql How to delete all triggers

Posted by on October 29, 2012 5 comments

I`v got an issue from our development team to dump all triggers from prod db and add them to development db.
It`s trivial, except all triggers deletion. Everyone use magic, procedures, etc.

How to dump only triggers:

/usr/bin/mysqldump --single-transaction  --triggers --routines --no-data  --no-create-db --no-create-info
-u backup -pMEGASECUREPASSWORD
-h dbslave1-1.randomthemes.com database_name > /home/backup/mysql/database_name_triggers.sql

How to delete all triggers:

Fastest way (a little bit hindy 🙂 🙂 but working o.k.): Upload trigger names to file, apply regexp, delete

$mysql -e "SELECT trigger_schema, trigger_name from information_schema.triggers" > ./drop_all_triggers.sql

regexps (use vi, Luke):

:%s/\n/;\r/g
:%s/database_name/DROP TRIGGER IF EXISTS database_name/g
:%s/\t/./g
$mysql mydatabase < ./drop_all_triggers.sql

How to check if zabbix agent insstalled at all infrastructure hosts

Posted by on October 2, 2012 No comments

We use LXC containers at PH hosts.
containers named like:
rolename$RoleVersion-$InstanceNumber

For example
at host sXX2
appserver1-1
db1-1
db1-2
zabbix3-1

We need to check, if zabbix agents are installed at all infrastructure. One way – user fabric, Luke, as all 80 level DevOps do, or use ssh as all 40 level DevOps do.
Sorry for a little bit 🙂 “hindi” code 🙂 no review was done 🙂
Read more »

How to install unassigned packages ubuntu/debian

Posted by on October 1, 2012 No comments

We start chef deployment…
Chef deserve additional Huge article, but let`s start:

  echo "deb http://apt.opscode.com/ `lsb_release -cs`-0.10 main" | sudo tee /etc/apt/sources.list.d/opscode.list
  aptitude update
  aptitude -o Aptitude::CmdLine::Ignore-Trust-Violations=true install opscode-keyring
  aptitude -y -o Aptitude::CmdLine::Ignore-Trust-Violations=true install chef

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

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