High availability MySQL cookbook : over 50 simple but incredibly effective recipes focusing on different methods of achieving high availability for MySQL databases

Cover......Page 1 Copyright......Page 3 Credits......Page 4 About the Author......Page 5 About the Reviewers......Page 6 Table of Contents......Page 8 Preface......Page 12 Introduction......Page 18 Designing a MySQL Cluster......Page 19 Creating an initial cluster configuration file—config.ini......Page 28 Installing a management node......Page 31 Starting a management node......Page 34 Installing and starting storage nodes......Page 37 Installing and starting SQL nodes......Page 43 Creating a MySQL Cluster table......Page 46 Restarting a MySQL Cluster without downtime......Page 49 Recovering from a cluster shutdown......Page 53 Introduction......Page 58 Importing SQL files to a MySQL server and converting them to MySQL Cluster......Page 60 Taking an online backup of a MySQL Cluster......Page 65 Restoring from a MySQL Cluster online backup......Page 70 Restricting write access to a MySQL Cluster with single-user mode......Page 75 Taking an offline backup with MySQL Cluster......Page 80 Introduction......Page 86 Configuring multiple management nodes......Page 87 Obtaining usage information......Page 91 Adding storage nodes online......Page 95 Replicating between MySQL Clusters......Page 102 a backup channel......Page 108 User-defined partitioning......Page 111 Disk-based tables......Page 115 Calculating DataMemory and IndexMemory......Page 120 Introduction......Page 126 Single storage node failure......Page 127 Multiple storage node failures......Page 131 Storage node partitioning and arbitration......Page 134 Debugging MySQL Clusters......Page 137 Seeking help......Page 141 NIC teaming with MySQL Cluster......Page 142 Introduction......Page 150 Designing a replication setup......Page 151 Configuring a replication master......Page 157 without syncing data......Page 161 migrating data with a simple SQL dump......Page 163 on a master when bringing a slave online......Page 164 Replication safety tricks......Page 167 initial installation......Page 169 installing the MySQL nodes......Page 173 installing monitoring node......Page 177 Multi Master Replication Manager (MMM)......Page 180 Introduction......Page 186 Preparing a Linux server for shared storage......Page 187 Configuring two servers for shared storage MySQL......Page 192 Configuring MySQL on shared storage with Conga......Page 196 Fencing for high availability......Page 202 Configuring MySQL with GFS......Page 206 Introduction......Page 212 Installing DRBD on two Linux servers......Page 214 Manually moving services within a DRBD cluster......Page 219 Using heartbeat for automatic failover......Page 222 Introduction......Page 230 Tuning the Linux kernel IO......Page 231 Tuning MySQL Cluster storage nodes......Page 239 Tuning MySQL Cluster SQL nodes......Page 241 Tuning queries within a MySQL Cluster......Page 243 Tuning GFS on shared storage......Page 244 MySQL Replication tuning......Page 246 Appendix A: Base Installation......Page 250 Appendix B: LVM and MySQL......Page 254 Single-site architectures......Page 262 Multi-site architectures......Page 264 Summary of options......Page 265 Index......Page 266

In Detail

High Availability is something that all web sites hope to achieve, especially those that are linked to big companies.

MySQL, an open source relational database management system (RDBMS), can be made highly available to protect from corruption, hardware failure, software crashes, and user error. Running a MySQL setup is quite simple. Things start getting complex when you start thinking about the best way to provide redundancy. There are a large number of techniques available to add 'redundancy' and 'high availability' to MySQL, but most are both poorly understood and documented.

This book will provide you with recipes showing how to design, implement, and manage a MySQL Cluster and achieve high availability using MySQL replication, block level replication, shared storage, and the open source Global File System (GFS).

This book covers all the major techniques available for increasing availability of your MySQL databases. It demonstrates how to design, implement, troubleshoot and manage a highly available MySQL setup using any one of several techniques, which are shown in different recipes. It is based on MySQL Cluster 7.0, MySQL (for non clustered recipes) 5.0.77, and CentOS / RedHat Enterprise Linux 5.3.

The book starts by introducing MySQL Cluster as a technology and explaining how to set up a simple cluster. It will help you to master the options available for backing up and restoring a file in the MySQL Cluster. By following the practical examples in this book, you will learn how to manage the MySQL Cluster. Further, we will discuss some troubleshooting aspects of the MySQL Cluster.

We also have a look at achieving high availability for MySQL databases with the techniques of MySQL Replication, block level replication, shared storage (a SAN or NAS), and DRBD.

Finally, you will learn the principles of Performance tuning and tune MySQL database for optimal performance.

A cookbook containing recipes for all the common high availability techniques for database administrators to tackle real-world challenges to achieve high availability using MySQL

Approach

This book uses the approach of a cookbook. Each recipe provides the reader with easy step-by-step descriptions of the actions necessary to accomplish a specific task. It is designed to present what often appear as extremely complicated techniques as a series of simple-to-follow recipes, allowing readers to achieve high uptimes on their MySQL servers.

Who this book is for

This book is targeted at system administrators or database administrators who have basic familiarity with Linux, the shell, and MySQL. The typical user will be able to get MySQL installed and working, but needs practical guidance to make it highly available.

Annotation High Availability is something that all web sites hope to achieve, especially those that are linked to big companies. MySQL, an open source relational database management system (RDBMS), can be made highly available to protect from corruption, hardware failure, software crashes, and user error. Running a MySQL setup is quite simple. Things start getting complex when you start thinking about the best way to provide redundancy. There are a large number of techniques available to add 'redundancy' and 'high availability' to MySQL, but most are both poorly understood and documented. This book will provide you with recipes showing how to design, implement, and manage a MySQL Cluster and achieve high availability using MySQL replication, block level replication, shared storage, and the open source Global File System (GFS). This book covers all the major techniques available for increasing availability of your MySQL databases. It demonstrates how to design, implement, troubleshoot and manage a highly available MySQL setup using any one of several techniques, which are shown in different recipes. It is based on MySQL Cluster 7.0, MySQL (for non clustered recipes) 5.0.77, and CentOS / RedHat Enterprise Linux 5.3. The book starts by introducing MySQL Cluster as a technology and explaining how to set up a simple cluster. It will help you to master the options available for backing up and restoring a file in the MySQL Cluster. By following the practical examples in this book, you will learn how to manage the MySQL Cluster. Further, we will discuss some troubleshooting aspects of the MySQL Cluster. We also have a look at achieving high availability for MySQL databases with the techniques of MySQL Replication, block level replication, shared storage (a SAN or NAS), and DRBD. Finally, you will learn the principles of Performance tuning and tune MySQL database for optimal performance. Over 60 simple but incredibly effective recipes focusing on different methods of achieving high availability for MySQL database