mysql replation_MySQL Replicationation基础

摘要

一、MySQL Replication

介绍MySQL Replication的基本概念，摘自于Mysql官网

二、Replication Configuration

2.1　Basic Steps in Configuration

一、MySQL Replication

1. What is Replicaiton?

For the purposes of this article we define "Replication" as the duplication of data to one or more locations.

These can be co-located within a data center, or geographically distributed.

Replication enables a database to copy or duplicate changes from one physical location or system to another.

note:

Replication is relatively good for scaling reads, which you can direct to a slave, but it's not a good way to scale writes unless you design it right

Attaching many slaves to a master simple causes the writes to be done, once on each slave

Replication is also wasteful with more than a few slaves, because it essentially duplicated a lot of data needlessly.

2. Problems Solved by Replication

note:MySQL's replication is usually not very bandwidth-intensive, and you can stop and start it at will

Load balancing: Distribute read queries across serveral servers, which works very wll for read-intensive applications.

Backups

High availability and failover

Testing MySQL upgrades

3. Replication: Master-Slave

概念简要说明：

Binary log: Binary log (二进制日志) is a Set of files :

Contains all writes and schema changes

!= REDO/Transaction log

Rotated when full (Set max_binlog_size)

Incrementing numbers (defaults, mysql-bin.000001,0000002,0000003...)

Relay Logs are alo binary logs

With 2 Formats: Statement Based(SBR), Row based(RBR, since mysql5.1)

Relay log: 中继日志

Binlog Dump Thread:

To send the binary logcontens to the slave.

A master that has multiple slaves "attached" to it creates one binlog dump thread for each currently connected slave, "with each slave having its own I/O and SQL threads."

Slave I/O Thread:

A START SLAVE statement creates an I/O thread, which connects to the master and asks it to send the updates recorded in its binary logs

Slave SQL Thread:

To read the relay logs that were written by the slave I/0 thread and executes the updates contained in the relay logs

If using multi-threaded slaves, mutiple SQL threads will be created on the slave.

4. Replication Modes and Data Consistency

Asynchronous Replication:By default, MySQL is asynchronus.

Updates are committed to the database on the master and than relayed(重播，转发) to the slave where they are also applied.

The master does not wait for the slave to receive the update, and so is able to continue processing further write operations without as it waits for acknowledgement from the slave.

Semi-Synchronous Replication

Using semi-synchronous replication, a commit is returned to the client only when a slave has received the update, or a timeout occurs.

Therrfore it is assured that the data exists on the master and at least one slave (note that the slave will have received the update but not necessarily applied it when a commit is returned to the master).

5. MySQL Replication workflow

二、Replication Configuration

2.1 Basic Steps in Replication

step1: Configure one server to be a master

step2: Configure one server to be a slave

step3: Connect the slave to the master

(1) Configure one server to be a master

To configure a server so that it can act as master, ensure the server has an active binary log and a unique server ID.

The Server ID is used to distinguish two servers from each other.

To set up the binary log and Server ID, you have to take the server down and add the log-bin,log-bin-index and server-id options to the my.cnf configuration file.

notes:

1. It is not necessary to give a name in the log-bin option, The default value is hostname-bin;

2. It is a good idea to create a name that is unique server and not tied to the machine the server is running on, since it can be confusing to work with a series of binlog files that suddenly change name midstream;

3. Each server is identified by a unique server ID;

(2) Creating Replication Accounts

Must create a user account on the master and give it the proper privileges, so the I/O thread can connect as  that user and read the master's binary log

(3) Configuration the Slave

server ID also

you may also want to consider adding the names of relay log and the relay log index files to the my.cnf file using the options relay-log and relay-log-index

(4) Connecting the Master and Slave

Final step: directing the slave to the master so that it knows where to replicate

It also lets you point the slave at a different master  in the future, without stopping the server.

(5) Monitoring Replication

(6) Watching Replication in Action

Event_type:

This is the type of the event.

Server_id:

This is the server 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 starts; that is, it's the first byte of the event/

End_log_pos:

This gives the position in the file where the event ends and the next event starts

Info:

This is human-readable text with information about the event.