MySQL主从复制--mysql-proxy实现MySQL-5.6读写分离

2eew

大纲
一、系统环境
二、准备工作
三、mysql-proxy安装与配置








一、系统环境

系统环境

CentOS5.8 x86_64

master.network.com    master    172.16.1.101

slave.network.com     slave     172.16.1.105

CentOS6.5 x86_64

myproxy.network.com   proxy    172.16.1.102

软件包

• mysql-5.6.26-linux-glibc2.5-x86_64.tar.gz(二进制通用安装包)
  
• mysql-proxy-0.8.5-1.el6.x86_64.rpm
  
  

拓扑图

二、MySQL初始化安装过程

1、时间同步

1 2 3 4 5 6 7 8 9 10

[iyunv@master ~]# ntpdate s2c.time.edu.cn [iyunv@slave ~]# ntpdate s2c.time.edu.cn [iyunv@proxy ~]# ntpdate s2c.time.edu.cn 可根据需要在每个节点上定义crontab任务 [iyunv@master ~]# which ntpdate /sbin/ntpdate [iyunv@master ~]# echo "*/5 * * * * /sbin/ntpdate s2c.time.edu.cn &> /dev/null" >> /var/spool/cron/root [iyunv@master ~]# crontab -l */5 * * * * /sbin/ntpdate s2c.time.edu.cn &> /dev/null

2、主机名称要与uname -n保持一致，并通过/etc/hosts解析

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

master [iyunv@master ~]# hostname master.network.com [iyunv@master ~]# uname -n master.network.com [iyunv@master ~]# sed -i 's@\(HOSTNAME=\).*@\1master.network.com@g'  /etc/sysconfig/network slave [iyunv@slave ~]# hostname slave.network.com [iyunv@slave ~]# uname -n slave.network.com [iyunv@slave ~]# sed -i 's@\(HOSTNAME=\).*@\1slave.network.com@g'  /etc/sysconfig/network proxy [iyunv@myproxy ~]# hostname myproxy.network.com [iyunv@myproxy ~]# uname -n myproxy.network.com [iyunv@myproxy ~]# sed -i 's@\(HOSTNAME=\).*@\1myproxy.network.com@g'  /etc/sysconfig/network master添加hosts解析 [iyunv@master ~]# vim /etc/hosts [iyunv@master ~]# cat /etc/hosts # Do not remove the following line, or various programs # that require network functionality will fail. 127.0.0.1       CentOS5.8 CentOS5 localhost.localdomain localhost ::1     localhost6.localdomain6 localhost6 172.16.1.101    master.network.com     master 172.16.1.105    slave.network.com  slave 172.16.1.102    myproxy.network.com     myproxy 拷贝此hosts文件至slave [iyunv@master ~]# scp /etc/hosts slave:/etc/ root@slave's password: hosts                  100%  233     0.2KB/s   00:00 拷贝此hosts文件至myproxy [iyunv@master ~]# scp /etc/hosts myproxy:/etc/ root@slave's password: hosts                  100%  233     0.2KB/s   00:00

3、关闭iptables和selinux

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

master [iyunv@master ~]# service iptables stop [iyunv@master ~]# vim /etc/sysconfig/selinux [iyunv@master ~]# cat /etc/sysconfig/selinux # This file controls the state of SELinux on the system. # SELINUX= can take one of these three values: #   enforcing - SELinux security policy is enforced. #   permissive - SELinux prints warnings instead of enforcing. #   disabled - SELinux is fully disabled. #SELINUX=permissive SELINUX=disabled # SELINUXTYPE= type of policy in use. Possible values are: #   targeted - Only targeted network daemons are protected. #   strict - Full SELinux protection. SELINUXTYPE=targeted slave [iyunv@slave ~]# service iptables stop [iyunv@slave ~]# vim /etc/sysconfig/selinux [iyunv@slave ~]# cat /etc/sysconfig/selinux # This file controls the state of SELinux on the system. # SELINUX= can take one of these three values: #   enforcing - SELinux security policy is enforced. #   permissive - SELinux prints warnings instead of enforcing. #   disabled - SELinux is fully disabled. #SELINUX=permissive SELINUX=disabled # SELINUXTYPE= type of policy in use. Possible values are: #   targeted - Only targeted network daemons are protected. #   strict - Full SELinux protection. SELINUXTYPE=targeted proxy [iyunv@myproxy ~]# service iptables stop [iyunv@myproxy ~]# vim /etc/sysconfig/selinux [iyunv@myproxy ~]# cat /etc/sysconfig/selinux # This file controls the state of SELinux on the system. # SELINUX= can take one of these three values: #   enforcing - SELinux security policy is enforced. #   permissive - SELinux prints warnings instead of enforcing. #   disabled - SELinux is fully disabled. #SELINUX=permissive SELINUX=disabled # SELINUXTYPE= type of policy in use. Possible values are: #   targeted - Only targeted network daemons are protected. #   strict - Full SELinux protection. SELINUXTYPE=targeted

三、mysql-proxy安装与配置
1、安装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

我这里使用的是epel源，请自行配置epel源或下载rpm包，源码包编译安装也行 [iyunv@myproxy ~]# yum install -y mysql-proxy 查看安装生成的文件 [iyunv@myproxy ~]# rpm -ql mysql-proxy /etc/mysql-proxy.cnf /etc/rc.d/init.d/mysql-proxy /etc/sysconfig/mysql-proxy /usr/bin/mysql-binlog-dump /usr/bin/mysql-myisam-dump /usr/bin/mysql-proxy /usr/lib64/libmysql-chassis-glibext.so.0 /usr/lib64/libmysql-chassis-glibext.so.0.0.0 /usr/lib64/libmysql-chassis-timing.so.0 /usr/lib64/libmysql-chassis-timing.so.0.0.0 /usr/lib64/libmysql-chassis.so.0 /usr/lib64/libmysql-chassis.so.0.0.0 /usr/lib64/libmysql-proxy.so.0 /usr/lib64/libmysql-proxy.so.0.0.0 /usr/lib64/mysql-proxy /usr/lib64/mysql-proxy/lua /usr/lib64/mysql-proxy/lua/admin.lua /usr/lib64/mysql-proxy/lua/chassis.so /usr/lib64/mysql-proxy/lua/glib2.so /usr/lib64/mysql-proxy/lua/lfs.so /usr/lib64/mysql-proxy/lua/lpeg.so /usr/lib64/mysql-proxy/lua/mysql.so /usr/lib64/mysql-proxy/lua/posix.so /usr/lib64/mysql-proxy/lua/proxy /usr/lib64/mysql-proxy/lua/proxy/auto-config.lua /usr/lib64/mysql-proxy/lua/proxy/balance.lua /usr/lib64/mysql-proxy/lua/proxy/commands.lua /usr/lib64/mysql-proxy/lua/proxy/parser.lua /usr/lib64/mysql-proxy/lua/proxy/test.lua /usr/lib64/mysql-proxy/lua/proxy/tokenizer.lua /usr/lib64/mysql-proxy/plugins /usr/lib64/mysql-proxy/plugins/libadmin.so /usr/lib64/mysql-proxy/plugins/libdebug.so /usr/lib64/mysql-proxy/plugins/libproxy.so /usr/lib64/mysql-proxy/plugins/libreplicant.so /usr/share/doc/mysql-proxy-0.8.5 /usr/share/doc/mysql-proxy-0.8.5/AUTHORS /usr/share/doc/mysql-proxy-0.8.5/COPYING /usr/share/doc/mysql-proxy-0.8.5/NEWS /usr/share/doc/mysql-proxy-0.8.5/README /usr/share/doc/mysql-proxy-0.8.5/examples /usr/share/doc/mysql-proxy-0.8.5/examples/tutorial-basic.lua /usr/share/doc/mysql-proxy-0.8.5/examples/tutorial-constants.lua /usr/share/doc/mysql-proxy-0.8.5/examples/tutorial-inject.lua /usr/share/doc/mysql-proxy-0.8.5/examples/tutorial-keepalive.lua /usr/share/doc/mysql-proxy-0.8.5/examples/tutorial-monitor.lua /usr/share/doc/mysql-proxy-0.8.5/examples/tutorial-packets.lua /usr/share/doc/mysql-proxy-0.8.5/examples/tutorial-prep-stmts.lua /usr/share/doc/mysql-proxy-0.8.5/examples/tutorial-query-time.lua /usr/share/doc/mysql-proxy-0.8.5/examples/tutorial-resultset.lua /usr/share/doc/mysql-proxy-0.8.5/examples/tutorial-rewrite.lua /usr/share/doc/mysql-proxy-0.8.5/examples/tutorial-routing.lua /usr/share/doc/mysql-proxy-0.8.5/examples/tutorial-scramble.lua /usr/share/doc/mysql-proxy-0.8.5/examples/tutorial-states.lua /usr/share/doc/mysql-proxy-0.8.5/examples/tutorial-tokenize.lua /usr/share/doc/mysql-proxy-0.8.5/examples/tutorial-union.lua /usr/share/doc/mysql-proxy-0.8.5/examples/tutorial-warnings.lua

2、启动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

[iyunv@myproxy ~]# mysql-proxy --daemon --log-level=debug --log-file="/var/log/mysql-proxy.log" \ > --plugins="proxy" --proxy-backend-addresses="172.16.1.101:3306" \ > --proxy-read-only-backend-addresses="172.16.1.105:3306" --proxy-backend-addresses后面跟的是主服务器，能读能写的 --proxy-read-only-backend-addresses后面跟的是从服务器，只能读的 查看日志信息 [iyunv@myproxy ~]# tail /var/log/mysql-proxy.log 2016-01-03 16:13:47: (critical) plugin proxy 0.8.5 started 2016-01-03 16:13:47: (debug) max open file-descriptors = 1024 2016-01-03 16:13:47: (message) proxy listening on port :4040 2016-01-03 16:13:47: (message) added read/write backend: 172.16.1.101:3306 2016-01-03 16:13:47: (message) added read-only backend: 172.16.1.105:3306 查看监听端口为4040 [iyunv@myproxy ~]# 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:22          0.0.0.0:*           LISTEN      1106/sshd            tcp        0      0 0.0.0.0:49401       0.0.0.0:*           LISTEN      1056/rpc.statd       tcp        0      0 0.0.0.0:4040        0.0.0.0:*           LISTEN      3483/mysql-proxy    tcp        0      0 :::22               :::*                LISTEN      1106/sshd            tcp        0      0 :::52904            :::*                LISTEN      1056/rpc.stat

3、远程连接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

在从服务器上尝试连接一下proxy服务器 [iyunv@slave ~]# mysql -uroot -h172.16.1.102 --port=4040 -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'172.16.1.102' (using password: YES) 在主服务器上允许远程访问 [iyunv@master ~]# mysql Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.6.26-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.16.%.%' IDENTIFIED BY 'redhat'; Query OK, 0 rows affected (0.77 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.22 sec) mysql> \q Bye 然后再尝试连接 [iyunv@slave ~]# mysql -uroot -h172.16.1.102 --port=4040 -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 13 Server version: 5.6.26-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE DATABASE proxydb; Query OK, 1 row affected (0.10 sec) mysql> SHOW DATABASES; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | proxydb            | | test               | +--------------------+ 5 rows in set (0.06 sec) 再在从服务器上查看是否有此库，有的话可以说明我们的写操作是被定向至主服务器上的 [iyunv@slave ~]# mysql -S /tmp/mysql.sock Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 14 Server version: 5.6.26-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SHOW DATABASES; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | proxydb            |         | test               | +--------------------+ 5 rows in set (0.02 sec) 我们的写请求凑巧被定向至主服务器了，因为mysql-proxy自身是没有读写分离功能，得借助于lua脚本才能实现

4、使用管理lua脚本

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

系统默认也没有提供，所以我们需要自行创建 [iyunv@myproxy ~]# vim /usr/share/doc/mysql-proxy-0.8.5/examples/admin.lua [iyunv@myproxy ~]# cat /usr/share/doc/mysql-proxy-0.8.5/examples/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             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

5、使用读写分离脚本

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

因为rpm包里没有提供读写分离lua脚本，所以我们自行从同版本二进制包中拷贝了一个 [iyunv@myproxy ~]# wget http://ftp.ntu.edu.tw/pub/MySQL/ ... l6-x86-64bit.tar.gz [iyunv@myproxy ~]# tar xf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz [iyunv@myproxy ~]# cp mysql-proxy-0.8.5-linux-el6-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua /usr/share/doc/mysql-proxy-0.8.5/examples/ 先强行停止mysql-proxy [iyunv@myproxy ~]# pkill mysql-proxy [iyunv@myproxy ~]# ps aux | grep "mysql-proxy" root      3657  0.0  0.3 103244   836 pts/3    S+   18:36   0:00 grep mysql-proxy 启动mysql-proxy [iyunv@myproxy ~]# mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="172.16.1.101:3306" --proxy-read-only-backend-addresses="172.16.1.105:3306" --proxy-lua-script="/usr/share/doc/mysql-proxy-0.8.5/examples/rw-splitting.lua" --plugins=admin --admin-username="admin" --admin-password="admin" --admin-lua-script="/usr/share/doc/mysql-proxy-0.8.5/examples/admin.lua" 查看监听端口 [iyunv@myproxy ~]# 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:22            0.0.0.0:*           LISTEN      1106/sshd            tcp        0      0 0.0.0.0:49401         0.0.0.0:*           LISTEN      1056/rpc.statd       tcp        0      0 0.0.0.0:4040          0.0.0.0:*           LISTEN      3675/mysql-proxy    tcp        0      0 0.0.0.0:4041          0.0.0.0:*           LISTEN      3675/mysql-proxy    tcp        0      0 :::22                 :::*                LISTEN      1106/sshd            tcp        0      0 :::52904              :::*                LISTEN      1056/rpc.statd 查看日志 [iyunv@myproxy ~]# tail /var/log/mysql-proxy.log 2016-01-03 18:37:34: (critical) plugin proxy 0.8.5 started 2016-01-03 18:37:34: (debug) max open file-descriptors = 1024 2016-01-03 18:37:34: (message) proxy listening on port :4040 2016-01-03 18:37:34: (message) added read/write backend: 172.16.1.101:3306 2016-01-03 18:37:34: (message) added read-only backend: 172.16.1.105:3306

6、测试读写分离效果

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

首先连接管理端口查看下状态 [iyunv@slave mysql]# mysql -uadmin -h 172.16.1.102 -padmin --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; +-------------+-------------------+---------+------+------+-------------------+ | backend_ndx | address           | state   | type | uuid | connected_clients | +-------------+-------------------+---------+------+------+-------------------+ |           1 | 172.16.1.101:3306 | unknown | rw   | NULL |                 0 | |           2 | 172.16.1.105:3306 | unknown | ro   | NULL |                 0 | +-------------+-------------------+---------+------+------+-------------------+ 2 rows in set (0.01 sec) 可以看到主节点和从节点都为unknown，因为我们没有发起任何读或者写操作 在另一个节点上向mysql-proxy发起一个写操作 [iyunv@node3 ~]# mysql -uroot -h 172.16.1.102 -p --port=4040 -e 'CREATE DATABASE hellodb;' Enter password: 再来通过管理接口查看状态信息，可以看到主节点状态已经为up mysql> SELECT * FROM backends; +-------------+-------------------+---------+------+------+-------------------+ | backend_ndx | address           | state   | type | uuid | connected_clients | +-------------+-------------------+---------+------+------+-------------------+ |           1 | 172.16.1.101:3306 | up      | rw   | NULL |                 0 | |           2 | 172.16.1.105:3306 | unknown | ro   | NULL |                 0 | +-------------+-------------------+---------+------+------+-------------------+ 2 rows in set (0.00 sec) 接下来我们再发起一个读请求 [iyunv@node3 ~]# mysql -uroot -h 172.16.1.102 -p --port=4040 -e 'SELECT user,host,password FROM mysql.user;' Enter password: +---------+-------------------+-------------------------------------------+ | user    | host              | password                                  | +---------+-------------------+-------------------------------------------+ | root    | localhost         |                                           | | root    | slave.network.com |                                           | | root    | 127.0.0.1         |                                           | | root    | ::1               |                                           | |         | localhost         |                                           | |         | slave.network.com |                                           | | repuser | 172.16.%.%        | *304A91F0E46BBB1E641D3D95E225E9AAA27077CE | | root    | 172.16.%.%        | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | +---------+-------------------+-------------------------------------------+ 再来通过管理接口查看状态信息，可以看到从节点状态已经为up mysql> SELECT * FROM backends; +-------------+-------------------+-------+------+------+-------------------+ | backend_ndx | address           | state | type | uuid | connected_clients | +-------------+-------------------+-------+------+------+-------------------+ |           1 | 172.16.1.101:3306 | up    | rw   | NULL |                 0 | |           2 | 172.16.1.105:3306 | up    | ro   | NULL |                 0 | +-------------+-------------------+-------+------+------+-------------------+ 2 rows in set (0.00 sec)

7、将配置信息保存至配置文件中

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

[iyunv@myproxy ~]# vim /etc/mysql-proxy.cnf [mysql-proxy] daemon = true pid-file = /var/run/mysql-proxy.pid log-file = /var/log/mysql-proxy.log log-level = debug max-open-files = 1024 plugins = admin,proxy #user = mysql-proxy # #Proxy Configuration proxy-address = 0.0.0.0:3306 proxy-backend-addresses = 172.16.1.101:3306 proxy-read-only-backend-addresses = 172.16.1.105:3306 proxy-lua-script = /usr/share/doc/mysql-proxy-0.8.5/examples/rw-splitting.lua #proxy-skip-profiling = true # # Admin Configuration #admin-address = 0.0.0.0:4041 admin-lua-script = /usr/share/doc/mysql-proxy-0.8.5/examples/admin.lua admin-username = admin admin-password = admin 然后重启mysql-proxy服务 [iyunv@myproxy ~]# service mysql-proxy restart Stopping mysql-proxy:                                      [  OK  ] Starting mysql-proxy:                                      [  OK  ] 查看监听端口，可以看到已经监听在3306端口了 [iyunv@myproxy ~]# 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:22           0.0.0.0:*            LISTEN      1106/sshd            tcp        0      0 0.0.0.0:49401        0.0.0.0:*            LISTEN      1056/rpc.statd       tcp        0      0 0.0.0.0:4041         0.0.0.0:*            LISTEN      4040/mysql-proxy    tcp        0      0 0.0.0.0:3306         0.0.0.0:*            LISTEN      4040/mysql-proxy    tcp        0      0 :::22                :::*                 LISTEN      1106/sshd            tcp        0      0 :::52904             :::*                 LISTEN      1056/rpc.statd 客户端尝试连接访问 [iyunv@node3 ~]# mysql -uroot -h 172.16.1.102 -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 26 Server version: 5.6.26-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE DATABASE mydb; Query OK, 1 row affected (0.05 sec) mysql> SHOW DATABASES; +--------------------+ | Database           | +--------------------+ | information_schema | | hellodb            | | mydb               | | mysql              | | performance_schema | | proxydb            | | test               | +--------------------+ 7 rows in set (0.03 sec) mysql> SHOW TABLES FROM mydb; Empty set (0.01 sec) 再从管理接口查看后端服务器状态 mysql> SELECT * FROM backends; +-------------+-------------------+-------+------+------+-------------------+ | backend_ndx | address           | state | type | uuid | connected_clients | +-------------+-------------------+-------+------+------+-------------------+ |           1 | 172.16.1.101:3306 | up    | rw   | NULL |                 0 | |           2 | 172.16.1.105:3306 | up    | ro   | NULL |                 0 | +-------------+-------------------+-------+------+------+-------------------+ 2 rows in set (0.00 sec) 到此，mysql借助于mysql-proxy实现读写分离的目的已然实现