MySQL

Paul DuBois is a writer, database administrator, and leader in the open source and MySQL communities. He has contributed to the online documentation for MySQL and is the author of MySQL and Perl for the Web (New Riders), MySQL Cookbook, Using csh and tcsh, and Software Portability with imake (O’Reilly). He is currently a technical writer with the MySQL documentation team at Oracle Corporation.

Introduction





Part I: General MySQL Use




Chapter 1 Getting Started with MySQL


1.1 How MySQL Can Help You


1.2 A Sample Database


1.2.1 The U.S. Historical League Project


1.2.2 The Grade-Keeping Project


1.2.3 How the Sample Database Applies to You


1.3 Basic Database Terminology


1.3.1 Structural Terminology


1.3.2 Query Language Terminology


1.3.3 MySQL Architectural Terminology


1.4 A MySQL Tutorial


1.4.1 Obtaining the Sample Database Distribution


1.4.2 Preliminary Requirements


1.4.3 Establishing and Terminating Connections to the MySQL Server


1.4.4 Executing SQL Statements


1.4.5 Creating a Database


1.4.6 Creating Tables


1.4.7 Adding New Rows


1.4.8 Resetting the sampdb Database to a Known State


1.4.9 Retrieving Information


1.4.10 Deleting or Updating Existing Rows


1.5 Tips for Interacting with mysql


1.5.1 Simplifying the Connection Process


1.5.2 Issuing Statements with Less Typing


1.6 Where to Now?






Chapter 2 Using SQL to Manage Data

2.1 The Server SQL Mode


2.2 MySQL Identifier Syntax and Naming Rules


2.3 Case Sensitivity in SQL Statements


2.4 Character Set Support


2.4.1 Specifying Character Sets


2.4.2 Determining Character Set Availability and Current Settings


2.4.3 Unicode Support


2.5 Selecting, Creating, Dropping, and Altering Databases


2.5.1 Selecting Databases


2.5.2 Creating Databases


2.5.3 Dropping Databases


2.5.4 Altering Databases


2.6 Creating, Dropping, Indexing, and Altering Tables


2.6.1 Storage Engine Characteristics


2.6.2 Creating Tables


2.6.3 Dropping Tables


2.6.4 Indexing Tables


2.6.5 Altering Table Structure


2.7 Obtaining Database Metadata


2.7.1 Obtaining Metadata with SHOW


2.7.2 Obtaining Metadata with INFORMATION_SCHEMA


2.7.3 Obtaining Metadata from the Command Line


2.8 Performing Multiple-Table Retrievals with Joins


2.8.1 Inner Joins


2.8.2 Qualifying References to Columns from Joined Tables


2.8.3 Left and Right (Outer) Joins


2.9 Performing Multiple-Table Retrievals with Subqueries


2.9.1 Subqueries with Relative Comparison Operators


2.9.2 IN and NOT IN Subqueries


2.9.3 ALL, ANY, and SOME Subqueries


2.9.4 EXISTS and NOT EXISTS Subqueries


2.9.5 Correlated Subqueries


2.9.6 Subqueries in the FROM Clause


2.9.7 Rewriting Subqueries as Joins


2.10 Performing Multiple-Table Retrievals with UNION


2.11 Multiple-Table Deletes and Updates


2.12 Performing Transactions


2.12.1 Using Transactions to Ensure Safe Statement Execution


2.12.2 Using Transaction Savepoints


2.12.3 Transaction Isolation


2.13 Foreign Keys and Referential Integrity


2.14 Using FULLTEXT Searches


2.14.1 Natural Language FULLTEXT Searches


2.14.2 Boolean Mode FULLTEXT Searches


2.14.3 Query Expansion FULLTEXT Searches


2.14.4 Configuring the FULLTEXT Search Engine




Chapter 3 Data Types


3.1 Data Value Categories


3.1.1 Numeric Values


3.1.2 String Values


3.1.3 Temporal (Date and Time) Values


3.1.4 Spatial Values


3.1.5 Boolean Values


3.1.6 The NULL Value


3.2 MySQL Data Types


3.2.1 Data Type Overview


3.2.2 Specifying Column Types in Table Definitions


3.2.3 Specifying Column Default Values


3.2.4 Numeric Data Types


3.2.5 String Data Types


3.2.6 Temporal (Date and Time) Data Types


3.3 How MySQL Handles Invalid Data Values


3.4 Working with Sequences


3.4.1 General AUTO_INCREMENT Properties


3.4.2 Storage Engine-Specific AUTO_INCREMENT Properties


3.4.3 Issues to Consider with AUTO_INCREMENT Columns


3.4.4 Tips for Working with AUTO_INCREMENT Columns


3.4.5 Generating Sequences Without AUTO_INCREMENT


3.5 Expression Evaluation and Type Conversion


3.5.1 Writing Expressions


3.5.2 Type Conversion


3.6 Choosing Data Types


3.6.1 What Kind of Values Will the Column Hold?


3.6.2 Do Your Values Lie Within Some Particular Range?





Chapter 4 Views and Stored Programs


4.1 Using Views


4.2 Using Stored Programs


4.2.1 Compound Statements and Statement Delimiters


4.2.2 Stored Functions and Procedures


4.2.3 Triggers


4.2.4 Events


4.3 Security for Views and Stored Programs




Chapter 5 Query Optimization


5.1 Using Indexing


5.1.1 Benefits of Indexing


5.1.2 Costs of Indexing


5.1.3 Choosing Indexes


5.2 The MySQL Query Optimizer


5.2.1 How the Optimizer Works


5.2.2 Using EXPLAIN to Check Optimizer Operation


5.3 Choosing Data Types for Efficient Queries


5.4 Choosing Table Storage Formats for Efficient Queries


5.5 Loading Data Efficiently


5.6 Scheduling, Locking, and Concurrency





Part II: Using MySQL Programming Interfaces




Chapter 6 Introduction to MySQL Programming


6.1 Why Write Your Own MySQL Programs?


6.2 APIs Available for MySQL


6.2.1 The C API


6.2.2 The Perl DBI API


6.2.3 The PHP API


6.3 Choosing an API


6.3.1 Execution Environment


6.3.2 Performance


6.3.3 Development Time


6.3.4 Portability





Chapter 7 Writing MySQL Programs Using C


7.1 Compiling and Linking Client Programs


7.2 Connecting to the Server


7.3 Handling Errors and Processing Command Options


7.3.1 Checking for Errors


7.3.2 Getting Connection Parameters at Runtime


7.3.3 Incorporating Option Processing into a Client Program


7.4 Processing SQL Statements


7.4.1 Handling Statements That Modify Rows


7.4.2 Handling Statements That Return a Result Set


7.4.3 A General-Purpose Statement Handler


7.4.4 Alternative Approaches to Statement Processing


7.4.5 mysql_store_result() Versus mysql_use_result()


7.4.6 Using Result Set Metadata


7.4.7 Encoding Special Characters and Binary Data


7.5 An Interactive Statement-Execution Program


7.6 Writing Clients That Include SSL Support


7.7 Using Multiple-Statement Execution


7.8 Using Server-Side Prepared Statements


7.9 Using Prepared CALL Support





Chapter 8 Writing MySQL Programs Using Perl DBI


8.1 Perl Script Characteristics


8.2 Perl DBI Overview


8.2.1 DBI Data Types


8.2.2 A Simple DBI Script


8.2.3 Handling Errors


8.2.4 Handling Statements That Modify Rows


8.2.5 Handling Statements That Return a Result Set


8.2.6 Quoting Special Characters in Statement Strings


8.2.7 Placeholders and Prepared Statements


8.2.8 Binding Query Results to Script Variables


8.2.9 Specifying Connection Parameters


8.2.10 Debugging


8.2.11 Using Result Set Metadata


8.2.12 Performing Transactions


8.3 Putting DBI to Work


8.3.1 Generating the Historical League Directory


8.3.2 Sending Membership Renewal Notices


8.3.3 Historical League Member Entry Editing


8.3.4 Finding Historical League Members with Common Interests


8.3.5 Putting the Historical League Directory Online


8.4 Using DBI in Web Applications


8.4.1 Setting Up Apache for CGI Scripts


8.4.2 A Brief CGI.pm Primer


8.4.3 Connecting to the MySQL Server from Web Scripts


8.4.4 A Web-Based Database Browser


8.4.5 A Grade-Keeping Project Score Browser


8.4.6 Historical League Common-Interest Searching





Chapter 9 Writing MySQL Programs Using PHP


9.1 PHP Overview


9.1.1 A Simple PHP Script


9.1.2 Using PHP Library Files for Code Encapsulation


9.1.3 A Simple Data-Retrieval Page


9.1.4 Processing Statement Results


9.1.5 Testing for NULL Values in Query Results


9.1.6 Using Prepared Statements


9.1.7 Using Placeholders to Handle Data Quoting Issues


9.1.8 Handling Errors


9.2 Putting PHP to Work


9.2.1 An Online Score-Entry Application


9.2.2 Creating an Interactive Online Quiz


9.2.3 Historical League Online Member Entry Editing





Part III: MySQL Administration




Chapter 10 Introduction to MySQL Administration


10.1 MySQL Components


10.2 General MySQL Administration


10.3 Access Control and Security


10.4 Database Maintenance, Backups, and Replication





Chapter 11 The MySQL Data Directory


11.1 The Data Directory Location


11.2 Structure of the Data Directory


11.2.1 How the MySQL Server Provides Access to Data


11.2.2 Representation of Databases in the Filesystem


11.2.3 Representation of Tables in the Filesystem


11.2.4 Representation of Views and Triggers in the Filesystem


11.2.5 How SQL Statements Map onto Table File Operations


11.2.6 Operating System Constraints on Database Object Names


11.2.7 Factors That Affect Maximum Table Size


11.2.8 Implications of Data Directory Structure for System Performance


11.2.9 MySQL Status and Log Files


11.3 Relocating Data Directory Contents


11.3.1 Relocation Methods


11.3.2 Relocation Precautions


11.3.3 Assessing the Effect of Relocation


11.3.4 Relocating the Entire Data Directory


11.3.5 Relocating Individual Databases


11.3.6 Relocating Individual Tables


11.3.7 Relocating the InnoDB System Tablespace


11.3.8 Relocating Status and Log Files




Chapter 12 General MySQL Administration


12.1 Securing a New MySQL Installation


12.1.1 Establishing Passwords for the Initial MySQL Accounts


12.1.2 Setting Up Passwords for Additional Servers


12.2 Arranging for MySQL Server Startup and Shutdown


12.2.1 Running the MySQL Server On Unix


12.2.2 Running the MySQL Server On Windows


12.2.3 Specifying Server Startup Options


12.2.4 Controlling How the Server Listens for Connections


12.2.5 Stopping the Server


12.2.6 Regaining Control of the Server When You Cannot Connect to It


12.3 Using System and Status Variables


12.3.1 Checking and Setting System Variable Values


12.3.2 Checking Status Variable Values


12.4 The Plugin Interface


12.5 Storage Engine Configuration


12.5.1 Selecting Storage Engines


12.5.2 Selecting a Default Storage Engine


12.5.3 Configuring the InnoDB Storage Engine


12.6 Globalization Issues


12.6.1 Configuring Time Zone Support


12.6.2 Selecting the Default Character Set and Collation


12.6.3 Selecting the Language for Error Messages


12.6.4 Selecting the Locale


12.7 Server Tuning


12.7.1 General-Purpose System Variables for Server Tuning


12.7.2 Storage Engine Tuning


12.7.3 Using the Query Cache


12.7.4 Hardware Optimizations


12.8 Server Logs


12.8.1 The Error Log


12.8.2 The General Query Log


12.8.3 The Slow Query Log


12.8.4 The Binary Log


12.8.5 The Relay Log


12.8.6 Using Log Tables


12.8.7 Log Management


12.9 Running Multiple Servers


12.9.1 General Multiple Server Issues


12.9.2 Configuring and Compiling Different Servers


12.9.3 Strategies for Specifying Startup Options


12.9.4 Using mysqld_multi for Server Management


12.9.5 Running Multiple Servers on Windows


12.9.6 Running Clients of Multiple Servers


12.10 Updating MySQL





Chapter 13 Security and Access Control


13.1 Securing Filesystem Access to MySQL


13.1.1 How to Steal Data


13.1.2 Securing Your MySQL Installation


13.2 Managing MySQL User Accounts


13.2.1 High-Level MySQL Account Management


13.2.2 Granting Privileges


13.2.3 Displaying Account Privileges


13.2.4 Revoking Privileges


13.2.5 Changing Passwords or Resetting Lost Passwords


13.2.6 Avoiding Access-Control Risks


13.2.7 Pluggable Authentication and Proxy Users


13.3 Grant Table Structure and Contents


13.3.1 Grant Table Scope-of-Access Columns


13.3.2 Grant Table Privilege Columns


13.3.3 Grant Table Authentication Columns


13.3.4 Grant Table SSL-Related Columns


13.3.5 Grant Table Resource Management Columns


13.4 How the Server Controls Client Access


13.4.1 Scope Column Contents


13.4.2 Statement Access Verification


13.4.3 Scope Column Matching Order


13.4.4 A Privilege Puzzle


13.5 Setting Up Secure Connections Using SSL





Chapter 14 Database Maintenance, Backups, and Replication


14.1 Principles of Preventive Maintenance


14.2 Performing Database Maintenance with the Server Running


14.2.1 Locking Individual Tables for Read-Only or Read/Write Access


14.2.2 Locking All Databases for Read-Only Access


14.3 General Preventive Maintenance


14.3.1 Using the Server’s Auto-Recovery Capabilities


14.3.2 Scheduling Preventive Maintenance


14.4 Making Database Backups


14.4.1 Storage Engine Portability Characteristics


14.4.2 Making Text Backups with mysqldump


14.4.3 Making Binary Database Backups


14.4.4 Backing Up InnoDB Tables


14.5 Copying Databases to Another Server


14.5.1 Copying Databases Using a Backup File


14.5.2 Copying Databases from One Server to Another


14.6 Checking and Repairing Database Tables


14.6.1 Checking Tables with CHECK TABLE


14.6.2 Repairing Tables with REPAIR TABLE


14.6.3 Using mysqlcheck to Check and Repair Tables


14.7 Using Backups for Data Recovery


14.7.1 Recovering Entire Databases


14.7.2 Recovering Individual Tables


14.7.3 Re-Executing Statements in Binary Log Files


14.7.4 Coping with InnoDB Auto-Recovery Problems


14.8 Setting Up Replication Servers


14.8.1 How Replication Works


14.8.2 Establishing a Master-Slave Replication Relationship


14.8.3 Binary Logging Formats


14.8.4 Using a Replication Slave for Making Backups





Part IV: Appendixes




Note: Appendixes G, H, and I are located online and are accessible either by registering this book at informit.com/register or visiting www.kitebird.com/mysql-book




Appendix A Software Required to Use This Book


A.1 Obtaining the sampdb Sample Database Distribution


A.2 Obtaining MySQL and Related Software


A.3 MySQL Installation Notes


A.3.1 Creating a Login Account for the MySQL User


A.3.2 Installing MySQL


A.3.3 Setting Your PATH Environment Variable


A.3.4 Initializing the Data Directory and Grant Tables


A.3.5 Starting the Server


A.3.6 Initializing Other System Tables


A.4 Perl DBI Installation Notes


A.5 PHP and PDO Installation Notes




Appendix B Data Type Reference


B.1 Numeric Types


B.1.1 Integer Types


B.1.2 Fixed-Point Types


B.1.3 Floating-Point Types


B.1.4 BIT Type


B.2 String Types


B.2.1 Binary String Types


B.2.2 Nonbinary String Types


B.2.3 ENUM and SET Types


B.3 Temporal (Date and Time) Types





Appendix C Operator and Function Reference


C.1 Operators


C.1.1 Operator Precedence


C.1.2 Grouping Operators


C.1.3 Arithmetic Operators


C.1.4 Comparison Operators


C.1.5 Bit Operators


C.1.6 Logical Operators


C.1.7 Cast Operators


C.1.8 Pattern-Matching Operators


C.2 Functions


C.2.1 Comparison Functions


C.2.2 Cast Functions


C.2.3 Numeric Functions


C.2.4 String Functions


C.2.5 Date and Time Functions


C.2.6 Summary Functions


C.2.7 Security and Compression Functions


C.2.8 Advisory Locking Functions


C.2.9 IP Address Functions


C.2.10 XML Functions


C.2.11 Spatial Functions


C.2.12 Miscellaneous Functions





Appendix D System, Status, and User Variable Reference


D.1 System Variables


D.1.1 InnoDB System Variables


D.2 Status Variables


D.2.1 InnoDB Status Variables


D.2.2 Query Cache Status Variables


D.2.3 SSL Status Variables


D.3 User-Defined Variables




Appendix E SQL Syntax Reference


E.1 SQL Statement Syntax (Noncompound Statements)


E.2 SQL Statement Syntax (Compound Statements)


E.2.1 Control Structure Statements


E.2.2 Declaration Statements


E.2.3 Cursor Statements


E.2.4 Condition-Handling Statements


E.3 Comment Syntax





Appendix F MySQL Program Reference


F.1 Displaying a Program’s Help Message


F.2 Specifying Program Options


F.2.1 Standard MySQL Program Options


F.2.2 Option Files


F.2.3 Environment Variables


F.3 myisamchk


F.3.1 Standard Options Supported by myisamchk


F.3.2 Options Specific to myisamchk


F.3.3 Variables for myisamchk


F.4 mysql


F.4.1 Standard Options Supported by mysql


F.4.2 Options Specific to mysql


F.4.3 Variables for mysql


F.4.4 mysql Commands


F.4.5 mysql Prompt Definition Sequences


F.5 mysql.server


F.5.1 Options Supported by mysql.server


F.6 mysql_config


F.6.1 Options Specific to mysql_config


F.7 mysql_install_db


F.7.1 Standard Options Supported by mysql_install_db


F.7.2 Options Specific to mysql_install_db


F.8 mysql_upgrade


F.8.1 Standard Options Supported by mysql_upgrade


F.8.2 Options Specific to mysql_upgrade


F.9 mysqladmin


F.9.1 Standard Options Supported by mysqladmin


F.9.2 Options Specific to mysqladmin


F.9.3 Variables for mysqladmin


F.9.4 mysqladmin Commands


F.10 mysqlbinlog


F.10.1 Standard Options Supported by mysqlbinlog


F.10.2 Options Specific to mysqlbinlog


F.10.3 Variables for mysqlbinlog


F.11 mysqlcheck


F.11.1 Standard Options Supported by mysqlcheck


F.11.2 Options Specific to mysqlcheck


F.12 mysqld 1

F.12.1 Standard Options Supported by mysqld


F.12.2 Options Specific to mysqld


F.12.3 Variables for mysqld


F.13 mysqld_multi


F.13.1 Standard Options Supported by mysqld_multi


F.13.2 Options Specific to mysqld_multi


F.14 mysqld_safe


F.14.1 Standard Options Supported by mysqld_safe


F.14.2 Options Specific to mysqld_safe


F.15 mysqldump


F.15.1 Standard Options Supported by mysqldump


F.15.2 Options Specific to mysqldump


F.15.3 Data Format Options for mysqldump


F.15.4 Variables for mysqldump


F.16 mysqlimport


F.16.2 Options Specific to mysqlimport


F.16.3 Data Format Options for mysqlimport


F.17 mysqlshow


F.17.1 Standard Options Supported by mysqlshow


F.17.2 Options Specific to mysqlshow


F.18 perror


F.18.1 Standard Options Supported by perror





Appendix G C API Reference (Online Only) 


G.1 Compiling and Linking


G.2 C API Data Structures


G.2.1 Scalar Data Types


G.2.2 Nonscalar Data Structures


G.2.3 Accessor Macros


G.3 C API Functions


G.3.1 Client Library Initialization and Termination Routines


G.3.2 Connection Management Routines


G.3.3 Error-Reporting Routines


G.3.4 Statement Construction and Execution Routines


G.3.5 Result Set Processing Routines


G.3.6 Multiple Result Set Routines


G.3.7 Information Routines


G.3.8 Transaction Control Routines


G.3.9 Prepared Statement Routines


G.3.10 Administrative Routines


G.3.11 Threaded Client Routines


G.3.12 Debugging Routines





Appendix H Perl DBI API Reference (Online Only)


H.1 Writing Scripts


H.2 DBI Methods


H.2.1 DBI Class Methods


H.2.2 Database-Handle Methods


H.2.3 Statement-Handle Methods


H.2.4 General Handle Methods


H.2.5 MySQL-Specific Administrative Methods


H.3 DBI Utility Functions


H.4 DBI Attributes


H.4.1 Database-Handle Attributes


H.4.2 General Handle Attributes


H.4.3 MySQL-Specific Database-Handle Attributes


H.4.4 Statement-Handle Attributes


H.4.5 MySQL-Specific Statement-Handle Attributes


H.4.6 Dynamic Attributes


H.5 DBI Environment Variables





Appendix I PHP API Reference (Online Only) 


I.1 Writing PHP Scripts


I.2 PDO Classes


I.3 PDO Methods


I.3.1 PDO Class Methods


I.3.2 PDOStatement Object Methods


I.3.3 PDOException Object Methods


I.3.4 PDO Constants







Note: Appendixes G, H, and I are located online and are accessible either by registering this book at informit.com/register or visiting www.kitebird.com/mysql-book