Mysql5.7 Gtid-based semi-synchronous replication

First, what is Gtid?

Gtid is the ID of the transaction, the unique identification number, and the globally unique.
The transaction is logged to binary log, which is used to identify the transaction.
Each transaction has a gtid_log_event.
Composition of the Gtid: UUID + Sequence number

Sequence number is a transaction order code inside the MySQL server. A transaction on a MySQL server does not have a duplicate sequential number (guaranteed to be unique within the server).
Each MySQL server has a globally unique UUID.

The purpose of Gtid
Simplifying the use of replication and reducing the difficulty of replication cluster maintenance is no longer dependent on the master's Binlog file name and location in the file.
General: Change MASTER to master_log_file= ' master-bin.000008 ', master_log_pos= ' 216 ';
Use Gtid:change MASTER to auto_position=1;

The principle of auto_position
MySQL Server Records the Gtid of all the transactions that have been performed, including those that have been replicated (available over the system variable gtid_executed view).
Slave records all transactions received from Master Gtid (viewable through Retrieve_gtid_set).
When the slave is connected to master, the Gtid in gtid_executed is sent to Master,master, which automatically skips the transactions and sends only those things that are not copied to slave.

Gtid statements/Transactions not supported
1.CREATE TABLE ... SELECT

2. Both support transactions and the engine that do not support transactions are used in the transaction.
BEGIN;
INSERT into Innodb_tbl (...);
INSERT into Myisam_tbl (...);
COMMIT;

3. Using Create/drop temporary TABLE in a transaction
BEGIN;
INSERT into Innodb_tbl (...);
CREATE temporary TABLE Temp1;
．．．
COMMIT;

Note: Before you enable Gtid, detect if there are statements/transactions in the business system that are not supported by Gtid and are processed in advance.

Enable Gtid

SET @ @GLOBAL. Gtid_mode = off_permissive; (set on each server)
SET @ @GLOBAL. Gtid_mode = on_permissive; (set on each server)
SET @ @GLOBAL. Gtid_mode = on; (set on each server)

Second, no data loss of semi-synchronous replication

Starting with MySQL5.5, MySQL supports semi-synchronous replication in the form of plugins. How to understand the semi-synchronous? First, let's look at the asynchronous, fully synchronous concept.

Asynchronous replication (asynchronous replication)

MySQL default replication is asynchronous, the main library after executing the client committed transactions will be returned to the client immediately, do not care whether the library has been received and processed, so there is a problem, if the main library crash dropped, at this time the main library has committed transactions may not be uploaded from the top, if at this time, Forcing will be based on Ascension, which may result in incomplete data on the new Master library.

Full synchronous replication (Fully synchronous replication)
When the main library executes a transaction, and so on, all of the transactions executed from the library are returned to the client. Because you need to wait for all of the transactions from the library to commit, the performance of full synchronous replication is bound to be severely impacted.

Semi-synchronous replication (semisynchronous replication)
Between asynchronous replication and full synchronous replication, the main library does not immediately return to the client after executing a client-submitted transaction, but waits for at least one receipt from the library and written to the relay log to return to the client. Semi-synchronous replication improves data security relative to asynchronous replication, and it also causes a certain amount of latency, which is at least a TCP/IP round-trip time. Therefore, semi-synchronous replication is best used in low latency networks.

Potential problems with semi-synchronous replication

After the client transaction is committed at the storage engine level, the main library is down during the process of getting confirmation from the library, and there are two possible scenarios:

1. The transaction has not been sent to the library

At this point, the client will receive a transaction submission failure information, the client will resubmit the transaction to the new master, when the main library of the outage to rejoin the master-slave structure from the identity of the library, it will be found that the transaction was submitted from the Library two times, one time before the primary, once was synchronized by the new host.

2. The transaction has been sent from the library

At this point, the transaction has been received and applied from the library, but the client will still receive information about the transaction submission failure and resubmit the transaction to the new Lord.

Countless data-loss semi-synchronous replication

For these potential problems, MySQL 5.7 introduces a new semi-synchronous scheme: Loss-less semi-synchronous replication.



Of course, the previous semi-synchronous scheme is also supported, and MySQL 5.7 introduces a new parameter for control-rpl_semi_sync_master_wait_point

There are two kinds of values for Rpl_semi_sync_master_wait_point:

After_sync: This is the new semi-synchronous scheme, waiting Slave dump before storage commit.
After_commit: The old semi-synchronous solution

III. deployment of semi-synchronous replication without data loss

--Installation Prerequisites
1. MySQL5.5 version or higher
2, the main, from the library have_dynamic_loading system variable value is yes
3. Primary, asynchronous replication deployed

Installing the Rpl_semi_sync_master plugin in the main library

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ' semisync_master.so ';
Query OK, 0 rows affected (0.12 sec)

To see if the plugin was loaded successfully
There are two ways of
1.
Mysql> Show plugins;

| Rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |

2.

Mysql> SELECT plugin_name, plugin_status from INFORMATION_SCHEMA. PLUGINS WHERE plugin_name like '%semi% ';

+----------------------+---------------+
| Plugin_name | Plugin_status |
+----------------------+---------------+
| Rpl_semi_sync_master | ACTIVE |
+----------------------+---------------+
1 row in Set (0.00 sec)

Mysql> Show variables like '%rpl% ';
+-------------------------------------------+------------+
| variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | OFF |
| Rpl_semi_sync_master_timeout | 10000 |
| Rpl_semi_sync_master_trace_level | 32 |
| Rpl_semi_sync_master_wait_for_slave_count | 1 |
| Rpl_semi_sync_master_wait_no_slave | On |
| Rpl_semi_sync_master_wait_point | After_sync |
| Rpl_stop_slave_timeout | 31536000 |
+-------------------------------------------+------------+
7 Rows in Set (0.00 sec)

The main library adds the following configuration:

[Mysqld]
#For GTID Replication
Server_id=18
Gtid_mode=on
Enforce_gtid_consistency=on
#For Semi Sync Config
Rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=3000 # 3 Second
#For Binlog
Log_bin=master-binlog
Binlog_format=row
Log_slave_updates=1

Installing the Rpl_semi_sync_slave plugin from the library

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ' semisync_slave.so ';
Query OK, 0 rows affected (0.06 sec)

Mysql> Show variables like '%rpl% ';
+---------------------------------+----------+
| variable_name | Value |
+---------------------------------+----------+
| rpl_semi_sync_slave_enabled | OFF |
| Rpl_semi_sync_slave_trace_level | 32 |
| Rpl_stop_slave_timeout | 31536000 |
+---------------------------------+----------+
3 rows in Set (0.01 sec)

Add the following configuration from the library:

[Mysqld]
#For GTID Replication
Server_id=19
Gtid_mode=on
Enforce_gtid_consistency=on
#For Binlog
Log_bin=master-binlog
Binlog_format=row
Log_slave_updates=1
#For as slave. Please mark it if switch to master.
Read_only=1
Skip_slave_start=1
Relay_log_recovery=1
# #For Semi Sync Config
Rpl_semi_sync_slave_enabled=1

Start a semi-synchronous replication

After installing the plug-in, the semi-synchronous copy is closed by default, and the semi-synchronous parameters are configured so that you can restart MySQL, or you can manually set the parameters to take effect without restarting MySQL:
Main:
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;

From:
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;

Restart the IO thread on the slave
mysql> STOP SLAVE Io_thread;
mysql> START SLAVE Io_thread;

If there is no restart, the default is asynchronous replication, and after rebooting, slave is registered as the slave role for semi-synchronous replication on master.

The following information is available in the Error.log on master:

2017-02-07t07:42:06.941095z [Note] Start binlog_dump to master_thread_id (+) Slave_server (+), POS (, 4)
2017-02-07t07:42:06.941124z [Note] Start semi-sync binlog_dump to Slave (server_id:19), POS (, 4)

See if the half-sync is running
Main:
Mysql> Show status like ' Rpl_semi_sync_master_status ';
+-----------------------------+-------+
| variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | On |
+-----------------------------+-------+
1 row in Set (0.00 sec)

From:
Mysql> Show status like ' Rpl_semi_sync_slave_status ';
+----------------------------+-------+
| variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | On |
+----------------------------+-------+
1 row in Set (0.00 sec)

These two variables are commonly used to monitor whether Master and slave are running in semi-synchronous replication mode.

When a half-synchronous replication time-out (controlled by the rpl_semi_sync_master_timeout parameter, in milliseconds, by default 10000, or 10s), semi-synchronous replication is temporarily turned off and asynchronous replication is used instead. When the master dump line Cheng all events for a transaction, if the response from the library is received within Rpl_semi_sync_master_timeout, the master and slave are reverted back to semi-synchronous replication.

View the status of a semi-synchronous replication

Mysql> Show status like '%rpl_semi% ';

This article is from the "June My Line" blog, please be sure to keep this source http://junewx.blog.51cto.com/12554494/1895712

Mysql5.7 Gtid-based semi-synchronous replication