Feed aggregator

Table of Content

• Introduction
• Prerequisites
  • System information
  • Installed packages
  • Required packages
• Upgrade the first node
  • Upgrade the installed binaries
  • Upgrade mysql schema
  • Prepare the node to join the cluster
  • Joining the cluster
• Rolling upgrade the other nodes
• Get rid of old release option

Introduction

Codership announced from weeks ago introducing the Galera Cluster new release 3.0 having many bug fixes, performance enhancements plus the main purpose which is working with MySQL 5.6. In this article, I'll go through the upgrade steps from Galera 2.x to the new release 3.0, but at the time of writing this article - as mentioned in the Codership release notes - THIS IS A BETA QUALITY RELEASE FOR TESTING PURPOSES. NOT RECOMMENDED FOR PRODUCTION YET.

Important note: a new Galera version (3.1) will be available soon for production and it will be INCOMPATIBLE with this beta version (3.0) but still compatible with 2.x, so again DO NOT go for production using 3.0 and postpone the production upgrade process until 3.1 become available.

Prerequisites System information

The following are the cluster system information:

• Operating System: CentOS release 6.4 (64 bit)
• Cluster system consists of 3 cluster nodes (192.168.1.251 "gcservera",192.168.1.252 "gcserverb" & 192.168.1.253 "gcserverc")

Installed packages

The following are the packages installed on the three cluster nodes:

• MySQL version: mysql-5.5.33_wsrep_23.7.6 (RPM)
• Galera provider version:galera-23.2.7 (RPM)

mysql> show global variables like'%version%'; +-------------------------+--------------------------------------------------+ | Variable_name | Value | +-------------------------+--------------------------------------------------+ | innodb_version | 5.5.33 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.5.33-log | | version_comment | MySQL Community Server (GPL), wsrep_23.7.6.r3919 | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+--------------------------------------------------+ 7 rows in set (0.03 sec) Required packages

The following are the needed packages to be installed:

• MySQL 5.6 + Galera plugin: Could be downloaded from here.
• Galera provider 3.0: Could be downloaded from here

Upgrade the first node Upgrade the installed binaries

To upgrade the installed binaries, you have to stop the mysqld first.

Important: If you are using a load balancer in your cluster system, you should bring the node in question out from the load balancer before stopping the mysqld.

[root@gcservera ~]# /etc/init.d/mysql stop

And then, upgrade with the binaries:

[root@gcservera ~]# rpm -qa|grep MySQL MySQL-server-5.5.33_wsrep_23.7.6-1.rhel6.x86_64 MySQL-client-5.5.34-1.el6.x86_64 [root@gcservera ~]# rpm -e MySQL-server-5.5.33_wsrep_23.7.6-1.rhel6.x86_64 [root@gcservera ~]# rpm -ivh /downloads/MySQL-server-5.6.13_wsrep_24.0-1.rhel6.x86_64.rpm Preparing... ########################################### [100%] 1:MySQL-server ########################################### [100%] [root@gcservera ~]# rpm -U /downloads/galera-24.3.0-1.rhel6.x86_64.rpm Upgrade mysql schema

It's recommended to perform the mysql schema upgrade before joining the cluster, so that, we need to start the node first as a standalone instance by disabling the provider option:

#my.cnf [mysqld] . . #wsrep_provider=/usr/lib64/galera/libgalera_smm.so

Then start the instance and perform the upgrade using the mysql_upgrade utility:

[root@gcservera ~]# /etc/init.d/mysql start Starting MySQL........... SUCCESS! [root@gcservera ~]# mysql_upgrade Prepare the node to join the cluster

All running nodes (second and third nodes) are using the old galera version (2.7) at the moment, so that a backward compatibility option (wsrep_provider_options="socket.checksum=1") MUST be added in the node's configuration in order to join the cluser, otherwise, it will fail to join it back.

Also don't forget to enable again the provider option:

#my.cnf [mysqld] . . wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_provider_options="socket.checksum=1" Joining the cluster

Now we are ready to join the cluster by restarting the node

[root@gcservera ~]# /etc/init.d/mysql restart Stopping MySQL........... SUCCESS! Starting MySQL........... SUCCESS!

We can check the new version as follows:

mysql> show global variables like'%version%'; +-------------------------+------------------------------------------------+ | Variable_name | Value | +-------------------------+------------------------------------------------+ | innodb_version | 5.6.13 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.13-log | | version_comment | MySQL Community Server (GPL), wsrep_24.0.r3937 | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+------------------------------------------------+ 7 rows in set (0.05 sec)

And the cluster status as well:

mysql> show global status like'wsrep%'; +----------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------+--------------------------------------+ | wsrep_local_state_uuid | ac53dc1e-3aff-11e3-b970-eb7044f6dc77 | . . | wsrep_local_state_comment | Synced | . . | wsrep_cluster_size | 3 | | wsrep_cluster_state_uuid | ac53dc1e-3aff-11e3-b970-eb7044f6dc77 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | wsrep_local_index | 2 | | wsrep_provider_name | Galera | | wsrep_provider_vendor | Codership Oy | | wsrep_provider_version | 24.3.0(r159) | | wsrep_ready | ON | +----------------------------+--------------------------------------+ 43 rows in set (0.13 sec)

Important: if you are using a load balancer in the cluster, it's now the time to add this node back again to it.

Rolling upgrade the other nodes

You can start doing the rolling upgrade to the other nodes the same like the first one but after making sure that all nodes state (wsrep_local_state_comment) is Synced. If you stopped a node while it's in the Donor state, then the donor and the joiner nodes might be crashed, so make sure of that first.

Get rid of the old release option

After making the upgrade on all the cluster nodes, the backward compatibility option (wsrep_provider_options="socket.checksum=1") is not needed anymore, so removing it from the configuration files and doing a rolling restart on all nodes will do the mission.

Have fun with the new MySQL and Galera releases :)

Table of Content

• Introduction
• Prerequisites
  • System information
  • Installed packages
  • Required packages
• Upgrade the first node
  • Upgrade the installed binaries
  • Upgrade mysql schema
  • Prepare the node to join the cluster
  • Joining the cluster
• Rolling upgrade the other nodes
• Get rid of old release option

Introduction

Codership announced from weeks ago introducing the Galera Cluster new release 3.0 having many bug fixes, performance enhancements plus the main purpose which is working with MySQL 5.6. In this article, I'll go through the upgrade steps from Galera 2.x to the new release 3.0, but at the time of writing this article - as mentioned in the Codership release notes - THIS IS A BETA QUALITY RELEASE FOR TESTING PURPOSES. NOT RECOMMENDED FOR PRODUCTION YET.

Important note: a new Galera version (3.1) will be available soon for production and it will be INCOMPATIBLE with this beta version (3.0) but still compatible with 2.x, so again DO NOT go for production using 3.0 and postpone the production upgrade process until 3.1 become available.

Prerequisites System information

The following are the cluster system information:

• Operating System: CentOS release 6.4 (64 bit)
• Cluster system consists of 3 cluster nodes (192.168.1.251 "gcservera",192.168.1.252 "gcserverb" & 192.168.1.253 "gcserverc")

Installed packages

The following are the packages installed on the three cluster nodes:

• MySQL version: mysql-5.5.33_wsrep_23.7.6 (RPM)
• Galera provider version:galera-23.2.7 (RPM)

mysql> show global variables like'%version%'; +-------------------------+--------------------------------------------------+ | Variable_name | Value | +-------------------------+--------------------------------------------------+ | innodb_version | 5.5.33 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.5.33-log | | version_comment | MySQL Community Server (GPL), wsrep_23.7.6.r3919 | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+--------------------------------------------------+ 7 rows in set (0.03 sec) Required packages

The following are the needed packages to be installed:

• MySQL 5.6 + Galera plugin: Could be downloaded from here.
• Galera provider 3.0: Could be downloaded from here

Upgrade the first node Upgrade the installed binaries

To upgrade the installed binaries, you have to stop the mysqld first.

Important: If you are using a load balancer in your cluster system, you should bring the node in question out from the load balancer before stopping the mysqld.

[root@gcservera ~]# /etc/init.d/mysql stop

And then, upgrade with the binaries:

[root@gcservera ~]# rpm -qa|grep MySQL MySQL-server-5.5.33_wsrep_23.7.6-1.rhel6.x86_64 MySQL-client-5.5.34-1.el6.x86_64 [root@gcservera ~]# rpm -e MySQL-server-5.5.33_wsrep_23.7.6-1.rhel6.x86_64 [root@gcservera ~]# rpm -ivh /downloads/MySQL-server-5.6.13_wsrep_24.0-1.rhel6.x86_64.rpm Preparing... ########################################### [100%] 1:MySQL-server ########################################### [100%] [root@gcservera ~]# rpm -U /downloads/galera-24.3.0-1.rhel6.x86_64.rpm Upgrade mysql schema

It's recommended to perform the mysql schema upgrade before joining the cluster, so that, we need to start the node first as a standalone instance by disabling the provider option:

#my.cnf [mysqld] . . #wsrep_provider=/usr/lib64/galera/libgalera_smm.so

Then start the instance and perform the upgrade using the mysql_upgrade utility:

[root@gcservera ~]# /etc/init.d/mysql start Starting MySQL........... SUCCESS! [root@gcservera ~]# mysql_upgrade Prepare the node to join the cluster

All running nodes (second and third nodes) are using the old galera version (2.7) at the moment, so that a backward compatibility option (wsrep_provider_options="socket.checksum=1") MUST be added in the node's configuration in order to join the cluser, otherwise, it will fail to join it back.

Also don't forget to enable again the provider option:

#my.cnf [mysqld] . . wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_provider_options="socket.checksum=1" Joining the cluster

Now we are ready to join the cluster by restarting the node

[root@gcservera ~]# /etc/init.d/mysql restart Stopping MySQL........... SUCCESS! Starting MySQL........... SUCCESS!

We can check the new version as follows:

mysql> show global variables like'%version%'; +-------------------------+------------------------------------------------+ | Variable_name | Value | +-------------------------+------------------------------------------------+ | innodb_version | 5.6.13 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.13-log | | version_comment | MySQL Community Server (GPL), wsrep_24.0.r3937 | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+------------------------------------------------+ 7 rows in set (0.05 sec)

And the cluster status as well:

mysql> show global status like'wsrep%'; +----------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------+--------------------------------------+ | wsrep_local_state_uuid | ac53dc1e-3aff-11e3-b970-eb7044f6dc77 | . . | wsrep_local_state_comment | Synced | . . | wsrep_cluster_size | 3 | | wsrep_cluster_state_uuid | ac53dc1e-3aff-11e3-b970-eb7044f6dc77 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | wsrep_local_index | 2 | | wsrep_provider_name | Galera | | wsrep_provider_vendor | Codership Oy | | wsrep_provider_version | 24.3.0(r159) | | wsrep_ready | ON | +----------------------------+--------------------------------------+ 43 rows in set (0.13 sec)

Important: if you are using a load balancer in the cluster, it's now the time to add this node back again to it.

Rolling upgrade the other nodes

You can start doing the rolling upgrade to the other nodes the same like the first one but after making sure that all nodes state (wsrep_local_state_comment) is Synced. If you stopped a node while it's in the Donor state, then the donor and the joiner nodes might be crashed, so make sure of that first.

Get rid of the old release option

After making the upgrade on all the cluster nodes, the backward compatibility option (wsrep_provider_options="socket.checksum=1") is not needed anymore, so removing it from the configuration files and doing a rolling restart on all nodes will do the mission.

Have fun with the new MySQL and Galera releases :)

We had a Galera Cluster support case recently. The customer was drenched in tears because his Galera Cluster did not work any more and he could not make it work any more.

Upsss! What has happened?

A bit of the background of this case: The customer wanted to do a rolling-restart of the Galera Cluster under load because of an Operating System upgrade which requires a reboot of the system.

Lets have a look at the MySQL error log to see what was going on. Customer restarted server with NodeC:

12:20:42 NodeC: normal shutdown --> Group 2/2 12:20:46 NodeC: shutdown complete 12:22:09 NodeC: started 12:22:15 NodeC: start replication 12:22:16 NodeC: CLOSED -> OPEN 12:22:16 all : Group 2/3 component all PRIMARY 12:22:17 NodeC: Gap in state sequence. Need state transfer. 12:22:18 all : Node 1 (NodeC) requested state transfer from '*any*'. Selected 0 (NodeB)(SYNCED) as donor. 12:22:18 NodeB: Shifting SYNCED -> DONOR/DESYNCED (TO: 660966498) 12:22:19 NodeC: Shifting PRIMARY -> JOINER (TO: 660966498) 12:22:19 NodeC: Receiving IST: 14761 writesets, seqnos 660951493-660966254 12:22:21 NodeC: 0 (NodeB): State transfer to 1 (NodeC) complete.

Everything went fine so far NodeC came up again and did an IST as expected. But then the first operational error happened: The customer did not wait to reboot NodeB until NodeC was completely recovered. It seems like NodeC took some time for the IST recovery. This should be checked on all nodes with SHOW GLOBAL STATUS LIKE 'wsrep%';...

12:22:21 NodeC: Member 0 (NodeB) synced with group. 12:22:21 NodeB: Shifting JOINED -> SYNCED (TO: 660966845) 12:22:21 NodeB: Synchronized with group, ready for connections --> NodeC seems not to be ready yet! 12:23:21 NodeB: Normal shutdown 12:23:21 all : Group 1/2 12:23:21 NodeC: Aborted (core dumped)

And now Murphy was acting already the first time: We hit a situation in the Galera Cluster which is not covered as expected. Now we have 2 nodes out of 3 not working. As a result the Cluster gets a quorum loss (non-Primary, more than 50% of nodes disappeared) and does not reply to any SQL queries any more. This is a bug because both nodes left the cluster gracefully. The third node should have stayed primary:

12:23:21 NodeB: Received SELF-LEAVE. Closing connection. 12:23:23 NodeB: Shifting CLOSED -> DESTROYED (TO: 660973981) 12:23:25 NodeB: Shutdown complete 12:23:29 NodeC: mysqld_safe WSREP: sleeping 15 seconds before restart 12:23:37 NodeA: Received NON-PRIMARY. 12:23:44 NodeC: mysqld_safe mysqld restarted 12:23:48 NodeC: Shifting CLOSED -> OPEN 12:23:48 NodeC: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 2 12:23:48 NodeC: Received NON-PRIMARY. 12:23:48 NodeA: New COMPONENT: primary = no, bootstrap = no, my_idx = 1, memb_num = 2 12:23:48 NodeA: Received NON-PRIMARY. 12:24:30 NodeB: mysqld_safe Starting mysqld daemon 12:24:36 NodeB: Start replication 12:24:37 NodeB: Received NON-PRIMARY.

As a result the customer decided to shutdown the whole cluster. Which was not necessary but is a acceptable approach:

12:27:55 NodeB: /usr/sbin/mysqld: Normal shutdown 12:27:58 NodeB: /usr/sbin/mysqld: Shutdown complete 12:28:14 NodeA: /usr/sbin/mysqld: Normal shutdown 12:28:19 NodeA: /usr/sbin/mysqld: Shutdown complete 12:31:45 NodeC: /usr/sbin/mysqld: Normal shutdown 12:31:49 NodeC: /usr/sbin/mysqld: Shutdown complete

We experience a complete cluster outage now. An then the next operational error happened: The customer has chosen the node (NodeC) with the worst (= oldest) data as the starting node for the new Cluster:

12:31:55 NodeC: Starting mysqld daemon 12:31:58 NodeC: PRIMARY, 1/1 12:31:58 NodeC: /usr/sbin/mysqld: ready for connections. 12:33:29 NodeB: mysqld_safe Starting mysqld daemon 12:33:33 NodeB: PRIMARY, 1/2

An alternative approach would have been to run the command SET GLOBAL wsrep_provider_options='pc.bootstrap=yes'; on the node (NodeA) with the most recent data...
After connecting NodeB (with the newer state) requested an state transfer from the older NodeC:

12:33:35 all : Node 1 (NodeB) requested state transfer from '*any*'. Selected 0 (NodeC)(SYNCED) as donor. 12:33:35 NodeC: Shifting SYNCED -> DONOR/DESYNCED (TO: 660982149) 12:33:35 NodeC: IST request --> Should be SST, why IST? 12:33:35 NodeB: Shifting PRIMARY -> JOINER (TO: 660982149) 12:33:35 NodeB: Receiving IST: 7914 writesets, seqnos 660973981-660981895 12:33:36 NodeB: Slave SQL: Could not execute Write_rows event on table test.test; Duplicate entry '8994678' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 102, Error_code: 1062

And now Mister Murphy is acting a second time: We hit another situation: The newer node requests an IST from the older node which has progressed in the meanwhile to an even newer state. So the newer joiner node receives data from the older donor node which causes an AUTO_INCREMENT Primary Key violation. As a consequence the node crashes:

12:33:36 NodeB: receiving IST failed, node restart required: Failed to apply app buffer: äJR#, seqno: 660974010, status: WSREP_FATAL 12:33:36 NodeB: Closed send monitor. 12:33:37 NodeB: Closing slave action queue. 12:33:37 NodeB: Aborted (core dumped) 12:33:37 NodeC: PRIMARY 1/1 12:33:44 NodeC: Shifting DONOR/DESYNCED -> JOINED (TO: 660983204) 12:33:59 NodeB: mysqld_safe mysqld restarted 12:34:04 NodeB: Shifting CLOSED -> OPEN 12:34:07 NodeB: Aborted (core dumped) ... Loop

This situation keeps the node NodeB now in a crashing loop. Restarted by the mysqld_safe process requesting an IST. This is another bug which is fixed in a newer Galera MySQL (5.5.33). And now the next operational error happened: Instead of killing NodeB and forcing an SST by deleting the grastat.dat file They started the third node as well...

12:37:12 NodeA: mysqld_safe Starting mysqld daemon ... --> code dumped ... Loop

NodeB and NodeA both have the same problem now...

As a result: Node NodeA and NodeB are now looping in a crash. But at least the node NodeC was up and running all the time.

Learnings

• Most important: Have an ntpd service running on all Cluster nodes to not mess up the times on different nodes while investigating in errors. This makes problem solving much easier...
• In case of split-brain or quorum loss choose the node with the most recent data as your initial Cluster node.
• If you have a Cluster in split-brain you do not have to restart it. You can bring the node out of split-brain with pc.bootstrap=yes variable if you found out which node is the most recent one.
• Analyse error log files carefully to understand what went wrong. Forcing an SST only takes a few seconds.
• Upgrade your software regularly to not hit old known bugs. The rule Do not touch a running system! does not apply here because we are already touching the running system! So regular upgrade from time to time can be very helpful!
• Be familiar with operational issues of your Cluster software. A Cluster does not only mean high-availability. It means also you have to train your staff to handle it.
• It is always valuable to have support for your business critical systems.

FromDual has the pleasure to announce the release of the new version 1.0 of its popular multi-instance MySQL Environment MyEnv.

Thanks to the countless feedback of many big customers and the community we finally feel ready to publish version 1.0. A major step forward from the last version 0.4 (dated March 2011) and not published version 0.5 (dated August 2013).

What is MyEnv?

MyEnv is a CLI environment for operating multiple MySQL instances on the same server. If you are using mysqld_multi or/and if you have set-up multiple mysqld instances on the same machine you should really have a look at MyEnv.

If you consider to consolidate your MySQL databases on one machine but if you do not want to pack everything in one instance you are a candidate for MyEnv as well...

You can download MyEnv from here.

In the inconceivable case that you find a bug in MyEnv please report it in our Bugtracker.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Upgrade from 0.x to 1.0

Package php-posix needs to be installed.

If the following manual steps are too complicated just run the installer installMyEnv.sh and copy your old myenv.conf to /etc/myenv.

# cd /home/mysql/product # tar xf /tmp/myenv-1.0.tar.gz # sudo mkdir /etc/myenv # sudo chown mysql: /etc/myenv # echo 'export MYENV_BASE=/home/mysql/product/myenv' > /etc/myenv/MYENV_BASE # cp /home/mysql/product/myenv/etc/myenv.conf /etc/myenv/ # rm -f myenv # ln -s myenv-1.y myenv # cp myenv/etc/aliases.conf.template /etc/myenv/aliases.conf # cp myenv/etc/variables.conf.template /etc/myenv/variables.conf # cat > ~/.bash_profile # BEGIN MyEnv # Written by the MyEnv installMyEnv.php script. . /etc/myenv/MYENV_BASE MYENV_PWD=`pwd` cd $MYENV_BASE/bin . myenv.profile cd $MYENV_PWD # END MyEnv _EOF
Changes in MyEnv 1.0 MyEnv

• cd abc xyz alias failed when one of the patterns contained a space. This is fixed now (Bug #10).
• Different smaller bug fixes.
• timezone added to MyEnv to avoid nasty error messages.
• Old path in PATH variables should be cleaned-up properly now.
• Incompatible Change: DEBUG variables replaced by MYENV_DEBUG.
• MyEnv start/stop script (myenv_start_stop.php) accepts as parameter and instance name now (customer enhancement request).
• hideschema parameter implemented for myenv.conf (customer enhancement request).
• Default section is now configurable in section [default] (customer enhancement request).
• Better support for Percona Server (customer enhancement request).
• Readline support added for set-ups not having it by default (customer enhancement request).
• cdb alias added for cd $basedir or cd $MYSQL_HOME.
• Security check for my.cnf introduced.
• Password was shown in clear text in error log (Bug #12).
• tmp directory is added to MyEnv structure.
• unknown version fixed.
• Made hideschema variable inheritable.
• Parameter drizzle and type removed. De-support for Drizzle.
• The utl directory should be added to the PATH as well.
• Add Path to the right of PATH is fixed now.
• Possibility to start mysqld without angel process (mysqld_safe) now.
• /tmp/myEnv.xxx files where not removed. This is fixed now.
• 5.6.12 as version pattern is now accepted (customer request).
• Incompatible Change: Configuration file (myenv.conf) is now relocated from $MYENV_HOME/etc to /etc/myenv.
• MySQL stop functionality was changed from mysqladmin shutdown to kill (MySQL like).
• my.cnf privilege check is less restrictive now (rwxr-----).

MyEnv Installer

• Many bug fixes and improvements for MyEnv Installer.
• Incompatible Change: MyEnv Installer rewritten, renamed (bin/installMyEnv.php) and code cleaned-up.
• MySQL database can now be created in installer.
• Check for mysql user is added to installer.
• Missing mysql_install_db is caught.
• Missing socket error is caught.
• Only a stopped database can be deleted.
• Configuration files are versioned when changed.
• Delete process more user-friendly.
• Missing basedir and datadir variables in my.cnf fixed.
• Privileges of my.cnf file set correctly to avoid warning later.
• Not existing my.cnf file caught.
• Selection characters made more intuitive.
• Empty my.cnf string caught.
• Installer is aware of missing environment variables.
• Start/stop instance parameter was added in installer.

MyEnv Utilities

• Row Extractor (utl/row_extractor.pl) to extract rows from corrupt tables (leading to a DB crash) added.
• Script to flush Query Cache regularly (utl/flush_query_cache.sh) added.
• Script to find last access to tables (utl/filesystem_table.php) added.
• MySQL Backup Manager (mysql_bman) added.
• Script to do a NDB Cluster channel fail-over (utl/channel_failover.pl) added.
• README about MyEnv utilities usage updated (utl/README).
• Compare script to compare two GLOBAL STATUS outputs added (utl/compare.php).
• Rotate log script to rotate different MySQL logs added (utl/rotate_log.sh).
• Script to convert \G output of MySQL client to one-line output added (utl/backslashG2table.pl).
• alter_engine.sh script improved by a Perl version (for windows customer) (utl/alter_engine.pl).
• Different resources, configuration files and monitors added for Heartbeat v1/v2: (utl/{stop-heartbeat.alert|ping.monitor|node.monitor|mysql.monitor|mail.alert|Crontab|canias}, etc/{drbd.conf.template|ha.cf.template|mon.cf.template|my.cnf.template}).)
• Slave Monitor added (utl/{slave_monitoring.php|slave_monitor.php}).
• DRBD Monitor added (utl/check_drbd.sh).
• MySQL Profiler added (utl/profiler/{profiler.pl|tracer.pl}).
• split_partition.php and drop_partition.php scripts added (utl/{split_partition.php|drop_partition.php}).
• Insert test added (utl/{insert_test.sh|insert_test.phpx}). Shell script is for connection testing. PHP script is more for INSERT load testing...
• Utility mem_map.pl added (utl/mem_map.pl).
• Start/stop script for VIP added (utl/vip).
• Script to fix wrong encoding added (utl/fix_encoding.php).
• Alter Engine script enhanced with MySQL 5.6 and Galera features.
• Ping Log utility added (utl/ping_log.sh).
• Script added to block MySQL port so Load Balancer recognizes if Galera Cluster node is away (utl/block_galera_node.sh, customer request).
• Create table statement for test.test table for insert_test.* added as comment to the script.

MySQL Backup Manager (mysql_bman)

• Several bugs fixed and improvements.
• Schema dump implemented now (customer enhancement request).
• Backup is now possible with MySQL 5.5 as well (Bug #31).
• All parameters can now be put in the configuration file.
• Configuration file is now more dynamic.
• Archive job fixed.
• Configuration backup fixed.
• Xtrabackup wrapper added.
• Empty schema option defaults now to all databases.
• Per schema backup should not contain drop/create database.

Have fun,
The FromDual Consulting Team

FromDual holds its annual company meeting this year in Leoforos Vravronos near Athens (20 km outside) in Greece.

We are happy to invite everybody interested in MySQL technologies (MySQL, Percona Cluster, MariaDB, Galera Cluster, etc.) to participate Wednesday evening October 9th at the Mare Nostrum Hotel in Leoforos Vravronos for exchanging ideas about MySQL.

Meeting at 17:00 in the hotel lobby.

• Presentation about Xtrabackup (25') and 5' Questions and Answers (Abdel-Mawla Gharieb, Support Engineer at FromDual)
• Presentation of a MySQL community member (25') and 5' Questions and Answers
• Break 15 min
• Presentation about Galera Cluster (25') and 5' Questions and Answers (Oli Sennhauser, CTO of FromDual))
• Presentation of a MySQL community member or discussion and questions about MySQL (25') and 5' Questions and Answers
• Dinner at the Mare Nostrum Hotel Restaurant

Please feel free to send us your suggestion about your presentation. Any technical or non-technical MySQL related topic is welcome. For example how you use MySQL in your companies or special problems you have faced and solved (or not solved yet), research work you have done on MySQL products. Business cases you solve with MySQL products, Evaluations or experience you have made. The proposal can be sent to contac@fromdual.com.

Please also let us know when you plan to participate at contact@fromdual.com. So we can arrange and organize all the infrastructure with the Hotel.

The event is free of costs for all participants.

Best Regards,
Your FromDual Team

In MySQL we have the typical behaviour that we open and close connections very often and rapidly. So we have very short-living connections to the server. This can lead in extreme cases to the situation that the maximum number of TCP ports are exhausted.

The maximum number of TCP ports we can find with:

# cat /proc/sys/net/ipv4/ip_local_port_range 32768 61000

In this example we can have in maximum (61000 - 32768 = 28232) connections concurrently open.

When a TCP connections closes the port cannot be reused immediately afterwards because the Operating System has to wait for the duration of the TIME_WAIT interval (maximum segment lifetime, MSL). This we can see with the command:

# netstat -nat Active Internet connections (servers and established) Proto Recv-Q Send-Q Local Address Foreign Address State tcp 0 0 0.0.0.0:10050 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:10051 0.0.0.0:* LISTEN tcp 0 0 127.0.0.1:10051 127.0.0.1:60756 TIME_WAIT tcp 0 0 127.0.0.1:10050 127.0.0.1:50191 TIME_WAIT tcp 0 0 127.0.0.1:10050 127.0.0.1:52186 ESTABLISHED tcp 0 0 127.0.0.1:10051 127.0.0.1:34445 TIME_WAIT

The reason for waiting is that packets may arrive out of order or be retransmitted after the connection has been closed. CLOSE_WAIT indicates that the other side of the connection has closed the connection. TIME_WAIT indicates that this side has closed the connection. The connection is being kept around so that any delayed packets can be matched to the connection and handled appropriately.

The Maximum Segment Lifetime can be found as follows:

# cat /proc/sys/net/ipv4/tcp_fin_timeout 60

This basically means your system cannot guarantee more than ((61000 - 32768) / 60 = 470) ports at any given time.

Solutions

There are several strategies out of this problem:

• Open less frequently connections to your MySQL database. Put more payload into one connection. Often Connection Pooling is used to achieve this.
• Increasing the port range. Setting the range to 15000 61000 is pretty common these days (extreme tuning: 1024 - 65535).
• Increase the availability by decreasing the FIN timeout.

Those values can be changed online with:

# echo 30 > /proc/sys/net/ipv4/tcp_fin_timeout # echo 15000 65000 > /proc/sys/net/ipv4/ip_local_port_range

Or permanently by adding it to /etc/sysctl.conf

An other possibility to change this behaviour is to use tcp_tw_recycle and tcp_tw_reuse. By default they are disabled:

# cat /proc/sys/net/ipv4/tcp_tw_recycle 0 # cat /proc/sys/net/ipv4/tcp_tw_reuse 0

These parameters allow fast cycling of sockets in TIME_WAIT state and re-using them. But before you do this change make sure that this does not conflict with the protocols that you would use for the application that needs these ports.

The tcp_tw_recycle could cause some problems when using load balancers:

tcp_tw_reuse Allow to reuse TIME_WAIT sockets for new connections when it is safe from protocol viewpoint. Default value is 0.
It should not be changed without advice/request of technical experts. tcp_tw_recycle Enable fast recycling TIME_WAIT sockets. Default value is 0. It should not be changed without advice/request of technical experts.
Literature

• TCP State Transition Diagram
• Transmission Control Protocol
• TIME_WAIT and its design implications for protocols and scalable client server systems
• Linux documentation about network parameters

Codership hat heute den Galera Cluster 3.0-beta (24.3.0) basierend auf MySQL 5.6 sowie Galera 2.7 (24.2.7) basierend auf MySQL 5.5 freigeben!

Wichtigste neue Features:

• Galera für MySQL 5.6
• Nutzung der MySQL Global Transaction ID (GTID)
• Cluster-Segmentierung optimiert für WAN Umgebungen
• Nutzung unterschiedlicher Binary-Log Channels für Replikation-Slaves ist jetzt einfacher.
• Optimierung der Kommunikation für grosse Transaktionen.

Download here.

Codership hat heute den Galera Cluster 3.0-beta (24.3.0) basierend auf MySQL 5.6 sowie Galera 2.7 (24.2.7) basierend auf MySQL 5.5 freigeben!

Wichtigste neue Features:

• Galera für MySQL 5.6
• Nutzung der MySQL Global Transaction ID (GTID)
• Cluster-Segmentierung optimiert für WAN Umgebungen
• Nutzung unterschiedlicher Binary-Log Channels für Replikation-Slaves ist jetzt einfacher.
• Optimierung der Kommunikation für grosse Transaktionen.

Download here.

Codership hat heute den Galera Cluster 3.0-beta (24.3.0) basierend auf MySQL 5.6 sowie Galera 2.7 (24.2.7) basierend auf MySQL 5.5 freigeben!

Wichtigste neue Features:

• Galera für MySQL 5.6
• Nutzung der MySQL Global Transaction ID (GTID)
• Cluster-Segmentierung optimiert für WAN Umgebungen
• Nutzung unterschiedlicher Binary-Log Channels für Replikation-Slaves ist jetzt einfacher.
• Optimierung der Kommunikation für grosse Transaktionen.

Download here.

Was für die Oracle Datenbank der Real Application Cluster (RAC) ist, ist für MySQL der Galera Cluster.

Nachdem der Galera-Cluster Workshop an den /ch/open Workshop-Tagen 2013 ein voller Erfolg war, haben Sie jetzt noch mal die Chance, sich bis Ende September (30. September) für den vollen 2-tägigen Galera-Cluster Kurs vom 17. und 18. Oktober in Zürich anzumelden.

In diesem Kurs lernen Sie, wie ein Galera Cluster aufgesetzt, sauber konfiguriert und betrieben wird. Zusammen bauen wir einen Galera-Cluster auf und spielen die wichtigsten Szenarios durch. Zudem zeigen wir Ihnen zahlreiche Galera-Tricks und -Kniffe, welche Sie sonst so nicht finden werden.

Dieser Kurs ist weltweit einzigartig und wird zur Zeit nirgends sonst angeboten.

Taxonomy upgrade extras: galeraclustertrainingschulung

Was für die Oracle Datenbank der Real Application Cluster (RAC) ist, ist für MySQL der Galera Cluster.

Nachdem der Galera-Cluster Workshop an den /ch/open Workshop-Tagen 2013 ein voller Erfolg war, haben Sie jetzt noch mal die Chance, sich bis Ende September (30. September) für den vollen 2-tägigen Galera-Cluster Kurs vom 17. und 18. Oktober in Zürich anzumelden.

In diesem Kurs lernen Sie, wie ein Galera Cluster aufgesetzt, sauber konfiguriert und betrieben wird. Zusammen bauen wir einen Galera-Cluster auf und spielen die wichtigsten Szenarios durch. Zudem zeigen wir Ihnen zahlreiche Galera-Tricks und -Kniffe, welche Sie sonst so nicht finden werden.

Dieser Kurs ist weltweit einzigartig und wird zur Zeit nirgends sonst angeboten.

Taxonomy upgrade extras: galeraclustertrainingschulung

Taxonomy upgrade extras: galeraclustertrainingschulung

Was für die Oracle Datenbank der Real Application Cluster (RAC) ist, ist für MySQL der Galera Cluster.

Nachdem der Galera-Cluster Workshop an den /ch/open Workshop-Tagen 2013 ein voller Erfolg war, haben Sie jetzt noch mal die Chance, sich bis Ende September (30. September) für den vollen 2-tägigen Galera-Cluster Kurs vom 17. und 18. Oktober in Zürich anzumelden.

In diesem Kurs lernen Sie, wie ein Galera Cluster aufgesetzt, sauber konfiguriert und betrieben wird. Zusammen bauen wir einen Galera-Cluster auf und spielen die wichtigsten Szenarios durch. Zudem zeigen wir Ihnen zahlreiche Galera-Tricks und -Kniffe, welche Sie sonst so nicht finden werden.

Dieser Kurs ist weltweit einzigartig und wird zur Zeit nirgends sonst angeboten.

Was für die Oracle Datenbank der Real Application Cluster (RAC) ist, ist für MySQL der Galera Cluster.

Nachdem der Galera-Cluster Workshop an den /ch/open Workshop-Tagen 2013 schnell ausgebucht war haben Sie jetzt noch mal die Chance, sich bis Ende dieser Woche (13. September) für den vollen 2-tägigen Galera-Cluster Kurs vom 23. und 24. September in Essen anzumelden.

In diesem Kurs lernen Sie, wie ein Galera Cluster aufgesetzt, sauber konfiguriert und betrieben wird. Zudem zeigen wir Ihnen zahlreiche Galera-Tricks und -Kniffe, welche Sie sonst so nicht finden werden.

Dieser Kurs ist weltweit einmalig und wird zur Zeit nirgends sonst angeboten.

Was für die Oracle Datenbank der Real Application Cluster (RAC) ist, ist für MySQL der Galera Cluster.

Nachdem der Galera-Cluster Workshop an den /ch/open Workshop-Tagen 2013 schnell ausgebucht war haben Sie jetzt noch mal die Chance, sich bis Ende dieser Woche (13. September) für den vollen 2-tägigen Galera-Cluster Kurs vom 23. und 24. September in Essen anzumelden.

In diesem Kurs lernen Sie, wie ein Galera Cluster aufgesetzt, sauber konfiguriert und betrieben wird. Zudem zeigen wir Ihnen zahlreiche Galera-Tricks und -Kniffe, welche Sie sonst so nicht finden werden.

Dieser Kurs ist weltweit einmalig und wird zur Zeit nirgends sonst angeboten.

Was für die Oracle Datenbank der Real Application Cluster (RAC) ist, ist für MySQL der Galera Cluster.

Nachdem der Galera-Cluster Workshop an den /ch/open Workshop-Tagen 2013 schnell ausgebucht war haben Sie jetzt noch mal die Chance, sich bis Ende dieser Woche (13. September) für den vollen 2-tägigen Galera-Cluster Kurs vom 23. und 24. September in Essen anzumelden.

In diesem Kurs lernen Sie, wie ein Galera Cluster aufgesetzt, sauber konfiguriert und betrieben wird. Zudem zeigen wir Ihnen zahlreiche Galera-Tricks und -Kniffe, welche Sie sonst so nicht finden werden.

Dieser Kurs ist weltweit einmalig und wird zur Zeit nirgends sonst angeboten.

Taxonomy upgrade extras: galerastartstop

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

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 the hardware load balancer does not see anybody listening on port 3306 any more and assumes that this IP address should not be served any more.

We also learned this week that the REJECT rule is better than the DROP rule when we want to have fast response time for immediate elimination of traffic.

The script block_galera_node.sh has to be hooked as before into the wsrep_notify_cmd variable and an additional sudoers rule has to be added for the mysql user.

# # /etc/sudoers.d/mysql # chmod 0440 # mysql ALL = (root) NOPASSWD: /sbin/iptables

We are interested to hear your experience and your opinion about this approach.

Wir freuen uns, Ihnen unsere Galera Cluster für MySQL Schulung anbieten zu können. Diese MySQL Schulung bieten wir erstmals an und sie ist weltweit bisher einzigartig.

Die nächsten Schulungstermine sind am 23. und 24. September 2013 in Essen (Deutschland) sowie am 17. und 18. Oktober 2013 in Zürich (Schweiz).

Galera Cluster für MySQL ist eine synchrone Multi-Master Replikation für MySQL basierend auf der InnoDB Storage Engine. Mit dieser Lösung erreichen Sie 99.999% Hochverfügbarkeit. Updates sind im laufenden Betrieb möglich.

Weitere Informationen, Termine und eine Möglichkeit sich anzumelden finden Sie hier.

Ihr FromDual Schulungsteam

Wir freuen uns, Ihnen unsere Galera Cluster für MySQL Schulung anbieten zu können. Diese MySQL Schulung bieten wir erstmals an und sie ist weltweit bisher einzigartig.

Die nächsten Schulungstermine sind am 23. und 24. September 2013 in Essen (Deutschland) sowie am 17. und 18. Oktober 2013 in Zürich (Schweiz).

Galera Cluster für MySQL ist eine synchrone Multi-Master Replikation für MySQL basierend auf der InnoDB Storage Engine. Mit dieser Lösung erreichen Sie 99.999% Hochverfügbarkeit. Updates sind im laufenden Betrieb möglich.

Weitere Informationen, Termine und eine Möglichkeit sich anzumelden finden Sie hier.

Ihr FromDual Schulungsteam

Wir freuen uns, Ihnen unsere Galera Cluster für MySQL Schulung anbieten zu können. Diese MySQL Schulung bieten wir erstmals an und sie ist weltweit bisher einzigartig.

Die nächsten Schulungstermine sind am 23. und 24. September 2013 in Essen (Deutschland) sowie am 17. und 18. Oktober 2013 in Zürich (Schweiz).

Galera Cluster für MySQL ist eine synchrone Multi-Master Replikation für MySQL basierend auf der InnoDB Storage Engine. Mit dieser Lösung erreichen Sie 99.999% Hochverfügbarkeit. Updates sind im laufenden Betrieb möglich.

Weitere Informationen, Termine und eine Möglichkeit sich anzumelden finden Sie hier.

Ihr FromDual Schulungsteam