This is my takeaway from this years MySQL conference in San Francisco.

The community believes Oracle has been a good steward of MySQL and delivered some great code in 5.6. Studies from 451 Research show MySQL is on the decline and a lot of this has to do with the ownership my Oracle and growth of no-SQL.

Big data, Cloud, hadoop and MongoDB are still the on the lips of the “JSON Generation”.  Marty Weiner and Yashh Nelapati (Pinterest) will tell you to start in the Cloud (Amazon) and grow until

  [Read more...]

I’m sitting here in the lounge at SFO thinking back on the last week, the majority of which has been spent meeting my new workmates and attending the Percona MySQL conference.

For me it has been as much of a family reunion as it has been about seeing the wonderful things going on in MySQL.

Having joined Continuent last month after an ‘absence’ in NoSQL land of almost 2.5 years, joining the MySQL community again just felt like coming home after a long absence. And that’s no bad thing. On a very personal level it was great to see so many of my old friends, many of whom were not only pleased to see me, but pleased to see me working back in the MySQL fold. Evidently many people think this is where I belong.

What was great to see is that the MySQL community is alive and

  [Read more...]

This is the tenth year for the MySQL developer conference and my third.

I’ll be posting some news about iDB From ScaleArc. Oracle is back this year. Everyone is fighting with developers wanting to move away from MySQL. And there is lots of talk about 5.6 and working in virtual environments.

 

 

Tweet

It’s about time for this year’s MySQL (Percona) conference in San Francisco.  I haven’t written anything in some time.  So, I thought I’d write something we can talk about should you meet me.

You need to make a slave of your MySQL server.

What if, you’re bonus was tied to database up time or maybe the systems group will not grant you any down time?  You’re ONE Master DB server is so big, coping tables takes hours. Even an ‘scp’ sync takes too long.  99.999% uptime is hard to achieve.

Percona and their tools are your friend… mostly. Xtrabackup will create a ready to use copy of all your databases, document your my.cnf and your current Master Position…  unless it doesn’t.

Xtrabackup uses it’s own XtraDB engine to copy the

  [Read more...]

I’ve just completed my first month here at Continuent, strangely back into the MySQL ecosystem which I have been working in for some time before I joined CouchOne, and then Couchbase, two and half years ago. Making the move back to MySQL is both an experience, and somehow, comfortable…

Continuent produce technology that makes for easier replication between MySQL servers and, more importantly, more flexible solutions when you need to scale out by providing connector and management functionality for your MySQL cluster. That means that you can easily backup, add slaves, and create complex replication scenarios such as multi-master, and even multiple-site, multiple-master topologies. This functionality is split over two products, Continuent Tungsten, which is the cluster management product, and the open source Tungsten Replicator, which provides the basic replication

  [Read more...]

One of the most rewarding things you can do is help someone get a great job or hire a great person for the position they need to fill. I have traveled a lot, written books, done a bunch of consulting, and spoken widely on MySQL, other databases, open source, and so forth. I’ve gotten to know a lot of people, some I’d call good friends, and many of them are leading large organizations. I think this is both a privilege and a serious responsibility.

It’s a privilege because I can ask some of these people for help or introductions or advice sometimes. It’s a responsibility because I need to be ready to do something for them, too. In many cases it’s a pay-it-forward kind of readiness.

Many, many people contact me looking for people to hire. I keep a list. When someone tells me they are on the job market, I try to

  [Read more...]

I was discussing how to avoid surprising users and someone pointed out that what seems intuitive and rational to one person is often complete insanity for others. The mental gap between a developer and a user can often be a chasm far too wide to cross. Of all the bug reports I’ve filed against MySQL, here is my all-time favorite:

select * from t where a >= 1.0order by a;

Does not cause an error. I believe it should, because there should be a whitespace before ORDER BY.

Similar syntax errors such as “select 1e0from dual” were also accepted as valid SQL. Much soul-searching later, the official reply from MySQL’s development team:

The server behaves properly here:
- “1″

  [Read more...]

With the Big Data craze that’s sweeping the world of technology right now, I often ask myself whether we’re deficit-spending, so to speak, with our data consumption habits. I’ve seen repeated examples of being unwilling to get rid of data, even though it’s unused and nobody can think of a future use for it. At the same time, much Big Data processing I’ve seen is brute-force and costly: hitting a not-very-valuable nut with a giant, expensive sledgehammer. I think the combination of these two problems represents a giant opportunity, and I’m going to call the solution Smart Data for lack of a better word.

What’s the problem, in 25 words or less? I think it’s that we’re collecting a lot of data simply because we can. Not because we know of any good use for it, but just because it’s

  [Read more...]

For many years I have recommended startup companies install and work with CentOS until they need and can afford RedHat Enterprise.  The idea, because the two are binary compatible, the applications the company uses and develops will migrate between the two without changes.  I have even said you can do an inplace migration between them.

I’ve been challenged to prove an inplace migration is possible.  This is the results.

There are a couple packages that define a CentOS/Redhat system.

• The centos/redhat-release package

This package hold the release files, product GPG keyes and repository config files.

• redhat-logos

  [Read more...]

Almost every business process or decision includes a WHERE!

Like IP addresses encode the destination of a domain name, with a geographic information system (GIS) you can encode and analyze the location data allowing you to reveal relationships, patterns, and trends.

Weather Decision Technologies

Show embedded map in full-screen mode

The world of GIS is confusing. Here is my understanding of how the peaces go together and how you can get started.

We describe locations on the earth in terms of

  [Read more...]

First you need to be able to resolve a few dependencies so you’ll need the epel repository.

$ wget http://mirror.chpc.utah.edu/pub/epel/6/i386/epel-release-6-7.noarch.rpm

Now you can add the PostgreSQL repository to your system. Use the pgdg-centos92 package for CentOS and the pgdg-redhat92 package for Redhat.

$ wget http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-centos92-9.2-5.noarch.rpm
$ wget http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-redhat92-9.2-7.noarch.rpm

NOTE: Redhat User’s only - I found Redhat repositories (6 and epel) do supply gdal-1.7 but it is compiled to be used with the UNIX ‘libodbc’ package that are not provided.  (I have no idea why.)  Because of this you will need to download the gdal-1.8 CentOS

  [Read more...]

Stop coding and start scaling!

At the April MySQL conference in San Francisco (2012) I discovered iDB by ScaleArc and was excited by what it can do. After three months of testing and working with ScaleArc I purchased it.

What I do

The company I work for (WDT Inc.) supplies weather data. From a database point of view, we take in data feeds from many sources with “Ingest” applications. All are processed (forecast applications) and provided to many “Out flow” applications. Current conditions are processed into forecasts data, RADAR images, lightning and alerts are mapped into images to be used by governments and individual customers (

  [Read more...]

Why is there no index for CSV files?  Indexes are very simple.

If the first column of your CSV file is in sorted order you can do a binary search to find your data.  But what if you need to find data in the second or third column?

If you have a separate index file pointing to the first byte of each line you could seek to that position in the CSV file and get your data.  Given a file with only your needed column and the byte offset and length of the line you can search the index to find the pointer to the position in the CSV file.

SAMPLE DATA
“record”,”title”,”data”
1,”test1”,”data1”
2,”test3”,”data3”
3,”test2”,”data2”

SAMPLE INDEX
“test1”,24,17
“test2”,58,17
“test3”,41,17

  [Read more...]

If you don’t know what HandlerSocket is read Yoshinori Matsunobu’s blog post.

Because I’m starting with a minimum install of CentOS 6.2,  You’ll need to make sure you have a few utilities and development tools installed:

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

Installing the MySQL source

You’ll also need the MySQL source and a few more supporting packages.

wget

  [Read more...]

We have a new toy to play with. MemSQL is a “Lock Free”, Just In TIme (GIJ) SQL compler (C++) that is MySQL client compatible. It was developed by two former Facebook developers, Eric Frenkiel and Nikita Shamgunov,  and is said to be the world’s fastest.

I gave it a 30min review.  The install was incredibly simple. I use mysqldump to dump a WordPress tables directly into MemSQL and all when well until I  tried to use the website.  MemSQL is NOT a MySQL drop in replacement. But, they don’t say it is.

MemSQL is ACID compliment and might fit well in a small application needing high read write transactions. (Think stock trading.) There is no descriptions

  [Read more...]

A deep dive into the code

MySQL security is like an onion, it has many layers. MySQL check the user source location (IP) and password. MySQL can also check a user’s SSL Certificate credentials. MySQL may or may not give a user access to a set of databases, tables, functions or views based on the user’s Access Control List.

I’ve been reviewing ScaleArc’s iDB for some time.  The use of a proxy complicates the authentication process even more.  This has led to me reviewing all of MySQL Security.  I’ll blog about what I’ve found as I dive into the code.

The Login Handshake

In version 4.0 and before:

• The server sends a random string to the client, in scramble_buff.
• The client encrypts the

  [Read more...]

Percona you know I love you.  You have the largest brains working with MySQL. Your toolkit (formerly Maatkit and Aspersa) is a real gift to the MySQL community. But stop teasing us with webinars about what it can do and show us how to use it.

The Percona ToolKit needs a cookbook. The documentation is reference only, very thin and total void of useful examples. You’re beginning to frustrate the masses.

Here is what’s happening.  My MySQL server crashes or stalls ever few months.  I searched the Goog and find PT-Stalk :

“watches for a trigger condition to become true, and then collects data to help in diagnosing problems. It is designed to run as a daemon with root privileges, so that you can diagnose intermittent problems that you cannot observe directly. You can also use it to  [Read more...]

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

  [Read more...]

Un-maned Failover Operations

I have video of two MySQL servers (one master, on slave) failing over from Master to Slave.  The Master becomes the Slave and vise-verse with the slave becoming read-only.  Next some load testing and then I’ll post how I did it with FREE software.

Click here to view the video on YouTube.

Tweet

I liked Shlomi Noach post on “IS TRUE and IS FALSE“.  This kind of logic create bugs.

What will this return?

select if((1 = true) and ( 1 is TRUE) and (NULL is not TRUE), TRUE, FALSE) as answer;

You should know,

NULL is never TRUE or FALSE but

NULL is always NOT TRUE or NOT FALSE.

NULL is a little like a vacuum.  Ancient Greek philosophers did not like to admit the existence of a vacuum, asking themselves “how can ‘nothing’ be something?”.    Does GOD exist in NULL?

If  select “” is NOT NULL; is TRUE what is

  [Read more...]

Searching for the Holly GrailFor months I have been writing and rewriting an blog post called “The Full Monty”. It’s a kind of how to build complete production system from scratch. I’m still not done. Maybe I’m fullish thinking I can build a one type fits all database server. But that’s not the problem. I’ve found myself chasing the Holly Grail of HA and replication.I’ve build many virtual clusters with all the HA / replication package I could find. I’ve tried DRBD, Heartbeat,  Multi  [Read more...]

MySQL doesn’t often crash, but, if you use MySQL on a production system you should have High Availability.

Maybe you’ve not heard of MHA.  Yoshinori Matsunobu only released it in July of this year (2011).  I’ve been reviewing it and I think you should too. There are a lot of people chasing after this “holy grail” and most systems are complex and / or hard to recover when they fail. MHA simple and easy to use.

MHA is a fail over tool. It’s designed to fail a master and promote a slave to a new master. (This is simply to say but you

  [Read more...]

In last weeks OurSQL postcast (episode 65)  Sheeri,  Sarah and Jerry talked about making MySQL safe with SSL.  Encryption always seems to be such a confusing subject. I think every database should be using SSL by default.  So, I was wondering just how easily SSL could be setup.

Most existing examples I found  setup SSL authentication and encryption.  If you are handling  PCI DSS or HIPAA data you must encrypt the data on the wire, but passwords are all you need to authenticate the application to

  [Read more...]

I ask Sheeri of the OurSQL podcast to publish her list of MySQL websites and podcasts she follows. I thought it was only fair I do the same.  If you don’t watch these all the time your missing out. I read most of these every week.

If I missed one please leave a comment.

Planet MySQL – http://planet.mysql.com/  - This is the big community list of MySQL info.  Not all my list is found here.
OurMySQL - http://www.oursqlcast.com/ – This a THE podcast for MySQL.  New shows each week.
Sergey Petrunia’s blog – http://s.petrunia.net/blog/
clever elephant – http://blog.cleverelephant.ca/

  [Read more...]

This is more boring parts but you must lay a good foundation.

MySQL Setup:

All the resources must be in place before you configure Pacemaker. The default location for MySQL data is /var/lib/mysql.  You will be moving this to the DRBD file system in /data/mysql.   The default MySQL configuration is in /etc/my.cnf.  I move it to /data/mysql so updates will move with system fail overs.   To keep life simple I use sym-links to point from /var/lib/mysql and /etc/my.cnf to their new locations.
Install MySQL of your choise on DB1, DB2 and DB3.

# rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
# yum -y install Percona-Server-server-51.x86_64 Percona-Server-client-51.x86_64 \
 Percona-Server-shared-51.x86_64

  [Read more...]

I’ve never objected to someone making money from MySQL. I’ve only expressed disappointment that they weren’t doing it effectively enough. As I have predicted many times, Oracle is good at this. Oracle is the number one reason I didn’t start a new career in some other database a few years ago. Oracle is making MySQL more successful not only for Oracle, but also for the users, the community, and the competition.

I am glad that Oracle is offering more pay-only extensions to the server in a way that creates opportunities for others to do the same, and I look forward to even more of them in the future.

Further Reading:

  [Read more...]

Installing DRDB in CentOS 5.6.

In Part 1 I when through the process of preparing a number of CentOS 5.6 servers. Now make the services they’ll preform more stable.

High Availability (HA)

I’ll be presenting two ways to provide redundant data and high available services. First, Pacemaker – with DRDB will duplicate your data at the disk partition level and watch for failures. Should the hardware failure, Pacemaker will take all the needed steps to start MySQL on the Hot Stand By (HSB). This is not perfect. Should someone run ‘rm *’ or drop a database DRDB will duplicate

  [Read more...]

It’s notoriously hard to measure the usage of open-source software. Software that’s open-source or free can be redistributed far and wide, so the original creators have no idea how many times it’s installed, deployed, or distributed. As a proxy, we often use downloads, but that’s woefully inadequate.

I’ve recently begun trying to figure out how many job openings are mentioning various open-source projects. I think that this might be a better metric because it’s driven by the end result (usage), rather than intermediate processes (downloads, etc). I think that it’s likely that usage and demand for skilled people is somewhat realistically related.

To be more concrete, I’ve been watching RSS feeds from job posting aggregators for several alternative versions of MySQL: Percona Server, MariaDB, and Drizzle.

  [Read more...]

People keep asking me “what is going to happen to MySQL now that Oracle has screwed MySQL?” I bluntly disagree that any such thing has happened. This blog post is just my personal view and does not reflect my employer’s opinion, but Oracle might have saved MySQL from what I can see. There is no evidence that supports the hysterical doomsday theories. (Witness MySQL 5.5, probably the best MySQL release in history. Not exactly what I’d call “screwing.”)

I believe that a product with such a large, diverse, and important market presence needs a variety of companies involved with it in many different ways. One of the absolutely key things is a company to make money from it. MySQL needs Oracle, because no one else involved is both capable and trying to make MySQL, the product, a large-scale commercial success. It looks like

  [Read more...]

Opps… What happens when you press the post button instead of the preview button.  You publish a story before it’s time.

I’m working on compiling a complete list of MySQL storage engines. I didn’t intend to publish this so soon.  Maybe you can tell me what I’m missing.

Here is what I’ve found so far.  This is mostly an update of an older work I found.  Now that it’s posted I work hard to complete it.

Provider

Engine

State

Platforms

License

Release

Features





Toru Maesaka

BlitzDB   SE



GPL v2


Highly efficient general purpose   use DB. Non-transactional competition to MyISAM and MEMORY engines. Developed   with

  [Read more...]