FromDual TechFeed (en)

Table of Contents

• Introduction
• Prerequisites
• What is MyEnv?
• MySQL Workbench configuration.
  • Add MySQL connections to MySQL Workbench.
  • Start/Stop instance configurations.

Introduction

MySQL Workbench is a very good and free GUI tool provided by Oracle to manage MySQL administration and development tasks. Opening many MySQL connections (same or different instances, remote or local MySQL servers) at the same time is one of its main features. While it’s working fine to perform SQL statements on the …

I just want to mention here that Galera 3.1 GA is now released but the online upgrade from MySQL 5.5 to 5.6 has some problems and a workaround should be made (Bug #1251137) to get this process done.

Hi,

The output you just provided could be gotten by having your database’s tables backed up individually and then restore them the same way.
For this purpose, the backup script should be something like:

#!/bin/bash
cd /backup/script/
for x in `cat tables.txt`
do 
echo $x
mysqldump --user=user_name --password='password' database_name $x  > /backup/$x.sql

echo 'Table '.$x.' completed ...'
done
echo 'Database backed up successfully ...'

Where the tables.txt file contains all tables names …

Hi Srihari,

What exactly you want to monitor in MySQL ?

Great News: Galera Cluster v3.1 GA for MySQL 5.6 was released at Percona Live London (PLUK) 2013. The information is still a bit hidden…

You can find it here:

• The Plugin: https://launchpad.net/galera/3.x/25.3.1
• The MySQL: https://launchpad.net/codership-mysql/5.6/5.6.14-25.1

Or directly on our download page.

Careful: Online-Upgrade from 5.5 to 5.6 will not work yet. We have to find a work-around…

Table of Contents

• Introduction
• Prerequisites
• Full Backup
  • Create Full Backup
  • Prepare Full Backup
  • Restore Full Backup
  • Prepare slave from full backup
  • Prepare GTID slave from full backup
  • Advantages / Disadvantages
  • Important Hints
• Incremental Backup
  • Create Incremental Backup
  • Prepare Incremental Backup
  • Restore Incremental Backup
  • Advantages / Disadvantages
  • Important Hints
  • Differential Backup
• Partial Backup
  • Create Partial Backup
  • Prepare Partial Backup
  • Restore Partial Backup
  • Advantages / Disadvantages …

I don’t think that there’s such way in MySQL like MSSQL to track users modifications, but you can check the general log file (if it’s enabled) and search for all DCL statements related to the user in question. In the general log file, MySQL records all commands received by the client along with the issuance timestamp. I hope that helps..

Thanks Oli for the very helpful post, it really helped me to understand how to use and operate GLB, but I'd like to mention here that the option "-q" works only in Ubuntu, but in CentOS, it should be replaced by "-s" to be used as follows:

 echo getinfo | nc -s 1 127.0.0.1 4444
 

- To drain a node from the GLB, the following command should be used:

 echo 192.168.56.101:3306:0 | nc -s 1 127.0.0.1 4444
 

- To add or remove a node …

Table of Contents

• 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 …

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: …

Hello thatsafunnynamecomment,

Thanks for reading and correcting my findings! You are absolutely right. I did a bit short-cut too much in maths!

For your comment #2 it looks like I did not investigate carefully enough. Thanks for correcting me. I found several sources pointing to tcp_fin_timeout and mentioned TIME_WAIT is affected. So I should just be more careful next time. Especially in a domain I am an absolute noob.

Shinguz

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 …

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:

# …

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 …

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) …

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 …

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] …

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 …

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 …

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 …