Tag Archives | BASH

Drop all MySQL DBs (except mysql & information_schema)

For some reason I can not remember the following command. I have been using it a lot lately when testing MySQL replication.

  1. mysql –skip-column-names –batch -e “show databases” | grep -E -v “mysql|information_schema” | xargs -I “@@” mysql -e “drop database @@”

This will drop all DBs except the default mysql and information_schema dbs. Then I can Rinse and Repeat this script. Of course only do this on the slave!

Script to compare RPMs on two different CentOS servers

I wanted to make sure that the same RPMs were installed on several servers. I wasn’t worried about versions of RPMs because everything should be kept up to date via yum. So I sat down and wrote the script below. It has been on my ToDo list for quite a while!

RRPM=$(ssh $REMOTESERVER "rpm -qa --queryformat '%{NAME}\n'" )
LRPM=$(rpm -qa --queryformat '%{NAME}\n')

echo "*** Missing from $REMOTESERVER" ***
grep -vf <(echo "$RRPM"| sort) <(echo "$LRPM"|sort)
echo
echo "*** Missing from Local system ***"
grep -vf <(echo "$LRPM"| sort) <(echo "$RRPM"|sort)
echo

This script connects to a remote machine and compares RPMs installed there to the RPMs that are installed locally.

AutoSSH on CentOS

I have been interested in MySQL replication over ssh and I wanted a way to make sure that the tunnel is always up. Everyone says to use AutoSSH. AutoSSH is not in EPEL, but is in rpmforge (Is that that same as DAG? Didn’t they merge?). I installed rpmforge:

rpm -Uhv http://apt.sw.be/redhat/el5/en/i386/rpmforge/RPMS/rpmforge-release-0.3.6-1.el5.rf.i386.rpm

I don’t like to do the RepoDance, so I disabled rpmforge:

sed -i "s/enabled = 1/enabled = 0/" /etc/yum.repos.d/rpmforge.repo

Next I installed AutoSSH

yum install --enablerepo=rpmforge autossh</p>

And finally my Bash function to create an AutoSSH tunnel:
 

function StartAutoSSH {
	. /etc/rc.d/init.d/functions
	AUTOSSH_PIDFILE=/var/run/autossh.pid # we are assuming only one autossh tunnel
	if [ ! -e $AUTOSSH_PIDFILE ]; then
	AUTOSSH_PIDFILE=$AUTOSSH_PIDFILE;export AUTOSSH_PIDFILE
	autossh -M29001 -f -N -L7777:127.0.0.1:3306 [email protected]
	else
	status -p $AUTOSSH_PIDFILE autossh
	fi
}

If you call this function, it will created the specified tunnel or if it is up and runnng, then it will spit back the PID.

Mirror all MySQL DBs to the local machine

Continuing on my quest to get MySQL replicating over ssh, I am using the following bash function to replicate all remote DBs locally:

 

function MirrorAllRemoteDBsToLocal {
	for REMOTEDB in $(mysql -h 127.0.0.1 -P 7777 --batch --skip-column-names -e "SHOW DATABASES")
	do
	LOCALDBEXISTS=$(mysql --batch --skip-column-names -e "SHOW DATABASES LIKE '"$REMOTEDB"';" | grep "$REMOTEDB" > /dev/null; echo "$?")
	if [ $LOCALDBEXISTS -ne 0 ];then
		echo "adding $REMOTEDB to local MySQL"
		mysql -e "create database $REMOTEDB;"
		echo "getting a dump"
		mysqldump -h 127.0.0.1 -P 7777 $REMOTEDB | mysql $REMOTEDB;
		echo " adding $REMOTEDB to my.conf"
		sed -i '/master-port/a\\treplicate-do-db='$REMOTEDB'' /etc/my.cnf
	fi
	done
}

Line 2 connects to the local AutoSSH tunnel and gets a list of all the remote DBs.

Then we loop through the DBs and if there is not a DB locally with that name, the script will create a database. Next the script gets (Line 9) a dump of the DB and copies it to the newly created DB.

And finally the script add the DB to the /etc/my.cnf (line 11).

All that should have to happen is to issue a slave stop and then slave start, and all DBs should be mirrored locally.

OS X: Move files based on creation date

I wanted to organize my flip videos – I wanted them moved into a folder named after their creation date.

One of the scripts that I found uses ls’s “–time-style” parameter. BSD’s ls does not have this value. MacPorts’ coreutils port contains gls (GNU’s ls) that does support the parameter. So here is my script to move movies into a sub folder based on the movies creation date.

for filename in *.MP4; do
    datepath="$(gls -l --time-style=+%Y-%m-%d $filename | awk '{print $6}')"
    echo "$datepath"
    if ! test -e "$datepath"; then
        mkdir -pv "$datepath"
    fi
    mv -v $filename $datepath
done

Check if a file exists on a remote server

I was working on a copy WordPress site to a local machine script, and I wanted to check if the remote path was a WordPress site.
Here is the code I used.

CHECKCMD="ls /path/to/wp-config.php | grep wp-config.php > /dev/null; echo \$?"
CHECKFILE=$(ssh $SRCSERVER $CHECKCMD)
if [ $CHECKFILE -eq 0 ]; then
	echo "file exists"
fi

Waking a sleeping Mac Pro upon opening a folder

Scenario

I have two macs at home, a MacPro and a MacMini. The MacMini is attached to our TV. I put my MacPro to sleep when I leave for work in the morning. My wife comes home and tries to play videos for my son on the Mac Mini. The videos are actually on the Mac Pro, but it is transparent to her, when the machine is on. And that is the problem. When she clicks on the symbolic lynk and the MacPro is sleeping she can’t find the videos she is looking for.

I needed a way to wake the MacPro when she is looking for the videos.

This is longer post describing my whole Wake/Sleep setup. Requirements are MacPorts, and a Wake on LAN (WOL) utility. I use DDWRT, so there is one on my home router.

I am a big fan of MacPorts. I used to use Fink, but I switched, and I don’t remember why. There are two utilities in MacPorts that are useful for sleeping macs, Sleepwatcher and wakeonlan. You could install Sleepwatcher via source, but I prefer a Package management system.

Sleep

Sleepwatcher is the most important part of this system. I used to put my Mac to sleep every night at 11 pm, but if I enabled “Wake for network access” in the energy saver preference, the machine would wake up every two hours. This article describes the problem and a solution – sleepwatcher.

So I installed sleepwatcher via MacPorts. Then I added the following two lines to my /opt/local/etc/rc.sleep ( I could not get it working in my “$home/.sleep” file)

/bin/sleep 1
/usr/sbin/systemsetup -setwakeonnetworkaccess on >/dev/null

Then I added the following to my /opt/local/etc/rc.wakeup (again I could not get my “$home/.wakeup” to work)

/usr/sbin/systemsetup -setwakeonnetworkaccess off >/dev/null

This allows the machine to go to sleep and not wake until it receives a WOL packet.

That takes care of the sleep part.

Wake

Now my machines are sleeping (properly), and they can be woken from a WOL packet. Since I use DDWRT, I can go to the web interface and wake a machine (I have OpenVPN tunnels going all over the place, so i can access the web interface internally). It occurred to me that if there is a web interface, there has to be a WOL executable on the router. With public key authentication, I can connect to my DDWRT router with the following command and wake a machine:

ssh homerouter "/usr/sbin/wol -i 192.168.X.255 xx:xx:xx:xx:xx:xx"

That takes care of the wake part.

Folder Actions

To have a machine wake when I access a folder, I add the following applescript to a “Folder Actions”:

on opening folder this_folder
	try
		tell application "Finder"
			activate
			try
				set ping_result to (do shell script "ping -c 1 machine.trying.towake;echo -n")
				if "100.0% packet loss" is in ping_result then
					do shell script "ssh homerouter "/usr/sbin/wol -i 192.168.X.255 xx:xx:xx:xx:xx:xx" "
				end if
			end try
		end tell
	on error errmsg
	end try
end opening folder

If the machine does not answer a ping, the script will ssh to the ddwrt router and launch the wol executable to wake the sleeping machine.

A complex system, but it works.

New MD5 based backup script

I found this use of md5 and find the other day. I based my current backup script around it. The md5 will show if anyone modifies a file, or adds/removes a file in the web hosting root (/var/www) or in the config directory (/etc/httpd/conf.d/). If there is a change then zip each site up individually and move to a backup folder to be rsynced to other servers.

NewWWWMD5=$(find /var/www/ -type f -exec md5sum {} \; | md5sum - | awk '{print $1}')
OldWWWMD5=$(cat $PARENTDIR/_var_www_*.md5)
NewConfMD5=$(find /etc/httpd/conf.d/ -type f -exec md5sum {} \; | md5sum - | awk '{print $1}')
OldConfMD5=$(cat $PARENTDIR/_etc_httpd_conf.d_*.md5)

if [ $NewWWWMD5 = $OldWWWMD5 -a $NewConfMD5 = $OldConfMD5 ]; then
	echo "Neither /var/www/ nor /etc/httpd/conf.d/ have changed"
else
	rm -rf $BACKUPDIR/*Files
	echo "/var/www or /etc/httpd/conf.d has changed"
	mkdir -p $BACKUPDIR-Files

	# backup /var/www
	for directory in /var/www/*; do
	  	if [ -d $directory ]; then
	    	bu $directory;
	    fi
	done

	# replace previous /var/www MD5
	rm -f $PARENTDIR/_var_www_*.md5
	find /var/www/ -type f -exec md5sum {} \; | md5sum - | awk '{print $1}' &gt; $PARENTDIR/_var_www_$CURRENTDAY.md5

	#backup /etc/httpd/conf.d
	bu "/etc/httpd/conf.d"

	# replace previous /etc/httpd/conf.d MD5
	rm -f $PARENTDIR/_etc_httpd_conf.d_*.md5
	find /etc/httpd/conf.d/ -type f -exec md5sum {} \; | md5sum - | awk '{print $1}' &gt; $PARENTDIR/_etc_httpd_conf.d_$CURRENTDAY.md5
fi

Seems to work!

BASH (readline) keyboard shortcuts

I was just in training and the instructor was a command line keyboard shortcut wizard. He was magically making words disappear and reappear. So i fond this list of shortcuts. Many of them did not work in my OS X BASH prompt.I fond I had to go into the terminal.app preferences and select “use option key as meta key” on the keyboard tab (it is at the bottom). Now I can add a few shortcuts to my repertoire.