You are here

mysql

Can you trust your MySQL backup?

Taxonomy upgrade extras: 

Today a customer with corrupted data files showed up. When we enquired a bit more he told us that he had a broken I/O controller. This is one of the worst things which can happen to you!

The reason is the following: When a I/O controller starts to die it often does not happen immediately. The controller dies slowly producing more and more corrupt data. When you just write data without checking or reading them it can take days or even weeks until you discover the problem.

What is CHECK TABLE doing with InnoDB tables?

Taxonomy upgrade extras: 

Recently we had a case where a customer got some corrupted blocks in his InnoDB tables. His largest tables where quite big, about 30 to 100 Gbyte. Why he got this corrupted blocks we did not find out yet (disk broken?).

When you have corrupted blocks in InnoDB, it is mandatory to get rid of them again. Otherwise your database can crash suddenly.

MySQL on VMware Workstation/DRBD vs. VMWare ESX Server/SAN

Or an active-active fail-over cluster à la VMware.

Today I have learned about a totally crazy/cool looking architecture where the expensive VMware ESX server was replace by a free/cheap VMware Workstation version in combination with DRBD.

Basically DRBD we name the poor man's SAN and that is exactly what this customer is doing. He replaced the SAN with DRBD and now he can easily move one VMware instance to the other host. Possibly it is not that flexible and powerful as an ESX Server but also not so expensive...

The architecture looks as follows:

The battle against Oracle is probably over but has the real war begun yet?

Taxonomy upgrade extras: 

According to different sources from the web the decision about the Oracle - Sun merger has been approved by the European commission soon. So at least in the West it is clear what is going on. Let us see what the East decides... [ 1 ], [ 2 ].

MySQL reporting to syslog

Taxonomy upgrade extras: 

There are 2 different possible situations you can face when you have to deal with MySQL and syslog:

  • MySQL is used as back-end for syslog to store the logging information. [ 6 ]
  • MySQL itself should report to the syslog.

In this blog article we look at the second situation: How can you make MySQL reporting to the syslog.

My wish for the New Year: MySQL DBA's, please install iostat on your servers!

Taxonomy upgrade extras: 

iostat is a very handy tool to help you investigating what kind of performance problems you have. Especially your databases can cause a lot of troubles to your I/O system and thus it would be very nice if every DBA has installed iostat on all of his MySQL database servers.

MySQL Tools

Performance Monitor for MySQL (mpm)

The FromDual Performance Monitor for MySQL (mpm) is a monitoring solution for MySQL, MySQL Cluster, Galera Cluster, Percona Server and MariaDB databases. The mpm can be downloaded from here.
It is based on the monitoring solution Zabbix.

The Performance Monitor for MySQL (mpm) supports the following Storage Engines:

Undefined

MySQL Database Health Check

Release v0.28

  • On this page you can check the health of your MySQL/MariaDB database.
  • If you want to find out how the MySQL Health Check works try out the examples.
  • If you are logged in, our CMS remembers your entry for your next visit.
  • If we should send you some advices about the findings please fill in the following form:
Contact
Name: Undefined

MySQL useful add-on collection using UDF

I really like this new toy (for me) called UDF. So I try to provide some more, hopefully useful, functionality.

The newest extension I like is the possibility to write to the MySQL error log through the application. Oracle can do that since long. Now we can do this as well...

A list of what I have done up to now you can find here:

Using MySQL User-Defined Functions (UDF) to get MySQL internal informations

In one of my previous posts I was writing about how to read other processes memory [ 1 ]. As an example I tried to get the value of the hard coded MySQL internal InnoDB variable spin_wait_delay (srv_spin_wait_delay).

In this example we were using gdb or the operating system ptrace function to retrieve this value. This method has the disadvantage that it is pretty invasive.

Determine in MySQL if we are in summer time or winter time (daylight saving time, DST)

Recently a colleague at Sun was asking me if MySQL can tell him to determine if we are currently in summer time or winter time. He was doing some data analysis of his house where he has installed solar panels.

I am not aware of what he wants to do exactly, but possibly he wants all the data in solar time. So UTC could help him because UTC does not change much over time.

Next thing which came to my mind is, that possibly the good place to do such mathematical calculations is the application code and not the database.

MySQL licenses for dummies

Taxonomy upgrade extras: 

The following summary shows my personal understanding of MySQL 5.1 licenses, packages and products. It does not necessarily reflect 100% the way MySQL understands it. But after all the discussions I hope it is as close as possible to the reality:

MySQL Embedded Database Server

(Download -> OEM Software)

MySQL Multi-Master – Single-Slave – Replication (Episode 2)

Introduction

One of the features that make MySQL so great is its easy replication set-up. If you are experienced and know-how to do it, it takes you about 15 minutes to set-up a slave. What you have in the end is a replication from one master to one or several slaves. So you can build a top-down data stream pyramid and spread your data on many slaves.

MySQL Replication

Test application for MySQL high availability (HA) set-up

When I set-up a MySQL HA environment for customers I usually do some final fail over tests after configuring the whole beast.

To check if the application behaves like expected I always run my little test application (test.sh) from the server(s) where the customers application runs. It displays "graphically" how the application behaves and you can show to the customer immediately what is going on...

Make sure, that you point it to the virtual IP (VIP) or the load balancer (LB).

Citation of the week

Taxonomy upgrade extras: 

Das dreieckige Rad hat gegenüber dem viereckigen einen gewaltigen Vorteil: Ein Rumms weniger pro Umdrehung!

Translation:

The triangular wheel has one enormous advantage over the quadrangular: One knock less per revolution!

Maybe not new, but I have not heard it yet and I love it. It was about reinventing functionality in a well known product...

Active/active fail over cluster with MySQL Replication

Electing a slave as new master and aligning the other slaves to the new master

In a simple MySQL Replication set-up you have high-availability (HA) on the read side (r). But for the master which covers all the writes (w) and the time critical read (rt) there is no HA implemented. For some situations this can be OK. For example if you have rarely writes or if you can wait until a new Master is set up.

But in other cases you need a fast fail-over to a new master.

Typical automated MySQL maintenance jobs, query cache

The following maintenance jobs are typically run against a MySQL database:

  • Backup
  • Clean-up binary logs
  • Optimize tables
  • Purge query cache
  • Rotate binary logs

Backup

A backup is not a typical maintenance job. But it behaves more or less like one. The backup should be done regularly depending on the restore/PITR (Point in Time Recovery) requirements.

My thoughts about MySQL (Cluster) replication

Taxonomy upgrade extras: 

According to Johans wishes I write down my concerns about MySQL (Cluster) replication. These items are things I run again and again into it with customers:

With MySQL-Enterprise Montior through firewalls

Taxonomy upgrade extras: 

Sometimes it is nice to show customers the functionality of MySQL-Enterprise Monitor (aka Merlin). I install the agents on the servers and the dashboard runs on my laptop. But very often only ssh is open to these servers.

So how to dig a whole through the firewall for MySQL-Enterprise Monitor?

# ssh -R 18080:localhost:18080 oli@where_the_agent_sits

Maybe trivial for you but for me its hard to remember...

Ranking in MySQL results

Taxonomy upgrade extras: 

A friend of me asked me long time ago: "How can I have a ranking on a result with MySQL?". Now I found some time to write it down:

Lets do first some preparation for the example:

CREATE TABLE sales (
  id     INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, fruit  VARCHAR(32)
, amount DECIMAL
);

INSERT INTO sales
VALUES (NULL, 'apple', 12.75), (NULL, 'orange', 1.89), (NULL, 'pear', 19.23)
     , (NULL, 'banana', 4.25), (NULL, 'cherry', 123.75), (NULL, 'plum', 23.15)
;

Now lets query:

MySQL logon trigger

With MySQL 5.0 the database provides trigger functionality on INSERT, REPLACE, UPDATE and DELETE.

Those of you who know some other RDBMS know, that there are also some system events where one would like to have triggers.

Unfortunately MySQL does not (yet) provide such functionality. This is sad because as database administrator this would be sometimes very helpful.

But you can build your own LOGON and STARTUP trigger.

MySQL provides some hooks for these events...

MySQL Cluster restore

Recently the question came up if it is faster to restore a MySQL cluster when all nodes are up or only ONE node from each node group during restore.

The answer from our gurus was: All nodes up during restore! I wanted to find out why. So I set up the following cluster and started to measure:

MySQL Cluster set up

Cluster set-up

MyEnv (MySQL BasEnv)

What is MyEnv?

MyEnv is a set of scripts to run comfortably multiple MySQL, Percona Server or MariaDB database instances on the same server. You can even run multiple database instances with different binary versions.

MyEnv is more comfortable than mysqld_multi and provides more functionality.

A large MySQL user (Alexa Ranking: Top 500 world-wide, top 20 in Germany) stated:

MySQL Active - Active Clustering

It is possible to use an active - active shared-disk cluster in MySQL in some cases. For doing this you have to fulfill the following requirements:

Round-Robin Database Storage Engine (RRD)

In a round-robin database (RRD) usually time-series data like network bandwidth, temperatures, CPU load etc. is stored. The data is stored in the way that system storage footprint remains constant over time. This avoids resource expensive purge jobs and reduces complexity.

RRD

MySQL does NOT yet provide this kind of storage engine. Although some people were thinking about and some prototypes exists.

Pages

Subscribe to RSS - mysql