Free Sample
+ Collection

MySQL Admin Cookbook

Cookbook
Daniel Schneller, Udo Schwedt

99 great recipes for mastering MySQL configuration and administration
$29.99
$49.99
RRP $29.99
RRP $49.99
eBook
Print + eBook

Want this title & more?

$21.99 p/month

Subscribe to PacktLib

Enjoy full and instant access to over 2000 books and videos – you’ll find everything you need to stay ahead of the curve and make sure you can always get the job done.

Book Details

ISBN 139781847197962
Paperback376 pages

About This Book

  • 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

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.

Table of Contents

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

What You Will Learn

  • 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.

Authors

Read More