############mysql-proxy实现读写分离##########

读写分离可以用很多软件实现：mysql-proxy 、MyCat 、Amoeba

westos1:172.25.40.11	master
westos2:172.25.40.12	slave
westos3:172.25.40.13	mysql-proxy

用westos3来做proxy，1和2一个读一个写
做以下实验之前，先做好westos1和westos2的gtid主从复制

westos3上：
#先关闭之前的mysql，因为proxy也用3306端口

[root@westos3 mysql]# systemctl stop mysqld
[root@westos3 mysql]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/
[root@westos3 mysql]# cd /usr/local/
[root@westos3 local]# ls
bin  games    lib    libexec                                sbin   src
etc  include  lib64  mysql-proxy-0.8.5-linux-el6-x86-64bit  share
[root@westos3 local]# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy#软链接便于访问

cd /usr/local/mysql-proxy/bin
./mysql-proxy --help ##查看帮助
./mysql-proxy --help-proxy ##查看proxy的帮助
./mysql-proxy --help-all ##查看所有帮助

编辑读写分离文件

[root@westos3 local]# cd /usr/local/mysql-proxy
[root@westos3 mysql-proxy]# ls
bin  include  lib  libexec  licenses  share
[root@westos3 mysql-proxy]# mkdir conf##建立配置文件目录
[root@westos3 mysql-proxy]# cd conf/
[root@westos3 conf]# vim mysql-proxy.conf#编辑配置文件（自己手动新建）
[mysql-proxy]
proxy-address=0.0.0.0:3306
proxy-backend-addresses=172.25.40.11:3306  ##读写
proxy-read-only-backend-addresses=172.25.40.12:3306  ##只读
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid
plugins=proxy
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log
log-level=debug
keepalive=true
daemon=true

编辑脚本

[root@westos3 conf]# mkdir /usr/local/mysql-proxy/log #创建日志目录
[root@westos3 mysql-proxy]# cd /usr/local/mysql-proxy
[root@westos3 mysql-proxy]# ls
bin  conf  include  lib  libexec  licenses  log  share

[root@westos3 conf]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua   #修改lua脚本

38 if not proxy.global.config.rwsplit then
 39         proxy.global.config.rwsplit = {
 40                 min_idle_connections = 1,
 41                 max_idle_connections = 2,##把原来的4和8改为1和2，默认超过4个连接才会启动读写分离，改为1个好测试
 42 
 43                 is_debug = false
 44         }

#启动mysql-proxy会报错

[root@westos3 conf]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
2019-07-04 15:52:43: (critical) mysql-proxy-cli.c:326: loading config from '/usr/local/mysql-proxy/conf/mysql-proxy.conf' failed: permissions of /usr/local/mysql-proxy/conf/mysql-proxy.conf aren't secure (0660 or stricter required)
2019-07-04 15:52:43: (message) Initiating shutdown, requested from mysql-proxy-cli.c:328
2019-07-04 15:52:43: (message) shutting down normally, exit code is: 1
[root@westos3 conf]# chmod 660  /usr/local/mysql-proxy/conf/mysql-proxy.conf #因为配置文件权限过大
[root@westos3 conf]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf  再启动，正常
[root@westos3 conf]# netstat -antlp
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      11979/mysql-proxy   
 ##mysql-proxy  端口打开

#查看日志
cat /usr/local/mysql-proxy/log/mysql-proxy.log
看到两个节点都加进来了

westos1和westos2做主从复制

[root@westos1 mysql]# systemctl stop mysqld
[root@westos1 mysql]# rm -fr /var/lib/mysql/*
[root@westos1 mysql]# vim /etc/my.cnf
server_id=1
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=true
[root@westos1 mysql]# systemctl start mysqld
[root@westos1 mysql]# cat /var/log/mysqld.log | grep password
2019-07-04T08:04:36.954951Z 1 [Note] A temporary password is generated for root@localhost: U#u+&rs=P0kp
[root@westos1 mysql]# mysql -uroot -p
Enter password: 
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 'Westos+001';
Query OK, 0 rows affected (0.45 sec)

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

westos2:

[root@westos2 mysql]# systemctl stop mysqld
[root@westos2 mysql]# rm -fr /var/lib/mysql/*
[root@westos2 mysql]# vim /etc/my.cnf
server_id=2
gtid_mode=ON
enforce-gtid-consistency=true
[root@westos2 mysql]# systemctl start mysqld
[root@westos2 mysql]# cat /var/log/mysqld.log | grep password
2019-07-04T08:05:01.815538Z 1 [Note] A temporary password is generated for root@localhost: ftsocQ67cL_(
[root@westos2 mysql]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24

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 'Westos+001';
Query OK, 0 rows affected (0.00 sec)

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

#在westos1上

mysql> grant replication slave on *.* to repl@'172.25.40.%' identified by 'Westos+001';
Query OK, 0 rows affected, 1 warning (0.37 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000002 |      691 |              |                  | 84095f83-9e33-11e9-9d9d-525400807d36:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

westos2:

mysql> CHANGE MASTER TO
    -> MASTER_HOST = '172.25.40.11',
    -> MASTER_USER = 'repl',
    -> MASTER_PASSWORD = 'Westos+001',
    -> MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.18 sec)

mysql> start slave;
Query OK, 0 rows affected (0.04 sec)

mysql> start slave status\G
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'status' at line 1
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.40.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 691
               Relay_Log_File: westos2-relay-bin.000002
                Relay_Log_Pos: 904
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

#在westos11上授权新用户读写权限

mysql> grant insert,update,select on *.* to kiosk@'%' identified by 'Westos+001';
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> create database westos;
Query OK, 1 row affected (0.35 sec)

mysql> use westos;
Database changed
mysql> create table usertb (
    -> username varchar(10) not null,
    ->  password varchar(15) not null);
Query OK, 0 rows affected (0.82 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.01 sec)

#在物理机上打开一个shell来连接数据库

[kiosk@foundation40 ~]$ mysql -h 172.25.40.13 -ukiosk -pWestos+001

westos3看到连接已经建立:

[root@westos3 conf]# lsof -i:3306
COMMAND     PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 12099 root   10u  IPv4  41019      0t0  TCP *:mysql (LISTEN)
mysql-pro 12099 root   11u  IPv4  41023      0t0  TCP westos3:mysql->172.25.40.250:38010 (ESTABLISHED)
mysql-pro 12099 root   12u  IPv4  41024      0t0  TCP westos3:49396->westos1:mysql (ESTABLISHED)

#在物理机上打开一个shell来连接数据库
[kiosk@foundation40 ~]$ mysql -h 172.25.40.13 -uwsp -pWestos+001

#然后在server3上看到连接已经建立
[root@westos3 log]# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 2521 root 10u IPv4 28318 0t0 TCP *:mysql (LISTEN)
mysql-pro 2521 root 11u IPv4 28362 0t0 TCP server3:mysql->foundation0.ilt.example.com:55716 (ESTABLISHED)
mysql-pro 2521 root 12u IPv4 28363 0t0 TCP server3:47376->westos1:mysql (ESTABLISHED)

#在物理机上再打开一个shell来连接数据库
#再到westos3上查看
[root@swestos3 log]# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 2521 root 10u IPv4 28318 0t0 TCP *:mysql (LISTEN)
mysql-pro 2521 root 11u IPv4 28362 0t0 TCP server3:mysql->foundation0.ilt.example.com:55716 (ESTABLISHED)
mysql-pro 2521 root 12u IPv4 28363 0t0 TCP server3:47376->server1:mysql (ESTABLISHED)
mysql-pro 2521 root 13u IPv4 28709 0t0 TCP server3:mysql->foundation0.ilt.example.com:55722 (ESTABLISHED)
mysql-pro 2521 root 14u IPv4 28710 0t0 TCP server3:47382->westos1:mysql (ESTABLISHED)

‘发现两次连接都指向server1’

#在物理机上再打开一个shell来连接数据库
#再到westos3上查看
[root@westos3 log]# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 2521 root 10u IPv4 28318 0t0 TCP *:mysql (LISTEN)
mysql-pro 2521 root 11u IPv4 28362 0t0 TCP server3:mysql->foundation0.ilt.example.com:55716 (ESTABLISHED)
mysql-pro 2521 root 12u IPv4 28363 0t0 TCP server3:47376->westos1:mysql (ESTABLISHED)
mysql-pro 2521 root 13u IPv4 28709 0t0 TCP server3:mysql->foundation0.ilt.example.com:55722 (ESTABLISHED)
mysql-pro 2521 root 14u IPv4 28710 0t0 TCP server3:47382->westos1:mysql (ESTABLISHED)
mysql-pro 2521 root 15u IPv4 28769 0t0 TCP server3:mysql->foundation0.ilt.example.com:55726 (ESTABLISHED)
mysql-pro 2521 root 16u IPv4 28770 0t0 TCP server3:55914->westos2:mysql (ESTABLISHED)

‘这次连接到了server2，说明读写分离启用’

#测试读写分离
在物理机上插入数据：
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
MySQL [westos]> insert into usertb values(‘user1’,‘123’);
MySQL [westos]> select * from usertb;
±---------±---------+
| username | password |
±---------±---------+
| user1 | 123 |
±---------±---------+
1 row in set (0.00 sec)

发现server1和server2都能看到，看不出读写分离，因为存在主从复制

现在到server2上关闭复制
mysql> stop slave;

这时再在物理机上插入数据
MySQL [westos]> insert into usertb values (‘user4’,‘123’);
Query OK, 1 row affected (0.01 sec)

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

发现插入的数据看不到，但是在server1上可以看到，这就说明了它在读的时候读的是server2上的数据，而写操作却写在了server1上