42_04利用mysql-proxy实现读写分离_第一版_

MySQL Proxy

  1. 源码安装时，MySQL　proxy的依赖关系:

libevent 1.x or higher
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

  1. 添加用户 

    1
    # useradd - r mysql-proxy

  2. 安装 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    #tar xf mysql-proxy-0.8.2.tar.gz -C /usr/local 
    #ln -sv mysql-proxy-0.8.2 mysql-proxy 
    #cd mysql-proxy 
    #./configure 
    #make 
     
    #make check 
    如果管理员有密码，上面的步骤则需要使用如下格式进行: 
    #MYSQL_PASSWORD=root_pwd make check 
     
    #make install 
     
    默认情况下，mysql-proxy安装在/usr/local/sbin/mysql-proxy,而lua示例脚本安装在/usr/local/share目录中。

  3. 添加环境变量脚本

1
2
#vim /etc/profile.d/mysql-proxy.sh 
export PATH=$PATH:/usr/local/mysql-proxy/bin
  1. 配置指令

    mysql proxy的各配置参数请看官方文档:http://dev.mysql.com/doc/refman/

四、安装配置 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
#mysql-proxy --daemon --log-level=debug  --plugins="proxy" --log-file=/var/log/mysql-proxy.log   --proxy-backend-addresses="172.16.100.106:3306" --proxy-read-only-backend-addresses="172.16.100.107:3306"; 
 
#tail /var/log/mysql-proxy.log 
 
100.106: 
    >GRANT ALL ON *.* TO 'root'@'172.16.%.%' IDENTIFIED BY 'redhat'; 
    >flush privileges; 
 
100.107: 
    mysql -u root -p -h 172.16.100.201 --port=4040 
    redhat 
 
 
 
ls /usr/local/share/doc/mysql-proxy/rw-splitting.lua  这个脚本实现读写分离 
 
#mysql-proxy --daemon --log-level=debug  --plugins="proxy" --log-file=/var/log/mysql-proxy.log   --proxy-backend-addresses="172.16.100.106:3306" --proxy-read-only-backend-addresses="172.16.100.107:3306"　--proxy-lua-script=" /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua " 
 
tail /var/log/mysql-proxy.log 
 
 
实现admin功能: 
 
mysql-proxy --daemon --log-level=debug  --log-file=/var/log/mysql-proxy.log   --plugins="proxy" --proxy-backend-addresses="172.16.100.106:3306" --proxy-read-only-backend-addresses="172.16.100.107: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" 
说明，这个admin.lua没有提供

107:

mysql -uadmin -h 172.16.100.201 –port=4041 -p
admin

select * from backends;   用来查看有几个后端
会显示state是unknown状态；
mysql -u root -predhat -h 172.16.100.201 –port=4040 -e ‘create database hellodb’;
会发现状态变成了up,说明操作是在代理上操作的，写到了后端服务器上； 

1
2
3
4
5
6
7
8
9
10

# vim /etc/sysconfig/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" 
PROXY_USER="mysql-proxy" 
PROXY_OPTIONS "--daemon --log-level=info  --plugins=proxy --log-file=/var/log/mysql-proxy.log   --proxy-backend-addresses=172.16.100.106:3306 --proxy-read-only-backend-addresses=172.16.100.107:3306　--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua "

mysql-proxy的启动脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135

#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 configuraion. 
if [ -f /etc/sysconfig/mysql-proxy ];then 
    . /etc/sysconfig/mysql-proxy 
fi 
 
RETVAL=0 
start() { 
    echo -n $"Stating $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 
    fi 
} 
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 
    fi 
} 
#See how we were called 
case "$1" in 
    start) 
        start 
        ;; 
    stop) 
        stop 
        ;; 
    restart) 
        stop 
        start 
        ;; 
    condrestart|try-restart) 
        if status -p $PROXY_PIDFILE $porg &>/dev/null;then 
            stop 
            start 
        fi 
        ;; 
    status) 
        status -p $PROXY_PID $prog 
        ;; 
    *) 
        echo "Usage: $0 {start|stop|restart|reload|status|condrestart|try-restart}" 
        RETVAL=1 
        ;; 
esac 
exit $RETVAL 
 

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


## 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] = { 
 i, 
 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

这个写的不错：
http://www.zhengdazhi.com/archives/923

关于这一个，可以直接参考The Next;;;

赏

谢谢你请我吃糖果

支付宝

扫一扫，分享到微信

微信分享二维码

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true

探索世界美好的存在。