High Availability MySQL Cookbook
eBook - ePub

High Availability MySQL Cookbook

Alex Davies

Share book
  1. 264 pages
  2. English
  3. ePUB (mobile friendly)
  4. Available on iOS & Android
eBook - ePub

High Availability MySQL Cookbook

Alex Davies

Book details
Book preview
Table of contents
Citations

About This Book

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.

Frequently asked questions

How do I cancel my subscription?
Simply head over to the account section in settings and click on “Cancel Subscription” - it’s as simple as that. After you cancel, your membership will stay active for the remainder of the time you’ve paid for. Learn more here.
Can/how do I download books?
At the moment all of our mobile-responsive ePub books are available to download via the app. Most of our PDFs are also available to download and we're working on making the final remaining ones downloadable now. Learn more here.
What is the difference between the pricing plans?
Both plans give you full access to the library and all of Perlego’s features. The only differences are the price and subscription period: With the annual plan you’ll save around 30% compared to 12 months on the monthly plan.
What is Perlego?
We are an online textbook subscription service, where you can get access to an entire online library for less than the price of a single book per month. With over 1 million books across 1000+ topics, we’ve got you covered! Learn more here.
Do you support text-to-speech?
Look out for the read-aloud symbol on your next book to see if you can listen to it. The read-aloud tool reads text aloud for you, highlighting the text as it is being read. You can pause it, speed it up and slow it down. Learn more here.
Is High Availability MySQL Cookbook an online PDF/ePUB?
Yes, you can access High Availability MySQL Cookbook by Alex Davies in PDF and/or ePUB format, as well as other popular books in Computer Science & Programming in SQL. We have over one million books available in our catalogue for you to explore.

Information

Year
2010
ISBN
9781847199942
Edition
1

High Availability MySQL Cookbook

Alex Davies


Table of Contents

High Availability MySQL Cookbook
Credits
About the Author
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. High Availability with MySQL Cluster
Introduction
Designing a MySQL Cluster
How to do it...
How it works…
There's more…
Creating an initial cluster configuration file—config.ini
How to do it…
There's more…
Installing a management node
How to do it…
Starting a management node
How to do it…
There's more…
Installing and starting storage nodes
Getting ready
How to do it…
There's more…
Installing and starting SQL nodes
How to do it…
Creating a MySQL Cluster table
How to do it…
Restarting a MySQL Cluster without downtime
Getting started
How to do it…
Recovering from a cluster shutdown
How to do it…
How it works…
There's more…
2. MySQL Cluster Backup and Recovery
Introduction
Importing SQL files to a MySQL server and converting them to MySQL Cluster
How to do it…
There's more…
Taking an online backup of a MySQL Cluster
Getting ready
How to do it…
There's more…
Preventing commands hanging
Aborting backups in progress
Defining an exact time for a consistent backup
Restoring from a MySQL Cluster online backup
How to do it…
Restricting write access to a MySQL Cluster with single-user mode
How to do it…
There's more…
Taking an offline backup with MySQL Cluster
How to do it…
There's more…
Importing a cluster SQL file to an unclustered MySQL Server
Running mysqldump for a consistent backup
3. MySQL Cluster Management
Introduction
Configuring multiple management nodes
Getting ready
How to do it...
There's more...
See also
Obtaining usage information
Getting ready
How to do it...
How it works...
There's more...
See also
Adding storage nodes online
Getting ready
How to do it...
How it works...
There's more...
Replicating between MySQL Clusters
Getting ready
How to do it...
How it works...
There's more...
Replication between clusters with a backup channel
How to do it…
There's more…
See also
User-defined partitioning
Getting ready
How to do it...
There's more...
Disk-based tables
Getting ready
How to do it...
How it works...
There's more...
Calculating DataMemory and IndexMemory
Getting ready
How to do it...
There's more...
See also
4. MySQL Cluster Troubleshooting
Introduction
Single storage node failure
Getting ready
How to do it…
How it works…
There's more…
Multiple storage node failures
Getting ready
How to do it…
Storage node partitioning and arbitration
Getting ready
How to do it…
How it works…
Debugging MySQL Clusters
Getting ready
How to do it...
There's more…
Firewalls
Host resolution
Memory
Seeking help
Getting ready
How to do it...
There's more...
NIC teaming with MySQL Cluster
Getting ready
How to do it…
There's more…
5. High Availability with MySQL Replication
Introduction
Designing a replication setup
Getting ready
How to do it...
Master and slave
Multi-master (active / active)
Active / passive master
How it works...
Mixed-mode replication
Statement-based replication
Row-based replication
Configuring a replication master
Getting ready
How to do it...
How it works...
There's more...
Disk space
Only logging some databases
Limiting individual binary log size
Rotating binary logs
Performance
Binary log caching
Configuring a replication slave without syncing data
How to do it...
Configuring a replication slave, migrating data with a simple SQL dump
How to do it...
How it works...
Using LVM to reduce downtime on a master when bringing a slave online
Getting ready
How to do it...
Replication safety tricks
Getting ready
How to do it...
There's more...
Multi Master Replication Manager (MMM): initial installation
How to do it...
Multi Master Replication Manager (MMM): installing the MySQL nodes
How to do it...
Multi Master Replication Manager (MMM): installing monitoring node
How to do it...
Managing and using Multi Master Replication Manager (MMM)
How to do it…
How it works...
There's more...
Failure detection
6. High Availability with MySQL and Shared Storage
Introduction
Preparing a Linux server for shared storage
How to do it…
See also
Configuring two servers for shared storage MySQL
How to do it…
There's more…
Configuring MySQL on shared storage with Conga
How to do it…
How it works…
There's more…
Obtaining the cluster status
Migration of MySQL from node to node
Fencing for high availability
How to do it…
There's more…
See also
Configuring MySQL with GFS
How to do it…
There's more…
Cron job woes
Preventing unnecessary small writes
Mounting filesystem on both nodes
7. High Availability with Block Level Replication
Introduction
Installing DRBD on two Linux servers
Getting ready
How to do it...
How it works...
There's more...
Manually moving services within a DRBD cluster
Getting ready
How to do it...
How it works...
Using heartbeat for automatic failover
Getting ready
How to do it...
How it works...
8. Performance Tuning
Introduction
Make one modification at a time
Aim your efforts towards the biggest "bang for buck"
Be scientific in your approach
Tuning the Linux kernel IO
Getting ready
How to do it...
How it works...
There's more...
Tuning MySQL Cluster storage nodes
How to do it...
How it works...
Tuning MySQL Cluster SQL nodes
How to do it...
How it works...
There's more...
Tuning queries within a MySQL Cluster
Getting ready
How to do it...
Tuning GFS on shared storage
Getting ready
How to do it...
How it works...
MySQL Replication tuning
How to do it...
There's more...
A...

Table of contents