MySQL Security Essentials

February 15, 2012

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

Filed Under: Presentation

Better MySQL Security and Administration

October 26, 2011

Download PDF Presentation

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

Filed Under: Best Practice, Presentation Tagged With: administration, data integrity, hacking, installation, mysql, security

Successful Scalability Principles – Part 1

October 8, 2011

Download PDF
Presentation

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

Filed Under: Presentation

Oracle Open World 2011 Presentations

October 4, 2011

MySQL Explain
download pdf

Better Indexes

At Oracle Open World 2011 I gave two presentations.

You can download updated versions of Explaining the MySQL Explain and Improving Performance with Better Indexes presentations.

Want to know more, check out our Effective MySQL: Optimizing SQL Statements book that goes into great detail expanding on these presentations.

Improving MySQL Performance with better indexes

Looking for other MySQL presentations at Oracle Open World 2011. Check out

Filed Under: Event, Indexes, Performance Analysis

Backup and Recovery

September 30, 2011

The second book of the Effective MySQL series provides the reader with the tools, knowledge and tips necessary for a successful MySQL Backup and Recovery Schedule. Included in this book are examples of mysqldump, LVM snapshots, mylvmbackup, MySQL Enterprise Backup (MEB), Xtrabackup and mydumper. Advanced options including compression, incremental backups, partial backups and remote capabilities are discussed and examples provided with an example 5GB database. Effective MySQL: Optimizing SQL Statements by Ronald Bradford

Chapters

The Five Minute DBA – Download Sample Chapter (PDF)
Understanding Backup Options
Understanding Business Requirements
Using Replication
Understanding Recovery Options
MySQL Configuration Options
Disaster Scenarios
Optimizing Backup & Recovery
MySQL in the Cloud

All links in the book can be referenced in links.txt

Order Now

This title is available print, and ebook formats.
Kindle Version from Amazon & PDF version from McGraw-Hill.

Other Titles

See other titles in our series including:

Optimizing SQL Statements

September 29, 2011

The first book of the Effective MySQL series provides the reader with the tools, knowledge and tips necessary for optimizing SQL Statements in MySQL. Effective MySQL: Optimizing SQL Statements by Ronald Bradford

Written by Oracle ACE Director and MySQL expert Ronald Bradford, Effective MySQL: Optimizing SQL Statements is filled with detailed explanations and practical examples that can be applied immediately to improve database and application performances. Featuring a step-by-step approach to SQL optimization, this Oracle Press book helps you to analyze and tune problematic SQL statements.

Identify the essential analysis commands for gathering and diagnosing issues
Learn how different index theories are applied and represented in MySQL
Plan and execute informed SQL optimizations
Create MySQL indexes to improve query performance
much more…

Chapters

The Five Minute DBA – Code Examples – Download Sample Chapter (PDF)
The Essential Analysis Commands – Code Examples
Understanding MySQL Indexes – Code Examples
Creating Indexes – Code Examples
Creating Better Indexes – Code Examples
MySQL Configuration Options – Code Examples
The SQL Lifecycle – Code Examples
Hidden Performance Tips – Code Examples
Explaining the MySQL Explain – Code Examples

Buy Now

This title is available in print, and ebook formats from these retailers.

Get the Code

In addition to the individual chapter links provided above you can download all SQL examples via github with the following command.

$ git clone git://github.com/effectiveMySQL/OptimizingSQLStatements.git

Other Titles

See other titles in our series including:

Effective MySQL: Backup and Recovery
Effective MySQL: Advanced Replication Techniques

Next New York Meeting – Understanding Indexing

September 22, 2011

For our next meeting we have a guest speaker, Zardosht Kasheff, a frequent attendee at Effective MySQL. He will be talking about “Understanding Indexing”.

Registration Details

Event Date: Tuesday, October 11, 2011, 7:00 PM
Event Location: Hive at 55, 55 Broad Street, 13F,New York, NY 10004

We are switching to using EventBrite for registration. Please register at http://effectivemysql2011oct.eventbrite.com

The cost for event is $5 to cover the event space.

Agenda

7:00 Meet and Greet
7:15 Presentation
8:15 Q & A
8:30 Networking

Presentation Details

Indexes are used to improve query performance. As a result, good indexes are designed around queries that users find important in their application. This talk presents three simple and effective rules on how to construct indexes around queries that result in good performance.

The rules are explained using a simple model that does NOT rely on understanding B-Trees, Fractal trees, or any other data structure used to store the data on disk. They are derived from these simple properties:

point queries are slow
range queries are fast

Zardosht has been a senior member of Tokutek’s TokuDB engineering team since 2008. Leveraging Fractal Tree Indexing, TokuDB is a high performance storage engine for MySQL that is designed for write-intensive workloads

Filed Under: Event

Reasons to use MySQL 5.5

June 28, 2011

Download PDF Presentation

Download Audio

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

Filed Under: Presentation Tagged With: 5.5, innodb plugin, mysql, performance

Improving Performance with Better Indexes

May 30, 2011

Download PDF Presentation

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
Schedule:

May 2011 – Percona Live New Yorkin New York, NY.
February 2012 – RMOUG Training Day in Denver, CO.
July 2012 – Latin America MySQL/NoSQL/Cloud Conference in Buenos Aires, Argentina.

Filed Under: Indexes, Presentation

MySQL Best Practices for DBAs and Developers

April 13, 2011

Download PDF
Presentation Part I

Download PDF
Presentation Part 2

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.

Filed Under: Presentation