One more DRBD tutorial, this time I will describe howto setup MySQL with DRBD (Distributed Replicated Block Device). Purpose This document describes how to to setup a failover system with MySQL and DRBD (Distributed Replicated Block Device). Introduction In this tutorial we will setup two Debian Linux nodes with a DRBD volume. MySQL will be [...]
The following quirky dynamic SQL will scan each index of each table so that they’re loaded into the key_buffer (MyISAM) or innodb_buffer_pool (InnoDB). If you also use the PBXT engine which does have a row cache but no clustered primary key, you could also incorporate some full table scans.
To make mysqld execute this on startup, create /var/lib/mysql/initfile.sql and make it be owned by mysql:mysql
SET SESSION group_concat_max_len=100*1024*1024; SELECT GROUP_CONCAT(CONCAT('SELECT COUNT(`',column_name,'`) FROM `',table_schema,'`.`',table_name,'` FORCE INDEX (`',index_name,'`)') SEPARATOR ' UNION ALL ') INTO @sql FROM information_schema.statistics WHERE table_schema NOT IN ('information_schema','mysql') AND seq_in_index = 1; PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET SESSION group_concat_max_len=@@group_concat_max_len;
and in my.cnf add a line in the [mysqld] block
init-file = …[Read more]
This is a Request for Input. Dual MySQL masters with MMM in a single datacentre are in common use, and other setups like DRBD and of course VM/SAN based failover solutions are conceptually straightforward also. Thus, achieving various forms of resilience within a single data-centre is doable and not costly.
Doing the same across multiple (let’s for simplicity sake limit it to two) datacentres is another matter. MySQL replication works well across longer links, and it can use MySQL’s in-built SSL or tools like stunnel. Of course it needs to be kept an eye on, as usual, but since it’s asynchronous the latency between the datacentres is not a big issue (apart from the fact that the second server gets up-to-date a little bit later).
But as those who have tried will know, having a client (application server) connection to a MySQL instance in a remote data-centre is a whole other matter, latency becomes a big issue and is generally …[Read more]
The goal is to have only one entry in the Enterprise Monitor Dashboard that shows the status of the MySQL instance, no matter on which physical server in runs. There are two ways to achieve this:
- You can install the agent on both physical nodes
- You can install the agent on a shared storage.
In either case you have to make sure, that only one agent runs at
a time. You have to integrate the agent into your cluster
framework. I will not describe how this works, as it is highly
dependant on your cluster framework.
The following description assumes, that you will install the agent on both physical nodes.
- Install the agent but DO NOT START the agent yet.
- Edit the
In the [mysql-proxy] section add the following line:
To install the MEM agent in a way that both physical servers are listed in the MEM dashboard, you have to install the agent on both physical nodes. But: Do not start the agent after the installation! There are three different IDs in MEM: agent-uuid, mysql-uuid and host-id. Usually they are generated automatically and you will never notice these IDs. For more information about the meaning of the different IDs look at this very good explanation from Jonathon Coombes. The agent stores the uuid and the hostid in a MySQL table called mysql.inventory. After a failover the other agent on the new node will notice "wrong" hostid and uuid entries in the inventory table. The agent will stop and ask you to TRUNCATE mysql.inventory. But with this procedure MEM creates a new instance, so all old data is lost. Not good for a failover environment. So in case of a …[Read more]
This Thursday (October 22nd, 13:00 UTC), Walter Heck (of Open Query) will present Dual Master Setups With MMM. MMM (Multi-Master Replication Manager for MySQL) is a set of flexible scripts to perform monitoring/failover and management of MySQL master-master replication configurations (with only one node writable at any time). Session slides (PDF).
The toolset also has the ability to read balance standard
master/slave configurations with any number of slaves, so you can
use it to move virtual IP addresses around a group of servers
depending on whether they are behind in replication. For
information, see mysql-mmm.org.
For MySQL University sessions you point your browser …[Read more]
This is a “dogfood” type story (see below for explanation of the term)… Open Query has ideas on resilient architecture which it teaches (training) and recommends (consulting, support) to clients and the general public (blog, conferences, user group talks). Like many other businesses, when we first started we set up our infrastructure quickly and on the cheap, and it’s grown since. That’s how things grow naturally, and is as always a trade-off between keeping your business running and developing while also improving infrastructure (business processes and technical).
Quite a few months ago we also started investing (mostly time) in the technical infrastructure, and slowly moving the various systems across to new servers and splitting things up along the way. Around the same time, the main webserver frequently became unresponsive. I’ll spare you the details, we know what the problem was and it was predictable, but since it wasn’t …[Read more]
I’ve been thinking recently about the failure scenarios of MySQL replication clusters, such as master-master pairs or master-master-with-slaves. There are a few tools that are designed to help manage failover and load balancing in such clusters, by moving virtual IP addresses around. The ones I’m familiar with don’t always do the right thing when an irregularity is detected. I’ve been debating what the best way to do replication clustering with automatic failover really is.
I’d like to hear your thoughts on the following question: what types of scenarios require what kind of response from such a tool?
I can think of a number of failures. Let me give just a few simple examples in a master-master pair:
- Problem: Query overload on the writable master makes mysqld unresponsive
- Do nothing. Moving the queries to another server will cause cascading failures.
- Problem: The …
We received a number of followup questions from our readers,
requesting more technical background information. For example,
Callaghan was wondering about the following:
- How is failure detection done?
- How is promotion of a slave to the master done after failure detection?
- How are other slaves failed to the new master?
I asked Detlef to elaborate some more on the technical details of this solution. Here's his very exhaustive reply, thank you very much, Detlef!
I would also like to point out that he'll be …[Read more]
Last week I helped a customer setup a JBoss application against MySQL Cluster. It turns out it is not immediately obvious how you should setup our JDBC connector to do loadbalancing and failover. For instance, setting the connector up for an Master-Slave setup (with MySQL Enterprise) is well documented, but not doing the same with MySQL Cluster.
It's not really properly documented in the manual part, but I found in the changelogs, and confirmed on IRC that to do load-balancing across the SQL nodes in MySQL Cluster, you would use a different JDBC connection string with the "loadbalance" keyword added...
That does indeed …[Read more]