注意:在进行读写分离前先在server1和server2实现主从复制,主从复制的过程在这就不啰嗦了,详情请看mysql主从复制那篇博客
- server3配置
1.下载mysql-proxy安装包并解压到指定目录下
[root@server3 ~]# ls
mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@server3 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/
2.切换到/usr/local/ 目录下,制作软链接
[root@server3 ~]# cd /usr/local/
[root@server3 local]# ll
[root@server3 local]# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit mysql-proxy
[root@server3 local]# ls
bin games lib libexec mysql-proxy-0.8.5-linux-el6-x86-64bit share
etc include lib64 mysql-proxy sbin
3.建立目录存放读写分离的配置文件和日志
[root@server3 local]# cd mysql-proxy
[root@server3 mysql-proxy]# ls
bin include lib libexec licenses share
[root@server3 mysql-proxy]# mkdir conf
[root@server3 mysql-proxy]# mkdir log
[root@server3 mysql-proxy]# ls
bin conf include lib libexec licenses share log
4.将mysql-proxy的二进制命令放进系统环境变量中
[root@server3 ~]# vim ~/.bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql-proxy/bin
[root@server3 ~]# source ~/.bash_profile
5.修改数据库发生读写分离时的最大值和最小值
[root@server3 mysql-proxy]# cd share/
[root@server3 share]# ls
doc
[root@server3 share]# cd doc/mysql-proxy/
[root@server3 mysql-proxy]# vim rw-splitting.lua
min_idle_connections = 1, ##最小连接数
max_idle_connections = 2, ##最大连接数,最大连接数大于2时发生读写分离
6.编辑配置文件
[root@server3 mysql-proxy]# cd conf/
[root@server3 conf]# vim mysql-proxy.conf
[mysql-proxy]
proxy-address=0.0.0.0:3306
proxy-read-only-backend-addresses=172.25.63.2:3306 ##只读
proxy-backend-addresses=172.25.63.1:3306 ##可读写
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua #lua脚本地址
pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log ##日志位置
plugins=proxy
log-level=debug
keepalive=true ##mysql-proxy崩溃时,尝试重启
daemon=true ##打入后台
7.给文件设置权限,在启动mysql-proxy否则会失败
[root@server3 conf]# chmod 600 /usr/local/mysql-proxy/conf/mysql-proxy.conf
[root@server3 conf]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
8.查看3306端口已打开
[root@server3 ~]# netstat -anltp
- server1配置
mysql> show databases; ##查看数据
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> grant replication slave on *.* to repl@'172.25.63.%'identified by 'LYly12345@';
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql> show master status; ##查看状态
+---------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+----------------------------------------+
| binlog.000001 | 447 | | | 5f8f63a3-71c6-11e9-98fb-525400401903:1 |
+---------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
- 测试主从复制
mysql> create database westos; ##新建库
Query OK, 1 row affected (0.03 sec)
mysql> grant insert,update,select on *.* to wsp@'%' identified by 'LYly12345@'; ##给用户添加权限
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> flush privileges; ##刷新授权表
Query OK, 0 rows affected (0.04 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.22 sec)
mysql> insert into usertb values ('user1','123'); ##表中插入数据
Query OK, 1 row affected (0.09 sec)
mysql> select * from usertb; ##查找
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
+----------+----------+
1 row in set (0.00 sec)
- server2配置
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> change master to master_host='172.25.63.1',master_user='repl', master_password='LYly12345@',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.27 sec)
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> show slave status\G ##查看slave状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.63.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 1403
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 1610
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
- server2上能同步server1的信息,主从复制成功
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
mysql> select * from usertb; ##查找
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
+----------+----------+
1 row in set (0.00 sec)
- 读写分离测试一:
1.物理机安装mysql
[root@foundation63 ~]# yum install mysql -y
2.物理机上打开一个shell,通过server3连接数据库,查看信息同步成功
[root@foundation63 ~]# mysql -h 172.25.63.3 -uwsp -pLYly12345@
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 4
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)]> 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]> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
+----------+----------+
1 row in set (0.00 sec)
3.在物理机上通过server3连接数据库,向数据库的表中插入新的数据
MySQL [westos]> insert into usertb values ('user2','123');
Query OK, 1 row affected (0.10 sec)
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
+----------+----------+
2 rows in set (0.00 sec)
4.在server1和server2上都可以看到刚刚新添加的信息
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
+----------+----------+
2 rows in set (0.00 sec)
注意:在server2上可以看到数据,是因为server1和server2是主从复制关系,不能体现读写分离
5.关闭server2的slave
mysql> stop slave;
Query OK, 0 rows affected (0.04 sec)
6.在物理机上通过server3连接数据库,向数据库的表中插入新的数据
MySQL [westos]> insert into usertb values ('user3','123');
Query OK, 1 row affected (0.07 sec)
7.server1上可以查看到新建的信息
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
| user3 | 123 |
+----------+----------+
3 rows in set (0.00 sec)
server2上不能查看到新建的信息
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
+----------+----------+
2 rows in set (0.00 sec)
总结:说明读写分离了,写是在serevr1上,读是在server2上
- 实验二:
1.关掉serevr1的mysqld
[root@server1 ~]# systemctl stop mysqld
2.打开serevr2的slave
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
3.在物理机上连接数据库,插入数据失败
MySQL [westos]> insert into usertb values ('user4','123');
ERROR 2013 (HY000): Lost connection to MySQL server during query
MySQL [westos]>
总结:说明读写分离了,写是在serevr1上,读是在server2上
- 实验三:
1.server3上安装lsof软件
[root@server3 ~]# yum install lsof-4.87-4.el7.x86_64
2.server3上执行此命令,可以看到客户登陆mysql-proxy的操作
[root@server3 ~]# lsof -i:3306
3.物理机上通过server3连接数据库,连接三个(打开三个shell)
[kiosk@foundation63 ~]$ mysql -h 172.25.63.3 -uwsp -pLYly12345@
4.当连接三个物理机后,执行写操作是通过server3写入到server2中的