MySQL Enterprise Solutions

With more than three million users, MySQL is the most popular open-source database server in the world, providing an extremely fast, reliable, and inexpensive alternative to commercial database management systems Gives corporate users a complete guide to building enterprise-level database applications with MySQL Covers the pros and cons of adopting MySQL and installing, testing, and configuring the MySQL server Written by one of the lead programmers of the MySQL product and reviewed by Monty Widenius, the creator of MySQL Companion Web site includes all code examples as well as links to useful online resources MySQL Enterprise Solutions 1 MySQL Enterprise Solutions 4 COPYRIGHT 5 CONTENTS 6 ACKNOWLEDGMENTS 12 ABOUT THE AUTHOR 14 INTRODUCTION 16 Prominent Users of MySQL 17 Yahoo! Finance 17 NASA 17 U. S. Census Bureau 17 Texas Instruments 18 SS8 Networks 18 Moble. de 18 Who This Book is For 18 The Book¡ ̄s Structure 19 Part I Bringing MySQL into Your Enterprise 22 Chapter 1 Overview of MySQL 22 How Is MySQL Most Commonly Used in the Enterprise? 23 Database Backend for a Web Site 23 Usage Logger 24 Data Warehousing 24 Integrated Database 24 Embedded Database 25 Strengths and Weakness of MySQL 25 Strengths 26 Weaknesses 30 MySQL from the Application Developer¡ ̄s Perspective 33 Overview of MySQL Integration with Other Industry- Standard Software 34 Getting Help with MySQL 35 Online Documentation 35 Mailing List 36 Local Linux User Groups 37 Commercial Support from MySQL AB 37 Chapter 2 Selecting a Platform for MySQL Server 40 Platform Criteria 40 Size of the User Base 41 Amount of Usage Under High Load on Mission- Critical Servers 41 Maturity of the C/ C++ Compiler 41 Number of MySQL AB Developers Regularly Using the Platform 41 Degree of Standard Compliance in the System Libraries 41 Maturity of the Thread Library Available on the System 42 Platform Comparison 42 Linux 42 Windows 43 Solaris 44 FreeBSD 45 Other Systems 45 Operating System Tuning Tips 46 Hardware Tips 47 Chapter 3 Installing MySQL 48 Method of Installation 48 The Need for Transactional Table Support 49 Version Issue 50 Installation Process 52 Binary Installation 52 Source Installation 56 Basic Post- Installation Checks 58 Post- Installation Setup 59 Proxy Database Access 60 Hosting Provider 61 Single User 61 Direct Multiple- User Database Access 62 Troubleshooting 63 mysqld ended 64 Installation of grant tables failed! 64 ERROR 1045: Access denied 65 ERROR 2002: Can¡ ̄t connect to local MySQL server through socket ¡®/ tmp/ mysql. sock¡ ̄ ( 111) 66 Other Problems 67 Chapter 4 Testing Your MySQL Installation 70 The Standard MySQL Test Suite ( mysql- test- run) 70 The Server Limit Test ( crash- me) 76 The One- Threaded Standard MySQL Benchmark 89 The Basic Multithreaded Benchmark ( mysqlsyseval) 95 Your Own Tests 99 Chapter 5 Access Control and Security 104 MySQL Access Privilege System 104 Granting Privileges 106 Revoking Privileges 107 Removing Users 107 System Security 108 Check Setuid Binaries 108 Run Only Necessary Services 109 Set Up a Firewall 110 Monitor the System Daily 110 Database Application Security 110 Server Configuration Security 111 Data Transfer Security 112 Dealing with Especially Sensitive Data 113 Conclusion 113 Part II Developing MySQL Applications 114 Chapter 6 Choosing the Client Language and Client- Server Network Architecture 114 Choosing a Client Language 114 Performance 114 Development Time 115 Code Integration 115 Portability 116 Developer Skills and Preferences 116 Client Language Performance Comparison 117 Network Architecture 120 Client and Server on the Same Host 120 One Server Host and One Remote Client Host 121 One Server Host and Many Remote Client Hosts 121 Data Distributed Across Several Server Hosts and Queried by One Client Host 122 Data Distributed Across Several Server Hosts and Queried by Several Client Hosts 122 Estimating Load from Clients 122 Client Programming Principles 123 Chapter 7 MySQL Client in a Web Environment 126 Choosing a Web Server 126 Server- Application Integration Methods 127 Web Optimization Techniques 128 Avoiding Long Queries 128 Avoiding Unnecessary Queries 129 Avoiding Unnecessary Dynamic Execution 130 Using Persistent Connections 130 Stress- Testing a Web Application 131 Using ApacheBench 132 Other Approaches 133 Chapter 8 C/ C++ Client Basics 136 Preparing Your System 136 Structures and Functions of the API 137 API Overview 143 A Sample Application 145 Tips and Tricks 159 Chapter 9 PHP Client Basics 160 Preparing Your System 161 API Functions 162 API Overview 167 Sample Code 169 Tips and Tricks 183 Chapter 10 Perl API Basics 186 System Preparation 187 DBI Methods and Attributes 188 API Overview 190 Sample Code 191 Tips and Tricks 199 Chapter 11 Java Client Basics 202 System Configuration 203 JDBC Classes and Methods 203 API Overview 210 Sample Code 213 Chapter 12 Writing the Client for Optimal Performance 224 Query Caching 224 Replication- Aware Code 225 Improving Write- Dominant Applications 226 Reducing Network I/ O 227 Understanding the Optimizer 228 Part III Maintaining and Optimization 244 Chapter 13 Table Design 244 Column Types and Disk Space Requirements 244 Variable- Length versus Fixed- Length Records 247 Normalization 248 The Need for Proper Keys 251 Data Wisdom 254 The Proper Table Type 255 Chapter 14 Configuring the Server for OptimalPerformance 258 Optimizing the Schema 258 Optimizing Server Variables 259 Variable Descriptions 263 Additional Variables for MySQL 4.0 278 Verifying Support for Variables 279 Operating System Adjustments 284 Hardware Upgrades 285 Chapter 15 Analyzing and Improving Server Performance 286 Staying Out of Trouble 286 Dealing with Slow Performance 288 Using the EXPLAIN Command 291 Using the mysqldumpslow Script 296 Monitoring Server Activity Patterns with SHOW STATUS 298 Chapter 16 Replication 314 Replication Overview 314 Uses of Replication 315 Setting Up Replication 317 Configuring the Master 317 Configuring the Slave 320 Replication Maintenance 323 Measuring How Far the Slave Is Behind the Master 324 Replication Errors 325 Stopping Replication 325 Replication Caveats 325 Improperly Replicated Queries 326 Replication of Temporary Table Activity 326 Replication of LOAD DATA INFILE 326 Bidirectional Replication 327 Replication Internals 327 Masters and Slaves 328 Server IDs 328 Binary Logs 328 Conclusion 330 Chapter 17 Backup and Table Maintenance 332 Physical Backup 332 Logical Backup 334 Incremental Backup 337 Backup through Replication 337 Table Maintenance 338 Chapter 18 Exploring MySQL Server Internals 344 Getting Started 344 Tour of the Source 348 Execution Flow 349 General Guidelines for Extending the Source 361 Adding a New Native SQL Function 362 Adding a UDF 371 Adding a New Table Handler 374 Maintaining Your Code Modifications 381 Conclusion 383 Part IV Appendices 384 Appendix A Migration Notes 384 Appendix B Troubleshooting Notes 388 Functionality 388 Stability 389 Performance 390 Appendix C SQL Problem Solving Notes 392 Problem 1 394 Problem 2 395 Problem 3 395 Problem 4 395 Problem 5 396 Problem 6 397 Problem 7 397 Problem 8 398 Problem 9 398 Problem 10 399 Appendix D Online Resources 400 Index 402 With more than three million users, MySQL is the popular open-source database server in the world. This work gives corporate users a complete guide to building enterprise-level database applications with MySQL. It covers the pros and cons of adopting MySQL and installing, testing, and configuring the MySQL server. This book is designed to help the corporate DBA and database developer build and manage enterprise-level MySQL applications. It is a comprehensive guide and covers such topics as: pros and cons of a company adopting MySQL; how to select appropriate hardware; and migrating to MySQL from other commercial databases