MySQL Replicationation基礎

標籤：file   define   rom   sar   monitor   name   ati   connected   names   

摘要

　　一、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 Configuration2.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.

 

MySQL Replicationation基礎