Feed aggregator

Taxonomy upgrade extras: galeraclusterMySQL ClusterHigh Availability

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" VIP="192.168.0.99" PORT="3306" LBUSER="galera" LBUSER="root" ETC="/etc/mysql/conf.d/wsrep.cnf" ETC="/home/mysql/data/mysql-5.5-wsrep-23.7-a/my.cnf" MYIP='' WEIGHT="100" DATE=$(date '+%Y-%m-%d %H:%M:%S') echo $DATE >>$LOG regex='^.*=\s*([0-9]+.[0-9]+.[0-9]+.[0-9]+).*' str=$(grep "^wsrep_node_incoming_address" $ETC 2>>$LOG) if [[ $str =~ $regex ]] ; then MYIP=${BASH_REMATCH[1]} else echo "Cannot find IP address in $str" >>$LOG exit 1 fi while [ $# -gt 0 ] ; do case $1 in --status) STATUS=$2 shift ;; --uuid) CLUSTER_UUID=$2 shift ;; --primary) PRIMARY=$2 shift ;; --index) INDEX=$2 shift ;; --members) MEMBERS=$2 shift ;; esac shift done # echo $* >> $LOG echo $STATUS >> $LOG # Undefined means node is shutting down # Synced means node is ready again if [ "$STATUS" != "Synced" ] ; then cmd="ssh $LBUSER@$LBIP 'sudo /sbin/ipvsadm -e -t $VIP:$PORT -r $MYIP -w 0'" else cmd="ssh $LBUSER@$LBIP 'sudo /sbin/ipvsadm -e -t $VIP:$PORT -r $MYIP -w $WEIGHT'" fi echo $cmd >>$LOG eval $cmd >>$LOG 2>&1 echo "ret=$?" >>$LOG exit 0

We assume that the same script can be used with little modifications for the Galera Load Balancer as well.

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 flush_time variable added - Read_buffer_size and max_allowed_package conflict implemented - Binlog_cache_size too small trigger back-ported from live - Read_buffer_size rule added - Max_allowed_packet and read_buffer_size added for rules - All 24 cores added to template items - Process memory graph with RAM size - Thread_stack_size too small alert implemented - Network packet graph reordered - Trigger for missing MaaS agent added - Server screens visually improved - Screens for Galera, network, CPU and server added - Transfer of data over HTTPS is possible now.

Download and Install

The most recent FromDual Performance Monitor for MySQL you can download from here... Or for more information you can reach us by e-mail at: contact@fromdual.com or by phone on +41 44 940 24 82.

Der neue Release des MySQL Performance Monitors (mpm) ist herausgekommen!

Neuerungen und Verbesserungen

• Einfach zu nutzende Vorlagen
• Verbesserte Sicherheit
• Neue Checks
• Neue Warnungen
• Zeitzonenverschiebung
• Neue Screens
• Datentransfer hinzugekommen

und vieles mehr (siehe weiter unten).

Der MySQL Performance Monitor (mpm) für MySQL, Galera Cluster, Percona Server und MariaDB ist eine Überwachungslösung basierend auf dem Enterprise open source Monitor Zabbix.

Er beinhaltet all unentbehrlichen Module für das detaillierte überwachen von MySQL Performance-Metriken welche graphisch angezeigt werden können.

Neue Funktionen

Das Konfigurationstemplate wurde vereinfacht - Security Modul hinzugefügt - Slave und MySQL Templates aufgrund von Kundenrückmeldungen verbessert - Innodb Log und pending I/O Informationen hinzugefügt - Sort_buffer_size Trigger hinzugefügt - Slave error skipped Trigger hinzugefügt - Check für Isolation Level hinzugefügt - Binlog do und ignore Filter Warnungen eingeschaltet - Innodb Deadlock Trigger von Warnung auf Info herabgestuft - Timeshift implementiert - Flush_time Trigger hinzugefügt - MyISAM flush_time Variable hinzugefügt - Read_buffer_size und max_allowed_package Konfliktwarnung implementiert - Binlog_cache_size zu klein Trigger implementiert - Read_buffer_size Regel hinzugefügt - Max_allowed_packet und read_buffer_size für Regel hinzugefügt - Process-Speicher Graph durch RAM Grösse erweitert - Thread_stack_size zu klein Alert implementiert - Netzwerkpakete Graph umsortiert - Trigger für fehlenden MaaS Agenten hinzugefügt - Server Screens optisch verbessert - Screens für Galera, Netzwerk, CPU und Server hinzugefügt - Transfer der Daten über HTTPS ist jetzt möglich.

Herunterladen und Installieren

Den allerneuste FromDual Performance Monitor für MySQL können Sie hier herunterladen...

Für weitere Informationen können Sie uns via e-Mail erreichen unter: contact@fromdual.com oder via Telefon unter +41 44 940 24 82.

Wir brauchen Dich: MySQL DBA für FromDual Support Dienste

FromDual sucht enthusiastische und erfahrene Mitarbeiter die:

• detaillierte Kenntnisse über MySQL, Percona Server oder MariaDB aufweisen
• mit dem Open-Source Ökosystem vertraut sind
• als DBA oder DevOps wissen, wie man Datenbank-Systeme betreibt
• verstehen, was beim Betrieb von Datenbanken falsche gemacht werden kann
• gerne selbständig remote arbeiten und über IRC, Skype, Mail und Telefon zu kommunizieren gewohnt sind
• sich auf Linux Systemen wohl fühlen
• gute Team-Player sind und zum Wachstum der Firma beitragen wollen
• gerne den direkten Kontakt mit Kunden haben und
• auf der Suche nach einer neuen Herausforderung sind

Stellenbeschreibung

Wir suchen Vollzeit-MySQL Support DBA's (Sie oder Ihn), welche primär für unsere MySQL Support Dienstleistungen zuständig sind und unseren Kunden helfen, ihre MySQL Datenbanken zu betreiben (remote-DBA und Notfall-Interventionen).

Du bist fit in MySQL und:

• hast Erfahrung im Betrieb kritischer und hoch verfügbarer produktiver MySQL Datenbanken hauptsächlich auf Linux.
• Deine tägliche Arbeit ist MySQL-Replikation in allen Variationen.
• weisst, wie die meist verbreitetsten MySQL HA Setups funktionieren und wie man sie wieder effizient repariert, wenn ein Problem auftritt. (Wenn Du bereits Erfahrungen mit Galera Cluster gesammelt hast, ist das ein Vorteil!)
• kennst die gängigen Open-Source Technologien (LAMP Stack, etc.)
• kannst Bash skripten und einfache Programme in mindestens einer verbreiteten Programmier-/Skripting-Sprache (Perl, PHP, ...) erstellen.

Du wirst im direkten Kontakt mit Kunden stehen. Du hast ein gutes Gespür für deren Probleme und kannst zuhören, weisst wie antworten und findest die eigentlichen Probleme. Du wirst proaktiv handeln, bevor etwas passiert und den Kunden wieder auf den richtigen Pfad führen.
Du bist ein guter Kommunikator und ein aktiver Team Player.

Um Deine Arbeit erledigen zu können, arbeitest Du in einer Europäischen Zeitzone. Deine Arbeitszeit kannst Du in bestimmten Grenzen flexibel gestalten. Wir erwarten, dass Du Deinen Beitrag zum Bereitschaftsdienst leistest. FromDual ist eine vollständig virtuelle Firma. Ein Umzug ist daher nicht notwendig (Home-Office). Gute schriftliche und mündliche Englischkenntnisse sind zwingend. Die meisten unserer Kunden sind deutschsprachig. Deutschkenntnisse sind daher von Vorteil.

Neben Deiner Tätigkeit als Support DBA erwarten wir, dass Du Dir laufend neue Kenntnisse aneignest und Deine Fähigkeiten verbesserst sowie dazu beiträgst, unsere Monitoring-Lösung, unsere Datenbank-Steuerung und unseren weiteren Tools zu verbessern. Im weiteren erwarten wir, dass Du regelmässig zur Verfassung technischer Artikel (Blog oder Zeitschriften) beiträgst und überall mit hilfst, wo Hilfe nötig ist...

Du solltest in der Lage sein, die meiste Zeit selbständig zu arbeiten, denken und zu handeln und Dir neues Wissen selbständig anzueignen (durch Google, die MySQL Dokumentation, Ausprobieren, etc.). Wenn Du mal nicht weiterkommst, werden Dir Deine Kollegen von FromDual helfen.

Wenn Du jemanden brauchst, der Dir die ganze Zeit Dein Händchen hält, ist FromDual nicht die richtige Wahl.

Wer ist FromDual?

FromDual ist die führende unabhängige MySQL Beratungs- und Dienstleistungs-Firma in Europa mit ihrem Hauptsitz in der Schweiz.

Unsere Kunden befinden sich hauptsächlich in Europa und reichen vom kleinen Start-Up bis zur europäischen Top-500 Firma.

Du wirst in einer spannenden Zeit zu uns stossen. Wir sind am wachsen und brauchen die entsprechenden Leute, welche selbst und mit uns wachsen wollen. In dem Mass, wie wir uns weiter entwickeln, muss auch unser Team wachsen uns seine Fähigkeiten erweitern.

Sich bei uns zu bewerben, kann kann Deine beste Entscheidung sein.

Wie geht's weiter

Wenn Du an dieser Chance interessiert bist und Du denkst, dass Du die passende Kandidatin oder der passende Kandidat bist (wir wissen, dass es niemanden gibt, der 100% auf diese Stellenbeschreibung passt!), würden wir uns freuen, von Dir zu hören.

Bitte schicke Deinen ungeschönten Lebenslauf mit Deinen Lohnvorstellungen und einer Liste Deiner Open-Source Beiträgen, Blog-Artikel, Vorträgen, Tweets etc. an jobs@fromdual.com. Wenn Du mehr über diese Stelle erfahren oder wenn Du mit mir persönlich sprechen möchtest, ruf mich bitte an unter +41 79 830 09 33 (Oli Sennhauser, CTO). Bitte nur Bewerber, KEINE Headhunter!

Nachdem wir Deinen Lebenslauf erhalten und geprüft haben, laden wir Dich ein, Deine technischen Fähigkeiten in einem kleinen MySQL-Test unter Beweis zu stellen. Wenn Du den Test bestanden hast, laden wir Dich für die finalen Interviews ein.

Dieses Stellenangebot ist offen bis 31. Mai 2013

Wir brauchen Dich: MySQL DBA für FromDual Support Dienste

FromDual sucht enthusiastische und erfahrene Mitarbeiter die:

• detaillierte Kenntnisse über MySQL, Percona Server oder MariaDB aufweisen
• mit dem Open-Source Ökosystem vertraut sind
• als DBA oder DevOps wissen, wie man Datenbank-Systeme betreibt
• verstehen, was beim Betrieb von Datenbanken falsche gemacht werden kann
• gerne selbständig remote arbeiten und über IRC, Skype, Mail und Telefon zu kommunizieren gewohnt sind
• sich auf Linux Systemen wohl fühlen
• gute Team-Player sind und zum Wachstum der Firma beitragen wollen
• gerne den direkten Kontakt mit Kunden haben und
• auf der Suche nach einer neuen Herausforderung sind

Stellenbeschreibung

Wir suchen Vollzeit-MySQL Support DBA's (Sie oder Ihn), welche primär für unsere MySQL Support Dienstleistungen zuständig sind und unseren Kunden helfen, ihre MySQL Datenbanken zu betreiben (remote-DBA und Notfall-Interventionen).

Du bist fit in MySQL und:

• hast Erfahrung im Betrieb kritischer und hoch verfügbarer produktiver MySQL Datenbanken hauptsächlich auf Linux.
• Deine tägliche Arbeit ist MySQL-Replikation in allen Variationen.
• weisst, wie die meist verbreitetsten MySQL HA Setups funktionieren und wie man sie wieder effizient repariert, wenn ein Problem auftritt. (Wenn Du bereits Erfahrungen mit Galera Cluster gesammelt hast, ist das ein Vorteil!)
• kennst die gängigen Open-Source Technologien (LAMP Stack, etc.)
• kannst Bash skripten und einfache Programme in mindestens einer verbreiteten Programmier-/Skripting-Sprache (Perl, PHP, ...) erstellen.

Du wirst im direkten Kontakt mit Kunden stehen. Du hast ein gutes Gespür für deren Probleme und kannst zuhören, weisst wie antworten und findest die eigentlichen Probleme. Du wirst proaktiv handeln, bevor etwas passiert und den Kunden wieder auf den richtigen Pfad führen.
Du bist ein guter Kommunikator und ein aktiver Team Player.

Um Deine Arbeit erledigen zu können, arbeitest Du in einer Europäischen Zeitzone. Deine Arbeitszeit kannst Du in bestimmten Grenzen flexibel gestalten. Wir erwarten, dass Du Deinen Beitrag zum Bereitschaftsdienst leistest. FromDual ist eine vollständig virtuelle Firma. Ein Umzug ist daher nicht notwendig (Home-Office). Gute schriftliche und mündliche Englischkenntnisse sind zwingend. Die meisten unserer Kunden sind deutschsprachig. Deutschkenntnisse sind daher von Vorteil.

Neben Deiner Tätigkeit als Support DBA erwarten wir, dass Du Dir laufend neue Kenntnisse aneignest und Deine Fähigkeiten verbesserst sowie dazu beiträgst, unsere Monitoring-Lösung, unsere Datenbank-Steuerung und unseren weiteren Tools zu verbessern. Im weiteren erwarten wir, dass Du regelmässig zur Verfassung technischer Artikel (Blog oder Zeitschriften) beiträgst und überall mit hilfst, wo Hilfe nötig ist...

Du solltest in der Lage sein, die meiste Zeit selbständig zu arbeiten, denken und zu handeln und Dir neues Wissen selbständig anzueignen (durch Google, die MySQL Dokumentation, Ausprobieren, etc.). Wenn Du mal nicht weiterkommst, werden Dir Deine Kollegen von FromDual helfen.

Wenn Du jemanden brauchst, der Dir die ganze Zeit Dein Händchen hält, ist FromDual nicht die richtige Wahl.

Wer ist FromDual?

FromDual ist die führende unabhängige MySQL Beratungs- und Dienstleistungs-Firma in Europa mit ihrem Hauptsitz in der Schweiz.

Unsere Kunden befinden sich hauptsächlich in Europa und reichen vom kleinen Start-Up bis zur europäischen Top-500 Firma.

Du wirst in einer spannenden Zeit zu uns stossen. Wir sind am wachsen und brauchen die entsprechenden Leute, welche selbst und mit uns wachsen wollen. In dem Mass, wie wir uns weiter entwickeln, muss auch unser Team wachsen uns seine Fähigkeiten erweitern.

Sich bei uns zu bewerben, kann kann Deine beste Entscheidung sein.

Wie geht's weiter

Wenn Du an dieser Chance interessiert bist und Du denkst, dass Du die passende Kandidatin oder der passende Kandidat bist (wir wissen, dass es niemanden gibt, der 100% auf diese Stellenbeschreibung passt!), würden wir uns freuen, von Dir zu hören.

Bitte schicke Deinen ungeschönten Lebenslauf mit Deinen Lohnvorstellungen und einer Liste Deiner Open-Source Beiträgen, Blog-Artikel, Vorträgen, Tweets etc. an jobs@fromdual.com. Wenn Du mehr über diese Stelle erfahren oder wenn Du mit mir persönlich sprechen möchtest, ruf mich bitte an unter +41 79 830 09 33 (Oli Sennhauser, CTO). Bitte nur Bewerber, KEINE Headhunter!

Nachdem wir Deinen Lebenslauf erhalten und geprüft haben, laden wir Dich ein, Deine technischen Fähigkeiten in einem kleinen MySQL-Test unter Beweis zu stellen. Wenn Du den Test bestanden hast, laden wir Dich für die finalen Interviews ein.

Dieses Stellenangebot ist offen bis 31. Mai 2013

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 set-up's and how to fix them efficiently if problems occur. (If you are already experienced in running Galera Cluster this would be a plus!
• Open Source Technologies (LAMP stack, etc.)
• Bash scripting and you can do some simple programs in at least one popular programming/scripting language (Perl, PHP, ...).

You will be in direct contact with the customers and you need good antennae to listen to them, know how to respond and get the answers to their real problems. You also have to be proactive when something goes wrong and direct the customer back to the right track.

You need to have good communication skills and be an active team player.

To fulfil your job you have to work in European Time Zones. You can organize your working time flexible within certain ranges. Participating in the on call duty is expected. FromDual is a completely virtual company and relocation is not needed (home office). Good English verbally and in writing is a must. Most of our current customers speak German and having German skills is a plus.

Beside being our support engineer we expect you to improve your knowledge and skills and to contribute to improving our monitoring solution, our database controlling solution and our other tools. Further, we expect that you write regular technical articles and give help wherever it is needed or requested...

You should be prepared to work, think and act autonomously most of the time and to teach yourself (using Google, MySQL documentation, testing etc.). If you are ever stuck, your colleagues at FromDual will assist you.

If you need somebody holding your hand all the time, FromDual is not a good choice for you.

Who is FromDual?

FromDual is the leading independent and professional MySQL database consulting and service company in Europe with its Headquarters in Switzerland.

Our customers are mostly located in Europe and range from small start-up companies to some of the top-500 companies of Europe.

You will be joining us at an exciting time. We are growing and we need like-minded people to grow with us, individually and collectively. As our horizons expand, we need our team to expand in its skills, knowledge and expertise.

Applying to join FromDual could be the best decision you make.

How to continue

If you are interested in this opportunity and if you feel you are a good "fit" (we know that there will not be a 100% match!) we would be glad to hear from you.

Please send your true CV with your salary expectation and a list of your open source involvements, blog articles, slides, tweets etc. to jobs@fromdual.com. If you want to know more about this job opportunity or if you want to speak with me, please call me at +41 79 830 09 33 (Oli Sennhauser, CTO). Only candidates, NO head hunters please!

After we received and screened your CV we will invite you to prove your technical skills by taking an exam in operating MySQL. If you pass the exam you will be invited for the final interviews.

This job opportunity is open until May 31st 2013.

Taxonomy upgrade extras: galeramyisaminnodbstorage engine

Switching from MySQL/MyISAM to Galera Cluster requires that all tables (except those from the mysql, information_schema and performance_schema) are using the InnoDB Storage Engine.

For altering the Storage Engine of the tables we wrote a script (alter_engine.pl) long time ago already. Because we have made many of those switches recently we have extended its functionality.

New features

• Recognizes VIEW's and does NOT try to alter their Storage Engine (bug).
• Script is MySQL version aware. Complain if too old MySQL version is used.
• Find tables without a Primary Key.
• Check for too long InnoDB Primary Keys
• Check for FULLTEXT indexes in MySQL 5.1 and 5.5 and write a note if version is older.

Example ./alter_engine.pl User [root] : Password [] : secret Schema from (or all) [test] : all Engine to [InnoDB] : Version is : 5.6.10 MR Version is: 050610 The following tables might not have a Primary Key: +--------------+----------------------+ | table_schema | table_name | +--------------+----------------------+ | test | innodb_table_monitor | | test | log_event | | test | parent | | test | t | +--------------+----------------------+ The tables above not having a Primary Key will negatively affect perfor- mance and data consistency in MySQL Master/Slave replication and Galera Cluster replication. The following tables might have a too long Primary Key for InnoDB (> 767 bytes): +--------------+------------+-------------+ | table_schema | table_name | column_name | +--------------+------------+-------------+ | test | test | data | +--------------+------------+-------------+ The following tables might have a FULLTEXT index (which is only supported in MySQL 5.6 and newer): +--------------+------------+-------------+ | table_schema | table_name | column_name | +--------------+------------+-------------+ | test | test | data | +--------------+------------+-------------+ Output written to /tmp/alter_table_all.sql After reviewing it you can apply it with mysql --user=root --password=secret

From time to time some maintenance work on the MySQL database has to be done. During the maintenance window we do not want to have application traffic on the database.

Sometimes it is hard to shut down all applications spread over the whole company. Or we want to allow only some specific hosts to access mysql from remote (for example the monitoring system or the backup server).

For this purpose we can use the Linux packet filtering.

To see what packet filtering rules are available we can run the following command:

iptables -L INPUT -v

To close the MySQL port on all interfaces we use:

iptables -A INPUT -p tcp --dport mysql -j DROP

and to open the MySQL port again after the maintenance window:

iptables -D INPUT -p tcp --dport mysql -j DROP

With the -i option we can restrict the rule to a specific interface for example eth0 and with the option -s we can specify a specific source only. Or with a ! -s we can implement an inverse rule (all but).

Taxonomy upgrade extras: galeracluster

A while ago it was pretty inconvenient to start a complete Galera Cluster from scratch. Rolling restart an such things are already working well but bootstrapping was a pain.

With Galera v2.2 new functionality came in. We tried it out and it did not work as documented. :-( Thanks to Teemu's help we found there was a documentation bug in the Galera documentation.

The settings which were working for us are:

wsrep_cluster_address = "gcomm://192.168.1.2,192.168.1.3?pc.wait_prim=no"

And when all 3 nodes of the Galera Cluster are started and ready to join you can run:

SET GLOBAL wsrep_provider_options="pc.bootstrap=1";

I hope we can go life on Thursday with the new Telco VoIP Cluster for 2500 employees...

Have fun and enjoy an even better Galera Cluster for MySQL!

Due to customer requests, we have added two of our advanced MySQL training courses in late April in Zurich, Switzerland. One course will be presented in German, the other in English.

The venue is the HSO in Zurich-Oerlikon.

The following dates has been set:

April 22 - 26Advanced MySQLHSO,​ ZurichApril 29 - Mai 3Advanced MySQLHSO,​ Zurich

Caution: the 2nd training contains May 1st (bank holiday). The training takes place regardless.

Remember to book now to reserve your place.

FromDual führt zusätzlich zwei Kurse MySQL für Fortgeschrittene Ende April in Zürich durch. Ein Kurs wird in deutsch, der andere in englisch abgehalten.

Der Kurs wird in den Räumlichkeiten der HSO in Zürich-Örlikon durchgeführt.

Folgende Daten wurden festgelegt:

22. - 26. AprilMySQL für FortgeschritteneHSO,​ Zürich29. April - 3. MaiMySQL für FortgeschritteneHSO,​ Zürich

Achtung: der 2. Kurs beinhaltet den 1. Mai (Feiertag). Der Unterricht findet trotzdem statt.

Vergessen Sie nicht, jetzt zu buchen um sich einen Platz zu sichern.

Die MySQL SIG der DOAG lädt ein am 27. Februar ins Innside Hotel in München.
Schwerpunkt des SIG-Events: MySQL Replikation und neue Features.

The MySQL SIG of DOAG invites you to join on February 27 at the Inside Hotel in Munich.
Topics of the SIG Event: MySQL Replication and new Features.

Taxonomy upgrade extras: mysql-trainingmysql-schulungtrainingschulungBackupPerformance TuningOperationsHigh Availabilityhochverfügbarkeit

FromDual bietet auch im Jahr 2013 wieder intensive MySQL Schulungen für fortgeschrittene MySQL Anwender an.

Folgende deutschsprachigen Kurse sind geplant:

• MySQL für Profis, vom 18. - 22. März in Berlin
• MySQL für Fortgeschrittene, vom 8. - 12. April im Linuxhotel in Essen
• MySQL Backup, am 8. April im Linuxhotel in Essen
• MySQL Hochverfügbarkeit, vom 9. - 10. April im Linuxhotel in Essen
• MySQL Performance Tuning, vom 11. - 12. April im Linuxhotel in Essen
• MySQL für Fortgeschrittene, vom 7. - 11. Oktober im Linuxhotel in Essen
• MySQL Backup, am 7. Oktober im Linuxhotel in Essen
• MySQL Hochverfügbarkeit, vom 8. - 9. Oktober im Linuxhotel in Essen
• MySQL Performance Tuning, vom 10. - 11. Oktober im Linuxhotel in Essen
• MySQL für Profis, vom 18. - 22. November in Berlin

Weiter Infos finden Sie auf unserer Website unter MySQL Schulung.

Taxonomy upgrade extras: Backup Restore RecoveryBackup

Some MySQL customers do not want to use the root user for mysqldump backups. For this user you have to grant the following minimal MySQL privileges:

mysqldump --single-transaction (InnoDB) CREATE USER 'backup'@'localhost' IDENTIFIED BY 'secret'; GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO 'backup'@'localhost';
mysqldump --lock-all-tables (MyISAM) GRANT LOCK TABLES ON *.* TO 'backup'@'localhost';

If we missed a privilege please let us know.

Taxonomy upgrade extras: innodb

In this weeks MySQL workshop we were discussing, beside other things, about the innodb_file_per_table parameter and its advantages of enabling it. In addition there was a discussion if the InnoDB system tablespace file can be shrinked once it has been grown very large or not. We all know the answer: The InnoDB system tablespace file does never shrink again.

But why should it not be possible? Other databases like for example Oracle can shrink or even get rid of tablespace files... After some philosophising about it we came to the conclusion that we should give it a try if this is possible with InnoDB as well.

The scenario we considered was the following: You inherit a MySQL database with InnoDB tables but innodb_file_per_table was set to 0. So all the tables are located in the InnoDB tablespace file. And only a small amount of space is left on the device and there is a lot of free space in the InnoDB system tablespace file. The database itself is much too big to dump and restore and we want to get rid of the one big InnoDB system tablespace file and have many small tablespace files as we get them with innodb_file_per_table = 1.

So what we did is the following: We created InnoDB tables inside the InnoDB system tablespace (ibdata1) and bloat them up. Then we altered them to be placed in their own tablespace files by OPTIMIZE TABLE. And now the tricky part starts: How can we shrink the InnoDB system tablespace file to free the disk space again?

CAUTION: This is a prove of concept and should never be used on a production system!!!

First we move all tables out of the InnoDB system tablespace (with innodb_file_per_table = 1):

mysqlcheck --optimize --all-databases --user=root ... note : Table does not support optimize, doing recreate + analyze instead status : OK ...

Now all tables have been moved out of the system tablespace, but the file is still about 674 Mbyte in size:

ll ibdata1 -rw-rw----. 1 mysql mysql 706740224 Dec 6 23:37 ibdata1

Then we search for empty blocks at the end of the InnoDB data files:

innochecksum -v -d ibdata1 file ibdata1 = 706740224 bytes (43136 pages)... checking pages in range 0 to 43135 page 0: log sequence number: first = 3558400819; second = 3558400819 page 0: old style: calculated = 148443420; recorded = 148443420 page 0: new style: calculated = 4252778336; recorded = 4252778336 ... page 42508: log sequence number: first = 0; second = 0 page 42508: old style: calculated = 1371122432; recorded = 0 page 42508: new style: calculated = 1575996416; recorded = 0 ... page 43135: log sequence number: first = 0; second = 0 page 43135: old style: calculated = 1371122432; recorded = 0 page 43135: new style: calculated = 1575996416; recorded = 0

In ideal case we should also find blocks which are not used any more but not blanked out. Theses 627 blocks (of 16k = 10 Mbyte) can easily be removed...

Next we shrink the InnoDB system tablespace file after stopping the mysqld:

printf '' | dd of=ibdata1 bs=16384 seek=42508 ll ibdata1 -rw-rw----. 1 mysql mysql 696451072 Dec 6 23:42 ibdata1

As a next step we have to change the number of blocks in the header of the InnoDB system tablespace file. This can be done with a tool like hexedit (aptitude install hexedit). We have to change at position 0x0030 the value from 43136 (0xA880) to 42508 (0xA60C):

hexdump -C -n 256 ibdata1 00000000 fd 7c 3f 60 00 00 00 00 00 00 00 00 00 00 00 00 |.|?`............| 00000010 00 00 00 00 d4 18 e3 33 00 08 00 00 00 00 d4 18 |.......3........| 00000020 e4 13 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00000030 a8 80 00 00 a6 c0 00 00 00 00 00 00 01 21 00 00 |.............!..|

Otherwise we would get an error like:

InnoDB: Error: tablespace size stored in header is 43146 pages, but InnoDB: the sum of data file sizes is only 42508 pages

It looks like InnoDB itself corrects somehow the block number to a 0x100 boundary (4 Mbyte) later.

As the next step we have to fix the new style check sum (at position 0x0000) and the old style check sum (at position 0x3FFC). You have to do this until innochecksum does not complain anymore:

innochecksum -d -p 0 ibdata1 file ibdata1 = 696451072 bytes (42508 pages)... checking pages in range 0 to 0 page 0: log sequence number: first = 3558400819; second = 3558400819 page 0: old style: calculated = 2354503790; recorded = 2354503790 page 0: new style: calculated = 3427457314; recorded = 3587772574

When you have done this the database should be ready to start.

The tables later on can be possibly transferred with the transportable tablespace feature which comes with MySQL 5.6.

I have not found a good way yet to find the highest used block in the tablespace file. So it is a wild guess which is dangerous. Especially because some InnoDB UNDO LOG blocks seems to be located there at very high positions:

SELECT page_type, MAX(page_number) AS max_page_number FROM information_schema.innodb_buffer_page WHERE space = 0 AND page_number != 0 GROUP BY page_type ORDER BY max_page_number; +-------------------+-----------------+ | page_type | max_page_number | +-------------------+-----------------+ | TRX_SYSTEM | 5 | | SYSTEM | 300 | | BLOB | 9366 | | EXTENT_DESCRIPTOR | 32768 | | IBUF_BITMAP | 32769 | | INODE | 42123 | | INDEX | 45229 | | ALLOCATED | 45247 | | UNDO_LOG | 45503 | +-------------------+-----------------+

It would be good if we have a method to relocate those blocks somehow...

To verify that everything works I have tried to increase the system tablespace again. This seems to work if the number of blocks is dividable by 256 (4 Mbyte, or 128 2 Mbyte?). But growing the system tablespace again should not be the intention.

Further according to our tests this method of shrinking the InnoDB system tablespace seems to work with MySQL 5.1, 5.5 and 5.6.

Thanks to Ralf, Torsten and Stefan for assistance!

It would be nice to get some feedback from the InnoDB and Percona guys about how this feature could be implemented correctly...

And finally: Do not blame and beat me. I know that this is an evil hack, but I like to play in my sandbox as I want!

Taxonomy upgrade extras: userlogin triggerlogon triggerloggingaudithosterhosting

MySQL hosting providers can easily loose the overview over their customers and which user or schema is still in use and which not.

The MySQL database becomes bigger and bigger, uses more and more RAM and disk space and the backup takes longer and longer.

In this situation it would be nice to know which MySQL database user has logged in within the last 6 months for example. MySQL database users who did not login within a defined period can be backuped and removed from the production MySQL database.

The following MySQL login trigger helps to track the login of all non-super privileged MySQL users.

First we need a table where to log the login of the users:

-- DROP DATABASE tracking; CREATE DATABASE tracking; use tracking; -- DROP TABLE IF EXISTS login_tracking; CREATE TABLE login_tracking ( user VARCHAR(16) , host VARCHAR(60) , ts TIMESTAMP , PRIMARY KEY (user, host) ) engine = MyISAM;

Then we need a MySQL stored procedure which does the logging of the login:

-- DROP PROCEDURE IF EXISTS login_trigger; DELIMITER // CREATE PROCEDURE login_trigger() SQL SECURITY DEFINER BEGIN INSERT INTO login_tracking (user, host, ts) VALUES (SUBSTR(USER(), 1, instr(USER(), '@')-1), substr(USER(), instr(USER(), '@')+1), NOW()) ON DUPLICATE KEY UPDATE ts = NOW(); END; // DELIMITER ;

Then we have to grant the EXECUTE privilege to all users of the database which do not have the SUPER privilege. MySQL users with the SUPER privilege are not logged with the init_connect login trigger hook:

-- REVOKE EXECUTE ON PROCEDURE tracking.login_trigger FROM 'oli'@'%'; GRANT EXECUTE ON PROCEDURE tracking.login_trigger TO 'oli'@'%';

Those GRANTSs can be created with the following query:

tee /tmp/grants.sql SELECT CONCAT("GRANT EXECUTE ON PROCEDURE tracking.login_trigger TO '", user, "'@'", host, "';") AS query FROM mysql.user WHERE Super_priv = 'N'; notee +---------------------------------------------------------------------------------+ | query | +---------------------------------------------------------------------------------+ | GRANT EXECUTE ON PROCEDURE tracking.login_trigger TO 'oli'@'localhost'; | | GRANT EXECUTE ON PROCEDURE tracking.login_trigger TO 'replication'@'127.0.0.1'; | | GRANT EXECUTE ON PROCEDURE tracking.login_trigger TO 'oli'@'%'; | | GRANT EXECUTE ON PROCEDURE tracking.login_trigger TO ''@'localhost'; | +---------------------------------------------------------------------------------+

As the last step we have to activate the stored procedure by hooking it into the login trigger hook:

-- SET GLOBAL init_connect=""; SET GLOBAL init_connect="CALL tracking.login_trigger()";

If something went wrong with the login trigger you find the needed information in the MySQL error log.

Reporting

To find out, which users have logged in we can run the following query:

SELECT * FROM tracking.login_tracking; +------+-----------+---------------------+ | user | host | ts | +------+-----------+---------------------+ | oli | localhost | 2012-11-30 15:36:39 | +------+-----------+---------------------+

To find at what time a user has logged in last you can run:

SELECT u.user, u.host, l.ts FROM mysql.user AS u LEFT JOIN tracking.login_tracking AS l ON u.user = l.user AND l.host = u.host WHERE u.Super_priv = 'N'; +-------------+-----------+---------------------+ | user | host | ts | +-------------+-----------+---------------------+ | oli | localhost | 2012-12-01 09:55:33 | | replication | 127.0.0.1 | NULL | | crm | 127.0.0.1 | NULL | +-------------+-----------+---------------------+

And to find users which are logged but could not be found from the mysql user table you can run:

SELECT l.user, l.host FROM tracking.login_tracking AS l LEFT JOIN mysql.user AS u ON u.user = l.user AND l.host = u.host WHERE u.user IS NULL;

Taxonomy upgrade extras: Backupcompress

When a MySQL Slave is set-up with mysqldump you have 2 possibilities:

• You dump into a file and then load the data into the Slave with the mysql client utility.
• You dump directly into the mysql client utility.

The first possibility has the advantage that you can start the load again if it failed. You can look into the file (and do some changes if needed).
The second possibility has the advantage that you do not need disk space and that it is possibly faster. But when the load fails you have to start from the very beginning.

What I was looking for is a way to combine everything in one step: Dumping to a file including compression and in the same step load the database to a slave. This is what I found to solve these requirements:

mysqldump --user=root --all-databases --flush-privileges --single-transaction --master-data=1 --quick \ --flush-logs --triggers --routines --events | tee >(gzip > /tmp/full_backup.sql.gz) | mysql --user=root --host=192.168.1.60 --port 3306

With this command you can even load several CPUs of the system:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 24747 mysql 20 0 534m 56m 5504 S 36.1 0.7 4:12.35 mysqld 4967 mysql 20 0 402m 33m 5236 S 7.0 0.4 0:02.06 mysqld 4982 mysql 20 0 23348 2112 1216 S 6.6 0.0 0:01.64 mysqldump 4984 mysql 20 0 28608 3856 1372 S 5.6 0.0 0:01.58 mysql 4986 mysql 20 0 4296 688 304 S 5.3 0.0 0:02.10 gzip 4983 mysql 20 0 98.5m 628 544 S 0.7 0.0 0:00.13 tee

If gzip becomes the bottleneck you can try with pigz.

Important: Before you start any operation mentioned below do a proper file system backup of your XFS file system you want to resize. If MySQL is running on this mount point do this with a stopped mysqld. Alternatively you can also use mysqldump to do the MySQL backup but test the restore time before continuing to not experience ugly surprises...

All these operations have to be performed as the root user. First we want to see what mount points are available:

shell> df -h Filesystem Size Used Avail Use% Mounted on /dev/sda1 485M 77M 383M 17% / /dev/sdb1 496M 314M 157M 67% /var/lib/mysql

Our MySQL data are located on /dev/sdb1.

After the file system backup unmount /dev/sdb1 and resize the disk, partition or volume (works for VMware, NetApp filer and similar equipment, for LVM use lvextend):

shell> tar cvf /backup/mysql.tar /var/lib/mysql shell> umount /var/lib/mysql shell> fdisk /dev/sdb

Change the units in fdisk to have a better overview over your begin and end of your partition:

fdisk> u Changing display/entry units to sectors fdisk> p Disk /dev/sdb: 1073 MB, 1073741824 bytes 139 heads, 8 sectors/track, 1885 cylinders, total 2097152 sectors Units = sectors of 1 * 512 = 512 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes Disk identifier: 0xea17dfd0 Device Boot Start End Blocks Id System /dev/sdb1 8 1047503 523748 83 Linux fdisk> d Selected partition 1 fdisk> n Command action e extended p primary partition (1-4) fdisk> p Partition number (1-4): fdisk> 1 First sector (8-2097151, default 8): Using default value 8 Last sector, +sectors or +size{K,M,G} (8-2097151, default 2097151): Using default value 2097151 fdisk> w shell> fdisk /dev/sdb fdisk> p Disk /dev/sdb: 1073 MB, 1073741824 bytes 139 heads, 8 sectors/track, 1885 cylinders, total 2097152 sectors Units = sectors of 1 * 512 = 512 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes Disk identifier: 0xea17dfd0 Device Boot Start End Blocks Id System /dev/sdb1 8 2097151 1048572 83 Linux

Now the partition has the new size. The next step is to resize the XFS file system. Install the XFS tools if they are not already there:

apt-get install xfsprogs yum install xfsprogs

And then extend the XFS file system on-line and mount it again:

shell> xfs_growfs /var/lib/mysql shell> df -h Filesystem Size Used Avail Use% Mounted on /dev/sda1 485M 77M 383M 17% / /dev/sdb1 992M 314M 627M 34% /var/lib/mysql

Taxonomy upgrade extras: temporarymemory tablemyisam

MySQL temporary tables are created either in memory (as MEMORY tables) or on disk (as MyISAM tables). How many tables went to disk and how many tables went to memory you can find with:

mysql> SHOW GLOBAL STATUS LIKE 'Created_tmp%tables'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Created_tmp_disk_tables | 49094 | | Created_tmp_tables | 37842181 | +-------------------------+----------+

Tables created in memory are typically faster than tables created on disk. Thus we want as many as possible tables to be created in memory.

To achieve this we can configure the variables accordingly:

mysql> SHOW GLOBAL VARIABLES LIKE '%table_size'; +---------------------+----------+ | Variable_name | Value | +---------------------+----------+ | max_heap_table_size | 25165824 | | tmp_table_size | 25165824 | +---------------------+----------+

All result sets which are smaller than these values can be handled as MEMORY tables. All result sets bigger than these values are handled as MyISAM tables an go to disk.

But there is still an other reason for tables going to disk: MEMORY tables cannot handle TEXT or BLOB attributes as it often occurs in CMS like Typo3. In these cases MySQL has to do directly MyISAM tables on disk and they are counted as Created_tmp_disk_tables.

If these temporary disk tables are causing serious I/O performance problems one could consider to use a RAM-disk instead of normal physical disks instead.

On Linux we have 2 possibilities to create a RAM-disk: ramfs and tmpfs [ 1 ].

We recommend to use tmpfs.

A RAM-disk can be created as follows:

shell> mkdir -p /mnt/ramdisk shell> chown mysql:mysql /mnt/ramdisk shell> mount -t tmpfs -o size=512M tmpfs /mnt/ramdisk

To make this persistent we have to add it to the fstab:

# # /etc/fstab # tmpfs /mnt/ramdisk tmpfs rw,mode=1777,size=512M 0 0

MySQL still writes to the default location which is found as follows:

mysql> SHOW GLOBAL VARIABLES LIKE 'tmpdir'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | tmpdir | /tmp | +---------------+-------+

To changes this value you have to configure your my.cnf accordingly and restart the database...