Managing & Using MySQL, 2nd Edition

Managing & Using MySQL, 2nd Edition

by Tim King, George Reese, Randy Yarger, Hugh E. Williams
Released April 2002
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596002114

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon Buy on ebooks.com
Start your free trial

Book description

MySQL is a popular and robust open source database product that supports key subsets of SQL on both Linux and Unix systems. MySQL is free for nonprofit use and costs a small amount for commercial use. Unlike commercial databases, MySQL is affordable and easy to use. This book includes introductions to SQL and to relational database theory. If you plan to use MySQL to build web sites or other Linux or Unix applications, this book teaches you to do that, and it will remain useful as a reference once you understand the basics. Ample tutorial material and examples are included throughout.

This book has all you need to take full advantage of this powerful database management system. It takes you through the whole process from installation and configuration to programming interfaces and database administration. This second edition has a greatly enhanced administration chapter that includes information on administrative tools, server configuration, server startup and shutdown, log file management, database backup and restore, and database administration and repair. In addition, a new chapter on security describes data, server, and client-server security, while a chapter on extending MySQL provides an overview of MySQL internals and describes the use of MySQL user-defined functions.

If you know C/C++, Java, Perl, PHP, or Python, you can write programs to interact with your MySQL database. In addition, you can embed queries and updates directly in an HTML file so that a web page becomes its own interface to the database. Managing and Using MySQL includes chapters on the programming language interfaces, and it also includes a complete reference section with specific function calls for each language.

Also included in the reference section are references to the SQL language, and details of the MySQL system variables, programs, and utilities. New to the second edition is a reference to the internal MySQL tables, which will be of particular interest to those who want to work extensively with MySQL security.

Table of contents

  1. A Note Regarding Supplemental Files
  2. Preface
    1. Audience
    2. Purpose
    3. Using This Book
    4. Conventions Used in This Book
    5. Comments and Questions
    6. Acknowledgments
      1. From Randy Yarger
      2. From George Reese
      3. From Tim King
  3. I. Introduction
    1. 1. MySQL
      1. 1.1. Relational Databases
      2. 1.2. The History of MySQL
      3. 1.3. MySQL Design
      4. 1.4. MySQL Features
      5. 1.5. MySQL Applications
      6. 1.6. What You Get
    2. 2. Installation
      1. 2.1. Preparation
      2. 2.2. Unix Installation
        1. 2.2.1. Binary (Tarball) Distributions
        2. 2.2.2. Binary (RPM) Distributions
        3. 2.2.3. Source Distributions
      3. 2.3. Windows Installation
        1. 2.3.1. Windows 9x Startup
        2. 2.3.2. Windows NT/2000 Startup
    3. 3. SQL According to MySQL
      1. 3.1. SQL Basics
        1. 3.1.1. The SQL Story
        2. 3.1.2. The Design of SQL
        3. 3.1.3. Sending SQL to MySQL
      2. 3.2. Database Creation
      3. 3.3. Table Management
      4. 3.4. MySQL Data Types
        1. 3.4.1. Numeric Types
        2. 3.4.2. Character Types
        3. 3.4.3. Binary Data Types
        4. 3.4.4. Enumerations and Sets
        5. 3.4.5. Other Kinds of Data
      5. 3.5. Indexing
      6. 3.6. Managing Data
        1. 3.6.1. Inserts
        2. 3.6.2. Sequence Generation
        3. 3.6.3. Updates
        4. 3.6.4. The WHERE Clause
        5. 3.6.5. Deletes
      7. 3.7. Queries
        1. 3.7.1. Joins
        2. 3.7.2. Aliasing
        3. 3.7.3. Ordering and Grouping
          1. 3.7.3.1. Basic ordering
          2. 3.7.3.2. Localized sorting
          3. 3.7.3.3. Grouping
        4. 3.7.4. Limiting Results
      8. 3.8. SQL Operators
        1. 3.8.1. Logical Operators
        2. 3.8.2. Null’s Idiosyncrasies
        3. 3.8.3. Membership Tests
        4. 3.8.4. Pattern Matching
      9. 3.9. Advanced Features
        1. 3.9.1. Full Text Searching
          1. 3.9.1.1. The Basics
          2. 3.9.1.2. Relevance values
          3. 3.9.1.3. Boolean mode
          4. 3.9.1.4. Tips
        2. 3.9.2. Transactions
        3. 3.9.3. Table Locking
        4. 3.9.4. Functions
          1. 3.9.4.1. Date functions
          2. 3.9.4.2. String functions
        5. 3.9.5. Outer Joins
        6. 3.9.6. Unions
        7. 3.9.7. Batch Processing
          1. 3.9.7.1. Command-line loads
          2. 3.9.7.2. The LOAD command
          3. 3.9.7.3. Pulling data from MySQL
    4. 4. Database Administration
      1. 4.1. Configuration
        1. 4.1.1. File Locations
        2. 4.1.2. File Content
      2. 4.2. Server Startup and Shutdown
        1. 4.2.1. Unix/Linux
          1. 4.2.1.1. SVR4
          2. 4.2.1.2. Other Unix
        2. 4.2.2. Mac OS X
        3. 4.2.3. Windows NT/2000
      3. 4.3. Logging
        1. 4.3.1. The Error Log
        2. 4.3.2. The Binary Log
        3. 4.3.3. The Slow Query Log
        4. 4.3.4. Log Rotation
      4. 4.4. Backup
        1. 4.4.1. mysqldump
        2. 4.4.2. mysqlhotcopy
      5. 4.5. Recovery
        1. 4.5.1. mysqldump Recovery
        2. 4.5.2. mysqlhotcopy Recovery
      6. 4.6. Table Maintenance and Crash Recovery
        1. 4.6.1. Checking a Table
        2. 4.6.2. Repairing a Table
        3. 4.6.3. Scheduled Table Checking
  4. II. MySQL Administration
    1. 5. Performance Tuning
      1. 5.1. An Approach to Performance Tuning
      2. 5.2. Application Tuning
        1. 5.2.1. Host Application Tuning
        2. 5.2.2. SQL Query Tuning
          1. 5.2.2.1. Index guidelines
          2. 5.2.2.2. EXPLAIN SELECT
          3. 5.2.2.3. Other options
      3. 5.3. Database Server Tuning
      4. 5.4. Operating System/Hardware Tuning
    2. 6. Security
      1. 6.1. Database Security
        1. 6.1.1. User Management
        2. 6.1.2. Privilege Management
          1. 6.1.2.1. GRANT and REVOKE
          2. 6.1.2.2. The security tables
        3. 6.1.3. Recovering from Password and Permission Problems
      2. 6.2. System Security
        1. 6.2.1. Operating System Security
        2. 6.2.2. Hardware Security
        3. 6.2.3. Network Security
          1. 6.2.3.1. Network topology
          2. 6.2.3.2. Encryption
          3. 6.2.3.3. Direct compromise
      3. 6.3. Application Security
        1. 6.3.1. The Application Server
          1. 6.3.1.1. User management
          2. 6.3.1.2. Resource protection
        2. 6.3.2. Client Applications
    3. 7. Database Design
      1. 7.1. Database Design Primer
        1. 7.1.1. Database Entities
        2. 7.1.2. Entity Attributes
        3. 7.1.3. Data Model
      2. 7.2. Normalization
        1. 7.2.1. First Normal Form
        2. 7.2.2. The Unique Identifier
        3. 7.2.3. Relationships
        4. 7.2.4. Second Normal Form
        5. 7.2.5. Kinds of Relationships
        6. 7.2.6. Refining Relationships
        7. 7.2.7. More 2NF
        8. 7.2.8. Third Normal Form
      3. 7.3. A Logical Data-Modeling Methodology
      4. 7.4. Physical Database Design
        1. 7.4.1. Tables and Columns
        2. 7.4.2. Foreign Keys
  5. III. MySQL Programming
    1. 8. Database Applications
      1. 8.1. Architecture
        1. 8.1.1. Client/Server Architecture
          1. 8.1.1.1. Application logic
          2. 8.1.1.2. Fat and thin clients
        2. 8.1.2. Distributed Application Architecture
        3. 8.1.3. Web Architecture
      2. 8.2. Connections and Transactions
        1. 8.2.1. Connections
        2. 8.2.2. Transactions
          1. 8.2.2.1. Transaction isolation levels
          2. 8.2.2.2. Using READ UNCOMMITTED
      3. 8.3. Object/Relational Modeling
    2. 9. Perl
      1. 9.1. Introduction to DBI
        1. 9.1.1. Basic Perl Example
        2. 9.1.2. Setting Up the Database and Program
        3. 9.1.3. Error Handling and Attributes
        4. 9.1.4. Introducing Bind Variables and Optimizations
      2. 9.2. DBI and CGI
        1. 9.2.1. Introduction to Perl CGI
        2. 9.2.2. The Model/View/Controller Methodology
        3. 9.2.3. A Sample CGI/DBI Program
      3. 9.3. A General Model for Maintainable Perl Programs
        1. 9.3.1. A Model for Relational Data
        2. 9.3.2. Implementing the Model
          1. 9.3.2.1. The Publisher class
          2. 9.3.2.2. Methods that build and execute SQL
          3. 9.3.2.3. Methods that handle WHERE clauses
          4. 9.3.2.4. Getter/setter methods
          5. 9.3.2.5. Primary key select method
          6. 9.3.2.6. Constructors
          7. 9.3.2.7. The DB class
          8. 9.3.2.8. The mysql class
          9. 9.3.2.9. The Cache class
        3. 9.3.3. Example of the Model’s Use
    3. 10. Python
      1. 10.1. DB-API
        1. 10.1.1. The Database Connection
        2. 10.1.2. Cursors
        3. 10.1.3. Parameterized SQL
        4. 10.1.4. Other Objects
      2. 10.2. Proprietary Operations
      3. 10.3. Applied DB-API
    4. 11. PHP
      1. 11.1. Introducing PHP
        1. 11.1.1. A Short Language Primer
      2. 11.2. Installing PHP
        1. 11.2.1. Getting Started Under Unix
          1. 11.2.1.1. Installation problems
        2. 11.2.2. Getting Started Under Microsoft Windows
      3. 11.3. Accessing the MySQL DBMS with PHP
        1. 11.3.1. The Wedding Gift Registry Database
        2. 11.3.2. Opening and Using a Database Connection
        3. 11.3.3. Handling Results
        4. 11.3.4. Frequently Used MySQL Library Functions
        5. 11.3.5. Handling MySQL Errors
        6. 11.3.6. Include Files
      4. 11.4. Securing User Data
      5. 11.5. Managing Sessions
      6. 11.6. Writing Data with PHP
      7. 11.7. Using the HTML <form> Environment
      8. 11.8. Where to Find Out More
    5. 12. C API
      1. 12.1. API Overview
        1. 12.1.1. The Connection
        2. 12.1.2. Queries and Results
        3. 12.1.3. Closing the Connection
      2. 12.2. The C API in Practice
        1. 12.2.1. Support Functions
        2. 12.2.2. Quote Retrieval
        3. 12.2.3. Adding Symbols
      3. 12.3. Advanced Issues
    6. 13. Java
      1. 13.1. The JDBC API
        1. 13.1.1. The JDBC Architecture
        2. 13.1.2. Connecting to MySQL
          1. 13.1.2.1. Data source connectivity
          2. 13.1.2.2. Driver manager connectivity
        3. 13.1.3. Maintaining Portability Using Properties Files
          1. 13.1.3.1. Properties files
          2. 13.1.3.2. Data sources revisited
      2. 13.2. Simple Database Access
        1. 13.2.1. Queries and Result Sets
        2. 13.2.2. Error Handling and Clean Up
      3. 13.3. Dynamic Database Access
        1. 13.3.1. Metadata
        2. 13.3.2. Processing Dynamic SQL
      4. 13.4. A Guest Book Servlet
    7. 14. Extending MySQL
      1. 14.1. User-Defined Functions
        1. 14.1.1. Standard Functions
          1. 14.1.1.1. The init routine
          2. 14.1.1.2. The main routine
          3. 14.1.1.3. The deinit routine
        2. 14.1.2. Aggregate Functions
          1. 14.1.2.1. init
          2. 14.1.2.2. reset
          3. 14.1.2.3. add
          4. 14.1.2.4. main
          5. 14.1.2.5. deinit
          6. 14.1.2.6. Aggregate example
        3. 14.1.3. Calling a UDF
      2. 14.2. Alternative Character Sets
        1. 14.2.1. Simple Character Sets
        2. 14.2.2. Complex Character Sets
  6. IV. MySQL Reference
    1. 15. SQL Syntax for MySQL
      1. 15.1. Basic Syntax
        1. 15.1.1. Literals
        2. 15.1.2. Identifiers
        3. 15.1.3. Comments
      2. 15.2. SQL Commands
    2. 16. MySQL Data Types
      1. 16.1. Numeric Data Types
      2. 16.2. String Data Types
      3. 16.3. Date Data Types
      4. 16.4. Complex Data Types
    3. 17. Operators and Functions
      1. 17.1. Operators
        1. 17.1.1. Rules of Precedence
        2. 17.1.2. Arithmetic Operators
        3. 17.1.3. Comparison Operators
        4. 17.1.4. Logical Operators
      2. 17.2. Functions
        1. 17.2.1. Aggregate Functions
        2. 17.2.2. General Functions
    4. 18. MySQL PHP API Reference
      1. 18.1. Data Types
      2. 18.2. Functions
    5. 19. C Reference
      1. 19.1. Data Types
      2. 19.2. Functions
    6. 20. The Python DB-API
      1. 20.1. Module: MySQLdb
        1. 20.1.1. Module Attributes
        2. 20.1.2. Module Methods
        3. 20.1.3. Connection Attributes
        4. 20.1.4. Connection Methods
        5. 20.1.5. Cursor Attributes
        6. 20.1.6. Cursor Methods
  7. Index
  8. About the Authors
  9. Colophon
  10. Copyright

Product information

  • Title: Managing & Using MySQL, 2nd Edition
  • Author(s): Tim King, George Reese, Randy Yarger, Hugh E. Williams
  • Release date: April 2002
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596002114