My colleague and teammate Ernie Souhrada will be presenting a webinar on Wednesday, August 21, 2013 at 10 a.m. PDT titled “MySQL Security: Armoring Your Dolphin.”
This is a popular topic with news breaking routinely that yet another Internet company has leaked private data of one form or another. Ernie’s webinar will be a great overview of security MySQL from top to bottom, including changes related to security in the 5.6 release.
Topics to be covered include:
To only slightly misquote one of the greatest movies of all times:
You keep using that option. I do not think it means what you think it means.
Perhaps like many users, I had certain assumptions about what max_connect_errors really does – but in looking closely as part of investigating the new PERFORMANCE_SCHEMA.HOST_CACHE table in MySQL 5.6, I learned that some very fundamental elements had escaped my notice. I’m writing this blog post to help others who hold similar misconceptions of what this option does.
Many, if not most, MySQL DBAs are familiar with “host blocked” errors:
C:\mysql-5.5.27-winx64>bin\mysql -utest_mce -P3307 -h192.168.2.8 ERROR 1129 (HY000): Host …[Read more]
Good news everyone! I recently presented a webinar: Percona Server 5.6: Enterprise Grade MySQL. It was also recorded so you can watch along or view the slide deck. As with all my talks, I am not simply reading the slides so it really is worth to listen to the audio rather than just glance through the slide deck.
There were a number of great questions asked which I’ll answer below:
Q: How does Stewart feel about this version of 5.6 taking into consideration “Stewart’s .20 rule?” (ref 2013 Percona Live Conference).
A: For those who aren’t familiar with it, I have a rule which I call “Stewart’s dot twenty rule” which I’ve posted a few times about on my personal blog. It …
[Read more]
In an earlier post, I commented on clients and
utility programs which seem to no longer be useful, and opened
(or referenced existing) public bug reports to deprecate and
remove, where appropriate. That effort came actually was
the product of a different initiative: I was looking for
clients which might leverage the full spectrum of MySQL protocol commands in an effort to
understand whether certain protocol commands are in use. I
thought I would share my observations, in the hope that we might
also get feedback from others regarding usage of these commands.
And even though it’s no longer spring (I started this post in
April), I finally found time to finish this post.
The first group to mention are those which are …
Over three years ago, I noticed that there was no STATUS counter for COM_PING commands – something that is useful for ensuring proper configuration of JDBC connection pools. Mark Leith even provided a patch, but it’s never been incorporated. With the advances PERFORMANCE_SCHEMA makes in MySQL 5.6, that’s OK – a STATUS counter becomes somewhat redundant:
mysql> SELECT SUM(count_star) as pings
-> FROM events_statements_summary_global_by_event_name
-> WHERE event_name = 'statement/com/Ping';
+-------+
| pings |
+-------+
| 12 |
+-------+
1 row in set (0.02 sec)
Not only does PERFORMANCE_SCHEMA provide capabilities which
mirror the STATUS counters, it really goes well beyond what’s
capable there. A global counter is interesting, but …
With the release of MySQL Utilities 1.3.4, the standalone (not bundled with the MySQL WorkBench GUI) package is now Generally Available and fully supported. This post will focus on a very important change (the ability to run as a daemon rather than in a terminal) to the mysqlfailover utility which allows you to build a light-weight HA database solution using MySQL Replication.
For a general overview of MySQL Utilities, take a look at this recent webinar or for a deeper dive into using them to setup replication and adding auto-failover of the master function to slaves watch this video and worked example. …
[Read more]
MySQL 5.6.13 was released earlier this week, and
in that release (as well as 5.5.33) the ability to use unique
option prefixes was deprecated. This is fully removed from
MySQL 5.7, and I thought it might be useful to amplify the
change log notes on why this was done:
Previously, program options could be specified in full or as any
unambiguous prefix. For example, the --compress option could be given
to mysqldump as --compr, but not as
--comp because the latter is ambiguous. Option
prefixes now are deprecated. They can cause problems when new
options are implemented for programs. A prefix that is …
Idle connections can cause problems both at the application side, increasing the risk of connection timeouts for applications where persistent connections are used, and the server side, where resources remain allocated to idle connections. Any application with persistent connections, such as a JDBC application using a connection pool, will have periods where connections are idle – but it’s good to know how much time is spent idle. Too much idle time might mean connections pools configured to allow too many connections to sit idle in a connection pool, or not properly doing connection pool maintenance.
PERFORMANCE_SCHEMA in MySQL 5.6 makes it trivial to measure
absolute time spent waiting. This will show total, average
and maximum idle times by account:
mysql> SELECT …[Read more]
Installed on a clean CentOS 6.4 AWS instance.
sudo su - cd /tmp wget http://cdn.mysql.com/Downloads/MySQL-5.6/MySQL-5.6.13-1.el6.x86_64.rpm-bundle.tar tar xvf MySQL-5.6.13-1.el6.x86_64.rpm-bundle.tar yum install -y libaio perl rpm -i MySQL*.rpm
The following output is the sign that security is being considered with new MySQL versions. Woot!
A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER ! You will find that password in '/root/.mysql_secret'. You must change that password on your first connect, no other statement but 'SET PASSWORD' will be accepted. See the manual for the semantics of the 'password expired' flag. Also, the account for the anonymous user has been removed. In addition, you can run: /usr/bin/mysql_secure_installation which will also give you the option of removing the test database. This is strongly recommended for production servers. See the manual for more instructions. Please report any problems with the …[Read more]
I’ve often wished that PROCESSLIST exposed when a connection was first established, and I find myself wishing for this information more now with MySQL 5.6. Improvements to PERFORMANCE_SCHEMA make it trivial to see how much time is being spent in various operations for a given connection – but it would make some analysis (“what percentage of connection time is spent doing X?”) easier.
That said, it is possible to approximate connection age with PERFORMANCE_SCHEMA in MySQL 5.6. I say “approximate” because results will vary based on what instrumentation exists, is enabled, and is collecting timing data. That’s because we’re just doing a SUM() on the SUM_TIMER_WAIT column for all instrumented waits. Here’s an example (FYI, I’m using the format_time() function from Mark Leith’s …
[Read more]