"MySQL and can attain project of any scale"

Congrats to PHP Developers, Inc. best of luck and continued success.

http://www.onlineprnews.com/news/133788-1304147582-php-developers-inc-praised-with-ceo-quotes-for-their-success.html

The Southeast LinuxFest (SELF)

The Southeast LinuxFest (SELF) is just around the corner ! June 10-12, 2011 in Spartanburg, SC.

The schedule is available if you have not had a chance to see it yet: http://www.southeastlinuxfest.org/SELF2011-Schedule.pdf

I will be at the conference this year and I am eagerly awaiting to meet everyone.

So please stop by and say hi !

MySQL Relayed Replication Solution

Once I had a situation where MySQL cluster was not an option.  I still wanted replication and redundancy. 

What I built was a relayed replication solution. 

I had a MYSQL DB running the INNODB engine that contained over a TB of data. 

MySQL master server ( SERVER_A ) that replicated to the slave ( SERVER_B ).

SERVER_B was also a slave that then replicated to a second slave (SERVER_C).

 I would have preferred to add a server_d but budget did not allow. 

What did this do for us?  Well this structure allowed us few different options.

First we had redundancy hardware and with replication. If SERVER_A crashed we could update the masterDB reference, which was just an internal dns reference, to SERVER_B and the site was up and running with little downtime. We could then do some research on SERVER_A to find what the problem was. Once this box (server_a) was ready for rotation again we would make it a slave to SERVER_C since SERVER_B was the new master.  

This round robin of servers also allowed us to add database updates in a manor that updated the slaves first and near zero downtime for an update to the master. We just rotated the order of the databases. 

Another benefits of this hardware setup was we where able to build out smart database handles. So based on your query and related settings we could decide which database would handle the select. All writes went to the master of course.  This allowed the big queries to stay out of the way of other selects and resulted in few if any deadlocks or slow responses. 

So for example: 

Then after a database rotation:

If you are running a Master with two to three slaves off it I would ask why? Once you loose the master all of your slaves are also down. With relayed replication you can keep replication in case of a master crash. The worst case scenario is when the slave in the middle crashes. The best solution for this would be a forth box so you can skip down further and still have replication up.

With optimized queries you can keep the seconds behind master at 0 and diversify your select load to enable a robust footprint that has built in replication redundancy.

The Data Charmer also talks about  Relayed replication

MySQL Debian Install from Source

Without a doubt Debian versions of Linux are becoming more and more popular. (Ubunto and Suse for example)  It is not rocket science to get a MySQL server installed on a Debian system.

 apt-get install mysql-server

The problem some people have is making it easy to get the latest version of MySQL and not just the version that is available via the Advanced Packaging Tool (apt).

 I wanted to at least make available a script/steps for everyone to get the job done quickly and easily.  I do not take all the credit for this. I did find a lot of the work done here , I just packaged it up. 

Cut and paste the following into a script.  ( mysql_source_install.sh )  

chmod +x the script ( chmod +x mysql_source_install.sh )  and then execute it  ./mysql_source_install.sh

Or just download it here mysql_source_install.sh

#!/bin/bash
# http://sqlhjalp.blogspot.com/
# April 2011

VERSION=5.5
RELEASE=11

echo  -n " Are you ready to start. !  1 = YES | 0 = NO: "
read start

if (($start > 0 ))
then
        echo "OK then... : ";
else
        exit;
fi


echo -n " What Version are you downloading? (ie: $VERSION)  :    "
read VERSION

if [ -z "$VERSION" ]
then
  echo -n "Need a Version ";
  exit;
else
  echo ""
fi

echo -n " What release is it of $VERSION ? ie: $VERSION.($RELEASE)  :    "
read RELEASE

if [ -z "$RELEASE" ]
then
  echo -n "Need a release";
  exit;
else
  echo ""
fi

echo " Where are you located: "
echo " Europe - 1 ";
echo " North America - 2 ";
echo " Latin America - 3";
echo " Asia - 4 ";
echo " Africa - 5 ";
echo " Oceania -6 ";

read LOCATION


if [ "$LOCATION " -eq "1" ]
then
 echo " Europe ";
 URL="ftp.sunet.se/pub/unix/databases/relational/mysql"
elif  [ "$LOCATION " -eq "2" ]
then
 URL="mysql.mirrors.hoobly.com"

elif  [ "$LOCATION " -eq "3" ]
then
 URL="mysql.cce.usp.br"

elif  [ "$LOCATION " -eq "4" ]
then
 URL="mysql.ntu.edu.tw"

elif  [ "$LOCATION " -eq "5" ]
then
 URL="mysql.mirror.ac.za"

elif  [ "$LOCATION " -eq "6" ]
then
 URL="mysql.mirrors.ilisys.com.au"

else
 echo " Invalid Location ";
 exit;
fi



echo "Downloading  mysql-$VERSION.$RELEASE.tar.gz  to /usr/local/src/  FROM $URL  ";
wget http://dev.mysql.com/get/Downloads/MySQL-$VERSION/mysql-$VERSION.$RELEASE.tar.gz/from/http://$URL/  -O mysql-$VERSION.$RELEASE.tar.gz


echo  -n " Can I continue?. !  1 = YES | 0 = NO: "
read start
exit;
if (($start > 0 ))
then
        echo "Unpacking.... ";
else
        exit;
fi

tar -vxzf mysql-$VERSION.$RELEASE.tar.gz

cd mysql-$VERSION.$RELEASE


echo "Install tools...";

apt-get install cmake
apt-get install g++
apt-get install libncurses5-dev
apt-get install bison
apt-get install libaio-dev


cmake -LAH > options
cmake .
make
make install
make install scripts


cd /usr/local/mysql
chown -R mysql .
chgrp -R mysql .

scripts/mysql_install_db --user=mysql

# chown -R root .
# chown -R mysql data

./bin/mysqld_safe --user=mysql &
./bin/mysql_secure_installation

cp support-files/my-huge.cnf /etc/my.cnf


ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql

echo "MySQL tools installed in /usr/local/mysql/bin/ ";

mysql -p