MySQL® Administrator's Bible

MySQL® Administrator's Bible

Released May 2009
Publisher(s): Wiley
ISBN: 9780470416914

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.

Start your free trial

Book description

With special focus on the next major release of MySQL, this resource provides a solid framework for anyone new to MySQL or transitioning from another database platform, as well as experience MySQL administrators. The high-profile author duo provides essential coverage of the fundamentals of MySQL database management—including MySQL’s unique approach to basic database features and functions—as well as coverage of SQL queries, data and index types, stores procedure and functions, triggers and views, and transactions. They also present comprehensive coverage of such topics as MySQL server tuning, managing storage engines, caching, backup and recovery, managing users, index tuning, database and performance monitoring, security, and more.

Table of contents

  1. Copyright
  2. About the Authors
  3. Credits
  4. Acknowledgments
    1. Sheeri Cabral
    2. Keith Murphy
  5. Introduction
    1. Who Should Read This Book
    2. How This Book Is Organized
      1. Conventions and Features
        1. Tips, Notes, and Cautions
    3. What's on the Companion Website
    4. Where To Go From Here
  6. I. First Steps with MySQL
    1. 1. Introduction to MySQL
      1. 1.1. MySQL Mission—Speed, Reliability, and Ease of Use
        1. 1.1.1. Company background
        2. 1.1.2. Community and Enterprise server versions
      2. 1.2. The MySQL Community
        1. 1.2.1. How to contribute
        2. 1.2.2. Reasons to contribute
      3. 1.3. Summary
    2. 2. Installing and Upgrading MySQL Server
      1. 2.1. Before Installation
        1. 2.1.1. Choosing the MySQL version
        2. 2.1.2. MySQL support
        3. 2.1.3. Downloads
      2. 2.2. Installation
        1. 2.2.1. MySQL Server installations on Unix
          1. 2.2.1.1. Installing from an rpm file on GNU/Linux
          2. 2.2.1.2. Installing from a pkg file on Solaris
          3. 2.2.1.3. Installing from an archive package on Unix
        2. 2.2.2. MySQL Server Installation on Windows
          1. 2.2.2.1. Installing MySQL from the installation packages on windows
          2. 2.2.2.2. Initial configuration of MySQL on windows
            1. 2.2.2.2.1. Service Options Screen
            2. 2.2.2.2.2. Security Options Screen
            3. 2.2.2.2.3. Confirmation Screen
        3. 2.2.3. Installing MySQL from a Noinstall Zip Archive
        4. 2.2.4. Starting and stopping MySQL from the Windows command line
        5. 2.2.5. Starting and stopping MySQL as a Windows service
      3. 2.3. Initial Configuration
        1. 2.3.1. Unix configuration file
        2. 2.3.2. Windows configuration file
      4. 2.4. MySQL Configuration Wizard on Windows
        1. 2.4.1. Detailed Configuration
        2. 2.4.2. The Server Type screen
        3. 2.4.3. Database Usage screen
        4. 2.4.4. InnoDB Tablespace screen
        5. 2.4.5. Concurrent Connections screen
        6. 2.4.6. Networking Options and Strict Mode Options screen
        7. 2.4.7. Character Set screen
        8. 2.4.8. Service Options screen
        9. 2.4.9. Security Options screen
        10. 2.4.10. Confirmation screen
      5. 2.5. MySQL Post-Install Configuration on Unix
        1. 2.5.1. Initializing the system tables
        2. 2.5.2. Setting initial passwords
        3. 2.5.3. Root user password assignment
        4. 2.5.4. Anonymous users
      6. 2.6. Securing Your System
      7. 2.7. Windows PATH Variable Configuration
        1. 2.7.1. Automated startup
        2. 2.7.2. Starting and stopping mysqld on System V-based Unix
        3. 2.7.3. System V run levels
      8. 2.8. Upgrading mysqld
        1. 2.8.1. The MySQL changelog
        2. 2.8.2. Upgrading MySQL on Windows
      9. 2.9. Troubleshooting
      10. 2.10. Summary
    3. 3. Accessing MySQL
      1. 3.1. Accessing mysqld with Command-Line Tools
        1. 3.1.1. Frequently used options
        2. 3.1.2. Using the command-line mysql client
          1. 3.1.2.1. Non-interactive modes
          2. 3.1.2.2. Useful features of mysql
        3. 3.1.3. mysqladmin—Client for administering a server
      2. 3.2. GUI Tools
        1. 3.2.1. SQLyog
          1. 3.2.1.1. Server connections
          2. 3.2.1.2. SQL query creation
          3. 3.2.1.3. Database management
          4. 3.2.1.4. Database synchronization
        2. 3.2.2. phpMyAdmin
          1. 3.2.2.1. Creating a database
          2. 3.2.2.2. Creating a table in your database
          3. 3.2.2.3. Data insertion into a table
          4. 3.2.2.4. Database backup
        3. 3.2.3. MySQL Query Browser
        4. 3.2.4. MySQL Administrator
          1. 3.2.4.1. User administration
          2. 3.2.4.2. Health monitoring
          3. 3.2.4.3. Backup and recovery
          4. 3.2.4.4. Server information
          5. 3.2.4.5. Replication status
          6. 3.2.4.6. Server log management
          7. 3.2.4.7. Service control
          8. 3.2.4.8. Catalogs
        5. 3.2.5. MySQL Workbench
      3. 3.3. Summary
  7. II. Developing with MySQL
    1. 4. How MySQL Extends and Deviates from SQL
      1. 4.1. Learning MySQL Language Structure
        1. 4.1.1. Comments and portability
        2. 4.1.2. Case-sensitivity
        3. 4.1.3. Escape characters
        4. 4.1.4. Naming limitations and quoting
        5. 4.1.5. Dot notation
        6. 4.1.6. Time zones
        7. 4.1.7. Character sets and collations
      2. 4.2. Understanding MySQL Deviations
        1. 4.2.1. Privileges and permissions
        2. 4.2.2. Transaction management
        3. 4.2.3. Check constraints
        4. 4.2.4. Upsert statements
      3. 4.3. Using MySQL Extensions
        1. 4.3.1. Aliases
        2. 4.3.2. ALTER TABLE extensions
        3. 4.3.3. CREATE extensions
        4. 4.3.4. DML extensions
        5. 4.3.5. DROP extensions
        6. 4.3.6. The LIMIT extension
        7. 4.3.7. SELECT extensions
        8. 4.3.8. SELECT ... INTO OUTFILE/SELECT ... INTO DUMPFILE
        9. 4.3.9. SQL_SMALL_RESULT/SQL_BIG_RESULT
        10. 4.3.10. UNION ... ORDER BY
        11. 4.3.11. SELECT ... FOR UPDATE
        12. 4.3.12. SELECT ... LOCK IN SHARE MODE
        13. 4.3.13. DISTINCTROW
          1. 4.3.13.1. SQL_CALC_FOUND_ROWS
        14. 4.3.14. SQL_BUFFER_RESULT
        15. 4.3.15. HIGH_PRIORITY/LOW_PRIORITY
        16. 4.3.16. Server maintenance extensions
        17. 4.3.17. The SET extension and user-defined variables
          1. 4.3.17.1. Local variables in stored code
          2. 4.3.17.2. Assigning values to dynamic server variables
        18. 4.3.18. The SHOW extension
        19. 4.3.19. Table definition extensions
        20. 4.3.20. Table maintenance extensions
          1. 4.3.20.1. Index and data corruption
          2. 4.3.20.2. Fragmentation
          3. 4.3.20.3. Reclaiming disk space
          4. 4.3.20.4. Maintaining table statistics
          5. 4.3.20.5. Getting a table checksum
        21. 4.3.21. Transactional statement extensions
      4. 4.4. Summary
    2. 5. MySQL Data Types
      1. 5.1. Looking at MySQL Data Types
      2. 5.2. Character String Types
        1. 5.2.1. Length
          1. 5.2.1.1. CHAR Length
          2. 5.2.1.2. VARCHAR length
          3. 5.2.1.3. TEXT sizes
        2. 5.2.2. Character string type attributes
      3. 5.3. National Character String Types
      4. 5.4. Binary Large Object String Types
        1. 5.4.1. BLOB values
        2. 5.4.2. BINARY values
        3. 5.4.3. BINARY length
        4. 5.4.4. VARBINARY length
      5. 5.5. Numeric Types
        1. 5.5.1. Numeric data sizes and ranges
          1. 5.5.1.1. DECIMAL size and range
          2. 5.5.1.2. Integer sizes and ranges
          3. 5.5.1.3. BIT size and range
          4. 5.5.1.4. FLOAT size and range
          5. 5.5.1.5. DOUBLE size and range
        2. 5.5.2. Numeric data type attributes
      6. 5.6. Boolean Types
      7. 5.7. Datetime Types
        1. 5.7.1. Allowed input values
        2. 5.7.2. Microsecond input
        3. 5.7.3. Automatic updates
        4. 5.7.4. Conversion issues
        5. 5.7.5. Numeric functions and DATETIME types
        6. 5.7.6. Other conversion issues
        7. 5.7.7. Datetime data type attributes
        8. 5.7.8. The effect of time zones
      8. 5.8. Interval Types
      9. 5.9. ENUM and SET Types
        1. 5.9.1. Enumerations
        2. 5.9.2. ENUM and SET data type attributes
      10. 5.10. Choosing SQL Modes
        1. 5.10.1. Invalid Data
        2. 5.10.2. SQL modes
          1. 5.10.2.1. SQL mode functionality
          2. 5.10.2.2. SQL mode definitions
      11. 5.11. Using NULL Values
      12. 5.12. Finding an Optimal Data Type for Existing Data
        1. 5.12.1. Small data samples and PROCEDURE ANALYSE()
      13. 5.13. Summary
    3. 6. MySQL Index Types
      1. 6.1. Looking at Keys and Indexes
      2. 6.2. Using Indexes to Speed Up Lookups
        1. 6.2.1. Creating and dropping indexes
        2. 6.2.2. Index order
        3. 6.2.3. Index length
        4. 6.2.4. Index types
        5. 6.2.5. Redundant indexes
      3. 6.3. Creating and Dropping Key Constraints
        1. 6.3.1. Creating and dropping unique key constraints
        2. 6.3.2. Creating and dropping foreign key constraints
        3. 6.3.3. Foreign key constraints and data changes
        4. 6.3.4. Requirements for foreign key constraints
      4. 6.4. Using FULLTEXT Indexes
      5. 6.5. Summary
    4. 7. Stored Routines, Triggers, and Events
      1. 7.1. Comparing Stored Routines, Triggers, and Events
      2. 7.2. Using Triggers
        1. 7.2.1. Creating a trigger
        2. 7.2.2. Dropping a trigger
        3. 7.2.3. Multiple SQL statements in triggers
        4. 7.2.4. Changing a trigger
        5. 7.2.5. Triggers on views and temporary tables
        6. 7.2.6. Trigger runtime behavior
          1. 7.2.6.1. Changing the sql_mode of a trigger
          2. 7.2.6.2. Character set and collation
          3. 7.2.6.3. Changing whom a trigger is invoked as
        7. 7.2.7. Finding all triggers
        8. 7.2.8. Trigger storage and backup
        9. 7.2.9. Triggers and replication
        10. 7.2.10. Trigger limitations
      3. 7.3. Using Stored Routines
        1. 7.3.1. Performance implications of stored routines
        2. 7.3.2. Stored procedures vs. stored functions
        3. 7.3.3. Creating a stored routine
        4. 7.3.4. Invoking a stored procedure
        5. 7.3.5. Dropping a stored routine
        6. 7.3.6. Multiple SQL statements in stored routines
        7. 7.3.7. INOUT arguments to a stored procedure
        8. 7.3.8. Local variables
        9. 7.3.9. Stored routine runtime behavior
          1. 7.3.9.1. sql_mode
          2. 7.3.9.2. Character set and collation
          3. 7.3.9.3. How the stored routine runs
        10. 7.3.10. Options when creating routines
          1. 7.3.10.1. Comments
          2. 7.3.10.2. Language
          3. 7.3.10.3. Determinism
          4. 7.3.10.4. SQL usage
          5. 7.3.10.5. Full CREATE PROCEDURE Syntax
        11. 7.3.11. Creating a basic stored function
        12. 7.3.12. Full CREATE FUNCTION syntax
        13. 7.3.13. Invoking a stored function
        14. 7.3.14. Changing a stored routine
        15. 7.3.15. Naming: stored routines
        16. 7.3.16. Stored procedure result sets
        17. 7.3.17. Stored routine errors and warnings
        18. 7.3.18. Conditions and handlers
          1. 7.3.18.1. Handlers
          2. 7.3.18.2. Conditions
        19. 7.3.19. Stored routine flow control
          1. 7.3.19.1. IF
          2. 7.3.19.2. CASE
          3. 7.3.19.3. WHILE
          4. 7.3.19.4. REPEAT
          5. 7.3.19.5. LOOP
            1. 7.3.19.5.1. ITERATE
            2. 7.3.19.5.2. LEAVE
        20. 7.3.20. Recursion
        21. 7.3.21. Stored routines and replication
        22. 7.3.22. Stored function limitations
        23. 7.3.23. Stored routine backup and storage
      4. 7.4. Using Cursors
      5. 7.5. Using Events
        1. 7.5.1. Turning on the event scheduler
        2. 7.5.2. Creating an event
        3. 7.5.3. Dropping an event
        4. 7.5.4. Multiple SQL statements in events
        5. 7.5.5. Start and end times for periodic events
        6. 7.5.6. Event status
        7. 7.5.7. Finding all events
        8. 7.5.8. Changing an event
        9. 7.5.9. After the last execution of an event
        10. 7.5.10. Event logging
        11. 7.5.11. Event runtime behavior
          1. 7.5.11.1. sql_mode
          2. 7.5.11.2. Character set and collation
          3. 7.5.11.3. Changing whom an event is invoked as
        12. 7.5.12. Event limitations
        13. 7.5.13. Event backup and storage
      6. 7.6. Summary
    5. 8. MySQL Views
      1. 8.1. Defining Views
        1. 8.1.1. View definition limitations and unexpected behavior
        2. 8.1.2. Security and privacy
        3. 8.1.3. Specify a view's definer
        4. 8.1.4. Abstraction and simplification
        5. 8.1.5. Performance
          1. 8.1.5.1. View algorithm
          2. 8.1.5.2. Materialized views and view indexes
          3. 8.1.5.3. Simulating check constraints
          4. 8.1.5.4. WITH CHECK OPTION on views that reference other views
        6. 8.1.6. Updatable views
          1. 8.1.6.1. Updatable view limitations
          2. 8.1.6.2. Updatable view problems
      2. 8.2. Changing a View Definition
      3. 8.3. Replication and Views
      4. 8.4. Summary
    6. 9. Transactions in MySQL
      1. 9.1. Understanding ACID Compliance
        1. 9.1.1. Atomicity
        2. 9.1.2. Consistency
        3. 9.1.3. Isolation
        4. 9.1.4. Durability
      2. 9.2. Using Transactional Statements
        1. 9.2.1. BEGIN, BEGIN WORK, and START TRANSACTION
        2. 9.2.2. COMMIT
        3. 9.2.3. ROLLBACK
        4. 9.2.4. Savepoints
        5. 9.2.5. AUTOCOMMIT
      3. 9.3. Using Isolation Levels
        1. 9.3.1. READ UNCOMMITED
        2. 9.3.2. READ COMMITTED
        3. 9.3.3. REPEATABLE READ
        4. 9.3.4. SERIALIZABLE
        5. 9.3.5. Multi-version concurrency control
      4. 9.4. Explaining Locking and Deadlocks
        1. 9.4.1. Table-level locks
        2. 9.4.2. Page-level locks
        3. 9.4.3. Row-level locks
      5. 9.5. Recovering MySQL Transactions
      6. 9.6. Summary
  8. III. Core MySQL Administration
    1. 10. MySQL Server Tuning
      1. 10.1. Choosing Optimal Hardware
      2. 10.2. Tuning the Operating System
        1. 10.2.1. Operating system architecture
        2. 10.2.2. File systems and partitions
        3. 10.2.3. Buffers
        4. 10.2.4. Kernel parameters
        5. 10.2.5. Linux
          1. 10.2.5.1. Open files limit
          2. 10.2.5.2. I/O schedulers
          3. 10.2.5.3. Swap memory
        6. 10.2.6. Other daemons
      3. 10.3. Tuning MySQL Server
        1. 10.3.1. Status variables
        2. 10.3.2. System variables
        3. 10.3.3. Option file
          1. 10.3.3.1. Overall mysqld options
          2. 10.3.3.2. MyISAM storage engine options
          3. 10.3.3.3. InnoDB storage engine options
          4. 10.3.3.4. Falcon storage engine options
          5. 10.3.3.5. Maria storage engine options
          6. 10.3.3.6. Query cache options
        4. 10.3.4. Dynamic variables
      4. 10.4. SUMMARY
    2. 11. Storage Engines
      1. 11.1. Understanding Storage Engines
        1. 11.1.1. Storage engines as plugins
        2. 11.1.2. Storage engine comparison
      2. 11.2. Using Different Storage Engines
        1. 11.2.1. MyISAM storage engine
          1. 11.2.1.1. MyISAM configuration options
          2. 11.2.1.2. MyISAM utilities
          3. 11.2.1.3. myisamchk
          4. 11.2.1.4. myisampack
          5. 11.2.1.5. myisam_ftdump
          6. 11.2.1.6. Merge storage engine
        2. 11.2.2. InnoDB storage engine
          1. 11.2.2.1. Tablespace configuration variables
          2. 11.2.2.2. Performance configuration variables
          3. 11.2.2.3. SHOW ENGINE InnoDB STATUS
          4. 11.2.2.4. InnoDB tablespace management
          5. 11.2.2.5. Working with ibdata Files
          6. 11.2.2.6. Adding an additional ibdata file to a shared tablespace
          7. 11.2.2.7. Removing or reorganizing shared tablespace files
          8. 11.2.2.8. Moving from a shared tablespace file to per-table tablespace
          9. 11.2.2.9. InnoDB log files and crash recovery
        3. 11.2.3. MEMORY storage engine
          1. 11.2.3.1. MEMORY configuration options
        4. 11.2.4. Maria storage engine
          1. 11.2.4.1. Maria configuration options
          2. 11.2.4.2. Log configuration parameters
          3. 11.2.4.3. Page cache configuration
          4. 11.2.4.4. Maria table options
          5. 11.2.4.5. Miscellaneous configuration
          6. 11.2.4.6. Maria command-line tools
        5. 11.2.5. Falcon storage engine
          1. 11.2.5.1. Architectural overview
          2. 11.2.5.2. Falcon configuration options
          3. 11.2.5.3. Performance optimization configuration
          4. 11.2.5.4. Transactional support configuration
          5. 11.2.5.5. Record cache configuration
          6. 11.2.5.6. Serial log configuration
          7. 11.2.5.7. Falcon tablespace management
          8. 11.2.5.8. Creating a new tablespace
          9. 11.2.5.9. Creating tables and indexes
        6. 11.2.6. PBXT storage engine
          1. 11.2.6.1. Architectural overview
          2. 11.2.6.2. PBXT configuration options
          3. 11.2.6.3. Transaction configuration
          4. 11.2.6.4. Data log configuration
          5. 11.2.6.5. File growth variables
          6. 11.2.6.6. SHOW ENGINE PBXT STATUS
          7. 11.2.6.7. Installing the PBXT binary plugin
        7. 11.2.7. FEDERATED storage engine
          1. 11.2.7.1. Limitations of FEDERATED tables
        8. 11.2.8. NDB storage engine
        9. 11.2.9. Archive storage engine
        10. 11.2.10. Blackhole storage engine
        11. 11.2.11. CSV storage engine
      3. 11.3. Working with Storage Engines
        1. 11.3.1. CREATE TABLE
        2. 11.3.2. ALTER TABLE
        3. 11.3.3. DROP TABLE
      4. 11.4. Summary
    3. 12. Caching with MySQL
      1. 12.1. Implementing Cache Tables
      2. 12.2. Working with the Query Cache
        1. 12.2.1. What gets stored in the query cache?
        2. 12.2.2. Query cache memory usage and tuning
        3. 12.2.3. Query cache fragmentation
      3. 12.3. Utilizing Memcached
      4. 12.4. Summary
    4. 13. Backups and Recovery
      1. 13.1. Backing Up MySQL
        1. 13.1.1. Uses for backups
          1. 13.1.1.1. Disaster recovery
          2. 13.1.1.2. Data recovery
          3. 13.1.1.3. Easy test server creation
          4. 13.1.1.4. Easy slave creation
        2. 13.1.2. Backup frequency
        3. 13.1.3. What to back up
        4. 13.1.4. Backup locations
        5. 13.1.5. Backup methods
          1. 13.1.5.1. mysqldump
          2. 13.1.5.2. SELECT INTO OUTFILE
          3. 13.1.5.3. maatkit parallel dump/restore
          4. 13.1.5.4. File system snapshot
          5. 13.1.5.5. ZFS
          6. 13.1.5.6. LVM
          7. 13.1.5.7. SAN-based
          8. 13.1.5.8. Microsoft VSS
        6. 13.1.6. Online backup
        7. 13.1.7. mysqlhotcopy
        8. 13.1.8. Commercial options
          1. 13.1.8.1. InnoDB hot backup
          2. 13.1.8.2. Zmanda
          3. 13.1.8.3. R1Soft
      2. 13.2. Copying Databases to Another Machine
      3. 13.3. Recovering from Crashes
      4. 13.4. Planning for Disasters
      5. 13.5. Summary
    5. 14. User Management
      1. 14.1. Learning about MySQL Users
        1. 14.1.1. Access control lists
        2. 14.1.2. Wildcards
        3. 14.1.3. System tables
      2. 14.2. Managing User Accounts
        1. 14.2.1. GRANT and REVOKE commands
          1. 14.2.1.1. Global
          2. 14.2.1.2. Database
          3. 14.2.1.3. Table
          4. 14.2.1.4. Column
          5. 14.2.1.5. Routine
          6. 14.2.1.6. REVOKE
        2. 14.2.2. SHOW GRANTS and mk-show-grants
      3. 14.3. Resetting the Root Password
        1. 14.3.1. Windows server
        2. 14.3.2. Unix-Based server
      4. 14.4. Debugging User Account Problems
        1. 14.4.1. Bad password
        2. 14.4.2. Access issues
        3. 14.4.3. Client does not support authentication protocol
        4. 14.4.4. Can't connect to local mysqld through socket '/path/to/mysqld.sock'
        5. 14.4.5. I do not have the right permissions!
      5. 14.5. Summary
    6. 15. Partitioning
      1. 15.1. Learning About Partitioning
      2. 15.2. Partitioning Tables
        1. 15.2.1. RANGE partitioning
        2. 15.2.2. LIST partitioning
        3. 15.2.3. HASH partitioning
        4. 15.2.4. KEY partitioning
        5. 15.2.5. Composite partitioning
        6. 15.2.6. Partition management commands
          1. 15.2.6.1. Adding and dropping partitions
          2. 15.2.6.2. Partition table maintenance
        7. 15.2.7. Restrictions of partitioning
      3. 15.3. MERGE Tables
        1. 15.3.1. Creating a MERGE table
        2. 15.3.2. Changing a MERGE table
        3. 15.3.3. Advantages of MERGE tables
      4. 15.4. Partitioning with MySQL Cluster
      5. 15.5. Programmatic Partitioning
      6. 15.6. Summary
    7. 16. Logging and Replication
      1. 16.1. Log Files
        1. 16.1.1. Error log
        2. 16.1.2. Binary logs
        3. 16.1.3. Relay logs
        4. 16.1.4. General and slow query logs
        5. 16.1.5. Rotating logs
          1. 16.1.5.1. flush logs
          2. 16.1.5.2. max_binlog_size
          3. 16.1.5.3. purge binary logs
          4. 16.1.5.4. expire_logs_days
        6. 16.1.6. Other methods of rotating
      2. 16.2. Replication
        1. 16.2.1. Setting up semisynchronous replication
        2. 16.2.2. Statement-based, row-based, and mixed-based replication
      3. 16.3. Replication Configurations
        1. 16.3.1. Simple replication
        2. 16.3.2. CHANGE MASTER statement
        3. 16.3.3. More complex setups
          1. 16.3.3.1. Single Master and Multiple Slaves
          2. 16.3.3.2. Master and relay slave
          3. 16.3.3.3. Master-Master Replication
          4. 16.3.3.4. Circular replication
        4. 16.3.4. Additional replication configuration options
      4. 16.4. Correcting Data Drift
        1. 16.4.1. mk-table-checksum overview
        2. 16.4.2. mk-table-sync overview
        3. 16.4.3. Putting this together
      5. 16.5. Summary
    8. 17. Measuring Performance
      1. 17.1. Benchmarking
        1. 17.1.1. mysqlslap
        2. 17.1.2. SysBench
          1. 17.1.2.1. CPU Test Mode
          2. 17.1.2.2. I/O Test Mode
          3. 17.1.2.3. mutex contention test mode
          4. 17.1.2.4. Memory test mode
          5. 17.1.2.5. OLTP test mode
          6. 17.1.2.6. Compiling SysBench
        3. 17.1.3. Benchmarking recommendations
      2. 17.2. Profiling
        1. 17.2.1. SHOW GLOBAL STATUS
        2. 17.2.2. mysqltuner
          1. 17.2.2.1. General statistics
          2. 17.2.2.2. Storage engine statistics
          3. 17.2.2.3. Performance metrics
        3. 17.2.3. mysqlreport
        4. 17.2.4. mk-query-profiler
        5. 17.2.5. mysqldumpslow
      3. 17.3. Capacity Planning
      4. 17.4. Summary
  9. IV. Extending Your Skills
    1. 18. Query Analysis and Index Tuning
      1. 18.1. Using EXPLAIN
        1. 18.1.1. EXPLAIN plan basics
        2. 18.1.2. Data access strategy
          1. 18.1.2.1. Full table scan
          2. 18.1.2.2. Full index scan
          3. 18.1.2.3. Partial index scan
          4. 18.1.2.4. Using more than one index
          5. 18.1.2.5. Looking up nonunique, nullable index values
          6. 18.1.2.6. Data access strategy for fulltext searching
          7. 18.1.2.7. Joining and looking up nonunique index values
          8. 18.1.2.8. Joins and unique index values
          9. 18.1.2.9. Looking up unique index values
          10. 18.1.2.10. Constant propagation
          11. 18.1.2.11. Retrieve at most one record from a system table
          12. 18.1.2.12. No Data accesss strategy
        3. 18.1.3. EXPLAIN plan indexes
        4. 18.1.4. Rows
        5. 18.1.5. Extra
        6. 18.1.6. Subqueries and EXPLAIN
        7. 18.1.7. EXPLAIN EXTENDED
      2. 18.2. EXPLAIN on Non-SELECT Statements
      3. 18.3. Other Query Analysis Tools
      4. 18.4. Optimizing Queries
        1. 18.4.1. Factors affecting key usage
        2. 18.4.2. Optimizer hints
        3. 18.4.3. Adding an Index
          1. 18.4.3.1. Optimizing away using filesort
          2. 18.4.3.2. Optimizing away range checked for each record
        4. 18.4.4. Optimizing away Using temporary
        5. 18.4.5. Using an index by eliminating functions
        6. 18.4.6. Non-index schema changes
        7. 18.4.7. Batching expensive operations
        8. 18.4.8. Optimizing frequent operations
      5. 18.5. Summary
    2. 19. Monitoring Your Systems
      1. 19.1. Deciding What to Monitor
      2. 19.2. Examining Open Source Monitoring
        1. 19.2.1. Nagios
        2. 19.2.2. Cacti
        3. 19.2.3. Hyperic HQ
        4. 19.2.4. OpenNMS
        5. 19.2.5. Zenoss Core
        6. 19.2.6. Munin
        7. 19.2.7. Monit
      3. 19.3. Examining Commercial Monitoring
        1. 19.3.1. MySQL enterprise monitor
        2. 19.3.2. MONyog
      4. 19.4. Summary
    3. 20. Securing MySQL
      1. 20.1. Access Control Lists
        1. 20.1.1. Wildcards and blank values
        2. 20.1.2. Privilege and privilege levels
      2. 20.2. Accessing the Operating System
        1. 20.2.1. Database access
        2. 20.2.2. Changing MySQL connectivity defaults
        3. 20.2.3. Operating system login
      3. 20.3. Securing Backups and Logs
      4. 20.4. Data Security
        1. 20.4.1. Data flow
        2. 20.4.2. Encrypted connectivity
          1. 20.4.2.1. Confirming encrypted connectivity support
          2. 20.4.2.2. Creating the mysqld certificate
          3. 20.4.2.3. Configure mysqld to enable encrypted connections
          4. 20.4.2.4. Requiring encrypted connections
        3. 20.4.3. Data security using mysql objects
      5. 20.5. Creating Security Policies
      6. 20.6. Summary
    4. 21. The MySQL Data Dictionary
      1. 21.1. Object Catalog
        1. 21.1.1. SCHEMATA
        2. 21.1.2. TABLES
        3. 21.1.3. VIEWS
        4. 21.1.4. COLUMNS
        5. 21.1.5. STATISTICS
        6. 21.1.6. TABLE_CONSTRAINTS
        7. 21.1.7. KEY_COLUMN_USAGE
        8. 21.1.8. REFERENTIAL_CONSTRAINTS
        9. 21.1.9. TRIGGERS
        10. 21.1.10. ROUTINES
        11. 21.1.11. PARAMETERS
        12. 21.1.12. EVENTS
        13. 21.1.13. PARTITIONS
      2. 21.2. System Information
        1. 21.2.1. CHARACTER_SETS
        2. 21.2.2. COLLATIONS
        3. 21.2.3. COLLATION_CHARACTER_SET_APPLICABILITY
        4. 21.2.4. ENGINES
        5. 21.2.5. PLUGINS
        6. 21.2.6. PROCESSLIST
        7. 21.2.7. PROFILING
        8. 21.2.8. GLOBAL_VARIABLES
        9. 21.2.9. SESSION_VARIABLES
        10. 21.2.10. GLOBAL_STATUS
        11. 21.2.11. SESSION_STATUS
      3. 21.3. Displaying Permissions
        1. 21.3.1. COLUMN_PRIVILEGES
        2. 21.3.2. TABLE_PRIVILEGES
        3. 21.3.3. SCHEMA_PRIVILEGES
        4. 21.3.4. USER_PRIVILEGES
      4. 21.4. Storage Engine-Specific Metadata
      5. 21.5. Custom Metadata
        1. 21.5.1. Defining the plugin
        2. 21.5.2. Compiling the plugin
        3. 21.5.3. Installing the plugin
      6. 21.6. Summary
    5. 22. Scaling and High Availability Architectures
      1. 22.1. Replication
        1. 22.1.1. One read slave
        2. 22.1.2. Promoting a new master
          1. 22.1.2.1. Gather information
          2. 22.1.2.2. Make sure the slave is caught up applying its relay logs
          3. 22.1.2.3. Stop and reset the slave process
          4. 22.1.2.4. Change configuration parameters
          5. 22.1.2.5. Restart
          6. 22.1.2.6. RESET MASTER
          7. 22.1.2.7. Change any remaining slaves to replicate off the new master
          8. 22.1.2.8. Slave promotion summary
        3. 22.1.3. Many read slaves
        4. 22.1.4. Master/master replication
        5. 22.1.5. Circular replication
      2. 22.2. SAN
      3. 22.3. DRBD
        1. 22.3.1. MySQL and DRBD setup
      4. 22.4. MySQL Proxy
        1. 22.4.1. Scaling read queries
        2. 22.4.2. Automated failover
        3. 22.4.3. Read/write splitting
        4. 22.4.4. Sharding
      5. 22.5. Linux-HA Heartbeat
      6. 22.6. MySQL Cluster
      7. 22.7. Connection Pooling
      8. 22.8. memcached
      9. 22.9. Summary
    6. A. MySQL Proxy
      1. A.1. Understanding MySQL Proxy
        1. A.1.1. Installing mysql-proxy
        2. A.1.2. Proxy backends
        3. A.1.3. Scripting
        4. A.1.4. Running MySQL proxy
      2. A.2. Learning Lua Basics
        1. A.2.1. Basic script functionality
        2. A.2.2. The proxy tokenizer
      3. A.3. Changing the Query Backend
      4. A.4. Changing and Injecting Queries
      5. A.5. Understanding MySQL Proxy Internals
        1. A.5.1. MySQL proxy tokens
      6. A.6. Summary
    7. B. Functions and Operators
      1. B.1. Using Aggregation Functions
      2. B.2. Using Bitwise Operators
        1. B.2.1. Combining multiple statements
      3. B.3. Compressing and Encrypting Data
        1. B.3.1. Cryptographic
      4. B.4. Testing, Logic, and Control Flow
        1. B.4.1. Control flow
        2. B.4.2. Logical
      5. B.5. Using Server-Level Functions
        1. B.5.1. Keyword locking
      6. B.6. Working with Data Types
        1. B.6.1. Mathematical functions and numbers
        2. B.6.2. Date and time functions
        3. B.6.3. String functions on TEXT types
          1. B.6.3.1. Character sets and collations
          2. B.6.3.2. Coercibility
        4. B.6.4. Data type conversions
        5. B.6.5. Working with XML
    8. C. Resources
      1. C.1. Finding Paid Support
      2. C.2. Asking Questions
        1. C.2.1. MySQL forums
        2. C.2.2. MySQL lists
        3. C.2.3. Bug reports
      3. C.3. Sharing Solutions
      4. C.4. Getting Documentation
        1. C.4.1. Cheat sheets and quick references
      5. C.5. Learning More
        1. C.5.1. Articles
        2. C.5.2. Presentations
          1. C.5.2.1. User Group Meetings
          2. C.5.2.2. Conferences
        3. C.5.3. Training/certification

Product information

  • Title: MySQL® Administrator's Bible
  • Author(s):
  • Release date: May 2009
  • Publisher(s): Wiley
  • ISBN: 9780470416914