Posts Tagged ‘bash’

MySQL, Percona, MariaDB long running processes clean up one liner

April 30, 2015 Leave a comment

There are tools like pt-kill from the percona tool kit that may print/kill the long running transactions at MariaDB, MySQL or at Percona data instances, but a lot of backup scripts are just some simple bash lines.
So checking for long running transactions before the backup to be executed seems to be a step that is missed a lot.

Here is one line that might be just added in every bash script before the backup to be executed
Variant 1. Just log all the processlist entries and calculate which ones were running longer than TIMELIMIT:

$ export TIMELIMIT=70 && echo "$(date) : check for long runnig queries start:" >> /tmp/ && mysql -BN -e 'show processlist;' | tee -a /tmp/ | awk -vlongtime=${TIMELIMIT} '($6>longtime){print "kill "$1";"}' | tee -a /tmp/

Variant 2: Log all the processlist, calculate the calculate which processes are running longer than TIMELIMIT, and kill them before to execute the backup:

$ export TIMELIMIT=70 && echo "$(date) : check for long runnig queries start:" >> /tmp/ && mysql -BN -e 'show processlist;' | tee -a /tmp/ | awk -vlongtime=${TIMELIMIT} '($6>longtime){print "kill "$1";"}' | tee -a /tmp/ | mysql >> /tmp/ 2>&1


Update bash at Slackware 11 with the latest patch against shellshock bugs CVE-2014-6271 CVE-2014-7169 CVE-2014-7186 CVE-2014-7187

October 1, 2014 1 comment

I still have Slackware 11 machines … so I had to recompile the bash

I took a moment to read this nice blog related to shellshock:
So in short what I did at my Slackware 11 machines:

root@DL-380:[Wed Oct 01 23:05:47]:[~]$ cat /etc/slackware-version 
Slackware 11.0.0
root@DL-380:[Wed Oct 01 23:05:47]:[/opt/installs]$ bash --version
GNU bash, version 3.1.17(2)-release (i486-slackware-linux-gnu)
Copyright (C) 2005 Free Software Foundation, Inc.

root@DL-380:[Wed Oct 01 23:06:02]:[/opt/installs]$ wget
root@DL-380:[Wed Oct 01 23:06:35]:[/opt/installs]$ lftp
cd: received redirection to `'
cd ok, cwd=/gnu/bash                               
lftp> mirror bash-3.1-patches 
Total: 1 directory, 44 files, 0 symlinks                  
New: 44 files, 0 symlinks
60375 bytes transferred in 5 seconds (11.3K/s)
lftp> exit
root@DL-380:[Wed Oct 01 23:07:39]:[/opt/installs]$ rm bash-3.1-patches/*sig
root@DL-380:[Wed Oct 01 23:07:53]:[/opt/installs]$ tar xvf bash-3.1.tar.gz 
root@DL-380:[Wed Oct 01 23:08:54]:[/opt/installs]$ cd bash-3.1
root@DL-380:[Wed Oct 01 23:08:59]:[/opt/installs/bash-3.1]$ 
root@DL-380:[Wed Oct 01 23:08:59]:[/opt/installs/bash-3.1]$ for patch_file in `find /opt/installs/bash-3.1-patches/ -type f `;  do echo $patch_file && patch -p0 < $patch_file ; done
root@DL-380:[Wed Oct 01 23:09:23]:[/opt/installs/bash-3.1]$ tail patchlevel.h
#if !defined (_PATCHLEVEL_H_)
#define _PATCHLEVEL_H_

/* It's important that there be no other strings in this file that match the
   regexp `^#define[     ]*PATCHLEVEL', since that's what support/
   looks for to find the patch level (for the sccs version string). */

#define PATCHLEVEL 21

#endif /* _PATCHLEVEL_H_ */
root@DL-380:[Wed Oct 01 23:09:44]:[/opt/installs/bash-3.1]$ ./configure 
root@DL-380:[Wed Oct 01 23:10:49]:[/opt/installs/bash-3.1]$ make --j3 
ls -l bash
-rwxr-xr-x 1 root root 1556950 2014-10-01 23:11 bash
size bash
   text       data        bss        dec        hex    filename
 634120      22840      19432     676392      a5228    bash
root@DL-380:[Wed Oct 01 23:11:19]:[/opt/installs/bash-3.1]$ 
root@DL-380:[Wed Oct 01 23:11:19]:[/opt/installs/bash-3.1]$ env x='() { :;}; echo vulnerable' bash -c "echo this is a test" 
this is a test
root@DL-380:[Wed Oct 01 23:12:36]:[/opt/installs/bash-3.1]$ env x='() { :;}; echo vulnerable' ./bash -c "echo this is a test" 
this is a test
root@DL-380:[Wed Oct 01 23:12:41]:[/opt/installs/bash-3.1]$ ./bash --version
GNU bash, version 3.1.21(2)-release (i686-pc-linux-gnu)
Copyright (C) 2005 Free Software Foundation, Inc.
root@DL-380:[Wed Oct 01 23:12:46]:[/opt/installs/bash-3.1]$ which bash
root@DL-380:[Wed Oct 01 23:13:11]:[/opt/installs/bash-3.1]$ file /usr/bin/bash
/usr/bin/bash: symbolic link to `/bin/bash'
root@DL-380:[Wed Oct 01 23:13:15]:[/opt/installs/bash-3.1]$ file /bin/bash
/bin/bash: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), dynamically linked (uses shared libs), stripped
root@DL-380:[Wed Oct 01 23:13:18]:[/opt/installs/bash-3.1]$ cp -fp bash /bin/bash
root@DL-380:[Wed Oct 01 23:13:28]:[/opt/installs/bash-3.1]$ env x='() { :;}; echo vulnerable' bash -c "echo this is a test" 
this is a test
root@DL-380:[Wed Oct 01 23:13:43]:[/opt/installs/bash-3.1]$ (for x in {1..200} ; do echo "for x$x in ; do :"; done; for x in {1..200} ; do echo done ; done) | bash || echo "CVE-2014-7187 vulnerable, word_lineno" 
root@DL-380:[Wed Oct 01 23:13:53]:[/opt/installs/bash-3.1]$ bash -c 'true <<EOF <<EOF <<EOF <<EOF <<EOF <<EOF <<EOF <<EOF <<EOF <<EOF <<EOF <<EOF <<EOF <<EOF' || echo "CVE-2014-7186 vulnerable, redir_stack" 
root@DL-380:[Wed Oct 01 23:14:02]:[/opt/installs/bash-3.1]$ 


Slackware4LIfe 🙂

oneliner how to clear store of particular old MO/DLR messages of Kannel bearerbox version `svn-r5011M’.

February 10, 2013 Leave a comment

We assume the kannel stora is spooled at /var/log/kannel/kannel.spool/
How to clean the old non sent/routed MO messages from 1st to 9th Feb 2013

root@darkstar:[Sun Feb 10 17:28:56]:[~/bin]$ curl -s "" | awk '$2~/\[MO\]/{print $0};$3~/\[2013-02-0/{ gsub(/[\[\]]/,"",$1);print $1}'  | xargs --no-run-if-empty -icrap find /var/log/kannel/kannel.spool/ -type f -name crap -exec rm -f {} \;

serice kannel restart

Categories: bash, CentOS, Kannel, Linux, Slackware Tags: , , , , ,

Migrating MySQL 5.5.25a jiradb ERROR 2013 (HY000) on huge single db import

January 18, 2013 Leave a comment

well, I incremented max_allowed_packet from 16M to 512M
anyway, I got the same error on the next clean import.
so decided to find a workaround.
so, how to get the data separated from the ddl statements:

# get the tables names into the insert statement, its better to have that in file for future usage
sed '/^INSERT INTO/!d;s/ VALUES.*$//' jiradb.20130118.sql | sort | uniq > tablas.como.nombres.txt

then how to get the data separated:

root@jiragg:[Fri Jan 18 15:26:33]:[/usr/local/BACKUP]$ cat
# trim function thank to
# and
trim() {
    local var=$1
    var="${var#"${var%%[![:space:]]*}"}"   # remove leading whitespace characters
    var="${var%"${var##*[![:space:]]}"}"   # remove trailing whitespace characters
    echo -n "$var"

while read tabname
        tablename=$(trim $(echo $tabname | sed 's/INSERT INTO//;s/[[:punct:]]*//g'))
        echo "${tabname}:=>${tablename}"
        sed "/^INSERT INTO \`${tablename}\` VALUES/!d" /usr/local/BACKUP/jiradb.20130118.sql | gzip > "/usr/local/BACKUP/${tablename}.jiradb.20130118.sql.gz"
done < tablas.como.nombres.txt

how to get the DDLs:

sed '/^INSERT INTO/d' jiradb.20130118.sql > non.insert.jiradb.20130118.sql

how to import the whole jiradb:

# create the empty database
mysql --defaults-file=~/..credentials.jira -e 'create database jiradb;'
#import the DDLs:
mysql --defaults-file=~/..credentials.jira jiradb < /usr/local/BACKUP/non.insert.jiradb.20130118.sql
# make a list of the complressed datafiles per table:
find /usr/local/BACKUP/ -type f -name "*sql.gz"  > list.import.tables.txt
# execute simple export script 
root@jiragg:[Fri Jan 18 15:35:05]:[/usr/local/BACKUP]$ cat
while read tablefile
	echo -n "importing ${tablefile}: "
	zcat "${tablefile}" |  mysql --defaults-file=~/..credentials.jira jiradb
	echo ""
done < list.import.tables.txt

and that is it
anyway do not forget to increment the max_allowed_packet

Slackware4Life !

Categories: bash, CentOS, Linux, MySQL, RHEL Tags: , , ,

Bash oneliner HOWTO to remove all the logs but from the last three days in case you face the “Argument list too long” issue

January 17, 2013 2 comments

# we assume the target directory is /var/spool/bsms/main/errors/
cd /var/spool/bsms/main/errors/; ls -lrt  | egrep ".sql$" | egrep -v "$(date +"%Y-%m-%d")|$(date -d '1 day ago' +"%Y-%m-%d")|$(date -d '2 day ago' +"%Y-%m-%d")" | awk '{print$8}' | xargs --no-run-if-empty -icrap rm -f crap

Slackware4Life 😛

Install MySQL at non default data directory on different drive on CentOS6.3/RHEL 6

January 16, 2013 4 comments

We assume the new disk is /dev/sdb1 formatted as ext3
and it will be mounted as /data

# 0. make sure there is no mysqlm mysql data directory :
yum remove mysql mysql-server -y 
test -d /data/mysql/ && rm -rf /data/mysql/
test -d /var/lib/mysql/ && rm -rf /var/lib/mysql/

# 1. install Mysql
yum install mysql mysql-server -y

# 2. check the mysql  status
service mysqld status

# 3. start the mysqld if not started
service mysqld start

# 4. check the mysql status again
service mysqld status

# 5. stop mysqld in case its started, and check thre is n mysql process:
service mysqld stop
ps axu | grep mysql

# 6. make sure the /data partition is added to the /etc/fstab. If not add it:
test  `cat  /etc/fstab | grep /data | wc -l ` -eq 0  && echo "/dev/sdb1 /data ext3  defaults 1 1" >>  /etc/fstab

# 7. make sure the /data partition is mounted, 
test  `cat /proc/mounts  | grep /data | grep -v grep | wc -l` -eq 0 && mount /data

# 8. disable selinx
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config

# 9. move the mysql data directory to /data partition and create the symbolic link
test -d /var/lib/mysql/ && mv /var/lib/mysql/ /data/ && ln -s /data/mysql /var/lib/
ls -lrth /var/lib/ | grep mysql 

# 10 .make sure the mysqld is set to start on boot time 
chkconfig mysqld on 

# 11. reboot the server in a view to test everything 

# 12. try to use mysql after the reboot:
mysql -NB -e 'select version()'

# 13. set the MySQL root password.

# 14. check the init script :
service mysqld  restart

Slackware4life 🙂

Categories: bash, CentOS, Linux, MySQL, RHEL Tags: , , ,

PostgreSQL backup and compress schemas only of a db server

May 12, 2012 Leave a comment
root@darkwater:[Sun May 13 00:27:32]:[/home/db.schema.backup]$ cat /home/db.schema.backup/bin/

cd /home/db.schema.backup/

for db in `echo "\l" |  /opt/pgsql/bin/psql -U postgres  | grep -v template | sed '4,$!d' | awk '{print $1}' | grep -v "(" | sed 's/^$//'`
  echo  "$(date): ${db} schema dump START" | tee -a | gzip -9 -c  >> ${db}.schema.only.sql.gz
  /opt/pgsql/bin/pg_dump -s -U postgres ${db} | /usr/bin/sed '/^$/d' | gzip -9 -c  >> ${db}.schema.only.sql.gz
  echo  "$(date): ${db} schema dump END" | tee -a | gzip -9 -c >>  ${db}.schema.only.sql.gz

cd "${here}"