MySQL Admin Cookbook

MySQL Admin Cookbook

By : Daniel Schneller, Udo Schwedt

Buy this Book

MySQL Admin Cookbook

By: Daniel Schneller, Udo Schwedt

Buy this Book

Overview of this book

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.

MySQL Admin Cookbook

Credits

About the Authors

About the Reviewers

Preface

Free Chapter

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

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

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

Introduction

Avoiding silent replication disruption on full master disk

Maximizing usable memory on 32-bit Windows

Using separate temporary directories for multiple MySQL servers on a single machine, preventing conflicts

Preventing mysqldump from failing with Error 2013

Non-availability of InnoDB may escape monitoring

Troubleshooting "Can't start server: Bind on TCP/IP port: No such file or directory" error

Choosing character sets

Understanding auto-increment values

Index

Customer Reviews

5 star

4 star

3 star

2 star

1 star

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 as a professional C, C++, and Java developer in a software company that delivers leading solutions in the financial online transaction processing sector. In 2003, he joined his current employer as a Java framework developer for a large-scale international project, where he met Daniel. In the course of the project, he gained extensive experience in using MySQL in a professional context.

For both Daniel and Udo, the common project involved the design and implementation of a database infrastructure solution for a Java-based merchandise management software system with tens of thousands of clients. The evaluation of different database systems and the realization of the infrastructure made it necessary for them to delve into MySQL beyond the typical utilization scenarios. The resulting decentralized multi-platform environment based on more than 500 decentralized MySQL server instances with more than 5,500 replication slaves bears challenges not covered by the standard MySQL documentation.

MySQL Admin Cookbook

By : Daniel Schneller, Udo Schwedt

MySQL Admin Cookbook

By: Daniel Schneller, Udo Schwedt

Overview of this book

Related Content you might be interested in

Current Title:

MySQL Admin Cookbook

About the Authors