Wed, 2015-04-22 08:05martinbrampton

When you want to connect a client to a database server through an insecure network, there are two main choices: use SSL or use an SSH tunnel. Although SSL often may seem to be the best option, SSH tunnels are in fact easier to implement and can be very effective. Traffic through an SSH tunnel is encrypted with all of the security of the SSH protocol, which has a strong track record against attacks.

There are various ways to implement an SSH tunnel. This article suggests a simple approach which is adequate in many situations. For the examples here, let’s assume that there is a database server running on a host named, server.example.com, with an IP address of 1.2.3.4. Suppose further that the client is on a host named, client.example.com, with an IP address of 5.6.7.8. We’ll also suppose that there are tightly configured iptables …

Article about bug report #68814 related to testing count(*) explain plan.

Or sales table huge enough to play with.

mysql> select count(*) from sales;
+----------+
| count(*) |
+----------+
|  2500003 |
+----------+
1 row in set (0.56 sec)

First with regular count(*) without where clause:

mysql> explain select count(*) from sales\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: index
possible_keys: NULL
          key: sales_cust_idx
      key_len: 4
          ref: NULL
         rows: 2489938
        Extra: Using index
1 row in set (0.00 sec)

Estimated rows -> rows: 2489938

Then with {where sales_id > 0}:

mysql> explain select count(*) from sales where sales_id > 0\G
*************************** 1. row …

I’ve written previously about use cases where having accounts which cannot be used to establish client connections are useful. There are various hacks to accomplish this with legacy versions (insert invalid password hash into mysql.user table, etc.), and we introduced the mysql_no_login authentication plugin for this very purpose. Now as of MySQL 5.7.6, account locking gets native support through the ACCOUNT LOCK clause of CREATE USER and ALTER USER commands. This post revisits the use cases which drove this feature and the implementation details.

Use Cases

Security …

Last week I presented my talk, "How to Analyze and Tune SQL Queries for Better Performance" both at Percona Live in Santa Clara and at airbnb Tech Talks in San Francisco.  The slides are available on slideshare. A video recording from the airbnb talk should eventually be available the airbnb Tech Talks page.

The slides for my MySQL Security Essentials presentation at Percona Live 2015 MySQL Conference and Expo are now available.

In this presentation I discuss just how insecure legacy versions of MySQL are and what are the essential requirements for securing your installation on disk, via network and with user privileges. I provide recommendations for how to manage application access for your most important data asset.

Download PDF Presentation

This presentation describes the key security improvements in MySQL 5.6 and MySQL 5.7 as well as additional features provided in MariaDB 10.0 …

MySQL sharding is one of the most used and surely the most abused MySQL scaling technology. My April 2 Dzone article, “To Shard, or Not to Shard,” proved there is indeed quite an interest in this topic.

As such, I’m hosting a live webinar tomorrow (April 22) that will shed light on questions about sharding with MySQL. It’s titled: To Shard or Not to Shard That is the Question!

I’ll be answering questions such as:

• Is sharding right for your application or should you use other scaling technologies?
• If you’re sharding, what things do you need to consider and which questions do you need to have answered?
• What kind of specific technologies can assist you with sharding?

I hope …

MySQL 5.7 will be a great milestone in MySQL total history.
Oracle has released many useful new features in LAB version . MySQL is becoming more similar to Oracle database

Read this presentation I post on slideshare:

MySQL 5.7 milestone

I recently gave a presentation at Percona Live 2015 in Santa Clara, CA. In this presentaiton I originally wanted to simply show running MySQL replication, first asynchronous, and more importantly, a Galera cluster, and in so doing, demonstrate how useful Kubernetes is.

Why?

The talk was a good chance to introduce the MySQL community– developers, DBAs, sysadmins, and others to what Kubernetes is and what it means for MySQL

A bit of learning

I thought at the time when I submitted my synopsis that the talk would be straightforward. About 2-3 months ago, I started working on the setup I would use for the demonstration. My goal was to use a stock CoreOS cluster with the necessary Kubernetes components installed and running as a cluster.

The …

What?

MySQL 5.7 server binaries compiled with the OpenSSL library now make it easy to set up SSL/TLS and RSA artifacts, and to enable them within MySQL. Two new read-only global options have been introduced through this work:

• --auto-generate-certs: Enables automatic generation and detection of SSL artifacts at server start-up.
• --sha256-password-auto-generate-rsa-keys: Enables automatic generation of an RSA key pair.

These options govern automatic generation and detection of SSL/TLS artifacts and RSA key pairs respectively. Auto generated files are placed inside the data directory, and both options now default to ON.

For the …

In a previous post, we gave you a quick overview of the MaxScale load balancer and walked through installation and configuration. We did some quick benchmarks using sysbench, a system performance benchmark that supports testing CPU, memory, IO, mutex and also MySQL performance. We will be sharing the results in this blog post.

Sysbench setup

For our tests we used the latest version of sysbench, straight from bzr. Installation is simple. First, make sure you have all the prerequisites. For Ubuntu these are: libmysqlclient-dev (or equivalent), bzr, automake, make, libtool and libssl-dev.

Get the code and compile it:

$ cd /root
$ bzr branch lp:sysbench
$ cd /root/sysbench
$ …