Description
Book SynopsisWith special focus on the next major release of MySQL, this resource provides a solid framework for anyone new to MySQL or transitioning from another database platform, as well as experience MySQL administrators. The high-profile author duo provides essential coverage of the fundamentals of MySQL database managementincluding MySQL's unique approach to basic database features and functionsas well as coverage of SQL queries, data and index types, stores procedure and functions, triggers and views, and transactions. They also present comprehensive coverage of such topics as MySQL server tuning, managing storage engines, caching, backup and recovery, managing users, index tuning, database and performance monitoring, security, and more.
Table of ContentsIntroduction 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
