首页 / 数据库 / MySQL / MySQL5.5配置安装半同步复制

确认master和slave上是否开启have_dynamic_loadingmasterMASTER@root@（none） 10:54:58>show variables like "have_dynamic_loading";
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| have_dynamic_loading | YES |
+----------------------+-------+
MASTER@root@（none） 10:54:58>show variables like "have_dynamic_loading";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| have_dynamic_loading | YES |
+----------------------+-------+slaveSLAVE@root@test 10:47:52>show variables like "have_dynamic_loading";
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| have_dynamic_loading | YES |
+----------------------+-------+
SLAVE@root@test 10:47:52>show variables like "have_dynamic_loading";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| have_dynamic_loading | YES |
+----------------------+-------+
使用root在master上安装半同步插件MASTER@root@（none） 10:58:14>
MASTER@root@（none） 10:58:14>install plugin rpl_semi_sync_master soname "semisync_master.so";
Query OK, 0 rows affected （0.01 sec）MASTER@root@（none） 10:58:22>show plugins;
+--------------------------+--------+--------------------+--------------------+---------+
| Name | Status | Type | Library | License |
+--------------------------+--------+--------------------+--------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
+--------------------------+--------+--------------------+--------------------+---------+
MASTER@root@（none） 10:58:14>
MASTER@root@（none） 10:58:14>install plugin rpl_semi_sync_master soname "semisync_master.so";
Query OK, 0 rows affected （0.01 sec）MASTER@root@（none） 10:58:22>show plugins;
+--------------------------+--------+--------------------+--------------------+---------+
| Name                   | Status | Type             | Library            | License |
+--------------------------+--------+--------------------+--------------------+---------+
| binlog                 | ACTIVE | STORAGE ENGINE   | NULL             | GPL   |
| mysql_native_password    | ACTIVE | AUTHENTICATION   | NULL             | GPL   |
| mysql_old_password     | ACTIVE | AUTHENTICATION   | NULL             | GPL   |
| MRG_MYISAM             | ACTIVE | STORAGE ENGINE   | NULL             | GPL   |
| CSV                      | ACTIVE | STORAGE ENGINE   | NULL             | GPL   |
| MEMORY                 | ACTIVE | STORAGE ENGINE   | NULL             | GPL   |
| MyISAM                 | ACTIVE | STORAGE ENGINE   | NULL             | GPL   |
| InnoDB                 | ACTIVE | STORAGE ENGINE   | NULL             | GPL   |
| INNODB_TRX             | ACTIVE | INFORMATION SCHEMA | NULL             | GPL   |
| INNODB_LOCKS           | ACTIVE | INFORMATION SCHEMA | NULL             | GPL   |
| INNODB_LOCK_WAITS        | ACTIVE | INFORMATION SCHEMA | NULL             | GPL   |
| INNODB_CMP             | ACTIVE | INFORMATION SCHEMA | NULL             | GPL   |
| INNODB_CMP_RESET       | ACTIVE | INFORMATION SCHEMA | NULL             | GPL   |
| INNODB_CMPMEM            | ACTIVE | INFORMATION SCHEMA | NULL             | GPL   |
| INNODB_CMPMEM_RESET      | ACTIVE | INFORMATION SCHEMA | NULL             | GPL   |
| INNODB_BUFFER_PAGE     | ACTIVE | INFORMATION SCHEMA | NULL             | GPL   |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL             | GPL   |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL             | GPL   |
| BLACKHOLE                | ACTIVE | STORAGE ENGINE   | NULL             | GPL   |
| ARCHIVE                  | ACTIVE | STORAGE ENGINE   | NULL             | GPL   |
| FEDERATED                | ACTIVE | STORAGE ENGINE   | NULL             | GPL   |
| PERFORMANCE_SCHEMA     | ACTIVE | STORAGE ENGINE   | NULL             | GPL   |
| partition                | ACTIVE | STORAGE ENGINE   | NULL             | GPL   |
| rpl_semi_sync_master   | ACTIVE | REPLICATION        | semisync_master.so | GPL   |
+--------------------------+--------+--------------------+--------------------+---------+
使用root在slave上安装半同步插件SLAVE@root@test 10:55:18>install plugin rpl_semi_sync_slave soname "semisync_slave.so";
Query OK, 0 rows affected （0.01 sec）SLAVE@root@test 10:59:45>show plugins;
+--------------------------+--------+--------------------+-------------------+---------+
| Name | Status | Type | Library | License |
+--------------------------+--------+--------------------+-------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |
+--------------------------+--------+--------------------+-------------------+---------+
SLAVE@root@test 10:55:18>install plugin rpl_semi_sync_slave soname "semisync_slave.so";
Query OK, 0 rows affected （0.01 sec）SLAVE@root@test 10:59:45>show plugins;
+--------------------------+--------+--------------------+-------------------+---------+
| Name                   | Status | Type             | Library         | License |
+--------------------------+--------+--------------------+-------------------+---------+
| binlog                 | ACTIVE | STORAGE ENGINE   | NULL              | GPL   |
| mysql_native_password    | ACTIVE | AUTHENTICATION   | NULL              | GPL   |
| mysql_old_password     | ACTIVE | AUTHENTICATION   | NULL              | GPL   |
| MRG_MYISAM             | ACTIVE | STORAGE ENGINE   | NULL              | GPL   |
| CSV                      | ACTIVE | STORAGE ENGINE   | NULL              | GPL   |
| MEMORY                 | ACTIVE | STORAGE ENGINE   | NULL              | GPL   |
| MyISAM                 | ACTIVE | STORAGE ENGINE   | NULL              | GPL   |
| InnoDB                 | ACTIVE | STORAGE ENGINE   | NULL              | GPL   |
| INNODB_TRX             | ACTIVE | INFORMATION SCHEMA | NULL              | GPL   |
| INNODB_LOCKS           | ACTIVE | INFORMATION SCHEMA | NULL              | GPL   |
| INNODB_LOCK_WAITS        | ACTIVE | INFORMATION SCHEMA | NULL              | GPL   |
| INNODB_CMP             | ACTIVE | INFORMATION SCHEMA | NULL              | GPL   |
| INNODB_CMP_RESET       | ACTIVE | INFORMATION SCHEMA | NULL              | GPL   |
| INNODB_CMPMEM            | ACTIVE | INFORMATION SCHEMA | NULL              | GPL   |
| INNODB_CMPMEM_RESET      | ACTIVE | INFORMATION SCHEMA | NULL              | GPL   |
| INNODB_BUFFER_PAGE     | ACTIVE | INFORMATION SCHEMA | NULL              | GPL   |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL              | GPL   |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL              | GPL   |
| BLACKHOLE                | ACTIVE | STORAGE ENGINE   | NULL              | GPL   |
| ARCHIVE                  | ACTIVE | STORAGE ENGINE   | NULL              | GPL   |
| FEDERATED                | ACTIVE | STORAGE ENGINE   | NULL              | GPL   |
| PERFORMANCE_SCHEMA     | ACTIVE | STORAGE ENGINE   | NULL              | GPL   |
| partition                | ACTIVE | STORAGE ENGINE   | NULL              | GPL   |
| rpl_semi_sync_slave      | ACTIVE | REPLICATION        | semisync_slave.so | GPL   |
+--------------------------+--------+--------------------+-------------------+---------+
分别在master和slave的my.cnf里添加以下配置 masterrpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000
rpl_semi_sync_master_trace_level=32
rpl_semi_sync_master_wait_no_slave=on
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000
rpl_semi_sync_master_trace_level=32
rpl_semi_sync_master_wait_no_slave=onslaverpl_semi_sync_slave_enabled=1
rpl_semi_sync_slave_enabled=1
master日志140203 23:06:17 [Note] Semi-sync replication initialized for transactions.
140203 23:06:17 [Note] Semi-sync replication enabled on the master.
140203 23:06:17 [Note] Server hostname （bind-address）: "0.0.0.0"; port: 30307
140203 23:06:17 [Note] - "0.0.0.0" resolves to "0.0.0.0";
140203 23:06:17 [Note] Server socket created on IP: "0.0.0.0".
140203 23:06:17 [Warning] "proxies_priv" entry "@ root@mynode1" ignored in --skip-name-resolve mode.
140203 23:06:17 [Note] Event Scheduler: Loaded 0 events
140203 23:06:17 [Note] /service/mysql/bin/mysqld: ready for connections.
Version: "5.5.34-log" socket: "/data/mysql/mysql.sock" port: 30307 MASTER_DB
140203 23:06:17 [Note] Event Scheduler: scheduler thread started with id 1
140203 23:06:20 [Note] Start semi-sync binlog_dump to slave （server_id: 2）, pos（binlog-master.000010, 290）
140203 23:06:17 [Note] Semi-sync replication initialized for transactions.
140203 23:06:17 [Note] Semi-sync replication enabled on the master.
140203 23:06:17 [Note] Server hostname （bind-address）: "0.0.0.0"; port: 30307
140203 23:06:17 [Note] - "0.0.0.0" resolves to "0.0.0.0";
140203 23:06:17 [Note] Server socket created on IP: "0.0.0.0".
140203 23:06:17 [Warning] "proxies_priv" entry "@ root@mynode1" ignored in --skip-name-resolve mode.
140203 23:06:17 [Note] Event Scheduler: Loaded 0 events
140203 23:06:17 [Note] /service/mysql/bin/mysqld: ready for connections.
Version: "5.5.34-log"  socket: "/data/mysql/mysql.sock"  port: 30307  MASTER_DB
140203 23:06:17 [Note] Event Scheduler: scheduler thread started with id 1
140203 23:06:20 [Note] Start semi-sync binlog_dump to slave （server_id: 2）, pos（binlog-master.000010, 290）slave日志140203 23:06:20 [Note] Slave I/O thread: Start semi-sync replication to master "rep@mynode1:30307" in log "binlog-master.000010" at position 290
140203 23:06:20 [Note] Slave SQL thread initialized, starting replication in log "binlog-master.000010" at position 290, relay log "./mynode2-relay-bin.000029" position: 440
140203 23:06:20 [Note] Event Scheduler: scheduler thread started with id 1
140203 23:06:20 [Note] Slave I/O thread: connected to master "rep@mynode1:30307",replication started in log "binlog-master.000010" at position 290
140203 23:06:20 [Note] Slave I/O thread: Start semi-sync replication to master "rep@mynode1:30307" in log "binlog-master.000010" at position 290
140203 23:06:20 [Note] Slave SQL thread initialized, starting replication in log "binlog-master.000010" at position 290, relay log "./mynode2-relay-bin.000029" position: 440
140203 23:06:20 [Note] Event Scheduler: scheduler thread started with id 1
140203 23:06:20 [Note] Slave I/O thread: connected to master "rep@mynode1:30307",replication started in log "binlog-master.000010" at position 290 
MASTER@root@test 11:10:41>show global status like "rpl%";
+--------------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 619 |
| Rpl_semi_sync_master_net_wait_time | 619 |
| Rpl_semi_sync_master_net_waits | 1 |
| 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 | 734 |
| Rpl_semi_sync_master_tx_wait_time | 734 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
| Rpl_status | AUTH_MASTER |
+--------------------------------------------+-------------+
MASTER@root@test 11:10:41>show global status like "rpl%";
+--------------------------------------------+-------------+
| Variable_name                              | Value     |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients             | 1         |
| Rpl_semi_sync_master_net_avg_wait_time   | 619       |
| Rpl_semi_sync_master_net_wait_time       | 619       |
| Rpl_semi_sync_master_net_waits           | 1         |
| 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      | 734       |
| Rpl_semi_sync_master_tx_wait_time          | 734       |
| Rpl_semi_sync_master_tx_waits              | 1         |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0         |
| Rpl_semi_sync_master_wait_sessions       | 0         |
| Rpl_semi_sync_master_yes_tx                | 1         |
| Rpl_status                               | AUTH_MASTER |
+--------------------------------------------+-------------+
SLAVE@root@test 11:10:02>show global status like "rpl%";
+----------------------------+-------------+
| Variable_name | Value |
+----------------------------+-------------+
| Rpl_semi_sync_slave_status | ON |
| Rpl_status | AUTH_MASTER |
+----------------------------+-------------+相关阅读：说说MySQL的半同步复制 http://www.linuxidc.com/Linux/2013-06/86627.htmMySQL基于SSL的半同步复制 http://www.linuxidc.com/Linux/2012-12/75471.htmMySQL主从，半同步，主主复制 http://www.linuxidc.com/Linux/2012-09/70332.htmMySQL服务器复制的主从架构半同步的实现 http://www.linuxidc.com/Linux/2012-04/58568.htmMySQL半同步复制（Semi-synchronous Replication） http://www.linuxidc.com/Linux/2012-03/57706.htmOracle rman 备份日志单独备份和交叉校验统计MySQL binlog中的DML语句相关资讯      MySQL同步复制  MySQL半同步复制 

MySQL半同步复制详解  （08月05日） MySQL半同步复制实现  （07月11日） MySQL半同步复制的实现  （06/26/2014 19:26:18）

MySQL半同步复制实践  （07月25日） MySQL5.6配置同步复制的新方法以及  （10/13/2014 19:18:46） MySQL 5.5半同步复制  （05/25/2014 10:06:56）

本文评论 查看全部评论 （0）

表情： 姓名： 匿名字数 </textar </div> </div> </div> <div class="copyright"> <div id="footbar"> 版权所有©石家庄振强科技有限公司2024 <a href="https://beian.miit.gov.cn" target="_blank">冀ICP备08103738号-5</a> <a href="/storage/sitemap.xml">网站地图</a> </div> </div> <script type="text/javascript" src="//cpro.baidustatic.com/cpro/ui/cm.js"></script> <script> var _mtj = _mtj || []; (function () { var mtj = document.createElement("script"); mtj.src = "https://node12.aizhantj.com:21233/tjjs/?k=p2tceukth5c"; var s = document.getElementsByTagName("script")[0]; s.parentNode.insertBefore(mtj, s); })(); </script> </body> </html>