MySQL Admin Cookbook

Table of Contents

• Preface

• Chapter :1 Replication
  • Introduction
  • Setting up automatically updated slaves of a server based on a SQL dump
  • Setting up automatically updated slaves of a selection of tables based on a SQL dump
  • Setting up automatically updated slaves using data file copy
  • Sharing read load across multiple machines
  • Using replication to provide full-text indexing for InnoDB tables
  • Estimating network and slave I/O load
  • Limiting network and slave I/O load in heavy write scenarios using the blackhole storage engine
  • Setting up slaves via network streaming
  • Skipping problematic queries
  • Checking if servers are in sync
  • Avoiding duplicate server IDs
  • Setting up slaves to report custom information about themselves to the master

• Chapter :2 Indexing
  • Introduction
  • Adding indexes to tables
  • Adding a fulltext index
  • Creating a normalized text search column
  • Removing indexes from tables
  • Estimating InnoDB index space requirements
  • Using prefix primary keys
  • Choosing InnoDB primary key columns
  • Speeding up searches for (sub)domains
  • Finding duplicate indexes

• Chapter :3 Tools
  • Introduction
  • Transferring connection settings between different machines using a network share
  • Sorting MySQL GUI Tools' stored connections
  • Automatically creating stored connections
  • Adding custom graphs to MySQL Administrator
  • Displaying query results page by page and with scrolling using the MySQL command-line client
  • Extracting information from verbose output using the MySQL command-line client
  • Specifying a default pager
  • Using a custom prompt to distinguish connections
  • Encrypting a MySQL server connection with SSH
  • Creating an encrypted MySQL console via SSH
  • Using a PuTTY template connection for SSH secured connections

• Chapter :4 Backing Up and Restoring MySQL Data
  • Introduction
  • Using MySQL Administrator GUI Tool as a frontend for backups
  • Copying all data files to a backup location
  • Creating a SQL dump of all databases
  • Creating a SQL dump of specific databases
  • Creating a SQL dump of specific tables
  • Compressing SQL dumps on-the-fly
  • Rotating and purging binary logs
  • Using replication to perform backups without hurting a production system's performance
  • Restoring data from a dump to a previously backed-up state
  • Performing a point-in-time recovery using the binary logs

• Chapter :5 Managing Data
  • Introduction
  • Exporting data to a simple CSV file
  • Exporting data to a custom file format
  • Using stored procedures to export repeatedly
  • Importing data from a simple CSV file
  • Importing data from custom file formats
  • Inserting new data and updating data if it already exists
  • Inserting data based on existing database content
  • Deleting all data from large tables
  • Deleting all but a fragment of a large table's data
  • Deleting all data incrementally from large tables

• Chapter :6 Monitoring and Analyzing a MySQL Installation
  • Introduction
  • Checking free InnoDB tablespace
  • Establishing alerting mechanisms for low remaining tablespace by using triggers
  • Estimating tablespace requirements
  • Identifying and changing MySQL variables
  • Assessing the overall table count
  • Finding the biggest tables
  • Finding all columns with a certain name and/or type
  • Finding all tables referencing each other

• Chapter :7 Configuring MySQL
  • Introduction
  • Setting up a fixed InnoDB tablespace
  • Setting up an auto-extending InnoDB tablespace
  • Storing InnoDB data in one file per table
  • Decreasing InnoDB tablespace
  • Enabling and configuring binary logging
  • Configuring the InnoDB redo log
  • Understanding and configuring important MySQL and InnoDB timeout options
  • Adjusting table and database name letter case handling for better platform independence
  • Installing MySQL as a Windows service with custom options
  • Running multiple MySQL server instances in parallel on a Linux server
  • Preventing invalid date values from being stored in DATE or DATETIME columns

• Chapter :8 MySQL User Management
  • Introduction
  • Configuring MySQL Administrator to display global privileges and hosts
  • Defining an alternative user for administrative tasks
  • Disabling the default accounts
  • Creating a basic user
  • Creating an installation user
  • Creating a read-only account
  • Defining a specific user for backup
  • Defining a specific user for replication
  • Allowing access from specific hosts only
  • Regaining access to your database in case of lost account information
  • Avoiding plain text passwords in administrative scripts

• Chapter :9 Managing Schemas
  • Introduction
  • Adding new columns at specific positions
  • Defining a primary key for a table containing (non-unique) data
  • Allowing individual INSERT statements with "0" values in auto-incrementing columns
  • Globally allowing INSERT statements with "0" values in auto-incrementing columns
  • Choosing a suitable storage engine
  • Improving the performance of ALTER TABLE for InnoDB
  • Using a stored procedure to conditionally add columns or indexes
  • Improving query performance for InnoDB tables with BLOB columns
  • Identifying differences between two schemas
  • Comparing schema revisions using hash values

• Appendix : Good to Know
  • Introduction
  • Avoiding silent replication disruption on full master disk
  • Maximizing usable memory on 32-bit Windows
  • Using separate temporary directories for multiple MySQL servers on a single machine, preventing conflicts
  • Preventing mysqldump from failing with Error 2013
  • Non-availability of InnoDB may escape monitoring
  • Troubleshooting "Can't start server: Bind on TCP/IP port: No such file or directory" error
  • Choosing character sets
  • Understanding auto-increment values

Daniel Schneller

Udo Schwedt

If you are a beginning to intermediate MySQL administrator, picking up a copy of Schneller's and Schwedt's "MySQL Admin Cookbook" could be worth your while. Read More

This is a useful book with useful tips and some good recipes for the developer/administrator using MySQL in practical situations. Read more

Code Downloads

Download the code and support files for this book.

Submit Errata

Please let us know if you have found any errors not listed on this list by completing our errata submission form. Our editors will check them and add them to this list. Thank you.

Sample chapters

You can view our sample chapters and prefaces of this title on PacktLib or download sample chapters in PDF format.