MySQL's replication rationale and basic configuration

Log type for MySQL

Binary log, transaction log, error log, general query log, relay log, slow query log

The binary log has the following contents

Data catalog, usually placed in Mysql-bin. XXXXX number)

Scroll: Reach a maximum limit, flush logs, server restart

Format: statement

Row

Mixed

Mysql-bin.index: Binary log file Index file

MySQL > Show master Status View the state of the primary server

MySQL > Show binary LOGS view binary log

MySQL > Show BINLOG events in ' file ' to view event in binary log

More important options in the event:

Timestamp time stamp

Position,offset,operation,server-id

The isolation level of MySQL:

read-uncommited

read-commited

If data inconsistency may occur when using mixed

Official recommended method of using row

Repeatable-read

SERIALIZABLE

Features of the replication implementation

1. Enable data backup

2. If there is a failure from the server after the primary server, the write function from the server is opened, thus providing a highly available usage feature

3. Remote disaster recovery

4. Load-sharing (scale out) master server: Write from server: Read

   
   

There are three replication methods, namely master- slave replication , semi-synchronous replication, and primary master replication .

First, master-slave replication (use more frequently)

On the primary server, the front-end user every time the database changes or the instructions that caused the modification, will be stored in the binary log as an event, each save an event, will be sent through the MySQL port 3306 to another server, another server will receive this log, Then save in the local log file, and then each time read an operation, and then each modification to their own server, this process is called MySQL replication

There is a binary binary file on master, binary log

Slave files copied from a binary file on the primary server, called a trunk log (relay log)

When there are multiple transactions concurrently executed by the primary server, but only one write is written to the binary, it is necessary to cache a temporary cached log file and then write to the binary file

There is also a distinction between synchronous replication and asynchronous replication on the replication method:

Synchronous replication

From the server can not be slower than the primary server, the front-end changes can not be timely to get slave response

So it's basically a method of using semi-synchronous replication

Semi-synchronous replication simply sends the data synchronously to another node, only to ensure that the near node responds in a timely manner.

Asynchronous replication : a master multi-slave, waiting for each server to be completed synchronously,

When multiple copies are made from the server, it is possible that the primary server is difficult to handle because of too many requests and therefore requires a front-end agent.

Read/write Separation (rw-splitting): Find a MySQL front-end agent, working in the application layer, able to understand the MySQL statement, to complete the different operations (read and write) directed to different servers, distributed to the primary server and the slave server, to complete the read and write separation

Multi-level replication : a slave server that may be a primary server from a server, or another slave server from the server

If a relay log is not logged from the server, the content cannot be sent to the next-level server and cannot be written from the server

Next, we introduce the two-master model :

Dual-master model, the configuration is almost the same, but pay attention to the importance of server-id, to prevent the occurrence of replication loops

Unable to reduce write operation, it is easy to go wrong

For example

Tutors:name,age,gender,tid

Tom 10

Jerry 30

A host: UPDATE tutords SET Name=jerry

B Host: UPDATE tutors SET age=30 WHERE Name=tom

When the final synchronization is completed, the results of the query do not recognize the ability to merge, resulting in conflicts

Therefore, in a production environment, it is generally not recommended to use a dual-master model

Read and write separations can be implemented by the following components:

Mysql-proxy

Amoeba

Implementing the route after the data has been split:

Cobar

Add:

Master:slave

1-->n

Slave:master

1-->n X

A slave server can only belong to one master server

The replication implementation before MySQL 5.5 is very simple,

MySQL 5.6 References Gtid,multi-thread replication (multithreaded replication)

Basic steps for configuring MySQL replication

Master-slave replication

I. Master

Enable binary logging

Log-bin=master-bin

Log-bi-index =master-bin.index

1. 1. Choose a unique Server-id

      Server-id ={0-2^32}

   2. To create a user with replication permissions

REPLICATION SLAVE

REPLICATION CLIENT

Two. Slave

1. Enable the relay log

Relay-log =relay-log

log-bin-index=

2. Select a unique Server-id

Server-id ={0-2^32}

3. Connect to the master server and start copying data

MySQL > CHANGER MASTER to master_host= ', master_port= ', master_log_file= ', master_log_file_pos= ', master_user= ' , master_password= ';

MySQL >start SLAVE

Mysql>start SLAVE sql_thread Open SQL thread

Mysql>start SLAVE io_thread Open IO thread

Replication Threads:

Master:dump

Slave:io_thread,sql_thread

Read_only=yes

Set on slave server, not valid for users with super privileges

Sync-binlog = On

Set on the primary server for transaction security

Semi-synchronous replication

Install a Google-provided plugin on both master and slave

On the primary server

Mysql>install PLUGIN rpl_semi_sync_master SONAME ' semisync_master.so ';

Mysql>set GLOBAL rpl_semi_sync_master_enabled=1;

Mysql>set GLOBAL rpl_semi_syc_master_timeout=1000;

From the server

Mysql>install PLUGIN rpl_semi_sync_slave SONAME ' semisync_slave.so ';

Mysql>set GLOBAL rpl_semi_sync_slave_enables = 1;

Mysql_stop SLAVE Io_thread;start SLAVE io_thread;

Dual master replication

1. Set up a user with replication permissions on both servers

2. Modifying a configuration file

On the primary server

Server-id =10

Log-bin =mysql-bin

Relay-log=relay-mysql

Relay-log-index =relay-mysql.index

Auto-increment-incremeng =2

Auto-increment-offset =1

From the server

Server-id=20

Log-bin=mysql=bin

Relay-log=relay-mysql

Relay-log-index=relay-mysql.index

auto-increment-increment=2

Auto-increment-offset =2

3. If both servers are newly established and there is no other write operation, each server needs to record its current binaries and the location of the event as another server to copy the starting location

4. Each server is then assigned to another server as its own master server.

A host must look at B's binaries and locations as a starting point for their own replication

B hosts must view A's binaries and locations as their copy starting point

Two servers can read and write, which is a dual master model

This article is from the "Newton also eat apple" blog, please be sure to keep this source http://mi55u.blog.51cto.com/11619321/1926833

MySQL's replication rationale and basic configuration