MySQL Admin Cookbook LITE: Replication and Indexing
Make your database quicker, more efficient, and better organized with replication and indexing
1st Edition
Published on 3. February 2025
104 pages
978-1-84951-615-0 (ISBN)
Description
Key FeaturesBook DescriptionThis cookbook presents 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. This book is for ambitious MySQL users as well as professional data center database administrators. Beginners as well as experienced administrators will benefit 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.What you will learnWho this book is for
More details
Other editions
Additional editions
Book
04/2011
Packt Publishing
€26.60
Shipment within 3-4 weeks
Content
- Intro
- MySQL Admin Cookbook: LITE
- 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...
