Part I. High Availability and Scalability

One of the database features that supports both high availability and scalability in an application is replication.
Replication is used to create redundancy in the database layer as well as to make copies of the databases available for scaling the reads.
Part I covers how you can use replication to ensure high availability and how you can scale your system.

Chapter 1 Introduction

What’s This Replication Stuff, Anyway?

One of the two important uses of replication in many modern aplications is that of scaling out.

  • Read-Intensive scenario(读写分离)
  • Sharding(分片)

Another important application of replication is ensuring high availability by adding redundancy.

  • dual-master setup(双主配置)

So, Backups Are Not Needed Then?

  • Protection against mistakes
  • Creating new servers
  • Legal reasons

Chapter 2 MySQL Replication Library

This chapter intruduces the MySQL Replication libary, a simple library written by the authors for managing replication. You can get code at here:

Chapter 3 MySQL Replication Fundamentals

This chapter covers the following replication use cases:

High availability through hot standby(热备份)
To configure an extra server with the sole purpose of acting as a hot standby, ready to take over the job of the main server if it fails.

Report generation(生产报表 离线库)
You can get a snapshot of the database at a certain time by stopping replication on the report server and then running large queries on it without disturbing the main business server.

Debugging and auditting(调试和审计)
To see whether there is performance problems, or whether a server has gone out of sync by investigate queries that have been executed on the server.

Basic Steps in Replication

  1. Configure one server to be a master
  2. Configure one server to be slave
  3. Connect the slave to the master

Configuring the Master

[code lang=”shell”] [mysqld] user = mysql pid-file = /var/run/mysqld/ socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp log-bin = master-bin log-bin-index = master-bin.index server-id = 1 [/code]

Configuring the Slave

[code lang=”shell”] [mysqld] user = mysql pid-file = /var/run/mysqld/ socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp server-id = 2 relay-log-index = slave-relay-bin.index relay-log = slave-relay-bin [/code]

Connecting the Master and Slave

[code lang=”shell”] change master to -> MASTER_HOST = ‘…’, -> MASTER_PORT = 3306, -> MASTER_USER = ‘repl_user’, -> MASTER_PASSWORD = ‘123456’; [/code]

A Brief Introduction to the Binary Log

Binary Log is a record of all changes made to the database on a server. It’s used for replication, as well as for point-in-time-recovery(PITR).

What’s Recoreded in the Binary Log

There are two methods statement-based replication and row-based replication.

Use SHOW BINLOG EVENTS\G to see what events are in the binary log.

  • Event_type Type of event
  • Server_id ID of the server that created the event
  • Log_name The name of the file that stores the event
  • Pos This is the position of the file where the event start.(i.e., the first byte of the event)
  • End_log_pos This gives the position in the file where the event ends and the next event start.
    • The event is range from Pos to End_log_pos – 1, the length of the event can be computed as End_log_pos – Pos.
  • Info human-readable text

The Binary Log’s Structure and Content

One binlog file is active binlog file. This is the file that is currently being written to( and usually read from as well).

Each binlog file starts with a format description event and ends with a rotate event.

Each file is organized into binary log events, where each event makes a standalone atomic piece of the binary log.


Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.