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（读写分离）
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
- Configure one server to be a master
- Configure one server to be slave
- 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.
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.