Feed Aggregator

New version of FromDual’s Performance Monitor for MySQL (MPM) v0.7.2 is out

Dear MySQL User,

On December 2^nd 2011 FromDual released the new version v0.7.2 of its MySQL Performance Monitor. The new version can be downloaded from here.

The Performance Monitor for MySQL is an agent which is hooked into the agent of the well known Monitoring solution called Zabbix. Zabbix is an integrated Enterprise Monitoring solution which can produce performance graphs and alerting.

The changes in the new release are:

New functionality

• A new module for Galera was added (mainly Galera 0.8).
• A new module for the MySQL Performance Monitor (mpm) was added.
• Information about InnoDB Row Locking was added (status and graph).
• Some MySQL variables were added for monitoring (customer request).
• Triggers for some of those MySQL variables are implemented (customer request).

Changed functionality

• MPM agent hook (FromDual.MySQL.check) was moved from the mysql module to the mpm module. To call the MPM agent you have to run the mpm …

Please continue here.

Rumbi pointed me to a bug in the PDF!

The first Slave S should point to Master M on port 3315.

I have fixed the PDF now. Thanks Rumbi!

This concept/PoC is old! Nowadays we do not need this anymore.

What in this blog post was described nowadays is called Multi-Source Replication. If you really need Multi-Source Replication I would go for Tungsten Replicator.

This concept/PoC is old! Nowadays we do not need this anymore.

What in this blog post was described nowadays is called Multi-Source Replication. If you really need Multi-Source Replication I would go for Tungsten Replicator.

Canias is a great ERP product but there is still some potential to improve it. We only focus on MyISAM/InnoDB installations of Canias v6.0.2 in this article. For other DB back-ends those recommendations might be obsolete.

A general problem is, that in InnoDB Primary Keys are crucial in MySQL. Canias often lacks a Primary Key. In this case InnoDB will use the Unique Key for the Primary Key.

In InnoDB data are sorted by the Primary Key so it is a good idea to have the Primary Key on CLIENT and COMPANY as well to get proper sorting of the rows.

This has been implemented very in-consequent in Canias development.

In the following table there is NO Primary Key. Thus MySQL will elect the UNIQUE KEY IASSALITEM as Primary Key and sort the rows according to the DOCNUM instead of (CLIENT, COMPANY, DOCTYPE, DOCNUM, ITEMNUM, SETITEMNUM) what logically would be correct…

Table IASSALITEM

CREATE TABLE `IASSALITEM` (
  ...
  UNIQUE KEY `IASSALITEM_U1` (`DOCNUM`,`ITEMNUM`,`SETITEMNUM`,`DOCTYPE`,`COMPANY`,`CLIENT`), …

Yesterday we released the new version of the MPM v0.8. The most important new features are:

• All modules are now available under GPL (including InnoDB and NDB module!).
• The MySQL Performance Monitor agent can now be use for Monitoring as a Service. We provide the Monitoring solution. You just have to install the agent.

Currently we are looking for people who want to test the new feature. If you are interested please get in contact with us.

Recently I found in a forum the following request for help:

My MySQL instance crashed because of free disk space fault. I saw in /var/lib/mysql all the files: ibdata1, ib_logfile* and all the folders containing frm files. Well, when i solved the problem and run successfully the instance, some databases disappeared. One of those is the most important, and i don’t know how many tables had and their structures. Is there any way for recover the entire lost database (structure and data) only having the ibdata1 file?

First of all the observation sounds a bit strange because files do not just disappear. So I fear that its not just the .frm files which are lost. But let’s think positive and assume just the .frm files have gone…

To recover the tables is a bit tricky because the .frm files contains the information about the table structure for MySQL.

If you have any old backup or only a structure dump it would be very helpful..

In InnoDB there is the table structure stored as well. You can get it out …

It seems that if you have any ENUM or SET types in your tables you’re a bit out of luck. There’s nothing in InnoDB that would give you the correct information for those. Also, since long VARCHAR columns are silently converted to TEXT you may get a definition that matches with the InnoDB data dictionary but will be slightly off from the original .frm file.

Dear user,

Our website is currently overloaded. Please try again in a few minutes.

Your FromDual team

A few days ago Codership announced their new version Galera v1.1 - synchronous Replication Cluster for MySQL. Before we look at the new feature of Rolling Online Schema Upgrade (OSU) we have a look at how to upgrade to the new Galera release.

A rolling upgrade of your synchronous Galera Replication Cluster from version 1.0 to 1.1 is quite easy when you stay at the same MySQL version (5.5).

To not lose the availability of your database service during the upgrade you should have at least 3 Galera nodes in your Cluster.

For further details please also look at MySQL/Galera cluster upgrade.

Hint: If you can do without rolling upgrade, you better avoid it and take your Galera Cluster down.

Check the version

Check first the version you are currently running on:

SHOW GLOBAL VARIABLES LIKE 'version';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| version       | 5.5.15-wsrep_21.1-log |
+---------------+-----------------------+

We can see …

CREATE TABLE `order` (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, name VARCHAR(64) NOT NULL
) ENGINE = InnoDB;

INSERT INTO `order` VALUES
  (NULL, 'Test order 1')
, (NULL, 'Test order 2')
, (NULL, 'Test order 3');


CREATE TABLE pos (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, order_id INT UNSIGNED NOT NULL
, name VARCHAR(64) NOT NULL
, amount SMALLINT NOT NULL
, price DECIMAL (6,2) NOT NULL
, status TINYINT NULL
) ENGINE = InnoDB;

INSERT INTO pos VALUES
  (null, 1, 'Schrauben', 50, 0.10, 0)
, (null, 1, 'Muttern', 50, 0.10, 0)
, (null, 2, 'Nägel', 1000, 0.05, 1);


CREATE TABLE pos_shadow LIKE pos;
ALTER TABLE pos_shadow ENGINE = MEMORY;
INSERT INTO pos_shadow SELECT * FROM pos;

delimiter //

CREATE TRIGGER upd BEFORE UPDATE ON pos
FOR EACH ROW
BEGIN
  UPDATE pos_shadow AS p
    JOIN `order` AS o ON o.id = p.order_id and o.id = NEW.order_id
    SET status = 1
  WHERE o.id = 1;
END;
//

delimiter ;

SELECT o.name, p.name, p.amount, p.price, (p.amount*p.price) AS total, p. status …

If you cannot wait you can read Building Galera Replication from scratch…

Ok, thank you, I guess I will have to wait for the 1.1 release, hopefully soon :)

On December 2^nd 2011 FromDual released the new version v0.7.2 of its Performance Monitor for MySQL. The new version can be downloaded from here.

The Performance Monitor for MySQL is an agent which is hooked into the agent of the well known Monitoring solution called Zabbix. Zabbix is an integrated Enterprise Monitoring solution which can produce performance graphs and alerting.

The changes in the new release are:

New functionality

• A new module for Galera was added (mainly Galera 0.8).
• A new module for the MySQL Performance Monitor (mpm) was added.
• Information about InnoDB Row Locking was added (status and graph).
• Some MySQL variables were added for monitoring (customer request).
• Triggers for some of those MySQL variables are implemented (customer request).

Changed functionality

• MPM agent hook (FromDual.MySQL.check) was moved from the mysql module to the mpm module. To call the MPM agent you have to run the mpm module only once per monitored server (computer).
• Path to the MPM agent libraries is not hard …

Yes I know! :)

It will NOT work with 1.0… We did some tests earlier with 1.0 and they failed. The results made it into into 1.1…

Hello,

Thank you, very usefull informations… I am planning a migration too soon, but I am wondering because you say it’s working with release 1.1, which does not seem to be out there yet..

So.. do you know if it would it work with v1.0 ?

Thank you !

Introduction

Galera is a synchronous Multi-Master Replication for MySQL. It is therefore in competition with several other MySQL architectures:

• Master-Master Replication with MySQL
• MySQL Cluster
• The non-open source product called Schooner

Very often they can be easily replaced by Galera’s synchronous Multi-Master Replication for MySQL.

All those products have some advantages and disadvantages. Very often MySQL Master-Master Replication is used in the field because of its simplicity to set-up. But after a while one faces its disadvantages which is mainly data inconsistency between the 2 Masters. This is not only the fault of MySQL Replication but MySQL Replication makes it easy to get such data inconsistencies.

In the following article we look at how you can replace a MySQL Master-Master Replication by Galera Multi-Master Replication with the possibility to fall back if you do not like the solution or if you run into troubles.

Starting point

Some MySQL users have a typical Master-Master Replication …

Follow the Galera documentation as well!

What was needed in addition in my case:

yum remove boost.x86_64 boost-devel.x86_64
yum install boost141.x86_64 boost141-devel.x86_64 openssl-devel.x86_64
yum install ncurses-devel.x86_64 bison.x86_64 bison-devel.x86_64
yum install gcc44.x86_64 gcc44-c++.x86_64

ldconfig -p | grep libboost_program_options

ln -s /usr/lib64/libboost_program_options.so.5 /usr/lib64/libboost_program_options.so