9781590595053

Pro MySQL

by
  • ISBN13:

    9781590595053

  • ISBN10:

    159059505X

  • Format: Paperback
  • Copyright: 2005-08-29
  • Publisher: Apress
  • Purchase Benefits
  • Free Shipping On Orders Over $35!
    Your order must be $35 or more to qualify for free economy shipping. Bulk sales, PO's, Marketplace items, eBooks and apparel do not qualify for this offer.
  • Get Rewarded for Ordering Your Textbooks! Enroll Now
List Price: $59.99

Summary

The MySQL database server continues to enjoy an aggressive development schedule, with new features and enhancements released on a regular basis. Most notably, the changes to version 4 and the forthcoming version 5 are among the most significant in the history of the project, and serve to further extend the already considerable capabilities this popular open source database server offers its millions of users worldwide. MySQL 5 is expected to release in late spring/early summer 2005.Pro MySQL provides intermediate and advanced MySQL users with a comprehensive guide to the array of features available to these new versions. Clustering, replication, transactions, and subqueries are just a few of the topics you'll find in this book.

Table of Contents

Forewordp. xix
About the Authorsp. xxi
About the Technical Reviewerp. xxiii
Acknowledgmentsp. xxv
Introductionp. xxvii
Design and Development
Analyzing Business Requirementsp. 3
The Projectp. 4
From Concept to Modelp. 13
Database Selectionp. 32
Your Environmentp. 35
Summaryp. 38
Index Conceptsp. 39
Data Storagep. 40
How Indexes Affect Data Accessp. 44
Clustered vs. Non-Clustered Data and Index Organizationp. 55
Index Layoutsp. 57
Compressionp. 62
General Index Strategiesp. 64
Summaryp. 67
Transaction Processingp. 69
Transaction Processing Basicsp. 70
Ensuring Atomicity, Consistency, and Durabilityp. 76
Implementing Isolation and Concurrencyp. 88
Identifying Your Transaction Control Requirementsp. 102
Summaryp. 103
MySQL System Architecturep. 105
The MySQL Source Code and Documentationp. 106
MySQL Architecture Overviewp. 111
Process, Thread, and Resource Managementp. 114
Storage Engine Abstractionp. 117
Caching and Memory Management Subsystemp. 121
Network Management and Communicationp. 128
Access and Grant Managementp. 131
Log Managementp. 133
Query Parsing, Optimization, and Executionp. 135
The Query Cachep. 139
A Typical Query Executionp. 140
Summaryp. 151
Storage Engines and Data Typesp. 153
Storage Engine Considerationsp. 154
The MyISAM Storage Enginep. 154
The InnoDB Storage Enginep. 164
Other Storage Enginesp. 173
Guidelines for Choosing a Storage Enginep. 178
Data Type Choicesp. 179
Summaryp. 188
Benchmarking and Profilingp. 189
What Can Benchmarking Do for You?p. 190
General Benchmarking Guidelinesp. 193
Benchmarking Toolsp. 198
What Can Profiling Do for You?p. 217
General Profiling Guidelinesp. 218
Profiling Toolsp. 219
Summaryp. 234
Essential SQLp. 235
SQL Stylep. 236
MySQL Joinsp. 238
EXPLAIN and Access Typesp. 262
Join Hintsp. 274
Subqueries and Derived Tablesp. 280
Summaryp. 297
SQL Scenariosp. 299
Handling OR Conditions Prior to MySQL 5.0p. 300
Dealing with Duplicate Entries and Orphaned Recordsp. 303
Dealing with Hierarchical Datap. 311
Retrieving Random Recordsp. 326
Calculating Distances with Geographic Coordinate Datap. 328
Generating Running Sums and Averagesp. 344
Summaryp. 347
Stored Proceduresp. 349
Stored Procedure Considerationsp. 349
Stored Procedures in MySQLp. 353
Building Stored Proceduresp. 354
Using Stored Proceduresp. 366
Managing Stored Proceduresp. 369
Stored Procedure Permissionsp. 372
Summaryp. 373
Functionsp. 375
Database Function Usesp. 376
Functions Compared with Other Database Toolsp. 377
Functions in MySQLp. 380
Creating Functionsp. 382
Using Functionsp. 394
Managing Functionsp. 398
Function Permissionsp. 401
Performance of Functionsp. 402
Summaryp. 403
Cursorsp. 405
Database Cursorsp. 405
Cursors in MySQLp. 407
Creating Cursorsp. 408
Using Cursorsp. 413
Summaryp. 418
Viewsp. 419
Database View Usesp. 419
Views in MySQLp. 421
Creating Viewsp. 422
Creating Updatable Viewsp. 433
Defining Views of Viewsp. 435
Managing Viewsp. 436
View Permissionsp. 440
Performance of Viewsp. 440
Summaryp. 442
Triggersp. 443
Database Triggersp. 443
The Debate Over Using Triggersp. 444
Triggers in MySQLp. 446
Creating MySQL Triggersp. 448
Using Triggersp. 456
Managing Triggersp. 461
Trigger Permissionsp. 463
Trigger Performancep. 463
Summaryp. 465
Administration
MySQL Installation and Configurationp. 469
Using an Existing Installationp. 470
Installing Prebuilt Binariesp. 470
Building from Source or the Development Source Treep. 477
Starting and Stopping MySQLp. 483
Performing Post-Installation Setupp. 484
Configuring MySQLp. 485
Upgrading MySQLp. 493
Uninstalling MySQLp. 494
Running Multiple Database Servers on a Single Machinep. 495
Summaryp. 496
User Administrationp. 497
MySQL Privilegesp. 497
How MySQL Controls Access and Verifies Privilegesp. 510
Managing User Accounts from the Command Linep. 516
Using the MySQL Administrator GUI Toolp. 519
Thinking in Terms of User Rolesp. 529
Practical Guidelines for User Administrationp. 531
Summaryp. 531
Securityp. 533
Understanding Security Requirementsp. 534
Building a Security Planp. 536
Getting Started: A MySQL Security Quick Listp. 540
Setting Up Database Security: An Examplep. 541
Securing Your Whole System: Seven Major Areasp. 545
Summaryp. 553
Backup and Restorationp. 555
Why Do We Create Backups?p. 555
Creating a Backup and Restore Planp. 556
Backing Up a Database: A Quick Examplep. 559
Using MySQL Tools to Make and Restore Backupsp. 560
Using Binary Logs for Up-to-Date Tablesp. 581
Summaryp. 583
Replicationp. 585
What Is Replication?p. 586
Why Replicate Data?p. 589
What Isn't Solved with Replicationp. 591
Planning for Replicationp. 591
How MySQL Implements Replicationp. 592
Initial Replication Setupp. 597
Configuration Optionsp. 601
Monitoring and Managingp. 606
Replication Performancep. 611
Replication Examplesp. 613
Summaryp. 615
Clusterp. 617
What Is Clustering?p. 618
MySQL's Cluster Implementationp. 619
Installing MySQL Clusterp. 627
Initial Configuration and Startupp. 627
Cluster Processesp. 634
Configuration File Optionsp. 636
Managing the Clusterp. 640
Log Filesp. 643
Securityp. 644
Summaryp. 644
Troubleshootingp. 645
Troubleshooting Toolkitp. 646
Commonly Encountered Issuesp. 652
How to Report MySQL Bugsp. 667
Support Optionsp. 668
Summaryp. 668
MySQL Data Dictionaryp. 669
Benefits of a Standardized Interfacep. 670
The INFORMATION_SCHEMA Viewsp. 671
Usage Examplesp. 694
Summaryp. 698
Indexp. 699
Table of Contents provided by Ingram. All Rights Reserved.

Rewards Program

Write a Review