马哥 42_04 _MySQL主从复制——mysql-proxy.0.8.3实现MySQL-5.6读写分离有大用

星期六, 2021-03-20 07:24 — adminshiping1

一主多从

从不能写操作

应用程序连到服务器,应该由从提供读服务,主提供写服务,但应用程序连进来,有可能读,有可能写,,, 比如 discuz,发贴时,就得写,浏览贴,就得读

需要一个专用的服务(设备,mysql的proxy),它能够分析每个sql语句,把写操作定向到主,把读操作定向到从

多个从服务器,负载均衡,平摊请求,提供负载均衡设备,可以是lvm的direcctor,又可以是haproxy等.用户请求过来,负载均衡器进行分发,

不同的请求,同一个查询,分别分发到slaveA,slaveB,如果能缓存查询结果,效果会更好,所以要提供memcached服务器,,所以用户查询时,直接到memcached查行查询,若memcached没有,请求再到director,由director找一个从服务器进行响应,当然从服务器中的结果要先缓存到memcached当中,再返回给客户端(当然mysql的proxy没有这种能力),,,,,,memcached是一个旁路的缓存,缓存中如果没有,得靠应用程序自己去找director,(有些缓存,如果缓存中没有,缓存自己会去找原始服务器,原始服务器返回结果给缓存,缓存存下来后再返回结果给客户端),,,,,memcached只是一个旁路服务器,只是一个api,是需要应用程序自身提供后续功能的

MySQL Proxy (lua):本身只是服务器,并不负责读写分离,以及额外的操作,基于插件化的方式内置了lua引擎,这种引擎能够调用lua脚本所写的任何额外的配置程序,(所以要想使用MySQL Proxy,必须得有lua的编程能力才行),有了lua脚本以后,能够实现查询分析,读写分离,负载均衡,HA等,但是它严重依赖于lua脚本,MySQL Proxy仅仅是提供了在某种场景下让你调用某种特定脚本实现特定功能的框架而已,,,,lua脚本是一种嵌入式脚本,很多应用程序都嵌入lua来实现其额外的二次开发功能(二次配置功能),很多网游服务器都内置了lua引擎,很多配置都使用lua来实现的,MySQL Proxy也是这么一个组件,,,,MySQL Proxy到现在为止,官方仍然认为它是不稳定的,还是0.几的版本,最新的是0.8.3的版本,但是很多企业把MySQL Proxy 用在生产环境中了,但是需要给MySQL Proxy不停的打补丁,修复它的某些缺陷的部分,所以MySQL官方并不特别建议,把MySQL Proxy用在生产环境中,但是很多企业已经用了,只不过它需要依赖于自己的二次开发的功能去修复MySQL Proxy的某些内生缺陷的

Amoeba (Java)淘宝公司一个项目,原作者转投盛大门下,非常好的项目,参考了MySQL Proxy的实现,提供查询路由、查询分析、查询过滤、读写分离、负载均衡、HA、分库等功能,Amoeba 的配置,不像MySQL Proxy,严重依赖于lua脚本,Amoeba是有Java写的,Amoeba提供的一个配置文件是XML格式的,只需要编写xml文档,里面写有几个从服务器,几个主服务器,或者是不是做了分库,把分库的每一个库上所对应的表的所对应的这些分区的相关信息在里面一定义,马上就能够工作起来,不需要额外依赖于脚本,只需要修改配置文件就能够让你实现功能的

Cobar (Java) 本身不是读写分离的,是分库的,是建立在Amoeba 的基础上的,发展为另外一个项目,可以参考阿里巴巴开源项目站点,阿里巴巴另一个tengine比较有名(好像是nginx的改版吧)

https://www.mysql.com/

https://www.mysql.com/downloads/

https://dev.mysql.com/downloads/

https://dev.mysql.com/downloads/mysql/

https://downloads.mysql.com/archives/

https://downloads.mysql.com/archives/utilities/ 很多工具都在这儿了

下图是马哥下载的

https://downloads.mysql.com/archives/

https://downloads.mysql.com/archives/proxy/ 我这边显示最新的是 0.8.5 alpha版本

马哥显示的最新版本是 0.8.3 alpha 版本,可以直接拿来用的

马哥说在红帽6上,建议rpm包可以直接使用,但是rpm提供的是 0.8.2的版本,更稳定,马哥说去 epel站点去下载

百度 epel

得到 https://fedoraproject.org/wiki/EPEL/zh-cn

https://ftp-stud.hs-esslingen.de/pub/Mirrors/archive.fedoraproject.org/epel/6/i386/Packages/m/

mysql-mmm Multi-Master Replication Manager for MySQL,,,MySQL多主模型(双主模型)的时候,安全性不是特别的好,有一种辅助机制,可以让MySQL的双主节点能够工作起来也不错,双主节点并不意味着让写操作分摊负载的,mmm能够提供分摊负载的机制,能够监控我们的服务器的写操作,不致于让我们的服务器处于不一致状态的,只要了解就可以,马哥不介绍了

马哥这边还看到 mytop mysql上的类似于top的监控mysql运行性能的命令

马哥不安装rpm包了,因为太简单了

马哥下载的截图

https://downloads.mysql.com/archives/proxy/ 下面是我的截图

第三个 rhel6节点 192.168.0.62 上中间电脑 MySQL Proxy

来提供MySQL Proxy,将用户的请求(读请求转发到从服务器192.168.0.61)(写请求转发到主服务器192.168.0.60),,

MySQL Proxy 严重依赖于lua,

三、MySQL Proxy

1、源码安装时，MySQL proxy 的依赖关系：

libevent 1.x or higher (1.3b or later is preferred).

lua 5.1.x or higher.

glib2 2.6.0 or higher. #编译时用到的

pkg-config.

libtool 1.5 or higher.

MySQL 5.0.x or higher developer files. #MySQL服务器的开发库,开发头???

如果编译安装MySQL Proxy时,需要以上的东西

如果不是编译安装,是通用二进制格式的,就直接解压使用

我们演示的是通用二进制格式的解压安装的方式

2、安装

# tar zxf mysql-proxy-0.8.2.tar.gz

# cd mysql-proxy-0.8.2

# ./configure

# make

# make check

如果管理员有密码，上面的步骤则需要使用如下格式进行：

# MYSQL_PASSWORD=root_pwd make check

# make install

默认情况下， mysql-proxy安装在/usr/local/sbin/mysql-proxy，而Lua示例脚本安装在/usr/local/share目录中。

3、配置指令

mysql proxy的各配置参数请参见官方文档，http://dev.mysql.com/doc/refman/5.6/en/mysql-proxy-configuration.html

使用rpm包在rhel6上安装mysql-proxy-0.8.2，其会提供配置文件及服务脚本，但没有提供读写分享???(应该是读写分离吧)的脚本。

/etc/sysconfig/mysql-proxy文件用于定义mysql-proxy的启动参数。

ADMIN_USER – the user for the proxy's admin interface. You can leave the default admin user.

ADMIN_PASSWORD – the password for the admin user in clear text. Change the default password for better security.

ADMIN_LUA_SCRIPT – the admin script in the Lua programming language. Without this script the admin interface cannot work. You can leave the default value.

PROXY_USER – the system user under which the proxy will work. By default it is mysql-proxy, and it's safe to leave it as is.

PROXY_OPTIONS – proxy options such as logging level, plugins, and Lua scripts to be loaded.

其中PROXY_OPTIONS是最常用的一个选项，用于定义mysql-proxy工作时的重要参数，例如：

PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog --plugins=proxy --proxy-backend-addresses=192.168.1.102:3306 --proxy-read-only-backend-addresses=192.168.1.105:3306 --proxy-lua-script=/usr/lib/mysql-proxy/lua/proxy/rw-splitting.lua" --plugins=admin

在第三个 rhel6节点 192.168.0.62 上中间电脑 MySQL Proxy

[root@localhost ~]# rpm -q lua #已装过lua

lua-5.1.4-4.1.el6.i686

[root@localhost ~]#

马哥的

https://downloads.mysql.com/archives/proxy/

https://cdn.mysql.com/archives/mysql-proxy/mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz

[root@localhost ~]# wget https://cdn.mysql.com/archives/mysql-proxy/mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz

跟安装mysql差不多,创建用户,解压缩,启动

[root@localhost ~]# useradd -r mysql-proxy #添加系统用户

[root@localhost ~]#

[root@localhost ~]# tar xf mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz -C /usr/local/ #解压

[root@localhost ~]# cd /usr/local/

[root@localhost local]# ls

bin games lib mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit share

etc include libexec sbin src

[root@localhost local]# ln -sv mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit mysql-proxy #创建链接

"mysql-proxy" -> "mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit"

[root@localhost local]#

[root@localhost local]# ll

总用量 40

drwxr-xr-x. 2 root root 4096 6月 28 2011 bin

drwxr-xr-x. 2 root root 4096 6月 28 2011 etc

drwxr-xr-x. 2 root root 4096 6月 28 2011 games

drwxr-xr-x. 2 root root 4096 6月 28 2011 include

drwxr-xr-x. 2 root root 4096 6月 28 2011 lib

drwxr-xr-x. 2 root root 4096 6月 28 2011 libexec

lrwxrwxrwx. 1 root root 42 3月 23 10:52 mysql-proxy -> mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit

drwxr-xr-x. 8 7157 wheel 4096 8月 6 2012 mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit

drwxr-xr-x. 2 root root 4096 6月 28 2011 sbin

drwxr-xr-x. 5 root root 4096 3月 18 23:27 share

drwxr-xr-x. 2 root root 4096 6月 28 2011 src

[root@localhost local]#

[root@localhost local]# cd mysql-proxy

[root@localhost mysql-proxy]# pwd

/usr/local/mysql-proxy

[root@localhost mysql-proxy]#

[root@localhost mysql-proxy]# ls

bin include lib libexec licenses share

[root@localhost mysql-proxy]#

[root@localhost mysql-proxy]# ls bin/

mysql-binlog-dump mysql-myisam-dump mysql-proxy

[root@localhost mysql-proxy]#

[root@localhost mysql-proxy]# vim /etc/profile.d/mysql-proxy.sh

export PATH=$PATH:/usr/local/mysql-proxy/bin

[root@localhost mysql-proxy]# . /etc/profile.d/mysql-proxy.sh #执行,并把程序的执行加入当前shell中

[root@localhost mysql-proxy]#

[root@localhost mysql-proxy]# mysql-proxy --help #显示帮助

Usage:

mysql-proxy [OPTION...] - MySQL Proxy

Help Options:

-?, --help Show help options #显示帮助选项

--help-all Show all help options #显示所有帮助选项

--help-proxy Show options for the proxy-module

Application Options:

-V, --version Show version

--defaults-file=<file> configuration file

--verbose-shutdown Always log the exit code when shutting down

--daemon Start in daemon-mode

--user=<user> Run mysql-proxy as user

--basedir=<absolute path> Base directory to prepend to relative paths in the config

--pid-file=<file> PID file in case we are started as daemon

--plugin-dir=<path> path to the plugins

--plugins=<name> plugins to load

--log-level=(error|warning|info|message|debug) log all messages of level ... or higher

--log-file=<file> log all messages in a file

--log-use-syslog log all messages to syslog

--log-backtrace-on-crash try to invoke debugger on crash

--keepalive try to restart the proxy if it crashed

--max-open-files maximum number of open files (ulimit -n)

--event-threads number of event-handling threads (default: 1)

--lua-path=<...> set the LUA_PATH

--lua-cpath=<...> set the LUA_CPATH

[root@localhost mysql-proxy]#

[root@localhost mysql-proxy]# mysql-proxy --help-all

Usage:

mysql-proxy [OPTION...] - MySQL Proxy

Help Options:

-?, --help Show help options

--help-all Show all help options

--help-proxy Show options for the p roxy-module

proxy-module #代理模块相关的配置

-P, --proxy-address=<host:port> listening address:port of the proxy-server (default: :4040) #工作的哪个地址哪个端口

-r, --proxy-read-only-backend-addresses=<host:port> address:port of the re mote slave-server (default: not set) #只读后端地址端口

-b, --proxy-backend-addresses=<host:port> address:port of the re mote backend-servers (default: 127.0.0.1:3306) #能读能写的后端地址端口

--proxy-skip-profiling disables profiling of queries (default: enabled)

--proxy-fix-bug-25371 fix bug #25371 (mysqld > 5.1.12) for older libmysql versions

-s, --proxy-lua-script=<file> filename of the lua script (default: not set) #为了实现代理功能,我们用的lua脚本的文件路径

--no-proxy don't start the proxy- module (default: enabled)

--proxy-pool-no-change-user don't use CHANGE_USER to reset the connection coming from the pool (default: enabled)

--proxy-connect-timeout connect timeout in seconds (default: 2.0 seconds) #连接超时时长

--proxy-read-timeout read timeout in second s (default: 8 hours) #读超时时长

--proxy-write-timeout write timeout in secon ds (default: 8 hours) #写超时时长

Application Options: #MySQL Proxy 应用程序自身相关的配置

-V, --version Show version

--defaults-file=<file> configuration file #默认读取的配置文件, MySQL Proxy也支持使用配置文件的,可以叫 /etc/mysql-proxy.conf 可以在 /etc/mysql.conf 中加一段 [mysql-proxy],只读这一段里面用在MySQL Proxy上,上面(proxy-module )和下面是可以用到的指令参数,,,设置参数的时候,不要加--

--verbose-shutdown Always log the exit co de when shutting down

--daemon Start in daemon-mode #以守护进程模式运行,不然的话,它会运行在前台的

--user=<user> Run mysql-proxy as user #以这个用户的身份运行我们已经建过了叫 mysql-proxy

--basedir=<absolute path> Base directory to prepend to relative paths in the config #指定配置文件所在的路径

--pid-file=<file> PID file in case we are started as daemon #pid文件路径

--plugin-dir=<path> path to the plugins # 把这个目录下的插件都加载进来加载这个目录下的所有插件

--plugins=<name> plugins to load # 额外加载的插件加载一个或一组插件

--log-level=(error|warning|info|message|debug) log all messages of level ... or higher #日志级别,跟syslog的日志级别不太一样

--log-file=<file> log all messages in a file #日志文件

--log-use-syslog log all messages to syslog #使用syslog 说明它俩没必要同时使用

--log-backtrace-on-crash try to invoke debugger on crash

--keepalive try to restart the proxy if it crashed #如果MySQL Proxy宕机了,崩溃了,会尝试重启MySQL Proxy

--max-open-files maximum number of open files (ulimit -n)

--event-threads number of event-handling threads (default: 1) #事件处理器句柄打开几个线程,默认是一个

--lua-path=<...> set the LUA_PATH #lua相关的路径,lua引擎的路径,不用指,默认lua只要使用rpm包安装的,它会自动找到的

--lua-cpath=<...> set the LUA_CPATH #lua相关的路径,lua引擎的路径,不用指,默认lua只要使用rpm包安装的,它会自动找到的

[root@localhost mysql-proxy]#

在第三个 rhel6节点 192.168.0.62 上中间电脑 MySQL Proxy

[root@localhost mysql-proxy]# pwd

/usr/local/mysql-proxy

[root@localhost mysql-proxy]# ls

bin include lib libexec licenses share

[root@localhost mysql-proxy]# ls lib

libevent-1.4.so.2 libmysql-chassis-glibext.so.0

libevent-1.4.so.2.1.3 libmysql-chassis-glibext.so.0.0.0

libevent_core-1.4.so.2 libmysql-chassis.la

libevent_core-1.4.so.2.1.3 libmysql-chassis.so

libevent_core.so libmysql-chassis.so.0

libevent_extra-1.4.so.2 libmysql-chassis.so.0.0.0

libevent_extra-1.4.so.2.1.3 libmysql-chassis-timing.la

libevent_extra.so libmysql-chassis-timing.so

libevent.so libmysql-chassis-timing.so.0

libglib-2.0.so libmysql-chassis-timing.so.0.0.0

libglib-2.0.so.0 libmysql-proxy.la

libglib-2.0.so.0.1600.6 libmysql-proxy.so

libgmodule-2.0.so libmysql-proxy.so.0

libgmodule-2.0.so.0 libmysql-proxy.so.0.0.0

libgmodule-2.0.so.0.1600.6 libpcreposix.so

libgthread-2.0.so libpcreposix.so.0

libgthread-2.0.so.0 libpcreposix.so.0.0.0

libgthread-2.0.so.0.1600.6 libpcre.so

liblua-5.1.so libpcre.so.0

liblua.so libpcre.so.0.0.1

libmysql-chassis-glibext.la mysql-proxy

libmysql-chassis-glibext.so pkgconfig

[root@localhost mysql-proxy]#

[root@localhost mysql-proxy]# ls lib/mysql-proxy/

lua plugins

[root@localhost mysql-proxy]# ls lib/mysql-proxy/plugins/ #插件所在目录

libadmin.la libdebug.la libproxy.la libreplicant.la

libadmin.so libdebug.so libproxy.so libreplicant.so

libadmin.so(它是MySQL Proxy给我们提供的一个管理接口) ,可以远程连接到这个管理接口里面,看看后端有多少个服务器,每个服务器当前状态等等,,要用到这个管理接口 --plugins=admin(不要写libadmin)

[root@localhost mysql-proxy]#

[root@localhost mysql-proxy]# mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="admin" --admin-username="admin" --admin-password="admin" --proxy-backend-addresses="192.168.0.60:3306" --proxy-read-only-backend-addresses="192.168.0.61:3306"

--daemon #表示在后台以守护进程的方式运行最好必须吧

--log-level #日志等级

--log-file #日志文件

--plugins=admin #表示使用admin插件

--admin-username #使用admin插件的额外选项用户名

--admin-password #使用admin插件的额外选项密码

--proxy-backend-addresses #后端读写地址最好必须吧

--proxy-read-only-backend-addresses #后端只读地址最好必须吧

[root@localhost mysql-proxy]#

[root@localhost mysql-proxy]# tail /var/log/mysql-proxy.log #有报错

2021-03-23 14:37:28: (critical) mysql-proxy-cli.c:503: Unknown option --admin-user=admin (use --help to show all options) #错

2021-03-23 14:37:28: (message) Initiating shutdown, requested from mysql-proxy-cli.c:513

2021-03-23 14:37:28: (message) shutting down normally, exit code is: 1

[root@localhost mysql-proxy]#

[root@localhost mysql-proxy]# mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="admin" --admin-users="admin" --admin-password="admin" --proxy-backend-addresses="192.168.0.60:3306" --proxy-read-only-backend-addresses="192.168.0.61:3306" # --admin-user 改成 --admin-users

[root@localhost mysql-proxy]#

[root@localhost mysql-proxy]# tail /var/log/mysql-proxy.log #还是有报错

2021-03-23 14:37:28: (critical) mysql-proxy-cli.c:503: Unknown option --admin-user=admin (use --help to show all options)

2021-03-23 14:37:28: (message) Initiating shutdown, requested from mysql-proxy-cli.c:513

2021-03-23 14:37:28: (message) shutting down normally, exit code is: 1

2021-03-23 14:42:00: (critical) mysql-proxy-cli.c:503: Unknown option --admin-users=admin (use --help to show all options) #错

2021-03-23 14:42:00: (message) Initiating shutdown, requested from mysql-proxy-cli.c:513

2021-03-23 14:42:00: (message) shutting down normally, exit code is: 1

[root@localhost mysql-proxy]#

[root@localhost mysql-proxy]# mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="admin" --proxy-backend-addresses="192.168.0.60:3306" --proxy-read-only-backend-addresses="192.168.0.61:3306" #去掉--admin-user="admin" --admin-password="admin" 试试

[root@localhost mysql-proxy]#

[root@localhost mysql-proxy]# tail /var/log/mysql-proxy.log #仍然有错

2021-03-23 14:36:40: (message) shutting down normally, exit code is: 1

2021-03-23 14:37:28: (critical) mysql-proxy-cli.c:503: Unknown option --admin-user=admin (use --help to show all options)

2021-03-23 14:37:28: (message) Initiating shutdown, requested from mysql-proxy-cli.c:513

2021-03-23 14:37:28: (message) shutting down normally, exit code is: 1

2021-03-23 14:42:00: (critical) mysql-proxy-cli.c:503: Unknown option --admin-users=admin (use --help to show all options)

2021-03-23 14:42:00: (message) Initiating shutdown, requested from mysql-proxy-cli.c:513

2021-03-23 14:42:00: (message) shutting down normally, exit code is: 1

2021-03-23 14:44:28: (critical) mysql-proxy-cli.c:503: Unknown option --proxy-backend-addresses=192.168.0.60:3306 (use --help to show all options) #错

2021-03-23 14:44:28: (message) Initiating shutdown, requested from mysql-proxy-cli.c:513

2021-03-23 14:44:28: (message) shutting down normally, exit code is: 1

[root@localhost mysql-proxy]#

[root@localhost mysql-proxy]# mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="admin" -b 192.168.0.60:3306 -r 192.168.0.61:3306 #改成-b -r 试试

[root@localhost mysql-proxy]#

[root@localhost mysql-proxy]# tail /var/log/mysql-proxy.log #还有错

2021-03-23 14:37:28: (message) shutting down normally, exit code is: 1

2021-03-23 14:42:00: (critical) mysql-proxy-cli.c:503: Unknown option --admin-users=admin (use --help to show all options)

2021-03-23 14:42:00: (message) Initiating shutdown, requested from mysql-proxy-cli.c:513

2021-03-23 14:42:00: (message) shutting down normally, exit code is: 1

2021-03-23 14:44:28: (critical) mysql-proxy-cli.c:503: Unknown option --proxy-backend-addresses=192.168.0.60:3306 (use --help to show all options)

2021-03-23 14:44:28: (message) Initiating shutdown, requested from mysql-proxy-cli.c:513

2021-03-23 14:44:28: (message) shutting down normally, exit code is: 1

2021-03-23 14:48:14: (critical) mysql-proxy-cli.c:503: Unknown option -b (use --help to show all options) #错代理模块没加载???

2021-03-23 14:48:14: (message) Initiating shutdown, requested from mysql-proxy-cli.c:513

2021-03-23 14:48:14: (message) shutting down normally, exit code is: 1

[root@localhost mysql-proxy]#

MySQL 5.6引入的GTID(Global Transaction IDs)使得其复制功能的配置、监控及管理变得更加易于实现，且更加健壮。

要在MySQL 5.6中使用复制功能，其服务配置段[mysqld]中于少应该定义如下选项：

binlog-format：二进制日志的格式，有row、statement和mixed几种类型；

需要注意的是：当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW，现在MySQL官方认为STATEMENT这个已经不再适合继续使用；但mixed类型在默认的事务隔离级别下，可能会导致主从数据不一致；

log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port和report-host：用于启动GTID及满足附属的其它需求；

master-info-repository和relay-log-info-repository：启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能；

sync-master-info：启用之可确保无信息丢失；

slave-paralles-workers：设定从服务器的SQL线程数；0表示关闭多线程复制功能；

binlog-checksum、master-verify-checksum和slave-sql-verify-checksum：启用复制有关的所有校验功能；

binlog-rows-query-log-events：启用之可用于在二进制日志记录事件相关的信息，可降低故障排除的复杂度；

log-bin：启用二进制日志，这是保证复制功能的基本前提；

server-id：同一个复制拓扑中的所有服务器的id号必须惟一；

report-host：

The host name or IP address of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server.

report-port:

The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration.

master-info-repository:

The setting of this variable determines whether the slave logs master status and connection information to a FILE (master.info), or to a TABLE (mysql.slave_master_info)

relay-log-info-repository：

This option causes the server to log its relay log info to a file or a table.

log_slave_updates：

Whether updates received by a slave server from a master server should be logged to the slave's own binary log. Binary logging must be enabled on the slave for this variable to have any effect.

enforce_gtid_consistency：

一、简单主从模式配置步骤

1、配置主从节点的服务配置文件

1.1、配置master节点：

[mysqld]

binlog-format=ROW

log-bin=master-bin

log-slave-updates=true

gtid-mode=on

enforce-gtid-consistency=true

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=2

binlog-checksum=CRC32

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log_events=1

server-id=1

report-port=3306

port=3306

datadir=/mydata/data

socket=/tmp/mysql.sock

report-host=master.magedu.com

1.2、配置slave节点：

[mysqld]

binlog-format=ROW

log-slave-updates=true

gtid-mode=on

enforce-gtid-consistency=true

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=2

binlog-checksum=CRC32

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log_events=1

server-id=11

report-port=3306

port=3306

log-bin=mysql-bin.log

datadir=/mydata/data

socket=/tmp/mysql.sock

report-host=slave.magedu.com

2、创建复制用户

mysql> GRANT REPLICATION SLAVE ON *.* TO repluser@172.16.100.7 IDENTIFIED BY 'replpass';

说明：172.16.100.7是从节点服务器；如果想一次性授权更多的节点，可以自行根据需要修改；

3、为备节点提供初始数据集

锁定主表，备份主节点上的数据，将其还原至从节点；如果没有启用GTID，在备份时需要在master上使用show master status命令查看二进制日志文件名称及事件位置，以便后面启动slave节点时使用。

4、启动从节点的复制线程

如果启用了GTID功能，则使用如下命令：

mysql> CHANGE MASTER TO MASTER_HOST='master.magedu.com', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_AUTO_POSITION=1;

没启用GTID，需要使用如下命令：

slave> CHANGE MASTER TO MASTER_HOST='172.16.100.6',

-> MASTER_USER='repluser',

-> MASTER_PASSWORD='replpass',

-> MASTER_LOG_FILE='master-bin.000003',

-> MASTER_LOG_POS=1174;

二、半同步复制

1、分别在主从节点上安装相关的插件

master> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

slave> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

2、启用半同步复制

在master上的配置文件中，添加

rpl_semi_sync_master_enabled=ON

在至少一个slave节点的配置文件中添加

rpl_semi_sync_slave_enabled=ON

而后重新启动mysql服务即可生效。

或者，也可以mysql服务上动态启动其相关功能：

master> SET GLOBAL rpl_semi_sync_master_enabled = ON;

slave> SET GLOBAL rpl_semi_sync_slave_enabled = ON;

slave> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;

3、确认半同步功能已经启用

master> CREATE DATABASE magedudb;

master> SHOW STATUS LIKE 'Rpl_semi_sync_master_yes_tx';

slave> SHOW DATABASES;

三、MySQL Proxy

1、源码安装时，MySQL proxy的依赖关系：

libevent 1.x or higher (1.3b or later is preferred).

lua 5.1.x or higher.

glib2 2.6.0 or higher.

pkg-config.

libtool 1.5 or higher.

MySQL 5.0.x or higher developer files.

2、安装

# tar zxf mysql-proxy-0.8.2.tar.gz

# cd mysql-proxy-0.8.2

# ./configure

# make

# make check

如果管理员有密码，上面的步骤则需要使用如下格式进行：

# MYSQL_PASSWORD=root_pwd make check

# make install

默认情况下， mysql-proxy安装在/usr/local/sbin/mysql-proxy，而Lua示例脚本安装在/usr/local/share目录中。

3、配置指令

mysql proxy的各配置参数请参见官方文档，http://dev.mysql.com/doc/refman/5.6/en/mysql-proxy-configuration.html

使用rpm包在rhel6上安装mysql-proxy-0.8.2，其会提供配置文件及服务脚本，但没有提供读写分享的脚本。

/etc/sysconfig/mysql-proxy文件用于定义mysql-proxy的启动参数。

ADMIN_USER – the user for the proxy's admin interface. You can leave the default admin user.

ADMIN_PASSWORD – the password for the admin user in clear text. Change the default password for better security.

ADMIN_LUA_SCRIPT – the admin script in the Lua programming language. Without this script the admin interface cannot work. You can leave the default value.

PROXY_USER – the system user under which the proxy will work. By default it is mysql-proxy, and it's safe to leave it as is.

PROXY_OPTIONS – proxy options such as logging level, plugins, and Lua scripts to be loaded.

其中PROXY_OPTIONS是最常用的一个选项，用于定义mysql-proxy工作时的重要参数，例如：

四、安装配置mysql-proxy:

4.1 下载所需要的版本，这里的系统平台为rhel6.4 32位系统，因此就以mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz为例。

# tar xf mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz -C /usr/local

# cd /usr/local

# ln -sv mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit mysql-proxy

添加代理用户

# useradd mysql-proxy

4.2 为mysql-proxy提供SysV服务脚本，内容如下所示

#!/bin/bash

# mysql-proxy This script starts and stops the mysql-proxy daemon

# chkconfig: - 78 30

# processname: mysql-proxy

# description: mysql-proxy is a proxy daemon for mysql

# Source function library.

. /etc/rc.d/init.d/functions

prog="/usr/local/mysql-proxy/bin/mysql-proxy"

# Source networking configuration.

if [ -f /etc/sysconfig/network ]; then

. /etc/sysconfig/network

# Check that networking is up.

[ ${NETWORKING} = "no" ] && exit 0

# Set default mysql-proxy configuration.

ADMIN_USER="admin"

ADMIN_PASSWD="admin"

ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

PROXY_OPTIONS="--daemon"

PROXY_PID=/var/run/mysql-proxy.pid

PROXY_USER="mysql-proxy"

# Source mysql-proxy configuration.

if [ -f /etc/sysconfig/mysql-proxy ]; then

. /etc/sysconfig/mysql-proxy

RETVAL=0

start() {

echo -n $"Starting $prog: "

daemon $prog $PROXY_OPTIONS --pid-file=$PROXY_PID --proxy-address="$PROXY_ADDRESS" --user=$PROXY_USER --admin-username="$ADMIN_USER" --admin-lua-script="$ADMIN_LUA_SCRIPT" --admin-password="$ADMIN_PASSWORD"

RETVAL=$?

echo

if [ $RETVAL -eq 0 ]; then

touch /var/lock/subsys/mysql-proxy

}

stop() {

echo -n $"Stopping $prog: "

killproc -p $PROXY_PID -d 3 $prog

RETVAL=$?

echo

if [ $RETVAL -eq 0 ]; then

rm -f /var/lock/subsys/mysql-proxy

rm -f $PROXY_PID

}

# See how we were called.

case "$1" in

start)

start

;;

stop)

stop

;;

restart)

stop

start

;;

condrestart|try-restart)

if status -p $PROXY_PIDFILE $prog >&/dev/null; then

stop

start

;;

status)

status -p $PROXY_PID $prog

;;

echo "Usage: $0 {start|stop|restart|reload|status|condrestart|try-restart}"

RETVAL=1

;;

esac

exit $RETVAL

将上述内容保存为/etc/rc.d/init.d/mysql-proxy，给予执行权限，而后加入到服务列表。

# chmod +x /etc/rc.d/init.d/mysql-proxy

# chkconfig --add mysql-proxy

4.3 为服务脚本提供配置文件/etc/sysconfig/mysql-proxy，内容如下所示：

# Options for mysql-proxy

ADMIN_USER="admin"

ADMIN_PASSWORD="admin"

ADMIN_ADDRESS="" #地址加端口

ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

PROXY_ADDRESS="" #地址加端口

PROXY_USER="mysql-proxy"

PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog"

其中最后一行，需要按实际场景进行修改，例如：

PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog --plugins=proxy --proxy-backend-addresses=172.16.100.6:3306 --proxy-read-only-backend-addresses=172.16.100.7:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua --plugins=admin "

其中的proxy-backend-addresses选项和proxy-read-only-backend-addresses选项均可重复使用多次，以实现指定多个读写服务器或只读服务器。

4.4 mysql-proxy的配置选项

mysql-proxy的配置选项大致可分为帮助选项、管理选项、代理选项及应用程序选项几类，下面一起去介绍它们。

--help

--help-admin

--help-proxy

--help-all ———— 以上四个选项均用于获取帮助信息；

--proxy-address=host:port ———— 代理服务监听的地址和端口；

--admin-address=host:port ———— 管理模块监听的地址和端口；

--proxy-backend-addresses=host:port ———— 后端mysql服务器的地址和端口；

--proxy-read-only-backend-addresses=host:port ———— 后端只读mysql服务器的地址和端口；

--proxy-lua-script=file_name ———— 完成mysql代理功能的Lua脚本；

--daemon ———— 以守护进程模式启动mysql-proxy；

--keepalive ———— 在mysql-proxy崩溃时尝试重启之；

--log-file=/path/to/log_file_name ———— 日志文件名称；

--log-level=level ———— 日志级别；

--log-use-syslog ———— 基于syslog记录日志；

--plugins=plugin,.. ———— 在mysql-proxy启动时加载的插件；

--user=user_name ———— 运行mysql-proxy进程的用户；

--defaults-file=/path/to/conf_file_name ———— 默认使用的配置文件路径；其配置段使用[mysql-proxy]标识；

--proxy-skip-profiling ———— 禁用profile；

--pid-file=/path/to/pid_file_name ———— 进程文件名；

5、复制如下内容建立admin.lua文件，将其保存至/usr/local/mysql-proxy/share/doc/mysql-proxy/目录中。

--[[ $%BEGINLICENSE%$

This program is free software; you can redistribute it and/or

modify it under the terms of the GNU General Public License as

published by the Free Software Foundation; version 2 of the

License.

This program is distributed in the hope that it will be useful,

but WITHOUT ANY WARRANTY; without even the implied warranty of

MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

GNU General Public License for more details.

You should have received a copy of the GNU General Public License

along with this program; if not, write to the Free Software

Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA

02110-1301 USA

$%ENDLICENSE%$ --]] #上面是注释,把注释复制过来,格式上可能有错误,从下面复制吧

function set_error(errmsg)

proxy.response = {

type = proxy.MYSQLD_PACKET_ERR,

errmsg = errmsg or "error"

}

end

function read_query(packet)

if packet:byte() ~= proxy.COM_QUERY then

set_error("[admin] we only handle text-based queries (COM_QUERY)")

return proxy.PROXY_SEND_RESULT

end

local query = packet:sub(2)

local rows = { }

local fields = { }

if query:lower() == "select * from backends" then

fields = {

{ name = "backend_ndx",

type = proxy.MYSQL_TYPE_LONG },

{ name = "address",

type = proxy.MYSQL_TYPE_STRING },

{ name = "state",

type = proxy.MYSQL_TYPE_STRING },

{ name = "type",

type = proxy.MYSQL_TYPE_STRING },

{ name = "uuid",

type = proxy.MYSQL_TYPE_STRING },

{ name = "connected_clients",

type = proxy.MYSQL_TYPE_LONG },

}

for i = 1, #proxy.global.backends do

local states = {

"unknown",

"up",

"down"

}

local types = {

"unknown",

"rw",

"ro"

}

local b = proxy.global.backends[i]

rows[#rows + 1] = {

b.dst.name, -- configured backend address

states[b.state + 1], -- the C-id is pushed down starting at 0

types[b.type + 1], -- the C-id is pushed down starting at 0

b.uuid, -- the MySQL Server's UUID if it is managed

b.connected_clients -- currently connected clients

}

end

elseif query:lower() == "select * from help" then

fields = {

{ name = "command",

type = proxy.MYSQL_TYPE_STRING },

{ name = "description",

type = proxy.MYSQL_TYPE_STRING },

}

rows[#rows + 1] = { "SELECT * FROM help", "shows this help" }

rows[#rows + 1] = { "SELECT * FROM backends", "lists the backends and their state" }

else

set_error("use 'SELECT * FROM help' to see the supported commands")

return proxy.PROXY_SEND_RESULT

end

proxy.response = {

type = proxy.MYSQLD_PACKET_OK,

resultset = {

fields = fields,

rows = rows

}

return proxy.PROXY_SEND_RESULT

end

6、测试

6.1 管理功能测试

# mysql -uadmin -padmin -h172.16.100.107 --port=4041

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.0.99-agent-admin

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM backends;

+-------------+-------------------+-------+------+------+-------------------+

+-------------+-------------------+-------+------+------+-------------------+

| 1 | 172.16.100.6:3306 | up | rw | NULL | 0 |

| 2 | 172.16.100.7:3306 | up | ro | NULL | 0 |

+-------------+-------------------+-------+------+------+-------------------+

2 rows in set (0.00 sec)

6.2 读写分离测试

# mysql -uroot -pmagedu.com -h172.16.100.107 --port=4040

下面的读写分享脚本是由mysql-proxy-0.8.3提供了，将其复制保存为/usr/lib/mysql-proxy/lua/proxy/rw-splitting.lua，就可以启动服务了。

--[[ $%BEGINLICENSE%$

This program is free software; you can redistribute it and/or

modify it under the terms of the GNU General Public License as

published by the Free Software Foundation; version 2 of the

License.

This program is distributed in the hope that it will be useful,

but WITHOUT ANY WARRANTY; without even the implied warranty of

MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

GNU General Public License for more details.

You should have received a copy of the GNU General Public License

along with this program; if not, write to the Free Software

Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA

02110-1301 USA

$%ENDLICENSE%$ --]]

---

-- a flexible statement based load balancer with connection pooling

-- * build a connection pool of min_idle_connections for each backend and maintain

-- its size

-- *

local commands = require("proxy.commands")

local tokenizer = require("proxy.tokenizer")

local lb = require("proxy.balance")

local auto_config = require("proxy.auto-config")

--- config

-- connection pool

if not proxy.global.config.rwsplit then

proxy.global.config.rwsplit = {

min_idle_connections = 4,

max_idle_connections = 8,

is_debug = false

}

end

---

-- read/write splitting sends all non-transactional SELECTs to the slaves

-- is_in_transaction tracks the state of the transactions

local is_in_transaction = false

-- if this was a SELECT SQL_CALC_FOUND_ROWS ... stay on the same connections

local is_in_select_calc_found_rows = false

---

-- get a connection to a backend

-- as long as we don't have enough connections in the pool, create new connections

function connect_server()

local is_debug = proxy.global.config.rwsplit.is_debug

-- make sure that we connect to each backend at least ones to

-- keep the connections to the servers alive

-- on read_query we can switch the backends again to another backend

if is_debug then

print()

print("[connect_server] " .. proxy.connection.client.src.name)

end

local rw_ndx = 0

-- init all backends

for i = 1, #proxy.global.backends do

local s = proxy.global.backends[i]

local pool = s.pool -- we don't have a username yet, try to find a connections which is idling

local cur_idle = pool.users[""].cur_idle_connections

pool.min_idle_connections = proxy.global.config.rwsplit.min_idle_connections

pool.max_idle_connections = proxy.global.config.rwsplit.max_idle_connections

if is_debug then

print(" [".. i .."].connected_clients = " .. s.connected_clients)

print(" [".. i .."].pool.cur_idle = " .. cur_idle)

print(" [".. i .."].pool.max_idle = " .. pool.max_idle_connections)

print(" [".. i .."].pool.min_idle = " .. pool.min_idle_connections)

print(" [".. i .."].type = " .. s.type)

print(" [".. i .."].state = " .. s.state)

end

-- prefer connections to the master

if s.type == proxy.BACKEND_TYPE_RW and

s.state ~= proxy.BACKEND_STATE_DOWN and

cur_idle < pool.min_idle_connections then

proxy.connection.backend_ndx = i

break

elseif s.type == proxy.BACKEND_TYPE_RO and

s.state ~= proxy.BACKEND_STATE_DOWN and

cur_idle < pool.min_idle_connections then

proxy.connection.backend_ndx = i

break

elseif s.type == proxy.BACKEND_TYPE_RW and

s.state ~= proxy.BACKEND_STATE_DOWN and

rw_ndx == 0 then

rw_ndx = i

end

if proxy.connection.backend_ndx == 0 then

if is_debug then

print(" [" .. rw_ndx .. "] taking master as default")

end

proxy.connection.backend_ndx = rw_ndx

end

-- pick a random backend

-- we someone have to skip DOWN backends

-- ok, did we got a backend ?

if proxy.connection.server then

if is_debug then

print(" using pooled connection from: " .. proxy.connection.backend_ndx)

end

-- stay with it

return proxy.PROXY_IGNORE_RESULT

end

if is_debug then

print(" [" .. proxy.connection.backend_ndx .. "] idle-conns below min-idle")

end

-- open a new connection

end

---

-- put the successfully authed connection into the connection pool

-- @param auth the context information for the auth

-- auth.packet is the packet

function read_auth_result( auth )

if is_debug then

print("[read_auth_result] " .. proxy.connection.client.src.name)

end

if auth.packet:byte() == proxy.MYSQLD_PACKET_OK then

-- auth was fine, disconnect from the server

proxy.connection.backend_ndx = 0

elseif auth.packet:byte() == proxy.MYSQLD_PACKET_EOF then

-- we received either a

-- * MYSQLD_PACKET_ERR and the auth failed or

-- * MYSQLD_PACKET_EOF which means a OLD PASSWORD (4.0) was sent

print("(read_auth_result) ... not ok yet");

elseif auth.packet:byte() == proxy.MYSQLD_PACKET_ERR then

-- auth failed

end

---

-- read/write splitting

function read_query( packet )

local is_debug = proxy.global.config.rwsplit.is_debug

local cmd = commands.parse(packet)

local c = proxy.connection.client

local r = auto_config.handle(cmd)

if r then return r end

local tokens

local norm_query

-- looks like we have to forward this statement to a backend

if is_debug then

print("[read_query] " .. proxy.connection.client.src.name)

print(" current backend = " .. proxy.connection.backend_ndx)

print(" client default db = " .. c.default_db)

print(" client username = " .. c.username)

if cmd.type == proxy.COM_QUERY then

print(" query = " .. cmd.query)

end

if cmd.type == proxy.COM_QUIT then

-- don't send COM_QUIT to the backend. We manage the connection

-- in all aspects.

proxy.response = {

type = proxy.MYSQLD_PACKET_OK,

}

if is_debug then

print(" (QUIT) current backend = " .. proxy.connection.backend_ndx)

end

return proxy.PROXY_SEND_RESULT

end

-- COM_BINLOG_DUMP packet can't be balanced

-- so we must send it always to the master

if cmd.type == proxy.COM_BINLOG_DUMP then

-- if we don't have a backend selected, let's pick the master

if proxy.connection.backend_ndx == 0 then

proxy.connection.backend_ndx = lb.idle_failsafe_rw()

end

return

end

proxy.queries:append(1, packet, { resultset_is_needed = true })

-- read/write splitting

-- send all non-transactional SELECTs to a slave

if not is_in_transaction and

cmd.type == proxy.COM_QUERY then

tokens = tokens or assert(tokenizer.tokenize(cmd.query))

local stmt = tokenizer.first_stmt_token(tokens)

if stmt.token_name == "TK_SQL_SELECT" then

is_in_select_calc_found_rows = false

local is_insert_id = false

for i = 1, #tokens do

local token = tokens[i]

-- SQL_CALC_FOUND_ROWS + FOUND_ROWS() have to be executed

-- on the same connection

-- print("token: " .. token.token_name)

-- print(" val: " .. token.text)

if not is_in_select_calc_found_rows and token.token_name == "TK_SQL_SQL_CALC_FOUND_ROWS" then

is_in_select_calc_found_rows = true

elseif not is_insert_id and token.token_name == "TK_LITERAL" then

local utext = token.text:upper()

if utext == "LAST_INSERT_ID" or

utext == "@@INSERT_ID" then

is_insert_id = true

end

-- we found the two special token, we can't find more

if is_insert_id and is_in_select_calc_found_rows then

break

end

-- if we ask for the last-insert-id we have to ask it on the original

-- connection

if not is_insert_id then

local backend_ndx = lb.idle_ro()

if backend_ndx > 0 then

proxy.connection.backend_ndx = backend_ndx

end

else

print(" found a SELECT LAST_INSERT_ID(), staying on the same backend")

end

-- no backend selected yet, pick a master

if proxy.connection.backend_ndx == 0 then

-- we don't have a backend right now

-- let's pick a master as a good default

proxy.connection.backend_ndx = lb.idle_failsafe_rw()

end

-- by now we should have a backend

-- in case the master is down, we have to close the client connections

-- otherwise we can go on

if proxy.connection.backend_ndx == 0 then

return proxy.PROXY_SEND_QUERY

end

local s = proxy.connection.server

-- if client and server db don't match, adjust the server-side

-- skip it if we send a INIT_DB anyway

if cmd.type ~= proxy.COM_INIT_DB and

c.default_db and c.default_db ~= s.default_db then

print(" server default db: " .. s.default_db)

print(" client default db: " .. c.default_db)

print(" syncronizing")

proxy.queries:prepend(2, string.char(proxy.COM_INIT_DB) .. c.default_db, { resultset_is_needed = true })

end

-- send to master

if is_debug then

if proxy.connection.backend_ndx > 0 then

local b = proxy.global.backends[proxy.connection.backend_ndx]

print(" sending to backend : " .. b.dst.name);

print(" is_slave : " .. tostring(b.type == proxy.BACKEND_TYPE_RO));

print(" server default db: " .. s.default_db)

print(" server username : " .. s.username)

end

print(" in_trans : " .. tostring(is_in_transaction))

print(" in_calc_found : " .. tostring(is_in_select_calc_found_rows))

print(" COM_QUERY : " .. tostring(cmd.type == proxy.COM_QUERY))

end

return proxy.PROXY_SEND_QUERY

end

---

-- as long as we are in a transaction keep the connection

-- otherwise release it so another client can use it

function read_query_result( inj )

local is_debug = proxy.global.config.rwsplit.is_debug

local res = assert(inj.resultset)

local flags = res.flags

if inj.id ~= 1 then

-- ignore the result of the USE <default_db>

-- the DB might not exist on the backend, what do do ?

if inj.id == 2 then

-- the injected INIT_DB failed as the slave doesn't have this DB

-- or doesn't have permissions to read from it

if res.query_status == proxy.MYSQLD_PACKET_ERR then

proxy.queries:reset()

proxy.response = {

type = proxy.MYSQLD_PACKET_ERR,

errmsg = "can't change DB ".. proxy.connection.client.default_db ..

" to on slave " .. proxy.global.backends[proxy.connection.backend_ndx].dst.name

}

return proxy.PROXY_SEND_RESULT

end

return proxy.PROXY_IGNORE_RESULT

end

is_in_transaction = flags.in_trans

local have_last_insert_id = (res.insert_id and (res.insert_id > 0))

if not is_in_transaction and

not is_in_select_calc_found_rows and

not have_last_insert_id then

-- release the backend

proxy.connection.backend_ndx = 0

elseif is_debug then

print("(read_query_result) staying on the same backend")

print(" in_trans : " .. tostring(is_in_transaction))

print(" in_calc_found : " .. tostring(is_in_select_calc_found_rows))

print(" have_insert_id : " .. tostring(have_last_insert_id))

end

---

-- close the connections if we have enough connections in the pool

-- @return nil - close connection

-- IGNORE_RESULT - store connection in the pool

function disconnect_client()

local is_debug = proxy.global.config.rwsplit.is_debug

if is_debug then

print("[disconnect_client] " .. proxy.connection.client.src.name)

end

-- make sure we are disconnection from the connection

-- to move the connection into the pool

proxy.connection.backend_ndx = 0

end

[root@localhost mysql-proxy]# mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="192.168.0.60:3306" --proxy-read-only-backend-addresses="192.168.0.61:3306" #把 --plugins="admin" 改成 --plugins="proxy"

[root@localhost mysql-proxy]#

[root@localhost mysql-proxy]# tail /var/log/mysql-proxy.log #正常了

2021-03-23 14:44:28: (message) Initiating shutdown, requested from mysql-proxy-cli.c:513

2021-03-23 14:44:28: (message) shutting down normally, exit code is: 1

2021-03-23 14:48:14: (critical) mysql-proxy-cli.c:503: Unknown option -b (use --help to show all options)

2021-03-23 14:48:14: (message) Initiating shutdown, requested from mysql-proxy-cli.c:513

2021-03-23 14:48:14: (message) shutting down normally, exit code is: 1

2021-03-23 14:53:03: (critical) plugin proxy 0.8.3 started

2021-03-23 14:53:03: (debug) max open file-descriptors = 1024

2021-03-23 14:53:03: (message) proxy listening on port :4040 #监听在4040端口

2021-03-23 14:53:03: (message) added read/write backend: 192.168.0.60:3306

2021-03-23 14:53:03: (message) added read-only backend: 192.168.0.61:3306

[root@localhost mysql-proxy]#

[root@localhost mysql-proxy]# netstat -tnlp

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name

tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN 1761/rpcbind

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 2105/sshd

tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN 1839/cupsd

tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 2184/master

tcp 0 0 0.0.0.0:42567 0.0.0.0:* LISTEN 1783/rpc.statd

tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 3387/mysql-proxy #4040端口启动了

tcp 0 0 :::111 :::* LISTEN 1761/rpcbind

tcp 0 0 :::22 :::* LISTEN 2105/sshd

tcp 0 0 ::1:631 :::* LISTEN 1839/cupsd

tcp 0 0 :::44920 :::* LISTEN 1783/rpc.statd

tcp 0 0 ::1:25 :::* LISTEN 2184/master

[root@localhost mysql-proxy]#

在第二个ha2 rhel6节点 192.168.0.61 上从服务器

[root@slave ~]# mysql -uroot -p -h192.168.0.62 --port=4040

Enter password:

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.0.62' (113) #连不上,管理员密码不能为空??

[root@slave ~]#

在第一个ha1 rhel6节点 192.168.0.60 上主服务器

创建一个能够允许远程访问管理员的帐号

mysql> grant all on *.* to root@'%' identified by 'aaaaaa';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql>

在第二个ha2 rhel6节点 192.168.0.61 上从服务器

[root@slave ~]# mysql -uroot -p -h192.168.0.62 --port=4040 #还是连不上

Enter password:

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.0.62' (113) #这里出现113很可能是iptables的阻碍,去掉防火墙的防护就可以了

[root@slave ~]#

马哥连自己都连不上，说什么主机名，反解拒绝，我这边是可以连接自己的

马哥看看关于dns

马哥看看关于 hostname

马哥看看关于 name 跳过名称解析

修改只读变量 skip_name_resolve ,要改配置文件

马哥用从服务器来连主服务器是可以的

我这边也是可以的

[root@slave ~]# mysql -u root -p -h 192.168.0.60

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 7

Server version: 5.6.10-log MySQL Community Server (GPL)

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>

马哥这一次为何连上去了呢连的是代理本身,既不是主,也不是从

在第二个ha2 rhel6节点 192.168.0.61 上从服务器

[root@slave ~]# mysql -u root -p -h 192.168.0.62 --port=4040 #我这里为什么始终连不上代理服务器 192.168.0.62 (可能是防火墙的问题吧把 192.168.0.62防火墙关掉就可以了 )

Enter password:

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.0.62' (113)

[root@slave ~]#

在第三个 rhel6节点 192.168.0.62 上中间电脑 MySQL Proxy 上,执行 # iptables -F # service iptables save 就可以了

在第二个ha2 rhel6节点 192.168.0.61 上从服务器

[root@slave yum.repos.d]# mysql -uroot -p -h192.168.0.62 --port=4040 #连上去了此时连的是代理服务器

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 12

Server version: 5.6.10-log MySQL Community Server (GPL)

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> create database hellodb; #假如现在连是的从,这里进行写操作的话,那么从上有,主上没有,,,,,连的是主的话,就是主上有,从上也有

Query OK, 1 row affected (0.02 sec)

mysql>

在第一个ha1 rhel6节点 192.168.0.60 上主服务器

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| hellodb | #主上有,说明代理连到主上来了

| mydb |

| mysql |

| performance_schema |

| test |

+--------------------+

6 rows in set (0.02 sec)

mysql>

在第二个ha2 rhel6节点 192.168.0.61 上从服务器

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| hellodb | #从上也有了(因为主上有,会复制过去的)

| mydb |

| mysql |

| performance_schema |

| test |

+--------------------+

6 rows in set (0.04 sec)

mysql>

上面碰巧连到主上了,因为MySQL Proxy自己不会实现读写分离的功能,刚才只是凑巧而已,要想实现读写分离,得依赖于lua脚本

在第三个 rhel6节点 192.168.0.62 上中间电脑 MySQL Proxy

[root@localhost mysql-proxy]# killall mysql-proxy #杀死代理守护进程

[root@localhost mysql-proxy]#

[root@localhost mysql-proxy]# mysql-proxy --help-all

Usage:

mysql-proxy [OPTION...] - MySQL Proxy

Help Options:

-?, --help Show help options

--help-all Show all help options

--help-proxy Show options for the proxy-module

proxy-module

-P, --proxy-address=<host:port> listening address:port of the proxy-server (default: :4040)

-r, --proxy-read-only-backend-addresses=<host:port> address:port of the remote slave-server (default: not set)

-b, --proxy-backend-addresses=<host:port> address:port of the remote backend-servers (default: 127.0.0.1:3306)

--proxy-skip-profiling disables profiling of queries (default: enabled)

--proxy-fix-bug-25371 fix bug #25371 (mysqld > 5.1.12) for older libmysql versions

-s, --proxy-lua-script=<file> filename of the lua script (default: not set) #代理脚本的文件名要指定读写分离脚本(下面的 rw-splitting.lua),它才能实现读写分离的,,,,,若没有 --proxy-lua-script这个选项,虽然可以自动实现类似于读写分离的功能,但是它的读写分离功能很一般的,,刚才创建数据库在主服务器上,真的是凑巧了

--no-proxy don't start the proxy-module (default: enabled)

--proxy-pool-no-change-user don't use CHANGE_USER to reset the connection coming from the pool (default: enabled)

--proxy-connect-timeout connect timeout in seconds (default: 2.0 seconds)

--proxy-read-timeout read timeout in seconds (default: 8 hours)

--proxy-write-timeout write timeout in seconds (default: 8 hours)

Application Options:

-V, --version Show version

--defaults-file=<file> configuration file

--verbose-shutdown Always log the exit code when shutting down

--daemon Start in daemon-mode

--user=<user> Run mysql-proxy as user

--basedir=<absolute path> Base directory to prepend to relative paths in the config

--pid-file=<file> PID file in case we are started as daemon

--plugin-dir=<path> path to the plugins

--plugins=<name> plugins to load

--log-level=(error|warning|info|message|debug) log all messages of level ... or higher

--log-file=<file> log all messages in a file

--log-use-syslog log all messages to syslog

--log-backtrace-on-crash try to invoke debugger on crash

--keepalive try to restart the proxy if it crashed

--max-open-files maximum number of open files (ulimit -n)

--event-threads number of event-handling threads (default: 1)

--lua-path=<...> set the LUA_PATH

--lua-cpath=<...> set the LUA_CPATH

[root@localhost mysql-proxy]#

[root@localhost mysql-proxy]# pwd

/usr/local/mysql-proxy

[root@localhost mysql-proxy]# ls

bin include lib libexec licenses share

[root@localhost mysql-proxy]# cd share/doc/mysql-proxy/

[root@localhost mysql-proxy]# ls

active-queries.lua ro-balance.lua tutorial-resultset.lua

active-transactions.lua ro-pooling.lua tutorial-rewrite.lua

admin-sql.lua rw-splitting.lua tutorial-routing.lua

analyze-query.lua tutorial-basic.lua tutorial-scramble.lua

auditing.lua tutorial-constants.lua tutorial-states.lua

commit-obfuscator.lua tutorial-inject.lua tutorial-tokenize.lua

commit-obfuscator.msc tutorial-keepalive.lua tutorial-union.lua

COPYING tutorial-monitor.lua tutorial-warnings.lua

histogram.lua tutorial-packets.lua xtab.lua

load-multi.lua tutorial-prep-stmts.lua

README tutorial-query-time.lua

#rw-splitting.lua 读写分离的lua脚本

[root@localhost mysql-proxy]# pwd

/usr/local/mysql-proxy/share/doc/mysql-proxy

[root@localhost mysql-proxy]#

[root@localhost mysql-proxy]# hostname mysql-proxy.magedu.com

[root@localhost mysql-proxy]# vim /etc/sysconfig/network

NETWORKING=yes

HOSTNAME=mysql-proxy.magedu.com

重新给在第三个 rhel6节点 192.168.0.62 上中间电脑 MySQL Proxy 打开一个新窗口

[root@mysql-proxy mysql-proxy]# mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="192.168.0.60:3306" --proxy-read-only-backend-addresses="192.168.0.61:3306" --proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" #只给上面的多了个--proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" 脚本,才能真正实现读写分离的功能

[root@mysql-proxy mysql-proxy]#

[root@mysql-proxy mysql-proxy]# tail /var/log/mysql-proxy.log 2021-03-23 17:21:50: (message) Initiating shutdown, requested from mysql-proxy-c

2021-03-23 17:21:50: (message) shutting down normally, exit code is: 1

2021-03-23 17:28:12: (critical) mysql-proxy-cli.c:503: Unknown option --proxy-re.168.0.61:3306 (use --help to show all options)

2021-03-23 17:28:12: (message) Initiating shutdown, requested from mysql-proxy-c

2021-03-23 17:28:12: (message) shutting down normally, exit code is: 1

2021-03-23 17:28:35: (critical) plugin proxy 0.8.3 started

2021-03-23 17:28:35: (debug) max open file-descriptors = 1024 #最多能打开1024个文件

2021-03-23 17:28:35: (message) proxy listening on port :4040 #说明启动了

2021-03-23 17:28:35: (message) added read/write backend: 192.168.0.60:3306

2021-03-23 17:28:35: (message) added read-only backend: 192.168.0.61:3306

,,,,,,,,,,,马哥说凑巧了,,,马哥又说,刚才为什么到主服务器去创建,是因为我们设置从服务器read-only的,现在由于我们由于给它传递了一个读写分离脚本,才能真正意义实现读写分离的

[root@mysql-proxy mysql-proxy]#

有个admin模块,要传递几个选项, --admin-username,--admin-password,,要实现admin功能,需要admin插件,所以要加上 --plugins=admin,还要指定admin的lua脚本--admin-lua-script="$ADMIN_LUA_SCRIPT",这个lua脚本,MySQL Proxy 0.8.3没有给我们提供一个合适的脚本,马哥提供了,见上面 "复制如下内容建立admin.lua文件"

第三个 rhel6节点 192.168.0.62 上中间电脑 MySQL Proxy

[root@mysql-proxy ~]# cd /usr/local/mysql-proxy/share/doc/mysql-proxy/

[root@mysql-proxy mysql-proxy]# pwd

/usr/local/mysql-proxy/share/doc/mysql-proxy

[root@mysql-proxy mysql-proxy]#

[root@mysql-proxy mysql-proxy]# vim admin.lua

function set_error(errmsg)

proxy.response = {

type = proxy.MYSQLD_PACKET_ERR,

errmsg = errmsg or "error"

}

end

function read_query(packet)

if packet:byte() ~= proxy.COM_QUERY then

set_error("[admin] we only handle text-based queries (COM_QUERY)")

return proxy.PROXY_SEND_RESULT

end

local query = packet:sub(2)

local rows = { }

local fields = { }

if query:lower() == "select * from backends" then

fields = {

{ name = "backend_ndx",

type = proxy.MYSQL_TYPE_LONG },

{ name = "address",

type = proxy.MYSQL_TYPE_STRING },

{ name = "state",

type = proxy.MYSQL_TYPE_STRING },

{ name = "type",

type = proxy.MYSQL_TYPE_STRING },

{ name = "uuid",

type = proxy.MYSQL_TYPE_STRING },

{ name = "connected_clients",

type = proxy.MYSQL_TYPE_LONG },

}

for i = 1, #proxy.global.backends do

local states = {

"unknown",

"up",

"down"

}

local types = {

"unknown",

"rw",

"ro"

}

local b = proxy.global.backends[i]

rows[#rows + 1] = {

b.dst.name, -- configured backend address

states[b.state + 1], -- the C-id is pushed down starting at 0

types[b.type + 1], -- the C-id is pushed down starting at 0

b.uuid, -- the MySQL Server's UUID if it is managed

b.connected_clients -- currently connected clients

}

end

elseif query:lower() == "select * from help" then

fields = {

{ name = "command",

type = proxy.MYSQL_TYPE_STRING },

{ name = "description",

type = proxy.MYSQL_TYPE_STRING },

}

rows[#rows + 1] = { "SELECT * FROM help", "shows this help" }

rows[#rows + 1] = { "SELECT * FROM backends", "lists the backends and their state" }

else

set_error("use 'SELECT * FROM help' to see the supported commands")

return proxy.PROXY_SEND_RESULT

end

proxy.response = {

type = proxy.MYSQLD_PACKET_OK,

resultset = {

fields = fields,

rows = rows

}

return proxy.PROXY_SEND_RESULT

end

[root@mysql-proxy mysql-proxy]# killall mysql-proxy #有这个进程的话,就杀死它吧

mysql-proxy: 没有进程被杀死

[root@mysql-proxy mysql-proxy]#

第三个 rhel6节点 192.168.0.62 上中间电脑 MySQL Proxy

[root@mysql-proxy mysql-proxy]# mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="192.168.0.60:3306" --proxy-read-only-backend-addresses="192.168.0.61:3306" --proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" --plugins="admin" --admin-username="admin" --admin-password="admin" --admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua" #这里只是比上次添加了 --plugins="admin" --admin-username="admin" --admin-password="admin" --admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua" 是关于插件 admin的内容

[root@mysql-proxy mysql-proxy]#

[root@mysql-proxy mysql-proxy]# tail /var/log/mysql-proxy.log #无报错

2021-03-23 17:28:35: (debug) max open file-descriptors = 1024

2021-03-23 17:28:35: (message) proxy listening on port :4040

2021-03-23 17:28:35: (message) added read/write backend: 192.168.0.60:3306

2021-03-23 17:28:35: (message) added read-only backend: 192.168.0.61:3306

2021-03-24 09:46:42: (critical) plugin proxy 0.8.3 started

2021-03-24 09:46:42: (critical) plugin admin 0.8.3 started

2021-03-24 09:46:42: (debug) max open file-descriptors = 1024

2021-03-24 09:46:42: (message) proxy listening on port :4040

2021-03-24 09:46:42: (message) added read/write backend: 192.168.0.60:3306

2021-03-24 09:46:42: (message) added read-only backend: 192.168.0.61:3306

[root@mysql-proxy mysql-proxy]#

[root@mysql-proxy mysql-proxy]# netstat -tnlp

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name

tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 2969/mysql-proxy #这里多了4041,这是管理接口

tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN 1758/rpcbind

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 2103/sshd

tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN 1837/cupsd

tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 2193/master

tcp 0 0 0.0.0.0:47801 0.0.0.0:* LISTEN 1780/rpc.statd

tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 2969/mysql-proxy #这里4040

tcp 0 0 :::111 :::* LISTEN 1758/rpcbind

tcp 0 0 :::22 :::* LISTEN 2103/sshd

tcp 0 0 ::1:631 :::* LISTEN 1837/cupsd

tcp 0 0 ::1:25 :::* LISTEN 2193/master

tcp 0 0 :::52644 :::* LISTEN 1780/rpc.statd

[root@mysql-proxy mysql-proxy]#

在第二个ha2 rhel6节点 192.168.0.61 上从服务器

[root@slave ~]# mysql -uadmin -padmin -h192.168.0.62 --port=4041 #连上MySQL Proxy的admin模块了了,,,,这管理接口只能执行一个命令 select * from backends

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 1

Server version: 5.0.99-agent-admin #显示是代理管理接口

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>

mysql> select * from backends; #两个后端

+-------------+-------------------+---------+------+------+----------------

+-------------+-------------------+---------+------+------+----------------

+-------------+-------------------+---------+------+------+----------------

2 rows in set (0.00 sec)

mysql>

在第二个ha2 rhel6节点 192.168.0.61 上从服务器第二个窗口

[root@slave ~]# mysql -uroot -paaaaaa -h192.168.0.62 --port=4040 -e 'create database mageducom;' #这里连上代理执行sql语句 -e 表示执行sql语句

Warning: Using a password on the command line interface can be insecure.

[root@slave ~]#

在第二个ha2 rhel6节点 192.168.0.61 上从服务器第一个窗口

mysql> select * from backends; #因为刚刚执行了写操作,所以这里连到主服务器上执行一次写操作,所以写节点 state为up了,,,,所以刚才的写一定是在主服务器上执行的

+-------------+-------------------+---------+------+------+-------------------+

+-------------+-------------------+---------+------+------+-------------------+

| 1 | 192.168.0.60:3306 | up | rw | NULL | 0 |

| 2 | 192.168.0.61:3306 | unknown | ro | NULL | 0 |

+-------------+-------------------+---------+------+------+-------------------+

2 rows in set (0.00 sec)

在第二个ha2 rhel6节点 192.168.0.61 上从服务器第二个窗口

[root@slave ~]# mysql -uroot -paaaaaa -h192.168.0.62 --port=4040 -e 'select user, password from mysql.user;' #执行查询操作

Warning: Using a password on the command line interface can be insecure.

+----------+-------------------------------------------+

| user | password |

+----------+-------------------------------------------+

| root | |

| | |

| repluser | *D98280F03D0F78162EBDBB9C883FC01395DEA2BF |

| root | *ED0DE3278EF02C0701FD328CA1DA43F22173FF3F |

| root | *EE840CF03C4E9C47647DD31FFD5CDB7BDF349532 |

+----------+-------------------------------------------+

[root@slave ~]#

在第二个ha2 rhel6节点 192.168.0.61 上从服务器第一个窗口

mysql> select * from backends; #还是主服务器up,刚刚select语句的结果也是从主服务器返回的??

+-------------+-------------------+---------+------+------+-------------------+

+-------------+-------------------+---------+------+------+-------------------+

| 1 | 192.168.0.60:3306 | up | rw | NULL | 0 |

| 2 | 192.168.0.61:3306 | unknown | ro | NULL | 0 |

+-------------+-------------------+---------+------+------+-------------------+

2 rows in set (0.00 sec)

mysql>

在第二个ha2 rhel6节点 192.168.0.61 上从服务器第二个窗口

[root@slave ~]# mysql -uroot -paaaaaa -h192.168.0.62 --port=4040 -e 'show tables from mageducom;' #执行查询 (当然数据库mageducom里面没有表)

Warning: Using a password on the command line interface can be insecure.

[root@slave ~]#

在第二个ha2 rhel6节点 192.168.0.61 上从服务器第一个窗口

mysql> select * from backends; #从服务器还是没有连上

+-------------+-------------------+---------+------+------+---------------- ---+

+-------------+-------------------+---------+------+------+---------------- ---+

| 1 | 192.168.0.60:3306 | up | rw | NULL | 0 |

| 2 | 192.168.0.61:3306 | unknown | ro | NULL | 0 |

+-------------+-------------------+---------+------+------+---------------- ---+

2 rows in set (0.00 sec)

在第二个ha2 rhel6节点 192.168.0.61 上从服务器第二个窗口

[root@slave ~]# mysql -uroot -paaaaaa -h192.168.0.62 --port=4040 -e 'show tables from mageducom;'

Warning: Using a password on the command line interface can be insecure.

ERROR 1047 (08S01): Unknown command #为何报错,说明这里连的是从服务器上, 但是从服务器连不上,,,,为什么从服务器iptables 看下

[root@slave ~]#

在第二个ha2 rhel6节点 192.168.0.61 上从服务器第一个窗口

mysql> select * from backends; #从服务器down,啥意思?想连的是它,但是未连上

+-------------+-------------------+-------+------+------+------------------ -+

+-------------+-------------------+-------+------+------+------------------ -+

| 1 | 192.168.0.60:3306 | up | rw | NULL | 0 |

| 2 | 192.168.0.61:3306 | down | ro | NULL | 3 |

+-------------+-------------------+-------+------+------+------------------ -+

2 rows in set (0.00 sec)

mysql>

#从服务器down,啥意思???把从服务器 192.168.0.61 防火墙关掉吧

# iptables -F

# service iptables save 就行了

最终是

在第二个ha2 rhel6节点 192.168.0.61 上从服务器第二个窗口

[root@slave ~]# mysql -uroot -paaaaaa -h192.168.0.62 --port=4040 -e 'show tables from mageducom;' #正常

Warning: Using a password on the command line interface can be insecure.

[root@slave ~]#

在第二个ha2 rhel6节点 192.168.0.61 上从服务器第一个窗口

mysql> select * from backends; #从管理接口上代理连到从服务器up了,正常了

+-------------+-------------------+-------+------+------+-------------------+

+-------------+-------------------+-------+------+------+-------------------+

| 1 | 192.168.0.60:3306 | up | rw | NULL | 0 |

| 2 | 192.168.0.61:3306 | up | ro | NULL | 6 |

+-------------+-------------------+-------+------+------+-------------------+

2 rows in set (0.00 sec)

mysql>

此后通过这个代理接口进行任何的读写操作,都会自动实现分离的,而且我们有了管理接口,随时查看这两台服务器的的每个节点的状态及访问类型

第三个 rhel6节点 192.168.0.62 上中间电脑 MySQL Proxy

[root@mysql-proxy mysql-proxy]# pwd

/usr/local/mysql-proxy/share/doc/mysql-proxy

[root@mysql-proxy mysql-proxy]#

[root@mysql-proxy mysql-proxy]# vim /etc/mysql-proxy.cnf #把刚刚mysql-proxy 的命令的参数去掉两个中短横放在里面放在 [mysql-proxy] 下面

[mysql-proxy]

log-level=debug

log-file=/var/log/mysql-proxy.log

plugins="proxy"

proxy-backend-addresses="192.168.0.60:3306"

proxy-read-only-backend-addresses="192.168.0.61:3306"

proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"

plugins="admin"

admin-username="admin"

admin-password="admin"

admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

[root@mysql-proxy mysql-proxy]# killall mysql-proxy #杀死进程

[root@mysql-proxy mysql-proxy]#

[root@mysql-proxy mysql-proxy]# mysql-proxy --help

Usage:

mysql-proxy [OPTION...] - MySQL Proxy

Help Options:

-?, --help Show help options

--help-all Show all help options

--help-proxy Show options for the proxy-module

Application Options:

-V, --version Show version

--defaults-file=<file> configuration file #这个文件被读取

--verbose-shutdown Always log the exit code when shutting down

--daemon Start in daemon-mode #这个手动写

--user=<user> Run mysql-proxy as user

--basedir=<absolute path> Base directory to prepend to relative paths in the config

--pid-file=<file> PID file in case we are started as daemon

--plugin-dir=<path> path to the plugins

--plugins=<name> plugins to load

--log-level=(error|warning|info|message|debug) log all messages of level ... or higher

--log-file=<file> log all messages in a file

--log-use-syslog log all messages to syslog

--log-backtrace-on-crash try to invoke debugger on crash

--keepalive try to restart the proxy if it crashed

--max-open-files maximum number of open files (ulimit -n)

--event-threads number of event-handling threads (default: 1)

--lua-path=<...> set the LUA_PATH

--lua-cpath=<...> set the LUA_CPATH

[root@mysql-proxy mysql-proxy]#

[root@mysql-proxy mysql-proxy]# mysql-proxy --daemon --defaults-file="/etc/mysql-proxy.cnf"

2021-03-24 15:20:09: (critical) mysql-proxy-cli.c:326: loading config from '/etc/mysql-proxy.cnf' failed: permissions of /etc/mysql-proxy.cnf aren't secure (0660 or stricter required) #提示要660权限

2021-03-24 15:20:09: (message) Initiating shutdown, requested from mysql-proxy-cli.c:328

2021-03-24 15:20:09: (message) shutting down normally, exit code is: 1

[root@mysql-proxy mysql-proxy]#

[root@mysql-proxy mysql-proxy]# chmod 660 /etc/mysql-proxy.cnf

[root@mysql-proxy mysql-proxy]# ls -la /etc/mysql-proxy.cnf

-rw-rw----. 1 root root 395 3月 24 15:12 /etc/mysql-proxy.cnf

[root@mysql-proxy mysql-proxy]#

[root@mysql-proxy mysql-proxy]# mysql-proxy --daemon --defaults-file="/etc/mysql-proxy.cnf" #好像正常了

[root@mysql-proxy mysql-proxy]#

[root@mysql-proxy mysql-proxy]# tail /var/log/mysql-proxy.log

2021-03-24 10:15:32: (debug) network-backend.c.184: backend 192.168.0.61:3306 was d own for more than 4 sec, waking it up

2021-03-24 10:15:32: (message) proxy-plugin.c.1664: connect(192.168.0.61:3306) fail ed: No route to host. Retrying with different backend.

2021-03-24 10:15:32: (debug) [network-mysqld.c:1134]: error on a connection (fd: -1 event: 0). closing client connection.

2021-03-24 10:16:26: (debug) network-backend.c.184: backend 192.168.0.61:3306 was d own for more than 4 sec, waking it up

2021-03-24 15:09:57: (message) Initiating shutdown, requested from signal handler

2021-03-24 15:09:57: (message) shutting down normally, exit code is: 0

2021-03-24 15:27:49: (critical) loading module '/usr/local/mysql-proxy-0.8.3-linux- glibc2.3-x86-32bit/lib/mysql-proxy/plugins/lib"admin".so' failed: /usr/local/mysql- proxy-0.8.3-linux-glibc2.3-x86-32bit/lib/mysql-proxy/plugins/lib"admin".so: cannot open shared object file: No such file or directory #这里好像有错

2021-03-24 15:27:49: (critical) setting --plugin-dir=<dir> might help

2021-03-24 15:27:49: (message) Initiating shutdown, requested from mysql-proxy-cli. c:467

2021-03-24 15:27:49: (message) shutting down normally, exit code is: 1

[root@mysql-proxy mysql-proxy]# vim /etc/mysql-proxy.cnf

[mysql-proxy]

log-level=debug

log-file=/var/log/mysql-proxy.log

plugins=proxy #双引号去掉看看

proxy-backend-addresses="192.168.0.60:3306"

proxy-read-only-backend-addresses="192.168.0.61:3306"

proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"

plugins=admin #双引号去掉看看

admin-username="admin"

admin-password="admin"

admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

[root@mysql-proxy mysql-proxy]# mysql-proxy --daemon --defaults-file="/etc/mysql-proxy.cnf" #再以守护进程的方式启动 mysql-proxy #不过还是报错,自己排错过程不详述了,下面把正确的/etc/mysql-proxy.cnf 代码贴出来 ,重启下mysql-proxy

[root@mysql-proxy mysql-proxy]# vim /etc/mysql-proxy.cnf [mysql-proxy]

log-level=debug

log-file=/var/log/mysql-proxy.log

plugins=proxy #不要双引号

proxy-backend-addresses=192.168.0.60:3306 #不要双引号

proxy-read-only-backend-addresses=192.168.0.61:3306 #不要双引号

proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"

plugins=admin #不要双引号

admin-username="admin"

admin-password="admin"

admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

[root@mysql-proxy mysql-proxy]# mysql-proxy --daemon --defaults-file="/etc/mysql-proxy.cnf" #启动 mysql-proxy

[root@mysql-proxy mysql-proxy]# tail /var/log/mysql-proxy.log

2021-03-24 15:53:55: (critical) plugin proxy 0.8.3 started

2021-03-24 15:53:55: (debug) max open file-descriptors = 1024

2021-03-24 15:53:55: (message) proxy listening on port :4040 #这里看到4040启动了

2021-03-24 15:53:55: (message) added read/write backend: 192.168.0.60:3306

2021-03-24 15:53:55: (message) added read-only backend: 192.168.0.61:3306

2021-03-24 15:54:57: (message) Initiating shutdown, requested from signal handler

2021-03-24 15:54:57: (message) shutting down normally, exit code is: 0

2021-03-24 15:57:04: (debug) chassis-path.c.122: adjusting relative path ("/usr/loc ysql-proxy/admin.lua") to base_dir (/usr/local/mysql-proxy-0.8.3-linux-glibc2.3-x86 cal/mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit/"/usr/local/mysql-proxy/share/doc/my

2021-03-24 15:57:04: (critical) plugin admin 0.8.3 started

2021-03-24 15:57:04: (debug) max open file-descriptors = 1024

[root@mysql-proxy mysql-proxy]# netstat -tnlp #但是这里4040没看到

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address Foreign Address State

tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN

tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN

tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN

tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN

tcp 0 0 0.0.0.0:47801 0.0.0.0:* LISTEN

tcp 0 0 :::111 :::* LISTEN

tcp 0 0 :::22 :::* LISTEN

tcp 0 0 ::1:631 :::* LISTEN

tcp 0 0 ::1:25 :::* LISTEN

tcp 0 0 :::52644 :::* LISTEN

[root@mysql-proxy mysql-proxy]#

[root@mysql-proxy mysql-proxy]# mysql-proxy --help-all

Usage:

mysql-proxy [OPTION...] - MySQL Proxy

Help Options:

-?, --help Show help options

--help-all Show all help options

--help-proxy Show options for the prox y-module

proxy-module

-P, --proxy-address=<host:port> listening address:port of the proxy-server (default: :4040) #指定代理的端口和主机

-r, --proxy-read-only-backend-addresses=<host:port> address:port of the remot e slave-server (default: not set)

-b, --proxy-backend-addresses=<host:port> address:port of the remot e backend-servers (default: 127.0.0.1:3306)

--proxy-skip-profiling disables profiling of que ries (default: enabled)

--proxy-fix-bug-25371 fix bug #25371 (mysqld > 5.1.12) for older libmysql versions

-s, --proxy-lua-script=<file> filename of the lua scrip t (default: not set)

--no-proxy don't start the proxy-mod ule (default: enabled)

--proxy-pool-no-change-user don't use CHANGE_USER to reset the connection coming from the pool (default: enabled)

--proxy-connect-timeout connect timeout in second s (default: 2.0 seconds)

--proxy-read-timeout read timeout in seconds ( default: 8 hours)

--proxy-write-timeout write timeout in seconds (default: 8 hours)

Application Options:

-V, --version Show version

--defaults-file=<file> configuration file

--verbose-shutdown Always log the exit code when shutting down

--daemon Start in daemon-mode

--user=<user> Run mysql-proxy as user

--basedir=<absolute path> Base directory to prepend to relative paths in the config

--pid-file=<file> PID file in case we are s tarted as daemon

--plugin-dir=<path> path to the plugins

--plugins=<name> plugins to load

--log-level=(error|warning|info|message|debug) log all messages of level ... or higher

--log-file=<file> log all messages in a fil e

--log-use-syslog log all messages to syslo g

--log-backtrace-on-crash try to invoke debugger on crash

--keepalive try to restart the proxy if it crashed

--max-open-files maximum number of open fi les (ulimit -n)

--event-threads number of event-handling threads (default: 1)

--lua-path=<...> set the LUA_PATH

--lua-cpath=<...> set the LUA_CPATH

[root@mysql-proxy mysql-proxy]#

[root@mysql-proxy mysql-proxy]# vim /etc/mysql-proxy.cnf

[mysql-proxy]

log-level=debug

log-file=/var/log/mysql-proxy.log

plugins=proxy

proxy-backend-addresses=192.168.0.60:3306

proxy-read-only-backend-addresses=192.168.0.61:3306

proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"

plugins=admin

admin-username="admin"

admin-password="admin"

admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

proxy-address="0.0.0.0:3306" #加上这一行看看就用3306端口吧,不用4040端口了

[root@mysql-proxy mysql-proxy]# killall mysql-proxy

[root@mysql-proxy mysql-proxy]#

[root@mysql-proxy mysql-proxy]# mysql-proxy --daemon --defaults-file="/etc/mysql-proxy.cnf"

[root@mysql-proxy mysql-proxy]#

[root@mysql-proxy mysql-proxy]# tail /var/log/mysql-proxy.log #感觉 3306端口还是未启动

2021-03-24 15:54:57: (message) Initiating shutdown, requested from signal handler

2021-03-24 15:54:57: (message) shutting down normally, exit code is: 0

2021-03-24 15:57:04: (debug) chassis-path.c.122: adjusting relative path ("/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua") to base_dir (/usr/local/mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit). New path: /usr/local/mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit/"/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

2021-03-24 15:57:04: (critical) plugin admin 0.8.3 started

2021-03-24 15:57:04: (debug) max open file-descriptors = 1024

2021-03-24 16:11:51: (message) Initiating shutdown, requested from signal handler

2021-03-24 16:11:52: (message) shutting down normally, exit code is: 0

2021-03-24 16:13:12: (debug) chassis-path.c.122: adjusting relative path ("/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua") to base_dir (/usr/local/mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit). New path: /usr/local/mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit/"/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

2021-03-24 16:13:12: (critical) plugin admin 0.8.3 started

2021-03-24 16:13:12: (debug) max open file-descriptors = 1024

[root@mysql-proxy mysql-proxy]#

[root@mysql-proxy mysql-proxy]# netstat -tnlp #嗯 3306端口未启动

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name

tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 4115/mysql-proxy

tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN 1758/rpcbind

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 2103/sshd

tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN 1837/cupsd

tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 2193/master

tcp 0 0 0.0.0.0:47801 0.0.0.0:* LISTEN 1780/rpc.statd

tcp 0 0 :::111 :::* LISTEN 1758/rpcbind

tcp 0 0 :::22 :::* LISTEN 2103/sshd

tcp 0 0 ::1:631 :::* LISTEN 1837/cupsd

tcp 0 0 ::1:25 :::* LISTEN 2193/master

tcp 0 0 :::52644 :::* LISTEN 1780/rpc.statd

[root@mysql-proxy mysql-proxy]#

马哥暂时也解决不了了

使用脚本的方式来启动 mysql-proxy,是马哥自己写的代码

[root@mysql-proxy mysql-proxy]# vim /etc/rc.d/init.d/mysql-proxy

#!/bin/bash

# mysql-proxy This script starts and stops the mysql-proxy daemon

# chkconfig: - 78 30

# processname: mysql-proxy

# description: mysql-proxy is a proxy daemon for mysql

# Source function library.

. /etc/rc.d/init.d/functions

prog="/usr/local/mysql-proxy/bin/mysql-proxy"

# Source networking configuration.

if [ -f /etc/sysconfig/network ]; then

. /etc/sysconfig/network

# Check that networking is up.

[ ${NETWORKING} = "no" ] && exit 0

# Set default mysql-proxy configuration.

ADMIN_USER="admin"

ADMIN_PASSWD="admin"

ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

PROXY_OPTIONS="--daemon"

PROXY_PID=/var/run/mysql-proxy.pid

PROXY_USER="mysql-proxy"

# Source mysql-proxy configuration.

if [ -f /etc/sysconfig/mysql-proxy ]; then

. /etc/sysconfig/mysql-proxy

RETVAL=0

start() {

echo -n $"Starting $prog: "

RETVAL=$?

echo

if [ $RETVAL -eq 0 ]; then

touch /var/lock/subsys/mysql-proxy

}

stop() {

echo -n $"Stopping $prog: "

killproc -p $PROXY_PID -d 3 $prog

RETVAL=$?

echo

if [ $RETVAL -eq 0 ]; then

rm -f /var/lock/subsys/mysql-proxy

rm -f $PROXY_PID

}

# See how we were called.

case "$1" in

start)

start

;;

stop)

stop

;;

restart)

stop

start

;;

condrestart|try-restart)

if status -p $PROXY_PIDFILE $prog >&/dev/null; then

stop

start

;;

status)

status -p $PROXY_PID $prog

;;

echo "Usage: $0 {start|stop|restart|reload|status|condrestart|try-restart}"

RETVAL=1

;;

esac

exit $RETVAL

[root@mysql-proxy mysql-proxy]# chmod +x /etc/rc.d/init.d/mysql-proxy #给执行权限

[root@mysql-proxy mysql-proxy]# chkconfig --add mysql-proxy #加到服务列表中去

[root@mysql-proxy mysql-proxy]#

mysql-proxy 是需要依赖额外的很多配置,这里不使用配置文件了,这个脚本 /etc/rc.d/init.d/mysql-proxy中没有使用 --defaults-file,但是它要依赖于另一个文件(脚本的配置文件 /etc/sysconfig/mysql-proxy),这个文件里面传递几个变量,这几个变量里面定义了我们mysql-proxy的工作状态的

[root@mysql-proxy mysql-proxy]# vim /etc/sysconfig/mysql-proxy #把里面的内容作为参数传给 /etc/rc.d/init.d/mysql-proxy 了

# Options for mysql-proxy

ADMIN_USER="admin" #admin user 的名称

ADMIN_PASSWORD="admin" #admin user 的密码

ADMIN_ADDRESS="0.0.0.0:4041" #管理地址,如果不定义,就是0.0.0.0:4041端口上

ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua" #管理所使用的脚本路径

PROXY_ADDRESS="0.0.0.0:4040" #没定义就是在0.0.0.0:4040端口上,可以改成3306?????好像相反,头都乱了

PROXY_USER="mysql-proxy" #代理用户

PROXY_OPTIONS="--daemon --log-level=info --log-file="/var/log/mysql-proxy.log" --plugins=proxy --proxy-backend-addresses=192.168.0.60:3306 --proxy-read-only-backend-addresses=192.168.0.61:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua --plugins=admin " #许多额外属性,

[root@mysql-proxy mysql-proxy]# killall mysql-proxy #杀死以前的进程

[root@mysql-proxy mysql-proxy]#

[root@mysql-proxy mysql-proxy]# service mysql-proxy start #启动了

正在启动 /usr/local/mysql-proxy/bin/mysql-proxy： [确定]

[root@mysql-proxy mysql-proxy]#

/usr/local/mysql-proxy/bin/mysql-proxy --daemon --log-level=info --log-file=/var/log/mysql-proxy.log --plugins=proxy --proxy-backend-addresses=192.168.0.60:3306 --proxy-read-only-backend-addresses=192.168.0.61:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua --pid-file=/var/run/mysql-proxy.pid --user=mysql-proxy --plugins=admin --admin-username=admin --admin-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua --admin-password=admin #mysql-proxy是启用默认4040端口的

/usr/local/mysql-proxy/bin/mysql-proxy --daemon --log-level=info --log-file=/var/log/mysql-proxy.log --plugins=proxy --proxy-backend-addresses=192.168.0.60:3306 --proxy-read-only-backend-addresses=192.168.0.61:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua --pid-file=/var/run/mysql-proxy.pid --proxy-address="" --user=mysql-proxy --plugins=admin --admin-username=admin --admin-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua --admin-password=admin #这命令比上一个命令多了 --proxy-address="" ,那么mysql-proxy是启用3306端口的,不知为什么端口会有变化

[root@mysql-proxy init.d]# netstat -tnlp

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name

tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 5003/mysql-proxy #启动了

tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN 1758/rpcbind

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 2103/sshd

tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN 1837/cupsd

tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 2193/master

tcp 0 0 0.0.0.0:47801 0.0.0.0:* LISTEN 1780/rpc.statd

tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 5003/mysql-proxy #启动了

tcp 0 0 :::111 :::* LISTEN 1758/rpcbind

tcp 0 0 :::22 :::* LISTEN 2103/sshd

tcp 0 0 ::1:631 :::* LISTEN 1837/cupsd

tcp 0 0 ::1:25 :::* LISTEN 2193/master

tcp 0 0 :::52644 :::* LISTEN 1780/rpc.statd

[root@mysql-proxy init.d]#

在第二个ha2 rhel6节点 192.168.0.61 上从服务器

连上代理服务器试试

[root@slave ~]# mysql -uroot -paaaaaa -h192.168.0.62 --port=4040 -e "show tables from mageducom;" #没有内容

Warning: Using a password on the command line interface can be insecure.

[root@slave ~]#

[root@slave ~]# mysql -uroot -paaaaaa -h192.168.0.62 --port=4040 -e "select user,password from mysql.user;" #正常显示了

Warning: Using a password on the command line interface can be insecure.

+----------+-------------------------------------------+

| user | password |

+----------+-------------------------------------------+

| root | |

| | |

| repluser | *D98280F03D0F78162EBDBB9C883FC01395DEA2BF |

| root | *ED0DE3278EF02C0701FD328CA1DA43F22173FF3F |

| root | *EE840CF03C4E9C47647DD31FFD5CDB7BDF349532 |

+----------+-------------------------------------------+

[root@slave ~]#

在第二个ha2 rhel6节点 192.168.0.61 上从服务器

连上代理管理窗口试试

[root@slave ~]# mysql -uadmin -padmin -h 192.168.0.62 --port=4041

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 1

Server version: 5.0.99-agent-admin

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 stateme nt.

mysql> select * from backends;

#主的up(连上去)了,,,从的正在检测,过一会儿,检测好了,应该也会up(连上去)了,,,,我总感觉上次执行的是读命令,应该是从服务器up啊

+-------------+-------------------+---------+------+------+---------------- ---+

+-------------+-------------------+---------+------+------+---------------- ---+

| 1 | 192.168.0.60:3306 | up | rw | NULL | 0 |

| 2 | 192.168.0.61:3306 | unknown | ro | NULL | 0 |

+-------------+-------------------+---------+------+------+---------------- ---+

2 rows in set (0.00 sec)

mysql>

第三个 rhel6节点 192.168.0.62 上中间电脑 MySQL Proxy

[root@mysql-proxy init.d]# vim /etc/sysconfig/mysql-proxy

# Options for mysql-proxy

ADMIN_USER="admin"

ADMIN_PASSWORD="admin"

ADMIN_ADDRESS=""

ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

PROXY_ADDRESS="0.0.0.0:3306" #改这里代理变成3306端口,客户端连的时候,就不用指定端口4040了,因为# mysql命令默认就是3306端口

PROXY_USER="mysql-proxy"

PROXY_OPTIONS="--daemon --log-level=info --log-file="/var/log/mysql-proxy.log" --plugins=proxy --plugins=admin --proxy-backend-addresses=192.168.0.60:3306 --proxy-read-only-backend-addresses=192.168.0.61:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"

[root@mysql-proxy init.d]# service mysql-proxy restart #重启服务

停止 /usr/local/mysql-proxy/bin/mysql-proxy： [确定]

正在启动 /usr/local/mysql-proxy/bin/mysql-proxy： [确定]

[root@mysql-proxy init.d]#

[root@mysql-proxy init.d]# netstat -tnlp

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name

tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 5099/mysql-proxy

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 5099/mysql-proxy #变成3306端口了

tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN 1758/rpcbind

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 2103/sshd

tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN 1837/cupsd

tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 2193/master

tcp 0 0 0.0.0.0:47801 0.0.0.0:* LISTEN 1780/rpc.statd

tcp 0 0 :::111 :::* LISTEN 1758/rpcbind

tcp 0 0 :::22 :::* LISTEN 2103/sshd

tcp 0 0 ::1:631 :::* LISTEN 1837/cupsd

tcp 0 0 ::1:25 :::* LISTEN 2193/master

tcp 0 0 :::52644 :::* LISTEN 1780/rpc.statd

[root@mysql-proxy init.d]#

马哥还是4040端口??

马哥这里改了之后,重启 mysql-proxy ,然后netstat 一下,就可以看到 3306端口了

此时客户端连上代理,不需要加4040了,因为默认3306是可以忽略掉的

在第二个ha2 rhel6节点 192.168.0.61 上从服务器

[root@slave ~]# mysql -uroot -paaaaaa -h192.168.0.62 -e "show tables from mageducom;" #没用3306,就可以连上代理

Warning: Using a password on the command line interface can be insecure.

[root@slave ~]#

[root@slave ~]# mysql -uroot -paaaaaa -h192.168.0.62 -e "select user,password from mysql.user;" #没用3306,就可以连上代理

Warning: Using a password on the command line interface can be insecure.

+----------+-------------------------------------------+

| user | password |

+----------+-------------------------------------------+

| root | |

| | |

| repluser | *D98280F03D0F78162EBDBB9C883FC01395DEA2BF |

| root | *ED0DE3278EF02C0701FD328CA1DA43F22173FF3F |

| root | *EE840CF03C4E9C47647DD31FFD5CDB7BDF349532 |

+----------+-------------------------------------------+

[root@slave ~]#

代理服务器按道理来讲,客户端是不知道它是代理服务器的,所以代理的时候,代理服务器的端口应该与原始服务器当中是一样的,

自己亲自做的时候,可以使用一主两从来尝试,看看mysql读写分离有没有效果

普通分类:

linux

You are here

友情链接

搜索表单

用户登录

You are here

马哥 42_04 _MySQL主从复制——mysql-proxy.0.8.3实现MySQL-5.6读写分离 有大用

友情链接

马哥 42_04 _MySQL主从复制——mysql-proxy.0.8.3实现MySQL-5.6读写分离有大用