MySQL Replicationation Basics

Summary

First, MySQL Replication

Introducing the basic concept of MySQL replication, from the MySQL website

Second, Replication Configuration

2.1 Basic Steps in Configuration

　　　　　　

First, 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 can direct to a slave, but it's not a good a-to Scale writes unless your design it right
  • Attaching many slaves to a master simple causes the writes to is 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 would

• 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

Brief description of the concept:

binary logs: binary log (binary) 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 is alo binary Logs
• with 2 Formats:statement Based (SBR), Row Based (RBR, since mysql5.1)

Relaylog: Relay logs

Binlog Dump Thread:

　　To send the binary logcontensto the slave.

A master that had multiple slaves "attached" to it creates one Binlog dump thread for each currently connected slave, " with Each slave has 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-send the updates recorded in it s binary logs

Slave SQL Thread:

To read the relay logs , were written by the slave i/0 thread and executes the updates contained in t He relay logs

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

4. Replication Modes and Data consistency

• Asynchronous Replication: By default, MySQL is Asynchronus.
  

Updates is committed to the database on the master and than relayed (replay, forward) to the slave where they is also applied.

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

　　

• Semi-synchronous Replication

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

Therrfore It is assured, the data exists on the master and at least one slave (note that the slave would have received The update necessarily applied it when a commit was returned to the master).

5. MySQL Replication Workflow

Second, Replication Configuration2.1 Basic Steps in Replication

Step1:configure one server to bes a master

Step2:configure one server to bes a slave

Step3:connect the slave to the master

(1) Configure one server to bes a master

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

　　The Server ID is used to distinguish, servers from each of the other.

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

Notes

1. It isn't 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 "is unique server" and "tied to the machine" is running on, since it Can is confusing to work with a series of binlog files, suddenly change name midstream;

3. Each server was 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 an D Read the master ' s binary log

(3) Configuration the Slave

Server ID Also

Also want to consider adding the names of relay logs and the relay log index files to the my.cnf file using the OPT Ions Relay-log and Relay-log-index

　

(4) connecting the Master and Slave

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

It also lets 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 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's, 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 was human-readable text with information about the event.

MySQL Replicationation Basics