在mysql 5.7 中如何開啟半同步復(fù)制

show?plugins;??查看模塊  help?--uninstall;?查看卸載模塊

master:  mysql>?install?plugin?rpl_semi_sync_master?soname?'semisync_master.so';??--安裝?semisync_master.so插件?  Query?OK,?0?rows?affected?(0.03?sec)  slave:  root@localhost?[zw3306]>install?plugin?rpl_semi_sync_slave?soname?'semisync_slave.so';?--安裝?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)

set?global?rpl_semi_sync_master_enabled=1;  set?global?rpl_semi_sync_master_timeout=1000;  set?global?rpl_semi_sync_slave_enabled=1;

master:  [mysqld]  rpl_semi_sync_master_enabled?=?1?  rpl_semi_sync_master_timeout?=?1000?#?1?second  slave：  [mysqld]  rpl_semi_sync_slave_enabled?=?1

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%';

stop?slave?io_thread;  start?slave?io_thread;

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?statusG;  ***************************?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

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)

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?????|?有多少個(gè)Semi-sync的備庫  |?Rpl_semi_sync_master_net_avg_wait_time?????|?0?????|?事務(wù)提交后，等待備庫響應(yīng)的平均時(shí)間  |?Rpl_semi_sync_master_net_wait_time?????????|?0?????|?等待網(wǎng)絡(luò)響應(yīng)的總次數(shù)  |?Rpl_semi_sync_master_net_waits?????????????|?7?????|?總的網(wǎng)絡(luò)等待時(shí)間  |?Rpl_semi_sync_master_no_times??????????????|?0?????|?一共有幾次從Semi-sync跌回普通狀態(tài)  |?Rpl_semi_sync_master_no_tx?????????????????|?0?????|?庫未及時(shí)響應(yīng)的事務(wù)數(shù),如果這個(gè)值很大就有問題  |?Rpl_semi_sync_master_status????????????????|?ON????|?主庫上Semi-sync是否正常開啟  |?Rpl_semi_sync_master_timefunc_failures?????|?0?????|?時(shí)間函數(shù)未正常工作的次數(shù)  |?Rpl_semi_sync_master_tx_avg_wait_time??????|?410???|?開啟Semi-sync，事務(wù)返回需要等待的平均時(shí)間  |?Rpl_semi_sync_master_tx_wait_time??????????|?2876??|?事務(wù)等待備庫響應(yīng)的總時(shí)間  |?Rpl_semi_sync_master_tx_waits??????????????|?7?????|?事務(wù)等待備庫響應(yīng)的總次數(shù)  |?Rpl_semi_sync_master_wait_pos_backtraverse?|?0?????|?改變當(dāng)前等待最小二進(jìn)制日志的次數(shù)  |?Rpl_semi_sync_master_wait_sessions?????????|?0?????|?當(dāng)前有幾個(gè)線程在等備庫響應(yīng)  |?Rpl_semi_sync_master_yes_tx????????????????|?7?????|?Semi-sync模式下，成功的事務(wù)數(shù)  +--------------------------------------------+-------+  15?rows?in?set?(0.00?sec)

root@localhost?[zw3306]>show?global?variables?like?'%semi%';

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????|?不是用半同步復(fù)制的  |?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)

操作10個(gè)事物，可以發(fā)現(xiàn)都是??Rpl_semi_sync_master_no_tx?  master接收到N個(gè)slave的應(yīng)答后，才commit事物，等待1s用戶可以設(shè)置應(yīng)道slave的數(shù)量。  rpl_semi_sync_master_wait_for_slave_count=1?默認(rèn)是1?  set?global?rpl_semi_sync_master_wait_for_slave_count=2;