《MySQL High Availability》Chapter 1&3 Learn Note

看到此文,是否觉得体内洪荒之力爆发,饥渴难耐想吐槽、情不自禁想捐赠
本文为原创文章,尊重辛勤劳动,可以免费摘要、推荐或聚合,亦可完整转载,但完整转载需要标明原出处,违者必究。

支付宝微  信

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: https://code.launchpad.net/mysql-replicant-python

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

[mysqld]
user                   = mysql
pid-file               = /var/run/mysqld/mysqld.pid
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

Configuring the Slave

[mysqld]
user                   = mysql
pid-file               = /var/run/mysqld/mysqld.pid
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

Connecting the Master and Slave

change master to
-> MASTER_HOST = '...',
-> MASTER_PORT = 3306,
-> MASTER_USER = 'repl_user',
-> MASTER_PASSWORD = '123456';

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.


这是一篇原创文章,如果您觉得有价值,可以通过捐赠来支持我的创作~
捐赠者会展示在博客的某个页面,钱将会用在有价值的地方,思考中...


分类: MySQL, 技术, 随记 | 标签: , , , , | 评论 | Permalink

发表评论

电子邮件地址不会被公开。

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据