mysql 5.5半同步复制_MySQL5.5配置安装半同步复制
確認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 |
+----------------------+-------+
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 |
+----------------------+-------+
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里添加以下配置
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
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=onslave
rpl_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 5.5半同步复制_MySQL5.5配置安装半同步复制的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: hive 0.11 mysql_Hive
- 下一篇: mysql ereg_php中正则表达式