jbmurphy.com

  • Comparing master and slave MySQL WordPress DBs

    My VPS host – Rackspace contacted me the other day to tell me that my host server became unresponsive. After it came back online I went to my MySQL replicated slave to check the status. The /var/log/mysqld.log said:

    Error reading packet from server: Client requested master to start replication from impossible position

    This article siad

    “9 times out of 10 it’s because the master crashed and when it came back online a new binlog file was made”.

    That sounds about right, my virtual host was probably force rebooted, and my VM crashed. I recovered my DBs by re-mirroring my DBs over an AutoSSH connection between my two servers.

    The article goes on to say:

    Now if your super sensitive of lost events because a row or two could of been lost from this replication event, do some spot testing for tables written to often”

    I wanted to verify my DBs were the same on both master and slave, so I wrote this BASH function:

    function CompareDBs {
if [ $1 ]; then
DB=$1;
RDBDUMP=$(mysqldump -h <a href="tel:127.0.0.1">127.0.0.1</a> -P 7777 --order-by-primary --skip-extended-insert --skip-opt --skip-comments $DB)
LDBDUMP=$(mysqldump --order-by-primary --skip-extended-insert --skip-opt --skip-comments $DB)
diff -y --suppress-common-lines &lt;(echo &quot;$RDBDUMP&quot;) &lt;(echo &quot;$LDBDUMP&quot;)
fi
}

    Call the function with the DB you want to compare. This script assumes that you have an SSH Tunnel between your two servers.

     

    ********* MAKE SURE YOUR TIME IS RIGHT ON BOTH SERVERS*********
    This will save you some hair pulling!

  • 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!

  • WordPress 3.2 requirements and CentOS 5.6

    Current requirements

    • PHP version 4.3 or greater
    • MySQL version 4.1.2 or greater

    WordPress 3.2 requirements:

    • PHP version 5.2.4 or greater
    • MySQL version 5.0 or greater.

    Default PHP on Cent0S 5.6 = PHP 5.1.6.

    CRAP

    Updated: See this post on how to install an updated PHP on CentOS 5.6

  • How to update ESXi 4.1 without vCenter

    I wanted to update a standalone ESXi box from 4.1 to 4.1 Update 1. Here is how I went about it:

    1. Downloaded the update on a windwos box from here and unziped it
    2. Open the viClient datastore browser and upload the unzipped folder. If you put it off the root of your datastore, the path will be:
      1. /vmfs/volumes/datastore1/update/update-from-esxi4.1-4.1_update01
    3. Install the VMware vSphere PowerCLI – which is a Windows PowerShell interface to the vSphere API
    4. Add the VMware cmdlts to your PowerShell session: add-pssnapin “VMware.VimAutomation.Core”
    5. Put the ESXi server into maintenance mode.
    6. In PowerShell, connect to the ESXi server:  Connect-VIServer servername.domain.local
    7. In PowerShell: Install-VMHostPatch -HostPath /vmfs/volumes/datastore1/update-from-esxi4.1-4.1_update01/metadata.zip
    8. The result was: WARNING: The update completed successfully, but the system needs to be rebooted for the changes to be effective.
    9. Reboot!

    The summary below was also returned:

    Id                                              VMHostId IsIns IsApp Needs Needs
                                                         talle licab Resta Recon
                                                         d     le    rt    nect
--                                              -------- ----- ----- ----- -----
cross_oem-vmware-esx-drivers-scsi-3w-9xxx_... ...ha-host False True  True  False
cross_oem-vmware-esx-drivers-net-vxge_400.... ...ha-host False True  True  False
deb_vmware-esx-firmware_4.1.0-1.4.348481      ...ha-host False False True  False
deb_vmware-esx-tools-light_4.1.0-1.4.348481   ...ha-host True  True  False False

  • 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.

  • Google Reader Starred items to Together.app

    I use Together.app from Reinvented Software as my archiving solution – my knowledge base. I like the product because it leaves the pdfs I create on the filesystem and the db contains the tags and links associated with each file. I used to use Yojimbo, but it keeps all the files in their database. I am not sure it is that big of an issue (especially because we are considering SharePoint as a document management system!), but I am living with Together.app. I just need a way to get my Together.app data to my iPhone – but that is another issue.

    My information consumption workflow starts in Google reader, and Reeder for the iPad and iPhone, and ends in Together.app. Interesting items are “Starred” in Google Reader, and I needed a way to get the starred items to Together.app. I could not find a way to do it in bulk until I ran across this post explaining how to dump your starred items to a html document. I took the script a little further and I used apple script to import the url into Together.app:

     

    require "rubygems"
require "open-uri"
require "simple-rss"
feed = "http://www.google.com/reader/public/atom/user%0000000000000000000000%2Fstate%2Fcom.google%2Fstarred?n=50"
rss = SimpleRSS.parse open(feed)
rss.entries.each do |item|
puts "Downloading: #{item.title.sub( ":", "-" )}\n"
%x(osascript -e 'tell application \"Together\" to import url \"#{item.link}\" as web PDF')
end

     
    Make your starred items public, and change the “0000000000000000000000” to your user id (as described in the the original post). Run it, and 50 starred items at a time will be added to your Together.app
     
    My colleague suggested that I unstar the item automatically after added to Together, but I will have to sit down and figure that out.