在MySQL5.6发布之后添加GTID、多线程等新特性之后,回顾一下从MySQL5.5引入的半同步复制.半同步的原理主要是:数据库执行更改前，确认更改操作至少被写入一台Slave的中继日志.半同步主要是保证数据完整性防止数据丢失.
实验环境MySQL5.6.16
主:
mysql> grant replication client,replication slave on *.* to repl@'192.168.23.%' identified by '
XXXXX';flush privilege;
配置文件添加:
binlog-do-db=percona
查看主的状态:
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000048
Position: 120
Binlog_Do_DB: percona
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
从:
从服务器需要启用中继日志,在配置文件中添加:
relay-log=relay-bin
relay-log-index=relay-bin.index
replicate-do-db=percona
查看从的状态:
mysql> show variables like '%relay%';
+---------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------+-----------------------------+
| max_relay_log_size | 0 |
| relay_log | relay-bin |
| relay_log_basename | /mysql/data/relay-bin |
| relay_log_index | /mysql/data/relay-bin.index |
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | FILE |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| sync_relay_log | 10000 |
| sync_relay_log_info | 10000 |
+---------------------------+-----------------------------+
11 rows in set (0.00 sec)
mysql> change master to master_host='M_IP',master_user='repl',master_password='XXXXX';
启用主从的半同步
主:
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.23 sec)
mysql> set global rpl_semi_sync_master_enabled=on ;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'rpl_%';
+------------------------------------+----------+
| Variable_name | Value |
+------------------------------------+----------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_stop_slave_timeout | 31536000 |
+------------------------------------+----------+
5 rows in set (0.00 sec)
从:
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.23 sec)
mysql> set global rpl_semi_sync_slave_enabled=on ;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'rpl_semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
监控半同步
mysql> show status like 'rpl_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 | 0 |
| 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 | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.01 sec)
参数Rpl_semi_sync_master_clients代表半同步连接的Slave数量
参数Rpl_semi_sync_master_status表示半同步的复制状态,正常状态应该是ON.如果显示OFF,有可能是人为关闭,还有就是因为某些原因恢复到异步复制状态,比如发生超时.
查看Slave同步状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: M_IP
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000048
Read_Master_Log_Pos: 120
Relay_Log_File: relay-bin.000003
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000048
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: percona
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: 120
Relay_Log_Space: 613
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: 2
Master_UUID: 69a73914-62ca-11e3-870f-080027dff846
Master_Info_File: /mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
今天回顾MySQL半同步基础知识.先到此吧,明天继续.^_^
