You are previewing MySQL Admin Cookbook.
O'Reilly logo
MySQL Admin Cookbook

Book Description

99 great recipes for mastering MySQL configuration and administration

  • Set up MySQL to perform administrative tasks such as efficiently managing data and database schema, improving the performance of MySQL servers, and managing user credentials

  • Deal with typical performance bottlenecks and lock-contention problems

  • Restrict access sensibly and regain access to your database in case of loss of administrative user credentials

  • Part of Packt's Cookbook series: Each recipe is a carefully organized sequence of instructions to complete the task as efficiently as possible

In Detail

MySQL is the most popular open-source database and is also known for its easy set up feature. However, proper configuration beyond the default settings still is a challenge, along with some other day-to-day maintenance tasks such as backing up and restoring, performance tuning, and server monitoring. These tasks have not been covered thoroughly in the default documentation.

This book provides both step-by-step recipes and relevant background information on these topics and more. It covers everything from basic to advanced aspects of MySQL administration and configuration. One of the things you are really going to love about this book is that all recipes are based on real-world experience and were derived from proven solutions used in an enterprise environment.

This book shows you everything you need to know about MySQL Administration. You will learn to set up MySQL replication to manage load balancing and deal with online backup and fail-over scenarios. As you consider the benefits of backing up, you might like to back up your database efficiently with advanced techniques covered in this book.

The book demonstrates how to create, modify, and delete indexes. You will also learn to identify duplicate indexes, which hinder your MySQL server performance. This book focuses on administration tasks and will help you as an administrator to optimize the database for efficiency and reliability.

You will learn to manage data efficiently by inserting data in existing database content and importing and exporting databases. The sooner you learn about taking advantage of metadata from this book, the sooner you can start using the space efficiently. Get to know about managing users and assigning privileges and regaining lost administrative user credentials. Finally, learn to manage the database schema by customizing it to automate database schema evolution in the context of application updates.

A quick step-by-step guide for MySQL users and database administrators to tackle real-world challenges with MySQL configuration and administration

Table of Contents

  1. MySQL Admin Cookbook
    1. MySQL Admin Cookbook
    2. Credits
    3. About the Authors
    4. About the Reviewers
    5. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
      6. Customer support
        1. Errata
        2. Piracy
        3. Questions
    6. 1. Replication
      1. Introduction
        1. Statement Based Replication
          1. Filtering
      2. Setting up automatically updated slaves of a server based on a SQL dump
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. See also
      3. Setting up automatically updated slaves of a selection of tables based on a SQL dump
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      4. Setting up automatically updated slaves using data file copy
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Conserving data file by using LVM snapshots
          2. Backing up data using Percona xtrabackup
      5. Sharing read load across multiple machines
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Working with connection pools
          2. Working on other programming environments
          3. Considering efficiency while adding slaves
      6. Using replication to provide full-text indexing for InnoDB tables
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Setting up new slaves in this scenario
        5. See also
      7. Estimating network and slave I/O load
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Handling intermittent connectivity between master and slave
          2. Enabling compression with the slave_compressed_protocol option
        5. See also
      8. Limiting network and slave I/O load in heavy write scenarios using the blackhole storage engine
        1. Getting ready
        2. How to do it...
          1. How it works...
            1. Other storage engines than InnoDB
      9. Setting up slaves via network streaming
        1. Getting ready
        2. How to do it...
        3. How it works...
          1. Temporary daemon
          2. Dumping master data
          3. Shutting down and compressing
          4. Transferring to the slave and uncompressing
          5. Adjusting slave configuration
          6. Connecting to the master
          7. Starting the slave
      10. Skipping problematic queries
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      11. Checking if servers are in sync
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      12. Avoiding duplicate server IDs
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Recognizing symptoms of duplicate server IDs
      13. Setting up slaves to report custom information about themselves to the master
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
    7. 2. Indexing
      1. Introduction
        1. Infinite storage, infinite expectations
        2. Speed by redundancy
        3. Storage engine differences
        4. MyISAM
        5. InnoDB
          1. Primary (clustered) indexes
          2. Secondary indexes
        6. General requirements for the recipes in this chapter
      2. Adding indexes to tables
        1. Getting ready
        2. How to do it...
          1. How it works...
          2. There's more...
            1. Using MySQL Query Browser to generate the SQL statements
            2. Prefix indexes
            3. Prefix primary keys
          3. See also
      3. Adding a fulltext index
        1. Getting ready
        2. How to do it...
          1. How it works...
          2. There's more...
            1. Case sensitivity
            2. Word length
            3. Stopwords
            4. Ignoring frequent words
            5. Query modes
            6. Sphinx
          3. See also
      4. Creating a normalized text search column
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There is more...
      5. Removing indexes from tables
        1. Getting ready
        2. How to do it...
          1. How it works...
          2. There's more...
          3. See also
      6. Estimating InnoDB index space requirements
        1. Getting ready...
        2. How to do it...
        3. How it works
        4. There's more...
          1. Considering actual data lengths in your estimate
          2. Minding character sets
      7. Using prefix primary keys
        1. Getting ready...
        2. How to do it...
          1. How it works...
      8. Choosing InnoDB primary key columns
        1. Getting ready
        2. How to do it...
        3. How it works...
          1. Uniqueness
          2. Immutability
          3. Key length
          4. Single column keys
          5. Clustered Index
      9. Speeding up searches for (sub)domains
        1. Getting ready
        2. How to do it...
          1. How it works...
          2. There's more...
          3. See also
      10. Finding duplicate indexes
        1. Getting ready
        2. How to do it...
        3. How it works
        4. There's more...
    8. 3. Tools
      1. Introduction
        1. Tools used in this recipe
          1. Platform differences
          2. MySQL GUI Tools config file locations
      2. Transferring connection settings between different machines using a network share
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Dealing with changes
      3. Sorting MySQL GUI Tools' stored connections
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      4. Automatically creating stored connections
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      5. Adding custom graphs to MySQL Administrator
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      6. Displaying query results page by page and with scrolling using the MySQL command-line client
        1. Getting ready
        2. How to do it...
        3. How it works...
      7. Extracting information from verbose output using the MySQL command-line client
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      8. Specifying a default pager
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      9. Using a custom prompt to distinguish connections
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      10. Encrypting a MySQL server connection with SSH
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      11. Creating an encrypted MySQL console via SSH
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      12. Using a PuTTY template connection for SSH secured connections
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
    9. 4. Backing Up and Restoring MySQL Data
      1. Introduction
      2. Using MySQL Administrator GUI Tool as a frontend for backups
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Scheduling backups
          2. Understanding and handling limitations of using MySQL Administrator for backups
          3. Exploring additional backup options
        5. See also
      3. Copying all data files to a backup location
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Understanding the restrictions of the file-based backup method
          2. Backing up using LVM snapshots
          3. Restoring data from a file-based backup
        5. See also
      4. Creating a SQL dump of all databases
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Preventing locking issues by using InnoDB storage engine
          2. Creating consistent dumps of InnoDB tables
          3. Preventing dump inconsistency across databases
          4. Including binary log position in the dump
          5. Performing consistent dumps for binary data
          6. Reducing performance impacts by using multiple disks
        5. See also
      5. Creating a SQL dump of specific databases
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Considering the side effects of automated backup
          2. Increasing performance by dumping in parallel
        5. See also
      6. Creating a SQL dump of specific tables
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      7. Compressing SQL dumps on-the-fly
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Achieving better compression ratio
          2. Considering performance factors
          3. Considering data robustness and tool availability
          4. Achieving better compression with alternative tools
        5. See also
      8. Rotating and purging binary logs
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Rotating and purging binary logs on Linux systems
          2. Considering risks of data loss
          3. Ensuring sufficient disk space
        5. See also
      9. Using replication to perform backups without hurting a production system's performance
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. See also
      10. Restoring data from a dump to a previously backed-up state
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Restoring compressed dumps
          2. Temporarily disabling binlogs to save time and space
          3. Increasing recovery performance by using parallel restore
          4. Restoring tables excluding potentially very large tables
        5. See also
      11. Performing a point-in-time recovery using the binary logs
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Restoring only a specific database
          2. Determining the exact location of a failure and restoring up to that point
        5. See also
    10. 5. Managing Data
      1. Introduction
      2. Exporting data to a simple CSV file
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Handling errors if the target file already exists
          2. Handling NULL values
          3. Handling line breaks
          4. Including headers
      3. Exporting data to a custom file format
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      4. Using stored procedures to export repeatedly
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      5. Importing data from a simple CSV file
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      6. Importing data from custom file formats
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      7. Inserting new data and updating data if it already exists
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      8. Inserting data based on existing database content
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      9. Deleting all data from large tables
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Temporarily disabling Foreign key constraints
      10. Deleting all but a fragment of a large table's data
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Performance considerations
      11. Deleting all data incrementally from large tables
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
    11. 6. Monitoring and Analyzing a MySQL Installation
      1. Introduction
      2. Checking free InnoDB tablespace
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      3. Establishing alerting mechanisms for low remaining tablespace by using triggers
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Enabling the MySQL scheduler
          2. Improving configuration
        5. See also
      4. Estimating tablespace requirements
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      5. Identifying and changing MySQL variables
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Displaying more than one named variable at a time
          2. Displaying global settings
        5. See also
      6. Assessing the overall table count
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. See also
      7. Finding the biggest tables
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. See also
      8. Finding all columns with a certain name and/or type
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      9. Finding all tables referencing each other
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. See also
    12. 7. Configuring MySQL
      1. Introduction
      2. Setting up a fixed InnoDB tablespace
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      3. Setting up an auto-extending InnoDB tablespace
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Making an existing tablespace auto-extensible
          2. Controlling the steps of tablespace extension
          3. Limiting the size of an auto-extending tablespace
          4. Adding a new auto-extending data file
        5. See also
      4. Storing InnoDB data in one file per table
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. See also
      5. Decreasing InnoDB tablespace
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      6. Enabling and configuring binary logging
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      7. Configuring the InnoDB redo log
        1. Getting ready
        2. How to do it...
        3. What just happened...
        4. There's more...
      8. Understanding and configuring important MySQL and InnoDB timeout options
        1. Getting ready
        2. How to do it...
        3. How it works...
          1. Setting values for innodb_rollback_on_timeout / innodb_lock_wait_timeout
          2. Setting values for interactive_timeout / wait_timeout
          3. Setting values for net_read_timeout / net_write_timeout
      9. Adjusting table and database name letter case handling for better platform independence
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. See also...
      10. Installing MySQL as a Windows service with custom options
        1. Getting started
        2. How to do it...
        3. How it works...
        4. There's more...
      11. Running multiple MySQL server instances in parallel on a Linux server
        1. Getting ready
        2. How to do it...
          1. How it works...
          2. There's more...
            1. Turning off AppArmor / SELinux for Linux distributions
            2. Windows
            3. Considering the alternative MySQL Sandbox project
          3. See also
        3. Preventing invalid date values from being stored in DATE or DATETIME columns
          1. Getting ready
          2. How to do it...
            1. Getting ready
            2. There's more...
              1. Configuring SQL mode for the current session only
    13. 8. MySQL User Management
      1. Introduction
      2. Configuring MySQL Administrator to display global privileges and hosts
        1. Getting ready
        2. How to do it...
        3. How it works...
      3. Defining an alternative user for administrative tasks
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      4. Disabling the default accounts
        1. Getting ready
        2. How to do it...
          1. How it works...
          2. See also
      5. Creating a basic user
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      6. Creating an installation user
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Creating the account without using MySQL Administrator
          2. Permitting management of user rights
        5. See also
      7. Creating a read-only account
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Creating the account without using MySQL Administrator
          2. Allowing stored procedure calls
        5. See also
      8. Defining a specific user for backup
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      9. Defining a specific user for replication
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      10. Allowing access from specific hosts only
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Creating the account without using MySQL Administrator
          2. Allowing access from a group of hosts
        5. See also
      11. Regaining access to your database in case of lost account information
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      12. Avoiding plain text passwords in administrative scripts
        1. Getting ready
        2. How to do it...
        3. How it works...
          1. Example of creating a user in a script without a plain text password
        4. There's more...
    14. 9. Managing Schemas
      1. Introduction
      2. Adding new columns at specific positions
        1. Getting ready
        2. How to do it...
          1. How it works...
          2. There's more...
          3. See also
      3. Defining a primary key for a table containing (non-unique) data
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      4. Allowing individual INSERT statements with "0" values in auto-incrementing columns
        1. Getting ready
        2. How to do it...
          1. How it works...
      5. Globally allowing INSERT statements with "0" values in auto-incrementing columns
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. See also
      6. Choosing a suitable storage engine
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Keeping a watch on silent engine substitution
      7. Improving the performance of ALTER TABLE for InnoDB
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      8. Using a stored procedure to conditionally add columns or indexes
        1. Getting ready...
        2. How to do it...
        3. How it works...
        4. There's more...
      9. Improving query performance for InnoDB tables with BLOB columns
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      10. Identifying differences between two schemas
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also...
      11. Comparing schema revisions using hash values
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also...
    15. A. Good to Know
      1. Introduction
      2. Avoiding silent replication disruption on full master disk
      3. Maximizing usable memory on 32-bit Windows
        1. Limitations of 32-bit systems
        2. Impact on MySQL/InnoDB
        3. Getting even more with the /3GB switch
      4. Using separate temporary directories for multiple MySQL servers on a single machine, preventing conflicts
      5. Preventing mysqldump from failing with Error 2013
        1. Diagnosing the symptoms
        2. Finding the cause
        3. Preventing the problem
      6. Non-availability of InnoDB may escape monitoring
      7. Troubleshooting "Can't start server: Bind on TCP/IP port: No such file or directory" error
      8. Choosing character sets
        1. Text around the world
        2. Character sets
        3. Defaults
        4. Multiple levels of configuration
        5. Getting ready...
        6. How to do it...
          1. Determining required languages
          2. Choosing from Unicode character sets
          3. Deciding on a Unicode character set
          4. Considering conversion needs between server and clients
      9. Understanding auto-increment values
        1. Getting ready...
        2. How to do it...
          1. What just happened...
          2. There's more...