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

one。 Semi-synchronous replication

Characteristics of semi-synchronous replication

1. Compared with traditional asynchronous replication, semi-synchronous replication selects one node among multiple Slave nodes for semi-synchronous replication. In other words, when Master commits a transaction, after the Slave side of the semi-synchronous replication returns a synchronously completed Ack packet, the server will return to the user that the transaction was committed successfully, while other nodes use the traditional asynchronous replication method for synchronization.

two。 Semi-synchronous replication is based on asynchronous replication, that is, it needs to be configured to asynchronous replication before configuring semi-synchronous replication.

3. Semi-synchronous replication can ensure that when the primary node fails, there is always one node with the same data as the primary node. In this way, when switching, you can more quickly set this Slave node as the primary node to use.

5.7

5.6

Semi-synchronous replication performance

-semi-synchronous replication has a lower performance than asynchronous replication.

-Mode after_sync has higher performance than after_commit

two。 Installation conditions for semi-synchronous replication

1. Must be MySQL5.5 or above

2.MySQL must have automatic loading function, that is, the have_dynamic_loading variable is YES

Mysql > show variables like 'have_dynamic_loading'

+-+ +

| | Variable_name | Value |

+-+ +

| | have_dynamic_loading | YES |

+-+ +

3. Master-slave replication has been configured (asynchronous replication)

three。 Configuration

1. Set semi-synchronous mode (main library, 5.7 is the default)

SET rpl_semi_sync_master_wait_point= AFTER_SYNC

two。 Load plug-in

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; (main library)

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; (slave library)

Show plugins; (confirm View)

3. Start semi-synchronous replication

Main library

SET GLOBAL rpl_semi_sync_master_enabled = 1

Slave library

SET GLOBAL rpl_semi_sync_slave_enabled = 1

The above startup mode is operated on the command line or can be written in the configuration file.

Plugin_dir=/usr/local/mysql/lib/plugin

Plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

Loose_rpl_semi_sync_master_enabled = 1

Loose_rpl_semi_sync_slave_enabled = 1

Loose_rpl_semi_sync_master_timeout = 5000

Rpl_semi_sync_master_wait_point = AFTER_SYNC or AFTER_COMMIT

Rpl_semi_sync_master_wait_for_slave_count = 1

Restart the slave server IO thread and manually switch from asynchronous mode to semi-synchronous mode. After restarting, slave registers as a semi-synchronous replicated slave role on master.

STOP SLAVE IO_THREAD

START SLAVE IO_THREAD

4. Confirm the running status of semi-synchronous replication

Main library

Mysql > show status like 'Rpl_semi_sync_master_status'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_master_status | ON |

+-+ +

Slave library

Mysql > show status like 'Rpl_semi_sync_slave_status'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_slave_status | ON |

+-+ +

5. Parameters related to semi-synchronous replication

Main library

Rpl_semi_sync_master_status: indicates whether the primary server is in asynchronous or semi-synchronous replication mode.

Rpl_semi_sync_master_clients: shows how many slaves are configured for semi-synchronous replication mode.

Rpl_semi_sync_master_yes_tx: displays the number of successful submissions confirmed from the server.

Rpl_semi_sync_master_no_tx: displays the number of unsuccessful submissions confirmed from the server.

Rpl_semi_sync_master_timeout=1000 defaults to 1000 milliseconds, or 10-second timeout, and switches to asynchronous replication.

Slave library

Rpl_semi_sync_slave_status indicates that semi-synchronous replication is enabled from the server

6. test

Simulated semi-synchronous replication slave server downtime

-turn off semi-synchronous slave server

-the view of the main library has not changed after it has been closed.

Mysql > show status like'% semi%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_master_clients | 1 | |

| | Rpl_semi_sync_master_net_avg_wait_time | 0 | |

| | Rpl_semi_sync_master_net_wait_time | 0 | |

| | Rpl_semi_sync_master_net_waits | 2 | |

| | Rpl_semi_sync_master_no_times | 0 | |

| | Rpl_semi_sync_master_no_tx | 0 | |

| | Rpl_semi_sync_master_status | ON |

| | Rpl_semi_sync_master_timefunc_failures | 0 | |

| | Rpl_semi_sync_master_tx_avg_wait_time | 5754 | |

| | Rpl_semi_sync_master_tx_wait_time | 11509 | |

| | Rpl_semi_sync_master_tx_waits | 2 | |

| | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | |

| | Rpl_semi_sync_master_wait_sessions | 0 | |

| | Rpl_semi_sync_master_yes_tx | 2 | |

+-+ +

-the main library executes the transaction and waits for 10 seconds

Mysql > delete from wwj.t3 where idt3=2

Query OK, 1 row affected (10.03 sec)

-check again

Mysql > show status like'% semi%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_master_clients | 1 | |

| | Rpl_semi_sync_master_net_avg_wait_time | 0 | |

| | Rpl_semi_sync_master_net_wait_time | 0 | |

| | Rpl_semi_sync_master_net_waits | 3 | |

| | Rpl_semi_sync_master_no_times | 1 | |

| | Rpl_semi_sync_master_no_tx | 1 | |

| | Rpl_semi_sync_master_status | OFF |

| | Rpl_semi_sync_master_timefunc_failures | 0 | |

| | Rpl_semi_sync_master_tx_avg_wait_time | 5754 | |

| | Rpl_semi_sync_master_tx_wait_time | 11509 | |

| | Rpl_semi_sync_master_tx_waits | 2 | |

| | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | |

| | Rpl_semi_sync_master_wait_sessions | 0 | |

| | Rpl_semi_sync_master_yes_tx | 2 | |

+-+ +

-restart semi slave and semi-synchronously.

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.