Installing MySQL on CentOS 6.2 with PaceMaker, MHA and more

When I work with High Availability software, I’m reminded of the maze in the original computer adventure game “You are Lost in a maze of twisty-turny passages all alike…”.

If you search the web for HA programs you will find many well maintained projects all related that refer each other. The goal of this document is to give you with a step by step guide to a production worthy MySQL system. It should provide at least 99.999% access to your data and be able to scale read requests as you grow.

I have chosen these programs and utilities because they are free (as in beer) and each has enterprise support available. (When you make the money to pay for it.) If you start with this MySQL platform you will avoid many common problems. Just write your application to read and write data from different servers.

Here is what we’ll be …

It’s very handy to have FT search out of the box, but there are several drawbacks attached. Problem is that MyISAM Full-text search is not designed to handle big amounts of text data. If you plan to index more than 1M documents you will probably need to take a look on the external search system like Lucene or Sphinx. For the usual LAMP-based service I personally would prefer to use Sphinx as it provides simple transition from MySQL FT and easy to integrate into any application (Sphinx could be queried via native APIs or via MySQL protocol).

Say we have table called <my_table> with `title` and `content` text fields. In MySQL you have to fire query like this:

SELECT * FROM <my_table> WHERE MATCH(`title`,`content`) AGAINST ('I love Sphinx');

Let’s see how could we do the same …

So you have inherited a MySQL server and no one know what applications are use what databases.   Space is every tight and every night transactions logs almost fill your hard disk.  The server was once used as a replication server and it seems every database the company uses is duplicated here.

You could do a full dump, drop everything and wait for the requests to restore. (This might be OK on a test database.)

We could ask MySQL for all the databases that have tables that have been updated in the last 30 days.

$ mysql information_schema -e  \
"select DISTINCT TABLE_SCHEMA as NAME from TABLES
  WHERE UPDATE_TIME IS NOT NULL and UPDATE_TIME > NOW() - INTERVAL 30 DAY"  \
> Databases

But this only gives a list of databases that have changed.  What about tables that are only read from?

Bin-Logs don’t help because they too only have updates.  What we really need is a long snapshot of the …

PART 1 – This will be a multi part post.

THIS DOCUMENT IS BEING UPDATED – PLEASE WATCH FOR CHANGES!

After years of supporting MySQL, for many different companies, I’ve seen this story played out again and again.
The company:

• chooses a Database Management System (MySQL)
• installs the DBMS on a computer with other processes
• writes many programs to access the data (Without concern on how the queries are written.)
• moves DBMS to a computer of its own and writes more programs
• buy bigger computer to run the DBMS and writes more programs
• tires of DBMS response times and outages caused by developers working on production systems and hires a Database Administrator to fix the mess

This is a step by step description of  how I build a highly available, production MySQL servers. Like most things it life, these problems …

I love short and consist 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 …

This document was updated and tested for CentOS 6.0

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 …

The title says it all. If you don’t know what HandlerSocket is or why you would want to use it you need to reads Yoshinori Matsunobu’s blog post.

Lets get started.

 login as root

Make sure you have these packages installed:

yum install git perl libtool gcc make openssl-devel gcc-c++ perl-DBI perl-DBD-MySQL.x86_64

If you haven’t already, install MariaDB and it’s source do that first and make sure it works. Look here http://askmonty.org/wiki/MariaDB:Download#Packages for the packages.

I’m using CentOS 5.5 x64 so I used:

wget http://ftp.osuosl.org/pub/mariadb/mariadb-5.2.4/kvm-rpm-centos5-amd64/rpms/MariaDB-client-5.2.4-94.el5.x86_64.rpm
 wget http://ftp.osuosl.org/pub/mariadb/mariadb-5.2.4/kvm-rpm-centos5-amd64/rpms/MariaDB-server-5.2.4-94.el5.x86_64.rpm …

Just in case you don't know resizing Innodb log file is not just simple as changing the value of innodb_log_file_size in the MySQL configuration file. But its even more simpler. Follow the steps in this post and you will be done with the resizing.

Often we make some sketches of the database we plan to create on a blackboard or a sheet of paper before we actually design its structure on computer. After that we discuss the entities we’ve got, normalize them and repeat these actions several times. As a result we get a completely approved database structure in the form of an image file in the project documentation.

Question:
How to create a database diagram basing on the image available?

Let’s try to do this using Database Designer of dbForge Studio for MySQL.

Let us suppose that you have a sketch of the future database:

Database Structure

To place this picture onto an empty diagram you should create an empty document, for …

In my business (weather) we use lots map based (Geo) information.  Almost every table has latitude and longitude. Working with this kind of data can be exciting and frustrating.  This should give you a quick start into GIS with MySQL.

“A geographic information system (GIS), or geographical information system, is any system that captures, stores, analyzes, manages, and presents data that are linked to location. In the simplest terms, GIS is the merging of cartography, statistical analysis, and database technology. GIS systems are used in cartography, remote sensing, land surveying, …