Backup and restore is one of the most important aspects of database administration. If a database crashed and there was no way to recover it, any resulting data loss might lead to devastating results to a business. As the DBA operating a MySQL or Galera cluster in production, you need to ensure your backups are scheduled, executed and regularly tested.

There are multiple ways to take backups, but which method fits your specific needs? How do I implement point in time recovery? 

Join us for this live session on backup strategies for MySQL and Galera clusters led by Krzysztof Książek, Senior Support Engineer at Severalnines.

DATE & TIME

Europe/MEA/APAC
Tuesday, June 30th at 09:00 BST / 10:00 CEST (Germany, France, Sweden)
Register Now

North America/LatAm …

In a post, written a few months ago, I found that using EXT4 transactions with the “data=journal” mount option, improves the write performance significantly, by 55%, without putting data at risk. Many people commented on the post mentioning they were not able to reproduce the results and thus, I decided to further investigate in order to find out why my results were different.

So, I ran sysbench benchmarks on a few servers and found when the InnoDB double-write buffer limitations occur and when they don’t. I also made sure some of my colleagues were able to reproduce the results. Basically, in order to reproduce the results you need the following conditions:

• Spinning disk (no SSD)
• Enough CPU power
• A dataset that fits in the InnoDB buffer pool
• A continuous high write …

With the shipment of the first release candidate (RC) of MySQL 5.7, the next major version of the server is rapidly shaping up. Over the course of the nearly two and a half years that have passed since 5.6 went GA, we have put a lot of work into streamlining the server code in order to ease the burden of developing and maintaining such a large product and codebase.

An important aspect of this work is deprecation and removal. To explain the terms, deprecating a feature means that we signal to the outside world that “this feature is available now, but it will be removed in a future release, so please adjust your use case accordingly”. Removing a feature means just that – in one version the feature is available, but then it is gone, and if you try to use it, you will get an error message saying that the feature is unknown.

Generally we don’t remove features in existing GA releases, but we …

Some applications, particularly those written with a single-node database server in mind, attempt to immediately read a value they have just inserted into the database, without making those those operations part of a single transaction. A read/write splitting proxy or a connection pool combined with a load-balancer can direct each operation to a different database node.

Since Galera allows, for performance reasons, a very small amount of “slave lag”, the node that is processing the read may have not yet applied the write. It can return old data, causing an application that did not expect that to misbehave or produce an error.

The Solution

Through the mechanism of flow control, slave lag is kept to a minimum, but additionally Galera provides the causal wait facility for those queries that must always see the most up-to-date …

If you draw a diagram of information flow and interaction amongst teams and processes in IT, you’ll probably find that although some parts of the organization are “leaf” or “edge” nodes, the people who manage the data are not. The DBAs would usually be one of the lavender circles in the chart below, not a blue circle.

DBAs also occupy a central position in the continuum of skills:

• On one hand, they have to understand a lot about how the application code works, because application developers are their customers.

• On the other hand, they need to understand how the application runs in production, because operations staff are also their customers.

DBAs end up knowing a lot about everything, and because they can develop this all-encompassing set of skills and knowledge, the organization relies on them to do so.

Consider the old adage, “if you want to get something done, ask a busy …

Hooray for Data Structures… Where Are They?

The Key-Value Pair is my favorite programming tool. Most SQL type databases lack this feature in it simplest form. Some would argue, “Well that’s because databases don’t need dah-blah-dee-blah-blah…” Well, if they don’t need it I don’t need to write this post. :p

How many times have you found yourself asking “Why isn’t there at least simple array support!?” When you don’t want the overhead of creating a table, or a temporary table, simple data structures really do come in handy (hint, hint, nudge, nudge to all the companies producing database software with stored routine support).

I’m not one to let an apparent lack of functionality ruin my day. Instead, I add it. I had a great computer science professor who said “You don’t need pointers to create a linked list.” Then he proved it. You can create, pretty much, any abstract data structure with …

We usually try to avoid subselects because sometimes they force the use of a temporary table and limits the use of indexes. But, when is good to use a subselect?

This example was tested over table a (1310723 rows), b, c and d ( 5 rows each) and with MySQL version 5.5 and 5.6.

Let’s suppose we have a query like this:

select a.name,sum(a.count) aSum,avg(a.position) aAVG,b.col1,c.col2,d.col3
from
a join
b on (a.bid = b.id) join
c on (a.cid = c.id) join
d on (a.did = d.id)
group by a.name,b.id,c.id,d.id

What will MySQL do? First it will take the entire data set – this means that will go through each row scanning the value of  “bid,” “cid” and “did” and then apply the join to each table. At this point it has the complete data set and then it will start to cluster it, executing the sum and the average functions.

Let’s analyze it step by step:

1. Scan each row of  table a which …

The MySQL developer tools team announces 6.3.4 as our GA release for MySQL Workbench 6.3.

For the full list of changes in this revision, visit
http://dev.mysql.com/doc/relnotes/workbench/en/changes-6-3.html

For discussion, join the MySQL Workbench Forums:
http://forums.mysql.com/index.php?151

Download MySQL Workbench 6.3.4 GA now, for Windows, Mac OS X 10.7+,
Oracle Linux 6 and 7, Fedora 21 and Fedora 22, Ubuntu 14.04, Ubuntu
14.10 and Ubuntu 15.04 or sources, from:

http://dev.mysql.com/downloads/tools/workbench/

Enjoy!

Changes in MySQL Workbench 6.3.4 (2015-06-15)

…

Dear MySQL users,

The MySQL developer tools team announces 6.3.4 as our GA release for
MySQL Workbench 6.3.

For the full list of changes in this revision, visit
http://dev.mysql.com/doc/relnotes/workbench/en/changes-6-3.html

For discussion, join the MySQL Workbench Forums:
http://forums.mysql.com/index.php?151

Download MySQL Workbench 6.3.4 GA now, for Windows, Mac OS X 10.7+,
Oracle Linux 6 and 7, Fedora 21 and Fedora 22, Ubuntu 14.04, Ubuntu
14.10 and Ubuntu 15.04 or sources, from:

http://dev.mysql.com/downloads/tools/workbench/

There are many many approaches to MySQL high availability - from traditional, loosely-coupled database setups based on asynchronous replication to more modern, tightly-coupled architectures based on synchronous replication. These offer varying degrees of protection, and DBAs almost always have to choose a tradeoff between high-availability and cost. 

This is the third installment in the ‘Become a MySQL DBA’ series, and discusses the pros and cons of different approaches to high availability in MySQL. Our previous posts in the DBA series include Backup and Restore and Monitoring & Trending.

High Availability - what does it mean?

Availability is somewhat self-explanatory. If your database …