Check MySQL connection from shell (bash) and backup database

et voilà. In case you should ever be in a situation to check your mysql connection from a shell script you can easily do so by:

#!/bin/bash
echo -n -e "Please enter the DB user: \t\t"
read DB_USER

echo -n -e "Please enter the DB password: \t\t"
read DB_PWD

DB_con_ok=$(mysql -u $DB_USER --password=$DB_PWD -e "show databases;"|grep "mysql")
if [[ $DB_con_ok != "mysql" ]]
then
echo
echo "The DB connection could not be established. Check you username and password and try again."
echo
exit;
fi
#whatever code is executed here, only is done so, IF the mysql credentials are valid

And above, if you need to back up your DB (e.g. in a cron), you can easily do so by

#!/bin/bash
#DEFINITIONS FIRST
DB_USER="mysqlUser"
DB_PWD="mysqlPwd"
VERSION=$(date +"%d-%m-%Y") #store the day, month and year the backup was performed

#CHANGE TO BACKUP DIRECTORY
cd /backup
#PERFORM THE MYSQL BACKUP COMMAND
mysqldump --routines --all-databases -u $DB_USER --password=$DB_PWD > db_$VERSION.mysql

The --routines command makes sure, you're backing up stored procedures as well.

OR if you only want to backup specific databases, just use

#!/bin/bash
#DEFINITIONS FIRST
DB_USER="mysqlUser"
DB_PWD="mysqlPwd"
DATABASES="database1 database2 database3" #define the databases that you want to backup (delimited by the ' ' whitespace character)
VERSION=$(date +"%d-%m-%Y") #store the day, month and year the backup was performed

#CHANGE TO BACKUP DIRECTORY
cd /backup
#PERFORM THE MYSQL BACKUP COMMAND
mysqldump --routines -u $DB_USER --password=$DB_PWD --databases $DATABASES > db_$VERSION.mysql

check out the MySQL manual for mysqldump as well. Hope this was useful to some of you :D

Cheers,
Charly

PHP Performance Optimization

In every development cycle there comes the time, when you've got to optimize things. I really love the following article: guys & iris: you should all have a good read of http://c2.com/cgi/wiki?PrematureOptimization about this topic.

It clearly explains and lays out why it is the best to:

1. Make it work.
2. Make it right.
3. Make it fast.

in exactly that order. To be even more precise it says:

1. Make it work.
2. Make it right
3. Make everything work.
4. Make everything right.
5. Use the system and find performance bottlenecks.
6. Use a profiler in those bottlenecks to determine what needs to be optimized.
7. Make it fast. You maintained unit tests, right? Then you can refactor the code mercilessly in order to improve the performance.

So, I think we've hit that 2nd/4th point now. As you know from one of my last blogs, I massively like my MacBook. So of course, I'm not prepared to switch away for a simple performance analysis tool.

As you might be well aware, a typical combination to identify PHP bottlenecks is XDebug to log execution times and KCacheGrind to make them human-readable. Unfortunately, the installation process of KCacheGrind of Mac OS X is not that straight forward, although it's pretty straightforward :).

I've taken the following small tutorial from http://brent.izolo.com/blog/?p=4 and amended it where necessary.

1. Install Fink. An excellent article on how to set up Fink on your Mac can be found in the O'Reilly macdevcenter. Check it out. Otherwise, you can just download the installable binaries for your version of Mac OS from the Fink homepage.
2. install the X11 development package as hinted by this incredibly useful little tutorial:
   Installing X11 on OS X 10.4 Tiger. It's 5 years old, but still up to date.
3. edit /sw/etc/fink.conf and add:
   unstable/main unstable/crypto
   to the Trees key. The line should now read like this:
   Trees: local/main stable/main stable/crypto unstable/main unstable/crypto
4. Open the command-line and enter:
   fink selfupdate; fink index; fink scanpackages
5. After the update and reindexing finished enter:
   sudo fink install kcachegrind
   in the command line
6. Answer all the questions by the script and prepare for a looooooooooong configuration and installation time (many, many package dependencies)

To run KCacheGrind:

1. start X11 (e.g. by using the Spotlight and searching for "X11")
2. open the console and type:
   /sw/bin/kcachegrind
   

That's it (hopefully!). Enjoy.

Bests,
Charly

Install Sphinx on Mac OSX Tiger (10.4)

Hello everyone (especially developers),

I know, I know - you should NEVER use a development environment that is different from your deployment one, but I just can't help myself. I just love my Mac and don't want to replace my nice little black MacBook by a clumsy other one, just because I've to run a customized version of Gentoo on it :) - neither do I want to mess with BootCamp...

That aside, I've struggled for a little bit getting Sphinx (the incredible indexing and search engine) to work on my machine, a MacBook with OSX Tiger (10.4) on it. Maybe some of you are struggling with the same problem, so I thought I put a short tutorial together that helped me compile and run it successfully.

So here we go:

• download version 2.5 of XCode from the Apple page. Version 2.5 is for Tiger (10.4).
  
• install the X11 development package as hinted by this incredibly useful little tutorial:
  Installing X11 on OS X 10.4 Tiger. It's 5 years old, but still up to date.
• download and install MacPorts (if you haven't already): http://www.macports.org/
• open console and enter sudo /opt/local/bin/port install mysql5-devel
• wait, wait some more, wait, wait, get coffee and wait
• Download Sphinx and untar it to any directory
• in the console, change to the directory you just untar'd Sphinx to
  
• enter in console: ./configure --with-mysql-libs=/opt/local/lib/mysql5/mysql/ --with-mysql-includes=/opt/local/include/mysql5/mysql/
• enter in console: ./make
• enter in console: sudo ./make install

That should have done the trick!

Enjoy.

Bests,
Charly