Many early startups that use a single server for all services or a single database server for their website talk about how they would like to achieve higher availability with MySQL. This is not possible without at least two database servers. Using MySQL replication you can then support higher availability in several varying capacities. An additional MySQL database server can satisfy several infrastructure needs including:

• A primary hot backup
• A datasource for performing backups
• Read scalability infrastructure
• A reporting server
• A benchmarking server
• A fail-over/fail back master environment

MySQL replication is very easy to configure and deploy, a task that takes < 30 minutes for an experienced MySQL DBA. However, altering your backup and recovery strategy, modifying your application to support read/write splitting or implementing a fail-over/fail back …

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 [...]

Working with customer, I faced pretty nasty bug, which is actually not rare situation , but in this particular there are some lessons I would like to share.

The case is pretty much described in bug 55981, or
in pastebin.

Everything below is related to InnoDB-plugin/XtraDB, but not to regular InnoDB ( i.e in MySQL 5.0)

In short, if you use big BLOBS ( TEXT, MEDIUMBLOB, etc) (that allocated in external segment in InnoDB), you can get your database in trash state just executing update on row with blob and rolling back transaction twice ( on the same row)

The keywords that diagnose you hit this bug is

InnoDB: Serious error! InnoDB is trying to free page N
InnoDB: though it is already marked as free in the tablespace!
InnoDB: The tablespace free …

This post is the fifth of a series that started here.

From the previous posts of this series, we now have an instance restart script that can restart the database node in case of failure and automatically reconfigure Pacemaker and the other servers that needs to access the MySQL server. What we will cover in this post is the monitoring script that run on the MySQL node.

At its smallest expression, the instance monitoring script is a simple empty loop that will run forever like:

PLAIN TEXT CODE:

1. #!/bin/bash
2.  
3. while [ 1 ]
4. do
5.     sleep 60
6. done

Although fully functional, this monitoring script is rather weak could be …

OSSCube’s internationally renowned team of MySQL Experts - Sonali Minocha and Rakesh Kumar, lead a successful workshop on setting up MySQL High Availability for your MySQL Database servers. Scheduled on the second day of the conference, September 20th, the workshop was jam packed with enthusiastic participants.

Sonali, Asia’s first MySQL certified DBA and an internationally renowned MySQL expert, …

This post is the fourth of a series that started here.

From the previous of this series, we now have resources configured but instead of starting MySQL, Pacemaker invokes a script to start (or restart) the EC2 instance running MySQL. This blog post describes the instance restart script. Remember, I am more a DBA than a script writer so it might not be written in the most optimal way.

First, let's recap what's the script has to perform (the full script is given below).

1. Kill the MySQL EC2 instance if running
2. Make sure the MySQL EC2 instance is stopped
3. Prepare the user-data script for the new MySQL EC2 instance
4. Launch the new MySQL instance
5. Make sure it is running
6. Reconfigure local heartbeat
7. Broadcast the new MySQL …

I was asked to write a blog post about MySQL High Availability at Yahoo, particularly for writes. Our standard practice is not particularly high-tech, but we've been using it for over 4 years now and it has become a company-wide standard with a few exceptions.   Let me start by saying that at Yahoo! we consider a datacenter as a Single Point of Failure (SPoF). We build and manage many of our own datacenters, and we still don't assume they are invulnerable. How many people can attest to the fact that however to configure your racks, how many redundant switches, power supplies, drives, etc. you buy, if your leased datacenter has power or network issues, you are at their mercy.  

read more

Multi-master MySQL, with the MMM management software brings a whole host of new features, and manageability to your MySQL deployments.   Run backups, alter tables, perform upgrades all without slowing down your production users.

Read more at Database Journal – Using Multi-master MySQL To Get A Leg Up On Database Performance

A short post marks Pythian’s 195th edition of Log Buffer, a blog of blogs encapsulating what’s going on in the world of database administration.

Remember if you find a link or interesting blog post that you think Log Buffer should mention, send a note to the editor at Log Buffer and be sure to include the link, and a short note on why you think that others will want to read it too.

Now on to Log Buffer #195. Alex Gorbachev starts us off with his suggested readings and funnily enough, …

This post is the third of a series that started here.

From the previous of this series, we now have two working EC2 instances that are EBS based. The first instance is the monitor, usually an m1.small type instance and the second instance is hamysql, a large instance type. So far, we have configured Heartbeat for communication between the nodes. Now, it is time to configure the HA resources in Pacemaker. Here are the requirements for the HA resources:

On Monitor

• Kill the other instance
• Free the share resources
• Start a new hamysql instance
• Reconfigure the clients
• Give away the resources

On hamysql

• Run MySQL
• Monitor MySQL
• Get out …