Sponsored by BreachDirectory – A data breach search engine protecting you from cybercrime 24/7/365

About

Hacking MySQL

Hacking MySQL is split into 3 distinct parts: Breaking, Optimizing, and Securing. Each of those parts has their own merits: the Breaking part walks you through what breaks your MySQL database instance, queries, makes you understand query components and the server behind them.

The Optimization part of the book walks you through methods you can employ to optimize the performance of storage engines, schemas and data types, queries, provides you with actionable advice you can use to optimize MySQL for big data sets, optimize indexes, partitions, backups, and recovery as well as replication procedures.

Finally, the Securing part of the book walks you through the methods you can employ to secure your database from harm by covering security guidelines for specific use cases, user security, account categories, reserved accounts and roles, password management, security when running big data, and more.

Contents

The book consists of a foreword, preface, 18 chapters, and an appendix.

Click on the chapter to the right and you will be able to read a small excerpt where available. Chapters available for reading are emboldened.

Excerpts of available chapters are only available as early uncorrected drafts.

The book is scheduled to publish in early 2025.

At present, the raw and unedited excerpts of available chapters are as follows:

- Chapter 5: Understanding Query Components - Chapter 10: Optimizing MySQL for Big Data - Chapter 11: Indexing MySQL 

Foreword by Louis "Dr. SQL" Davidson
    Preface
        Part 1: The Basics of MySQL
        1 The World of MySQL
		1.1 The History of MySQL
		1.2 The Architecture of MySQL 
		1.3 Basic Use Cases and Initial Considerations
		1.4 Storage Engines
		1.5 Summary
		2 Individual Storage Engines
		2.1 The Modern King of Storage Engines
		2.2 The Primary Contestant of InnoDB
		2.3 InnoDB versus MyISAM
		2.4 Storage Engine Use Cases
		2.5 The NDB High-Availability Storage Engine
		2.6 FEDERATED and EXAMPLE
		2.7 Summary
		Part 2 Breaking MySQL
		3 What Breaks MySQL?
		3.1 MySQL use cases for software appliances 
		3.2 Problematic use cases
		3.2.1 Availability issues
		3.2.2 Performance hiccups
		3.2.3 Security problems
		3.3 Understanding your data
		3.3.1 Choosing the proper schema and data types
		3.3.2 Character sets and collations
		3.4 Your architecture is a mess
		3.5 Communicating with MySQL through software
		3.5.1 Top causes of slow query performance 
		3.6 Case Studies
		3.7 Summary
		4 How You Broke Your Queries
		4.1 The good, the bad & the ugly: understanding queries in MySQL
		4.2 Types of queries in MySQL
			4.2.1 INSERT queries in MySQL
			4.2.2 SELECT queries in MySQL
			4.2.3 UPDATE queries in MySQL
			4.2.4 DELETE queries in MySQL
		4.3 Why are queries slow?
		4.4 Devising the perfect schema design
		4.5 Understanding data types
		4.6 Understanding indexes
		4.7 Understanding partitions
		4.8 Things to avoid when optimizing your queries
		4.9 Summary
		5 Understanding Query Components
		5.1 How do queries work under the hood?
			5.1.1 INSERT queries under the hood
			5.1.2 SELECT queries under the hood
			5.1.3 UPDATE queries under the hood
			5.1.4 DELETE queries under the hood
		5.2 Parsers and optimizers
		5.3 Factors disliked by your queries
		5.4 SHOW STATUS and EXPLAIN
		5.5 Queries and error messages
		5.6 Summary
		6 Understanding Your Server
		6.1 Efficiently writing queries
		6.2 Simulating errors
		6.3 Server components and their interaction with MySQL
		6.4 Configuring MySQL for your server
		6.5 Coding for MySQL performance and security
		6.6 What not to do 
		6.7 Summary
		Part 3 Optimizing MySQL
		7 Optimizing Your Server for MySQL
		7.1 Why optimize your server for MySQL? 
		7.2 Common webserver issues affecting MySQL
		7.3 What limits the performance of MySQL?
		7.4 Choosing hard drives
		7.5 Configuring MySQL options through my.cnf
		7.6 Configuring MySQL I/O for your OS
		7.7 Setting up advanced options for your OS
		7.8 Testing your hardware
		7.9 Taking advantage of ACID properties
		7.10 Summary
		8 Optimizing Storage Engines, Schemas, and Data Types
		8.1 Why optimize storage engines?
		8.2 Optimizing InnoDB
			8.2.1 InnoDB use cases
			8.2.2 Optimizing InnoDB for high performance
			8.2.3 Optimizing InnoDB for big data
			8.2.4 Advanced InnoDB options
		8.3 Optimizing MyISAM
		8.4 Optimizing other storage engines
		8.5 Real world examples
		8.6 Why optimize your schema?
		8.7 Why optimize data types?
		8.8 Data types in MySQL
			8.8.1 String-based data types
			8.8.2 Numeric data types
			8.8.3 Date and time data types
			8.8.4 Spatial data types
			8.8.5 The JSON data type
			8.8.6 Storage requirements for data types
			8.8.7 Data types and indexes
			8.8.8 Choosing the right data type
		8.9 Benefits of good schema design
		8.10 Optimizing schema and data types for big data
		8.11 Summary
		9 Optimizing Your Schema and Data Types
		9.1 Why optimize your schema?
		9.2 Why optimize data types?
		9.3 Data types in MySQL
			9.3.1 String-based data types
			9.3.2 Numeric data types
			9.3.3 Date and time data types
			9.3.4 Spatial data types
			9.3.5 The JSON data type
			9.3.6 Storage requirements for data types
			9.3.7 Data types and indexes
			9.3.8 Choosing the right data type
		9.4 Benefits of good schema design
		9.5 Optimizing schema and data types for big data
		9.6 Summary
		10 Optimizing MySQL for Big Data
		10 Optimizing MySQL for Big Data 
		10.1 Why optimize queries for big data?
			10.1.1 When to optimize queries for big data?
		10.2 Optimizing MySQL configuration for big data
		10.3 Operations with big data sets
			10.3.1 Inserting big data into MySQL
			10.3.2 Reading big data with MySQL
			10.3.3 Updating big data in MySQL
			10.3.4 Deleting big data sets from MySQL
		10.4 Storage engines and big data
		10.5 ACID and big data
		10.6 Big data pitfalls and known issues
		10.7 Summary
		11 Indexing MySQL
		11.1 Why index?
		11.2 What and when to index?
		11.3 Myths and misconceptions about indexes
		11.4 Your hardware and indexes
		11.5 MySQL and indexes
		11.6 Types of indexes
			11.6.1 B-Tree indexes
			11.6.2 Spatial (R-Tree) indexes
			11.6.3 Hash indexes
			11.6.4 Covering indexes
			11.6.5 Clustered indexes
			11.6.6 Multicolumn or composite indexes
			11.6.7 Prefix indexes
		11.7 Devising the perfect index design
		11.8 Indexing for big data
		11.9 Indexing for performance
		11.10 Summary
		12 Optimizing Partitions
		12.1 Why Partition Data?
		12.2 When to Partition Data?
		12.3 Internals of Database Partitioning
		12.4 Types of Partitioning in MySQL
		12.5 Partitioning Tips: Subpartitioning, Limitations, NULL Values & More
		12.6 Summary
		13 Optimizing Backups and Recovery
		13.1 Why, When, and How to Backup MariaDB?
		13.2 Backup Types and Tools 
		13.3 Backup Compression and Security
		13.4 Backing Up Big Data Sets
		13.5 Recovering MariaDB
			13.5.1 Recovering Big Data
		13.6 Backup and Recovery Pitfalls
			13.6.1 Pitfalls for Big Data
		13.7 Summary
		14 Optimizing Replication
		14.1 Understanding Replication
		14.2 Configuring and Implementing Replication
		14.3 Types of Replication
		14.4 Replication Notes and Tips
		14.5 Securing Replication
		14.6 Summary
		15 Optimizing for Security
		15.1 Understanding Security in MariaDB
		15.2 Securing MariaDB Upon Installation
		15.3 General Security Measures
		15.4 Summary
		Part 4 Securing MySQL
		16 The World of Security in MySQL
		16.1 General Security Guidelines and Measures Revisited
		16.2 Access Control
		16.3 User Security
		16.4 MariaDB Components and Plugins That Keep Data Safe
		16.5 Firewalling MariaDB
		16.6 Summary
		17 Securing Your Database Instance
		17.1 Security Guidelines for Specific Use Cases and Defense in Depth
		17.2 Account Categories and Reserved Accounts
		17.3 Password Management and Account Locking
		17.4 SQL Injection, Input Sanitization, and MariaDB
			17.4.1 Corner Cases of SQL Injection
		17.5 Other Attacks Targeting Your Database
		17.6 Summary
		18 Security and Big Data
		18.1 How to Secure Big Data Sets?
		18.2 Security During Specific Big Data Operations
		18.2.1 Security During INSERT Operations
		18.2.2 Security During SELECT Operations
		18.2.3 Security During UPDATE Operations
		18.2.4 Security During DELETE Operations
		18.3 Security and Code
		18.4 Protecting Big Data Against Misuse and Fraud
		18.5 Limitations when Securing Big Data
		18.6 Summary
		Appendix - Things You Wish You Knew, But Don't
		A.1 Schrödinger's Tables
		A.2 Having Fun with ibdata1
		A.3 Having Fun with Indexes
		A.4 Query That Breaks MySQL 5.7
		A.5 Reliably Using MyISAM
		A.6 Building APIs and Interacting with Big Data
		A.7 Preparing For the Future
		A.8 Summary

Hacking MySQL

Why Read It?

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. Enter "Hacking MySQL," which 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, the book provides you with the knowledge necessary for you to break your database instance so you can better optimize it for performance and secure it from data breaches. In other words, you will 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 settings to configure, among other essential skills.

Most MySQL books focus exclusively on optimization of performance or security-related dilemmas, but "Hacking MySQL" 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 affecting performance, availability, and security.

You will learn:

  • * The basics of MySQL and the storage engines including InnoDB, MyISAM, among others
  • * How to spot the ways you are harming the performance, availability, or security of your database often without even realizing it
  • * How to fix minor bugs and issues that have surprisingly serious impact
  • * How to optimize schemas, data types, queries, indexes, partitions and other things to head-off issues
  • * Key MySQL security strategies
  • This book is for database administrators, developers, system administrators and security professionals with an intermediary knowledge of database management systems.