MySQL Admin Cookbook LITE: Configuration, Server Monitoring, Managing Users电子书

MySQL Admin Cookbook: LITE

Table of Contents

MySQL Admin Cookbook: LITE

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

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

Index