MySQL Admin Cookbook

MySQL Admin Cookbook
eBook: $29.99
Formats: PDF, PacktLib, ePub and Mobi formats
save 15%!
Print + free eBook + free PacktLib access to the book: $79.98    Print cover: $49.99
save 37%!
Free Shipping!
UK, US, Europe and selected countries in Asia.
Also available on:
Table of Contents
Sample Chapters
  • 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

Book Details

Language : English
Paperback : 376 pages [ 235mm x 191mm ]
Release Date : March 2010
ISBN : 1847197965
ISBN 13 : 9781847197962
Author(s) : Daniel Schneller, Udo Schwedt
Topics and Technologies : All Books, Big Data and Business Intelligence, Cookbooks, MySQL, Open Source

Table of Contents

Chapter :1 Replication
Chapter :2 Indexing
Chapter :3 Tools
Chapter :4 Backing Up and Restoring MySQL Data
Chapter :5 Managing Data
Chapter :6 Monitoring and Analyzing a MySQL Installation
Chapter :7 Configuring MySQL
Chapter :8 MySQL User Management
Chapter :9 Managing Schemas
Appendix : Good to Know
  • 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

Daniel Schneller works as a software developer, database administrator, and general IT professional for an independent software vendor in the retail sector. After successfully graduating from the University of Cooperative Education in Heidenheim, Germany with a degree in Business Computer Science, he started his career as a professional software developer, focused on the Microsoft technology stack. In 2002 he started focusing on enterprise-level Java development and has since gained extensive knowledge and experience implementing large scale systems based on Java EE and relational databases, especially MySQL since version 4.0.

Currently he is mostly involved with the ongoing development of framework-level functionality, including customization and extension of an ORM-based persistence layer. He is involved in different open source projects such as FindBugs, Eclipse, and Checkstyle and infrequently blogs about Java, MySQL, Windows, Linux and other insanities at

Udo Schwedt

Udo Schwedt is head of the Java architecture team and deputy head of the Java development department at the IT service provider for Germany's market leader in the Do-It-Yourself sector. After finishing his studies at RWTH Aachen, Germany with a degree in Computer Science, he started his career as a professional Java developer in a leading software company in the banking sector. In 2003, he joined his current company as a framework developer, subsequently taking the role of a lead architect.

Both authors are involved in a large scale international software project, which encompasses development of a Java-based merchandise management software solution. This decentralized multi-platform environment is based on more than 500 decentralized MySQL server instances with more than 5,500 replication slaves. Daniel and Udo are responsible for configuration and management of this infrastructure.

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.

Frequently bought together

MySQL Admin Cookbook +    Oracle Application Express 3.2 - The Essentials and More =
50% Off
the second eBook
Price for both: £29.05

Buy both these recommended eBooks together and get 50% off the cheapest eBook.

What you will learn from this book

  • Set up MySQL replication to manage load balancing, online back up, and fail-over scenarios
  • Create, modify, and delete indexes; also identify duplicate indexes to optimize your MySQL servers' performance
  • Leverage the power of MySQL Administrator, Query Browser GUI Tools, and the MySQL command line client
  • Back up your database and restore the data efficiently and reliably with advanced techniques such as on-the-fly compression, point-in-time recovery, avoiding extended lock situations, backup in replication scenarios, and partial backup and restore.
  • Manage data by deleting data in a highly efficient manner, inserting data based on existing database content, and importing and exporting data in a customizable manner
  • Monitor table space usage and use database metadata to your advantage
  • Deal with typical performance bottlenecks and lock-contention problems
  • Configure MySQL and leverage available settings to their full potential
  • Manage MySQL user accounts and assign appropriate privileges
  • Restrict access sensibly and regain access to your database in case of loss of administrative user credentials
  • Manage the schema by adding and removing columns from tables, choosing a suitable storage engine and character set for individual needs
  • Add a new primary key column to a table already filled with data and also manage and automate database schema evolution in the context of application updates

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


This book uses the approach of a cookbook, presenting solutions to problems in the form of recipes. Each recipe provides the reader with easy step-by-step descriptions of the actions necessary to accomplish a specific task. Example values and code samples are used throughout the recipes, which makes adaptation for individual needs easy

Administrators of all skill-levels will find step-by-step instructions for practical approaches to MySQL configuration, maintenance, and optimization topics. Each recipe uses a concrete example for easy understanding.

Who this book is for

This book is for ambitious MySQL users as well as professional data center database administrators. Beginners as well as experienced administrators will profit from this cookbook and get fresh ideas to improve their MySQL environments. Detailed background information will enable them to widen their MySQL horizon.

It does not cover SQL basics, how to install MySQL servers, or how to design a relational database schema. Readers are expected to have a basic understanding of the SQL language and database concepts in general.

Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software