MySQL lets database administrators define access rights on many levels – from the ability to run global commands down to access to individual columns. Some rights can be applied to many different objects, such as for example SELECT or UPDATE, which can be granted globally or restricted only to certain databases or tables, while others are only meant for one specific purpose. An example of the latter could be FILE privilege, which permits user to interact with the file system from inside a database instance. It only makes sense as the global right and not anywhere else.

As any other activity that produces changes, GRANT statements are replicated to MySQL slaves. Regardless of the binary log format setting, such events are always logged in STATEMENT format. It is likely because the command needs to handle more than just updating the contents of a few system tables, so such design allows each slave to fully execute the changes within their …

The new MySQL 5.6 GA release delivers a host of new capabilities to support developers releasing new services faster, with more agility, performance and security .

One of the areas with the most far-reaching set of enhancements is MySQL replication used by the largest web, mobile and social properties to horizontally scale highly-available MySQL databases across distributed clusters of low cost, commodity servers.

A new on-demand MySQL 5.6 replication webinar takes you on a guided tour through all of those enhancements, including:

- 5x higher master and slave …

Percona Live 2013 is coming up fast.  This is hands-down the best MySQL conference of the year, attended by a lot of people I really respect.  Check the speaker list if you need some of their names.  I will also be doing two talks myself.

• 9am Wednesday 24 April - Keynote:  How MySQL Can Thrive in the World of Massive Data Hype.  NoSQL solutions are oversold, but this is no reason for complacency in the MySQL community.  There are new challenges in data management, and we need to solve them or become irrelevant.   I will show some of the advances Continuent has on tap …

<< Previous Post: Advanced use of Global Transaction Identifiers

As we saw in my previous posts, Global Transaction Identifiers in MySQL 5.6 allow you to change the replication topology arbitrarily. You don't even need to specify the positions in the replication stream – when you turn on auto-positioning, the master automatically sends only those transactions that are missing on the slave.

When you do a fail-over, you want to make sure that the new master is up-to-date. For instance, consider the following scenario:
Server A is a master; B, C and …

In the first part of this article we examined the types of conflicts and their causes. In this part, we will analyse some of the methods available to deal with conflicts.

Pessimistic locking (or: conflicts won't happen)

Applicability: synchronous clusters with 2pc

We've covered this topic in the previous article, but it's worth repeating. If you use a synchronous cluster, you don't have conflicts. For example, MySQL Cluster ensures consistent data with updates coming from different nodes. However, MySQL Cluster is not a replacement for a MySQL server, and it has severe limitations.

Optimistic locking

Applicability: synchronous clusters without 2pc (Galera)

Conflicting transactions proceed on different nodes with local locking. The last one then …

As alway with new technology there is always different approaches regarding the adoption. You can try to use the bleeding edge features or start with a very standard configuration. My personal advise to new users is to start with the most basic configuration.

This allow you to get familiar with the fundamentals : - how [...]

What is a conflict?

Readers of this blog know that one of my favorite tools, Tungsten Replicator, can easily create multi-master replication topologies, such as all-masters, star, fan-in. While this is good news for system designers and ambitious DBAs, it also brings some inconvenience. When you allow updates to happen in more than one master, you risk having conflicts. You may have heard this term before. For the sake of clarity, let's define what conflicts are, before analyzing each case in detail.

You have a conflict when several sources (masters) update concurrently the same data in asynchronous replication.

It's important to stress that this happens with asynchronous replication. In a truly synchronous cluster, where all data is kept consistent through 2-phase …

The Percona Live MySQL Conference and Expo 2013 is almost 1 month away. It's time to start planning, set the expectations, and decide what to attend. This post will give a roundup of some of the sessions that I recommend attending and I look forward to.

First, the unexpected!

After much talk and disbelief, here they come! Oracle engineers will participate to the Percona Live conference. This is wonderful! Their participation was requested by the organizers, by the attendees, and by community advocates, who all told the Oracle management how important it is to be in this conference. Finally, they have …

Stating the problem.

In my job, I do a lot of testing. And no matter how much organized we try to be, we end up with fewer machines than we would need to run all the tests that we want.

For some tasks, we can run MySQL Sandbox, and get the job done. But sometimes we need to make sure that applications and systems work well across the network, and we need to install and run systems on separate servers.

However, when you test replication systems, and every cluster takes three or four servers, you run our of available hosts very quickly. So you decide to use the clusters that are dedicated to automated testing to also run your own manual tests. Soon you realize that the tests that you are running manually are clashing with the automated ones, or with the ones that your colleagues are running.

A simple solution is installing additional sandboxes for the MySQL …

MySQL is deployed in 9 of the top 10 most trafficked sites on the web including Facebook, Twitter, eBay and YouTube, as well as in some of the fastest growing services such as Tumblr, Pinterest and box.com

Working with these companies has given MySQL developers, consultants and support engineers unique insight into how to design database-driven web architectures – whether deployed on-premise or in the cloud.

The MySQL Web Reference Architectures are a set of documented and repeatable best practices for building infrastructure that deliver the highest levels of scalability, agility and availability with the lowest levels of cost, risk and complexity. 

Four components common to most web and mobile properties are sized, with optimum deployment architectures for each:

• …