MySQL Admin Cookbook -- Table of Contents -- MySQL Admin Cookbook -- Credits -- About the Authors -- About the Reviewers -- Preface -- What this book covers -- What you need for this book -- Who this book is for -- Conventions -- Reader feedback -- Customer support -- Errata -- Piracy -- Questions -- 1. Replication -- Introduction -- Statement Based Replication -- Filtering -- Setting up automatically updated slaves of a server based on a SQL dump -- Getting ready -- How to do it... -- How it works... -- See also -- Setting up automatically updated slaves of a selection of tables based on a SQL dump -- Getting ready -- How to do it... -- How it works... -- There's more... -- Setting up automatically updated slaves using data file copy -- Getting ready -- How to do it... -- How it works... -- There's more... -- Conserving data file by using LVM snapshots -- Backing up data using Percona xtrabackup -- Sharing read load across multiple machines -- Getting ready -- How to do it... -- How it works... -- There's more... -- Working with connection pools -- Working on other programming environments -- Considering efficiency while adding slaves -- Using replication to provide full-text indexing for InnoDB tables -- Getting ready -- How to do it... -- How it works... -- There's more... -- Setting up new slaves in this scenario -- See also -- Estimating network and slave I/O load -- Getting ready -- How to do it... -- How it works... -- There's more... -- Handling intermittent connectivity between master and slave -- Enabling compression with the slave_compressed_protocol option -- See also -- Limiting network and slave I/O load in heavy write scenarios using the blackhole storage engine -- Getting ready -- How to do it... -- How it works... -- Other storage engines than InnoDB -- Setting up slaves via network streaming -- Getting ready -- How to do it...

How it works... -- Temporary daemon -- Dumping master data -- Shutting down and compressing -- Transferring to the slave and uncompressing -- Adjusting slave configuration -- Connecting to the master -- Starting the slave -- Skipping problematic queries -- Getting ready -- How to do it... -- How it works... -- There's more... -- Checking if servers are in sync -- Getting ready -- How to do it... -- How it works... -- There's more... -- Avoiding duplicate server IDs -- Getting ready -- How to do it... -- How it works... -- There's more... -- Recognizing symptoms of duplicate server IDs -- Setting up slaves to report custom information about themselves to the master -- Getting ready -- How to do it... -- How it works... -- There's more... -- 2. Indexing -- Introduction -- Infinite storage, infinite expectations -- Speed by redundancy -- Storage engine differences -- MyISAM -- InnoDB -- Primary (clustered) indexes -- Secondary indexes -- General requirements for the recipes in this chapter -- Adding indexes to tables -- Getting ready -- How to do it... -- How it works... -- There's more... -- Using MySQL Query Browser to generate the SQL statements -- Prefix indexes -- Prefix primary keys -- See also -- Adding a fulltext index -- Getting ready -- How to do it... -- How it works... -- There's more... -- Case sensitivity -- Word length -- Stopwords -- Ignoring frequent words -- Query modes -- Sphinx -- See also -- Creating a normalized text search column -- Getting ready -- How to do it... -- How it works... -- There is more... -- Removing indexes from tables -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Estimating InnoDB index space requirements -- Getting ready... -- How to do it... -- How it works -- There's more... -- Considering actual data lengths in your estimate -- Minding character sets.

Using prefix primary keys -- Getting ready... -- How to do it... -- How it works... -- Choosing InnoDB primary key columns -- Getting ready -- How to do it... -- How it works... -- Uniqueness -- Immutability -- Key length -- Single column keys -- Clustered Index -- Speeding up searches for (sub)domains -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Finding duplicate indexes -- Getting ready -- How to do it... -- How it works -- There's more... -- 3. Tools -- Introduction -- Tools used in this recipe -- Platform differences -- MySQL GUI Tools config file locations -- Transferring connection settings between different machines using a network share -- Getting ready -- How to do it... -- How it works... -- There's more... -- Dealing with changes -- Sorting MySQL GUI Tools' stored connections -- Getting ready -- How to do it... -- How it works... -- There's more... -- Automatically creating stored connections -- Getting ready -- How to do it... -- How it works... -- There's more... -- Adding custom graphs to MySQL Administrator -- Getting ready -- How to do it... -- How it works... -- There's more... -- Displaying query results page by page and with scrolling using the MySQL command-line client -- Getting ready -- How to do it... -- How it works... -- Extracting information from verbose output using the MySQL command-line client -- Getting ready -- How to do it... -- How it works... -- There's more... -- Specifying a default pager -- Getting ready -- How to do it... -- How it works... -- There's more... -- Using a custom prompt to distinguish connections -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Encrypting a MySQL server connection with SSH -- Getting ready -- How to do it... -- How it works... -- There's more... -- Creating an encrypted MySQL console via SSH.

Getting ready -- How to do it... -- How it works... -- There's more... -- Using a PuTTY template connection for SSH secured connections -- Getting ready -- How to do it... -- How it works... -- There's more... -- 4. Backing Up and Restoring MySQL Data -- Introduction -- Using MySQL Administrator GUI Tool as a frontend for backups -- Getting ready -- How to do it... -- How it works... -- There's more... -- Scheduling backups -- Understanding and handling limitations of using MySQL Administrator for backups -- Exploring additional backup options -- See also -- Copying all data files to a backup location -- Getting ready -- How to do it... -- How it works... -- There's more... -- Understanding the restrictions of the file-based backup method -- Backing up using LVM snapshots -- Restoring data from a file-based backup -- See also -- Creating a SQL dump of all databases -- Getting ready -- How to do it... -- How it works... -- There's more... -- Preventing locking issues by using InnoDB storage engine -- Creating consistent dumps of InnoDB tables -- Preventing dump inconsistency across databases -- Including binary log position in the dump -- Performing consistent dumps for binary data -- Reducing performance impacts by using multiple disks -- See also -- Creating a SQL dump of specific databases -- Getting ready -- How to do it... -- How it works... -- There's more... -- Considering the side effects of automated backup -- Increasing performance by dumping in parallel -- See also -- Creating a SQL dump of specific tables -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Compressing SQL dumps on-the-fly -- Getting ready -- How to do it... -- How it works... -- There's more... -- Achieving better compression ratio -- Considering performance factors -- Considering data robustness and tool availability.

Achieving better compression with alternative tools -- See also -- Rotating and purging binary logs -- Getting ready -- How to do it... -- How it works... -- There's more... -- Rotating and purging binary logs on Linux systems -- Considering risks of data loss -- Ensuring sufficient disk space -- See also -- Using replication to perform backups without hurting a production system's performance -- Getting ready -- How to do it... -- How it works... -- See also -- Restoring data from a dump to a previously backed-up state -- Getting ready -- How to do it... -- How it works... -- There's more... -- Restoring compressed dumps -- Temporarily disabling binlogs to save time and space -- Increasing recovery performance by using parallel restore -- Restoring tables excluding potentially very large tables -- See also -- Performing a point-in-time recovery using the binary logs -- Getting ready -- How to do it... -- How it works... -- There's more... -- Restoring only a specific database -- Determining the exact location of a failure and restoring up to that point -- See also -- 5. Managing Data -- Introduction -- Exporting data to a simple CSV file -- Getting ready -- How to do it... -- How it works... -- There's more... -- Handling errors if the target file already exists -- Handling NULL values -- Handling line breaks -- Including headers -- Exporting data to a custom file format -- Getting ready -- How to do it... -- How it works... -- There's more... -- Using stored procedures to export repeatedly -- Getting ready -- How to do it... -- How it works... -- There's more... -- Importing data from a simple CSV file -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Importing data from custom file formats -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also.

Inserting new data and updating data if it already exists.