PacktLib: MySQL Admin Cookbook

MySQL Admin Cookbook

Credits

About the Authors

About the Reviewers

Preface

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

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

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

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

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

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

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

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

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

Good to Know