Mariadb-10GTID replication and multi-source Replication

--- Outline 1, what is GTID 2, application scenario 3, multi-thread replication description 4, implementation process 5, multi-source replication Principle 6, implementation process ---------------------------- 1. What is GITD from MySQL

--- Outline 1, what is GTID 2, application scenario 3, multi-thread replication description 4, implementation process 5, multi-source replication Principle 6, implementation process ---------------------------- 1. What is GITD from MySQL

--- Outline

1. What is GTID?

II. Application scenarios

Iii. multi-thread Replication

IV. Implementation Process

V. Multi-source replication principles

Vi. Implementation Process

----------------------------------

1. What is GITD?

GTID (Global Transaction IDs) introduced in MySQL 5.6 makes the configuration, monitoring, and management of its replication function easier to implement and more robust. In this document, we can see that the official definition of the global transaction ID is: GTID = source_id: transaction_id

Gtid is a unique identifier of a unique. It is globally unique identifier of the uuid of the server and is composed of 128 random characters, the mysql-5.6 relies on the server-id and uuid to identify each host in the replication architecture, because 128-bit random strings are not repeated globally, server-id and uuid and the transaction serial number of every mysql transaction constitute a unique gtid, since the introduction of mysql-5.6, every binary log will write the gtid mark in the header of every transaction, therefore, gtid makes it easy to track and compare duplicate things and can quickly recover from crashes. In particular, the innodb engine must use gtid to implement high availability.

II. Application scenarios

GTID is especially important when mariadb is used to achieve high availability. For example, in a high-availability environment, when the master database goes down in the one-master-multiple-slave mode (write latency ), the cluster resource manager can select one of the multiple slave databases in the node list as the master database without affecting the business, other slave databases will be upgraded to the master database as the master database in the current cluster, and will be copied and synchronized from this database later. Although this completes the "instant" service switching, however, before the master database goes down, replication of multiple slave databases lags behind the master database. In this way, when the master database goes down, the cluster resource manager switches to a slave database (B) that may be inconsistent with its own transaction commit. When an application reads the transaction committed in the master database that was previously down, if the current master database does not exist, transaction inconsistency will occur, and GTID can solve this problem well. First, when a slave database is promoted to a master database, the slave database pointing to the original master database will all point to the new master database for subsequent replication. Then, the upgraded master database B will integrate all the transactions completed from the database to supplement what it lacks. B will know from there that what it lacks is that transaction, there are so many transactions. This transaction is identified by GTID, because GTID identifies the source server and the number of transactions (Source_id: transaction_id ), each change is related to GTID and recorded in the binlog log for synchronization by other slave databases, so as to achieve a globally unique identifier.

Note:

GTID does not change in multi-level replication.

In GTID, if the master server has multiple databases, multi-threaded replication relies on I/O replication to the slave database's relay logs. Multiple SQL threads are used locally.

Iii. multi-thread Replication

In versions earlier than MySQL 5.6, synchronous replication is single-threaded, and queues can only be executed one by one. In MySQL 5.6, multi-threaded replication between multiple databases can be performed, for example, in a database, store user tables, product tables, price lists, and order tables. Store each business table in a single database. In this case, you can perform multi-threaded replication, but tables in one database can be copied, multi-threaded replication is invalid.

Note:

Each database can only use one thread. multi-thread replication makes sense when multiple databases are involved in replication;

Transaction replication of the same database must be performed sequentially.

In the replication model, the same thread (I/O thread) can provide services for multiple databases.

IV. Implementation Process

Note:

GTID replication is only implemented here.

1. Resource allocation

Service type version type IP Address

OS
Centos6.5x86 _ 64 none

Mariadb Master10.0.10-MariaDB-log Source distribution192.168.1.122/24

Mariadb Slave10.0.10-MariaDB-log Source distribution192.168.1.210/24

2. Mariadb Master configuration list

(1) edit the configuration file/etc/my. cnf

[Client] port = 3306 socket =/tmp/maria. sock [mysqld] port = 3306 socket =/tmp/maria. sockskip-external-lockingkey_buffer_size = bytes = 1Mtable_open_cache = bytes = 8query_cache_size = bytes = 4 datadir =/mydata/datalog-bin = mysql-binbinlog_format = rowserver-id = 10log-slave-update = Truemaster- info-repository = TABLErelay-log-info-repository = TABLEsync-master-info = bytes = CRC32slave-sql-verify-checksum = bytes = 1report-port = 3306report-host = master.mysql.com [mysqldump] bytes = 16 M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 128Msort_buffer_size = 128Mread_buffer = 2Mwrite_buffer = 2 M [mysqlhotcopy] interactive-timeout

Note:

Binlog-format

Binary log formats: row, statement, and mixed;

When you set the isolation level to READ-COMMITED, you must set the binary log format to ROW, and now MySQL officially thinks STATEMENT is no longer suitable for continued use; but the mixed type is at the default transaction isolation level, the Master/Slave Data may be inconsistent;

Log-slave-updates, report-port, and report-host

It is used to start GTID and meet additional requirements;

Master-info-repository and relay-log-info-repository

Enable these two functions to ensure binary and slave server security during crash;

Sync-master-info

Enabling it ensures no information is lost;

Slave-paralles-threads

Sets the number of SQL threads on the slave server. 0 indicates that the multi-thread replication function is disabled;

Binlog-checksum, master-verify-checksum, and slave-SQL-verify-checksum

Enable all verification functions related to replication;

Binlog-rows-query-log-events

This feature can be used to record event-related information in binary logs to reduce the complexity of troubleshooting;

Log-bin

Enabling binary logs is the basic prerequisite for ensuring the replication function;

Server-id

The IDS of all servers in the same replication topology must be unique.

Log_slave_updates

Record slave server updates

(2) authorized copy nodes and users