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