程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL5.5配置安裝半同步復制

MySQL5.5配置安裝半同步復制

編輯:MySQL綜合教程

確認master和slave上是否開啟have_dynamic_loading

master

MASTER@root@(none) 10:54:58>show variables like 'have_dynamic_loading';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| have_dynamic_loading | YES   |
+----------------------+-------+
slave
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     |
+--------------------------+--------+--------------------+--------------------+---------+

使用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     |
+--------------------------+--------+--------------------+-------------------+---------+

分別在master和slave的my.cnf裡添加以下配置

master

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=on
slave
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)
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
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 |
+----------------------------+-------------+

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved