一、主从配置
192.168.130.61 master
192.168.130.62 slave
192.168.130.63 proxy
master配置
[mysqld]
socket=/tmp/mysql.sock
basedir=/usr/local/mysql
datadir=/mydata/data
log-bin=/mydata/binlog/log-bin
server-id=1
bind-address = 0.0.0.0
skip-name-resolve
service mysqld restart
mysql
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.130.%' IDENTIFIED BY 'replpass';
FLUSH PRIVILEGES;
mysql> SHOW MASTER STATUS;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| log-bin.000019 | 625 | | | |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
slave配置
[mysqld]
socket=/tmp/mysql.sock
basedir=/usr/local/mysql
datadir=/mydata/data
#log-bin=/mydata/binlog/log-bin
server-id=2
bind-address = 0.0.0.0
skip-name-resolve
relay-log = /mydata/binlog/relay-bin
service mysqld restart
mysql
CHANGE MASTER TO MASTER_HOST = '192.168.130.61', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='log-bin.000019', MASTER_LOG_POS=625;
START SLAVE;
二、安装配置mysql-proxy:
2.1 下载所需要的版本
wget https://cdn.mysql.com/archives/mysql-proxy/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz --no-check-certificate
tar xf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz -C /usr/local/
cd /usr/local
ln -sv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit mysql-proxy
添加代理用户
useradd mysql-proxy
2.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
fi
# 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
fi
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
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 $prog >&/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
将上述内容保存为/etc/rc.d/init.d/mysql-proxy，给予执行权限，而后加入到服务列表。
chmod +x /etc/rc.d/init.d/mysql-proxy
chkconfig --add mysql-proxy
2.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 --plugins=admin --proxy-backend-addresses=192.168.130.61:3306 --proxy-read-only-backend-addresses=192.168.130.62:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"
其中的proxy-backend-addresses选项和proxy-read-only-backend-addresses选项均可重复使用多次，以实现指定多个读写服务器或只读服务器。
2.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%$
Copyright (c) 2007, 2012, Oracle and/or its affiliates. All rights reserved.
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] = {
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
service mysql-proxy start
[root@www local]# netstat -tuanlp
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 2799/mysql-proxy
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2799/mysql-proxy
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 926/sshd
tcp 0 96 192.168.130.63:22 192.168.53.137:60222 ESTABLISHED 1914/sshd: root@pts
tcp6 0 0 :::22 :::* LISTEN 926/sshd
6、测试
6.1 管理功能测试
[root@www local]# mysql -uadmin -padmin -h192.168.130.63 --port=4041
mysql: [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
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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> SELECT * FROM backends;
+-------------+---------------------+---------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+---------------------+---------+------+------+-------------------+
| 1 | 192.168.130.61:3306 | unknown | rw | NULL | 0 |
| 2 | 192.168.130.62:3306 | unknown | ro | NULL | 0 |
+-------------+---------------------+---------+------+------+-------------------+
2 rows in set (0.00 sec)
在master和slave上
GRANT ALL ON *.* TO 'admin'@'192.168.130.%' IDENTIFIED BY 'admin';
FLUSH PRIVILEGES;
tcpdump -i eth0 -s0 -nn -XX tcp dst port 3306 and dst host 192.168.130.61
6.2 读写分离测试
mysql -uadmin -padmin -h192.168.130.67
[root@localhost ~]# netstat -tuanlp
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 12159/mysql-proxy
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 12159/mysql-proxy
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1303/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 2179/master
tcp 0 0 192.168.130.67:3306 192.168.130.67:59754 ESTABLISHED 12159/mysql-proxy
tcp 0 0 192.168.130.67:58734 192.168.130.61:3306 ESTABLISHED 12159/mysql-proxy
tcp 0 0 192.168.130.67:58736 192.168.130.61:3306 ESTABLISHED 12159/mysql-proxy
tcp 0 96 192.168.130.67:22 192.168.53.137:60000 ESTABLISHED 2976/sshd: root@pts
tcp 0 0 192.168.130.67:59754 192.168.130.67:3306 ESTABLISHED 12288/mysql
tcp6 0 0 :::22 :::* LISTEN 1303/sshd
tcp6 0 0 ::1:25 :::* LISTEN 2179/master
[root@localhost ~]#
tcpdump的语法：
tcpdump [options] [Protocol] [Direction] [Host(s)] [Value] [Logical Operations] [Other expression]
Protocol(协议):
Values(取值): ether, fddi, ip, arp, rarp, decnet, lat, sca, moprc, mopdl, tcp and udp.
If no protocol is specified, all the protocols are used.
Direction(流向):
Values(取值): src, dst, src and dst, src or dst
If no source or destination is specified, the "src or dst" keywords are applied. (默认是src or dst)
For example, "host 10.2.2.2" is equivalent to "src or dst host 10.2.2.2".
Host(s)(主机):
Values(替代关键字): net, port, host, portrange.
If no host(s) is specified, the "host" keyword is used. 默认如果此段没有指定关键字，默认即host。
For example, "src 10.1.1.1" is equivalent to "src host 10.1.1.1".
Logical Operations:
(1) AND
and or &&
(2) OR
or or ||
(3) EXCEPT
not or !
常用选项：
-i any : Listen on all interfaces just to see if you're seeing any traffic.
-n : Don't resolve hostnames.
-nn : Don't resolve hostnames or port names.
-X : Show the packet's contents in both hex and ASCII.
-XX : Same as -X, but also shows the ethernet header.
-v, -vv, -vvv : Increase the amount of packet information you get back.
-c # : Only get x number of packets and then stop.
-s : Define the snaplength (size) of the capture in bytes. Use -s0 to get everything, unless you are intentionally capturing less.
-S : Print absolute sequence numbers.
-e : Get the ethernet header as well.
-q : Show less protocol information.
-E : Decrypt IPSEC traffic by providing an encryption key.
-A ：Display Captured Packets in ASCII
-w /path/to/some_file : Capture the packets and write into a file
-r /path/from/some_file : Reading the packets from a saved file
-tttt : Capture packets with proper readable timestamp
ip host 172.16.100.1
ip src host 172.16.100.1
ip dst host 172.16.100.1
ip src and dst host 172.16.100.1
tcp src port 110
tcpdump -i eth0 -s0 -nn -XX tcp dst port 3306 and dst host 192.168.130.61
