MySQL Administrator's Bible

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 www.technocation.org. Keith Murphy is a MySQL database administrator who has been using MySQL server since 1998. He recently formed Paragon Consulting Services (www.paragon-cs.com) 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.paragon-cs.com in addition he is the editor of MySQL Magazine (www.mysqlzine.net). Readers are invited to contact Keith by email at bmurphy@pargon-cs.com.

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 439

Backing Up MySQL 439

Uses for backups 441

Backup frequency 443

What to back up 445

Backup locations 445

Backup methods 445

Online backup 460

mysqlhotcopy 462

Commercial options 464

Copying Databases to Another Machine 467

Recovering from Crashes 468

Planning for Disasters 471

Summary 472

Chapter 14: User Management 473

Learning about MySQL Users 473

Access Control Lists 474

Wildcards 475

System tables 476

Managing User Accounts 478

Grant and Revoke commands 481

Show Grants and mk-show-grants 485

Resetting the Root Password 487

Windows server 488

Unix-based server 489

Debugging User Account Problems 490

Bad password 490

Access issues 491

Client does not support authentication protocol 491

Can’t connect to local mysqld through socket ‘/path/to/mysqld.sock’ 492

I do not have the right permissions! 493

Summary 494

Chapter 15: Partitioning 495

Learning about Partitioning 495

Partitioning Tables 496

RANGE partitioning 497

LIST partitioning 502

HASH partitioning 503

KEY partitioning 504

Composite partitioning 504

Partition management commands 507

Restrictions of partitioning 510

Merge Tables 510

Creating a Merge table 511

Changing a Merge table 512

Advantages of Merge tables 513

Partitioning with MySQL Cluster 513

Programmatic Partitioning 514

Summary 514

Chapter 16: Logging and Replication 517

Log Files 517

Error log 517

Binary logs 518

Relay logs 520

General and slow query logs 520

Rotating logs 522

Other methods of rotating 523

Replication 524

Setting up semisynchronous replication 525

Statement-based, row-based, and mixed-based replication 527

Replication Configurations 529

Simple replication 529

Change Master statement 534

More complex setups 534

Additional replication configuration options 539

Correcting Data Drift 540

mk-table-checksum overview 540

mk-table-sync overview 542

Putting this together 542

Summary 543

Chapter 17: Measuring Performance 545

Benchmarking 546

mysqlslap 547

SysBench 552

Benchmarking recommendations 565

Profiling 566

Show Global Status 566

mysqltuner 568

mysqlreport 572

mk-query-profiler 580

mysqldumpslow 583

Capacity Planning 585

Summary 585

Part IV Extending Your Skills

Chapter 18: Query Analysis and Index Tuning 589

Using Explain 590

Explain plan basics 590

Data access strategy 596

Explain plan indexes 606

Rows 607

Extra 608

Subqueries and Explain 611

Explain Extended 612

Explain on Non-Select Statements 614

Other Query Analysis Tools 614

Optimizing Queries 615

Factors affecting key usage 615

Optimizer hints 616

Adding an Index 616

Optimizing away Using temporary 620

Using an index by eliminating functions 623

Non-index schema changes 626

Batching expensive operations 628

Optimizing frequent operations 629

Summary 631

Chapter 19: Monitoring Your Systems 633

Deciding What to Monitor 634

Examining Open Source Monitoring 636

Nagios 636

Cacti 637

Hyperic HQ 638

OpenNMS 640

Zenoss Core 641

Munin 642

Monit 643

Examining Commercial Monitoring 644

MySQL enterprise monitor 644

MONyog 645

Summary 646

Chapter 20: Securing MySQL 649

Access Control Lists 649

Wildcards and blank values 650

Privilege and privilege levels 651

Accessing the Operating System 654

Database access 654

Changing MySQL connectivity defaults 654

Operating system login 654

Securing Backups and Logs 656

Data Security 656

Data flow 657

Encrypted connectivity 659

Data security using MySQL objects 664

Creating Security Policies 665

Summary 666

Chapter 21: The MySQL Data Dictionary 667

Object Catalog 668

Schemata 668

Tables 670

Views 674

Columns 676

Statistics 679

Table_Constraints 681

Key_Column_Usage 682

Referential_Constraints 684

Triggers 685

Routines 686

Parameters 690

Events 691

Partitions 693

System Information 695

Character_Sets 695

Collations 696

Collation_Character_Set_Applicability 696

Engines 697

Plugins 697

Processlist 698

Profiling 709

Global_Variables 710

Session_Variables 710

Global_Status 711

Session_Status 711

Displaying Permissions 711

Column_Privileges 712

Table_Privileges 713

Schema_Privileges 714

User_Privileges 715

Storage Engine-Specific Metadata 716

Custom Metadata 716

Defining the plugin 716

Compiling the plugin 722

Installing the plugin 724

Summary 725

Chapter 22: Scaling and High Availability Architectures 727

Replication 728

One read slave 729

Promoting a new master 729

Many read slaves 734

Master/master replication 735

Circular replication 736

SAN 737

DRBD 738

MySQL and DRBD setup 738

MySQL Proxy 739

Scaling read queries 740

Automated failover 740

Read/write splitting 742

Sharding 742

Linux-HA Heartbeat 742

MySQL Cluster 744

Connection Pooling 746

memcached 747

Summary 748

Appendix A: MySQL Proxy 749

Appendix B: Functions and Operators 783

Appendix C: Resources 813

Index 821