We have been using SHOW ENGINE INNODB MUTEX command for years. It shows us mutex and rw-lock information that could be useful during service troubleshooting in case of performance problems. As Morgan Tocker announced in his blog post the command will be removed from MySQL 5.7 and we have to use performance_schema to get that info.

The documentation of MySQL also says that most of the command output has been removed from 5.6 and that we can find similar info in performance_schema. It doesn’t show any examples of how to use performance_schema or what is the query we need to use from now on. It is also important to mention that 5.6 doesn’t show any warning about the feature being deprecated.

This is a short blog post to show how to configure performance_schema and get the info we need. Hoping it will end …

In this blog post I will describe different ways of using SSL with the MySQL database server.

What does SSL give you?

You might use MySQL replication over the internet or connect to MySQL over the internet.

Another posibility is that you connect over an enterprise network to which just too many people have access. This is especially an issue if you use an BYOD network.

SSL helps here by encrypting the network traffic to prevent against evesdropping. It also validates that you're talking to the correct server to prevent man-in-the-middle attacks.

And you can also use SSL client certificates together with an password as two factor authentication.

SSL is not the only option, you could use SSH and many MySQL GUI clients like MySQL Workbench support …

Sometimes it is desired to use particular software versions in production, and not necessary the latest ones. There may be several reasons for that, where I think the most common is when a new version should spend some time in testing or a staging environment before getting to production. In theory each new version is supposed to be better as usually it contains a handful of bug fixes and even new or improved functionality. However there is also a risk of some regression or a new bug introduction as a side effect of code changes.

Quite often DBAs want the same MySQL version to be installed on all database instances, regardless of what actually is the latest version available in the software provider’s repository. There are several ways to achieve this:
* download specific version packages manually and then install them,
* have custom local repository mirror where you decide when and which version gets there, and just update …

January 5, 2015 By Severalnines

Cloud storage is hot. Dropbox, Apple iCloud, Google Drive, Microsoft SkyDrive and Amazon Cloud Drive all offer cloud sharing platforms where you can store documents in the cloud and access them from all your devices. For enterprises who require full control, ownCloud is an open source solution that allows files to be stored on-premises and/or a backend cloud. 

In this blog post, we’ll show you how to deploy a high availability setup using ownCloud Community Edition. We will need to set up redundancy in each of the following layers:

• file storage
• web server
• database server
• load balancer

We will use five servers. ownCloud will run on three separate servers with MySQL Galera Cluster 5.6 and GlusterFS running on RHEL 6.5 64bit. ownCloud supports GlusterFS as primary storage. ClusterControl will be co-located …

Query caching is one of the prominent features in MySQL and a vital part of query optimization. It is important to know how it works as it has the potential to cause significant performance improvements – or a slowdown – of your workload.

The MySQL query cache is a global one shared among the sessions. It caches the select query along with the result set, which enables the identical selects to execute faster as the data fetches from the in memory. It is important to have everything identical, no new comments, spaces, or most significantly differences in the WHERE clause. Basically when you trigger a select query, if it is available in the cache; it fetches from there or it considers the query as a new one and will go to the parser.

Even though it has some nice advantages, the MySQL query cache has its own downsides too. Well, let’s think about this: If you are frequently updating the table, you are then invalidating …

So assume you just uploaded the certificate you use to identify yourself to the MySQL server to Github or some other place it doesn't belong...and there is no undelete.

First: Don't panic.
Often a password is required besides a certificate to connect to the server. So someone with the certificate can't use it without the password. The certificate itself might be protected by a password, but that's really rare. Also access to MySQL and/or your account should be limited to certain IP's.

The next step is to revoke the certificate. This is possible since MySQL 5.6.3 by using a Certificate Revocation List (CRL).
A CRL is a list of the serials of the revoked certificates and signed by the CA. So this will only work if the certificates have unique serials.
…

At the last OOW MySQL Plugin for Oracle Enterprise Manager (OEM) was recognized as most popular MySQL product. If you don't have OEM installed, but want to test the plugin you can download OEM virtual box template. But, althought this is the easiest way to get started, you still need to make few additions. At least I had to do them when deployed such installation for MySQL Support Team.

Here they are. I prefer to use command line when possible.

0. Import virtual machine image and change network adapter to working one, then allow to connections via rdesktop:

sudo vboxmanage import VBox_EM12cR4.ova --vsys 0 --vmname  …

If you’ve been studying complex systems you know what minor changes might cause consequences of much greater proportions, sometimes causing some effects that are not easily explained at first. I recently ran across a great illustration of such behavior while doing MySQL benchmarks which I thought would be interesting to share.

I’m using a very simple benchmark – Sysbench 0.5 on Percona Server 5.6.21-70.1 just running update queries:

sysbench  --num-threads=64 --report-interval=10 --max-time=0 --max-requests=0 --rand-type=pareto --oltp-table-size=1000000000 --mysql-user=root --mysql-password= --mysql-db=sbinnodb  --test=/usr/share/doc/sysbench/tests/db/update_index.lua run

Some people frown upon such benchmarks due to their triviality and being irrelevant to workloads. I like them because they often allow you to study already complex system behavior in a much more controlled environment than “real” workloads – and so …

In this post ,we mainly talk about MHA GTID behavior, we test different cases and find something is different from previous versions .

we have four machines for this test.

environment:

master server: 10.0.128.77
slave server : 10.0.128.110/113/114
port : 3306 

————————————————————————————–

we first do normal failover .

kill master server

we find MHA outputs:

Tue Dec 30 13:32:14 2014 - [warning] Got error on MySQL connect ping: DBI connect(';host=10.0.128.77;port=3306;mysql_connect_timeout=1','dbadmin',...) failed: Lost connection to MySQL server at 'reading initial communication packet', system error: 111 at /usr/share/perl5/vendor_perl/MHA/HealthCheck.pm line 97
2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Tue Dec 30 13:32:14 2014 - [info] Executing secondary network check …

State Snapshot Transfer (SST) is used in Percona XtraDB Cluster (PXC) when a new node joins the cluster or to resync a failed node if Incremental State Transfer (IST) is no longer available. SST is triggered automatically but there is no magic: If it is not configured properly, it will not work and new nodes will never be able to join the cluster. Let’s have a look at a few classic issues.

Port for SST is not open

The donor and the joiner communicate on port 4444, and if the port is closed on one side, SST will always fail.

You will see in the error log of the donor that SST is started:

[...]
141223 16:08:48 [Note] WSREP: Node 2 (node1) requested state transfer from '*any*'. Selected 0 (node3)(SYNCED) as donor.
141223 16:08:48 [Note] WSREP: Shifting SYNCED -> DONOR/DESYNCED (TO: 6)
141223 16:08:48 …