December 23, 2013 By Severalnines

This post is a continuation of our previous post on Online Schema Upgrade in Galera using TOI method. We will now show you how to perform a schema upgrade using the Rolling Schema Upgrade (RSU) method. This requires that the new schema is backward compatible with the old schema, so it requires a bit more planning than the TOI method. In some cases, you might need to turn off any connections to the target node. 

If you are using non-uniform hardware across your Galera cluster, RSU might be preferable as the more predictable process. RSU does not slow down your cluster.

As described in details in the previous …

December 19, 2013 By Severalnines

Database clusters are pretty sophisticated distributed systems with complex dependencies between nodes. The failure of a node will generally impact the overall cluster, as the remaining nodes need to reconfigure themselves to continue to operate without the failed node. Since re-introducing a node will also affect the existing cluster, the timing could therefore be dependent on the state of the other nodes in the cluster. Repair and restarts often needs to be performed in a particular order in compliance with the redundancy model of the cluster so as not to jeopardize the normal functioning of existing nodes.

Webinar: Repair and Recovery for your MySQL, MariaDB and MongoDB/TokuMX clusters

…

In short, during an optimization or a MySQL query involving a lot of replace(replace(replace…))) I decided to compile PREG as lib_mysqludf_preg from UDF Repository for MySQL in a view to use PCRE functions directly in MySQL.

Too bad MySQL still cant not use functions for index creation, but this is not the theme of this post.

So, fist install MariaDB 10.0.6:
set the MariaDB 10.0.6 repo, I used the ftp mirror at ftp.ulak.net.tr

root@seik-centos-01:[Wed Dec 18 23:55:12][/tmp]$ cat /etc/yum.repos.d/MariaDB.repo 
# MariaDB 10.0 CentOS repository list - created 2013-12-18 18:07 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
#baseurl = http://yum.mariadb.org/10.0/centos6-amd64
baseurl = ftp://ftp.ulak.net.tr/pub/MariaDB/mariadb-10.0.6/yum/centos6-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1 …

In my last post, a tale of two MySQL upgrades, a few folks asked if I would outline the process we used to upgrade, and what kind of downtime we had.

Well, the processes were different for each upgrade, so I will tackle them in separate blog posts. The first step was to upgrade all our MySQL 5.1 machines to MariaDB 5.5. As mentioned in the previous post, MariaDBs superior performance for subqueries is why we switched and we switched back to MySQL for 5.6 to take full advantage of the performance_schema.

It is not difficult to blog about our procedure, as we have documentation on each process. My first tip would be to do that in your own environment. This also enables other folks to help, even if they are sysadmins and not normally DBAs. You may notice the steps contain items that might be obvious to someone who has done maintenance before we try to …

December 10, 2013 By Severalnines

As a follow-up to the Webinar on Zero Downtime Schema Changes in Galera Cluster, we’ll now walk you through the detailed steps on how to update your schema. The two methods (TOI and RSU) have both their pros and cons, and given parameters like table size, indexes, key_buffer_size, disk speed, etc., it is possible to estimate the time taken for the schema to be upgraded. Also, please note that a schema change is non-transactional so it would not be possible to rollback the DDL if it fails midway. Therefore, it is always recommended to test the schema changes and ensure you have recoverable backups before performing this on your production clusters. 

This post examines the way DDL changes are propagated in Galera, and outlines the steps to upgrade the …

I was pleased to see Morgan’s announcement about a fix to an old problem of mine. In March 2012 I complained about MySQL verbosity during installation.

In MySQL 5.7.3, this behavior was changed. While the default is still as loud as it can, you can now add an option (log_error_verbosity) to send only errors to STDERR, which allows you to hide the output of mysql_install_db, and still get the errors, if they occur.

Well done!

However, the same obnoxious verbosity is also in MariaDB 10.0.x. Since I discussed this specific bug with a few MariaDB developers early in 2012, I was disappointed to see this same output when running mysql_install_db with MariaDB. Here’s the same appeal: MariaDB …

This is both hilarious and sad. The new MySQL 5.7 milestone release presents a new feature — replication filters are now dynamic. This is a great and long awaited feature, no doubt about it.

In short, for years MySQL slaves could filter the incoming stream of replication events based on the database or table name these events were applicable to. These filters were configured using the my.cnf file (or command-line), in particular with the following variables:

replicate_do_db
 replicate_ignore_db
 replicate_do_table
 replicate_ignore_table
 replicate_wild_do_table
 replicate_wild_ignore_table

Naturally, users wanted to be able to change the values of these options without having to restart the server. And eventually Davi Arnaut implemented it. MariaDB got it as a contribution back in version …

MariaDB CONNECT Storage Engine allows to access heterogeneous datasourses. This includes various file formats. But this also includes ODBC accessible datasources The CONNECT Storage Engine ODBC table type allows to access SQLite, Excel, SQL Server or Oracle databases. Some nice features of the ODBC CONNECT table type are: - Auto discovery of table structure. This [...]

December 5, 2013 By Severalnines

Thanks to everyone who attended yesterday’s webinar; if you missed the sessions or would like to watch the webinar again & browse through the slides, they are now available online.

Thanks again to our speaker, Seppo Jaakola from Codership, the creators of Galera Cluster, for this in-depth talk on Galera Cluster Best Practices - Zero Downtime Schema Changes.

Webinar topics covered

• How to perform Zero Downtime Schema Changes
• 2 main methods: TOI and RSU
• Total Order Isolation: predictability and consistency
• Rolling Schema Upgrades
• pt-online-schema-change
…

December 4, 2013 By Severalnines

As OpenStack deployments mature from evaluation/development to production environments supporting apps and services, high-availability becomes a key requirement. In a previous post, we showed you how to cluster the database backend - which is central to the operation of OpenStack. In that setup, you would have two controllers, while placing a 3-node Galera cluster on separate hosts. Now, it can be quite a leap to go from one VM with all services running on it, to a fully distributed setup with 5 VMs. The good news is that you can have a highly available setup starting with just 3 VMs.

In this post, we are going to show you how to cluster OpenStack Havana in a minimal node setup with 2 controllers and one compute node. Our controllers will be running all OpenStack services, as well as clustered RabbitMQ …