Cuttlefish business MySQL architecture-from scratch

Project description

Using MySQL replication as the database underlying schema write for the project at master read on slave

The main library uses

MYSQL5.6 version

Use from library

MYSQL5.7 version #为了测试MYSQL5.7 and older versions of compatibility

InnoDB Storage engine Each table has a self-increment ID master key, RR isolation level

1. Configuring the Environment Description

1.1 Selection of Binlog format

Common Binlog format has statement,row,mixed

Use row format as Binlog log format

Row format Advantages Special functions can effectively replicate security tables have the self-increment ID key copy speed will be faster

If you choose SRB and mix replication possible special functions cannot be copied effectively such as UUID now etc.

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/8E/60/wKiom1i-qMDgZqujAAGPvSe1AxA813.png-wh_500x0-wm_ 3-wmp_4-s_465270151.png "title=" qq picture 20170307203319.png "Width=" "height=" 251 "border=" 0 "hspace=" 0 "vspace=" 0 " Style= "width:500px;height:251px;" alt= "Wkiom1i-qmdgzqujaagpvse1axa813.png-wh_50"/>

1.2 from Read_onle and Super_read_onle to avoid writing from

Read_onle=on && Super_read_onle=on #这样任何账户都不能写入

1.3 With Gtid replication mode

Gtid Introduction

Each transaction has a unique number

Uuid:n UUID is the MySQL unique identifier n is the transaction ID number

A transaction corresponds to a unique ID, and a gtid is executed only once on a server

Gtid ServerID: Transaction ID The number of each transaction ID is unique and cannot be executed repeatedly

MYSQL5.6.2 Support MYSQL5.6.10 Perfect

Gtid restrictions

Non-transactional engine not supported (Error stop slave from library; Start slave; Ignored

CREATE TABLE is not supported .... SELECT statement Replication (Main Library direct error)

Don't let a SQL update a table for both the transaction engine and the non-transactional engine

In a replication group, you must require a unified turn on Gtid or close Gtid

Reboot required to open Gtid (5.7 not required)

When you turn on Gtid, you are not using the original traditional copy method

Not supported for Create tmporary table and drop temporary table statements

Sql_slave_skip_counter not supported

Personal Understanding:

GTID facilitates master-slave maintenance master-slave replication Master-slave verification master-slave switch, etc.

Two-to-one guaranteed data consistency MASTER innodb_flush_log_at_trx_commit=1 sync_binlog=1

The stress test at that time met the current business master and slave have opened a double

Hosts configured with a domain name that specifies master and slave

So the application does not have to modify the automatic synchronization of the host file with Ansible

Disadvantages:

1. master-Slave fault manual switch monitoring to do the first time to do the switch

2. Data consistency is difficult to guarantee (1)

Mid-term environmental improvements

keepalived-1.2.13

MYSQL5.7 MASTER

MYSQL5.7 MASTER

2. Configuration description

Using keepalived to do the double main structure MySQL also does the double master mutual main from the relationship

auto_increment_increment=2# Self-increment

auto_increment_offset=1/2# Start Step

Keepalived:mysql is not in the same switch on the test script inside write Ping gateway if it does not return non-0, in order to prevent brain crack

Open master_info_repository=table relay_log_info_repository=tblae in master and slave

The master-slave record is written to the table and not to the system file.

Relay_log_recovery is turned on to prevent the relay from suddenly shutting down during execution slave or relay log corruption

Disadvantages:

1. The problem of update loss can be difficult to determine whether the master database data or the library data will prevail

Simultaneous update of two machines will not cause a blockage and will not cause the lock to be written to binlog according to the primary key update and then pass the other's relay log and apply, resulting in the loss of updates. Because master-slave replication updates according to the primary key, no school team other data. If no primary key is updated based on a level two index, the first longest index is used to match the primary key if none of the full table scan is updated.

Double-main structure PXC structure MGR structure will cause this problem solving method single machine update. Insert or delete can be load distributed

2. If the master transmits Binlog without secure transfer to slave it will also cause inconsistent data.

3. Post-environmental improvements

proxysql-1.3.4 Read/write separation

keepalived-1.2.13

MYSQL5.7 MASTER

MYSQL5.7 MASTER

MYSQL5.7 SLAVE ..... Multiple

Configuration description

1. Troubleshooting Data consistency is difficult to guarantee (1)

Double 1 resolves the client submission after the database is successfully executed and written to redo log and Binlog, which is a two-phase commit

5.7 Enhanced semi-synchronous resolves Master's binlog transfer to slave before submitting the return client

Double table avoids file and POS information that is not refreshed in a timely manner, resulting in loss of log points

The relay_log_recovery is turned on to prevent the relay from abruptly shutting down during execution slave or relay log corruption and re-executing relay log if it is found to be closed unexpectedly

Row format Gtid copy format

2. Use Proxysql to read and write MYSQL5.7 MASTER to write operations MASTER2 and slave do the reading operation

3.master1 and Master dual-master do enhanced semi-synchronous replication

4. Parallel replication with MYSQL5.7 is a true parallel copy of group commit level non 5.6 library commit level

Disadvantages

1. Fix re-add to Proxysql Information if node is hung after repair

Plan to automate with Python Add node change node information

This article from "Linux" blog, declined reprint!

Cuttlefish business MySQL architecture-from scratch