MySQL5.5配置安装半同步复制
jopen
11年前
确认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=onslave
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 | +----------------------------+-------------+
来自：http://blog.csdn.net/staricqxyz/article/details/18913483