Shulou(Shulou.com)06/01 Report--

This article mainly introduces the mysql-proxy agent machine analysis, hoping to supplement and update some knowledge, if you have other questions to understand, you can continue to follow my updated article in the industry information.

I. introduction to the use of scenarios

Mysql-proxy 's machine belongs to the proxy machine, which is equivalent to the whitelist audit machine. Through the firewall rules, it restricts the ports that allow those IP to access the local agent's intranet database.

One thing: to ensure that the database of the intranet is not exposed on the public network, ensuring the security of the database.

Cons: once the mysql-proxy proxy cloud server is down, it means that the application can no longer connect to the database. At the same time, if too many applications link to the intranet database through mysql-proxy, the mysql-proxy proxy server must also ensure enough bandwidth to be responsible for causing the link to the database to be particularly slow or unable to link to the database.

Second, the demonstration environment:

2 physical machines, both systems are CentOS release 6.8 (Final)

One machine is configured with internal and external network card: 104.137.27.4 192.168.1.100 deploy mysql-proxy service

A machine is configured with a foreign network card: 104.137.27.3 deploy the mysql service, log in to this machine database, and authorize an account to allow 104.137.27.4 machines to link to this database.

Grant all on mtoyydb.* to zytestuser01@'104.137.27.4' identified by 'dr3dfKj=DHee';mysql > flush privileges

Third, binary installation mysql-proxy:

Download the installation package:

Wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gztar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit-C / usr/local/cd / usr/local/mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit mysql-proxymkdir / usr/local/mysql-proxy/ {conf,log}-p

Define the environment variables:

[root@book sysconfig] # tail-3 / etc/profileLUA_PATH= "/ usr/local/mysql-proxy/share/doc/mysql-proxy/?.lua" export LUA_PATHexport PATH=$PATH:/usr/local/mysql-proxy/bin

4. Introduction of mysql-proxy parameters:

Application Options:

4.1 introduction to application parameters:

Mysql-proxy-help-all

Introduction to parameters of 4.2mysql-proxy Agent Module

4.3 online profile introduction:

[root@book mysql-proxy] # cat / usr/local/mysql-proxy/conf/mysql-proxy.conf [mysql-proxy] user=wwwdaemon=truekeepalive=trueplugins=proxy Admin### log level log-level=infolog-file=/usr/local/mysql-proxy/log/mysql-proxy.log### native ip address proxy-address=104.137.27.4:9196## back main attention addressesproxy-backend-addresses=104.137.27.3:3306##proxy administrative user admin IP and port admin-address=104.137.27.4:9197### three parameters must be set Otherwise, the lua script address of the admin-username=zykjadminadmin-password=Zyjkwestos###admin that the mysql-proxy service cannot start Admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua

5. Start mysql-proxy

/ usr/local/mysql-proxy/bin/mysql-proxy-- defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

Log in to the mysql-proxy management account and view the reverse agent management list

[root@book] # mysql-uzykjadmin-pZyjkwestos-h204.137.27.4-- port=9197

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 1Server version: 5.0.99-agent-adminCopyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c' to clear the current input statement.MySQL [(none)] > SELECT * FROM backends +-+ | backend_ndx | address | state | type | uuid | connected_clients | +-+- -+ | 1 | 104.137.27.3 up 3306 | rw | NULL | 2 | +- -+ 1 row in set (0.00 sec) MySQL [(none)] > SELECT * FROM help +-+-- + | command | description | +-+- -- + | SELECT * FROM help | shows this help | | SELECT * FROM backends | lists the backends and their state | + + 2 rows in set (0.00 sec) MySQL [(none)] >

Log in to the reverse proxy database through sqlyog

Login succeeded:

7. Configuration strategy of MySQL-porxy proxy server firewall:

[root@book sysconfig] # cat / etc/sysconfig/iptables#Generated by iptables-save v1.4.7 on Thu Mar 2 14:32:03 2017*filter:INPUT ACCEPT [358 ACCEPT-An INPUT 20023]: FORWARD ACCEPT [0:0]: OUTPUT ACCEPT [2491 etc/sysconfig/iptables#Generated by iptables-save 287941]-An INPUT-I lo-j ACCEPT-An INPUT-m state-- state RELATED ESTABLISHED-j ACCEPT-An INPUT-p tcp-m tcp-- dport 4567-j ACCEPT-An INPUT-p tcp-m multiport-- dports 80Let81443-m state-- state NEW-j ACCEPT-An INPUT-s 104.137.27.6 Universe 32-p tcp-m tcp-- dport 873-m state-- state NEW-j ACCEPT-An INPUT-s 304.37.57.45 32-p tcp-m multiport-- dports 9196-j ACCEPT-An INPUT-s 204.17.47.245 Tcp-m multiport-- dports 21 tcp 10050 3306-j ACCEPT#-An INPUT-p tcp-m tcp-- dport 9196-j DROP##-An INPUT-p icmp- m icmp--icmp-type 8-j ACCEPT-An INPUT-p icmp- m icmp--icmp-type 8-m limit--limit 5/sec-limit-burst 10-j ACCEPT-An INPUT-j DROPCOMMIT#Completed on Thu Mar 2 14:32:03 2017

7.1 configuration Policy explanation:

-'

An INPUT-p tcp-m tcp-- dport 4567-j ACCEPT

Here 6029 is equivalent to the login port number of sshd

-An INPUT-p tcp-m multiport-- dports 80Let81443-m state-- state NEW-j ACCEPT

Release 80BI 81 and 443 service ports

-An INPUT-s 104.137.27.6 ACCEPT 32-p tcp-m tcp-- dport 873-m state-- state NEW-j ACCEPT

Allow a specific IP104.137.27.6 to pull data from the local server through rsync

-An INPUT-s 304.37.57.45 tcp 32-p tcp-m multiport-- dports 9196-j ACCEPT

Allows the specified IP304.37.57.45 to link to port 9196 of the MySQL-proxy service, thereby linking to the intranet database

-An INPUT-s 204.17.47.245 tcp 32-p tcp-m multiport-- dports 21 ACCEPT 10050 Power3306-j ACCEPT

Explanation: deploy-server on 204.17.47.245 machine, log in to-server by dial-up, then log in to ftp and log in to 3306 database service

-An INPUT-p icmp- m icmp--icmp-type 8-m limit--limit 5/sec-- limit-burst 10-j ACCEPT

Explanation: make a restriction strategy for ping

-An INPUT-j DROP

Explanation: all other client IP addresses are prohibited from accessing the port of the service on this server

At the end of the introduction here, it is said that ucloud's Udb adopts such a database architecture pattern.

Read the above about the mysql-proxy agent machine analysis, hope to bring some help to everyone in the practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.