Shulou(Shulou.com)06/02 Report--

Log type of MySQL

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

The binary log has the following contents

Data directory, which is generally placed in the mysql-bin.XXXXX number)

Scroll: reach a maximum limit, flush logs, restart the server

Format: statement

Row

Mixed

Mysql-bin.index: binary log file index fil

Mysql > SHOW MASTER STATUS to view the status of the primary server

Mysql > SHOW BINARY LOGS View binary Log

Mysql > SHOW BINLOG EVENTS IN 'file' to view events in the binary log

The more important options in event:

Timestamp timestamp

Position,offset,operation,server-id

Isolation level of MySQL:

READ-UNCOMMITED

READ-COMMITED

Data inconsistencies may occur if you use mixed

The officially recommended method of using row

REPEATABLE-READ

SERIALIZABLE

Copy the functions implemented

Realize data backup

If there is a slave server, after the failure of the master server, the write function of the slave server is enabled to provide highly available usage functions.

Remote disaster recovery

Load sharing (scale out) master server: write slave server: read

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

First of all, master-slave replication is introduced (used more frequently)

On the main server, every time the front-end user executes an instruction to modify or cause changes to the database, it will be saved as an event in the binary log, and each event will be sent to another server through port 3306 of MySQL, and the other server will receive the log, first save it in the local log file, and then read one operation at a time. Each change is then executed on its own server, a process called MySQL replication

There is a binary file Binary log on master

Files copied by slave from binaries on the primary server are called relay logs (relay log).

When multiple transactions are executed concurrently on the primary server, but only one write can be done when writing to the binary, so a temporarily cached log file is required to cache, and then write to the binary

There is also a distinction between synchronous replication and asynchronous replication in terms of replication methods:

Synchronous replication

The slave server cannot be slower than the master server, and the changes in the front end can not get the slave response in time.

So basically using semi-synchronous replication.

Semi-synchronous replication only sends data synchronization to another node, which can only ensure that the nearby node can respond in a timely manner.

Asynchronous replication: one master and multiple slaves, waiting for each server to be completed synchronously

When replicating from multiple slave servers, it may be difficult for the master server to process effectively because there are too many requests, so a front-end agent is required.

Read-write separation (rw-splitting): find a MySQL front-end agent that works in the application layer, can understand MySQL statements, and can direct different operations (read and write) to different servers and distribute them to the master server and slave server respectively, thus completing the read-write separation

Multilevel replication: a slave server may be a slave server of one master server, or it may be a slave server of another slave server

If a slave server does not record the relay log, the content cannot be sent to the next level server, and the write operation cannot be performed from the server.

Next, we introduce the two-master model:

In the dual master model, the configuration is almost the same, but we should pay attention to the importance of server-id to prevent the occurrence of replication loops.

It is impossible to reduce the write operation, so it is easy to cause problems.

For example

Tutors:name,age,gender,tid

Tom 10

Jerry 30

Host A: UPDATE tutords SET name=jerry

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

When the synchronization is finally completed, the query results do not recognize that they can be merged, and there will be conflicts.

Therefore, in a production environment, it is generally not recommended to use a double master model.

Read-write separation can be implemented by the following components:

Mysql-proxy

Amoeba

Routing is implemented after data split:

Cobar

Add:

Master: slave

1mura-> N

Slave: master

1mura-> N X

A slave server can only belong to one master server

Replication implementations before MySQL 5.5 are very simple

Gtid,multi-thread replication (multithreaded replication) is referenced after MySQL 5.6,

Basic steps for configuring MySQL replication

Master-slave replication

I. master

Enable binary Log

Log-bin=master-bin

Log-bi-index = master-bin.index

Select a unique server-id

Server-id = {0-2 ^ 32}

Create a user with replication permissions

REPLICATION SLAVE

REPLICATION CLIENT

II. Slave

1. Enable Relay Log

Relay-log = relay-log

Log-bin-index=

two。 Select a unique server-id

Server-id = {0-2 ^ 32}

3. Connect to the primary server and start replicating 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 start the SQL thread

Mysql > START SLAVE IO_Thread start the IO thread

Copy thread:

Master:dump

Slave: IO_Thread,SQL_Thread

Read_only=YES

Set on the slave server, it does not take effect for users with SUPER permission

Sync-binlog = ON

Set on the primary server for transaction security

Semi-synchronous replication

Install a plug-in provided by google on master and slave respectively

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

Double master replication

Create a user with replication permissions on each of the two servers

Modify the 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 are no other write operations, each server needs to record its own binary file and event location as the starting point for replication on the other server.

4. Each server can then designate another server as its own master server.

Host A must view the binary file and location of B and use it as its starting point for replication.

Host B must view the binary file and location of An and use it as its starting point for replication.

Both servers can read and write, that is, the dual master model.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.