Mysql-proxy (American mission) deployment record

[Email protected] ~]# RPM-IVH epel-release-6-8.noarch.rpm

[Email protected] ~]# RPM-IVH percona-release-0.1-4.noarch.rpm

[[email protected] ~]# rpm-e mysql-devel MySQL

[email protected] ~]# Yum 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

[email protected] ~]# Yum install zlib-devel zlib libffi libffi-devel gettext automake libtool autoconf

[Email protected] ~]# tar xvf glib-2.42.0.tar

[Email protected] ~]# CD glib-2.42.0

[Email protected] glib-2.42.0]# AUTORECONF-IVF

[Email protected] glib-2.42.0]#/configure--prefix=/usr--libdir=/usr/lib64

[[email protected] glib-2.42.0]# make

[[email protected] glib-2.42.0]# make install

[Email protected] ~]# vi/etc/ld.so.conf

/usr/lib64

[Email protected] ~]# Ldconfig

[[email protected] ~]# git clone https://github.com/Meituan-Dianping/DBProxy.git

Initialized Empty Git repository in/root/dbproxy/.git/

Remote:counting objects:2204, done.

Remote:total 2204 (Delta 0), reused 0 (Delta 0), pack-reused 2204

Receiving objects:100% (2204/2204), 15.71 MiB | 349 kib/s, done.

Resolving deltas:100% (742/742), done.

[Email protected] ~]# CD Dbproxy

[Email protected] dbproxy]# sh autogen.sh

[Email protected] dbproxy]# sh bootstrap.sh

[[email protected] dbproxy]# make

[[email protected] dbproxy]# make install

[Email protected] dbproxy]# mkdir/usr/local/mysql-proxy/conf

[email protected] dbproxy]# CP script/source.cnf.samples/usr/local/mysql-proxy/conf/source.cnf

[Email protected] ~]# Cd/usr/local/mysql-proxy/bin

[Email protected] bin]#/encrypt TPCC

qayicw==

[Email protected] bin]# CAT/USR/LOCAL/MYSQL-PROXY/CONF/SOURCE.CNF

[Mysql-proxy]

#带 # is a non-required configuration item

#管理接口的用户名

Admin-username=guest

#管理接口的密码

Admin-password=guest

#dbproxy后端连接的MySQL主库的IP和端口, multiple items can be set, separated by commas

proxy-backend-addresses=192.168.1.201:3306

#dbproxy后端连接的MySQL从库的IP和端口, the number at the end of the @ represents the weight, used for load balancing, and if omitted the default is 1, you can set multiple items, separated by commas

proxy-read-only-backend-addresses=190.168.1.202:3306

#用户名与其对应的加密过的MySQL密码, password using the Prefix/bin directory encryption program encrypt encryption, downlink user1 and User2 For example, replace it with your MySQL username and encryption password!

pwds=tpcc:qayicw==

#设置dbproxy的运行方式, set to True when the daemon mode, set to False as the foreground mode, general development debugging is set to False, the online runtime is set to True

Daemon=true

#设置dbproxy的运行方式, set to True when Dbproxy will start two processes, one for monitor, and one for Worker,monitor to automatically restart after the worker exits unexpectedly, set to False when only the worker, No monitor, general development debug set to False, on-line runtime set to True

Keepalive=true

#工作线程数, has a great impact on the performance of Dbproxy, can be set appropriately according to the situation

Event-threads=8

#日志级别, divided into a message, warning, critical, error, debug five levels

Log-level=debug

#日志存放的路径

log-path=/var/log/dbproxy_log/

#SQL日志的开关, can be set to off, on, Realtime,off for not logging SQL logs, on for logging SQL logs, REALTIME for logging SQL logs and writing to disk in real time, default to OFF

Sql-log=on

#SELECT无where条件的限制, divided into On,off

Select-where-limit = OFF

#实例名称 for the distinction between multiple dbproxy instances on the same machine

Instance=source

#dbproxy监听的工作接口IP和端口

proxy-address=0.0.0.0:3308

#dbproxy监听的管理接口IP和端口

admin-address=0.0.0.0:3309

#分表设置, in this case, the person is the library name, MT is the table name, the ID is a sub-table field, 3 is the number of child tables, you can set multiple items, separated by commas, if the table does not need to set the item

#tables = Person.mt.id.3

#默认字符集, the client no longer needs to execute the SET NAMES statement after setting the item

#charset =utf8

#dbproxy前面挂接的LVS的物理网卡的IP (note is not a virtual IP), if there is LVS and set client-ips, this key must be set, otherwise you can not set

#lvs-ips = 192.168.1.1

max-connections=1000

#长时等待阈值, the synchronization wait time exceeds this threshold and is considered to be a long wait

long-wait-time=500

#慢查询阈值, query execution time exceeds this threshold is considered slow query

long-query-time=500

#0: No statistics, 1: Statistics only the overall response time, including slow query, 2: histogram statistics; default is 1.

query-response-time-stats=2

#SQL日志文件最大大小, in bytes, defaults to 1G

sql-log-file-size=1073741824

#保留的最大SQL日志文件个数, the default is 0, the history file is not preserved, only the current file is preserved

sql-log-file-num=500

#后台MySQL版本号, default is 5.5

mysql-version=5.6

#后台MySQL最大连接数, the default is 0, which means no limit

Backend-max-thread-running=64

#后台MySQL连接超过backend-max-thread-running, the time per sleep, in MS

thread-running-sleep-delay=10

#SQL过滤统计缓存的SQL模板数, default is 0

lastest-query-num=100000

#SQL过滤的时间阈值

query-filter-time-threshold=500

#SQL过滤的频率阈值

query-filter-frequent-threshold=10

#SQL过滤频率统计时间窗口内的最小执行次数, calculates the time window based on frequency and this parameter

Access-num-per-time-window = 5

#手动过滤SQL是否生效, the default is off

Manual-filter-flag=off

#自动过滤SQL是否生效, the default is off

Auto-filter-flag=off

[Email protected] ~]# Cd/usr/local/mysql-proxy/bin

[[email protected] bin]#./mysql-proxyd Source Start

Ok:mysql-proxy of Source is started

[[email protected] bin]#./mysql-proxyd Source Status

Mysql-proxy of Source is running (2389)

Mysql-proxy of Source is running (2390)

[Email protected] bin]# mysql-utpcc-ptpcc-p3308-h127.0.0.1

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 1

Server Version:5.6.22-71.0-log Source Distribution

Copyright (c) 2009-2018 Percona LLC and/or its affiliates

Copyright (c), 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| tpcc1000 |

+--------------------+

2 rows in Set (0.08 sec)

Mysql-proxy (American mission) deployment record