Sheeri K. Cabral won the MySQL Community Member of the Year award in 2007 and 2008. She organizes the Boston, Massachusetts, USA, MySQL User Group -- which she founded in November 2005 -- and produces freely available presentation videos as well as OurSQL: The MySQL Database Podcast for the Community, by the Community. You can check out her prolific blog postings at [...]

Keith Murphy is a MySQL database administrator who has been using MySQL server since 1998. He recently formed Paragon Consulting Services ([...] to provide consulting services for companies seeking MySQL training and help with MySQL solutions ranging from everyday database administration tasks to utilizing "cloud" computing services, performance tuning and scaling. Keith blogs at blog.[...] in addition he is the editor of MySQL Magazine ([...] Readers are invited to contact Keith by email at [...].

Introduction xxvii

Part I First Steps with MySQL

Chapter 1: Introduction to MySQL 3

MySQL Mission - Speed, Reliability, and Ease of Use 3

Company background 4

Community and Enterprise server versions 5

The MySQL Community 6

How to contribute 6

Reasons to contribute 7

Summary 7

Chapter 2: Installing and Upgrading MySQL Server 9

Before Installation 9

Choosing the MySQL version 11

MySQL support 12

Downloads 12

Installation 12

MySQL Server installations on Unix 13

MySQL Server Installation on Windows 20

Installing MySQL from a Noinstall Zip Archive 24

Starting and stopping MySQL from the Windows command line 25

Starting and stopping MySQL as a Windows service 26

Initial Configuration 29

Unix configuration file 31

Windows configuration file 31

MySQL Configuration Wizard on Windows 31

Detailed Configuration 32

The Server Type screen 33

Database Usage screen 33

InnoDB Tablespace screen 34

Concurrent Connections screen 34

Networking Options and Strict Mode Options screen 34

Character Set screen 35

Service Options screen 35

Security Options screen 35

Confirmation screen 36

MySQL Post-Install Configuration on Unix 36

Initializing the system tables 36

Setting initial passwords 37

Root user password assignment 37

Anonymous users 39

Securing Your System 40

Windows PATH Variable Configuration 42

Automated startup 42

Starting and stopping mysqld on System V-based Unix 42

System V run levels 43

Upgrading mysqld 45

The MySQL changelog 45

Upgrading MySQL on Windows 46

Troubleshooting 47

Summary 48

Chapter 3: Accessing MySQL 49

Accessing mysqld with Command-Line Tools 49

Frequently used options 50

Using the command-line mysql client 52

mysqladmin - Client for administering a server 62

GUI Tools 66

SQLyog 66

phpMyAdmin 69

MySQL Query Browser 71

MySQL Administrator 74

MySQL Workbench 80

Summary 83

Part II Developing with MySQL

Chapter 4: How MySQL Extends and Deviates from SQL 87

Learning MySQL Language Structure 88

Comments and portability 88

Case-sensitivity 90

Escape characters 91

Naming limitations and quoting 93

Dot notation 95

Time zones 97

Character sets and collations 98

Understanding MySQL Deviations 105

Privileges and permissions 110

Transaction management 110

Check constraints 111

Upsert statements 112

Using MySQL Extensions 114

Aliases 115

Alter Table extensions 115

Create Extensions 118

DML Extensions 119

Drop Extensions 124

The LIMIT Extension 125

SELECT Extensions 126

Select Into Outfile/Select Into Dumpfile 126

Sql_Small_Result/Sql_Big_Result 127

Union Order By 127

Select For Update 127

Select Lock In Share Mode 128

Distinctrow 128

Sql_Buffer_Result 129

High_Priority/Low_Priority 129

Server maintenance extensions 129

The Set extension and user-defined variables 131

The Show extension 135

Table definition extensions 147

Table maintenance extensions 150

Transactional statement extensions 156

Summary 158

Chapter 5: MySQL Data Types 159

Looking at MySQL Data Types 159

Character String Types 160

Length 162

Character string type attributes 164

National Character String Types 166

Binary Large Object String Types 168

Blob values 169

Binary values 169

Binary length 169

Varbinary length 170

Numeric Types 170

Numeric data sizes and ranges 172

Numeric data type attributes 177

Boolean Types 180

Datetime Types 183

Allowed input values 185

Microsecond input 186

Automatic updates 187

Conversion issues 188

Numeric functions and Datetime types 188

Other conversion issues 190

Datetime data type attributes 191

The effect of time zones 192

Interval Types 193

ENUM and SET Types 195

Enumerations 195

ENUM and SET data type attributes 198

Choosing SQL Modes 201

Invalid data 201

SQL modes 203

Using NULL Values 211

Finding an Optimal Data Type for Existing Data 212

Small data samples and Procedure Analyse() 215

Summary 217

Chapter 6: MySQL Index Types 219

Looking at Keys and Indexes 219

Using Indexes to Speed Up Lookups 221

Creating and dropping indexes 223

Index order 225

Index length 226

Index types 228

Redundant indexes 230

Creating and Dropping Key Constraints 231

Creating and dropping unique key constraints 231

Creating and dropping foreign key constraints 232

Foreign key constraints and data changes 234

Requirements for foreign key constraints 235

Using FULLTEXT Indexes 237

Summary 239

Chapter 7: Stored Routines, Triggers, and Events 241

Comparing Stored Routines, Triggers, and Events 241

Using Triggers 242

Creating a trigger 243

Dropping a trigger 244

Multiple SQL statements in triggers 245

Changing a trigger 246

Triggers on views and temporary tables 247

Trigger runtime behavior 248

Finding all triggers 252

Trigger storage and backup 252

Triggers and replication 254

Trigger limitations 254

Using Stored Routines 255

Performance implications of stored routines 256

Stored procedures vs stored functions 256

Creating a stored routine 256

Invoking a stored procedure 259

Dropping a stored routine 261

Multiple SQL statements in stored routines 261

INOUT arguments to a stored procedure 261

Local variables 262

Stored routine runtime behavior 264

Options when creating routines 265

Creating a basic stored function 268

Full Create Function syntax 269

Invoking a stored function 269

Changing a stored routine 270

Naming: stored routines 271

Stored procedure result sets 273

Stored routine errors and warnings 274

Conditions and handlers 275

Stored routine flow control 282

Recursion 284

Stored routines and replication 285

Stored function limitations 285

Stored routine backup and storage 286

Using Cursors 287

Using Events 289

Turning on the event scheduler 289

Creating an event 291

Dropping an event 292

Multiple SQL statements in events 293

Start and end times for periodic events 293

Event status 294

Finding all events 295

Changing an event 295

After the last execution of an event 296

Event logging 297

Event runtime behavior 298

Event limitations 299

Event backup and storage 300

Summary 300

Chapter 8: MySQL Views 301

Defining Views 302

View definition limitations and unexpected behavior 304

Security and privacy 305

Specify a view's definer 306

Abstraction and simplification 307

Performance 308

Updatable views 313

Changing a View Definition 317

Replication and Views 317

Summary 318

Chapter 9: Transactions in MySQL 319

Understanding ACID Compliance 320

Atomicity 321

Consistency 321

Isolation 321

Durability 321

Using Transactional Statements 322

Begin, Begin Work, and Start Transaction 322

Commit 322

Rollback 322

Savepoints 323

Autocommit 324

Using Isolation Levels 325

Read Uncommited 329

Read Committed 331

Repeatable Read 332

Serializable 334

Multi-version concurrency control 335

Explaining Locking and Deadlocks 336

Table-level locks 338

Page-level locks 341

Row-level locks 341

Recovering MySQL Transactions 343

Summary 344

Part III Core MySQL Administration

Chapter 10: MySQL Server Tuning 349

Choosing Optimal Hardware 349

Tuning the Operating System 352

Operating system architecture 352

File systems and partitions 353

Buffers 356

Kernel parameters 357

Linux 357

Other daemons 360

Tuning MySQL Server 360

Status variables 360

System variables 361

Option file 361

Dynamic variables 371

Summary 373

Chapter 11: Storage Engines 375

Understanding Storage Engines 375

Storage engines as plugins 376

Storage engine comparison 376

Using Different Storage Engines 378

MyISAM storage engine 378

InnoDB storage engine 384

Memory storage engine 394

Maria storage engine 396

Falcon storage engine 401

PBXT storage engine 410

Federated storage engine 415

NDB storage engine 417

Archive storage engine 417

Blackhole storage engine 419

CSV storage engine 420

Working with Storage Engines 421

Create Table 421

Alter Table 421

Drop Table 422

Summary 422

Chapter 12: Caching with MySQL 423

Implementing Cache Tables 424

Working with the Query Cache 427

What gets stored in the query cache? 427

Query cache memory usage and tuning 429

Query cache fragmentation 433

Utilizing memcached 434

Summary 438

Chapter 13: Backups and Recovery...