How to

October 2, 2014

Dynamic Columns Tutorial – Part 1: Introduction

For certain situations, the static structure of tables in a relational database can be very limited. Each column is statically defined, has a pre-defined type and you can only enter a value of that type into the column.You can be creative and list multiple values in one column, but then those values are not generally easily accessed and manipulated with other functions. You have to use an API or contortions of a function like SUBSTRING() to pull out a value you want. Even then, you have to know what is contained in the column to be able to manipulate it properly.

Read More

September 21, 2014

Changing the DEFINER of MySQL stored routines etc.

The question seemed easy enough:

We've dropped a user, now we want to change the DEFINER on all database objects that currently have it set to this dropped user?

This should be possible by checking the INFORMATION_SCHEMA tables of the appropriate object types (routines, triggers, views and events) and performing an ALTER on each of them that just modifies the DEFINER but nothing else, right?

Read More

August 21, 2014

How to make MaxScale High Available with Corosync/Pacemaker

MaxScale, an open-source database-centric router for MySQL and MariaDB makes High Availability possible by hiding the complexity of backends and masking failures. MaxScale itself however is a single application running in a Linux box between the client application and the databases - so how do we make MaxScale High Available? This blog post shows how to quickly setup a Pacemaker/Corosync environment and configure MaxScale as a managed cluster resource. We will guide you step by step on how to enable basic High Availability by setting up three Linux Centos 6.5 servers with MaxScale.

Read More

August 19, 2014

Using resource monitoring to avoid user service overload

With MariaDB, as with any service, you must monitor user resource usage to ensure optimal performance. MariaDB provides detailed statistics for resource usage on per-user basis that you can use for database service monitoring and optimization. User statistics are especially useful in shared environments to prevent a single gluttonous user from causing server-wide performance deterioration. If you detect abnormal use, you can apply fine-grained limits, as we'll see.

Read More

August 18, 2014

Building MaxScale from source with Vagrant and Puppet

MaxScale for MariaDB and MySQL hides the complexity of database scaling from the application. To streamline building MaxScale from source and running the test suite, you can automate the process with some useful tools to meet your needs. I have created a Vagrant / Puppet setup I'd like to share with you.

Read More

August 14, 2014

Script to manage MaxScale

MaxScale 1.0 from SkySQL is now in Beta and there are some cool features in it, I guess some adventurous people has already put it into production. There are still some rough edges and stuff to be fixed, but it is clearly close to GA. One thing missing though are something to manage starting and stopping MaxScale in a somewhat controlled way, which is what this blog is all about.

Read More

August 11, 2014

Putting Virtual Columns to good use

We all know that in general it's a bad idea to have columns values contain too much "hidden" information, and in particular for primary keys, this is a big no-no, although I know that not everybody agrees here. In some cases though, there is data that at it's heart contains several aspects and we just cannot avoid this, the prime example being data and time values. What I mean here is that a single datetime value has aspects that aren't always obvious from the datetime value itself. Examples include leap year information and weekday.

Read More