MySQL Enterprise Solutions - Alexander “Sasha” Pachev

MySQL Enterprise Solutions - Alexander “Sasha” Pachev - 2003.
    Chapters 1 – 3 in particular are written for technically oriented IT managers and other decision-makers with some background in information technology. System administrators and database developers will also find this information useful. In the remainder of the book, I assume that you have worked with a database before. Also, I discuss running MySQL on a variety of operating systems, including Windows, but you will get more out of this book if you are at home with a Unix shell, or at least willing to learn the basics.


CONTENTS
Acknowledgments ix
About the Author xi
Introduction xiii
Part I Bringing MySQL into Your Enterprise
Chapter 1 Overview of MySQL 1
How Is MySQL Most Commonly Used in the Enterprise? 2
Database Backend for a Web Site 2
Usage Logger 3
Data Warehousing 3
Integrated Database 3
Embedded Database 4
Strengths and Weakness of MySQL 4
Strengths 5
Weaknesses 9
MySQL from the Application Developer’s Perspective 12
Overview of MySQL Integration with Other Industry-Standard Software 13
Getting Help with MySQL 14
Online Documentation 14
Mailing List 15
Local Linux User Groups 16
Commercial Support from MySQL AB 16
Chapter 2 Selecting a Platform for MySQL Server 19
Platform Criteria 19
Size of the User Base 20
Amount of Usage under High Load on Mission-Critical Servers 20
Maturity of the C/C++ Compiler 20
Number of MySQL AB Developers Regularly Using the Platform 20
Degree of Standard Compliance in the System Libraries 20
Maturity of the Thread Library Available on the System 21
Platform Comparison 21
Linux 21
Windows 22
Solaris 23
FreeBSD 24
Other Systems 24
Operating System Tuning Tips 25
Hardware Tips 26
Chapter 3 Installing MySQL 27
Method of Installation 27
The Need for Transactional Table Support 28
Version Issue 29
Installation Process 31
Binary Installation 31
Source Installation 35
Basic Post-Installation Checks 37
Post-Installation Setup 38
Proxy Database Access 39
Hosting Provider 40
Single User 40
Direct Multiple-User Database Access 41
Troubleshooting 42
mysqld ended 43
Installation of grant tables failed! 43
ERROR 1045: Access denied 44
ERROR 2002: Can’t connect to local MySQL server through socket
'/tmp/mysql.sock' (111) 45
Other Problems 46
Chapter 4 Testing Your MySQL Installation 49
The Standard MySQL Test Suite (mysql-test-run) 49
The Server Limit Test (crash-me) 55
The One-Threaded Standard MySQL Benchmark 68
The Basic Multithreaded Benchmark (mysqlsyseval) 74
Your Own Tests 78
Chapter 5 Access Control and Security 83
MySQL Access Privilege System 83
Granting Privileges 85
Revoking Privileges 86
Removing Users 86
System Security 87
Check Setuid Binaries 87
Run Only Necessary Services 88
Set Up a Firewall 89
Monitor the System Daily 89
Database Application Security 89
Server Configuration Security 90
Data Transfer Security 91
Dealing with Especially Sensitive Data 92
Conclusion 92
Part II Developing MySQL Applications
Chapter 6 Choosing the Client Language and Client-Server
Network Architecture 93
Choosing a Client Language 93
Performance 93
Development Time 94
Code Integration 94
Portability 95
Developer Skills and Preferences 95
Client Language Performance Comparison 96
Network Architecture 99
Client and Server on the Same Host 99
One Server Host and One Remote Client Host 100
One Server Host and Many Remote Client Hosts 100
Data Distributed Across Several Server Hosts and Queried by One
Client Host 101
Data Distributed across Several Server Hosts and Queried by Several
Client Hosts 101
Estimating Load from Clients 101
Client Programming Principles 102
Chapter 7 MySQL Client in a Web Environment 105
Choosing a Web Server 105
Server-Application Integration Methods 106
Web Optimization Techniques 107
Avoiding Long Queries 107
Avoiding Unnecessary Queries 108
Avoiding Unnecessary Dynamic Execution 109
Using Persistent Connections 109
Stress-Testing a Web Application 110
Using ApacheBench 111
Other Approaches 112
Chapter 8 C/C++ Client Basics 115
Preparing Your System 115
Structures and Functions of the API 116
API Overview 122
A Sample Application 124
Tips and Tricks 138
Chapter 9 PHP Client Basics 139
Preparing Your System 140
API Functions 141
API Overview 146
Sample Code 148
Tips and Tricks 162
Contents v
vi Cont ent s
Chapter 10 Perl API Basics 165
System Preparation 166
DBI Methods and Attributes 167
API Overview 169
Sample Code 170
Tips and Tricks 178
Chapter 11 Java Client Basics 181
System Configuration 182
JDBC Classes and Methods 182
API Overview 189
Sample Code 192
Chapter 12 Writing the Client for Optimal Performance 203
Query Caching 203
Replication-Aware Code 204
Improving Write-Dominant Applications 205
Reducing Network I/O 206
Understanding the Optimizer 207
Part III Maintaining and Optimization
Chapter 13 Table Design 223
Column Types and Disk Space Requirements 223
Variable-Length versus Fixed-Length Records 226
Normalization 227
The Need for Proper Keys 230
Data Wisdom 233
The Proper Table Type 234
Chapter 14 Configuring the Server for Optimal Performance 237
Optimizing the Schema 237
Optimizing Server Variables 238
Variable Descriptions 242
Additional Variables for MySQL 4.0 257
Verifying Support for Variables 258
Operating System Adjustments 263
Hardware Upgrades 264
Chapter 15 Analyzing and Improving Server Performance 265
Staying Out of Trouble 265
Dealing with Slow Performance 267
Using the EXPLAIN Command 270
Using the mysqldumpslow Script 275
Monitoring Server Activity Patterns with SHOW STATUS 277
Chapter 16 Replication 293
Replication Overview 293
Uses of Replication 294
Setting Up Replication 296
Configuring the Master 296
Configuring the Slave 299
Replication Maintenance 302
Measuring How Far the Slave Is Behind the Master 303
Replication Errors 304
Stopping Replication 304
Replication Caveats 304
Improperly Replicated Queries 305
Replication of Temporary Table Activity 305
Replication of LOAD DATA INFILE 305
Bidirectional Replication 306
Replication Internals 306
Masters and Slaves 307
Server IDs 307
Binary Logs 307
Conclusion 309
Chapter 17 Backup and Table Maintenance 311
Physical Backup 311
Logical Backup 313
Incremental Backup 316
Backup through Replication 316
Table Maintenance 317
Chapter 18 Exploring MySQL Server Internals 323
Getting Started 323
Tour of the Source 327
Execution Flow 328
General Guidelines for Extending the Source 340
Adding a New Native SQL Function 341
Adding a UDF 350
Adding a New Table Handler 353
Maintaining Your Code Modifications 360
Conclusion 362
Part IV Appendices
Appendix A Migration Notes 363
Appendix B Troubleshooting Notes 367
Functionality 367
Stability 368
Performance 369
Contents vii
Appendix C SQL Problem Solving Notes 371
Problem 1 373
Problem 2 374
Problem 3 374
Problem 4 374
Problem 5 375
Problem 6 376
Problem 7 376
Problem 8 377
Problem 9 377
Problem 10 378
Appendix D Online Resources 379
Index 381