MySQL主从同步和半同步配置
mysql主从配置：
1，安装maraidb，使用国内yum镜像站下载：
[root@localhost mysql]# cat /etc/yum.repos.d/MairaDB.repo
# MariaDB 10.1 CentOS repository list - created 2018-03-13 03:39 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://mirrors.ustc.edu.cn/mariadb/yum/10.1/centos6-amd64
gpgkey=http://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
[root@localhost mysql]# cat /etc/yum.repos.d/MairaDB.repo
# MariaDB 10.1 CentOS repository list - created 2018-03-13 03:39 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://mirrors.ustc.edu.cn/mariadb/yum/10.1/centos6-amd64
gpgkey=http://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
配置主从复制，首先要关闭selinux和iptables两个服务；
关闭selinux：setenforce 0
关闭防火墙：service iptables stop
关闭selinux：setenforce 0
关闭防火墙：service iptables stop
主节点：192.168.125.200
从节点：192.168.125.201
从节点：192.168.125.201
2，编辑主节点my.cnf文件：
[mysqld]
innodb_file_per_table = 1 //开启独立的表空间
skip_name_resolve = 1 //禁止dns解析
log_bin = master-bin //开启二进制日志
relay_log = relay-bin //开启中继日志
server_id = 1 //server-id一定要唯一。
[mysqld]
innodb_file_per_table = 1 //开启独立的表空间
skip_name_resolve = 1 //禁止dns解析
log_bin = master-bin //开启二进制日志
relay_log = relay-bin //开启中继日志
server_id = 1 //server-id一定要唯一。
3，编辑从节点my.cnf文件：
[mysqld]
innodb_file_per_table = 1
skip_name_resolve = 1
log_bin = master-bin
relay_log = relay-bin
server_id = 2
read_only = 1 //仅仅只读
relay_log_purge = 0
[mysqld]
innodb_file_per_table = 1
skip_name_resolve = 1
log_bin = master-bin
relay_log = relay-bin
server_id = 2
read_only = 1 //仅仅只读
relay_log_purge = 0
4，在主节点创建可复制权限的账号：
MariaDB [(none)]> grant replication slave, replication client on *.* to 'glq'@'192.168.125.%' identified by 'glq';
MariaDB [(none)]> grant replication slave, replication client on *.* to 'glq'@'192.168.125.%' identified by 'glq';
5，主节点查看当前的日志名和当前位置：
MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000004 | 635 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000004 | 635 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
6，从节点添加复制数据的主服务器ip，用户和密码，以及二进制日志名称和日志位置：
CHANGE MASTER TO MASTER_HOST='192.168.125.200',MASTER_USER='glq',MASTER_PASSWORD='glq',MASTER_LOG_FILE='master-bin.000004',MASTER_LOG_POS=635;
CHANGE MASTER TO MASTER_HOST='192.168.125.200',MASTER_USER='glq',MASTER_PASSWORD='glq',MASTER_LOG_FILE='master-bin.000004',MASTER_LOG_POS=635;
7，查看复制状态：
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.125.200
Master_User: glq
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000004
Read_Master_Log_Pos: 2620
Relay_Log_File: relay-bin.000003
Relay_Log_Pos: 1719
Relay_Master_Log_File: master-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.125.200
Master_User: glq
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000004
Read_Master_Log_Pos: 2620
Relay_Log_File: relay-bin.000003
Relay_Log_Pos: 1719
Relay_Master_Log_File: master-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
半同步复制配置：
1，首先需要加载插件：
主节点：MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so';
从节点：MariaDB [dba]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
2，查看是否加载成功：
主节点：MariaDB [(none)]> show plugins;
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
从节点：MariaDB [dba]> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
+---------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE |
+---------------------+---------------+
1 row in set (0.00 sec)
主节点：MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so';
从节点：MariaDB [dba]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
2，查看是否加载成功：
主节点：MariaDB [(none)]> show plugins;
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
从节点：MariaDB [dba]> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
+---------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE |
+---------------------+---------------+
1 row in set (0.00 sec)
3，启动半同步复制：
主节点：MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled = 1;
主节点：MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled = 1;
从节点：MariaDB [dba]> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
4，重启从节点的IO线程：
MariaDB [dba]> stop slave IO_THREAD;
Query OK, 0 rows affected (0.01 sec)
MariaDB [dba]> stop slave IO_THREAD;
Query OK, 0 rows affected (0.01 sec)
MariaDB [dba]> start slave IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
5，查看半同步是否在运行：
主节点：MariaDB [(none)]> show status like 'Rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 5265 |
| Rpl_semi_sync_master_net_wait_time | 21060 |
| Rpl_semi_sync_master_net_waits | 4 |
| Rpl_semi_sync_master_no_times | 2 |
| Rpl_semi_sync_master_no_tx | 9 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 328 |
| Rpl_semi_sync_master_tx_wait_time | 657 |
| 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)
主节点：MariaDB [(none)]> show status like 'Rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 5265 |
| Rpl_semi_sync_master_net_wait_time | 21060 |
| Rpl_semi_sync_master_net_waits | 4 |
| Rpl_semi_sync_master_no_times | 2 |
| Rpl_semi_sync_master_no_tx | 9 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 328 |
| Rpl_semi_sync_master_tx_wait_time | 657 |
| 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)
从节点：MariaDB [dba]> show status like 'Rpl_semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON | //已经在同步
+----------------------------+-------+
1 row in set (0.00 sec)
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON | //已经在同步
+----------------------------+-------+
1 row in set (0.00 sec)
PS：当半同步复制发生超时时（由rpl_semi_sync_master_timeout参数控制，单位是毫秒，默认为10000，即10s），会暂时关闭半同步复制，转而使用异步复制。当master dump线程发送完一个事务的所有事件之后，如果在rpl_semi_sync_master_timeout内，收到了从库的响应，则主从又重新恢复为半同步复制。
验证是否正常：
1，先在主节点写入：
MariaDB [dba]> insert into dba.dba values(4);
Query OK, 1 row affected (0.01 sec)
1，先在主节点写入：
MariaDB [dba]> insert into dba.dba values(4);
Query OK, 1 row affected (0.01 sec)
MariaDB [dba]> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000004 | 2776 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000004 | 2776 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
2，在从节点查看复制状态：
MariaDB [dba]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.125.200
Master_User: glq
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000004
Read_Master_Log_Pos: 2776
Relay_Log_File: relay-bin.000003
Relay_Log_Pos: 694
Relay_Master_Log_File: master-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
MariaDB [dba]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.125.200
Master_User: glq
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000004
Read_Master_Log_Pos: 2776
Relay_Log_File: relay-bin.000003
Relay_Log_Pos: 694
Relay_Master_Log_File: master-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3，停止从节点slave：
MariaDB [dba]> stop slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [dba]> stop slave;
Query OK, 0 rows affected (0.01 sec)
4，在主节点插入数据：
MariaDB [dba]> insert into dba.dba values(5);
Query OK, 1 row affected (10.01 sec)
MariaDB [dba]> insert into dba.dba values(5);
Query OK, 1 row affected (10.01 sec)
//此处使用了10.01s时间。而这与rpl_semi_sync_master_timeout参数的时间相吻合。
MariaDB [dba]> show variables like '%Rpl%';
+------------------------------------+--------------+
| Variable_name | Value |
+------------------------------------+--------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |//此处配置的是10s
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_COMMIT |
+------------------------------------+--------------+
5 rows in set (0.00 sec)
MariaDB [dba]> show variables like '%Rpl%';
+------------------------------------+--------------+
| Variable_name | Value |
+------------------------------------+--------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |//此处配置的是10s
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_COMMIT |
+------------------------------------+--------------+
5 rows in set (0.00 sec)
