PlanetMysql.ru — информация о СУБД MySQL

I love short and consise install instructions. I know this is a MySQL blog but our good friend PostGreSQL has a great GIS library. This is what I learned upgrading our PostGIS system to GIS 1.5. Much thanks to Jeremy Tunnell for give this document it’s start.

Start with CentOS 5.6 x86_64 basic install.

Add the PostgreSQL Yum repository to your system.

 $ wget http://yum.pgrpms.org/reporpms/9.0/pgdg-centos-9.0-2.noarch.rpm
 $ rpm -i pgdg-centos-9.0-2.noarch.rpm

Another location for these is DAG. I have to tried these so your results may very.

You will need to exclude the packages CentOS provide by added two lines to the BASE and UPDATE sections of /etc/yum.repos.d/CentOS-Base.repo. They are:

exclude=postgresql*
exclude=geos*

You you are ready to install the needed packages. This includes proj version 4 and geos version 3.

 $ yum install postgresql90-contrib.x86_64
 $ yum install postgis90.x86_64
 $ yum install proj
 $ yum install geos
 $ yum install php-pear
 $ yum install php-devel

GEOS

There may be some dependencies you will have to work through. If you are using PDO and haven’t installed the pgsql PDO drivers, you can do it now:

 $ pecl install pdo_pgsql

PL/pgSQL

You you are ready to initialize the database files and start postgresql.

 $ service postgresql-9.0 initdb
 $ service postgresql-9.0 start

Now you can change to the postgres user and begin installing the functions for GIS. You have to start with defining the language.

 $ su – postgres
 $ psql
 # create language plpgsql ;
 # \q

Now you can create your database and add the GIS functions calls to it.

 $ createdb geos
 $ psql -d geos -f /usr/pgsql-9.0/share/contrib/postgis-1.5/postgis.sql
 $ psql -d geos -f /usr/pgsql-9.0/share/contrib/postgis-1.5/spatial_ref_sys.sql

You you can verify the install.

 $ psql geos
 # select postgis_full_version();
                                              postgis_full_version
——————————————————————————————————–
 POSTGIS=”1.5.2″ GEOS=”3.2.2-CAPI-1.6.2″ PROJ=”Rel. 4.7.1, 23 September 2009″ LIBXML=”2.6.26″ USE_STATS
(1 row)

For more on using PostGIS check out Jeremy’s “PostGIS part 2”.

.

Tweet

PlanetMySQL Voting: Vote UP / Vote DOWN

Installing Apache2 With PHP5 And MySQL Support On CentOS 6.0 (LAMP)

LAMP is short for Linux, Apache, MySQL, PHP. This tutorial shows how you can install an Apache2 webserver on a CentOS 6.0 server with PHP5 support (mod_php) and MySQL support.

PlanetMySQL Voting: Vote UP / Vote DOWN

Installing Lighttpd With PHP5 And MySQL Support On CentOS 5.6

Lighttpd is a secure, fast, standards-compliant web server designed for speed-critical environments. This tutorial shows how you can install Lighttpd on a CentOS 5.6 server with PHP5 support (through FastCGI) and MySQL support.

PlanetMySQL Voting: Vote UP / Vote DOWN

Installing Apache2 With PHP5 And MySQL Support On CentOS 5.6 (LAMP)

LAMP is short for Linux, Apache, MySQL, PHP. This tutorial shows how you can install an Apache2 webserver on a CentOS 5.6 server with PHP5 support (mod_php) and MySQL support.

PlanetMySQL Voting: Vote UP / Vote DOWN

Installing Apache2 With PHP5 And MySQL Support On CentOS 5.6 (LAMP)

LAMP is short for Linux, Apache, MySQL, PHP. This tutorial shows how you can install an Apache2 webserver on a CentOS 5.6 server with PHP5 support (mod_php) and MySQL support.

PlanetMySQL Voting: Vote UP / Vote DOWN

In my last two posts I installed the HandlerSocket plugin into MariaDB and showed how to use it with Perl.  That’s good, but if you are thinking of using HandlerSocket  I’m guessing you have a very high traffic website and it’s written in PHP.  In this post I’m going to connect HandlerSocket with PHP.  In the next post I’ll discuss using HandlerSocket on a production system.

There are a couple of HandlerSocket php modules projects.  I tried each of them and I found PHP-HandlerSocket was the best.  Both of them are still rough and neither of them have documentation beyond their source code.  Maybe this will move things forward.

Here are the applications you need to have installed that where not installed in my last two posts.  Run this to check your system.

 yum install php-devel re2c php php-mysql

Start in the a Downloads directory and wget the newest tarball.

 cd ~
 mkdir Downloads
 cd Downloads
 wget http://php-handlersocket.googlecode.com/files/php-handlersocket-0.0.7.tar.gz

Compile and install the module.

 phpize
 ./configure
 make
 make install

With everything in place, you need to teach PHP to load the module my editing the PHP configuration file  and add the extension.

 vi /etc/php.ini

Search for the Dynamic Extensions section and add this line.

 extension=handlersocket.so

Run php to test that the module is loading correctly.  If nothing happens, your good.

I had some trouble at the start getting the libraries in the right directory.   This is bad:

 PHP Warning:  PHP Startup: Unable to load dynamic library  '/usr/lib64/php/modules/hadlersocket.so'
 -  /usr/lib64/php/modules/hadlersocket.so: cannot open shared object file:
  No such file or directory in Unknown on line 0

To do more testing we need a database and some data.  Here is what I created. I’ve colored the sections of code as they correspond to the output.

CREATE TABLE `user` (
 `user_id` INT (10) UNSIGNED NOT NULL,
 `user_name` VARCHAR (50),
 `user_email` VARCHAR (255),
 `created` DATETIME DEFAULT NULL,
 PRIMARY KEY (`user_id`),
 KEY `NAME` (`user_name`)
 ) ENGINE = INNODB ;

INSERT INTO `user`  VALUES
 ( 1, 'mark', 'mark@mysqlfanboy.com', '0000-00-00 00:00:00' ),
 ( 2, 'linda', 'linda@mysqlfanboy.com', '0000-00-00 00:00:00' ),
 ( 3, 'mark', 'test@mysqlfanboy.com', NULL ) ;

I created this program to read this data:  (mytest.php)  HandlerSocket can write data to the database.  I’ve left writing as an exercise to the reader. (For now.)

<?php
 $host = 'db';
 $port = 9998;
 $dbname = 'test';
 $table = 'user';
 $index = 'NAME';
 $columns = 'user_id,user_name,user_email,created';

 $hs = new HandlerSocket($host, $port);
 if (!($hs->openIndex(0, $dbname, $table, $index, $columns)))
 {
 echo $hs->getError(), PHP_EOL;
 die();
 }

 $retval = $hs->executeSingle(0, '=', array('mark'), 10, 0);
 echo "data > ",$retval[0][1], " \n";
 var_dump($retval);

 $retval = $hs->executeMulti(
 array(array(0, '=', array('mark'), 1, 0),
 array(0, '=', array('linda'), 1, 0)));
 var_dump($retval);

 unset($hs);

 ?>

Here is the output of running mytest.php.

# php mytest.php
data > mark 
array(2) {
 [0]=>
 array(4) {
 [0]=>
 string(1) "1"
 [1]=>
 string(4) "mark"
 [2]=>
 string(20) "mark@mysqlfanboy.com"
 [3]=>
 string(19) "0000-00-00 00:00:00"
 }
 [1]=>
 array(4) {
 [0]=>
 string(1) "3"
 [1]=>
 string(4) "mark"
 [2]=>
 string(20) "test@mysqlfanboy.com"
 [3]=>
 NULL
 }
}
array(2) {
 [0]=>
 array(1) {
 [0]=>
 array(4) {
 [0]=>
 string(1) "1"
 [1]=>
 string(4) "mark"
 [2]=>
 string(20) "mark@mysqlfanboy.com"
 [3]=>
 string(19) "0000-00-00 00:00:00"
 }
 }
 [1]=>
 array(1) {
 [0]=>
 array(4) {
 [0]=>
 string(1) "2"
 [1]=>
 string(5) "linda"
 [2]=>
 string(21) "linda@mysqlfanboy.com"
 [3]=>
 string(19) "0000-00-00 00:00:00"
 }
 }
}
 

So it works, but do you really want to use it?  There are a lot more to questions to investigate.   Is it writing data any faster then SQL? Is writing faster then a bulk file load?  What if you mix SQL and HandlerSocket calls?  What does buffering do to these processes?

You you have any questions about about what I’ve written or that you would like be to investigate, email me – mark @ Grennan.com.

Tweet

PlanetMySQL Voting: Vote UP / Vote DOWN

I am currently working on a project to deploy new website builds to a
small number of servers. I needed something simple and reliable that could
be built in a very short period of time. I decided to whip something up in
bash with the intent of refining it in Python later.

As I began to write this code, I realized that it probably would have been
quicker to do it in Python from the start. I decided to stick with bash as
somewhat of an academic exercise. The vast majority of these deployment
scripts were trivial; check the code out of git, create a manifest, package
it up, spew it to the servers, etc, etc. The problem came during the last
step. We decided to use a symlink to point to the active build out of a
number of builds that could be available on the server at any given time.
Since all of our servers should be running the exact same version of the
build, it makes sense that I should only allow a user of my deployment
scripts to link a build which exists on all servers. But how do you
accomplish this in bash?

In most other languages, you have access to numerous array helping
functions that allow you to perform intersects, uniqs, and merges. My goal
was to do the same thing in bash without forking out to any external
binary. So how do you ensure that a particular thing exists on N number of
servers? Here it is:

function in_array() {
 local x
 ENTRY=$1
 shift 1
 ARRAY=( "$@" )
 [ -z "${ARRAY}" ] && return 1
 [ -z "${ENTRY}" ] && return 1
 for x in ${ARRAY[@]}; do
   [ "${x}" == "${ENTRY}" ] && return 0
 done
 return 1
}

MASTER=()
CURRENT=()
FIRST=1
for SERVER in ${SERVERS}; do
 # collect all builds from server and populate CURRENT list
 COMMAND="${LS} -1fd ${WEBROOT}/${SITE}.*"
 BUILDS=`${SSH} ${SSHOPTS} root@${SERVER} "${COMMAND}"`
 for BUILD in ${BUILDS}; do
   CURRENT=( ${CURRENT[@]-} ${BUILD} )
 done

 # if this is our first time around, copy CURRENT to MASTER
 if [ ${FIRST} -eq 1 ]; then
   MASTER=( ${CURRENT[@]} )
   FIRST=0
 fi

 # now we do a compare between MASTER and CURRENT to see what builds
 # are common
 INTERSECT=()
 for ENTRY in ${CURRENT[@]}; do
   in_array "${ENTRY}" "${MASTER[@]}"
   RET=$?
   if [ "${RET}" -eq 0 ]; then
     INTERSECT=( ${INTERSECT[@]-} ${ENTRY} )
   fi
 done
 MASTER=( ${INTERSECT[@]} )

 # clear the CURRENT array
 CURRENT=()
done

Let me take a moment to explain the code above:

• In order to check for array intersection, you need an in_array()
  function
  • The first argument as the “needle” and the second is the
    “haystack”
  • We verify that both parameters were passed
  • We simply loop through the haystack checking for the needle
  • If we find it, return success. Otherwise, eventually return
    false
• We need to loop through each server eventually, but we’ll start with
  the first one
  • Run an SSH command to get a listing of builds
  • Populate an array ($CURRENT) with the builds that were found
  • Since the first server has no previous server to compare with, so we
    just copy it to $MASTER
• We then loop to the 2nd server, and put the result of getting builds
  into $CURRENT
  • Now that we have the first server’s builds in $MASTER, we perform an
    intersect with $CURRENT
  • We realize the need for an $INTERSECT array to hold the intersections
    found above
  • $INTERSECT becomes $MASTER since it only contains similar builds from
    the 1st and 2nd server
• Looping to the 3rd server, we get the builds and put them in $CURRENT
  • Since $MASTER contains only the similar builds thus far, we again
    compare it with $CURRENT
  • The intersect can now be used to compare against builds on the 4th
    server, and so on
• Once you finish looping through all servers, your $MASTER should
  contain only similar builds

There are a few guides out there which show you how to do this via
forking, but I thought someone may appreciate the elegance of using 100%
bash to accomplish this. I hope this helps someone else out there!

PlanetMySQL Voting: Vote UP / Vote DOWN

I am currently working on a project to deploy new website builds to a
small number of servers. I needed something simple and reliable that could
be built in a very short period of time. I decided to whip something up in
bash with the intent of refining it in Python later.

As I began to write this code, I realized that it probably would have been
quicker to do it in Python from the start. I decided to stick with bash as
somewhat of an academic exercise. The vast majority of these deployment
scripts were trivial; check the code out of git, create a manifest, package
it up, spew it to the servers, etc, etc. The problem came during the last
step. We decided to use a symlink to point to the active build out of a
number of builds that could be available on the server at any given time.
Since all of our servers should be running the exact same version of the
build, it makes sense that I should only allow a user of my deployment
scripts to link a build which exists on all servers. But how do you
accomplish this in bash?

In most other languages, you have access to numerous array helping
functions that allow you to perform intersects, uniqs, and merges. My goal
was to do the same thing in bash without forking out to any external
binary. So how do you ensure that a particular thing exists on N number of
servers? Here it is:

function in_array() {
 local x
 ENTRY=$1
 shift 1
 ARRAY=( "$@" )
 [ -z "${ARRAY}" ] && return 1
 [ -z "${ENTRY}" ] && return 1
 for x in ${ARRAY[@]}; do
   [ "${x}" == "${ENTRY}" ] && return 0
 done
 return 1
}

MASTER=()
CURRENT=()
FIRST=1
for SERVER in ${SERVERS}; do
 # collect all builds from server and populate CURRENT list
 COMMAND="${LS} -1fd ${WEBROOT}/${SITE}.*"
 BUILDS=`${SSH} ${SSHOPTS} root@${SERVER} "${COMMAND}"`
 for BUILD in ${BUILDS}; do
   CURRENT=( ${CURRENT[@]-} ${BUILD} )
 done

 # if this is our first time around, copy CURRENT to MASTER
 if [ ${FIRST} -eq 1 ]; then
   MASTER=( ${CURRENT[@]} )
   FIRST=0
 fi

 # now we do a compare between MASTER and CURRENT to see what builds
 # are common
 INTERSECT=()
 for ENTRY in ${CURRENT[@]}; do
   in_array "${ENTRY}" "${MASTER[@]}"
   RET=$?
   if [ "${RET}" -eq 0 ]; then
     INTERSECT=( ${INTERSECT[@]-} ${ENTRY} )
   fi
 done
 MASTER=( ${INTERSECT[@]} )

 # clear the CURRENT array
 CURRENT=()
done

Let me take a moment to explain the code above:

• In order to check for array intersection, you need an in_array()
  function
  • The first argument as the “needle” and the second is the
    “haystack”
  • We verify that both parameters were passed
  • We simply loop through the haystack checking for the needle
  • If we find it, return success. Otherwise, eventually return
    false
• We need to loop through each server eventually, but we’ll start with
  the first one
  • Run an SSH command to get a listing of builds
  • Populate an array ($CURRENT) with the builds that were found
  • Since the first server has no previous server to compare with, so we
    just copy it to $MASTER
• We then loop to the 2nd server, and put the result of getting builds
  into $CURRENT
  • Now that we have the first server’s builds in $MASTER, we perform an
    intersect with $CURRENT
  • We realize the need for an $INTERSECT array to hold the intersections
    found above
  • $INTERSECT becomes $MASTER since it only contains similar builds from
    the 1st and 2nd server
• Looping to the 3rd server, we get the builds and put them in $CURRENT
  • Since $MASTER contains only the similar builds thus far, we again
    compare it with $CURRENT
  • The intersect can now be used to compare against builds on the 4th
    server, and so on
• Once you finish looping through all servers, your $MASTER should
  contain only similar builds

There are a few guides out there which show you how to do this via
forking, but I thought someone may appreciate the elegance of using 100%
bash to accomplish this. I hope this helps someone else out there!

PlanetMySQL Voting: Vote UP / Vote DOWN

This is one of a few MySQL High Availability strategies.  I have used this for years and found it work great.  If you don’t know about DRBD and MySQL you should read Peter’s comments.

These are step by step instructions for Redhat 5 or CentOS.

If you need more details please refer to:
http://www.drbd.org/users-guide/

Configuring MySQL for DRBD
http://dev.mysql.com/doc/refman/5.1/en/ha-drbd-install-mysql.html

Getting started:

The OS in this example is CentOS 5.5.  I added a new disk (/dev/sde) to the four disk RAID-5 and RAID-1 I was already using.   I’m only creating an 8 gig disk (vmware). You should start with a partition (LVM and or RAID) partition big enough for your data.

# uname -a
Linux db1.grennan.com 2.6.18-194.8.1.el5 #1 SMP Thu Jul 1 19:04:48 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux

# df
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/md1              24065660   2826564  19996896  13% /
/dev/md0                101018     20988     74814  22% /boot
tmpfs                   513476         0    513476   0% /dev/shm

# fdisk -l /dev/sde

Disk /dev/sde: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot      Start         End      Blocks   Id  System
/dev/sde1               1        1044     8385898+  83  Linux

DRBD:

Installation:
On machine1 and machine2 install DRBD and its kernel module.  You may need to review the packages you have available using ‘yum list | grep drbd’.  These are for CentOS 5.5.  You may also need to reboot after this step.

 # yum -y install drbd
 # yum –y install kmod-drbd82.x86_64
 # modprobe drbd  

Configuration:
On both machines edit this configuration file.  I have highlighted parts you will need to edit in red.

# vi /etc/drbd.conf

#
# please have a a look at the example configuration file in
# /usr/share/doc/drbd82/drbd.conf
#
# Our MySQL share
resource db
{
 protocol C;

 startup { wfc-timeout 0; degr-wfc-timeout 120; }
 disk { on-io-error detach; } # or panic, ...
 syncer {
 rate 6M;
 }

 on db1.grennan.com {
 device /dev/drbd1;}
 disk /dev/sde1;
 address 192.168.2.13:7789;
 meta-disk internal;
 }

 on db2.grennan.com {
 device /dev/drbd1;
 disk /dev/sde1;
 address 192.168.2.14:7789;
 meta-disk internal;
 }
}

Manage DRDB processes:

On both machines run

 # drbdadm adjust db

On machine1

 # drbdsetup /dev/drbd1 primary –o

 # service drbd start 

On machine2

 # service drbd start

On both machines(see status):

 # service drbd status

On machine1

# mkfs -j /dev/drbd1
# tune2fs -c -1 -i 0 /dev/drbd1
# mkdir /data
# mount -o rw /dev/drbd1 /data

On machine2

# mkdir /data

Test failover:
This is how you perform a manual fail over. You will use HA to do this for you in the next sections.

On primary (server1)

# umount /data
# drbdadm secondary db

On secondary (server2)

# drbdadm primary db
# service drbd status
# mount -o rw /dev/drbd1 /data
# df

Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/md1              24065660   1898696  20924764   9% /
/dev/md0                101018     14886     80916  16% /boot
tmpfs                   513472         0    513472   0% /dev/shm
/dev/drbd1             8253948    149628   7685040   2% /data

Note we never formatted (mkfs) the disk on machine2! Here it is, ready to go, DRDB has copied all the data.

MySQL:

Here are a few notes for you to think about.

• The default location for MySQL data is /var/lib/mysql.  You will be moving this to /data/mysql.

• MySQL configuration is in /etc/my.cnf.  So that changes to the configuration move with failover, you should put my.cnf in /data/mysql and create a sym-link of /etc/my.cnf to this file.

Now comes the hurdle.

• Install MySQL as you wish.
• Move your data directory to a /data/mysql

On machine1

# mkdir /data/mysql
# chown  mysql.mysql /data/mysql
# cp –prv /var/lib/mysql/* /data/mysql
Start MySQL on machine1.
Create some sample database and table. Stop MySQL. Do a manual switchover of DRBD. Start MySQL on machine2 and query for that table. It should work. But, this is of no use if you have to switchover manually every time. When you have this working you are ready to move to Heartbeat.

Here are a couple of scripts to make this easy.

drdb-secondary

# service mysql stop
# umount /data
# drbdadm secondary db
# drdb-primary:
# drbdadm primary db
# mount -o rw /dev/drbd1 /data
# service mysql start

HA:

• IMPORTANT: Heartbeat uses either Linux Services (LSB) Resource Agents or Heartbeat Resource Agents (HRA) to start and stop heartbeat resources. You will be adding MySQL (LSB), drbddisk (HRA) and IPaddr2 (HRA) are our heartbeat resources.
• Refer this page on Resource Agent
• As you are aware of it many *nix services are started using LSB Resource Agents. They are found in /etc/init.d

Installation:

On machine1 and machine2 install Heartbeat and needed utilities.  You may need to review the packages you have available using ‘yum list | grep drbd’.  These are for CentOS 5.5.  You may also need to reboot after this step.

# yum -y install gnutls*
# yum -y install ipvsadm*
# yum -y install heartbeat*
# yum -y install heartbeat.x86_64

Configuration:

Edit /etc/sysctl.conf and set net.ipv4.ip_forward = 1

# vi /etc/sysctl.conf

Controls IP packet forwarding net.ipv4.ip_forward = 1

# /sbin/chkconfig –level 2345 heartbeat on

# /sbin/chkconfig –del ldirectord

Configure HA:

You need to setup the following configuration files on both machines:

# vi /etc/ha.d/ha.cf

#/etc/ha.d/ha.cf content
debugfile /var/log/ha-debug
logfile /var/log/ha-log
logfacility local0
keepalive 2
deadtime 30
warntime 10
initdead 120
udpport 694 # If you have multiple HA setup in same network.. use different ports
bcast eth0 # Linux
auto_failback on # This will failback to machine1 after it comes back
ping 192.168.2.1 # The gateway
apiauth ipfail gid=haclient uid=hacluster
node db1.grennan.com
node db2.grennan.com  

On both machines

NOTE: Assuming 192.168.2.15 is virtual IP for your MySQL resource and mysqld is the LSB resource agent. The host name (db2) should be the secondary server’s name.
# vi /etc/ha.d haresources

# /etc/ha.d/haresources content
db2.grennan.com LVSSyncDaemonSwap::master Paddr2::192.168.2.15/24/eth0  rbddisk::db Filesystem::/dev/drbd1::/data::ext3 mysqld

# vi /etc/ha.d/authkeys

#/etc/ha.d/authkeys content
auth 2
2 sha1 BigSecretKeyks9wjwlf9gskg905snvl

Now, make your authkeys secure:

# chmod 600 /etc/ha.d/authkeys

Check your work:

On both machines, one at a time, stop MySQL and make sure MySQL does not start when the system reboots (init 6).

If it does, you may need to remove it from the init process with:

# /sbin/chkconfig –level 2345 MySQL off

Start Heartbeat.

# service heartbeat start

These commands will give you status about this LVS setup:
# /etc/ha.d/resource.d/LVSSyncDaemonSwap master status
# ip addr sh
# service heartbeat status
# df
# service mysqld status

Access your HA-MySQL server like:
# mysql –h 192.168.2.15

Shutdown machine1 to see MySQL up on machine2. ‘shutdown now’

Start machine1 to see MySQL back on machine1.

PlanetMySQL Voting: Vote UP / Vote DOWN

Sphinx As MySQL Storage Engine (SphinxSE)

SphinX is a great full-text search engine for MySQL. Installing the Sphinx daemon was straightforward as you can compile it from the source or use a .DEB/.RPM package but SphinxSE was a little bit tricky since it needed to be installed as a plugin on a running MySQL server. So if you use Debian or Centos and install your MySQL from a .deb or .rpm package this is how you do it.

PlanetMySQL Voting: Vote UP / Vote DOWN