Here is a little script that will show you what processes are using your swap

http://www.nicktailor.com/files/swapusage

vi swapusage && chmod +x swapusage

copy paste below & save

./swapusage (to run)

#!/bin/bash

# find-out-what-is-using-your-swap.sh
# — Get current swap usage for all running processes output
# — rev.0.1, 2011-05-27, Erik Ljungstrom – initial version
SCRIPT_NAME=`basename $0`;
SORT=”kb”; # {pid|kB|name} as first parameter, [default: kb]
[ “$1” != “” ] && { SORT=”$1″; }

[ ! -x `which mktemp` ] && { echo “ERROR: mktemp is not available!”; exit; }
MKTEMP=`which mktemp`;
TMP=`${MKTEMP} -d`;
[ ! -d “${TMP}” ] && { echo “ERROR: unable to create temp dir!”; exit; }

>${TMP}/${SCRIPT_NAME}.pid;
>${TMP}/${SCRIPT_NAME}.kb;
>${TMP}/${SCRIPT_NAME}.name;

SUM=0;
OVERALL=0;
echo “${OVERALL}” > ${TMP}/${SCRIPT_NAME}.overal;

for DIR in `find /proc/ -maxdepth 1 -type d -regex “^/proc/[0-9]+”`;
do
PID=`echo $DIR | cut -d / -f 3`
PROGNAME=`ps -p $PID -o comm –no-headers`

for SWAP in `grep Swap $DIR/smaps 2>/dev/null| awk ‘{ print $2 }’`
do
let SUM=$SUM+$SWAP
done

if (( $SUM > 0 ));
then
echo -n “.”;
echo -e “${PID}\t${SUM}\t${PROGNAME}” >> ${TMP}/${SCRIPT_NAME}.pid;
echo -e “${SUM}\t${PID}\t${PROGNAME}” >> ${TMP}/${SCRIPT_NAME}.kb;
echo -e “${PROGNAME}\t${SUM}\t${PID}” >> ${TMP}/${SCRIPT_NAME}.name;
fi
let OVERALL=$OVERALL+$SUM
SUM=0
done
echo “${OVERALL}” > ${TMP}/${SCRIPT_NAME}.overal;
echo;
echo “Overall swap used: ${OVERALL} kB”;
echo “========================================”;
case “${SORT}” in
name )
echo -e “name\tkB\tpid”;
echo “========================================”;
cat ${TMP}/${SCRIPT_NAME}.name|sort -r;
;;

kb )
echo -e “kB\tpid\tname”;
echo “========================================”;
cat ${TMP}/${SCRIPT_NAME}.kb|sort -rh;
;;

pid | * )
echo -e “pid\tkB\tname”;
echo “========================================”;
cat ${TMP}/${SCRIPT_NAME}.pid|sort -rh;
;;
esac
rm -fR “${TMP}/”;

#!/bin/bash

# find-out-what-is-using-your-swap.sh
# — Get current swap usage for all running processes
# —
# — rev.0.3, 2012-09-03, Jan Smid – alignment and intendation, sorting
# — rev.0.2, 2012-08-09, Mikko Rantalainen – pipe the output to “sort -nk3″ to get sorted output
# — rev.0.1, 2011-05-27, Erik Ljungstrom – initial version
SCRIPT_NAME=`basename $0`;
SORT=”kb”; # {pid|kB|name} as first parameter, [default: kb]
[ “$1” != “” ] && { SORT=”$1″; }

[ ! -x `which mktemp` ] && { echo “ERROR: mktemp is not available!”; exit; }
MKTEMP=`which mktemp`;
TMP=`${MKTEMP} -d`;
[ ! -d “${TMP}” ] && { echo “ERROR: unable to create temp dir!”; exit; }

>${TMP}/${SCRIPT_NAME}.pid;
>${TMP}/${SCRIPT_NAME}.kb;
>${TMP}/${SCRIPT_NAME}.name;

SUM=0;
OVERALL=0;
echo “${OVERALL}” > ${TMP}/${SCRIPT_NAME}.overal;

for DIR in `find /proc/ -maxdepth 1 -type d -regex “^/proc/[0-9]+”`;
do
PID=`echo $DIR | cut -d / -f 3`
PROGNAME=`ps -p $PID -o comm –no-headers`

for SWAP in `grep Swap $DIR/smaps 2>/dev/null| awk ‘{ print $2 }’`
do
let SUM=$SUM+$SWAP
done

if (( $SUM > 0 ));
then
echo -n “.”;
echo -e “${PID}\t${SUM}\t${PROGNAME}” >> ${TMP}/${SCRIPT_NAME}.pid;
echo -e “${SUM}\t${PID}\t${PROGNAME}” >> ${TMP}/${SCRIPT_NAME}.kb;
echo -e “${PROGNAME}\t${SUM}\t${PID}” >> ${TMP}/${SCRIPT_NAME}.name;
fi
let OVERALL=$OVERALL+$SUM
SUM=0
done
echo “${OVERALL}” > ${TMP}/${SCRIPT_NAME}.overal;
echo;
echo “Overall swap used: ${OVERALL} kB”;
echo “========================================”;
case “${SORT}” in
name )
echo -e “name\tkB\tpid”;
echo “========================================”;
cat ${TMP}/${SCRIPT_NAME}.name|sort -r;
;;

kb )
echo -e “kB\tpid\tname”;
echo “========================================”;
cat ${TMP}/${SCRIPT_NAME}.kb|sort -rh;
;;

pid | * )
echo -e “pid\tkB\tname”;
echo “========================================”;
cat ${TMP}/${SCRIPT_NAME}.pid|sort -rh;
;;
esac
rm -fR “${TMP}/”;

#!/bin/bash

# find-out-what-is-using-your-swap.sh
# — Get current swap usage for all running processes
# —
SCRIPT_NAME=`basename $0`;

SORT=”kb”; # {pid|kB|name} as first parameter, [default: kb]
[ “$1” != “” ] && { SORT=”$1″; }

[ ! -x `which mktemp` ] && { echo “ERROR: mktemp is not available!”; exit; }
MKTEMP=`which mktemp`;
TMP=`${MKTEMP} -d`;
[ ! -d “${TMP}” ] && { echo “ERROR: unable to create temp dir!”; exit; }

>${TMP}/${SCRIPT_NAME}.pid;
>${TMP}/${SCRIPT_NAME}.kb;
>${TMP}/${SCRIPT_NAME}.name;

SUM=0;
OVERALL=0;
echo “${OVERALL}” > ${TMP}/${SCRIPT_NAME}.overal;

for DIR in `find /proc/ -maxdepth 1 -type d -regex “^/proc/[0-9]+”`;
do
PID=`echo $DIR | cut -d / -f 3`
PROGNAME=`ps -p $PID -o comm –no-headers`

for SWAP in `grep Swap $DIR/smaps 2>/dev/null| awk ‘{ print $2 }’`
do
let SUM=$SUM+$SWAP
done

if (( $SUM > 0 ));
then
echo -n “.”;
echo -e “${PID}\t${SUM}\t${PROGNAME}” >> ${TMP}/${SCRIPT_NAME}.pid;
echo -e “${SUM}\t${PID}\t${PROGNAME}” >> ${TMP}/${SCRIPT_NAME}.kb;
echo -e “${PROGNAME}\t${SUM}\t${PID}” >> ${TMP}/${SCRIPT_NAME}.name;
fi
let OVERALL=$OVERALL+$SUM
SUM=0
done
echo “${OVERALL}” > ${TMP}/${SCRIPT_NAME}.overal;
echo;
echo “Overall swap used: ${OVERALL} kB”;
echo “========================================”;
case “${SORT}” in
name )
echo -e “name\tkB\tpid”;
echo “========================================”;
cat ${TMP}/${SCRIPT_NAME}.name|sort -r;
;;

kb )
echo -e “kB\tpid\tname”;
echo “========================================”;
cat ${TMP}/${SCRIPT_NAME}.kb|sort -rh;
;;

pid | * )
echo -e “pid\tkB\tname”;
echo “========================================”;
cat ${TMP}/${SCRIPT_NAME}.pid|sort -rh;
;;
esac
rm -fR “${TMP}/”;

How to pass a “password” to su as a variable in script and execute tasks to an array of hosts

Some of you may work for organizations that do access control for linux servers. In which case they do not use ssh keys for root, and are still doing the unthinkable allowing the use of password authentication.

So this means you have to log into a server and the “su –“ to root before you can execute commands, and if you have an array of servers this could be tedious and time consuming. I was told by everyone that you can’t pass a “password” as a variable in script to su, as it’s not allowed.

Guess what…that’s a lie, because I’m going to show you how to do it securely.

1. So you need to install something called expect on all your servers. This tool is used for interactive testing of scripts. It makes the script pass human typing where needed. You can pass variables to this and use it as a wrapper inside another script.
   1. “Yum install expect” on debian “apt-get install expect”
2. Now what you want to do is log into your server as the user not root, and inside the home directory you want to setup the following to scripts
   1. Create a file called gotroot
   2. Vi   gotroot, add the following below and save.

The script below is a wrapper script, that you will use inside another bash script later in this tutorial.

Usage would be

./gotroot <user> <host><userpass><rootpass>

These arguments will then get passed to the remote host and it will execute the send commands below in our case “ls –al”, and then once its done it will exit and log out of the server and return you to the host you started from. This script does not account for ssh fingerprinting, so you will need to ensure you fingerprint your user to each server before using this script. I will add fingerprinting in future, just got lazy.

What I like to do is..I will write a bash script that it going to do a bunch tasks, scp it over all the servers as my user, then comment out the ls –al section and uncomment the section where you can tell to run the bash script. This will then log in to the server and su to root, execute your bash script, exit and log  out.

========================
#!/usr/bin/expect -f
set mypassword [lindex $argv 2]
set mypassword2 [lindex $argv 3]
set user [lindex $argv 0]
set host  [lindex $argv 1]
spawn ssh -tq $user@$host

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

#this section is only needed if you are NOT using ssh keys
#expect “Password:”
#send “$mypassword\r”
#expect “$ “

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

send “su -\r”
expect “Password:”

send “$mypassword2\r”
expect “$ “

#this will execute command on the remote host

send “ls -al\r”
expect “$ “

#this will execute script you want to run on remote host
#send “/home/nicktailor/script.pl\r”
#expect “$ “

#this command will exit the remote host
send “exit\r”
send “exit\r”

interact

==============================================

How to wrap this script so it will do any array of hosts within bash.

1. Create a file called host
   1. Vi  hosts and the following below in it.
   2. Also created a file called logs.txt “touch logs.txt”

This script will all you to use the above script as a wrapper and it will go and execute the command you want from got root to each host in the servers variable listed below.

In addition you it will prompt you the user name and root pass, and will not show he passwords you enter, it will prompt you the same way if you were to do “su –“. It will then take those credentials and use it for each host securely, the passwords will not show up in logs or history anywhere, as some security departments would have issues with that.

So you simply type “./hosts”

It will prompt you for whatever it requires to continue. Just be sure that you add the array of hosts you want to execute the tasks on, and that you have setup a ssh fingerprint as your user first. Expect scripts are extremely easy to learn, once you play with this.

=======================================
#!/bin/bash

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

#some colour constants #

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

CLR_txtblk=’\e[0;30m’ # Black – Regular
CLR_txtred=’\e[0;31m’ # Red
CLR_txtgrn=’\e[0;32m’ # Green
CLR_txtylw=’\e[0;33m’ # Yellow
CLR_txtblu=’\e[0;34m’ # Blue
CLR_txtpur=’\e[0;35m’ # Purple
CLR_txtcyn=’\e[0;36m’ # Cyan
CLR_txtwht=’\e[0;37m’ # White
CLR_bldblk=’\e[1;30m’ # Black – Bold
CLR_bldred=’\e[1;31m’ # Red
CLR_bldgrn=’\e[1;32m’ # Green
CLR_bldylw=’\e[1;33m’ # Yellow
CLR_bldblu=’\e[1;34m’ # Blue
CLR_bldpur=’\e[1;35m’ # Purple
CLR_bldcyn=’\e[1;36m’ # Cyan
CLR_bldwht=’\e[1;37m’ # White
CLR_unkblk=’\e[4;30m’ # Black – Underline
CLR_undred=’\e[4;31m’ # Red
CLR_undgrn=’\e[4;32m’ # Green
CLR_undylw=’\e[4;33m’ # Yellow
CLR_undblu=’\e[4;34m’ # Blue
CLR_undpur=’\e[4;35m’ # Purple
CLR_undcyn=’\e[4;36m’ # Cyan
CLR_undwht=’\e[4;37m’ # White
CLR_bakblk=’\e[40m’   # Black – Background
CLR_bakred=’\e[41m’   # Red
CLR_bakgrn=’\e[42m’   # Green
CLR_bakylw=’\e[43m’   # Yellow
CLR_bakblu=’\e[44m’   # Blue
CLR_bakpur=’\e[45m’   # Purple
CLR_bakcyn=’\e[46m’   # Cyan
CLR_bakwht=’\e[47m’   # White
CLR_txtrst=’\e[0m’    # Text Reset

#

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

SERVERS=”host1 host2 host3”
#echo -e “${CLR_bldgrn}Enter Servers (space seperated)${CLR_txtrst}”

#read -p “servers: ” SERVERS
echo -e “${CLR_bldgrn}Enter User${CLR_txtrst}”
read -p “user: ” USERNAME
echo -e “${CLR_bldgrn}Enter Password${CLR_txtrst}”
read -p “password: ” -s USERPW
echo
echo -e “${CLR_bldgrn}Enter Root Password${CLR_txtrst}”
read -p “password: ” -s ROOTPW
echo
for machine in $SERVERS; do
~/gotroot ${USERNAME} ${machine} ${USERPW} ${ROOTPW}  2>&1 | tee -a logs.txt
done

======================================

Hope you enjoyed this tutorial and if you have any questions email nick@nicktailor.com

How to do multi-threaded backups and restores for mysql

So there are probably a lot of people out there who have the standard master-slave mysql database servers running with InnoDB and MyISAM Databases.

This not usually a problem unless you have high amount of traffic going to your databases using InnoDB, since mysql does not do multithreaded dumps or restores, this can be problematic if your replication is broken to the point where you need to do a full restore.

Now if your database was say 15gigs in size, consisting of Innodb and myisam db’s in a production environment this would be  brutal, as you would need to lock the tables on the primary while your restoring to the slave. Since mysql does not do a multithreaded restores, this could take 12 hours or more, keep in mind this is dependent on hardware.  To give you an idea the servers we had when we ran into this issue, to help you gauge your problem.

Xeon quad core, sata 1 T drives, 18 gigs of ram (Master and Slave)

Fortunately, there is a solution 🙂

There is a free application called xtrabackup by Percona which does multithreaded backup and restores of myisam and innodb combined. In this blog I will be explaining how to set it up, and what I did to minimize downtime for the businesses.

What you should consider doing

Since drive I/O is a factor with high traffic Database servers which can seriously impede performace significantly. We built new servers same specs but with SSD drives this time.

Xeon quad core, (sata3) 1T, (SSD) 120G 18 gigs of ram

Now this is not necessary, however if database traffic is high you should consider SSD or even fiber channel drives if your setup supports it.

Xtrabackup is free unless you use mysql enterprise, then its $5000/server to license it. Honestly using mysql enterprise in my opinion is just stupid, is exactly the same except you get support, the same support you could get online or irc on any forum which is probably better, why pay for something you don’t need to.

Install and setup

Note: This will need to be installed on both master and slave database servers, as this process will replace the mysqldump and restore method you use.

1. rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
2. Yum install percona-xtrabackup.x86_64 (Master & Slave both servers)

Create  backup

Note: There are number of ways you can do this. You can have it output to a /tmp directory while its doing the backup process, or you can have it output to stdout and compress to a directory. I will show you how to do both ways.

1. Since innobackupex, which is the tool with xtrabackup we are going to use, looks at the /etc/my.cnf file for the data directory for mysql, we do not have to define a lot in our command string. For this example we do not setup a mysql password, however if you did you simply add –user <user> -pass <pass> to the string.

This process took 5 minutes on a 15gig Database with Xeon quad core, (sata3) 1T, (SSD) 120G 18Gram

2. Innobackupex <outputdirectory>
     Eg. Innobackupex /test (this command will create another directory inside this one with a time stamp, it’s a fullback of all databases innodb and myisam uncompressed.)

3. innobackupex –stream=tar ./ | gzip – > /test/test.tar.gz (This command will do the same as the above except will output to stdout and compress the fullbackup into the tar file

Note: you also need to use the -i option to untar it eg. tar -ixvf test.tar.gz, ensure mysql is stopped on any slave before restoring, and dont forget to chown -R mysql:mysql the files after you restore the data to the data directory using the innobackupex –copy-back command.

Note: I have experienced issues with getting replication to start doing a full backup and restore on to a slave with innodb and myisam, using the innobackupex stream compression to gzip, after untarring for whatever reason the innodb log files had some corruption, which caused the slave to stop replication upon immediate connection of the master.

if the stream compression doesnt work do a uncompressed backup as shown above, and then rsync the data from your master to the slave via a gige switch if possible (ie. rsync -rva –numeric-ids <source> <destination>:/)

Our 15gig DB compressed to 3.4gigs

1. Now copy tar file or directory to that innobackupex created to the slave server via scp

Scp * user@host:  <-(edit accordingly)

Doing a Restore

Note: The beauty of this restore is it will be a multi-threaded restore utilizing multiple cores instead of just one, since our server data directory is now sitting on SSD, disk I/O will be almost nill, increasing performance significantly, and reducing load.

1. On your primary database server log into mysql and lock the tables
   1. Mysql> FLUSH TABLES WITH READ LOCK;
   2. Now on your slave: To do a restore of all the databases its pretty easy.
      1. innobackupex –copy-back /test/2013-02-03_17-21-52/ (update the path to where ever the innobackupex files are.)

This took 3 mins to restore a 15gig DB with innodb and myisam for us on
Xeon quad core, (sata3) 1T, (SSD) 120G 18 gigs of ram

Setting up the backup crons

1. Now if you were using mysqldump as part of your mysql backup process then you will need to change it to use the following.
2. Create a directory on the slave called mysqldumps
3. Create a file called backups.sh and save it.
   1. Add the following to it.

#!/bin/bash
innobackupex –stream=tar ./ | gzip – > /mysqldumps/innobackup-$(date +”%F”)

Note: that our backups are being stored on our sata3 drive and data directory resides on the SSD

1. Now now add this to your crontab as root, again change the cron to run however often you need to run.
   1. 0 11,23 * * * /fullpath/ backups.sh

Setting up diskspacewatch for the SSD drive.

1. Since the SSD drive is 120G, we need to setup alert to monitor to watch the space threshold. If you not have the resources to implement a tool to specifically to monitor diskspace, then you can write a script that watches the diskspace and send out an email alert in the event the threshold is reached.
2. Run a df –h on your server find the partition you want it to watch edit (df /disk2) on the script to which ever partition you want it to watch, threshold is defined by ( if [ $usep -ge 80 ]; then)
3. Create a file called diskspacewatch, add and save below

#!/bin/sh
df /disk2 | grep -vE ‘^Filesystem|tmpfs|cdrom’ | awk ‘{ print $5 ” ” $1 }’ | while read output;
do
echo $output
usep=$(echo $output | awk ‘{ print $1}’ | cut -d’%’ -f1  )
partition=$(echo $output | awk ‘{ print $2 }’ )
if [ $usep -ge 80 ]; then
echo “SSD Disk on slave database server Running out of space!!  \”$partition
($usep%)\” on $(hostname) as on $(date)” |
mail -s “Alert: Almost out of disk space $usep%” nick@nicktailor.com
fi
done

1. Now you want to setup a cron that runs this script every 1 hour, or however long you want
   1. 0 * * * * /path/diskspacewatch

That’s my tutorial on a mysql multithreaded backup and restore setup. If you have questions email nick@nicktailor.com