Sunday, January 20, 2019

Testing and verifying your MySQL backup strategy presentation

From the IAOUG Gold Coast OTN Day, Ronald Bradford gave a presentation on “Testing and Verifying your MySQL Backup Strategy”. Details in this presentation included:

  • Product options
    • mysqldump
    • mysqlpump
    • mydumper
    • Xtrabackup
    • MySQL Enterprise Backup
    • LVM/SAN Snapshot
    • Filesystem copy
  • Binary log backup options
  • B&R Strategy considerations
    • Time to backup
    • Time to restore
    • Consistency
    • Flexibility
    • Partial Capabilities
    • Cost
  • Technical Requirements
  • Testing & Verification
  • Using Failover

More information about mysqlpump available in MySQL 5.7 can be found at Introducing mysqlpump, playing with mysqlpump and mysqlpump reference manual.

Thanks to Pythian and more4apps for hosting the event.

MySQL Role-Based Security, Data Masking and Auditing Presentation

At our September 2016 New York City MySQL Meetup was a demonstration of how to implement role-based security in MySQL using Hexatier. In addition, several other important security features demonstrated included role based dynamic data masking down to a per column level and full statement auditing.

Thanks to Scott Unrick, Lead Database Administrator at Teladoc for the great presentation. Slides are available here.

MySQL @ Twitter NY Meetup Presentation

At our August meetup, our guest Calvin Sun gave a presentation on life @ Twitter. Some of the details of his presentation included:

  • Information on existing data and storage solutions in operation.
  • MySQL 5.6 performance improvements over MySQL 5.5
  • The short upgrade cadence, most servers are running 5.6.26.
  • Various patches and optimizations developed by Twitter to run at scale.
  • Cotton on Mesos
  • Best practices

  • Thanks to our sponsors for August.

High Performance SQL and NoSQL Presentation

In this presentation on High Performance SQL and NoSQL to the New York Web Performance Meetup I use the simple analogy of applying the same techniques used in Web Performance Optimization (WPO) to your data path (SQL or NoSQL) performance.

This presentation provides examples for 5 of the most common WPO optimizations and how to apply this to your applications access to data:

  1. Gzip assets
  2. Make fewer HTTP requests
  3. Add expires headers
  4. Use a CDN
  5. Optimized Images

This presentation was recorded. A video link will be available at a later time.

Thanks to Grovo for hosting the event.

MaxScale is now GA presentation

Thank you to Jon Day and Jaimee Swiderski from MariaDB for the organization and presentation of “MaxScale is now GA” to our New York MySQL meetup group.

For those that are not familiar with MaxScale in the MySQL ecosystem, from the initial slides:

What is MaxScale?

The simple answer is a classical proxy that sits between the database clients and servers forwarding requests and responses.
The more detailed answer is a proxy platform for building highly tailored and configurable proxy implementations, that is database aware, and that is built with a pluggable architecture.

The production information and software can be found at
The source code can be found at
The MaxScale blogs can be found at

MySQL Operations

This presentation from the MySQL/NoSQL/Cloud Conference in Latin America provides my experiences of the most common problems managing MySQL Operations in general with customers, and a number of current problems experienced recently.

Many consider the ease of installation and use of MySQL as a selling point, however MySQL installation is not install and forget. It sounds simple, however there are many important decisions that have long lasting effects. The choice of version, choice of repo, choice of variant is just the beginning.

The appropriate configuration before any application uses the MySQL instance is also critical. Some decisions can lead to data integrity issues that can be difficult or impossible to correct without expensive rework. The decision regarding timezone management is one point discussed in detail.

However the greatest impact to the time and energy of MySQL operations is application development. The use of frameworks, the absence of knowledge of writing SQL, the lack or abuse of transactions all contribute to unnecessary complexity and stress in ensuring a performing and highly available MySQL infrastructure.

Day to day, tasks including testing and managing MySQL upgrades in a 24x7x365 environment, changing QEPs due to data distribution, complexities in point in time recovery with triggers, ROW base replication are all edge cases that have to be experienced to understand that MySQL operations is a detailed process to manage and support.

The outline of this presentation includes:

  • What is Operations
  • Installation
  • Configuration
  • Operations Woes
  • Day to day problems
  • Other experiences

MySQL Backup & Recovery Essentials

A hardware, software or human failure can occur at any time. Are you prepared?
Many organizations take a risk of serious data loss and system downtime with inadequate procedures in place to support a disaster recovery. This presentation covers the essentials of MySQL backup and recovery options, identifying the necessary tools for an effective strategy to support data resilience and business continuity for your organization. MySQL has no one single unbreakable backup solution, so it is important to understand the impact of MySQL replication, storage engines, configuration options for durability, hardware configuration and the impact on locking and uptime for the various hot/warm/cold options available.

Short Url:
Presenter: Ronald Bradford

Lessons Learned Managing Large AWS Environments

At Cloud Expo East 2013 I gave a presentation of my experiences in dealing with large AWS environments. In summary this included:

  1. Understanding AWS Products (especially the growth of new/revised products and pricing)
  2. Knowing the true Cost of your deployment and options with instances types, sizes and products
  3. Being prepared for Web Scale
  4. The importance of Security, what exists, the minimum you need to know working with external resources
  5. Why most Instrumentation fails due to sampling, and determining what is important for your business needs
  6. Failure will happen, not if, but when. Some things you should know

The history and future of the MySQL Ecosystem

This week I was the guest speaker at the New York Linux Users Group (NYLUG) Meetup where I had the opportunity to talk about The history and future of the MySQL Ecosystem. This talk, titled “”Spaghetti and MySQLBalls (with a side of greens)” detailed the beginnings of MySQL, the MySQL acquisition history, described the state of current MySQL versions/variants/forks, storage engines, related vendors and much more. It was actually great to reminisce and share some of the great stories of the fantastic team at MySQL Inc/AB, and it amazed me as I presented just how many former employees have created companies in the ecosystem.

These Linux guys were also a great group to talk to, with lots of good questions and feedback. Many thanks to comments verbally and online including, “One of the best meetups I have been to. Ronald Bradford gave a great talk.”, “Ronald’s presentation was great. … the presentation really helped clear up a lot of longstanding confusion on the myriad of MySQL choices available…” and “Superb presentation and exactly what it said on the tin.”

A video of the presentation will be available soon.

New MySQL 5.6 Replication Features

There is a long list of new replication features in the latest MySQL 5.6 release including:

  • Binary log group commit
  • Multi-threaded slaves
  • Crash-save slaves
  • Global Transaction Identifier (GTID)
  • Replication checksums
  • Optimized row-based replication
  • Time-delayed replication
  • Remote binary log backup
  • UUID

This presentation from the MySQL Tech Tour events in New York and Boston provides an overview and syntax examples of many of these features. The Effective MySQL: Replication Techniques in Depth also discusses many of these features in detail. Chapter 3 is available for FREE download.

MySQL 5.6 features for NoSQL, Big Data and the Cloud

At the recent MySQL Tech Tour Events in New York and Boston I gave a presentation on MySQL integration with NoSQL,Big Data and the Cloud.

This covered discussion on topics including:

  • Memcached API for InnoDB
  • InnoDB Online Alter
  • InnoDB Full Text Search (FTS)
  • Partitioning inprovements for import/export
  • SSD Optimisations
  • Replication Improvements
  • And much more …

Secrets of mysqlnd Presentation

At the recent New York PHP Group in New York, I gave a presentation on “Secrets of mysqlnd”. While there are no real “secrets” of the new MySQL Native Driver for PHP, available in PHP 5.3, and the default for PHP 5.4+ replacing libmysqlclient, there are new features of the driver many are unaware of. If you have ever had to fight the gods with compiling a particular version of PHP with the right libmysqlclient dependency, the good news is that legacy requirement is no longer necessary.

Some of the topics discussed in detail included support for read/write splitting and load balancing with existing applications, and the implications for consistency with MySQL replication. Mysqlnd can also provide failover capabilities, and when combined with using MySQL cluster, Tungsten Replicator and HA manages including MMM and MHA, a more smoother application management of failover in the future may be possible with applicable design.

Mysqlnd also provides a number of other plugin extensions which shows the potential of this new driver for the future. There is still a lot of work to be done, particularly with getting distros and package management to support all capabilities for ease of installation and management.

Lessons learned managing large AWS Environments

At the recent AWS Meetup group in New York, I gave a presentation on “Lessons learned managing large AWS Environments”.

This included my experiences in managing and optimizing AWS costs, CLI automation for web scale (my work at GitHub), knowing about and improving security, what is important monitoring and dealing with failure.

Managing 500+ MySQL servers in the AWS cloud

This presentation discusses the lessons learned about the issues of managing a large number of MySQL instances, Including experiences from multiple clients including supporting one billion+ requests statements per day (and 50+ billion SQL statements), and smaller users of LAMP stack, Microsoft stack and RDS configurations. Topics include:

  • Monitoring and instrumentation are essential to manage cost
  • Cost saving techniques
  • How to automate installations, upgrades and deployments
  • Traffic minimization techniques
  • Creating HA with regions and zones
  • Real-time traffic stats (aggregated every 5 seconds)
  • AWS specific optimizations with EBS
  • Considerations and limitations with RDS

Presenter: Ronald Bradford

MySQL Disasters, and how to avoid yours

Organizations are always making improvements for scalability, however disaster preparedness is the poor cousin. This presentation will show you how to easily avoid the most common MySQL disaster situations.
Backup and recovery is critical for business continuity, many websites run the risk of data loss or corruption because existing procedures (if any) are generally flawed. Discussion includes:

  • The essential backup options
  • Why Binary logging for point in time recovery is important
  • How replication changes things
  • Recovery complexities

Presenter: Ronald Bradford

LAMP Performance Optimizations – Volume 1

Get a detailed introduction to the very broad topic of end-to-end performance optimization in web applications based on LAMP stack (Linux, Apache, MySQL, PHP).

Short Url:
Presenter: Ronald Bradford

MySQL Idiosyncrasies That Bite

While MySQL is a popular and widely used database product, there are some default features and settings which can be foreign in comparison with other commercial RDBMS products such as Oracle. In this discussion, Ronald Bradford will discuss some of the MySQL defaults that are not what you may expect. These include understanding the concept of storage engines, and the default non-transactional state, how silent data truncations occur which affect your data, ideal practices for date management, and the MySQL transaction isolation options. These are all critical to clearly understand and implement correctly for data integrity and consistency. He will cover in-depth topics including SQL_MODE and the recommended best practices for default settings, the ideal user permissions and privileges including not what to do, and also the best practices for character sets and collations to ensure your UTF8 is stored and retrieved correctly.

Presenter: Ronald Bradford
Percona Live – Santa Clara, April 2012
New York MySQL Meetup, April 2011, New York

Explaining the MySQL EXPLAIN

Determining the Query Execution Plan (QEP) of an SQL statement is the primary analysis tool for DBAs. Understanding how to interpret the information from the EXPLAIN command and what additional commands and tools exist to add supplementary information are essential skills that will be used daily in production operations.

The MySQL EXPLAIN QEP is significantly different from an Oracle QEP and Oracle DBAs need to understand and learn the most appropriate way to understand and navigate this information to effective performance tune a running MySQL environment.

In this presentation we will cover the following:

Presenter: Ronald Bradford

MySQL Security Essentials

Default MySQL security sucks. There is no super user privilege protection, or tight filesystem security. What are the essentials for any initial MySQL installation? What is the minimum an Oracle DBA should understand and undertake for a neglected MySQL system? The MySQL privilege system provides several levels of data protection when implemented correctly, however this is rarely used. The most common user permission implementation creates several security and auditing risks? What are your MySQL user permissions, and how can developers exploit them? There are ways to secure communications within a MySQL topology, approaches to managing exposed public facing data collection via the BLACKHOLE storage engine, auditing plugin interfaces, and external authentication capabilities with PAM and LDAP for example.

This presentation provides an overview and checklist of the essentials needed to improve MySQL security and provide an understanding of managing various levels of risk.

Presenter: Ronald Bradford
Schedule: RMOUG Training Days 2012 February 2012 Denver, Colorado. Insight Out DB Showcase. October 2011 Tokyo, Japan

Better MySQL Security and Administration

With the recent cyber attacks and breaches with data from large organizations including Sony, is your MySQL data safe? What are the best practices for securing and administering your MySQL environment? In this presentation we will cover the essential steps for better MySQL security. We will also cover the different installation and administration tasks necessary to ensure your data is managed.

Presenter: Ronald Bradford
Schedule: Insight Out DB Showcase. October 2011 Tokyo, Japan

Successful Scalability Principles – Part 1

Learn how the experts would design and architect a MySQL system to able to scale seamlessly. This presentation covers the necessary principles including:

  • System Architecture
  • Data Availability
  • Best Practices
  • Being proactive
  • Preparing for a disaster
  • Planning for success

Presenter: Ronald Bradford

You can view the video from SF MySQL Meetup – September 2010. View Meeting details and comments

Reasons to use MySQL 5.5

There are a number of significant new features in MySQL 5.5 including semi-synchronous replication, SIGNAL and RESIGNAL, the PERFORMANCE_SCHEMA, additional STATUS variables, new partitioning options, different default storage engine, better UTF8 support and removal of deprecated functions just to list key considerations.

However some of the performance improvements are worth the investment of time. For a high concurrency InnoDB environment one new configuration alone can provide a 50% improvement in performance. Other settings can help in the reduction of downward spikes in performance due to internal InnoDB disk management. Some new configuration settings are off by default. See the actual benefits of these new features and what you should be using to maximize your database performance.

Presenter: Ronald Bradford
Schedule: Effective MySQL Meetup York. June 2011 New York

Improving Performance with Better Indexes

Learn how to use one simple advanced technique to make better MySQL indexes and improve your queries by 500% or more. Even with a highly indexed schema significant improvements in performance can be achieved by creating better indexes.

This presentation introduces the approach for correct identification and verification of problem SQL statements and then describes the means of identifying index choices for optimization. Then discussed is not only how to apply indexes to improve query performance, but how to apply better indexes and provide even greater performance gains.

This presentation includes:

  • 6 steps to successful SQL review
  • Effective examples of capture SQL via application logging and TCP/IP analysis
  • All the commands necessary to identify why and how to create indexes
  • How the number of table rows and different storage engines can effect query performance
  • How to create an iterative verification process

Presenter: Ronald Bradford

MySQL Best Practices for DBAs and Developers

Learn the right techniques to maximize your investment in MySQL by knowing the best practices for DBAs and Developers. Learn what subtle differences between Oracle and MySQL are essential to understand in order to maximize the benefits of MySQL. We will be covering the ideal means for writing SQL statements, documenting your SQL, analyzing your SQL, MySQL software installation, configuration and maintenance best practices.

This new revised and expanded presentation now comes in 2 x 1 hr presentations.

Presenter: Ronald Bradford
Schedule: MySQL Users Conference 2011 – Santa Clara California.

Common MySQL Scalability Mistakes

The most common mistakes are easy to avoid however many startups continue to fall prey, with the impact including large re-design costs, delays in new feature releases, lower staff productivity and less then ideal ROI. All growing and successful sites need to achieve higher Availability, seamless Scalability and proven Resilience. Know the right MySQL environment to provide a suitable architecture and application design to support these essential needs.

  • The different types of accessible data (e.g. R/W, R, none)
  • What limits MySQL availability (e.g software upgrades, blocking statements, locking etc)
  • The three components of scalability – Read Scalability/Write Scalability/Caching
  • Design practices for increasing scalability and not physical resources
  • Disaster is inevitable. Having a tested and functional failover strategy
  • When other products are better (e.g. Static files, Session management via Key/Value store)
  • What a lack of accurate monitoring causes
  • What a lack of breakability testing causes
  • What does “No Downtime” mean to your organization
  • Implementing a successful “failed whale” approach with pre-emptive analysis
  • Identifying when MySQL is not your bottleneck

Presenter: Ronald Bradford
Schedule: IOUG Collaborate 11 – Orlando, Florida. OTN LA Tour – South America. Surge Scalability 2010 – Baltimore, Maryland.

Better Indexes $ave You Money

Can database performance improvements be achieved with zero code changes? Learn how to use one simple advanced technique to make better MySQL indexes and improve your queries by 500% or more. Even with a highly indexed schema as shown in our 10 table join example, significant improvements in performance can be achieved.

This presentation introduces the approach for correct identification and verification of problem SQL statements and then describes the means of identifying index choices for optimization. Then discussed is not only how to apply indexes to improve query performance, but how to apply better indexes and provide even great performance gains.

This presentation includes:

  • 6 steps to successful SQL review
  • Basic ways via application logging and TCP/IP analysis to capture SQL
  • All the commands necessary to identify why and how to create indexes
  • How the number of table rows and different storage engines can effect query performance
  • How to create an iterative verification process

Adding indexes is not the only approach nor the best approach to query optimization however learning how MySQL indexes work can provide quick improvements without the need to deploy code changes.

Presenter: Ronald Bradford
Schedule: Tuesday, March 22, 2011 in New York

MySQL HA Solutions

This presentation discusses high-availability techniques, sharding fundamentals, third-party technologies, and how to decrease the likelihood of system outages. It covers the following topics:

  • Hardware specifications to consider
  • Functionally sharding your data
  • Why to use various topologies such as a relay slave, master-master replication, or circular replication for high availability
  • Failover mechanisms you should consider with replication, such as keepalived and heartbeat
  • How to safely and reliably replicate over a WAN for disaster recovery
  • Mixing up storage engines (leveraging the Blackhole engine, for instance)
  • Third-party options for sharding your data

Presenter: Chris Schneider

Successful MySQL Scalability

Learn how the experts would design and architect a MySQL system to able to scale seamlessly. This presentation covers the necessary principles including:

  • System Architecture
  • Data Availability
  • Best Practices
  • Being proactive

Presenter: Ronald Bradford

Understanding MySQL Indexes

The correct implementation of indexes is critical to improving SQL performance in any RDBMS. However, there are some basic problems regarding how MySQL and MySQL Storage Engines implement indexes in relation to other RDBMS products including Oracle.

In this presentation attendees will learn how to identify and verify MySQL index usage, what essential MySQL Tools exists for determining how to create better indexes and be able to verify the results of improvements. Understanding how the optimizer chooses indexes, generally only one index per table join and also the exceptions. Learn about advanced topics including covering indexes and partial column/prefix column indexes.

There will also be specific examples of how indexes differ between the popular MySQL storage engines including InnoDB, MyISAM, Memory and other available third party storage engines.

The MySQL database, a recent acquisition of Oracle is commonly found with the Oracle RDBMS in many organizations. While MySQL is a relational database there are some fundamental differences in how indexes are best used in MySQL. Techniques used by Oracle Architects and DBAs do not always result in the most optimal possible solution.

See also the “Explaining the MySQL EXPLAIN” presentation.

This presentation is being delivered in 2011. Check back for dates and locations.