Tag Archives: MySQL

Rosetta Stone: MySQL, Pig and Spark (Basics)

In a world where new data processing languages appear every day, it can be helpful to have tutorials explaining language characteristics in detail from the ground up.  This blog post is not such a tutorial.   It also isn’t a tutorial on getting started with MySQL or Hadoop, nor is it a list of best practices for the various languages I’ll reference here – there are bound to be better ways to accomplish certain tasks, and where a choice was required, I’ve emphasized clarity and readability over performance.  Finally, this isn’t meant to be a quickstart for SQL experts to access Hadoop – there are a number of SQL interfaces to Hadoop such as Impala or Hive that make Hadoop incredibly accessible to those with existing SQL skills.

Instead, this post is a pale equivalent of the Rosetta Stone – examples of identical concepts expressed in three different languages:  SQL (for MySQL), Pig and Spark.  These are the exercises I’ve worked through in order to help think in Pig and Spark as fluently as I think in SQL, and I’m recording this experience in a blog post for my own benefit.  I expect to reference it periodically in my own future work in Pig and Spark, and if it benefits anybody else, great. Continue reading Rosetta Stone: MySQL, Pig and Spark (Basics)

New MySQL Online Training

Oracle University recently unveiled a new online training offering – the MySQL Learning Subscription.  The combination of freely-accessible and compelling paid content makes this an exciting development to me, and should prove valuable to the community and customer base alike.  This post will briefly explore this new MySQL educational resource.

Continue reading New MySQL Online Training

SYS Schema: Simplified Access To SSL/TLS Details

A while back, I wrote a blog post explaining how PERFORMANCE_SCHEMA improvements in MySQL Server 5.7 provides new visibility into the SSL/TLS status of each running client configuration.  An excellent recent post from Frederic Descamps at Percona covers similar territory.  Both of us use PERFORMANCE_SCHEMA tables directly – a powerful interface, but one that requires a query joining multiple tables.  Thanks to the excellent work of Mark Leith, and a contribution from Daniël van Eeden, access to this same information is made far easier via the SYS schema. Continue reading SYS Schema: Simplified Access To SSL/TLS Details

Leaving MySQL

After nearly ten years working for MySQL, I’m pursuing a new opportunity to expand into new areas of open source data infrastructure as part of the excellent Cloudera support organization.  I’ve been fortunate to work with incredibly talented, dedicated and wonderful people on relational databases, and I’m looking forward to doing the same in the Hadoop space in my new role.  Despite this transition, I intend to remain active in the MySQL community – most immediately, finishing off a handful of half-finished blog posts in the coming weeks.

My various bit roles at MySQL have given me a front-row seat as the company grew from a smaller independent company to a prominent product at Sun to part of a much larger, enterprise-focused portfolio within Oracle.  I’m incredibly proud of the progress MySQL has made over the years, in each stage – but the past 6 years under the stewardship of Oracle are particularly satisfying.  The Oracle way of doing things is well-understood, and has historically produced very successful results – for the products, the customers and the business – but it’s not for everybody.  While I certainly appreciate the motivation of those who wanted to continue an independent MySQL tradition outside of Oracle, my heroes are the committed MySQL staff who stayed to ensure MySQL flourished inside Oracle.  Thanks for all you have done – and continue to do – to ensure MySQL is strong and gets better.

Oracle isn’t perfect, and there have been mistakes made along the way, and things I still wish could change today.  It’s a big company, and MySQL is a small part of it.  But there is an incredible dedication within the MySQL team at Oracle to improve products and experiences for both community users and customers alike.  There’s also a number of legacy Oracle staff who have worked hard to position MySQL for success inside Oracle, and to help apply and adapt Oracle ways of doing things to add value for MySQL users.  Keep up the good work – I know I’m excited to see what the future holds for MySQL.

Simplified SSL/TLS Setup for MySQL Community

Transport Layer Security (TLS, also often referred to as SSL) is an important component of a secure MySQL deployment, but the complexities of properly generating the necessary key material and configuring the server dissuaded many users from completing this task.  MySQL Server 5.7 simplifies this task for both Enterprise and Community users.  Previous blog posts have detailed the changes supporting Enterprise builds; this blog post will focus on parallel improvements made to MySQL Community builds.

Continue reading Simplified SSL/TLS Setup for MySQL Community

Which accounts can access this data?

Knowing which privileges a given account has is easy – just issue SHOW GRANTS FOR user@host.  But what about when you need visibility into privileges from the other direction – which accounts can access specific data?  If you’re a DBA – or perform DBA duties, regardless of your title – you may have been asked this question.  It’s an important question to ask in an audit or compliance review – but it can be a difficult question to answer.    This post will walk through how to assess this, but if you’re impatient and need answers to this question immediately, jump to the end – there’s a simple shortcut. Continue reading Which accounts can access this data?

Practical P_S: Find Client JRE Version Using SQL

MySQL Connector/Java supports connection attributes since version 5.1.25.  This projects useful metadata about the client environment into the database, where MySQL administrators can query PERFORMANCE_SCHEMA tables to remotely survey application deployment environments.  One useful piece of information exposed is the version and vendor of the JVM in use by the client.  This very short blog demonstrates how to get this information from PERFORMANCE_SCHEMA.

Continue reading Practical P_S: Find Client JRE Version Using SQL

Protecting MySQL passwords with sha256_password plugin

Over the years, MySQL has used three different mechanisms for securing passwords both for storage and for transmission across networks.  This blog post aims to provide a brief history of the various mechanisms and highlight reasons to migrate accounts to use the sha256_password mechanism introduced in MySQL Server 5.6. Continue reading Protecting MySQL passwords with sha256_password plugin

MySQL High Availability with Oracle Clusterware

MySQL has an extensive range of high-availability solutions to suit many different use cases and deployment needs.  This list spans from the time-tested – yet continuously-improved – MySQL replication to the just-released MySQL Fabric, giving users many certified solutions for highly available MySQL deployments.  The list is growing yet again, with Oracle Clusterware adding support for MySQL.

Oracle’s Clusterware product is the foundation for the Oracle RAC, and has been battle-tested for high availability support for Oracle database, as well as other Oracle applications.  This technology is now available as part of the MySQL Enterprise subscription, and – like all Oracle commercial products – is freely available for evaluation purposes.  This post will explain Oracle Clusterware architecture and the benefits to MySQL users, and will be followed by a later post focusing on how to deploy Clusterware agents with MySQL.

A very flexible architecture gives Oracle Clusterware the ability to support various consistency mechanisms.  The initial release of the Clusterware agent for MySQL uses a shared resource approach, where essential resources – such as the data directory – are deployed on a shared disk.  A similar strategy is employed in other high-availability solutions (OVM High Availability Template for MySQL, Oracle Solaris Clustering, MySQL with Windows Cluster Failover).  The flexibility of Clusterware doesn’t dictate a specific shared resource implementation – anything from a simple NFS mount to a high-performance SAN may be used.  The recommended and tested solution leverages the Oracle ACFS filesystem.  As with other shared-disk high availability solutions for MySQL, an Oracle Clusterware-based solution requires only one MySQL instance be using a shared MySQL data directory at any one time.

While no high availability solution for MySQL is truly transparent, the Clusterware system provides useful infrastructure to minimize downtime.   The agent performs periodic health checks of the running MySQL Server using mysqladmin, and applications connect through a managed virtual IP address.  The use of a managed virtual IP address directs application traffic to a failover host without requiring configuration changes at the application layer. Failover time is bounded by the interval of agent health checks (every second by default) plus the time required to start the MySQL Server on the failover host (including any necessary crash recovery processing).

A big thanks goes out to the Oracle Clusterware team who did the heavy lifting in adding MySQL support!

 

 

Password expiration policy in MySQL Server 5.7

I’ve previously noted my wish to have a comprehensive password policy in MySQL Server.  MySQL Server 5.7.4 takes a significant step towards this goal by adding native support for enforcing password lifetime policy.  This complements the validate_password plugin introduced in MySQL Server 5.6, which helps ensure adequate password complexity, and builds on the password expiration mechanism also introduced in MySQL Server 5.6.  This new feature has a new documentation page, and is covered in the MySQL Server 5.7.4 change logs, which state:

MySQL now enables database administrators to establish a policy for automatic password expiration: Any user who connects to the server using an account for which the password is past its permitted lifetime must change the password.

Good stuff – let’s look at it in some detail. Continue reading Password expiration policy in MySQL Server 5.7