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

Basic knowledge of GTID BEGIN

INSERT INTO innodb_tbl (…)

INSERT INTO myisam_tbl (…)

COMMIT

Using temporary table BEGIN in a transaction

INSERT INTO innodb_tbl (…)

CREATE TEMPORARY TABLE temp1

...

COMMIT

Tips

Before enabling GTID, detect if there are any statements / transactions in the system that are not supported by GTID, and process them in advance.

Global system variable enforce-gtid-consistency

OFF does not detect whether there are statements / transactions that are not supported by GTID

When WARN finds an unsupported statement / transaction, it returns a warning and logs the warning message in the log.

ON returns an error when it finds that the statement / transaction does not support GTID. -so it was wrong.

In the online database server or test environment, turn on WARN mode. +-- +

| | Level | Code | Message | |

+-- +

| Warning | 1786 | Statement violates GTID consistency: CREATE TABLE. SELECT. | |

+-- +

2016-07-11T10:48:34.627976Z 2 [Warning] Statement violates GTID consistency: CREATE TABLE. SELECT.

After processing statements that are not supported by GTID, enable GTID.

You can refer to this article to upgrade h to gtid online.

Connection: http://blog.itpub.net/29096438/viewspace-2060975/

Mysql5.7 multithreaded concurrent replication

The basic construction process can be found in the following documents: implementation and configuration of http://www.innomysql.com/article/16317.htmlhttp://www.innomysql.com/innosqlmysql parallel replication /

Mysql5.6 multithreaded replication: datababase/schema-based mysql5.7 multithreaded replication: concurrency of transaction-based logical clocks (Logical Clock) while inheriting 5.6 (since 5.7)

The specific differences are as follows:

Two types of concurrency

Library-based (Database/Schema) concurrency (since 5.6a)

The names of all libraries used to record statements in Binlog.

Transactions on different libraries can be executed concurrently, and transactions in the same library can be executed sequentially.

Concurrency (since 5. 7) of logical clocks (Logical Clock) based on transaction execution.

The relative order information of transaction execution is recorded in Binlog.

The basic principle is lock conflict detection, so it is also called lock-based concurrency mechanism.

The principle of concurrent replication based on lock

To be continued.

Quickly switch online to concurrent replication:

Mysql > STOP SLAVE SQL_THREAD

Mysql > SET GLOBAL slave_parallel_workers = 8; # number of concurrent threads

Mysql > SET GLOBAL slave_parallel_type = "LOGICAL_CLOCK"; # or DATABASE

Mysql > START SLAVE SQL_THREAD Note: if master 5.6 uses LOGICAL_CLOCK from 5.7, the following type of error may occur

Last_Errno: 1756

Last_Error: … The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Reference document: http://imysql.com/2016/06/02/mysql-faq-5-6-to-5-7-replicaton-failure.shtml

Semi-synchronous replication of MySQL-5.7 first understand what is semi-synchronous, what is synchronous, and what is asynchronous.

Synchronous replication: information is not returned to the client until all slave have commit transactions; disadvantages: the delay in completing a transaction may be large

Semi-synchronous replication: 5.7Lossless semi-synchronous: master receives a reply from slave before commit transaction more details can be found in the document: http://mp.weixin.qq.com/s?__biz=MzIwNzEzNDkxNQ==&mid=400915320&idx=1&sn=58dcc499cbcedcd827002acd77c30d60&scene=4#wechat_redirect5.6 semi-synchronous: master waits for a reply from salve after commit

Asynchronous replication: Master writes events to binlog, but does not know whether or when Slave has received and processed it. In the case of an asynchronous replication mechanism, if the Master goes down, the transaction is committed on the Master, but it is likely that these transactions are not transferred to any Slave. Assuming that there is a Master- > Salve failover mechanism, Slave may also lose transactions

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.