DBProxy安装使用初探
前言：
DBProxy是由美团点评公司技术工程部DBA团队（北京）开发维护的一个基于MySQL协议的数据中间层。它在奇虎360公司开源的Atlas基础上，修改了部分bug，并且添加了很多特性，支持读写分离，从库负载均衡，IP过滤，分表，DBA可平滑上下线DB，自动摘除宕机的DB，监控信息完备，SQL过滤，从库流量配置。
架构：
DBProxy 可以配置一主多从结构，即必须配置一个主库，0个或多个从库，主库可进行读写操作，从库只可进行读操作。上游组件/应用的流量可以通过DBProxy中设置的规则，将流量路由到合适的数据库中。DBProxy部署示意图如下所示：
安装包:
DBProxy-github地址：https://github.com/Meituan-Dianping/DBProxy/
jemalloc-github地址: https://github.com/jemalloc/jemalloc
glib-2.42.0地址: https://src.fedoraproject.org/repo/pkgs/mingw-glib2/glib-2.42.0.tar.xz/71af99768063ac24033ac738e2832740/
percona地址:https://www.percona.com/downloads/percona-release/
centos地址:https://centos.pkgs.org/7/centos-x86_64
安装步骤：
github下载DBProxy源代码
安装依赖包
--centosyum install -y Percona-Server-devel-55.x86_64 Percona-Server-client-55.x86_64 Percona-Server-shared-55 jemalloc jemalloc-devel libevent libevent-devel openssl openssl-devel lua lua-devel bison flex libtool.x86_64#安装jemallocunzip /root/jemalloc-dev.zipmv jemalloc-dev jemalloccd jemalloc./autogen.shmake -j 4make installecho /root/jemalloc/lib >> /etc/ld.so.conf ldconfig#安装Percona依赖包[root@ol7db1 ~]# rpm -ivh Percona-Server-shared-56-5.6.51-rel91.0.1.el7.x86_64.rpm warning: Percona-Server-shared-56-5.6.51-rel91.0.1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEYPreparing... ################################# [100%]Updating / installing... 1:Percona-Server-shared-56-5.6.51-rwarning: /etc/my.cnf created as /etc/my.cnf.rpmnew################################# [100%][root@ol7db1 ~]# rpm -ivh Percona-Server-client-56-5.6.51-rel91.0.1.el7.x86_64.rpm warning: Percona-Server-client-56-5.6.51-rel91.0.1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEYPreparing... ################################# [100%]Updating / installing... 1:Percona-Server-client-56-5.6.51-r################################# [100%][root@ol7db1 ~]# rpm -ivh Percona-Server-devel-56-5.6.51-rel91.0.1.el7.x86_64.rpm warning: Percona-Server-devel-56-5.6.51-rel91.0.1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEYPreparing... ################################# [100%]Updating / installing... 1:Percona-Server-devel-56-5.6.51-re################################# [100%]#安装glibc#使用glib2-2.54没问题cd glib-2.42.0autoreconf -ivf./configuremake && make install--Ubuntu & Debianapt-get install libmysqlclient-dev libgcrypt11-dev pkg-config lua5.1-0 liblua5.1-0-dev libtool flex bison openssl libssl-dev libjemalloc1 libjemalloc-dev libevent-dev编译源码并安装
#进入解压后的目录DBProxy-master/sh autogen.shsh bootstrap.shmake && make install#安装成功,可以看到以下执行文件[root@ol7db1 bin]# ls -rlt /usr/local/mysql-proxy/bin/total 4552-rwxr-xr-x 1 root root 1496320 Apr 20 19:05 mysql-binlog-dump-rwxr-xr-x 1 root root 1491456 Apr 20 19:05 mysql-myisam-dump-rwxr-xr-x 1 root root 1638400 Apr 20 19:05 mysql-proxy-rwxr-xr-x 1 root root 10368 Apr 20 19:05 encrypt-rwxr-xr-x 1 root root 2351 Apr 20 19:05 mysql-proxyd-rwxr-xr-x 1 root root 267 Apr 20 19:05 log.sh-rwxr-xr-x 1 root root 997 Apr 20 19:05 create_table.sh-rwxr-xr-x 1 root root 785 Apr 20 19:05 cron.sh[root@ol7db1 bin]#注意，目前DBProxy默认安装路径为：/usr/local/mysql-proxy。修改DBProxy默认安装路径，需要修改bootstrap.sh文件。例如将默认安装路径修改为：/usr/dbproxy，则需修改bootstrap.sh中--prefix后的参数，修改如下：
#!/bin/shbase=$(cd "$(dirname "$0")"; pwd)cd $base./configure --prefix=/usr/dbproxy CFLAGS="-g -O0" ##仅仅修改该行安装报错处理:
编译glibc报错:
#报错gdate.c:2497:7: error: format not a string literal, format string not checked [-Werror=format-nonliteral]tmplen = strftime (tmpbuf, tmpbufsize, locale_format, &tm);#解决方法打开gdate.c文件(在glib目录下)，找到调用strftime函数的2497行，在strftime前后加上如下预处理命令#pragma GCC diagnostic push#pragma GCC diagnostic ignored "-Wformat-nonliteral"tmplen = strftime (tmpbuf, tmpbufsize, locale_format, &tm);#pragma GCC diagnostic popbootstrap.sh报错:
#报错1configure: error: checked for Lua via pkg-config: No package 'lua5.1' found. Make sure lua and its devel-package, which includes the lua5.1.pc (debian and friends) or lua.pc (all others) file, is installed#解决方法1:/etc/profile 添加以下环境变量export LUA_CFLAGS="-I/usr/local/lua/include"export LUA_LIBS="-L/usr/local/lua/lib -llua -ldl"#报错2configure: error: libevent is required#解决方法2安装libevent-develmake报错:
#报错ua-load-factory.c:34:10: fatal error: lua.h: No such file or directory#解决方法安装lua-devel配置启动文件:
拷贝模板配置文件
#创建配置文件所在文件夹mkdir /usr/local/mysql-proxy/conf #拷贝示例配置文件cp /root/DBProxy-master/script/source.cnf.samples /usr/local/mysql-proxy/conf/source.cnf编辑配置文件
[mysql-proxy]#带#号的为非必需的配置项目#管理接口的用户名admin-username=guest#管理接口的密码admin-password=guest#dbproxy后端连接的MySQL主库的IP和端口，可设置多项，用逗号分隔proxy-backend-addresses=192.168.1.110:3306#dbproxy后端连接的MySQL从库的IP和端口，@后面的数字代表权重，用来作负载均衡，若省略则默认为1，可设置多项，用逗号分隔proxy-read-only-backend-addresses=192.168.1.111:3306@1,192.168.1.111:3306@2#用户名与其对应的加密过的MySQL密码(用户为MySQL用户密码)，密码使用PREFIX/bin目录下的加密程序encrypt加密，下行的user1和user2为示例，将其替换为你的MySQL的用户名和加密密码！pwds=guest:JApndxsQ#设置dbproxy的运行方式，设为true时为守护进程方式，设为false时为前台方式，一般开发调试时设为false，线上运行时设为truedaemon=true#设置dbproxy的运行方式，设为true时dbproxy会启动两个进程，一个为monitor，一个为worker，monitor在worker意外退出后会自动将其重启，设为false时只有worker，没有monitor，一般开发调试时设为false，线上运行时设为truekeepalive=true#工作线程数，对dbproxy的性能有很大影响，可根据情况适当设置event-threads=16#日志级别，分为message、warning、critical、error、debug五个级别log-level=message#日志存放的路径log-path=/tmp/dbproxy_log/#SQL日志的开关，可设置为OFF、ON、REALTIME，OFF代表不记录SQL日志，ON代表记录SQL日志，REALTIME代表记录SQL日志且实时写入磁盘，默认为OFFsql-log=ON#SELECT无where条件的限制，分为ON,OFFselect-where-limit = ON#实例名称，用于同一台机器上多个dbproxy实例间的区分instance=test1#dbproxy监听的工作接口IP和端口proxy-address=0.0.0.0:3308#dbproxy监听的管理接口IP和端口admin-address=0.0.0.0:3309#分表设置，此例中person为库名，mt为表名，id为分表字段，3为子表数量，可设置多项，以逗号分隔，若不分表则不需要设置该项#tables = person.mt.id.3#默认字符集，设置该项后客户端不再需要执行SET NAMES语句#charset=utf8#dbproxy前面挂接的LVS的物理网卡的IP(注意不是虚IP)，若有LVS且设置了client-ips则此项必须设置，否则可以不设置#lvs-ips = 192.168.1.1max-connections=1000#长时等待阈值，同步等待时间超过该阈值则认为是长时等待long-wait-time=500 #慢查询阈值，查询执行时间超过该阈值则认为是慢查询long-query-time=500 #0：不统计，1：仅统计总体的响应时间，其中包括慢查询，2：进行直方图统计；默认为1。query-response-time-stats=2#SQL日志文件最大大小，单位为字节，默认为1Gsql-log-file-size=1073741824#保留的最大SQL日志文件个数，默认为0，不保留历史文件，仅保留当前文件sql-log-file-num=500#后台MySQL版本号，默认为5.5,只支持5.5，5.6mysql-version=5.6#后台MySQL最大连接数，默认为0，表示不限制backend-max-thread-running=64#后台MySQL连接超过backend-max-thread-running后，每次sleep的时间，单位为msthread-running-sleep-delay=10#SQL过滤统计缓存的SQL模板数，默认为0lastest-query-num=100000#SQL过滤的时间阈值query-filter-time-threshold=500#SQL过滤的频率阈值query-filter-frequent-threshold=10#SQL过滤频率统计时间窗口内的最小执行次数，根据频率和该参数计算时间窗口access-num-per-time-window = 5#手动过滤SQL是否生效，默认为OFFmanual-filter-flag=OFF#自动过滤SQL是否生效，默认为OFFauto-filter-flag=OFF注意，配置文件中配置的“pwds=guest:uqmOY9A=”项中的密码是加密后的密码，加密方法：
./dbproxy/script/encrypt [原始密码]启动DBProxy：
在安装目录下的bin/mysql-proxy进行启动，启动时需传入配置文件的路径。例如:默认安装的路径为：/usr/local/mysql-proxy，配置文件路径为：/usr/local/mysql-proxy/conf/source.cnf，且配置文件中配置了必须配置的参数，启动命令如下:
/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/source.cnf启动之后可以看到两个主进程worker和monitor
[root@ol7db1 dbproxy_log]# ps -ef |grep -i mysql-proxy |grep -v greproot 94292 1 0 19:42 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/source.cnfroot 94293 94292 0 19:42 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/source.cnf查看后台日志目录以及进程pid
[root@ol7db1 dbproxy_log]# pwd/tmp/dbproxy_log[root@ol7db1 dbproxy_log]# ls -rlttotal 8-rw------- 1 root root 5 Apr 20 19:42 test1.piddrwxr-xr-x 2 root root 27 Apr 20 19:42 sql-rw-r----- 1 root root 1814 Apr 20 19:42 test1.log[root@ol7db1 dbproxy_log]# tail -100f test1.log 2022-04-20 19:42:45.564036: (message)mysql-proxy-cli.c:954(main_cmdline)dbproxy 0.2.5 started - instance: test12022-04-20 19:42:45.564152: (warning)chassis-filter.c:195(load_sql_filter_from_file)[filter][load from file][failed][No such file or directory]2022-04-20 19:42:45.564704: (message)proxy-plugin.c:3833(network_mysqld_proxy_plugin_apply_config)proxy listening on port 0.0.0.0:33082022-04-20 19:42:45.571103: (message)network-backend.c:615(network_backends_add)add read/write backend: 192.168.1.110:3306 success2022-04-20 19:42:45.573224: (message)network-backend.c:606(network_backends_add)add read-only backend 192.168.1.111:3306 to default backends2022-04-20 19:42:45.573244: (message)network-backend.c:615(network_backends_add)add read-only backend: 192.168.1.111:3306@1 success2022-04-20 19:42:45.575125: (warning)network-backend.c:586(network_backends_add)backend 192.168.1.111:3306 is already known!2022-04-20 19:42:45.575153: (message)proxy-sql-log.c:484(load_sql_filenames)load_files_from_dir /tmp/dbproxy_log//sql2022-04-20 19:42:45.575182: (message)proxy-sql-log.c:505(load_sql_filenames)load_files_from_dir /tmp/dbproxy_log//sql success2022-04-20 19:42:45.575775: (message)chassis-event-thread.c:382(chassis_event_threads_start)starting 16 threads2022-04-20 19:42:45.580504: (message)chassis-unix-daemon.c:131(chassis_unix_proc_keepalive)[angel] we try to keep PID=94293 alive2022-04-20 19:42:45.591603: (message)proxy-sql-log.c:25(log_manager)log_manager thread start2022-04-20 19:42:45.724108: (message)proxy-percentile.c:336(check_percentile)check_percentile thread start2022-04-20 19:42:46.621287: (message)proxy-plugin.c:3444(check_state)check_state thread start2022-04-20 19:42:46.635664: (warning)proxy-plugin.c:3548(check_state)set backend (192.168.1.111:3306) state to UP通过admin连接:
管理用户和端口，可以通过配置文件查看
#管理接口的用户名admin-username=guest#管理接口的密码admin-password=guest#DBProxy监听的管理接口IP和端口admin-address=0.0.0.0:3308通过3309端口登陆，方式跟mysql一样
mysql -uguest -pguest -h10.38.12.100 -P3309进入之后，可以通过help查看命令
select * from help;通过proxy连接:
可以通过以下查询类型proxy用户
SELECT * FROM pwds ;guest@10.38.12.100 : (none)08:05:32> SELECT * FROM pwds ;+----------+----------+-------+----------+-------+| username | password | hosts | backends | type |+----------+----------+-------+----------+-------+| test | NxpxcA== || | proxy || ziniu | ORZsbQs= || | proxy || guest | JApndwo= || | admin || % | || | proxy |+----------+----------+-------+----------+-------+使用用户连接proxy,通过3308端口
mysql -uziniu -pziniu -h10.38.12.100 -P3308ziniu@10.38.12.100 : (none)08:08:22>show databases;+--------------------+| Database |+--------------------+| information_schema |+--------------------+1 row in set (0.00 sec)