本文详细介绍了MySQL5.7的主从复制配置，包括基于GTID的复制，以及解决半同步复制中遇到的问题。通过设置主从数据库，确保数据在多个服务器间实时同步。
一、主从复制
1.环境
系统：redhat6.5
防火墙：保持关闭
selinux=disabled
mysql主机：server1 172.25.32.4/24
mysql从机：server2 172.25.32.5/24
2.MySQL5.7安装启动（server4和server5同样操作）
1.下载并安装（下载地址：www.mysql.com）
mysql-community-client-5.7.17-1.el6.x86_64.rpm
mysql-community-common-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm
mysql-community-server-5.7.17-1.el6.x86_64.rpm
[root@server4 ~]# yum install -y mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm
2.开启MySQL并安全初始化
[root@server4 ~]# /etc/init.d/mysqld start
Initializing MySQL database: [ OK ]
Installing validate password plugin: [ OK ]
Starting mysqld: [ OK ]
[root@server4 ~]# mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root: ## ##输入查看/var/log/mysqld.log的里的密码
## 修改密码
New password:
Re-enter new password:
3.配置主从复制
mysql主库（master）：server4
1）.配置主配置文件（/etc/my.cnf）
[root@server4 ~]# vim /etc/my.cnf
[mysqld]
server-id=4 ## #服务器id
log-bin=mysql-bin ##开启二进制日志
binlog-do-db=test ##需要同步的数据库名
binlog-ignore-db=mysql ## ##禁止同步的数据库名
enforce_gtid_consistency=on ## #强制gtid一致性，开启后对于特定create table不被支持
## 重启服务
[root@server4 ~]# /etc/init.d/mysqld restart2）.配置数据库
在主库上建立帐户并授权
mysql> create user 'server4'@'172.25.32.5' identified by 'WESTOSlj@007';
Query OK, 0 rows affected (0.06 sec)
mysql> grant replication slave on *.* to 'server4'@'172.25.32.5' identified by 'WESTOSlj@007';
Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)
查看二进制日志是否打开
mysql> show variables like 'log_%';
+----------------------------------------+--------------------------------+
| Variable_name | Value |
+----------------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_builtin_as_identified_by_password | OFF |
| log_error | /var/log/mysqld.log |
| log_error_verbosity | 3 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_syslog | OFF |
| log_syslog_facility | daemon |
| log_syslog_include_pid | ON |
| log_syslog_tag | |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
| log_warnings | 2 |
+----------------------------------------+--------------------------------+
21 rows in set (0.00 sec)
查看主库的状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 860 | test | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql从库（slave）：server5
1）.配置文件（/etc/my.cnf）
[root@server5 ~]# vim /etc/my.cnf
[mysqld]
server-id=5
log-bin=mysql-bin
enforce_gtid_consistency=on
## 重启服务
[root@server5 ~]# /etc/init.d/mysqld restart2）.配置数据库
[root@server5 ~]# mysql -p
mysql> change master to master_host='172.25.32.4',master_user='server4',master_password='WESTOSlj@007',master_log_file='mysql-bin.000001',master_log_pos=860;
Query OK, 0 rows affected, 2 warnings (0.34 sec)
mysql> start slave;
Query OK, 0 rows affected (0.07 sec)
mysql> show slave status\G;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.32.4
Master_User: server4
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 860
Relay_Log_File: server5-relay-bin.000002
Relay_Log_Pos: 320
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: 860
Relay_Log_Space: 529
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: 4
Master_UUID: 38eaae01-7ec6-11e8-8ddb-525400cbb7a9
Master_Info_File: /var/lib/mysql/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:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
当看到Slave_IO_Running: Yes以及Slave_SQL_Running: Yes，则表示slave库已经正常运行了
当出现Slave_IO_Running: Connecting的提示时，说明主库和从库没有连接上，有以下三点原因：
1.网络问题：检查网络连接是否能够连接上
2.密码或POS号错误：查看pos号和主库的号是否对应
3.防火墙的问题：查看主库防火墙的策略，数据库是否拒绝外来连接，然后做相应的改动
4.主从测试
1）.主库：
创建数据库并添加数据：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database test;
Query OK, 1 row affected (0.04 sec)
mysql> use test;
Database changed
mysql> create table usertable (
-> username varchar(10) not null,
-> password varchar(16) not null);
Query OK, 0 rows affected (0.51 sec)
mysql> insert into usertable values('mark','westos');
Query OK, 1 row affected (0.05 sec)
mysql> insert into usertable values('harry','redhat');
Query OK, 1 row affected (0.10 sec)
mysql> select * from usertable;
+----------+----------+
| username | password |
+----------+----------+
| mark | westos |
| harry | redhat |
+----------+----------+
2 rows in set (0.00 sec)
2）.从库：
查看数据库：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| usertable |
+----------------+
1 row in set (0.00 sec)
mysql> select * from usertable;
+----------+----------+
| username | password |
+----------+----------+
| mark | westos |
| harry | redhat |
+----------+----------+
2 rows in set (0.00 sec)
查看到的数据库与主库所建立的相同，则主从复制设置成功
二、基于GTID的主从复制
mysql主库（master）：server4
1.配置主配置文件（/etc/my.cnf）
[root@server4 ~]# vim /etc/my.cnf
[mysqld]
server-id=4 ## 服务器id
log-bin=mysql-bin ## 二进制日志文件
gtid_mode=ON ## 开启gtid模式
enforce-gtid-consistency=true ## 强制gtid复制
## 重启服务
[root@server4 ~]# /etc/init.d/mysqld restart2.配置数据库
在主库上建立帐户并授权
mysql> create user 'server4'@'172.25.32.5' identified by 'WESTOSlj@007';
Query OK, 0 rows affected (0.06 sec)
mysql> grant replication slave on *.* to 'server4'@'172.25.32.5' identified by 'WESTOSlj@007';
Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)
查看二进制日志是否打开
mysql> show variables like 'log_%';
+----------------------------------------+--------------------------------+
| Variable_name | Value |
+----------------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_builtin_as_identified_by_password | OFF |
| log_error | /var/log/mysqld.log |
| log_error_verbosity | 3 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_syslog | OFF |
| log_syslog_facility | daemon |
| log_syslog_include_pid | ON |
| log_syslog_tag | |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
| log_warnings | 2 |
+----------------------------------------+--------------------------------+
21 rows in set (0.00 sec)
查看主库的状态
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000002 | 860 | | | 2fa61e32-8132-11e8-97a3-525400663b39:1-6 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql从库（slave）：server5
1.配置文件（/etc/my.cnf）
[root@server5 ~]# vim /etc/my.cnf
[mysqld]
server-id=5 ### 服务器id
gtid_mode=ON
enforce-gtid-consistency=true
## 重启服务
[root@server5 ~]# /etc/init.d/mysqld restart2.配置数据库
[root@server5 ~]# mysql -p
#### 指定主库(之前需要关闭主从复制)
mysql> change master to master_host='172.25.32.4',master_user='server4',master_password='WESTOSlj@007',master_log_file='mysql-bin.000001',MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.34 sec)
mysql> start slave;
Query OK, 0 rows affected (0.07 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.32.4
Master_User: server4
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 860
Relay_Log_File: server5-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
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: 860
Relay_Log_Space: 529
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: 4
Master_UUID: 38eaae01-7ec6-11e8-8ddb-525400cbb7a9
Master_Info_File: /var/lib/mysql/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:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
当看到Slave_IO_Running: Yes以及Slave_SQL_Running: Yes，则表示slave库已经正常运行了
当出现Slave_IO_Running: Connecting的提示时，说明主库和从库没有连接上，有以下三点原因：
1.网络问题：检查网络连接是否能够连接上
2.密码或POS号错误：查看pos号和主库的号是否对应
3.防火墙的问题：查看主库防火墙的策略，数据库是否拒绝外来连接，然后做相应的改动
主从测试
1.主库：
创建数据库并添加数据：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys
+--------------------+
4 rows in set (0.00 sec)
mysql> create database test;
Query OK, 1 row affected (0.04 sec)
mysql> use test;
Database changed
mysql> create table usertable (
-> username varchar(10) not null,
-> password varchar(16) not null);
Query OK, 0 rows affected (0.51 sec)
mysql> insert into usertable values('mark','westos');
Query OK, 1 row affected (0.05 sec)
mysql> insert into usertable values('harry','redhat');
Query OK, 1 row affected (0.10 sec)
mysql> select * from usertable;
+----------+----------+
| username | password |
+----------+----------+
| mark | westos |
| harry | redhat |
+----------+----------+
2 rows in set (0.00 sec)
2.从库：
查看数据库：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| usertable |
+----------------+
1 row in set (0.00 sec)
mysql> select * from usertable;
+----------+----------+
| username | password |
+----------+----------+
| mark | westos |
| harry | redhat |
+----------+----------+
2 rows in set (0.00 sec)
查看到的数据库与主库所建立的相同，则主从复制设置成功
三、半同步复制
环境：mysql5.7以上
selinux关闭
防火墙关闭
修改配置文件：/etc/my.cnf
## 主（server4）：
[mysqld]
server-id=4
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=true
## 从（server5）：
[mysqld]
server-id=5
gtid_mode=ON
enforce-gtid-consistency=true## 连接成功（即基于gtid的主从复制搭建成功）后关闭slave，
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.32.4
Master_User: server4
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 860
Relay_Log_File: server5-relay-bin.0000011
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: 860
Relay_Log_Space: 529
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: 4
Master_UUID: 38eaae01-7ec6-11e8-8ddb-525400cbb7a9
Master_Info_File: /var/lib/mysql/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:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.07 sec)1.主库端加载mater模块：
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.05 sec)
mysql> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)
2.从库端配置：
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.04 sec)
## 启动半同步复制,也可写到配置文件中
mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)
## 重启从上的IO线程,如果没有重启，则默认还是异步复制，重启后，slave会在master上注册为半同步复制的slave角色。
mysql> stop slave io_thread;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)3.主库端查看配置情况：
## 环境变量
mysql> show 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 |
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+-------------------------------------------+------------+
8 rows in set (0.00 sec)
rpl_semi_sync_master_wait_for_slave_count
MySQL 5.7.3引入的，该变量设置主需要等待多少个slave应答，才能返回给客户端，默认为1。
rpl_semi_sync_master_wait_no_slave
ON
默认值，当状态变量Rpl_semi_sync_master_clients中的值小于rpl_semi_sync_master_wait_for_slave_count时，Rpl_semi_sync_master_status依旧显示为ON。
OFF
当状态变量Rpl_semi_sync_master_clients中的值于rpl_semi_sync_master_wait_for_slave_count时，Rpl_semi_sync_master_status立即显示为OFF，即异步复制。## 状态变量
mysql> show status like '%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 | 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 | 277 |
| Rpl_semi_sync_master_tx_wait_time | 277 |
| 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_semi_sync_slave_status | OFF |
+--------------------------------------------+-------+
15 rows in set (0.00 sec)
Rpl_semi_sync_master_clients
当前半同步复制从的个数，如果是一主多从的架构，并不包含异步复制从的个数。
4.查看半同步是否在运行
## 主：
mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON |
+-----------------------------+-------+
1 row in set (0.00 sec)
## 从：
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
这两个变量常用来监控主从是否运行在半同步复制模式下。value为ON，
至此，MySQL半同步复制搭建完毕~事实上，半同步复制并不是严格意义上的半同步复制
当半同步复制发生超时时（由rpl_semi_sync_master_timeout参数控制，单位是毫秒，默认为10000，即10s），会暂时关闭半同步复制，转而使用异步复制。当master dump线程发送完一个事务的所有事件之后，如果在rpl_semi_sync_master_timeout内，收到了从库的响应，则主从又重新恢复为半同步复制。
5.测试：
## 测试一：
## 主：
mysql> insert into test.usertable values('user2','123');
Query OK, 1 row affected (0.08 sec)
mysql> select * from test.usertable;
+----------+----------+
| username | password |
+----------+----------+
| mark | westos |
| harry | redhat |
| user1 | 1234 |
| user2 | 123 |
+----------+----------+
4 rows in set (0.00 sec)
## 从： 主的操作很快就能返回
mysql> select * from test.usertable;
+----------+----------+
| username | password |
+----------+----------+
| mark | westos |
| harry | redhat |
| user1 | 1234 |
| user2 | 123 |
+----------+----------+
4 rows in set (0.00 sec)## 测试二：
## 先操作从：执行stop slave
mysql> stop slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
## 主：
mysql> insert into test.usertable values('user3','456');
Query OK, 1 row affected (10.08 sec)
mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | OFF |
+-----------------------------+-------+
1 row in set (0.00 sec)
## insert操作需要10.08s才返回，而这与rpl_semi_sync_master_timeout参数的时间相吻合。
此时，两个状态的值，均为“OFF”。
## 测试三：
##从：开启slave，则主和从会快速恢复到半同步复制
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
## 主：
mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON |
+-----------------------------+-------+
1 row in set (0.00 sec)
6.查看环境变量和状态变量
mysql> show 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 |
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+-------------------------------------------+------------+
8 rows in set (0.00 sec)
mysql> show status like '%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 | 3 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 4476 |
| Rpl_semi_sync_master_tx_wait_time | 8952 |
| 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 | 3 |
| Rpl_semi_sync_slave_status | OFF |
+--------------------------------------------+-------+
15 rows in set (0.00 sec)Rpl_semi_sync_master_no_tx
The number of commits that were not acknowledged successfully by a slave.
具体到上面的测试中，指的是insert into test.usertable values('user3','456');这个事务。
Rpl_semi_sync_master_yes_tx
The number of commits that were acknowledged successfully by a slave.
具体到上面的测试中，指的是以下三个事务
insert into test.usertable values ('user1','1234');
insert into test.usertable values('user2','123');
insert into test.usertable values('user4','235');
错误一：
问题及解决方案：
mysql> set global rpl_semi_sync_master_enabled=1;
ERROR 1193 (HY000): Unknown system variable 'rpl_semi_sync_master_enabled'
## 出现此错误，因为skip-grant-tables 参数把mysql的系统表都给跳过了，自然mysql就不认识半同步的配置参数了## 解决办法：
第一种办法：
[root@server4 ~]# /etc/init.d/mysqld restart --skip-grant-tables --skip_slave_start --plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so";
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
第二种方法：
## 写入到/etc/my.cnf里[mysqld]下，多个.so 可使用;隔开
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
#### 数据库操作存储日志
**查看主库，从库的二进制文件**
**主库进行以下操作：[root@server4 ~]# cd /var/lib/mysql**
**主库每次操作都会刷新以下的文件**
**我们在主库那边插入一条用户，主库的日志会产生变化**
**主库的操作会先同步到relay-log.info，然后再往从库中复制；**
####半同步复制技术优化
每次主库写文件的时候都会刷新文件，为了减少对IO的消耗，我们可以进行优化，把此文件改成从库当中的表；
MySQL5.7 对半同步复制的改进：
通过rpl_semi_sync_master_wait_point参数
有两种选择：
AFTER_SYNC (the default):
master写每一个事务到binlog并同时发送给slave,且sync binlog到磁盘。
mater sync完成后，开始等待slave的确认。
确认后，master commit事务到存储引擎,并返回结果给客户端，客户端才可继续。
AFTER_COMMIT:
master写每一个事务到binlog并同时发送给slave,且sync binlog到磁盘,并且commit事务到存储引擎里。
master commit完成后，开始等待slave确认。
确认后，master返回结果给客户端，客户端才可继续。
**并行复制配置与调优**
修改从库虚拟机的配置文件,开启enhanced multi-threaded slave
[root@server4 mysql]# vim /etc/my.cnf
前两行为并行复制，3，4行为优化成为表，最后一行是开启……
LOGICAL_CLOCK：基于组提交的并行复制方式
slave_parallel_workers=16 线程设置为16
参数master_info_repostitory设置为TABLE 性能可以有50%~80%的提升,这是因为并行复制开启后对于元master.info这个文件的更新将会大幅提升，资源的竞争也会变大。
写完之后重新启动mysql
此时可以看到下面的表
刚才的两个file文件被存成了表，放在数据库当中，可以减少对IO的消耗
查看刚才主库存下的表
###四、组复制，实现读写分离
环境：
关闭selinux和防火墙
三台虚拟机：
server3：主库
server4：从库
server5：代理服务器
####1.重新开启一个虚拟机server5，拷贝一个压缩包mysql-proxy（需要下载）
####2.解压，移动到/usr/local下，创建软连接
####3.找到存放lua脚本的位置，并且编辑rw脚本
**文件中最小/最大连接数默认为4和8min/max_idle_connections，即连接数达到8台，会开启读写分离，为了简单的看到实验效果，我们将其改成1和2；**
####4.然后我们在mysql-proxy目录下，创建一个conf目录，存放我们编辑好的配置文件
**编辑完成的配置文件如下：**
**2：指定用户为root，3：本机的ip和端口（代理机），4：读（从库）的ip和端口，5：主库的ip和端口，6：读写脚本的目录位置，7存放日志的地方（在该目录下创建一个logs目录）**
####5.创建logs目录
**配置文件编辑完成之后，退出，然后在mysql-proxy.conf文件中指定日志文件存放在logs目录下的mysql-proxy.log文件中**
####6.使mysql-proxy.conf文件生效
**组权限不够,其他人权限过大**
**给文件加上写权限,并去掉其他人读权限，重新执行命令**
####7.此时查看3306端口，即为mysql-proxy的端口
####8.做完之后在主库进行授权(数据库授权delete千万慎重)
####9.然后用物理机连接测试
**此时连接的为server3（主库），可以用lsof查看（如果没有，直接yum下载即可）**
**再开一个shell，用物理界登陆server5的数据库，还会连接到server3，即主库，但是一旦超过2个，则会自动连接到server4（从库），因为我们刚才设置的最大连接数为2，就开启读写分离。**
**如下，开启第三个shell与server5（代理机）的数据库连接，则会与从库（server4）连接到一起：**
**此时不管连接server3，或者server4，当写入内容的时候都会写入到主库当中，然后从库也会同步过来；**
###五、全同步
三台虚拟机：即3个节点，无主从之分
1.Server3，4关闭掉mysql服务，server5安装以下五个包
2.server3配置（第一个节点）：
首先关闭mysql服务，然后编辑/etc/my.cnf
[root@server3 ~]# /etc/init.d/mysqld stop
Stopping mysqld: [ OK ]
[root@server3 ~]# vim /etc/my.cnf
文件内容编辑如下：
loose-group_replication_group_name修改为从库的uuid
删除原先mysql的所有东西
[root@server3 log]# cd /var/lib/
[root@server3 lib]# rm -fr mysql
然后启动：
[root@server3 ~]# /etc/init.d/mysqld start
[root@server3 ~]# grep password /var/log/mysqld.log ##找初始密码
[root@server3 ~]# mysql -p ##进入修改密码
Enter password:
修改密码，授权：
查看节点状态为online，即可
3.Server4：第二个节点
首先关闭mysql，然后编辑配置文件和启动脚本（因为启动脚本中有一个密码相关的东西，需要注释掉，否则产生影响）
[root@server4 ~]# vim /etc/my.cnf
配置文件与server3大致相同，修改掉29行的server-id（id必须不同，又为正整数），还有42行的IP，改为自己的（参考上面图片）
[root@server4 ~]# vim /etc/init.d/mysqld ##注释掉下面五行
同样删除以前缓存下来的东西，然后重新启动，并且输入初始化密码进行修改：
[root@server4 ~]# cd /var/lib/
[root@server4 lib]# rm -fr mysql
“`
出现此错误，原因是相关进程未关闭
关闭进程后，再次删除/usr/local/mysql目录下的所有文件
然后启动mysql，然后输入初始化密码进入修改密码，修改配置策略如下（与主机server3最后两条有所不同）：
查看节点状态为online
最后一个节点server5与server4配置相同（配置文件的ID和IP需要修改），最后状态为online则为成功。
三台虚拟机全部全同步后，读写一致，无论在哪一台虚拟机的数据库中读写，都可以同步到其他两台虚拟机的数据库中。
以server3为例，写入数据
Server4
Server5
扫一扫
335
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
