You are previewing Managing & Using MySQL, 2nd Edition.
O'Reilly logo
Managing & Using MySQL, 2nd Edition

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. Managing and Using MySQL, 2nd Edition
  2. A Note Regarding Supplemental Files
  3. 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
  4. 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
  5. 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
  6. 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
  7. 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
        1. ALTER TABLE
        2. ANALYZE TABLE
        3. CREATE DATABASE
        4. CREATE FUNCTION
        5. CREATE INDEX
        6. CREATE TABLE
        7. DELETE
        8. DESCRIBE
        9. DESC
        10. DROP DATABASE
        11. DROP FUNCTION
        12. DROP INDEX
        13. DROP TABLE
        14. EXPLAIN
        15. FLUSH
        16. GRANT
        17. INSERT
        18. KILL
        19. LOAD
        20. LOCK
        21. OPTIMIZE
        22. REPLACE
        23. REVOKE
        24. SELECT
        25. SET
        26. SHOW
        27. TRUNCATE
        28. UNLOCK
        29. UPDATE
        30. USE
    2. 16. MySQL Data Types
      1. 16.1. Numeric Data Types
        1. BIGINT
        2. DEC
        3. DECIMAL
        4. DOUBLE
        5. DOUBLE PRECISION
        6. FLOAT
        7. INT
        8. INTEGER
        9. MEDIUMINT
        10. NUMERIC
        11. REAL
        12. SMALLINT
        13. TINYINT
      2. 16.2. String Data Types
        1. BLOB
        2. CHAR
        3. CHARACTER
        4. CHARACTER VARYING
        5. LONGBLOB
        6. LONGTEXT
        7. MEDIUMBLOB
        8. MEDIUMTEXT
        9. NCHAR
        10. NATIONAL CHAR
        11. NATIONAL CHARACTER
        12. NATIONAL VARCHAR
        13. TEXT
        14. TINYBLOB
        15. TINYTEXT
        16. VARCHAR
      3. 16.3. Date Data Types
        1. DATE
        2. DATETIME
        3. TIME
        4. TIMESTAMP
        5. YEAR
      4. 16.4. Complex Data Types
        1. ENUM
        2. SET
    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
        1. mysql_affected_rows
        2. mysql_change_user
        3. mysql_close
        4. mysql_connect
        5. mysql_create_db
        6. mysql_data_seek
        7. mysql_db_name
        8. mysql_drop_db
        9. mysql_errno
        10. mysql_error
        11. mysql_escape_string
        12. mysql_fetch_array
        13. mysql_fetch_assoc
        14. mysql_fetch_field
        15. mysql_fetch_lengths
        16. mysql_fetch_object
        17. mysql_fetch_row
        18. mysql_field_flags
        19. mysql_field_name
        20. mysql_field_len
        21. mysql_field_seek
        22. mysql_field_table
        23. mysql_field_type
        24. mysql_free_result
        25. mysql_get_client_info
        26. mysql_get_host_info
        27. mysql_get_proto_info
        28. mysql_get_server_info
        29. mysql_insert_id
        30. mysql_list_dbs
        31. mysql_list_fields
        32. mysql_list_tables
        33. mysql_num_fields
        34. mysql_num_rows
        35. mysql_pconnect
        36. mysql_query
        37. mysql_result
        38. mysql_select_db
        39. mysql_tablename
        40. mysql_unbuffered_query
    5. 19. C Reference
      1. 19.1. Data Types
      2. 19.2. Functions
        1. mysql_affected_rows
        2. mysql_change_user
        3. mysql_character_set_name
        4. mysql_close
        5. mysql_connect
        6. mysql_create_db
        7. mysql_data_seek
        8. mysql_debug
        9. mysql_drop_db
        10. mysql_dump_debug_info
        11. mysql_eof
        12. mysql_errno
        13. mysql_error
        14. mysql_escape_string
        15. mysql_fetch_field
        16. mysql_fetch_field_direct
        17. mysql_fetch_fields
        18. mysql_fetch_lengths
        19. mysql_fetch_row
        20. mysql_field_count
        21. mysql_field_seek
        22. mysql_field_tell
        23. mysql_free_result
        24. mysql_get_client_info
        25. mysql_get_host_info
        26. mysql_get_proto_info
        27. mysql_get_server_info
        28. mysql_info
        29. mysql_init
        30. mysql_insert_id
        31. mysql_kill
        32. mysql_list_dbs
        33. mysql_list_fields
        34. mysql_list_processes
        35. mysql_list_tables
        36. mysql_num_fields
        37. mysql_num_rows
        38. mysql_odbc_escape_string
        39. mysql_odbc_remove_escape
        40. mysql_options
        41. mysql_ping
        42. mysql_query
        43. mysql_read_query_result
        44. mysql_real_connect
        45. mysql_real_escape_string
        46. mysql_real_query
        47. mysql_refresh
        48. mysql_reload
        49. mysql_row_seek
        50. mysql_row_tell
        51. mysql_select_db
        52. mysql_send_query
        53. mysql_shutdown
        54. mysql_ssl_cipher
        55. mysql_ssl_clear
        56. mysql_ssl_set
        57. mysql_stat
        58. mysql_store_result
        59. mysql_thread_id
        60. mysql_thread_safe
        61. mysql_use_result
    6. 20. The Python DB-API
      1. 20.1. Module: MySQLdb
        1. 20.1.1. Module Attributes
          1. apilevel
          2. paramstyle
          3. quote_conv
          4. threadsafety
          5. type_conv
        2. 20.1.2. Module Methods
          1. MySQL.connect()
        3. 20.1.3. Connection Attributes
          1. db
        4. 20.1.4. Connection Methods
          1. close()
          2. commit()
          3. cursor()
          4. rollback( )
        5. 20.1.5. Cursor Attributes
          1. arraysize
          2. description
          3. rowcount
        6. 20.1.6. Cursor Methods
          1. callproc( )
          2. Method: close( )
          3. execute( )
          4. executemany( )
          5. Method: fetchall( )
          6. fetchmany( )
          7. fetchone( )
          8. insert_id( )*
          9. nextset( )
          10. setinputsizes( )
          11. setoutputsize( )
  8. Index
  9. About the Authors
  10. Colophon
  11. Copyright