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 xxviiPart I First Steps with MySQL Chapter 1: Introduction to MySQL 3MySQL Mission — Speed, Reliability, and Ease of Use 3Company background 4Community and Enterprise server versions 5The MySQL Community 6How to contribute 6Reasons to contribute 7Summary 7Chapter 2: Installing and Upgrading MySQL Server 9Before Installation 9Choosing the MySQL version 11MySQL support 12Downloads 12Installation 12MySQL Server installations on Unix 13MySQL Server Installation on Windows 20Installing MySQL from a Noinstall Zip Archive 24Starting and stopping MySQL from the Windows command line 25Starting and stopping MySQL as a Windows service 26Initial Configuration 29Unix configuration file 31Windows configuration file 31MySQL Configuration Wizard on Windows 31Detailed Configuration 32The Server Type screen 33Database Usage screen 33InnoDB Tablespace screen 34Concurrent Connections screen 34Networking Options and Strict Mode Options screen 34Character Set screen 35Service Options screen 35Security Options screen 35Confirmation screen 36MySQL Post-Install Configuration on Unix 36Initializing the system tables 36Setting initial passwords 37Root user password assignment 37Anonymous users 39Securing Your System 40Windows PATH Variable Configuration 42Automated startup 42Starting and stopping mysqld on System V-based Unix 42System V run levels 43Upgrading mysqld 45The MySQL changelog 45Upgrading MySQL on Windows 46Troubleshooting 47Summary 48Chapter 3: Accessing MySQL 49Accessing mysqld with Command-Line Tools 49Frequently used options 50Using the command-line mysql client 52mysqladmin — Client for administering a server 62GUI Tools 66SQLyog 66phpMyAdmin 69MySQL Query Browser 71MySQL Administrator 74MySQL Workbench 80Summary 83Part II Developing with MySQLChapter 4: How MySQL Extends and Deviates from SQL 87Learning MySQL Language Structure 88Comments and portability 88Case-sensitivity 90Escape characters 91Naming limitations and quoting 93Dot notation 95Time zones 97Character sets and collations 98Understanding MySQL Deviations 105Privileges and permissions 110Transaction management 110Check constraints 111Upsert statements 112Using MySQL Extensions 114Aliases 115Alter Table extensions 115Create Extensions 118DML Extensions 119Drop Extensions 124The LIMIT Extension 125SELECT Extensions 126Select Into Outfile/Select Into Dumpfile 126Sql_Small_Result/Sql_Big_Result 127Union Order By 127Select For Update 127Select Lock In Share Mode 128Distinctrow 128Sql_Buffer_Result 129High_Priority/Low_Priority 129Server maintenance extensions 129The Set extension and user-defined variables 131The Show extension 135Table definition extensions 147Table maintenance extensions 150Transactional statement extensions 156Summary 158Chapter 5: MySQL Data Types 159Looking at MySQL Data Types 159Character String Types 160Length 162Character string type attributes 164National Character String Types 166Binary Large Object String Types 168Blob values 169Binary values 169Binary length 169Varbinary length 170Numeric Types 170Numeric data sizes and ranges 172Numeric data type attributes 177Boolean Types 180Datetime Types 183Allowed input values 185Microsecond input 186Automatic updates 187Conversion issues 188Numeric functions and Datetime types 188Other conversion issues 190Datetime data type attributes 191The effect of time zones 192Interval Types 193ENUM and SET Types 195Enumerations 195ENUM and SET data type attributes 198Choosing SQL Modes 201Invalid data 201SQL modes 203Using NULL Values 211Finding an Optimal Data Type for Existing Data 212Small data samples and Procedure Analyse() 215Summary 217Chapter 6: MySQL Index Types 219Looking at Keys and Indexes 219Using Indexes to Speed Up Lookups 221Creating and dropping indexes 223Index order 225Index length 226Index types 228Redundant indexes 230Creating and Dropping Key Constraints 231Creating and dropping unique key constraints 231Creating and dropping foreign key constraints 232Foreign key constraints and data changes 234Requirements for foreign key constraints 235Using FULLTEXT Indexes 237Summary 239Chapter 7: Stored Routines, Triggers, and Events 241Comparing Stored Routines, Triggers, and Events 241Using Triggers 242Creating a trigger 243Dropping a trigger 244Multiple SQL statements in triggers 245Changing a trigger 246Triggers on views and temporary tables 247Trigger runtime behavior 248Finding all triggers 252Trigger storage and backup 252Triggers and replication 254Trigger limitations 254Using Stored Routines 255Performance implications of stored routines 256Stored procedures vs stored functions 256Creating a stored routine 256Invoking a stored procedure 259Dropping a stored routine 261Multiple SQL statements in stored routines 261INOUT arguments to a stored procedure 261Local variables 262Stored routine runtime behavior 264Options when creating routines 265Creating a basic stored function 268Full Create Function syntax 269Invoking a stored function 269Changing a stored routine 270Naming: stored routines 271Stored procedure result sets 273Stored routine errors and warnings 274Conditions and handlers 275Stored routine flow control 282Recursion 284Stored routines and replication 285Stored function limitations 285Stored routine backup and storage 286Using Cursors 287Using Events 289Turning on the event scheduler 289Creating an event 291Dropping an event 292Multiple SQL statements in events 293Start and end times for periodic events 293Event status 294Finding all events 295Changing an event 295After the last execution of an event 296Event logging 297Event runtime behavior 298Event limitations 299Event backup and storage 300Summary 300Chapter 8: MySQL Views 301Defining Views 302View definition limitations and unexpected behavior 304Security and privacy 305Specify a view’s definer 306Abstraction and simplification 307Performance 308Updatable views 313Changing a View Definition 317Replication and Views 317Summary 318Chapter 9: Transactions in MySQL 319Understanding ACID Compliance 320Atomicity 321Consistency 321Isolation 321Durability 321Using Transactional Statements 322Begin, Begin Work, and Start Transaction 322Commit 322Rollback 322Savepoints 323Autocommit 324Using Isolation Levels 325Read Uncommited 329Read Committed 331Repeatable Read 332Serializable 334Multi-version concurrency control 335Explaining Locking and Deadlocks 336Table-level locks 338Page-level locks 341Row-level locks 341Recovering MySQL Transactions 343Summary 344Part III Core MySQL AdministrationChapter 10: MySQL Server Tuning 349Choosing Optimal Hardware 349Tuning the Operating System 352Operating system architecture 352File systems and partitions 353Buffers 356Kernel parameters 357Linux 357Other daemons 360Tuning MySQL Server 360Status variables 360System variables 361Option file 361Dynamic variables 371Summary 373Chapter 11: Storage Engines 375Understanding Storage Engines 375Storage engines as plugins 376Storage engine comparison 376Using Different Storage Engines 378MyISAM storage engine 378InnoDB storage engine 384Memory storage engine 394Maria storage engine 396Falcon storage engine 401PBXT storage engine 410Federated storage engine 415NDB storage engine 417Archive storage engine 417Blackhole storage engine 419CSV storage engine 420Working with Storage Engines 421Create Table 421Alter Table 421Drop Table 422Summary 422Chapter 12: Caching with MySQL 423Implementing Cache Tables 424Working with the Query Cache 427What gets stored in the query cache? 427Query cache memory usage and tuning 429Query cache fragmentation 433Utilizing memcached 434Summary 438Chapter 13: Backups and Recovery 439Backing Up MySQL 439Uses for backups 441Backup frequency 443What to back up 445Backup locations 445Backup methods 445Online backup 460mysqlhotcopy 462Commercial options 464Copying Databases to Another Machine 467Recovering from Crashes 468Planning for Disasters 471Summary 472Chapter 14: User Management 473Learning about MySQL Users 473Access Control Lists 474Wildcards 475System tables 476Managing User Accounts 478Grant and Revoke commands 481Show Grants and mk-show-grants 485Resetting the Root Password 487Windows server 488Unix-based server 489Debugging User Account Problems 490Bad password 490Access issues 491Client does not support authentication protocol 491Can’t connect to local mysqld through socket ‘/path/to/mysqld.sock’ 492I do not have the right permissions! 493Summary 494Chapter 15: Partitioning 495Learning about Partitioning 495Partitioning Tables 496RANGE partitioning 497LIST partitioning 502HASH partitioning 503KEY partitioning 504Composite partitioning 504Partition management commands 507Restrictions of partitioning 510Merge Tables 510Creating a Merge table 511Changing a Merge table 512Advantages of Merge tables 513Partitioning with MySQL Cluster 513Programmatic Partitioning 514Summary 514Chapter 16: Logging and Replication 517Log Files 517Error log 517Binary logs 518Relay logs 520General and slow query logs 520Rotating logs 522Other methods of rotating 523Replication 524Setting up semisynchronous replication 525Statement-based, row-based, and mixed-based replication 527Replication Configurations 529Simple replication 529Change Master statement 534More complex setups 534Additional replication configuration options 539Correcting Data Drift 540mk-table-checksum overview 540mk-table-sync overview 542Putting this together 542Summary 543Chapter 17: Measuring Performance 545Benchmarking 546mysqlslap 547SysBench 552Benchmarking recommendations 565Profiling 566Show Global Status 566mysqltuner 568mysqlreport 572mk-query-profiler 580mysqldumpslow 583Capacity Planning 585Summary 585Part IV Extending Your SkillsChapter 18: Query Analysis and Index Tuning 589Using Explain 590Explain plan basics 590Data access strategy 596Explain plan indexes 606Rows 607Extra 608Subqueries and Explain 611Explain Extended 612Explain on Non-Select Statements 614Other Query Analysis Tools 614Optimizing Queries 615Factors affecting key usage 615Optimizer hints 616Adding an Index 616Optimizing away Using temporary 620Using an index by eliminating functions 623Non-index schema changes 626Batching expensive operations 628Optimizing frequent operations 629Summary 631Chapter 19: Monitoring Your Systems 633Deciding What to Monitor 634Examining Open Source Monitoring 636Nagios 636Cacti 637Hyperic HQ 638OpenNMS 640Zenoss Core 641Munin 642Monit 643Examining Commercial Monitoring 644MySQL enterprise monitor 644MONyog 645Summary 646Chapter 20: Securing MySQL 649Access Control Lists 649Wildcards and blank values 650Privilege and privilege levels 651Accessing the Operating System 654Database access 654Changing MySQL connectivity defaults 654Operating system login 654Securing Backups and Logs 656Data Security 656Data flow 657Encrypted connectivity 659Data security using MySQL objects 664Creating Security Policies 665Summary 666Chapter 21: The MySQL Data Dictionary 667Object Catalog 668Schemata 668Tables 670Views 674Columns 676Statistics 679Table_Constraints 681Key_Column_Usage 682Referential_Constraints 684Triggers 685Routines 686Parameters 690Events 691Partitions 693System Information 695Character_Sets 695Collations 696Collation_Character_Set_Applicability 696Engines 697Plugins 697Processlist 698Profiling 709Global_Variables 710Session_Variables 710Global_Status 711Session_Status 711Displaying Permissions 711Column_Privileges 712Table_Privileges 713Schema_Privileges 714User_Privileges 715Storage Engine-Specific Metadata 716Custom Metadata 716Defining the plugin 716Compiling the plugin 722Installing the plugin 724Summary 725Chapter 22: Scaling and High Availability Architectures 727Replication 728One read slave 729Promoting a new master 729Many read slaves 734Master/master replication 735Circular replication 736SAN 737DRBD 738MySQL and DRBD setup 738MySQL Proxy 739Scaling read queries 740Automated failover 740Read/write splitting 742Sharding 742Linux-HA Heartbeat 742MySQL Cluster 744Connection Pooling 746memcached 747Summary 748Appendix A: MySQL Proxy 749Appendix B: Functions and Operators 783Appendix C: Resources 813Index 821