1、MySQL半同步概述
在一般MySQL主从同步下，主库和从库的复制是异步复制的，异步复制是主库数据发生改变，从库的I/O线程请求主库的二进制日志，主库的dump线程把二进制日志发送给从库便结束了，主库并不会去验证从库是否接收到了二进制日志文件，这一过程中，如果从库发生故障了没有接收到主库的日志文件，那么主库和从库的数据便不一致了；所以MySQL的半同步功能就是保证在生产业务中，有多个从库时，用户往数据库写入数据，主库数据发生改变后，主库把二进制日志文件发送给从库，从库成功接收到日志文件并回复确认之后，主库才会告诉用户数据修改完成。这样便可以确定即使发生故障，也会由一个从库的数据和主库的数据具有一致性。
注意：MySQL的半同步复制功能需要在主库和从库都同时开启，否则的话主服务器默认还是会使用异步复制的模式。
2、半同步的配置
1）先对两台数据库做主从设置
主库：
[root@master ~]#
[root@master ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log_bin=mysql-bin
server_id=1
[root@master ~]# systemctl restart mariadb
[root@master ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> grant replication slave,replication client on *.* to 'jyy'@'192.168.126.135' identified by 'jyy';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 491 |
+------------------+-----------+
1 row in set (0.00 sec)
MariaDB [(none)]>
从库：
[root@www ~]#vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server_id=2
[root@www ~]# systemctl restart mariadb
[root@www ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> change master to master_host='192.168.126.132',master_user='jyy',master_password='jyy',master_log_file='mysql-bin.000001',master_log_pos=491;
Query OK, 0 rows affected (0.06 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.126.132
Master_User: jyy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 491
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000001
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: 491
Relay_Log_Space: 825
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: 1
1 row in set (0.00 sec)
ERROR: No query specified
2）测试是否可以复制
主库插入数据内容：
MariaDB [(none)]> create database aaa;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use aaa;
Database changed
MariaDB [aaa]> create table student(id int(20),name char(30));
Query OK, 0 rows affected (0.04 sec)
MariaDB [aaa]> insert into student values(1,'xiaoming'),(2,'xiaohong'),(3,'xiaodong');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
从库上查看是否有内容：
MariaDB [mysql]> show global variables like "server_id";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 2 |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [mysql]> select * from aaa.student;
+------+----------+
| id | name |
+------+----------+
| 1 | xiaoming |
| 2 | xiaohong |
| 3 | xiaodong |
+------+----------+
3 rows in set (0.00 sec)
MariaDB [mysql]>
3）安装支持半同步的插件
主库：
MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so'
-> ;
Query OK, 0 rows affected (0.05 sec)
MariaDB [(none)]> show global variables like '%semi%'; //查看半同步设置；
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | OFF | //是否开启半同步节点；
| rpl_semi_sync_master_timeout | 10000 | //等待从节点的超时时间；
| rpl_semi_sync_master_trace_level | 32 | //跟踪级别；默认为32；
| rpl_semi_sync_master_wait_no_slave | ON | //等待一个从节点；
+------------------------------------+-------+
4 rows in set (0.00 sec)
MariaDB [(none)]>
从库：
MariaDB [mysql]> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.02 sec)
MariaDB [mysql]> show global variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
MariaDB [mysql]>
4）开启主从的半同步功能
主库：
MariaDB [aaa]> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [aaa]> 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_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
MariaDB [aaa]>
从库：
MariaDB [mysql]> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> show global variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
MariaDB [mysql]>
注意：如果需要永久配置半同步设置，需要把设置写入到MySQL的配置文件中
版权声明：本文为Micky_Yang原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。