1. Environment
| Host | master | slave |
| Edition | 5.7.14 | 5.7.14 |
| ip | 192.168.26.233 | 192.168.26.108 |
| port | 3306 | 3306 |
1. Install related plug-ins
show plugins; view module
help --uninstall; view the uninstall module
Master Plug-in Installation -- Install Semsync_master.so Plug-in
mysql>install plugin rpl_semi_sync_master soname 'semisync_master.so'; Query OK, 0 rows affected (0.03 sec)
Slve Plug-in Installation: - - Install Semsync_slave.so Plug-in
root@localhost [zw3306]>install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; Query OK, 0 rows affected (0.00 sec) root@localhost [zw3306]>install plugin rpl_semi_sync_master soname 'semisync_master.so'; Query OK, 0 rows affected (0.00 sec)
2. Modified parameters:
set global rpl_semi_sync_master_enabled=1; set global rpl_semi_sync_master_timeout=1000; set global rpl_semi_sync_slave_enabled=1;
It can also be written directly to the configuration file my.cnf
master:
[mysqld] rpl_semi_sync_master_enabled = 1 rpl_semi_sync_master_timeout = 1000 # 1 second
Note: Description of relevant parameters
rpl_semi_sync_master_timeout=milliseconds
Set this parameter value (ms) to prevent blocking of semi-synchronous replication without acknowledgement. If Master does not receive any acknowledgement before the timeout, it will revert to normal asynchronous replication and continue to perform non-semi-synchronous replication.
rpl_semi_sync_master_wait_no_slave={ON|OFF}
If a transaction is committed, but Master does not have any Slave connection, then it is impossible to send the transaction to other places for protection. By default, Master will continue to wait for Slave connections within the time limit and confirm that the transaction has been correctly written to disk.
You can use this parameter option to turn off this behavior, in which case Master will revert to asynchronous replication without a Slave connection.
slave:
[mysqld] rpl_semi_sync_slave_enabled = 1
Modified parameters need to be restarted:
View the modified parameters
master:
mysql> show global variables like '%rpl_semi%'; +-------------------------------------------+------------+ | Variable_name | Value | +-------------------------------------------+------------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 1000 | | 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 | +-------------------------------------------+------------+ 6 rows in set (0.00 sec)
slave:
root@localhost [(none)]>show global variables like '%rpl_semi%'; +-------------------------------------------+------------+ | Variable_name | Value | +-------------------------------------------+------------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 1000 | | 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_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_trace_level | 32 | +-------------------------------------------+------------+ 8 rows in set (0.00 sec)
If: if the original replication structure has been built, it is very simple:
stop slave io_thread;
start slave io_thread;
3. Synchronize
change master to master_host='192.168.26.233', master_port=3306, master_user='repl',master_password='repl', master_auto_position=1; root@localhost [(none)]>start slave; Query OK, 0 rows affected (0.01 sec) root@localhost [(none)]>show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.26.233 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 194 Relay_Log_File: relay-bin.000004 Relay_Log_Pos: 407 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 194 Relay_Log_Space: 861 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 3306100 Master_UUID: 7e354a2c-6f5f-11e6-997d-005056a36f08 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 7e354a2c-6f5f-11e6-997d-005056a36f08:1-9 Executed_Gtid_Set: 7e354a2c-6f5f-11e6-997d-005056a36f08:1-9, ba0d5587-74d6-11e6-ab5c-005056a3f46e:1-2 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified
5. Check if slave has data.
root@localhost [zw3306]>show tables; +------------------+ | Tables_in_zw3306 | +------------------+ | t1 | +------------------+ 1 row in set (0.00 sec) root@localhost [zw3306]>select * from t1; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | +------+ 4 rows in set (0.00 sec)
6. How to confirm synchronization or semi-synchronization?
show global variables like '%semi%'; show global status like '%semi%';
master:
root@localhost [zw3306]>show global status like '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | How many are there? Semi-sync Reserve | Rpl_semi_sync_master_net_avg_wait_time | 0 | Average waiting time for standby response after transaction submission | Rpl_semi_sync_master_net_wait_time | 0 | Total number of times waiting for network response | Rpl_semi_sync_master_net_waits | 7 | Total network latency | Rpl_semi_sync_master_no_times | 0 | A total of several times from Semi-sync Drop back to normal | Rpl_semi_sync_master_no_tx | 0 | Number of transactions that the library did not respond in time,If that's a big value, there's a problem. | Rpl_semi_sync_master_status | ON | On the main library Semi-sync Is it open properly? | Rpl_semi_sync_master_timefunc_failures | 0 | Number of times the time function does not work properly | Rpl_semi_sync_master_tx_avg_wait_time | 410 | open Semi-sync，Average waiting time for transaction return | Rpl_semi_sync_master_tx_wait_time | 2876 | Total transaction waiting time for standby response | Rpl_semi_sync_master_tx_waits | 7 | Total number of transactions waiting for a backup response | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | Change the number of times the current minimum binary log is waiting | Rpl_semi_sync_master_wait_sessions | 0 | There are currently several threads waiting for a standby response | Rpl_semi_sync_master_yes_tx | 7 | Semi-sync In mode, the number of successful transactions +--------------------------------------------+-------+ 15 rows in set (0.00 sec) root@localhost [zw3306]>show global variables like '%semi%'; +-------------------------------------------+------------+ | Variable_name | Value | +-------------------------------------------+------------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 1000 | | 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_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_trace_level | 32 | +-------------------------------------------+------------+ 8 rows in set (0.00 sec)
Rpl_semi_sync_master_no_tx
There are other reasons.
mysql> show global 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 | 17 | | Rpl_semi_sync_master_no_times | 1 | | Rpl_semi_sync_master_no_tx | 10 | Not replicated semi-synchronously | Rpl_semi_sync_master_status | OFF | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 410 | | Rpl_semi_sync_master_tx_wait_time | 2876 | | Rpl_semi_sync_master_tx_waits | 7 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 7 | +--------------------------------------------+-------+ 14 rows in set (0.01 sec)
Operating on 10 things, you can find that all of them are Rpl_semi_sync_master_no_tx
Description of parameters:
master receives two semi-synchronous slave ack s within a specified time before commit
set global rpl_semi_sync_master_wait_for_slave_count=2;
Timeout, more than 1 second means timeout
set global Rpl_semi_sync_master_net_wait_time=1000