Read the original article at What Ops doesn’t tell you about your MySQL Database

MySQL is a very scalable platform which has proven robust even in the most dense and complex data environments. MySQL’s indispensable replication function is ‘sold’ as being fail-safe so you have little to sweat about as long as your backups are running regularly. But what the ops guys aren’t telling you is MySQL performs replication with tiny margins of error that could cause big problems in times of disaster.

The Scenario

Imagine the scene, you use replication to backup your data. Your secondary database is your peace of mind. It’s the always-on clone of your crown jewels. You even perform backups off of it so you don’t impact your live website. …

Just in case you missed these posts....

Facebook has some interesting blogs on benchmarks, InnoDB and etc...

What is in the InnoDB buffer pool? 12/15/2011 02:14 PM

Dynamic padding for tpcc-mysql 11/21/2011 11:51 AM

The shutdown benchmark 11/15/2011 05:29 PM

The effect of page size on InnoDB compression 11/07/2011 11:40 AM

There are about a gazillion FAQs and HOWTOs out there that talk about XFS configuration, RAID IO alignment, and mount point options.  I wanted to try to put some of that information together in a condensed and simplified format that will work for the majority of use cases.  This is not meant to cover every single tuning option, but rather to cover the important bases in a simple and easy to understand way.

Let’s say you have a server with standard hardware RAID setup running conventional HDDs.

RAID setup

For the sake of simplicity you create one single RAID logical volume that covers all your available drives.  This is the easiest setup to configure and maintain and is the best choice for operability in the majority of normal configurations.  Are there ways to squeeze more performance out of a server by dividing the logical volumes: perhaps, but it requires a lot of fiddling and custom tuning to …

One of the favourite blog topics on CAOS Theory blog over the years has been our quarterly and annual updates on venture capital funding for open source-related businesses, based on our database of over 600 funding deals since January 1997 involving nearly 250 companies, and over $4.8bn.

There are still a few days left for funding deals to be announced in 2011 but it is already clear that 2011 will be a record year. $672.8m has been invested in open source-related vendors in 2011, according to our preliminary figures, an increase of over 48% on 2010, and the highest total amount invested in any year, beating the previous best of $623.6m, raised in 2006.

Following the largest single quarter for funding for open source-related vendors ever in Q3, Q4 was the second largest single quarter for funding for open source-related vendors …

A big thank you to the MySQL web team and engineers for helping combat spam and keeping the bugs.mysql.com site spam free.

reCaptcha is now installed and is a requirement when adding a comment to a current bug. We want the community to be able to review and comment on bugs with little spam as possible getting in the way.

There have been some request to have some reconnect possibilities in Connector/Python. I’m wondering now whether there should be some automatic reconnect on certain errors within the database driver.

My personal feeling is to have no automatic reconnect within Connector/Python and the programmer has to come up with retrying transactions herself.

For example:

        cnx.disconnect() # For testing..
        tries = 2
        while tries > 0:
                tries -= 1
                try:
                        cursor.execute("INSERT INTO t1 (c1) VALUES ('ham')")
                        cnx.commit()
                except mysql.connector.InterfaceError:
                        if tries == 0:
                                print "Failed inserting data after retrying"
                                break
                        else:
                                print …

Statement based replication writes the queries that modify data in the Binary Log to replicate them on the slave or to use it as a PITR recovery. Here we will see what is the behavior of the MySQL when it needs to log “not usual” queries like Events, Functions, Stored Procedures, Local Variables, etc. We’ll learn what problems can we have and how to avoid them.

TRIGGERS

When a statement activates a Trigger only the original query is logged not the subsequent triggered statements. If you want to maintain the consistency of your data is necessary to define the same Triggers in Master and Slave servers.

Example:

mysql> create trigger Copy_data AFTER INSERT on t FOR EACH ROW INSERT INTO t_copy VALUE(NEW.i);
mysql> insert into t VALUES(1),(2),(3);

Binary Log:

#111213 23:16:21 server id 1 …

For a work project, I wrote a library in perl that can be used to query the NVD feed that NIST publishes here:

http://nvd.nist.gov/download.cfm

Here’s a snippit from the perldoc:

use NIST::NVD::Query;
 
# use convert_nvdcve to generate these files from the XML dumps at
# http://nvd.nist.gov/download.cfm
 
my( $path_to_db, $path_to_idx_cpe ) = @ARGV;
 
my $q = NIST::NVD::Query->new( database => $path_to_db,
                               idx_cpe  => $path_to_idx_cpe,
                              );
 
# Given a Common Platform Enumeration urn, returns a list of known
# CVE IDs
 
my $cve_id_list = $q->cve_for_cpe( cpe => 'cpe:/a:zaal:tgt:1.0.6' );
 
my @entry;
 
foreach my $cve_id ( @$cve_id_list ){
 
  # Given a CVE ID, returns a CVE entry
 
  my $entry = $q->cve( cve_id => …

We are delighted to announce the release of MONyog 4.8 GA. This release is all about customization – to be precise Custom SQL Objects (CSO) and Custom SQL Counters (CSC).

The above diagram shows how Monitors & Advisors (MySQL Counters) are populated. MONyog has a repository of SQL Queries which are executed in regular interval of time & the results are stored in SQLite database of MONyog. These results are exposed as JavaScript objects and are referenced to populate Monitors and Advisors (MySQL Counters).

With CSO, you can add your own SQL queries to this repository & customise counters based on that. Discussed below are some cases on how useful CSOs can be.

• Maintenance statements, for eg. CHECK TABLE, REPAIR TABLE etc.
• You could query on Information Schema to …

Tis the season to be fearless in blogging and read the blogs around the database globe. To make your foray in intrepid world of database blogs, this Log Buffer Edition compiles some of the most interesting posts in this Log Buffer #250. Oracle: One of the nice new features of the 11.2 installer for the [...]