mysql-proxy实现读写分离:
来源:互联网 发布:人工智能的伦理学思考 编辑:程序博客网 时间:2024/05/22 07:45
一:mysql主从数据库的配置
要求:配置主
从复制: server2 主 172.25.28.2
server3 从 172.25.28.3
二:安装配置mysql-proxy
[root@server1 /]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit -C /usr/local/
[root@server1 local]# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy ##简化路径
[root@server1 local]# mkdir /usr/local/mysql-proxy/conf[root@server1 local]# mkdir /usr/local/mysql-proxy/log
[root@server1 local]# vim mysql-proxy/conf/mysql-proxy.conf ##编译配置文件
[mysql-proxy]daemon=true #打入后台user=root #keepalive=trueplugins=proxy,adminlog-level=info #日志级别log-file=/usr/local/mysql-proxy/log/mysql-proxy.log ##proxy日志地址proxy-address=172.25.28.1:3306 #本机ip地址 proxy-backend-addresses=172.25.28.2:3306 ##backend主 注意addressesproxy-read-only-backend-addresses=172.25.28.3:3306 ##backend从proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua ##lua脚本地址admin-address=172.25.28.1:4041 ##proxy的管理用户adminiphe端口admin-username=adminadmin-password=westosadmin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua #admin的lua脚本地址;
更改lua脚本
[root@server1 local]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
使其快速进入读写分离状态
-- connection poolif not proxy.global.config.rwsplit then proxy.global.config.rwsplit = { min_idle_connections = 1, ##最小连接数 max_idle_connections = 2, ##最大连接数后实现读写分离 is_debug = false }
开启proxy控制
[root@server1 local]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
查看日志状态:
[root@server1 mysql-proxy]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf[root@server1 mysql-proxy]#cat log/mysql-proxy.log2017-10-21 13:15:54: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=1409 alive2017-10-21 13:15:54: (critical) plugin proxy 0.8.5 started2017-10-21 13:15:54: (critical) plugin admin 0.8.5 started2017-10-21 13:15:54: (critical) network-socket.c:492: bind(172.25.28.1:3306) failed: Address already in use (98)2017-10-21 13:15:54: (critical) chassis-mainloop.c:270: applying config of plugin proxy failed2017-10-21 13:15:54: (critical) mysql-proxy-cli.c:599: Failure from chassis_mainloop. Shutting down.2017-10-21 13:15:54: (message) Initiating shutdown, requested from mysql-proxy-cli.c:6002017-10-21 13:15:54: (message) shutting down normally, exit code is: 12017-10-21 13:15:54: (critical) chassis-unix-daemon.c:189: [angel] PID=1380 died on signal=11 (it used 2 kBytes max) ... waiting 3min before restart2017-10-21 13:15:54: (message) chassis-unix-daemon.c:176: [angel] PID=1409 exited normally with exit-code = 1 (it used 1 kBytes max)2017-10-21 13:15:54: (message) Initiating shutdown, requested from mysql-proxy-cli.c:2732017-10-21 13:15:54: (message) shutting down normally, exit code is: 12017-10-21 13:15:56: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=1410 alive2017-10-21 13:15:56: (critical) plugin proxy 0.8.5 started2017-10-21 13:15:56: (critical) plugin admin 0.8.5 started2017-10-21 13:15:56: (message) proxy listening on port 172.25.28.1:33062017-10-21 13:15:56: (message) added read/write backend: 172.25.28.2:33062017-10-21 13:15:56: (message) added read-only backend: 172.25.28.3:33062017-10-21 13:15:56: (message) admin-server listening on port 172.25.28.1:4041[root@server1 mysql-proxy]#
根据日志可以查看已经控制到mysql
过滤端口:
[root@server1 mysql-proxy]# netstat -anltpActive 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:22 0.0.0.0:* LISTEN 936/sshd tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1012/master tcp 0 0 172.25.28.1:4041 0.0.0.0:* LISTEN 1410/mysql-proxy tcp 0 0 172.25.28.1:3306 0.0.0.0:* LISTEN 1410/mysql-proxy tcp 0 0 172.25.28.1:53135 172.25.28.2:3306 TIME_WAIT - tcp 0 0 172.25.28.1:22 172.25.28.250:55476 ESTABLISHED 1072/sshd tcp 0 0 172.25.28.1:53137 172.25.28.2:3306 TIME_WAIT - tcp 0 0 :::22 :::* LISTEN 936/sshd tcp 0 0 ::1:25 :::* LISTEN 1012/master
z在master(server1)端给zpy用户权限:
mysql> grant all on test.* to zpy@'%' identified by 'zpy';Query OK, 0 rows affected (0.00 sec)
下来我们开始检测:
在mysql安装lsof:
[root@server2 ~]# yum install -y lsof
重新开一台设备:
[root@foundation28 mysql10.21]# mysql -h 172.25.28.1 -u zpy -p ##通过访问proxy,直接转到mysqlEnter password: Welcome to the MariaDB monitor. Commands end with ; or \g.Your MySQL connection id is 8Server version: 5.1.71-log Source distributionCopyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
如上每登陆一次,使用lsof观察3306 端口,连接一次
[root@server2 ~]# lsof -i :3306COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAMEmysqld 1411 mysql 11u IPv4 9514 0t0 TCP *:mysql (LISTEN)mysqld 1411 mysql 31u IPv4 10201 0t0 TCP 172.25.28.2:mysql->172.25.28.1:53132 (ESTABLISHED)
当连接数大于2(lua脚本中设置的时);即启动了读写分离机制:
我们可以登陆admin p’roxy管理用户查看:
[root@foundation28 mysql10.21]# mysql -h 172.25.28.1 -u admin -pwestos -P 4041;Welcome to the MariaDB monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.0.99-agent-adminCopyright (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)]> select * from backends;+-------------+------------------+---------+------+------+-------------------+| backend_ndx | address | state | type | uuid | connected_clients |+-------------+------------------+---------+------+------+-------------------+| 1 | 172.25.28.2:3306 | up | rw | NULL | 0 || 2 | 172.25.28.3:3306 | unknown | ro | NULL | 0 |+-------------+------------------+---------+------+------+-------------------+2 rows in set (0.00 sec)MySQL [(none)]> Bye[root@foundation28 mysql10.21]#
当然我们也可以使用上一篇所用的关闭I/O线程,然后写入数据最后在两端查看不同;;
阅读全文
0 0
- MySQL Proxy 实现读写分离
- mysql-proxy实现读写分离
- mysql-proxy 实现读写分离
- mysql-proxy实现读写分离
- mysql-proxy实现读写分离
- mysql-proxy 实现读写分离
- mysql-proxy实现读写分离
- mysql-proxy实现读写分离
- mysql-proxy实现读写分离:
- MySQL-Proxy实现MySQL读写分离
- mysql实现读写分离中间件MySQL Proxy
- MySQL-Proxy实现MySQL读写分离
- mysql读写分离-mysql proxy实现
- mysql+mmm+proxy实现mysql读写分离
- 用MySQL-Proxy实现读写分离
- 用MySQL-Proxy实现读写分离
- 如何使用"MySQL-Proxy"实现读写分离
- 用MySQL-Proxy实现读写分离
- 字符串中筛选数字
- 中点Bresenham算法画圆
- C语言:动态内存malloc,calloc,realloc
- 搭建单击spark
- 李彦宏:百度之父
- mysql-proxy实现读写分离:
- 全面屏新機旗艦
- sqlserver2014怎样导出表数据
- opencv-2.4.10.exe 以及debug.lib和release.lib
- Kali Linux安装VMWare Tools
- caffe 转caffe2模型出现的问题
- 小明的花店---简单DP
- 把电脑安装到公交车的项目灵感
- substance painter中的粒子笔刷重力方向错乱问题