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

MySQL master-slave replication

Master-slave replication type:

Asynchronous mode: for general MySQL replication, the master server is only responsible for sending binary logs, not check whether it is completed or not

Semi-synchronous mode: in a heap, the master only ensures that one of them returns synchronous completion, and if it times out, it is automatically downgraded to asynchronous

Cascading: the master copies to one of the slaves, and this slave replicates to the following pile of slaves to lighten the burden of the master.

Architecture:

Ordinary one master and multiple followers:

Read-write separation with load balancing:

Read-write separation with cascading replication and load balancing:

Master-slave steps:

The master opens a connection between the IO thread on the dump thread slave and the dump thread on the master for each slave, and sends the binary log, which is saved as the SQL thread on the realy log slave and read one by one from the relay log and executed locally

Master-slave configuration:

Main configuration: enable binlog to set server id to create synchronous users, and grant REPLICATION SLAVE and REPLICATION CLIENT permissions

Configuration from above: enable relay log settings different from the master server idCHANGE MASTER TO MASTER_HOST='',MASTER_PORT=,MASTER_USER='',MASTER_PASSWORD='',MASTER_LOG_FILE='',MASTER_LOG_POS=;start slave io_thread;start slave sql_thread; configuration skip-slave-start=1

Semi-synchronous configuration: install the master/slave remi plug-in: INSTALL PLUGIN enable the master/slave remi plug-in to set the timeout. If it exceeds this time, it will be automatically downgraded to asynchronous mode.

Synchronization filtering configuration:

Optional configuration on the host (not recommended, as it will result in loss of binary events): binlog-do-db: binary logging this library operation binlog-ingire-db: binary logging ignores this library operation

Optional from above (recommended, but will result in unnecessary performance consumption): replicate-do-db: synchronize this dbreplicate-ingire-db: ignore this dbreplicate-do-table: synchronize this table replicate-ingire-table: ignore this table replicate-wild-do-table=tb%: supports regular, replicate-wild-ingire-table=tb_: tables starting with synchronous tb host regular, ignore tables starting with tb followed by a character

Based on GTID and multithread replication:

1. GTID is a secure replication method. Global transaction ID is supported after MySQL5.6 version. Replication is based on global transaction ID. During replication, slaves will feedback their replication status and record which statements are replicated. Other slaves also know the replication location of other slaves, which is more secure when switching between master and slave.

2. Multithreaded replication is based on multiple libraries, each with a single thread, and multiple libraries can use multiple threads.

What is GTID (Global transaction identifiers):

MySQL-5.6.2 is supported and improved after MySQL-5.6.10. GTID is divided into two parts. One is that the UUid,UUID of the service is stored in the auto.cnf file of the mysql data directory.

This is a very important document and cannot be deleted. This part will not change. The other part is the transaction ID, which increases at a time as the transaction increases, as shown in the figure below.

+-- +

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-- +

| | binlog.000029 | 23556 | 724afcc2-29d6-11e4-9902-000c290c0121:1-362 |

+-- +

GTID:724afcc2-29d6-11e4-9902-000c290c0121:1-362,

UUID:724afcc2-29d6-11e4-9902-000c290c0121

TransactionId:1-362

GTID does not change throughout the replication architecture, even among multiple serial owners.

For example: ServerA-- > ServerB-> ServerC

GTID is the same in both ServerA and ServerB,ServerC.

Second, the working principle of GTID

1. When master updates data, GTID will be generated before the transaction and recorded in the binlog log.

2. The iUnip thread on the slave side writes the changed binlog to the local relay log.

3. The SQL thread gets the GTID from the relay log, and then compares whether the binlog on the slave side has a record.

4. If there is a record, the transaction of the GTID has been executed, and slave will ignore it.

5. If there is no record, slave will execute the transaction of the GTID from relay log and record it to binlog.

6. During the parsing process, it will determine whether there is a primary key, if not, use a secondary index, and if not, use a full scan.

3. Advantages of GTID:

1. A transaction corresponds to a unique ID, and a GTID is executed only once on a server.

2.GTID is a method used to replace traditional replication. The biggest difference between GTID replication and ordinary replication mode is that there is no need to specify a binary file name and location.

3. Reduce manual intervention and service failure time. When the host is down, upgrade one standby machine to the host through software.

So how does GTID replication automatically synchronize and automatically correspond to locations?

For example: ServerC ServerB

Host ServerA

Standby: ServerB,ServerC

When the host ServerA hangs, ServerB executes all the transactions passed from ServerA

ServerC delay a little bit. At this point, ServerB needs to be promoted to the host, and Server C will continue to be the standby.

When ServerC links to ServerC, first find the latest GTID from ServerA in your binary file

Then send the GTID to ServerB, and after ServerB gets the GTID, it starts from the next GTID of this GTID

Start sending transactions to ServerC. This self-finding replication location mode reduces the possibility of transaction loss and the time it takes to recover from failures.

Fourth, the restrictions of GTID:

1. Non-transaction engine is not supported

two。 Create table is not supported. Copy the select statement (the main database reports an error directly)

Principle: (two sql are generated, one is the DDL creation table SQL, and the other is the sql in which the insert into inserts the data.

Because DDL causes auto-commit, this sql requires at least two GTID, but in GTID mode, only one GTID can be generated for this sql)

3. A SQL is not allowed to update both a transaction engine table and a non-transaction engine table

4. In a replication group, you must either enable GTID or turn off GTID.

5. Restart is required to enable GTID (except 5.7)

6. When GTID is enabled, the original traditional replication method is no longer used.

7. Not supported for create temporary table and drop temporary table statements

8. Sql_slave_skip_counter is not supported

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.