[07:51:52] shyperson: I would suggest to --insert the rule instead of --apendING
[07:52:15] shyperson: Just to make sure there are no other rules allowing mysqlport before

I actually made a little upstart script (Ubuntu) - works well under Ubuntu 13.04 (rating) w/ percona xtradb cluster packages 5.5.31-23.7.5-438.raring

This is with a 2 node cluster, this is node #3 (the arbiter), group name “default”

/etc/init/garbd.conf:

garbd - galera arbitrator

description “Galera arbitrator”

start on runlevel [2345] stop on runlevel [!2345]

exec /usr/bin/garbd -a gcomm://db1,db2 -g default

About

glbd is a simple TCP connection balancer made with scalability and performance in mind. It was inspired by pen, but unlike pen its functionality is limited only to balancing generic TCP connections.

Features:

• list of backend servers is configurable in runtime.
• supports server draining, i.e. does not allocate new connections to server, but does not kill existing ones, waiting for them to end gracefully.
• can use epoll API provided by Linux version 2.6 and higher for ultimate routing performance.
• glb is multithreaded, so it can utilize multiple CPU cores. Also, if your OS does not support epoll API, consider using several threads even on a single core machine as it will lessen poll() overhead proportionally and can improve overall performance by a factor of 2 or more.
• optional watchdog module can monitor destinations and adjust routing table automatically.

libglb is a shared library that provides 0-effort connection balancing to any Linux application that uses standard libc connect() call by overloading …

It took me quite a while to find out how the beast Galera Arbitrator (garbd) works. To safe your time here a short summary:

How to start Galera Arbitrator (garbd)

shell> ./garbd --address gcomm://192.168.13.1,192.168.13.2 --group "Our Galera Cluster" --log /tmp/garbd.log --daemon

How to stop Galera Arbitrator (gardb)

shell> killall garbd

How to start Galera Arbitrator (garbd) with a configuration file

shell>./garbd --cfg /tmp/garb.cnf --daemon

The configuration file looks as follows:

#
# /etc/mysql/garb.cnf
#
address = gcomm://127.0.0.1:5671,127.0.0.1:5672,127.0.0.1:5673
group = Our Galera Cluster
options = gmcast.listen_addr=tcp://127.0.0.1:5674
log = /tmp/garbd.log

A service start/stop script can be found at: galera-src/garb/files/agrb.sh and galera-src/garb/files/garb.cnf

Hello Shinguz,

Pay attention: Your script is a bit too restrictive. It will IMHO also close the port for the SST when you use the wsrep_sst_methode = mysqldump. Possibly you should restrict it to the Load Balancer IP or allow access at least from the possible donors.

Cheers,
Oli

Our bigger customers where we help to deploy Galera Cluster for MySQL set-ups have some commercial hardware (e.g. F5 or Cisco) for load balancing instead of software load balancers.

For those hardware load balancer it is not possible to see if a Galera node is available or not because the MySQL daemon is still running and responding on port 3306 but the service is not available nonetheless.
So the load balancer still serves the Galera node while he feeds for example a joiner node with a SST. This would lead to application errors which is unlovely.

One can try somehow to teach the load balancer to find out if a Galera Cluster node is really available or not. But this requires a more sophisticated load balancer, know-how how to teach the load balancer the new behaviour and possible interaction between the MySQL node and the load balancer. See our other discussion for this mater.

An other concept we hit on this week is that we could also block the port 3306 of the MySQL node with firewall rules (iptables). Then …

I was recently told that it would be better to use REJECT than DROP:

As a general rule, use REJECT when you want the other end to know the port is unreachable' use DROP for connections to hosts you don't want people to see. [ 1 ]

Recently a forum question
[ 1
] got my attention:

*Is there any performance issue with Union?

I used union all sometime back and it was performance issue just to make an opinion that we should used union in query.*

The question itself was not too interesting because the answer is easy: It depends. But I wanted to see if there was an improvement in this common problem over time in MySQL.

Test set-up

So I prepared a little test to simulate some of the possible scenarios:

CREATE TABLE `u` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(10) unsigned DEFAULT NULL,
  `b` int(10) unsigned DEFAULT NULL,
  `c` int(10) unsigned DEFAULT NULL,
  `d` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`),
  KEY `c` (`c`),
  KEY `d` (`d`)
) ENGINE=InnoDB
;

INSERT INTO u SELECT NULL, ROUND(RAND()*10, 0), ROUND(RAND()*10, 0), ROUND(RAND()*1000000, 0), ROUND(RAND()*1000000, 0);
INSERT INTO u SELECT NULL, ROUND(RAND()*10, 0), ROUND(RAND()*10, 0), ROUND(RAND()*1000000, 0), …

Since MySQL 5.1.43 timestamp is not allowed as partition key any more [ 1 ]. Thus we can do it as follows:

CREATE TABLE ptn_test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT
, data VARCHAR(64)
, dt DATETIME
, PRIMARY KEY (id, dt)
, INDEX (dt)
) ENGINE = MyISAM
PARTITION BY RANGE ( TO_DAYS(dt) ) (
  PARTITION p_2010    VALUES LESS THAN ( TO_DAYS('2011-01-01 00:00:00') )
, PARTITION p_2011_01 VALUES LESS THAN ( TO_DAYS('2011-02-01 00:00:00') )
, PARTITION p_2011_02 VALUES LESS THAN ( TO_DAYS('2011-03-01 00:00:00') )
, PARTITION p_2011_03 VALUES LESS THAN ( TO_DAYS('2011-04-01 00:00:00') )
, PARTITION p_2011_04 VALUES LESS THAN ( TO_DAYS('2011-05-01 00:00:00') )
, PARTITION p_2011_05 VALUES LESS THAN ( TO_DAYS('2011-06-01 00:00:00') )
, PARTITION p_2011_06 VALUES LESS THAN ( TO_DAYS('2011-07-01 00:00:00') )
, PARTITION p_2011_07 VALUES LESS THAN ( TO_DAYS('2011-08-01 00:00:00') )
, PARTITION p_2011_08 VALUES LESS THAN ( TO_DAYS('2011-09-01 00:00:00') )
, PARTITION p_2011_09 VALUES LESS THAN ( …

Table of Contents

• Accounts not properly closing connections
• Unused indexes
• Who created temporary (disk) tables
• Accounts which never connected since last start-up
• Users which never connected since last start-up
• Totally unused accounts (never connected since last restart and not used to check Stored Program or View privileges) since last start-up
• SHOW FULL PROCESSLIST
• Storage Engines per schema
• Tables without a Primary Key
• Bad SQL queries of users
• SHOW PROFILE in PERFORMANCE_SCHEMA
• SELECT, INSERT, UPDATE and DELETE per table
• Top long running queries
• Tables never written to
• DML, DDL and DCL per account
• Finding syntactically wrong/erroneous MySQL queries
• Finding queries served by MySQL Query Cache
• Grant access to sys schema to another user
• Grant access to PERFORMANCE_SCHEMA schema to another user
• Find InnoDB Locks
• Find Metadata Locks
• What did a Connection before sleeping?
• How many statements did another (sleeping) connection?
• MyISAM locking
• Connections per user
• Connections per user and IP
• Accounts which could …

We did a similar thing this week with Galera Cluster for MySQL and the Galera Load Balancer (glb).

Here you can find the script.

I have not had the time yet to test it. Please let us know if it does not work or when you were successful...

Hi Fabio,

There are several ways to do this. The most simple one but possibly not what you need is:

mysql> pager grep A
SHOW DATABASES;

The better way to do this IMHO is:

mysql> SHOW DATABASES LIKE 'A';

And the best way (works in MySQL 5.0 and newer) IMHO is:

mysql> SELECT * FROM information_schema.schemata WHERE schema_name = 'A';

I hope this helps you solving your problem?

Regards,
Oli

I want to monitor a specific mysql database.

This is the output of “show databases” command:

+----------+
| Database |
+----------+
| ...      |
| ...      |
| ...      |
| A        |
| zabbix   |
+----------+

Let’s say I want to monitor database A.

The problem is that I monitor also zabbix databases (mysql and zabbix are on the same machine).

So for example if I see the chart “MySQL Queries executed” I obtain also the query executed by zabbix on zabbix database.

How can I isolate the monitor for database A?

Maybe you experienced some strange behaviour with MySQL: Everything is installed correctly and should work. But it does not.

Symptoms we have seen:

• MySQL starts/stops properly when started/stopped with service mysqld restart but MySQL does not start when a server is rebooted.
• Or after upgrading MySQL binaries mysqld will not start at all any more.
• Or after relocating MySQL datadir or changing default port MySQL does not start any more.

shell> service mysqld start
MySQL Daemon failed to start.
Starting mysqld:                                           [FAILED]

shell> grep mysqld /var/log/boot.log 
Starting mysqld:  [FAILED]

If you are lucky you get some error message like: ERROR! The server quit without updating PID file (/data/mysql/server.pid). or:

130620  9:49:14 [ERROR] Can't start server : Bind on unix socket: Permission denied
130620  9:49:14 [ERROR] Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ?
130620  9:49:14 [ERROR] Aborting

This typically happens …

VirtualBox

Network Interface does not start on Guest System:

MAC Address must be all the same in:

• /etc/sysconfig/network-script/ifcfg-eth0
• /etc/udev/rules.d/70-persistent-net.rules
• AND VirtualBox: Guest
  > Network
  > Adapter
  > Advanced
  > Mac Address

Recently we had to set-up a 3-node Galera Cluster with a Load Balancer in front of it. Because Galera Cluster nodes (mysqld) still reply to TCP requests on port 3306 when they are expelled from the Cluster it is not sufficient to just leave it to the Load Balancer to check the port if a Galera node is properly running or not.

We used the wsrep_notify_cmd variable to hook our own script into the Galera Cluster which disables each Node on the Load Balancer when its state changed.

# my.cnf
#
[mysqld]
wsrep_notify_cmd = /usr/local/bin/lvs_control.sh

The whole Galera Cluster Architecture looks as follows:

As Load Balancer we used the IPVS Load Balancer from the Linux Virtual Server (LVS) Project. This Load Balancer was made highly available with keepalived.

Our script to take a Galera Node out of the Load Balancer was the following:

#!/bin/bash -eu

#
# /etc/mysql/conf.d/wsrep.cnf
#
# [mysqld]
# wsrep_notify_cmd = /usr/local/bin/lvs_control.sh
#

LOG="/tmp/lvs_control.log"
LBIP="192.168.0.89" …

The new release of the MySQL Performance Monitor (mpm) is out!

New additions and improvements

• Easy to use templates
• Improved Security
• New Trigger Checks
• New Warnings Enabled
• Time Zone Shift Added
• New screens added
• Data transfer enabled

and much more (see below).

The MySQL Performance Monitor (mpm) for MySQL, Galera Cluster, Percona Server and MariaDB is a Monitoring Solution based on the Enterprise open source Monitor Zabbix.

It provides all the necessary modules to monitor MySQL performance metrics in detail and you can display them graphically.

New Features

Template improved for easier use - Security module added - Slave and MySQL templates fixed after feedback from customers - Innodb log information and pending I/O information added - Sort_buffer_size trigger added - Slave error skipped trigger added - Check for isolation level added - Binlog do and ignore filter warning enabled - Innodb deadlock trigger downgraded from warning to info - Time-shift feature implemented - Flush_time trigger added - MyISAM …

You are running already MariaDB or MySQL databases in production but you have higher requirements in database availability? If you want to solve these High Availability requirements with Galera Cluster this training is the right one for you.

Requirements

You are used in operating demanding MariaDB/MySQL databases. Further you are familiar with the Linux console and rule the most important UNIX commands.

Audience

• MariaDB/MySQL application developer
• MariaDB and MySQL database administrators
• System administrators with database responsibility

Contents

• MariaDB/MySQL High-Availability solutions overview
• What is Galera Cluster?
• Installation and configuration of Galera Cluster
• Operations of Galera Cluster
• Backup and Restore
• Load Balancing
• Performance Tuning and Monitoring
• High-Availability and Scalability with Galera Cluster
• Security
• Galera Cluster and Master/Slave Replication

Duration

3 days

Booking trainings

Trainings for Germany can be booked on-line here and trainings in Switzerland here.

Advanced trainings …

FromDual is looking for professional, enthusiastic and experienced people who:

• Know MySQL, Percona Server or MariaDB extensively
• Are Familiar with the open source eco-system
• Know how to operate database systems, as a DBA or a DevOps
• Understand what can go wrong in operating a database
• Are happy to work autonomously, remotely and to communicate with IRC, Skype, Mail and Phone
• Are comfortable on Linux systems
• Are team players, keen to contribute to the growth of the company
• Are Comfortable dealing direct with clients and
• Look for new challenges

Job description

We are looking for full-time MySQL support engineers (female or male) to primarily take care of our MySQL support services and help our customers operating their MySQL databases (remote-DBA and emergency interventions).

You are well trained and have good experience in:

• Operating critical highly available MySQL production databases mostly on Linux.
• Running MySQL-Replication in all variants is your daily business.
• The working of the most used MySQL HA …

I have installed your MPM for Zabbix, but I am not getting any data into Zabbix. I have followed the troubleshooting steps at http://www.fromdual.com/mpm-installation-guide, and added some extra checks. I even set this up on a test server first (and had this working) On the Zabbix server, Status is Monitored for Host, Availability is in the green, templates at Template_FromDual.MySQL.mpm, Template_FromDual.MySQL.mysql, Template_FromDual.MySQL.server, Template_FromDual.MySQL.slave Monitoring the agent. $ tail /var/log/zabbix/zabbix_agentd.log 1948:20130325:220353 Requested
[FromDual.MySQL.check
] 1948:20130325:220354 Before 1948:20130325:220354 Run remote command
[/usr/local/mysql_performance_monitor_agent/FromDualMySQLagent.pl /usr/local/mysql_performance_monitor_agent/etc/FromDualMySQLagent.conf
] Result
[1
]
[1
]… 1948:20130325:220354 Sending back
[1
] 1949:20130325:220403 Processing request. QUESTION: Is a result of
[1
] correct or incorrect? $ tail -30 /tmp/FromDualMySQLagent.log 6130:2013-03-25 …