专职DBA-MySQL主从半同步复制
MySQL复制的多种工作方式：
1.异步复制 async replication
默认情况下，MySQL的复制就是异步的，在master上将所有的更新操作都写入binlog之后并不关心所有的更新是否都复制了slave服务器的中继日志中，以及是否应用到了slave数据库里主库也不关心。
异步复制的明显优势就是复制效率很高，但是其缺点也十分明显，那就是不同的服务器进行复制时可能会存在数据不一致的问题，甚至还可能会丢失数据；异步复制合适于常规普通的互联网应用场景。
2.全同步复制 full sync replication
全同步复制是指当主库执行完一个事务后，需要确保所有的从库都执行了该事务才返回给客户端。因为需要等待所有的从库都执行完该事务才能返回，所以全同步复制的性能必然会受到所有从库更新的拖累。
同步复制的优点是能够确保将数据实时复制到所有的从库，但是主库需要等待所有从库的写入完成，这会影响主库的更新效率，可能还会导致主库的更新延迟，适合于对数据一致性要求比较高的应用场景。
3.半同步复制 semi-sync replication
半同步复制介于异步复制和全同步复制之间，主库在执行完客户端提交的事务之后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才能返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，半同步复制最好在低延时的复制节点之间使用。
引入半同步复制的目的是为了保证在master出现故障的时候，至少有一台slave数据库是完整的。在超时的情况下半同步复制也会转换为异步复制，以确保主库业务的正常更新。
半同步复制模式在一定程度上可以保证所提交的事务至少会发送给一个从库，但仅仅是保证事务已经发送到了从库，并不能确保已经在从库上应用完成。
MySQL主从半同步复制实验
本次实验环境延用MySQL主从异步复制的搭建环境
主机规划
----------------------------------------------------------------
数据库角色 主机名 bond0(SSH连接IP) bond1(内网通信IP)
----------------------------------------------------------------
主库1(master) db01 10.0.0.11 192.168.10.11
----------------------------------------------------------------
主库2(slave) db02 10.0.0.12 192.168.10.12
----------------------------------------------------------------
半同步的插件在MySQL的安装目录下
[root@db01 ~]# ls -l /u01/app/mysql/mysql-5.7.26-linux-glibc2.12-x86_64/lib/plugin/semisync_*
-rwxr-xr-x 1 mysql mysql 708906 Apr 13 22:11 /u01/app/mysql/mysql-5.7.26-linux-glibc2.12-x86_64/lib/plugin/semisync_master.so
-rwxr-xr-x 1 mysql mysql 152309 Apr 13 22:11 /u01/app/mysql/mysql-5.7.26-linux-glibc2.12-x86_64/lib/plugin/semisync_slave.so
[root@db01 ~]# mysqld --defaults-file=/data/mysql/3306/my.cnf &
[root@db01 ~]# mysql -S /data/mysql/3306/mysql.sock -p
Enter password:
Master [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)
Master [(none)]> select * from mysql.plugin;
+----------------------+--------------------+
| name | dl |
+----------------------+--------------------+
| rpl_semi_sync_master | semisync_master.so |
+----------------------+--------------------+
1 row in set (0.01 sec)
Master [(none)]> show plugins;
+----------------------------+----------+--------------------+--------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+--------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | 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_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_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 |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
+----------------------------+----------+--------------------+--------------------+---------+
45 rows in set (0.00 sec)
Master [(none)]> show global variables like "%rpl_%";
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
| rpl_stop_slave_timeout | 31536000 |
+-------------------------------------------+------------+
7 rows in set (0.01 sec)
Master [(none)]> set global rpl_semi_sync_master_enabled = on;
Query OK, 0 rows affected (0.00 sec)
Master [(none)]> show global 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_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
| rpl_stop_slave_timeout | 31536000 |
+-------------------------------------------+------------+
7 rows in set (0.01 sec)
超时时间默认是10秒
如果你想永久生效可以my.cnf配置文件里
[mysqld]下面
rpl_semi_sync_master_enabled = on
rpl_semi_sync_master_timeout = 10000
但是我不建议写在配置文件里，我要的是数据库可控性强。
查看主库半同步复制状态
Master [(none)]> show global status like "%rpl_%";
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| 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)
Master [(none)]> show global variables like "%semi%";
+-------------------------------------------+------------+
| 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_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)
[root@db02 ~]# mysqld --defaults-file=/data/mysql/3306/my.cnf &
[root@db02 ~]# mysql -S /data/mysql/3306/mysql.sock -p
Enter password:
Slave [(none)]> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.02 sec)
Slave [(none)]> select * from mysql.plugin;
+---------------------+-------------------+
| name | dl |
+---------------------+-------------------+
| rpl_semi_sync_slave | semisync_slave.so |
+---------------------+-------------------+
1 row in set (0.01 sec)
Slave [(none)]> show plugins;
+----------------------------+----------+--------------------+-------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+-------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | 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_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_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 |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |
+----------------------------+----------+--------------------+-------------------+---------+
45 rows in set (0.00 sec)
Slave [(none)]> show global variables like "%rpl%";
+---------------------------------+----------+
| Variable_name | Value |
+---------------------------------+----------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
| rpl_stop_slave_timeout | 31536000 |
+---------------------------------+----------+
3 rows in set (0.01 sec)
Slave [(none)]> set global rpl_semi_sync_slave_enabled = on;
Query OK, 0 rows affected (0.00 sec)
Slave [(none)]> show global variables like "%rpl%";
+---------------------------------+----------+
| Variable_name | Value |
+---------------------------------+----------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
| rpl_stop_slave_timeout | 31536000 |
+---------------------------------+----------+
3 rows in set (0.00 sec)
当然你也可以写到my.cnf配置文件里
Slave [(none)]> stop slave io_thread;
Query OK, 0 rows affected (0.01 sec)
Slave [(none)]> start slave io_thread;
Query OK, 0 rows affected (0.01 sec)
查看从库半同步复制状态
Slave [(none)]> show global status like "%rpl_%";
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.01 sec)
查看从库半同步复制插件的参数
Slave [(none)]> show variables like '%rpl_%';
+---------------------------------+----------+
| Variable_name | Value |
+---------------------------------+----------+
| rpl_semi_sync_slave_enabled | ON | #on表示已激活半同步插件
| rpl_semi_sync_slave_trace_level | 32 | #从不半同步复制的调试级别
| rpl_stop_slave_timeout | 31536000 |
+---------------------------------+----------+
3 rows in set (0.00 sec)
Master [(none)]> show global status like "%rpl_%";
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 | #有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.03 sec)
Master [(none)]> show global 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_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
| rpl_stop_slave_timeout | 31536000 |
+-------------------------------------------+------------+
7 rows in set (0.01 sec)
测试主从半同步复制
Slave [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.11
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 194
Relay_Log_File: db02-relay-bin.000006
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 194
Relay_Log_Space: 739
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: 113306
Master_UUID: 7c145945-a680-11e9-baea-000c29a14cf7
Master_Info_File: /data/mysql/3306/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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: 7c145945-a680-11e9-baea-000c29a14cf7:1-11
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.01 sec)
Master [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| app |
| app01 |
| app02 |
| app03 |
| app04 |
| mysql |
| performance_schema |
| shenzhen |
| sys |
+--------------------+
10 rows in set (0.00 sec)
Master [shenzhen]> use shenzhen;
Database changed
Master [shenzhen]> show tables;
+--------------------+
| Tables_in_shenzhen |
+--------------------+
| t1 |
+--------------------+
1 row in set (0.00 sec)
Master [shenzhen]> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
Master [shenzhen]> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
Master [shenzhen]> insert into t1(id) values(3);
Query OK, 1 row affected (0.01 sec)
Master [shenzhen]> insert into t1(id) values(4);
Query OK, 1 row affected (0.02 sec)
Master [shenzhen]> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
Master [shenzhen]> show global status like "%rpl_%";
+--------------------------------------------+-------+
| 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 | 2 |
| 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 | 4897 |
| Rpl_semi_sync_master_tx_wait_time | 9794 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
14 rows in set (0.03 sec)
Slave [shenzhen]> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
测试半同步复制超时等待
Slave [shenzhen]> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)
半同步复制与IO线程直接相关，与SQL线程无关。
Master [shenzhen]> insert into t1(id) values(5);
Query OK, 1 row affected (10.01 sec)
等待了十秒钟
半同步复制状态自动转换为异步复制了，所以再次更新数据就很快了。
Master [shenzhen]> insert into t1(id) values(6);
Query OK, 1 row affected (0.02 sec)
Master [shenzhen]> insert into t1(id) values(7);
Query OK, 1 row affected (0.00 sec)
Master [shenzhen]> show global status like "%rpl_%";
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 3 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 4897 |
| Rpl_semi_sync_master_tx_wait_time | 9794 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
开启从库的io线程复制，恢复半同步复制状态。
Slave [shenzhen]> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
再次查看主库的半同步复制状态
Master [shenzhen]> show global status like "%rpl_%";
+--------------------------------------------+-------+
| 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 | 3 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 3 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 4897 |
| Rpl_semi_sync_master_tx_wait_time | 9794 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
Slave [shenzhen]> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+------+
7 rows in set (0.00 sec)
主从复制故障时的半同步复制测试
Slave [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| app |
| app01 |
| app02 |
| app03 |
| app04 |
| mysql |
| performance_schema |
| shenzhen |
| sys |
+--------------------+
10 rows in set (0.00 sec)
Slave [(none)]> create database app05;
Query OK, 1 row affected (0.00 sec)
Slave [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| app |
| app01 |
| app02 |
| app03 |
| app04 |
| app05 |
| mysql |
| performance_schema |
| shenzhen |
| sys |
+--------------------+
11 rows in set (0.00 sec)
Master [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| app |
| app01 |
| app02 |
| app03 |
| app04 |
| mysql |
| performance_schema |
| shenzhen |
| sys |
+--------------------+
10 rows in set (0.00 sec)
Master [(none)]> create database app05;
Query OK, 1 row affected (0.01 sec)
Master [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| app |
| app01 |
| app02 |
| app03 |
| app04 |
| app05 |
| mysql |
| performance_schema |
| shenzhen |
| sys |
+--------------------+
11 rows in set (0.00 sec)
Slave [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.11
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 1661
Relay_Log_File: db02-relay-bin.000007
Relay_Log_Pos: 1143
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1007
Last_Error: Error 'Can't create database 'app05'; database exists' on query. Default database: 'app05'. Query: 'create database app05'
Skip_Counter: 0
Exec_Master_Log_Pos: 1499
Relay_Log_Space: 2199
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1007
Last_SQL_Error: Error 'Can't create database 'app05'; database exists' on query. Default database: 'app05'. Query: 'create database app05'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 113306
Master_UUID: 7c145945-a680-11e9-baea-000c29a14cf7
Master_Info_File: /data/mysql/3306/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 190717 02:16:19
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 7c145945-a680-11e9-baea-000c29a14cf7:12-17
Executed_Gtid_Set: 7c145945-a680-11e9-baea-000c29a14cf7:1-16
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
此时再查看主库的半同步复制状态
发现还是on，可见半同步复制和从库的SQL线程没有关系。
Master [(none)]> show global status like "%rpl%";
+--------------------------------------------+-------+
| 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 | 4 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 3 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 3918 |
| Rpl_semi_sync_master_tx_wait_time | 11756 |
| Rpl_semi_sync_master_tx_waits | 3 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 3 |
+--------------------------------------------+-------+
14 rows in set (0.01 sec)
处理从库的复制故障
Slave [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)
Slave [(none)]> set global sql_slave_skip_counter = 1;
ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction
你开启了gtid，他执行过了gtid是不会在执行的。
百度翻译：
错误1858（hy000）：当服务器以@@global.gtid_mode=on运行时，无法设置SQL_slave_skip_计数器。相反，对于要跳过的每个事务，生成一个与该事务具有相同GTID的空事务。
注入空事物的方法：
Slave [(none)]> set gtid_next='7c145945-a680-11e9-baea-000c29a14cf7:17';
Query OK, 0 rows affected (0.00 sec)
Slave [(none)]> begin;commit;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
这里的xxxxx:N 也就是你的slave sql thread报错的GTID，或者说是你想要跳过的GTID。
最好的解决方案：重新构建主从环境
Slave [(none)]> set gtid_next='AUTOMATIC';
Query OK, 0 rows affected (0.00 sec)
注入空事物完成
Slave [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)
Slave [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.11
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 1661
Relay_Log_File: db02-relay-bin.000008
Relay_Log_Pos: 360
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 1661
Relay_Log_Space: 1717
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: 113306
Master_UUID: 7c145945-a680-11e9-baea-000c29a14cf7
Master_Info_File: /data/mysql/3306/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 7c145945-a680-11e9-baea-000c29a14cf7:12-17
Executed_Gtid_Set: 7c145945-a680-11e9-baea-000c29a14cf7:1-17
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
最后查看主库半同步的状态
Master [(none)]> show global status like "%rpl%";
+--------------------------------------------+-------+
| 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 | 4 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 3 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 3918 |
| Rpl_semi_sync_master_tx_wait_time | 11756 |
| Rpl_semi_sync_master_tx_waits | 3 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 3 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
查看从库的半同步状态
Slave [(none)]> show global status like "%rpl%";
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.01 sec)
线网环境半同步复制建议及其他方案：
对于常规的互联网应用，主从复制就够用了，对于主从一致性要求较高的应用可以使用半同步复制方案。但是半同步复制方案也会有缺点，那就是会导致主库更新性能的下降。尤其是在从库网络不稳定时会对主库更新带来更大的性能下降。
参考解决办法：
1.将主库半同步超时调短（1~2秒）
2.半同步复制的从库硬件与主库之间的网络配置要更好。
3.半同步的从库不要提供任何业务服务（包括读业务都不要给）
除了半同步复制技术之外，还有一些方案可以用于解决数据一致性问题：
1.客户端程序实现双写数据库。
2.客户端程序在写数据库的同时，写一段时间数据到磁盘上或内存中（redis）