分类目录归档:mysql

Mysql-proxy代理程序

1、安装Lua、glib、pkg-config、libevent、GCC、make等工具不同的环境下不同。
2、安装mysql-proxy
tar xzf mysql-proxy-0.8.1.tar.gz
cd mysql-proxy-0.8.1
./configure  --prefix=/usr/local/mysql-proxy[root@node1 bin]# /usr/local/mysql-proxy/bin/mysql-proxy     --help-all
Usage:
mysql-proxy [OPTION...] - MySQL Proxy

Help Options:
-h, --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)
--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

Mysql数据库代理amoeba

基本环境:

A(172.25.16.10):客户端    J(172.25.16.19):代理数据库          B(172.25.16.11):主数据库       C(172.25.16.12):从数据库     D(172.25.16.13):从数据库

基于amoeba的读写分离

1.在serverj上装包

jdk-7u79-linux.rpm

  因为amoeba是用java写的所以需要jvm或jdk,装完声明一下全局变量“export JAVA_HOME="/usr/java/jdkxxxxx"”

  2.新建目录/usr/local/amoeba

mkdir /usr/local/amoeba

  3.把压缩包解压到该目录/usrl/local/amoeba

4.在conf目录下有amoeba的配置文件

需要修改的两个:amoeba.xml 和 dbServers.xml

     amoeba.xml(3个地方要改):

(1).监听的端口号:

<proxy>

<!-- service class must implements com.meidusa.amoeba.service.Service -->

<service name="Amoeba for Mysql" class="com.meidusa.amoeba.net.ServerableConnectionManager">

<!-- port -->

<property name="port">3306</property>

(2).客户端访问时用的用户名

<property name="authenticator">

<bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">

<property name="user">amoeba</property>

                                 <property name="password">amoeba</property>

<property name="filter">

<bean class="com.meidusa.amoeba.server.IPAccessController">

<property name="ipFile">${amoeba.home}/conf/access_list.conf</property>

</bean>

</property>

</bean>

</property>

 

 

(3).

<property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>

<property name="LRUMapSize">1500</property>

     <property name="defaultPool">serverb#默认访问的数据库#</property>

            <property name="writePool">serverb#指定可写池#</property>

            <property name="readPool">readgroup1#指定只读池#</property>

<property name="needParse">true</property>

 

 

 

dbServers.xml文件:

(1).修改端口号,用户名,密码。

<dbServer name="abstractServer" abstractive="true">

<factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">

<property name="manager">${defaultManager}</property>

<property name="sendBufferSize">64</property>

<property name="receiveBufferSize">128</property>

<!-- mysql port -->

<property name="port">3306</property>

<!-- mysql schema -->

<property name="schema">test</property>

<!-- mysql user -->

<property name="user">dbproxy</property>

<!--  mysql password  -->

<property name="password">uplooking</property>

</factoryConfig>

 

(2).指定可以代理的数据库

 <dbServer name="serverb"  parent="abstractServer">

<factoryConfig>

<!-- mysql ip -->

<property name="ipAddress">172.25.16.11</property>

</factoryConfig>

</dbServer>

<dbServer name="serverc"  parent="abstractServer">

<factoryConfig>

<!-- mysql ip -->

  <property name="ipAddress">172.25.16.12</property>

</factoryConfig>

</dbServer>

<dbServer name="serverd"  parent="abstractServer">

<factoryConfig>

<!-- mysql ip -->

<property name="ipAddress">172.25.16.13</property>

</factoryConfig>

</dbServer>

 

(3).指定只读组

<dbServer name="readgroup1" virtual="true">

<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">

<!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->

<property name="loadbalance">1</property>

<!-- Separated by commas,such as: server1,server2,server1 -->

<property name="poolNames">serverc,serverd</property>

</poolConfig>

</dbServer>

 

5.启动脚本在/bin下

启动时会报错,需要修改文件即可:

DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss228k"(最后一个参数改为228即可)。

./amoeba start &(放到后台执行)

     6.在BCD上授权允许J访问数据库

grant all on db1.* to dbproxy@'172.25.16.19' identified by 'uplooking';

 

 

 

测试环境:

在BCD上分别新建一个库,一个表,表里一个数据,写动作会在B上执行,读操作会依次轮询CD。