linux的LNMP架构中的MySQL（主从复制，基于GDIT主从复制，基于GDIT的半同步，组复制，读写分离）

本文链接：https://blog.csdn.net/qwqq233/article/details/89969135

本文介绍了Linux环境下MySQL的主从复制配置，包括基于GDIT的复制、半同步复制和组复制，以及读写分离的实现，通过详细步骤展示了如何在多台服务器间实现数据的一致性和高可用性。

摘要由CSDN通过智能技术生成

实验环境：
主库端： server1:172.25.26.1
从库端： server2:172.25.26.2
一.mysql的主从复制：
1.server1主端配置：
（1）解压安装

[root@server1 mysql]# tar xf mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar 
[root@server1 mysql]# ls
mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar
mysql-community-client-5.7.24-1.el7.x86_64.rpm
mysql-community-common-5.7.24-1.el7.x86_64.rpm
mysql-community-devel-5.7.24-1.el7.x86_64.rpm
mysql-community-embedded-5.7.24-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.24-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.24-1.el7.x86_64.rpm
mysql-community-libs-5.7.24-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm
mysql-community-minimal-debuginfo-5.7.24-1.el7.x86_64.rpm
mysql-community-server-5.7.24-1.el7.x86_64.rpm
mysql-community-server-minimal-5.7.24-1.el7.x86_64.rpm
mysql-community-test-5.7.24-1.el7.x86_64.rpm

[root@server1 mysql]# yum install mysql-community-client-5.7.24-1.el7.x86_64.rpm mysql-community-common-5.7.24-1.el7.x86_64.rpm mysql-community-libs-5.7.24-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm mysql-community-server-5.7.24-1.el7.x86_64.rpm -y

（2）更改配置文件

[root@server1 mysql]# vim /etc/my.cnf
添加
 28 log-bin=mysql-bin
 29 server-id=1 
 [root@server1 ~]# systemctl restart mysqld			##设置完之后，重启生效

在这里插入图片描述
（3）启动服务并查询初始化密码

[root@server1 mysql]# cd /var/lib/mysql
[root@server1 mysql]# systemctl start mysqld
[root@server1 ~]# grep password /var/log/mysqld.log
2019-05-08T05:56:29.707178Z 1 [Note] A temporary password is generated for root@localhost: ywanwwya!1Ar

（4）安全初始化

[root@server1 mysql]# mysql_secure_installation 

Securing the MySQL server deployment.

Enter password for user root: 					##这里输入刚刚查询的初始化密码

The existing password for the user account root has expired. Please set a new password.

New password: 			##设置密码，注意：密码需要有数字 大小写字母 特殊字符 三者构成 缺一不可！ 密码位数大于8

Re-enter new password: 

The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 100 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : 

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

（5）登录数据库，

[root@server1 mysql]# mysql -p
Enter password: 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> grant replication slave on *.* to repl@'172.25.26.%' identified by 'Aa316256.';
##设置主端
Query OK, 0 rows affected, 1 warning (0.13 sec)

mysql> show master status;	##查看主库状态，这里的两个参数，表示二进制文件和端口
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      447 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

在这里插入图片描述
参数解释：

replication 　　 ##表示授权复制的权限　　
*.* 　　　　　　　##表示所有数据库可以进行同步　
repl 　　　　 　　##表示授权名，可以随意填写　
'172.25.26.%' 　 ##表示授权172.25.26.0/24的网段所有服务器可以同步， %表示任意

查看二进制日志是否打开

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.36 sec)

（6）查看用于同步二进制日志文件

[root@server1 ~]# cd /var/lib/mysql
[root@server1 mysql]# ls
auto.cnf         ibdata1           mysql-bin.000002    public_key.pem
ca-key.pem       ib_logfile0       mysql-bin.index     server-cert.pem
ca.pem           ib_logfile1       mysql.sock          server-key.pem
client-cert.pem  ibtmp1            mysql.sock.lock     sys
client-key.pem   mysql             performance_schema
ib_buffer_pool   mysql-bin.000001  private_key.pem

在这里插入图片描述
（7）物理机测试远程登录(物理机上需要安装mysql)

[root@foundation26 ~]# mysql -h 172.25.26.1 -urepl -pAa316256.
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

MySQL [(none)]>

2.从属服务器环境配置:
（1）安装软件

[root@server2 mnt]# yum install mysql-community-client-5.7.24-1.el7.x86_64.rpm mysql-community-common-5.7.24-1.el7.x86_64.rpm mysql-community-libs-5.7.24-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm mysql-community-server-5.7.24-1.el7.x86_64.rpm -y

（2）编辑配置文件

[root@server2 ~]# vim /etc/my.cnf
添加
 28 server-id=2

在这里插入图片描述
（3）开启服务并查询密码

[root@server2 ~]# systemctl start mysqld
[root@server2 ~]# grep password /var/log/mysqld.log 
2019-05-08T05:56:48.370209Z 1 [Note] A temporary password is generated for root@localhost: Ap&;fsAqv4eC

（4）安全初始化

[root@server2 ~]# mysql_secure_installation -p'Ap&;fsAqv4eC'
mysql_secure_installation: [Warning] Using a password on the command line interface can be insecure.

Securing the MySQL server deployment.


The existing password for the user account root has expired. Please set a new password.

New password: 				##设置密码，这里为了方便起见和主库设置相同的密码

Re-enter new password: 
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.


Estimated strength of the password: 100 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

或者

[root@server2 ~]# mysql -p
Enter password: 
mysql> alter user root@localhost identified by 'Aa316256.';		##安全初始化
Query OK, 0 rows affected (0.00 sec)

（5）登录数据库并设置主从复制

mysql> change master to master_host='172.25.26.1', master_user='repl', master_password='Aa316256.', master_log_file='mysql-bin.000002',master_log_pos=447;
 ##这里host填入主库ip，log_file和log_pos填入查询主库状态时的得到的参数
Query OK, 0 rows affected, 2 warnings (0.40 sec)

mysql> start slave;			##开启slave模块，让设置生效
Query OK, 0 rows affected (0.09 sec)

mysql> show slave status\G;		##查询主从复制状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.26.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 447
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes		##这里要上下两个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: 447
              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: 1
                  Master_UUID: 0633a285-7156-11e9-8edd-525400402603
             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)

ERROR: 
No query specified

mysql>

3.测试
server1上添加数据看server2是否同步过来

（1）server1创建：

mysql> create database westos;		##创建库
Query OK, 1 row affected (0.01 sec)

mysql> use westos			##进入库
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table usertb(
    -> username varchar(10) not null,
    -> password varchar(15) not null);	##创建表头及格式
Query OK, 0 rows affected (0.03 sec)

mysql> desc usertb;			##显示表格式
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO   |     | NULL    |       |
| password | varchar(15) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into usertb values('user1','123');##插入数据
Query OK, 1 row affected (0.00 sec)

mysql> select * from usertb;		##显示表的数据
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
+----------+----------+
1 row in set (0.00 sec)

在这里插入图片描述
（2）server2查看：

mysql> show databases;			##查看是否有新的库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| westos             |
+--------------------+
5 rows in set (0.00 sec)

mysql> use westos			##进入库
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> select * from usertb;		##查看指定数据
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
+----------+----------+
1 row in set (0.00 sec)

在这里插入图片描述
二、基于GDIT主从复制

mysql数据库从5.6.5开始新增一种基于GDIT的复制方式。GTID (Global Transaction ID)
是对于一个已提交事务的编号，并且是一个全局唯一的编号。 GTID 实际上是由 UUID+TID 组成的。其中 UUID 是一个
MySQL 实例的唯一标识。

主从复制,默认是通过pos复制(postion),就是说在日志文档里,将用户进行的每一项操作都进行编号(pos),每一个event都有一个起始编号,一个终止编号,我们在配置主从复制时从节点时,要输入master的log_pos值就是这个原因,要求它从哪个pos开始同步数据库里的数据,这也是传统复制技术。

MySQL5.6增加了GTID复制,GTID就是类似于pos的一个作用,不过它是整个mysql复制架构全局通用的,就是说在这整个mysql冗余架构中,它们的日志文件里事件的GTID值是一致的.

GTID (Global Transaction ID) 是对于一个已提交事务的编号，并且是一个全局唯一的编号。 GTID 实际上是由
UUID+TID 组成的。其中 UUID 是一个 MySQL 实例的唯一标识。TID
代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。

通过GDIT保证每个主库上提交的事务在集群中有一个唯一的ID.这种方式强化了数据库的主备一致性，故障恢复以及容错能力。

pos与GTID有什么区别?

两者都是日志文件里事件的一个标志,如果将整个mysql集群看作一个整体,pos就是局部的,GTID就是全局的.

1.环境部署：
（1）修改配置文件并重启服务

server1:
vim  /etc/my.cnf
 32 gtid_mode=ON
 33 enforce-gtid-consistency=true


server2:
vim  /etc/my.cnf
 30 gtid_mode=ON
 31 enforce-gtid-consistency=true

systemctl restart mysqld			##server1和server2都重启服务

（2）在server2从库端先停掉slave，然后重新创建连接
如果进行change master to时使用MASTER_AUTO_POSITION = 1， slave连接master将使用基于GTID的复制协议。

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to master_host='172.25.26.1', master_user='repl', master_password='Aa316256.', master_auto_position=1;
##重新创建连接
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;				##重启slave
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;		##查看状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.26.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 698
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 414
        Relay_Master_Log_File: mysql-bin.000003
             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: 698
              Relay_Log_Space: 623
              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
                  Master_UUID: 0633a285-7156-11e9-8edd-525400402603
             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: 0633a285-7156-11e9-8edd-525400402603:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

在这里插入图片描述

2.测试：
（1）在server1添加数据

mysql> insert into usertb values('user4','123');
Query OK, 1 row affected (0.09 sec)

mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 123      |
| user3    | 123      |
| user4    | 123      |
+----------+----------+
4 rows in set (0.00 sec)

（2）在server2查看：

mysql> select * from westos.usertb;			##效果1
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 123      |
| user3    | 123      |
| user4    | 123      |
+----------+----------+
4 rows in set (0.00 sec)

mysql> use mysql;
Database changed
mysql> select * from gtid_executed;				##效果2查看
+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 0633a285-7156-11e9-8edd-525400402603 |              1 |            1 |
| 0633a285-7156-11e9-8edd-525400402603 |              2 |            2 |
| 0633a285-7156-11e9-8edd-525400402603 |              3 |            3 |
+--------------------------------------+----------------+--------------+
3 rows in set (0.00 sec)

三、基于GDIT的半同步

MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。
半同步复制就是为了解决数据丢失的问题。

server1主库环境配置:

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';##安装服务插件
Query OK, 0 rows affected (0.02 sec)

mysql> set global rpl_semi_sync_master_enabled=ON;		##开启服务
Query OK, 0 rows affected (0.00 sec)

mysql> select plugin_name,plugin_status
    -> from information_schema.plugins
    -> where plugin_name like '%semi%';					##查看服务状态
+----------------------+---------------+
| plugin_name          | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.00 sec)

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 |
+-------------------------------------------+------------+
6 rows in set (0.01 sec)

mysql> show status like '%rpl_semi_sync%';				##查看状态变量
+--------------------------------------------+-------+
| 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)

server2从属库环境配置

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';##安装服务
Query OK, 0 rows affected (0.01 sec)

mysql> set global rpl_semi_sync_slave_enabled=ON;##开启端口
Query OK, 0 rows affected (0.00 sec)

mysql> show 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)

mysql> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave io_thread;					##重启IO线程
Query OK, 0 rows affected (0.00 sec)

注意：要重启从上的IO线程,如果没有重启，则默认还是异步复制，重启后，slave会在master上注册为半同步复制的slave角色

测试：
（1）半同步失败时：
server2关闭IO线程：

mysql> stop slave io_thread;
Query OK, 0 rows affected (0.01 sec)

server1插入数据，这是由于server2的IO线程关闭，server1会等待十秒，十秒后会变成异步复制

mysql> insert into usertb values ('user5','555');
Query OK, 1 row affected (10.01 sec)

mysql> show 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              | 1     |
| Rpl_semi_sync_master_no_tx                 | 1     |表示没有成功接收slave提交的次数，也就是使用半同步失败的次数，10s后没有得到反馈信息，会转为异步复制
| Rpl_semi_sync_master_status                | OFF   |
| 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.00 sec)

这时再次插入数据，主端在等待了一次之后，默认变成了异步复制，无需再等待10s

mysql> insert into usertb values ('user6','555');
Query OK, 1 row affected (0.01 sec)

mysql> show 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              | 1     |
| Rpl_semi_sync_master_no_tx                 | 2     |##异步次数变为2
| Rpl_semi_sync_master_status                | OFF   |
| 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.00 sec)

mysql> select * from usertb;			##主端查询数据
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 123      |
| user3    | 123      |
| user4    | 123      |
| user5    | 555      |
| user6    | 555      |
+----------+----------+
6 rows in set (0.00 sec)

server2中先不开启IO线程，直接查看数据

mysql> use westos;
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> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 123      |
| user3    | 123      |
| user4    | 123      |
+----------+----------+
4 rows in set (0.00 sec)

mysql> start slave io_thread;			##开启IO线程
Query OK, 0 rows affected (0.00 sec)

mysql> select * from usertb;			##查看数据是否异步同步过来
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 123      |
| user3    | 123      |
| user4    | 123      |
| user5    | 555      |
| user6    | 555      |
+----------+----------+
6 rows in set (0.00 sec)

（2）半同步成功
server1：
这时待从库开启IO线程后，在插入数据，查看是否同步

mysql> insert into usertb values ('user7','555');
Query OK, 1 row affected (0.01 sec)

mysql> show 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              | 1     |
| Rpl_semi_sync_master_no_tx                 | 2     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 4566  |
| Rpl_semi_sync_master_tx_wait_time          | 4566  |
| 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     |##这时查看半同步成功次数变为1
+--------------------------------------------+-------+
14 rows in set (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_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.00 sec)

在server2中查看

mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 123      |
| user3    | 123      |
| user4    | 123      |
| user5    | 555      |
| user6    | 555      |
| user7    | 555      |
+----------+----------+
7 rows in set (0.00 sec)

四.组复制

组复制模型：

MySQL组复制是MySQL
5.7.17开始引入的新功能，为主从复制实现高可用功能。它支持单主模型和多主模型两种工作方式(默认是单主模型)。
单主模型：从复制组中众多个MySQL节点中自动选举一个master节点，只有master节点可以写，其他节点自动设置为read
only。当master节点故障时，会自动选举一个新的master节点，选举成功后，它将设置为可写，其他slave将指向这个新的master。
多主模型：复制组中的任何一个节点都可以写，因此没有master和slave的概念，只要突然故障的节点数量不太多，这个多主模型就能继续可用。

组复制原理：

复制组由多个 server成员构成，并且组中的每个 server
成员可以独立地执行事务。但所有读写（RW）事务只有在冲突检测成功后才会提交。只读（RO）事务不需要在冲突检测，可以立即提交。
换句话说，对于任何 RW 事务，提交操作并不是由始发 server 单向决定的，而是由组来决定是否提交。准确地说，在始发 server
上，当事务准备好提交时，该 server
会广播写入值（已改变的行）和对应的写入集（已更新的行的唯一标识符）。然后会为该事务建立一个全局的顺序。最终，这意味着所有 server
成员以相同的顺序接收同一组事务。因此，所有 server 成员以相同的顺序应用相同的更改，以确保组内一致。
组复制使您能够根据在一组 server 中复制系统的状态来创建具有冗余的容错系统。因此，只要它不是全部或多数 server
发生故障，即使有一些 server 故障，系统仍然可用，最多只是性能和可伸缩性降低，但它仍然可用。server
故障是孤立并且独立的。它们由组成员服务来监控，组成员服务依赖于分布式故障检测系统，其能够在任何 server
自愿地或由于意外停止而离开组时发出信号。
总之，MySQL 组复制提供了高可用性，高弹性，可靠的 MySQL 服务。

在单主模式下部署组复制：

准备单台纯净的安装过mysql的虚拟机，mysql的安装

主机 	        IP 		     角色
server3 	172.25.26.1 	master
server4 	172.25.26.2 	slave
server5 	172.25.26.3 	slave

1.server1环境配置
1.server1:
（1）关闭数据库，删除以前的文件，重新配置

[root@server1 mysql]# systemctl stop mysqld
[root@server1 mysql]# cd /var/lib/mysql
[root@server1 mysql]# ls
auto.cnf         ib_buffer_pool  mysql-bin.000001    private_key.pem  westos
ca-key.pem       ibdata1         mysql-bin.000002    public_key.pem
ca.pem           ib_logfile0     mysql-bin.000003    server-cert.pem
client-cert.pem  ib_logfile1     mysql-bin.index     server-key.pem
client-key.pem   mysql           performance_schema  sys
[root@server1 mysql]# cat auto.cnf 			##查询uuid
[auto]
server-uuid=0633a285-7156-11e9-8edd-525400402603
[root@server1 mysql]# rm -fr *
[root@server1 mysql]# ls

（2）编辑配置文件

[root@server3 mysql]# vim /etc/my.cnf
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW


transaction_write_set_extraction=XXHASH64    ###指示Server必须为每个事务收集写集合，并使用XXHASH64哈希算法将其编码为散列
loose-group_replication_group_name="0633a285-7156-11e9-8edd-525400402603"      ##告知插件,正在加入或创建的组要命名，一般写UUID
loose-group_replication_start_on_boot=off  ##指示插件在 server 启动时不自动启动组复制。
loose-group_replication_local_address="172.25.26.1:24901" ##告诉插件使用 IP 地址 本地主机,端口 24901 用于接受来自组中其他成
员的传入连接。
loose-group_replication_group_seeds="172.25.26.1:24901,172.25.26.2:24901,172.25.26.3:24901"
loose-group_replication_bootstrap_group=off    #配置是否自动引导组 
loose-group_replication_ip_whitelist="127.0.0.1,172.25.26.0/24"  #  用户白名单
loose-group_replication_enforce_update_everywhere_checks=ON   #多主模式下为多主更新启用或禁用严格一致性检查。   
loose-group_replication_single_primary_mode=OFF  #设置组自动选择一个 server 来处理读/写工作。

在这里插入图片描述

（3）开启服务后查看新密码

[root@server1 mysql]# systemctl start mysqld
[root@server1 mysql]# grep password /var/log/mysqld.log 
2019-05-08T12:54:00.063606Z 1 [Note] A temporary password is generated for root@localhost: gtsk=B3-BU&i

（4）初始化数据库

[root@server1 mysql]# mysql -p'gtsk=B3-BU&i'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.24-log

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter user root@localhost identified by 'Aa316256.';
Query OK, 0 rows affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

（5）启动组复制

mysql> SET SQL_LOG_BIN=0;    #禁用二进制日志
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Aa316256.';
Query OK, 0 rows affected (0.00 sec)  #创建用户

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';  ##加用户权限
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;     ##刷洗数据
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=1;    #启用二进制日志
Query OK, 0 rows affected (0.00 sec)


##一旦配置了用户，使用CHANGE MASTER TO语句将服务器配置为在下一次需要从其他成员恢复状态时使用group_replication_recovery复制通道的给定凭证。发出以下命令，用创建用户时使用的值替换rpl_user和密码。
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Aa316256.' FOR CHANNEL 'group_replication_recovery';    
Query OK, 0 rows affected, 2 warnings (0.23 sec)

mysql> INSTALL PLUGIN group_replication SONAME    #安装组复制插件'group_replication.so';
Query OK, 0 rows affected (0.85 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=ON;  ##master上要先打开，等打开组复制之后再开启（slave上不用进行） 
Query OK, 0 rows affected (0.00 sec)

mysql> START GROUP_REPLICATION;  	   ##打开组复制
Query OK, 0 rows affected (2.88 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from performance_schema.replication_group_members;
##查看组成员，查看状态，显示ONLINE则开启成功
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 595fc1a4-7190-11e9-9c92-525400402603 | server1     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

2.server2配置：

[root@server2 mysql]# systemctl stop mysqld
[root@server2 mysql]# cd /var/lib/mysql

[root@server2 mysql]# rm -fr *
[root@server2 mysql]# vim /etc/my.cnf
 30 server_id=2
 31 gtid_mode=ON
 32 enforce_gtid_consistency=ON
 33 master_info_repository=TABLE
 34 relay_log_info_repository=TABLE
 35 binlog_checksum=NONE
 36 log_slave_updates=ON
 37 log_bin=binlog
 38 binlog_format=ROW
 39 
 40 transaction_write_set_extraction=XXHASH64
 41 loose-group_replication_group_name="0633a285-7156-11e9-8edd-525400402603"
 42 loose-group_replication_start_on_boot=off
 43 loose-group_replication_local_address= "172.25.26.2:24901"
 44 loose-group_replication_group_seeds= "172.25.26.1:24901,172.25.26.2:24901,17    2.25.26.3:24901"
 45 loose-group_replication_bootstrap_group=off
 46 loose-group_replication_ip_whitelist="127.0.0.1,172.25.26.0/24"
 47 loose-group_replication_enforce_update_everywhere_checks=ON
 48 loose-group_replication_single_primary_mode=OFF

[root@server2 mysql]# systemctl start mysqld
[root@server2 mysql]# grep password /var/log/mysqld.log 
2019-05-02T07:37:31.463140Z 1 [Note] A temporary password is generated for root@localhost: s#oqUBDgj6gf


[root@server2 mysql]# mysql -p's#oqUBDgj6gf'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24-log

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter user root@localhost identified by 'Aa316256.';
Query OK, 0 rows affected (0.01 sec)

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Aa316256.';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Aa316256.' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.09 sec)

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.06 sec)

mysql> reset master;
Query OK, 0 rows affected (0.03 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (5.26 sec)

3.server3配置：

[root@server3 mnt]# vim /etc/my.cnf
[root@server3 mnt]# systemctl start mysqld
[root@server3 mnt]# grep password /var/log/mysqld.log 
2019-05-02T07:44:49.584904Z 1 [Note] A temporary password is generated for root@localhost: 7-rKw9vu8w,B
[root@server3 mnt]# mysql -p'7-rKw9vu8w,B'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24-log

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> alter user root@localhost identified by 'Aa316256.';
Query OK, 0 rows affected (0.01 sec)

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Aa316256.';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Aa316256.' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.08 sec)

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.06 sec)

mysql> reset master;
Query OK, 0 rows affected (0.02 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.25 sec)

4.server1中查看server2和server3是否添加到组中

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 051792d9-7194-11e9-8080-525400f87789 | server2     |        3306 | ONLINE       |
| group_replication_applier | 595fc1a4-7190-11e9-9c92-525400402603 | server1     |        3306 | ONLINE       |
| group_replication_applier | ad3b36a9-7194-11e9-bbc4-52540042c7d6 | server3     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

5.测试

在server1中向数据库添加数据，在2和3中查看是否同步过来
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.01 sec)

mysql> USE test;
Database changed
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO t1 VALUES (1, 'Luis');
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
+----+------+
1 row in set (0.01 sec)

在server3中给数据库添加数据

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> INSERT INTO t1 VALUES (2,'hui');
Query OK, 1 row affected (0.07 sec)

mysql> select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
|  2 | hui  |
+----+------+
2 rows in set (0.00 sec)

在server2查看

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> select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
|  2 | hui  |
+----+------+
2 rows in set (0.00 sec)

五.mysql的读写分离
1.什么是读写分离？

MySQL读写分离是指让master处理写操作，让slave处理读操作，非常适用于读操作量比较大的场景，可减轻master的压力。
使用mysql-proxy实现mysql的读写分离，mysql-proxy实际上是作为后端mysql主从服务器的代理，它直接接受客户端的请求，对SQL语句进行分析，判断出是读操作还是写操作，然后分发至对应的mysql服务器上。

2.为什么要读写分离？

因为数据库的写操作相对读操作是比较耗时的，所以数据库的读写分离，解决的是数据库的写入，影响了查询的效率。

3.实验环境：

rhel7.3 selinux and firewalld disabled
server1:172.25.26.1（master）
server2:172.25.26.2（slave）
server3:172.25.26.3（mysql-proxy）

4.搭建步骤
（1）[server1和server2]: 实现主从复制
在这里插入图片描述
测试：主从复制

在server1（主）上创建库，进入库，创建表，插入表信息

mysql> insert into userlist values('user1','123');
Query OK, 1 row affected (0.10 sec)

在server2（从）查看

  mysql> select * from westos.userlist;
   +----------+----------+ | username | password |
   +----------+----------+ | user1    | 123      |
   +----------+----------+ 1 row in set (0.00 sec)

在master（server1）上授权（授予全部权限，但是仅仅是实验环境所与不太在意安全）

mysql> grant all privileges on *.* to 'root'@'%' identified by 'Aa316256.';
Query OK, 0 rows affected, 1 warning (0.15 sec)

（2）server3 proxy环境配置

[root@server3 mysql-proxy]# systemctl stop mysqld		##停止mysqld服务，防止影响

下载安装包并解压到指定目录

[root@server3 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz  
[root@server3 ~]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy

建立目录存放读写分离的配置文件和日志

[root@server3 ~]# cd /usr/local/mysql-proxy/
[root@server3 mysql-proxy]# ls
bin  include  lib  libexec  licenses  share
[root@server3 mysql-proxy]# mkdir conf
[root@server3 mysql-proxy]# mkdir logs

将mysql-proxy的二进制命令放进系统环境变量中

[root@server3 mysql-proxy]# vim ~/.bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql-proxy/bin
[root@server3 mysql-proxy]# source ~/.bash_profile

修改数据库发生读写分离时的最大最小值

[root@server3 ~]# cd /usr/local/mysql-proxy/share/doc/mysql-proxy
[root@server3 mysql-proxy]# vim rw-splitting.lua
 40                 min_idle_connections = 1,  ##最小连接数
 41                 max_idle_connections = 2,  ##最大连接数，最大连接数大于2时发生读写分离
实现读写分离是有lua脚本实现的，现在mysql-proxy里面已经集成，无需再安装

创建配置文件

 [root@server3 ~]# cd /usr/local/mysql-proxy/conf/
[root@server3 conf]# vim mysql-proxy.conf
[mysql-proxy]
user=root     										   ##运行mysql-proxy用户
proxy-address=0.0.0.0:3306   						   ##mysql-proxy运行ip和端口
proxy-read-only-backend-addresses=172.25.26.2:3306     ##slave用户：只读
proxy-backend-addresses=172.25.26.1:3306    		   ##master用户：可读写
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua     ##lua脚本地址
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log   ##日志位置
log-level=debug  #定义log日志级别，由高到低分别有(error|warning|info|message|debug)
daemon=true 										   ##打入后台
keepalive=true   ##mysql-proxy崩溃时，尝试重启（持续连接）

给文件设置权限，再启动mysql-proxy（否则会启动失败）

[root@server3 conf]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf

启动：
[root@server3 conf]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

此时查看端口3306是否打开

[root@server3 conf]# netstat -anltp

在这里插入图片描述
（3）测试
在物理机上安装mysql客户端
在物理机上打开三个shell，都通过server3连接数据库进行如下操作：

[root@foundation26 ~]# mysql -h 172.25.26.3 -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| westos             |
+--------------------+
5 rows in set (0.00 sec)

MySQL [(none)]> select * from westos.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user1    | 123      |
+----------+----------+
2 rows in set (0.00 sec)

测试1：

(1)master和slave状态都开启
(2)在物理机上通过server3连接数据库，向数据库的表中插入新的数据信息

MySQL [(none)]> use westos;
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 [westos]> insert into userlist values('user2','222');
Query OK, 1 row affected (0.08 sec)

(3)在server1（主）和server2（从）上都可以看到添加的信息
在这里插入图片描述

注意：
在master端可以看到数据，说明写操作是在server1（master）上，而在server2上看到数据，是因为server1和server2是主从复制关系。（不能说明server2可以进行写操作）

测试2

（1）关闭server2（slave）

mysql> stop slave;
Query OK, 0 rows affected (0.05 sec)

（2）物理机添加新的信息，在master上可以查看到，slave上无法看到

MySQL [westos]> insert into userlist values('user3','333');
Query OK, 1 row affected (0.37 sec)

master上查看
在这里插入图片描述
slave上不能查看到

说明：读写分离了，读操作是在server2（slave）上

测试3

1.关掉server1（master）的mysql，开启server2的slave
server1
在这里插入图片描述
server2

2.在物理机上连接数据库，发现插入数据失败

说明：读写分离了，写操作在server1（master）上

测试4：通过tcpdump抓取数据读写分离

(1).server3上安装tcpdump抓包工具

[root@server3 conf]# yum install -y tcpdump

(2).server3上执行以下命令：可以看到客户端登陆mysql-proxy的操作

[root@server3 conf]# tcpdump -i eth0 port 3306

(3)执行上一条命令后不要关掉，然后在物理机上连接数据库，然后给表中插入一条新的信息后，在server3上看到相应的抓包信息
在这里插入图片描述
发现当连接3个物理机后，执行写操作是通过server3写入到server2（msater）中的