Hacking MySQL: Breaking, Optimizing, and Securing MySQL for Your Use Case

Table of Contents....5

About the Author....12

Acknowledgments....13

Preface....14

Foreword....18

Part I: The Basics of MySQL....19

Chapter 1: The World of MySQL....20

The History of MySQL....20

The Architecture of MySQL....21

Basic Use Cases and Initial Considerations....23

Storage Engines....24

Other Storage Engines....25

Summary....26

Chapter 2: Individual Storage Engines....27

The Modern King of Storage Engines....27

InnoDB in MySQL and MariaDB....27

Storage Engines in Percona Server....36

Percona MyRocks and TokuDB....37

MyRocks....37

TokuDB....39

The Primary Contestant of InnoDB....40

The Early Days of MyISAM....40

MyISAM in the Present Day....41

Storage Engine Use Cases....42

The MEMORY and TempTable Storage Engines....42

The CSV Storage Engine....43

The ARCHIVE and BLACKHOLE Storage Engines....44

The MERGE Storage Engine....45

The Storage Engine for High Availability....46

Installing and Using NDB....47

The FEDERATED and EXAMPLE Storage Engines....50

Storage Engines Exclusive to MariaDB....51

Summary....53

Part II: Breaking MySQL....54

Chapter 3: What Breaks MySQL?....55

MySQL Use Cases....55

Problematic Use Cases....56

Performance Hiccups....57

Availability Issues....59

Security Problems....60

Understanding Your Data....61

Choosing the Proper Schema and Data Types....62

Character Sets and Collations....65

Your Architecture Is a Mess....67

Communicating with MySQL Through Software....69

Top Causes of Slow Query Performance....70

Summary....73

Chapter 4: How You Broke Your Queries....74

The Good, Bad, and the Ugly: Understanding Queries in MySQL....74

How You Broke Queries in MySQL....75

Types of Queries in MySQL....75

Factors Breaking DML Queries in MySQL....76

Factors Breaking DDL Queries in MySQL....78

Factors Breaking DCL and TCL Queries in MySQL....81

Why Are Queries Slow?....81

Devising the Perfect Schema Design....87

Understanding Data Types....90

Understanding Character Sets and Collations....93

Understanding Indexes....97

Understanding Partitions....99

Partitions and Big Data....102

NULL Values and Pruning in Partitions....103

Things to Avoid When Optimizing Queries in MySQL....105

Dont Blindly Trust Documentation....106

Summary....108

Chapter 5: Understanding Query Components....109

SQL Queries and Stored Procedures....109

Parsers and Optimizers....111

Queries and Error Messages....115

Common MySQL Error Codes....116

Factors Disliked by Your Queries....118

Isolate Your Columns!....119

Get Rid of Duplicate Indexes....119

Use EXISTS Instead of IN....120

Make Use of Stored Procedures and Triggers....120

SHOW STATUS and EXPLAIN....122

Summary....126

Chapter 6: Understanding Your Server....127

Efficiently Using Server Resources....127

Understanding and Simulating Errors....129

Server Components and Their Interaction with MySQL....132

Coding for MySQL Performance and Security....136

What Not to Do....140

Summary....141

Part III: Optimizing MySQL....143

Chapter 7: Optimizing Your Server for MySQL....144

Why Optimize Your Server for MySQL?....144

Common Webserver Issues Affecting MySQL....147

What Limits the Performance of MySQL?....149

Choosing Servers and Hard Drives....150

Configuring MySQL Parameters....151

Configuring MySQL IO for Your Operating System....156

Testing Your Hardware....158

Taking Advantage of ACID Properties....161

Summary....164

Chapter 8: Optimizing Storage Engines, Schemas, and Data Types....165

Why Optimize Storage Engines and Data Types?....165

Optimizing Storage Engines....166

Data Types in MySQL....169

String-Based Data Types....170

Numeric Data Types....170

Date, Time, Spatial, and JSON Data Types....171

Storage Requirements for Data Types....172

Choosing the Right Data Type....174

Optimizing Schemas and Data Types for Big Data....175

Summary....177

Chapter 9: Optimizing Queries....178

Why Optimize SQL Queries?....178

Optimizing Specific Types of Queries....180

The Query Cache....180

Optimizing INSERT Queries....182

Optimizing SELECT Queries....186

Optimizing Indexes and Constraints....191

Optimizing UPDATE Queries....196

Optimizing DELETE Queries....200

Optimizing Queries for Big Data, Avoiding Deadlocks, and Other Query Optimization Tips....201

Summary....203

Chapter 10: Optimizing MySQL for Big Data....204

Can MySQL Deal with Big Data?....205

MariaDB and Big Data: Operations with Big Data Sets....206

Inserting Big Data Into MariaDB....207

Reading Big Data with MariaDB....209

Updating Big Data in MariaDB....211

Deleting Big Data From MariaDB....212

Storage Engines and Big Data....213

ACID and Big Data....214

Big Data Pitfalls and Known Issues....215

Summary....217

Chapter 11: Indexing MySQL....218

Why Index? Indexes Available in MariaDB....218

What and When to Index?....222

Indexing Myths, Misconceptions, and Fragmentation Issues....225

Your Hardware, Database, and Indexes....227

Types of Indexes....229

B-Tree Indexes....230

Covering Indexes....237

Multicolumn (Composite) Indexes....241

Prefix Indexes....244

Spatial (R-Tree), Hash, and Clustered Indexes....245

Devising the Perfect Index Design....247

Indexing for Performance and Big Data....251

Summary....256

Chapter 12: Optimizing Partitions....257

Why Partition Data?....257

When to Partition Data?....258

Internals of Database Partitioning....260

Types of Partitioning in MySQL....262

Partitioning Tips: Subpartitioning, Limitations, NULL Values, and More....269

Summary....276

Chapter 13: Optimizing Backups and Recovery....277

Why, When, and How to Backup MariaDB?....277

Backup Types and Tools....280

Backup Compression and Security....286

Backing Up Big Data Sets....288

Recovering MariaDB....291

Recovering Big Data....292

Backup and Recovery Pitfalls....293

Pitfalls for Big Data....294

Summary....297

Chapter 14: Optimizing Replication....298

Understanding Replication....298

Configuring and Implementing Replication....299

Types of Replication....302

Replication Notes and Tips....305

Securing Replication....306

Summary....308

Chapter 15: Optimizing for Security....309

Understanding Security in MariaDB....309

Securing MariaDB upon Installation....313

General Security Measures....315

Summary....316

Part IV: Securing MySQL....317

Chapter 16: The World of Security in MySQL....318

General Security Guidelines and Measures Revisited....318

Access Control....321

User Security....325

MariaDB Components and Plugins That Keep Data Safe....327

Firewalling MariaDB....329

Summary....330

Chapter 17: Securing Your Database Instance....331

Security Guidelines for Specific Use Cases and Defense in Depth....331

Account Categories and Reserved Accounts....334

Password Management and Account Locking....335

SQL Injection, Input Sanitization, and MariaDB....338

Corner Cases of SQL Injection....341

Other Attacks Targeting Your Database....341

Summary....343

Chapter 18: Security and Big Data....344

Security, Big Data, and Code in the Initial Phases of Your SDLC....347

Data, Script Kiddies & Co.....349

What Happens If?....351

Summary....355

Appendix: Things You Wish You Knew, but Dont....357

Schrdingers Tables....357

Having Fun with ibdata1....358

Having Fun with Indexes....359

Query That Breaks MySQL 5.7....361

Reliably Using MyISAM....362

Building APIs and Interacting with Big Data....363

Preparing for the Future....365

Summary....365

Index....367

Your MySQL instances are probably broken. Many developers face slow-running queries, issues related to database architecture, replication, or database security—and that’s only the beginning. This book will deliver answers to your most pressing MySQL database questions related to performance, availability, or security by uncovering what causes databases to break in the first place.

At its core, this book provides you with the knowledge necessary for you to break your database instances so you can better optimize it for performance and secure it from data breaches. In other words, you’ll discover the sorts of actions, minor and major, that degrade databases so you can fix and ultimately preempt them. MySQL sometimes acts according to its own rules, and this book will help you keep it working on your terms. At the same time, you will learn to optimize your backup and recovery procedures, determine when and which data to index to achieve maximum performance, and choose the best MySQL configurations, among other essential skills.

Most MySQL books focus exclusively on optimization, but this book argues that it’s just as important to pay attention to the ways databases break. Indeed, after reading this book, you will be able to safely break your database instances to expose and overcome the nuanced issues that affect performance, availability, and security.

What You Will Learn

• Know the basics of MySQL and the storage engines innoDB and MyISAM
• Spot the ways you are harming your database’s performance, availability and security without even realizing it
• Fix minor bugs and issues that have surprisingly serious impact
• Optimize schema, data types, queries, indexes, and partitions to head off issues
• Understand key MySQL security strategies

Who This Book Is For

Database administrators, web developers, systems administrators, and security professionals with an intermediary knowledge of database management systems and building applications in MySQL