MySQL®

MySQL®

by
Released August 2008
Publisher(s): Addison-Wesley Professional
ISBN: 9780768678673

Explore a preview version of MySQL® right now.

O’Reilly members get unlimited access to live online training experiences, plus books, videos, and digital content from 200+ publishers.

Start your free trial

Book description

The Definitive Guide to Using, Programming, and Administering MySQL 5.0 and 5.1

MySQL is an open source relational database management system that has experienced a phenomenal growth in popularity and use. Known for its speed and ease of use, MySQL has proven itself to be particularly well-suited for developing database-backed websites and applications.

In MySQL, Paul DuBois provides a comprehensive guide to using and administering MySQL effectively and productively. He describes everything from the basics of getting information into a database and formulating queries, to using MySQL with PHP or Perl to generate dynamic web pages, to writing your own programs that access MySQL databases, to administering MySQL servers.

The fourth edition of this bestselling book has been meticulously revised and updated to thoroughly cover the latest features and capabilities of MySQL 5.0, as well as to add new coverage of features introduced with MySQL 5.1.

“One of the best technical books I have read on any subject.”

–Gregory Haley, C Vu, The Association of C & C++ Users

“A top-notch user’s guide and reference manual, and in my opinion, the only book you’ll need for the daily operation and maintenance of MySQL databases.”

–Eugene Kim, Web Techniques

Introduction 1

Part I: General MySQL Use

Chapter 1: Getting Started with MySQL 13

Chapter 2: Using SQL to Manage Data 101

Chapter 3: Data Types 201

Chapter 4: Stored Programs 289

Chapter 5: Query Optimization 303

Part II: Using MySQL Programming Interfaces

Chapter 6: Introduction to MySQL Programming 341

Chapter 7: Writing MySQL Programs Using C 359

Chapter 8: Writing MySQL Programs Using Perl DBI 435

Chapter 9: Writing MySQL Programs Using PHP 527

Part III: MySQL Administration

Chapter 10: Introduction to MySQL Administration 579

Chapter 11: The MySQL Data Directory 585

Chapter 12: General MySQL Administration 609

Chapter 13: Access Control and Security 699

Chapter 14: Database Maintenance, Backups, and Replication 737

Part IV: Appendixes

Appendix A: Obtaining and Installing Software 777

Appendix B: Data Type Reference 797

Appendix C: Operator and Function Reference 813

Appendix D: System, Status, and User Variable Reference 889

Appendix E: SQL Syntax Reference 937

Appendix F: MySQL Program Reference 1037

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

Appendix G: C API Reference 1121

Appendix H: Perl DBI API Reference 1177

Appendix I: PHP API Reference 1207

Index 1225

Table of contents

  1. Copyright
  2. Developer’s Library
  3. About the Author
  4. Acknowledgments
    1. Fourth Edition
    2. Third Edition
    3. Second Edition
    4. First Edition
  5. We Want to Hear from You!
  6. Reader Services
  7. Introduction
    1. Why Choose MySQL?
    2. Already Running Another RDBMS?
    3. Tools Provided with MySQL
    4. What You Can Expect from This Book
    5. Road Map to This Book
      1. Part I: General MySQL Use
      2. Part II: Using MySQL Programming Interfaces
      3. Part III: MySQL Administration
      4. Part IV: Appendixes
    6. How to Read This Book
    7. Versions of Software Covered in This Book
    8. Conventions Used in This Book
    9. Additional Resources
  8. I. General MySQL Use
    1. 1. Getting Started with MySQL
      1. 1.1. How MySQL Can Help You
      2. 1.2. A Sample Database
        1. 1.2.1. The U.S. Historical League
        2. 1.2.2. The Grade-Keeping Project
        3. 1.2.3. How the Sample Database Applies to You
      3. 1.3. Basic Database Terminology
        1. 1.3.1. Structural Terminology
        2. 1.3.2. Query Language Terminology
        3. 1.3.3. MySQL Architectural Terminology
      4. 1.4. A MySQL Tutorial
        1. 1.4.1. Obtaining the Sample Database Distribution
        2. 1.4.2. Preliminary Requirements
        3. 1.4.3. Establishing and Terminating Connections to the MySQL Server
        4. 1.4.4. Executing SQL Statements
        5. 1.4.5. Creating a Database
        6. 1.4.6. Creating Tables
          1. 1.4.6.1. Tables for the U.S. Historical League
            1. 1.4.6.1.1. The president Table
            2. 1.4.6.1.2. The member Table
            3. 1.4.6.1.3. Creating the Historical League Tables
          2. 1.4.6.2. Tables for the Grade-Keeping Project
            1. 1.4.6.2.1. The student Table
            2. 1.4.6.2.2. The grade_event Table
            3. 1.4.6.2.3. The score Table
            4. 1.4.6.2.4. The absence Table
        7. 1.4.7. Adding New Rows
          1. 1.4.7.1. Adding Rows with INSERT
          2. 1.4.7.2. Adding New Rows from a File
        8. 1.4.8. Resetting the sampdb Database to a Known State
        9. 1.4.9. Retrieving Information
          1. 1.4.9.1. Specifying Retrieval Criteria
          2. 1.4.9.2. The NULL Value
          3. 1.4.9.3. Sorting Query Results
          4. 1.4.9.4. Limiting Query Results
          5. 1.4.9.5. Calculating and Naming Output Column Values
          6. 1.4.9.6. Working with Dates
          7. 1.4.9.7. Pattern Matching
          8. 1.4.9.8. Setting and Using User-Defined Variables
          9. 1.4.9.9. Generating Summaries
          10. 1.4.9.10. Retrieving Information from Multiple Tables
        10. 1.4.10. Deleting or Updating Existing Rows
      5. 1.5. Tips for Interacting with mysql
        1. 1.5.1. Simplifying the Connection Process
          1. 1.5.1.1. Using an Option File
          2. 1.5.1.2. Using Your Shell’s Command History
          3. 1.5.1.3. Using Shell Aliases and Scripts
        2. 1.5.2. Issuing Statements with Less Typing
          1. 1.5.2.1. Using the mysql Input Line Editor
          2. 1.5.2.2. Using Copy and Paste to Issue Statements
          3. 1.5.2.3. Using mysql to Execute Script Files
      6. 1.6. Where to Now?
    2. 2. Using SQL to Manage Data
      1. 2.1. The Server SQL Mode
      2. 2.2. MySQL Identifier Syntax and Naming Rules
      3. 2.3. Case Sensitivity in SQL Statements
      4. 2.4. Character Set Support
        1. 2.4.1. Specifying Character Sets
        2. 2.4.2. Determining Character Set Availability and Current Settings
        3. 2.4.3. Unicode Support
          1. 2.4.3.1. Unicode Support Prior to MySQL 6.0
          2. 2.4.3.2. Unicode Support in MySQL 6.0 and Up
      5. 2.5. Selecting, Creating, Dropping, and Altering Databases
        1. 2.5.1. Selecting Databases
        2. 2.5.2. Creating Databases
        3. 2.5.3. Dropping Databases
        4. 2.5.4. Altering Databases
      6. 2.6. Creating, Dropping, Indexing, and Altering Tables
        1. 2.6.1. Storage Engine Characteristics
          1. 2.6.1.1. Checking Which Storage Engines Are Available
          2. 2.6.1.2. Table Representation on Disk
          3. 2.6.1.3. The MyISAM Storage Engine
          4. 2.6.1.4. The MERGE Storage Engine
          5. 2.6.1.5. The MEMORY Storage Engine
          6. 2.6.1.6. The InnoDB Storage Engine
          7. 2.6.1.7. The Falcon Storage Engine
          8. 2.6.1.8. The FEDERATED Storage Engine
          9. 2.6.1.9. The NDB Storage Engine
          10. 2.6.1.10. Other Storage Engines
          11. 2.6.1.11. Storage Engine Portability Characteristics
        2. 2.6.2. Creating Tables
          1. 2.6.2.1. Table Options
          2. 2.6.2.2. Provisional Table Creation
          3. 2.6.2.3. Temporary Tables
          4. 2.6.2.4. Creating Tables from Other Tables or Query Results
          5. 2.6.2.5. Using MERGE Tables
          6. 2.6.2.6. Using Partitioned Tables
          7. 2.6.2.7. Using FEDERATED Tables
        3. 2.6.3. Dropping Tables
        4. 2.6.4. Indexing Tables
          1. 2.6.4.1. Storage Engine Index Characteristics
          2. 2.6.4.2. Creating Indexes
          3. 2.6.4.3. Dropping Indexes
        5. 2.6.5. Altering Table Structure
      7. 2.7. Obtaining Database Metadata
        1. 2.7.1. Obtaining Metadata with SHOW
        2. 2.7.2. Obtaining Metadata with INFORMATION_SCHEMA
        3. 2.7.3. Obtaining Metadata from the Command Line
      8. 2.8. Performing Multiple-Table Retrievals with Joins
        1. 2.8.1. The Inner Join
        2. 2.8.2. Qualifying References to Columns from Joined Tables
        3. 2.8.3. Left and Right (Outer) Joins
      9. 2.9. Performing Multiple-Table Retrievals with Subqueries
        1. 2.9.1. Subqueries with Relative Comparison Operators
        2. 2.9.2. IN and NOT IN Subqueries
        3. 2.9.3. ALL, ANY, and SOME Subqueries
        4. 2.9.4. EXISTS and NOT EXISTS Subqueries
        5. 2.9.5. Correlated Subqueries
        6. 2.9.6. Subqueries in the FROM Clause
        7. 2.9.7. Rewriting Subqueries as Joins
          1. 2.9.7.1. Rewriting Subqueries That Select Matching Values
          2. 2.9.7.2. Rewriting Subqueries That Select Non-Matching (Missing) Values
      10. 2.10. Performing Multiple-Table Retrievals with UNION
      11. 2.11. Using Views
      12. 2.12. Multiple-Table Deletes and Updates
      13. 2.13. Performing Transactions
        1. 2.13.1. Using Transactions to Ensure Safe Statement Execution
        2. 2.13.2. Using Transaction Savepoints
        3. 2.13.3. Transaction Isolation
        4. 2.13.4. Non-Transactional Approaches to Transactional Problems
      14. 2.14. Foreign Keys and Referential Integrity
        1. 2.14.1. Creating and Using Foreign Keys
        2. 2.14.2. Living Without Foreign Keys
      15. 2.15. Using FULLTEXT Searches
        1. 2.15.1. Natural Language FULLTEXT Searches
        2. 2.15.2. Boolean Mode FULLTEXT Searches
        3. 2.15.3. Query Expansion FULLTEXT Searches
        4. 2.15.4. Configuring the FULLTEXT Search Engine
    3. 3. Data Types
      1. 3.1. Data Value Categories
        1. 3.1.1. Numeric Values
          1. 3.1.1.1. Exact-Value and Approximate-Value Numbers
          2. 3.1.1.2. Bit-Field Values
        2. 3.1.2. String Values
          1. 3.1.2.1. Types of Strings and Character Set Support
          2. 3.1.2.2. Character Set-Related System Variables
        3. 3.1.3. Date and Time (Temporal) Values
        4. 3.1.4. Spatial Values
        5. 3.1.5. Boolean Values
        6. 3.1.6. The NULL Value
      2. 3.2. MySQL Data Types
        1. 3.2.1. Overview of Data Types
        2. 3.2.2. Specifying Column Types in Table Definitions
        3. 3.2.3. Specifying Column Default Values
        4. 3.2.4. Numeric Data Types
          1. 3.2.4.1. Exact-Value Numeric Data Types
          2. 3.2.4.2. Approximate-Value Numeric Data Types
          3. 3.2.4.3. The BIT Data Type
          4. 3.2.4.4. Numeric Data Type Attributes
          5. 3.2.4.5. Choosing Numeric Data Types
        5. 3.2.5. String Data Types
          1. 3.2.5.1. The CHAR and VARCHAR Data Types
          2. 3.2.5.2. The BINARY and VARBINARY Data Types
          3. 3.2.5.3. The BLOB and TEXT Data Types
          4. 3.2.5.4. The ENUM and SET Data Types
          5. 3.2.5.5. String Data Type Attributes
          6. 3.2.5.6. Choosing String Data Types
        6. 3.2.6. Date and Time Data Types
          1. 3.2.6.1. The DATE, TIME, and DATETIME Data Types
          2. 3.2.6.2. The TIMESTAMP Data Type
          3. 3.2.6.3. The YEAR Data Type
          4. 3.2.6.4. Date and Time Data Type Attributes
          5. 3.2.6.5. Working with Date and Time Values
          6. 3.2.6.6. Interpretation of Ambiguous Year Values
        7. 3.2.7. Spatial Data Types
      3. 3.3. How MySQL Handles Invalid Data Values
      4. 3.4. Working with Sequences
        1. 3.4.1. General AUTO_INCREMENT Properties
        2. 3.4.2. Storage Engine-Specific AUTO_INCREMENT Properties
          1. 3.4.2.1. AUTO_INCREMENT for MyISAM Tables
          2. 3.4.2.2. AUTO_INCREMENT for MEMORY Tables
          3. 3.4.2.3. AUTO_INCREMENT for InnoDB Tables
        3. 3.4.3. Issues to Consider with AUTO_INCREMENT Columns
        4. 3.4.4. Tips for Working with AUTO_INCREMENT Columns
          1. 3.4.4.1. Adding a Sequence Number Column to a Table
          2. 3.4.4.2. Resequencing an Existing Column
        5. 3.4.5. Generating Sequences Without AUTO_INCREMENT
      5. 3.5. Expression Evaluation and Type Conversion
        1. 3.5.1. Writing Expressions
          1. 3.5.1.1. Operator Types
          2. 3.5.1.2. Operator Precedence
          3. 3.5.1.3. NULL Values in Expressions
        2. 3.5.2. Type Conversion
          1. 3.5.2.1. Date and Time Interpretation Rules
          2. 3.5.2.2. Testing and Forcing Type Conversion
      6. 3.6. Choosing Data Types
        1. 3.6.1. What Kind of Values Will the Column Hold?
        2. 3.6.2. Do Your Values Lie Within Some Particular Range?
        3. 3.6.3. Inter-Relatedness of Data Type Choice Issues
    4. 4. Stored Programs
      1. 4.1. Compound Statements and Statement Delimiters
      2. 4.2. Stored Functions and Procedures
        1. 4.2.1. Privileges for Stored Functions and Procedures
        2. 4.2.2. Stored Procedure Parameter Types
      3. 4.3. Triggers
      4. 4.4. Events
      5. 4.5. Security for Stored Programs and Views
    5. 5. Query Optimization
      1. 5.1. Using Indexing
        1. 5.1.1. Benefits of Indexing
        2. 5.1.2. Costs of Indexing
        3. 5.1.3. Choosing Indexes
      2. 5.2. The MySQL Query Optimizer
        1. 5.2.1. How the Optimizer Works
        2. 5.2.2. Using EXPLAIN to Check Optimizer Operation
      3. 5.3. Choosing Data Types for Efficient Queries
      4. 5.4. Loading Data Efficiently
      5. 5.5. Scheduling and Locking Issues
        1. 5.5.1. Changing Statement Scheduling Priorities
        2. 5.5.2. Using Delayed Inserts
        3. 5.5.3. Using Concurrent Inserts
        4. 5.5.4. Locking Levels and Concurrency
      6. 5.6. Administrative-Level Optimizations
        1. 5.6.1. Using MyISAM Key Caches
        2. 5.6.2. Using the Query Cache
        3. 5.6.3. Hardware Optimizations
  9. II. Using MySQL Programming Interfaces
    1. 6. Introduction to MySQL Programming
      1. 6.1. Why Write Your Own MySQL Programs?
      2. 6.2. APIs Available for MySQL
        1. 6.2.1. The C API
        2. 6.2.2. The Perl DBI API
        3. 6.2.3. The PHP API
      3. 6.3. Choosing an API
        1. 6.3.1. Execution Environment
        2. 6.3.2. Performance
          1. 6.3.2.1. Compiled Versus Interpreted Languages
          2. 6.3.2.2. Standalone Versus Module Versions of Language Interpreters
        3. 6.3.3. Development Time
        4. 6.3.4. Portability
    2. 7. Writing MySQL Programs Using C
      1. 7.1. Compiling and Linking Client Programs
      2. 7.2. Connecting to the Server
      3. 7.3. Handling Errors and Processing Command Options
        1. 7.3.1. Checking for Errors
        2. 7.3.2. Getting Connection Parameters at Runtime
          1. 7.3.2.1. Accessing Option File Contents
          2. 7.3.2.2. Processing Command-Line Arguments
        3. 7.3.3. Incorporating Option-Processing into a MySQL Client Program
      4. 7.4. Processing SQL Statements
        1. 7.4.1. Handling Statements That Modify Rows
        2. 7.4.2. Handling Statements That Return a Result Set
        3. 7.4.3. A General-Purpose Statement Handler
        4. 7.4.4. Alternative Approaches to Statement Processing
        5. 7.4.5. mysql_store_result() Versus mysql_use_result()
        6. 7.4.6. Using Result Set Metadata
        7. 7.4.7. Encoding Special Characters and Binary Data
          1. 7.4.7.1. Working with Strings That Contain Special Characters
          2. 7.4.7.2. Working with Binary Data
      5. 7.5. An Interactive Statement-Execution Program
      6. 7.6. Writing Clients That Include SSL Support
      7. 7.7. Using the Embedded Server Library
        1. 7.7.1. Writing an Embedded Server Application
        2. 7.7.2. Producing the Application Executable Binary
      8. 7.8. Using Multiple-Statement Execution
      9. 7.9. Using Server-Side Prepared Statements
    3. 8. Writing MySQL Programs Using Perl DBI
      1. 8.1. Perl Script Characteristics
      2. 8.2. Perl DBI Overview
        1. 8.2.1. DBI Data Types
        2. 8.2.2. A Simple DBI Script
        3. 8.2.3. Handling Errors
        4. 8.2.4. Handling Statements That Modify Rows
        5. 8.2.5. Handling Statements That Return a Result Set
          1. 8.2.5.1. Writing Row-Fetching Loops
          2. 8.2.5.2. Determining the Number of Rows Returned by a Statement
          3. 8.2.5.3. Fetching Single-Row Results
          4. 8.2.5.4. Working with Complete Result Sets
          5. 8.2.5.5. Checking for NULL Values
        6. 8.2.6. Quoting Special Characters in Statement Strings
        7. 8.2.7. Placeholders and Prepared Statements
        8. 8.2.8. Binding Query Results to Script Variables
        9. 8.2.9. Specifying Connection Parameters
        10. 8.2.10. Debugging
          1. 8.2.10.1. Debugging Using Print Statements
          2. 8.2.10.2. Debugging Using Tracing
        11. 8.2.11. Using Result Set Metadata
        12. 8.2.12. Performing Transactions
      3. 8.3. Putting DBI to Work
        1. 8.3.1. Generating the Historical League Directory
          1. 8.3.1.1. Generating the Plain Text Member List
          2. 8.3.1.2. Generating the Rich Text Format Directory
        2. 8.3.2. Sending Membership Renewal Notices
        3. 8.3.3. Historical League Member Entry Editing
        4. 8.3.4. Finding Historical League Members with Common Interests
        5. 8.3.5. Putting the Historical League Directory Online
      4. 8.4. Using DBI in Web Applications
        1. 8.4.1. Setting Up Apache for CGI Scripts
        2. 8.4.2. A Brief CGI.pm Primer
          1. 8.4.2.1. Checking for Web Input Parameters
          2. 8.4.2.2. Producing Web Output
          3. 8.4.2.3. Escaping HTML and URL Text
          4. 8.4.2.4. Writing Multiple-Purpose Pages
        3. 8.4.3. Connecting to the MySQL Server from Web Scripts
        4. 8.4.4. A Web-Based Database Browser
        5. 8.4.5. A Grade-Keeping Project Score Browser
        6. 8.4.6. Historical League Common-Interest Searching
          1. 8.4.6.1. Performing Searches Using Pattern Matching
          2. 8.4.6.2. Performing Searches Using a FULLTEXT Index
    4. 9. Writing MySQL Programs Using PHP
      1. 9.1. PHP Overview
        1. 9.1.1. A Simple PHP Script
        2. 9.1.2. Using PHP Library Files for Code Encapsulation
        3. 9.1.3. A Simple Data-Retrieval Page
        4. 9.1.4. Processing Statement Results
          1. 9.1.4.1. Handling Statements That Modify Rows
          2. 9.1.4.2. Handling Statements That Return a Result Set
        5. 9.1.5. Testing for NULL Values in Query Results
        6. 9.1.6. Using Prepared Statements
        7. 9.1.7. Using Placeholders to Handle Data Quoting Issues
        8. 9.1.8. Handling Errors
      2. 9.2. Putting PHP to Work
        1. 9.2.1. An Online Score-Entry Application
          1. 9.2.1.1. Collecting Web Input in PHP
          2. 9.2.1.2. Displaying and Entering Scores
        2. 9.2.2. Creating an Interactive Online Quiz
        3. 9.2.3. Historical League Online Member Entry Editing
  10. III. MySQL Administration
    1. 10. Introduction to MySQL Administration
      1. 10.1. MySQL Components
      2. 10.2. General MySQL Administration
      3. 10.3. Access Control and Security
      4. 10.4. Database Maintenance, Backups, and Replication
    2. 11. The MySQL Data Directory
      1. 11.1. Location of the Data Directory
      2. 11.2. Structure of the Data Directory
        1. 11.2.1. How the MySQL Server Provides Access to Data
        2. 11.2.2. Representation of Databases in the Filesystem
        3. 11.2.3. Representation of Tables in the Filesystem
        4. 11.2.4. Representation of Views and Triggers in the Filesystem
        5. 11.2.5. How SQL Statements Map onto Table File Operations
        6. 11.2.6. Operating System Constraints on Database Object Names
        7. 11.2.7. Factors That Affect Maximum Table Size
        8. 11.2.8. Implications of Data Directory Structure for System Performance
        9. 11.2.9. MySQL Status and Log Files
          1. 11.2.9.1. The Process ID File
          2. 11.2.9.2. The MySQL Logs
      3. 11.3. Relocating Data Directory Contents
        1. 11.3.1. Relocation Methods
        2. 11.3.2. Relocation Precautions
        3. 11.3.3. Assessing the Effect of Relocation
        4. 11.3.4. Relocating the Entire Data Directory
        5. 11.3.5. Relocating Individual Databases
        6. 11.3.6. Relocating Individual Tables
        7. 11.3.7. Relocating the InnoDB Shared Tablespace
        8. 11.3.8. Relocating Status and Log Files
    3. 12. General MySQL Administration
      1. 12.1. Securing a New MySQL Installation
        1. 12.1.1. Establishing Passwords for the Initial MySQL Accounts
        2. 12.1.2. Setting Up Passwords for a Second Server
      2. 12.2. Arranging for MySQL Server Startup and Shutdown
        1. 12.2.1. Running the MySQL Server On Unix
          1. 12.2.1.1. Running the Server Using an Unprivileged Login Account
          2. 12.2.1.2. Starting the Server on Unix
        2. 12.2.2. Running the MySQL Server On Windows
          1. 12.2.2.1. Running the Server Manually on Windows
          2. 12.2.2.2. Running the Server as a Windows Service
        3. 12.2.3. Specifying Server Startup Options
        4. 12.2.4. Stopping the Server
        5. 12.2.5. Regaining Control of the Server When You Cannot Connect to It
      3. 12.3. Controlling How the Server Listens for Connections
      4. 12.4. Managing MySQL User Accounts
        1. 12.4.1. High-Level MySQL Account Management
          1. 12.4.1.1. Specifying Account Names
          2. 12.4.1.2. Specifying the Local Hostname in Account Names
        2. 12.4.2. Granting Privileges
          1. 12.4.2.1. Defining the Privileges an Account Has
          2. 12.4.2.2. Using the “No Privileges” USAGE Privilege
          3. 12.4.2.3. Requiring an Account to Use Secure Connections
          4. 12.4.2.4. Enabling an Account to Administer Privileges
          5. 12.4.2.5. Limiting an Account’s Resource Consumption
        3. 12.4.3. Displaying Account Privileges
        4. 12.4.4. Revoking Privileges and Removing Users
        5. 12.4.5. Changing Passwords or Resetting Lost Passwords
      5. 12.5. Maintaining Logs
        1. 12.5.1. The Error Log
          1. 12.5.1.1. The Error Log on Unix
          2. 12.5.1.2. The Error Log on Windows
        2. 12.5.2. The General Query Log
        3. 12.5.3. The Slow-Query Log
        4. 12.5.4. The Binary Log and the Binary Log Index File
        5. 12.5.5. The Relay Log and the Relay Log Index File
        6. 12.5.6. Using Log Tables
        7. 12.5.7. Log Management
          1. 12.5.7.1. Rotating Fixed-Name Log Files
          2. 12.5.7.2. Expiring Binary Log and Relay Log Files
          3. 12.5.7.3. Automating the Log Expiration Procedure
          4. 12.5.7.4. Expiring or Rotating Log Tables
      6. 12.6. Tuning the Server
        1. 12.6.1. Checking and Setting System Variable Values
          1. 12.6.1.1. Checking System Variable Values
          2. 12.6.1.2. Setting System Variables at Server Startup Time
          3. 12.6.1.3. Setting System Variables at Runtime
        2. 12.6.2. General-Purpose System Variables
        3. 12.6.3. Checking Status Variable Values
      7. 12.7. Storage Engine Configuration
        1. 12.7.1. Selecting Which Storage Engines a Server Supports
        2. 12.7.2. Configuring the MyISAM Storage Engine
        3. 12.7.3. Configuring the InnoDB Storage Engine
          1. 12.7.3.1. Configuring the InnoDB Tablespace
            1. 12.7.3.1.1. Shared InnoDB Tablespace Configuration Parameters
            2. 12.7.3.1.2. Configuring the Shared InnoDB Tablespace
            3. 12.7.3.1.3. Reconfiguring the Shared InnoDB Tablespace
            4. 12.7.3.1.4. Using Individual (Per-Table) InnoDB Tablespaces
          2. 12.7.3.2. InnoDB Storage Engine Variables
        4. 12.7.4. Configuring the Falcon Storage Engine
      8. 12.8. Enabling or Disabling LOCAL Capability for LOAD DATA
      9. 12.9. Internationalization and Localization Issues
        1. 12.9.1. Configuring Time Zone Support
        2. 12.9.2. Selecting the Language for Error Messages
        3. 12.9.3. Configuring Character Set Support
      10. 12.10. Running Multiple Servers
        1. 12.10.1. General Multiple Server Issues
        2. 12.10.2. Configuring and Compiling Different Servers
        3. 12.10.3. Strategies for Specifying Startup Options
        4. 12.10.4. Using mysqld_multi for Server Management
        5. 12.10.5. Running Multiple Servers on Windows
      11. 12.11. Updating MySQL
    4. 13. Access Control and Security
      1. 13.1. Internal Security: Preventing Unauthorized Filesystem Access
        1. 13.1.1. How to Steal Data
        2. 13.1.2. Securing Your MySQL Installation
          1. 13.1.2.1. Securing the Unix Socket File
          2. 13.1.2.2. Securing Option Files
      2. 13.2. External Security: Preventing Unauthorized Network Access
        1. 13.2.1. Structure and Contents of the MySQL Grant Tables
          1. 13.2.1.1. Grant Table Scope-of-Access Columns
          2. 13.2.1.2. Grant Table Privilege Columns
          3. 13.2.1.3. Administrative Privileges
          4. 13.2.1.4. Database and Table Privileges
          5. 13.2.1.5. How the Grant Tables Represent Privileges
          6. 13.2.1.6. Grant Table SSL-Related Columns
          7. 13.2.1.7. Grant Table Resource Management Columns
        2. 13.2.2. How the Server Controls Client Access
          1. 13.2.2.1. Scope Column Contents
          2. 13.2.2.2. Statement Access Verification
          3. 13.2.2.3. Scope Column Matching Order
        3. 13.2.3. A Privilege Puzzle
        4. 13.2.4. Grant Table Risks to Avoid
      3. 13.3. Setting Up Secure Connections
    5. 14. Database Maintenance, Backups, and Replication
      1. 14.1. Principles of Preventive Maintenance
      2. 14.2. Performing Database Maintenance with the Server Running
        1. 14.2.1. Locking Individual Tables for Read-Only or Read/Write Access
          1. 14.2.1.1. Locking a Table for Read-Only Access
          2. 14.2.1.2. Locking a Table for Read/Write Access
        2. 14.2.2. Locking All Databases for Read-Only Access
      3. 14.3. General Preventative Maintenance
        1. 14.3.1. Using the Server’s Auto-Recovery Capabilities
        2. 14.3.2. Scheduling Preventive Maintenance
      4. 14.4. Making Database Backups
        1. 14.4.1. Making Text Backups with mysqldump
        2. 14.4.2. Making Binary Database Backups
          1. 14.4.2.1. Making a Complete Binary Backup
          2. 14.4.2.2. Making a Partial Binary Backup
          3. 14.4.2.3. Making Backups with mysqlhotcopy
        3. 14.4.3. Backing Up InnoDB or Falcon Tables
      5. 14.5. Copying Databases to Another Server
        1. 14.5.1. Copying Databases Using a Backup File
        2. 14.5.2. Copying Databases from One Server to Another
      6. 14.6. Checking and Repairing Database Tables
        1. 14.6.1. Using the Server to Check and Repair Tables
        2. 14.6.1.1. Checking Tables with CHECK TABLE
        3. 14.6.1.2. Repairing Tables with REPAIR TABLE
        4. 14.6.2. Using mysqlcheck to Check and Repair Tables
        5. 14.6.3. Using myisamchk to Check and Repair Tables
          1. 14.6.3.1. Checking Tables with myisamchk
          2. 14.6.3.2. Repairing Tables with myisamchk
          3. 14.6.3.3. Getting myisamchk to Run Faster
      7. 14.7. Using Backups for Data Recovery
        1. 14.7.1. Recovering Entire Databases
        2. 14.7.2. Recovering Individual Tables
        3. 14.7.3. Re-Executing Statements in Binary Log Files
        4. 14.7.4. Coping with InnoDB Auto-Recovery Problems
      8. 14.8. Setting Up Replication Servers
        1. 14.8.1. How Replication Works
        2. 14.8.2. Establishing a Master-Slave Replication Relationship
        3. 14.8.3. Binary Logging Formats
        4. 14.8.4. Using a Replication Slave for Making Backups
  11. IV. Appendixes
    1. A. Obtaining and Installing Software
      1. A.1. Obtaining the sampdb Sample Database Distribution
      2. A.2. Obtaining MySQL and Related Software
      3. A.3. Choosing a Version of MySQL
      4. A.4. Installing MySQL on Unix
        1. A.4.1. Creating a Login Account for the MySQL User
        2. A.4.2. Obtaining and Installing a MySQL Distribution on Unix
          1. A.4.2.1. Installing a tar File Binary Distribution
          2. A.4.2.2. Installing an RPM Distribution
          3. A.4.2.3. Installing a Source Distribution
        3. A.4.3. Post-Installation Steps
          1. A.4.3.1. Setting Your PATH Environment Variable
          2. A.4.3.2. Initializing the Data Directory and Grant Tables
          3. A.4.3.3. Starting the Server
          4. A.4.3.4. Installing or Upgrading Additional System Tables
        4. A.4.4. Installing Perl DBI Support on Unix
        5. A.4.5. Installing Apache and PHP on Unix
      5. A.5. Installing MySQL on Windows
        1. A.5.1. Installing Perl DBI Support on Windows
        2. A.5.2. Installing Apache and PHP on Windows
    2. B. Data Type Reference
      1. B.1. Numeric Types
        1. B.1.1. Integer Types
        2. B.1.2. Fixed-Point Types
        3. B.1.3. Floating-Point Types
        4. B.1.4. BIT Type
      2. B.2. String Types
        1. B.2.1. Binary String Types
        2. B.2.2. Non-Binary String Types
        3. B.2.3. ENUM and SET Types
      3. B.3. Date and Time Types
      4. B.4. Spatial Types
    3. C. Operator and Function Reference
      1. C.1. Operators
        1. C.1.1. Operator Precedence
        2. C.1.2. Grouping Operators
        3. C.1.3. Arithmetic Operators
        4. C.1.4. Comparison Operators
        5. C.1.5. Bit Operators
        6. C.1.6. Logical Operators
        7. C.1.7. Cast Operators
        8. C.1.8. Pattern-Matching Operators
      2. C.2. Functions
        1. C.2.1. Comparison Functions
        2. C.2.2. Cast Functions
        3. C.2.3. Numeric Functions
        4. C.2.4. String Functions
        5. C.2.5. Date and Time Functions
        6. C.2.6. Summary Functions
        7. C.2.7. Security and Compression Functions
        8. C.2.8. Advisory Locking Functions
        9. C.2.9. Spatial Functions
          1. C.2.9.1. Spatial Value Format-Conversion Functions
          2. C.2.9.2. Spatial Property Functions
          3. C.2.9.3. Spatial Relationship Functions
        10. C.2.10. XML Functions
        11. C.2.11. Miscellaneous Functions
    4. D. System, Status, and User Variable Reference
      1. D.1. System Variables
      2. D.2. Session-Only System Variables
      3. D.3. Status Variables
        1. D.3.1. InnoDB Status Variables
        2. D.3.2. Query Cache Status Variables
        3. D.3.3. SSL Status Variables
      4. D.4. User-Defined Variables
    5. E. SQL Syntax Reference
      1. E.1. SQL Statement Syntax (Non-Compound Statements)
        1. ALTER DATABASE
        2. ALTER EVENT
        3. ALTER FUNCTION, ALTER PROCEDURE
        4. ALTER SERVER
        5. ALTER TABLE
        6. ALTER VIEW
        7. ANALYZE TABLE
        8. BEGIN
        9. CACHE INDEX
        10. CALL
        11. CHANGE MASTER
        12. CHECK TABLE
        13. CHECKSUM TABLE
        14. COMMIT
        15. CREATE DATABASE
        16. CREATE EVENT
        17. CREATE FUNCTION, CREATE PROCEDURE
        18. CREATE INDEX
        19. CREATE SERVER
        20. CREATE TABLE
        21. CREATE TRIGGER
        22. CREATE USER
        23. CREATE VIEW
        24. DEALLOCATE PREPARE
        25. DELETE
        26. DESCRIBE
        27. DO
        28. DROP DATABASE
        29. DROP EVENT
        30. DROP FUNCTION, DROP PROCEDURE
        31. DROP INDEX
        32. DROP SERVER
        33. DROP TABLE
        34. DROP TRIGGER
        35. DROP USER
        36. DROP VIEW
        37. EXECUTE
        38. EXPLAIN
        39. FLUSH
        40. GRANT
        41. HANDLER
        42. INSERT
        43. KILL
        44. LOAD DATA
        45. LOAD INDEX INTO CACHE
        46. LOCK TABLE
        47. OPTIMIZE TABLE
        48. PREPARE
        49. PURGE MASTER LOGS
        50. RELEASE SAVEPOINT
        51. RENAME TABLE
        52. RENAME USER
        53. REPAIR TABLE
        54. REPLACE
        55. RESET
        56. REVOKE
        57. ROLLBACK
        58. SAVEPOINT
        59. SELECT
        60. SET
        61. SET PASSWORD
        62. SET TRANSACTION
        63. SHOW
        64. SHOW BINLOG EVENTS
        65. SHOW CHARACTER SET
        66. SHOW COLLATION
        67. SHOW COLUMNS
        68. SHOW CREATE
        69. SHOW DATABASES
        70. SHOW ENGINE
        71. SHOW ENGINES
        72. SHOW ERRORS
        73. SHOW EVENTS
        74. SHOW FUNCTION STATUS, SHOW PROCEDURE STATUS
        75. SHOW GRANTS
        76. SHOW INDEX
        77. SHOW INNODB STATUS
        78. SHOW MASTER LOGS
        79. SHOW MASTER STATUS
        80. SHOW MUTEX STATUS
        81. SHOW OPEN TABLES
        82. SHOW PRIVILEGES
        83. SHOW PROCESSLIST
        84. SHOW SLAVE HOSTS
        85. SHOW SLAVE STATUS
        86. SHOW STATUS
        87. SHOW TABLE STATUS
        88. SHOW TABLE TYPES
        89. SHOW TABLES
        90. SHOW TRIGGERS
        91. SHOW VARIABLES
        92. SHOW WARNINGS
        93. START SLAVE
        94. START TRANSACTION
        95. STOP SLAVE
        96. TRUNCATE
        97. UNION
        98. UNLOCK TABLE
        99. UPDATE
        100. USE
      2. E.2. Compound Statement Syntax
        1. E.2.1. Control Structure Statements
          1. BEGIN ... END
          2. CASE
          3. IF
          4. ITERATE
          5. LEAVE
          6. LOOP
          7. REPEAT
          8. RETURN
          9. WHILE
        2. E.2.2. Declaration Statements
          1. DECLARE
        3. E.2.3. Cursor Statements
          1. CLOSE
          2. FETCH
          3. OPEN
      3. E.3. Comment Syntax
    6. F. MySQL Program Reference
      1. F.1. Displaying a Program’s Help Message
      2. F.2. Specifying Program Options
        1. F.2.1. Standard MySQL Program Options
          1. F.2.1.1. Standard SSL Options
          2. F.2.1.2. Setting Program Variables
        2. F.2.2. Option Files
          1. F.2.2.1. Keeping User-Specific Option Files Private
          2. F.2.2.2. Using my_print_defaults to Check Options
        3. F.2.3. Environment Variables
      3. F.3. myisamchk
        1. F.3.1. Standard Options Supported by myisamchk
        2. F.3.2. Options Specific to myisamchk
        3. F.3.3. Variables for myisamchk
      4. F.4. myisampack
        1. F.4.1. Standard Options Supported by myisampack
        2. F.4.2. Options Specific to myisampack
      5. F.5. mysql
        1. F.5.1. Standard Options Supported by mysql
        2. F.5.2. Options Specific to mysql
        3. F.5.3. Variables for mysql
        4. F.5.4. mysql Commands
        5. F.5.5. mysql Prompt Definition Sequences
      6. F.6. mysql.server
        1. F.6.1. Options Supported by mysql.server
      7. F.7. mysql_config
        1. F.7.1. Options Specific to mysql_config
      8. F.8. mysql_install_db
        1. F.8.1. Standard Options Supported by mysql_install_db
        2. F.8.2. Options Specific to mysql_install_db
      9. F.9. mysqladmin
        1. F.9.1. Standard Options Supported by mysqladmin
        2. F.9.2. Options Specific to mysqladmin
        3. F.9.3. Variables for mysqladmin
        4. F.9.4. mysqladmin Commands
      10. F.10. mysqlbinlog
        1. F.10.1. Standard Options Supported by mysqlbinlog
        2. F.10.2. Options Specific to mysqlbinlog
        3. F.10.3. Variables for mysqlbinlog
      11. F.11. mysqlcheck
        1. F.11.1. Standard Options Supported by mysqlcheck
        2. F.11.2. Options Specific to mysqlcheck
      12. F.12. mysqld
        1. F.12.1. Standard Options Supported by mysqld
        2. F.12.2. Options Specific to mysqld
          1. F.12.2.1. Windows Options
          2. F.12.2.2. InnoDB Options
          3. F.12.2.3. Replication Options
        3. F.12.3. Variables for mysqld
      13. F.13. mysqld_multi
        1. F.13.1. Standard Options Supported by mysqld_multi
        2. F.13.2. Options Specific to mysqld_multi
      14. F.14. mysqld_safe
        1. F.14.1. Standard Options Supported by mysqld_safe
        2. F.14.2. Options Specific to mysqld_safe
      15. F.15. mysqldump
        1. F.15.1. Standard Options Supported by mysqldump
        2. F.15.2. Options Specific to mysqldump
        3. F.15.3. Data Format Options for mysqldump
        4. F.15.4. Variables for mysqldump
      16. F.16. mysqlhotcopy
        1. F.16.1. Standard Options Supported by mysqlhotcopy
        2. F.16.2. Options Specific to mysqlhotcopy
      17. F.17. mysqlimport
        1. F.17.1. Standard Options Supported by mysqlimport
        2. F.17.2. Options Specific to mysqlimport
        3. F.17.3. Data Format Options for mysqlimport
      18. F.18. mysqlshow
        1. F.18.1. Standard Options Supported by mysqlshow
        2. F.18.2. Options Specific to mysqlshow
      19. F.19. perror
        1. F.19.1. Standard Options Supported by perror
    7. G. C API Reference
      1. G.1. Compiling and Linking
      2. G.2. C API Data Types
        1. G.2.1. Scalar Data Types
        2. G.2.2. Non-Scalar Data Types
        3. G.2.3. Accessor Macros
      3. G.3. C API Functions
        1. G.3.1. Client Library Initialization and Termination Routines
        2. G.3.2. Connection Management Routines
        3. G.3.3. Error-Reporting Routines
        4. G.3.4. Statement Construction and Execution Routines
        5. G.3.5. Result Set Processing Routines
        6. G.3.6. Information Routines
        7. G.3.7. Transaction Control Routines
        8. G.3.8. Multiple Result Set Routines
        9. G.3.9. Prepared Statement Routines
          1. G.3.9.1. Prepared Statement Error-Reporting Routines
          2. G.3.9.2. Prepared Statement Construction and Execution Routines
          3. G.3.9.3. Prepared Statement Result Set Processing Routines
        10. G.3.10. Administrative Routines
        11. G.3.11. Threaded Client Routines
        12. G.3.12. Debugging Routines
    8. H. Perl DBI API Reference
      1. H.1. Writing Scripts
      2. H.2. DBI Methods
        1. H.2.1. DBI Class Methods
        2. H.2.2. Database-Handle Methods
        3. H.2.3. Statement-Handle Methods
        4. H.2.4. General Handle Methods
        5. H.2.5. MySQL-Specific Administrative Methods
      3. H.3. DBI Utility Functions
      4. H.4. DBI Attributes
        1. H.4.1. Database-Handle Attributes
        2. H.4.2. General Handle Attributes
        3. H.4.3. MySQL-Specific Database-Handle Attributes
        4. H.4.4. Statement-Handle Attributes
        5. H.4.5. MySQL-Specific Statement-Handle Attributes
        6. H.4.6. Dynamic Attributes
      5. H.5. DBI Environment Variables
    9. I. PHP API Reference
      1. I.1. Writing PHP Scripts
      2. I.2. PDO Classes
      3. I.3. PDO Methods
        1. I.3.1. PDO Class Methods
        2. I.3.2. PDOStatement Object Methods
        3. I.3.3. PDOException Object Methods
        4. I.3.4. PDO Constants

Product information

  • Title: MySQL®
  • Author(s):
  • Release date: August 2008
  • Publisher(s): Addison-Wesley Professional
  • ISBN: 9780768678673