MySQL Admin Cookbook LITE: Replication and Indexing

By Daniel Schneller and Udo Schwedt

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

• Language: English
• Publisher: Packt Publishing
• Release date: May 19, 2011
• ISBN: 9781849516136

Book preview

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. Monitoring and Analyzing a MySQL Installation

Introduction

Checking free InnoDB tablespace

Getting ready

How to do it...

How it works...

There's more...

See also

Establishing alerting mechanisms for low remaining tablespace by using triggers

Getting ready

How to do it...

How it works...

There's more...

Enabling the MySQL scheduler

Improving configuration

See also

Estimating tablespace requirements

Getting ready

How to do it...

How it works...

There's more...

See also

Identifying and changing MySQL variables

Getting ready

How to do it...

How it works...

There's more...

Displaying more than one named variable at a time

Displaying global settings

See also

Assessing the overall table count

Getting ready

How to do it...

How it works...

See also

Finding the biggest tables

Getting ready

How to do it...

How it works...

See also

Finding all columns with a certain name and/or type

Getting ready

How to do it...

How it works...

There's more...

See also

Finding all tables referencing each other

Getting ready

How to do it...

How it works...

See also

2. Configuring MySQL

Introduction

Setting up a fixed InnoDB tablespace

Getting ready

How to do it...

How it works...

There's more...

Setting up an auto-extending InnoDB tablespace

Getting ready

How to do it...

How it works...

There's more...

Making an existing tablespace auto-extensible

Controlling the steps of tablespace extension

Limiting the size of an auto-extending tablespace

Adding a new auto-extending data file

Storing InnoDB data in one file per table

Getting ready

How to do it...

How it works...

Decreasing InnoDB tablespace

Getting ready

How to do it...

How it works...

There's more...

Enabling and configuring binary logging

Getting ready

How to do it...

How it works...

There's more...

Configuring the InnoDB redo log

Getting ready

How to do it...

What just happened...

There's more...

Understanding and configuring important MySQL and InnoDB timeout options

Getting ready

How to do it...

How it works...

Setting values for innodb_rollback_on_timeout / innodb_lock_wait_timeout

Setting values for interactive_timeout / wait_timeout

Setting values for net_read_timeout / net_write_timeout

Adjusting table and database name letter case handling for better platform independence

Getting ready

How to do it...

How it works...

See also...

Installing MySQL as a Windows service with custom options

Getting started

How to do it...

How it works...

There's more...

Running multiple MySQL server instances in parallel on a Linux server

Getting ready

How to do it...

How it works...

There's more...

Turning off AppArmor / SELinux for Linux distributions

Windows

Considering the alternative MySQL Sandbox project

See also

Preventing invalid date values from being stored in DATE or DATETIME columns

Getting ready

How to do it...

Getting ready

There's more...

Configuring SQL mode for the current session only

3. MySQL User Management

Introduction

Configuring MySQL Administrator to display global privileges and hosts

Getting ready

How to do it...

How it works...

Defining an alternative user for administrative tasks

Getting ready

How to do it...

How it works...

There's more...

See also

Disabling the default accounts

Getting ready

How to do it...

How it works...

Creating a basic user

Getting ready

How to do it...

How it works...

There's more...

See also

Creating an installation user

Getting ready

How to do it...

How it works...

There's more...

Creating the account without using MySQL Administrator

Permitting management of user rights

See also

Creating a read-only account

Getting ready

How to do it...

How it works...

There's more...

Creating the account without using MySQL Administrator

Allowing stored procedure calls

See also

Defining a specific user for backup

Getting ready

How to do it...

How it works...

There's more...

See also

Defining a specific user for replication

Getting ready

How to do it...

How it works...

There's more...

Allowing access from specific hosts only

Getting ready

How to do it...

How it works...

There's more...

Creating the account without using MySQL Administrator

Allowing access from a group of hosts

See also

Regaining access to your database in case of lost account information

Getting ready

How to do it...

How it works...

There's more...

Avoiding plain text passwords in administrative scripts

Getting ready

How to do it...

How it works...

Example of creating a user in a script without a plain text password

There's more...

Index

MySQL Admin Cookbook: LITE

---

MySQL Admin Cookbook: LITE

Copyright © 2011 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors, nor Packt Publishing and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

First published: April 2011

Production Reference: 1130411

Published by Packt Publishing Ltd. 32 Lincoln Road Olton Birmingham, B27 6PA, UK.

ISBN 978-1-849516-12-9

www.packtpub.com

Cover Image by Vinayak Chittar ( <[e-mail]> )

Credits

Authors

Daniel Schneller

Udo Schwedt

Reviewers

Kai Seidler

Marc Delisle

Acquisition Editor

Sarah Cullington

Technical Editor

Gauri Iyer

Indexer

Tejal Daruwale

Graphics

Geetanjali Sawant

Production Coordinator

Adline Swetha Jesuthas

Cover Work

Kruthika Bangera

About the Authors

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 http://www.danielschneller.com.

When I first was asked by Packt Publishing whether I would be interested in writing a book about MySQL on Christmas Eve 2008 little did I know how much work, stress, but also what a lot of fun I was headed for.

Now, that the book is finally done I would like to thank those people without whom getting it done would have been impossible.

First of all, I'd like to thank Udo for agreeing to be my co-author. Without him, this whole thing would have taken a lot longer and would have been not half as useful as I believe it has turned out now.

I would also like to thank the team at Packt Publishing—most importantly for noticing and reading my blog, consequently contacting me to get the whole thing started—but also for taking care of schedules, providing support, guidance and feedback, and keeping us on track the whole way.

Last, but by no means least, I want to thank Jenny—for encouraging me to write a book in the first place, and then making sure I never ran out of tea, cookies, or motivation on the countless evenings I spent sitting in front of the keyboard instead of with her. I dedicate this book to her.

Udo Schwedt has over ten years of experience in the IT industry as a professional Java developer and software architect. He 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.

He has been fascinated by computers since his childhood, and taught himself the basics of programming during his school years. After graduating from school, he began his studies at the RWTH Aachen, Germany, which he finished with a summa cum laude degree in computer science, minoring in psychology with a focus on software ergonomics.

Udo started his career