Book Image

MySQL Admin Cookbook

By : Daniel Schneller, Udo Schwedt
Book Image

MySQL Admin Cookbook

By: Daniel Schneller, Udo Schwedt

Overview of this book

<p>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.<br /><br />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.<br /><br />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.<br /><br />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.<br /><br />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.</p>
Table of Contents (16 chapters)
MySQL Admin Cookbook
Credits
About the Authors
About the Reviewers
Preface
Index

Avoiding silent replication disruption on full master disk


While using replication, you might experience corrupted or incomplete binlog files on the master when the disk they get stored on becomes full. In older versions, the file would be started and when the disk became full in the middle of an event being written, this partial data would be replicated and cause errors on the slaves.

Versions 5.0 and up handle an out-of-space situation more gracefully, as they do not write partial statements to the binlog and try harder to keep the table data and the binlog in sync. However, you still need to be aware that there is no 100-percent sure way of preventing problems on the slaves because under certain circumstances you can end up with the last statement having executed in the database, but not recorded in the binlog.

The master server will log this fact into its logfile, so your monitoring system might pick this up with lines like:

The binary log <name> is shorter than its expected size. 

but still the problem remains.

The MySQL manual has more details about this in chapters 5.2.4 at http://dev.mysql.com/doc/refman/5.1/en/binary-log.html and B.1.4.3 on disk-full problems at http://dev.mysql.com/doc/refman/5.1/en/full-disk.html.

Note

The bottom line about this, however, is that even with the most conservative settings—--sync_binlog=1 and --innodb_support_xa=1, leading to reduced performance due to more disk syncs—you can still end up with incomplete binlogs, be it on MySQL's part or the operating system's, requiring you to reset the replication and manually re-sync the slaves from a fresh dump.

Considering the performance penalties, the options MySQL offers to limit the risks of damaging the binlogs and the fact that they do not guarantee problem-free operations anyway, we recommend investing your resources in a reliable system-monitoring solution that will keep you informed about critical conditions regarding disk space and allow you to prevent these problems in the first place.