sysown / proxysql Goto Github PK

High-performance MySQL proxy with a GPL license.

License: GNU General Public License v3.0

Makefile 0.94% C++ 91.29% C 5.29% Shell 0.83% Python 1.17% Perl 0.09% Dockerfile 0.05% PHP 0.17% Visual Basic 6.0 0.18%

proxysql's Introduction

Introduction

ProxySQL is a high performance, high availability, protocol aware proxy for MySQL and forks (like Percona Server and MariaDB). All the while getting the unlimited freedom that comes with a GPL license.

Its development is driven by the lack of open source proxies that provide high performance.

Useful links

Getting started

Installation

Released packages can be found here: https://github.com/sysown/proxysql/releases

Just download a package and use your systems package manager to install it:

wget https://github.com/sysown/proxysql/releases/download/v2.4.2/proxysql_2.4.2-ubuntu20_amd64.deb
dpkg -i proxysql_2.4.2-ubuntu20_amd64.deb

Alternatively you can also use the available repositories:

Ubuntu / Debian:

Adding repository:

apt-get update && apt-get install -y --no-install-recommends lsb-release wget apt-transport-https ca-certificates
wget -nv -O /etc/apt/trusted.gpg.d/proxysql-2.4.x-keyring.gpg 'https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/repo_pub_key.gpg'
echo "deb https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/$(lsb_release -sc)/ ./" | tee /etc/apt/sources.list.d/proxysql.list

Installing:

apt-get update
apt-get install proxysql OR apt-get install proxysql=version

Red Hat / CentOS:

Adding repository:

cat > /etc/yum.repos.d/proxysql.repo << EOF
[proxysql]
name=ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/repo_pub_key
EOF

Installing:

yum install proxysql OR yum install proxysql-version

Amazon Linux:

Adding repository:

cat > /etc/yum.repos.d/proxysql.repo << EOF
[proxysql]
name=ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/centos/8
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/repo_pub_key
EOF

Installing:

yum install proxysql OR yum install proxysql-version

Almalinux:

Adding repository:

cat > /etc/yum.repos.d/proxysql.repo << EOF
[proxysql]
name=ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/almalinux/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/repo_pub_key
EOF

Installing:

yum install proxysql OR yum install proxysql-version

OpenSUSE:

Adding repository:

cat > /etc/zypp/repos.d/proxysql.repo << EOF
[proxysql]
name=ProxySQL Zypper repository
enabled=1
autorefresh=0
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/opensuse/\$releasever_major
gpgcheck=1
EOF

zypper addrepo -g -n 'ProxySQL Zypper repository' 'https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/opensuse/$releasever_major' proxysql

Installing:

yum install proxysql OR yum install proxysql-version

Service management

Once the software is installed, you can use the service command to control the process:

Starting ProxySQL:

service proxysql start

Stopping ProxySQL:

service proxysql stop

Or alternatively via the Admin interface:

$ mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
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 4
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2016, 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.

Admin> proxysql stop

Restarting ProxySQL:

service proxysql restart

Or alternatively via the Admin interface:

$ mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
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 4
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2016, 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.

Admin> proxysql restart

Reinitializing ProxySQL from the config file (after first startup the DB file is used instead of the config file):

# If you are using the init script run:
/etc/init.d/proxysql initial
# or
service proxysql initial
 
# If you are using the systemd unit file run:
systemctl start proxysql-initial
# or
service proxysql-initial start

Upgrades

Just install the new package and restart ProxySQL:

wget https://github.com/sysown/proxysql/releases/download/v2.1.0/proxysql_2.1.0-ubuntu16_amd64.deb
dpkg -i proxysql_2.1.0-ubuntu16_amd64.deb
service proxysql restart

How to check the ProxySQL version

$ proxysql --version

ProxySQL version 2.1.0-544-g17a4b4a7, codename Truls

A debug version has _DEBUG in its version string. It is slower than non-debug version, but easier to debug in case of failures.

$ proxysql --version

Main init phase0 completed in 0.000146 secs.
ProxySQL version 2.1.0-544-g17a4b4a7_DEBUG, codename Truls

Configuring ProxySQL via the `admin interface`

First of all, bear in mind that the best way to configure ProxySQL is through its admin interface. This lends itself to online configuration (without having to restart the proxy) via SQL queries to its admin database. It's an effective way to configure it both manually and in an automated fashion.

As a secondary way to configure it, we have the configuration file.

Configuring ProxySQL through the admin interface

To log into the admin interface (with the default credentials) use a mysql client and connect using the following admin credentials locally on port (6032):

$ mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
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 4
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2016, 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.

Admin>

note: If your MySQL client version is version 8.04 or higher add --default-auth=mysql_native_password to the above command to connect to the admin interface.

Once connected to the admin interface, you will have a list of databases and tables at your disposal that can be queried using the SQL language:

Admin> SHOW DATABASES;
+-----+---------+-------------------------------+
| seq | name    | file                          |
+-----+---------+-------------------------------+
| 0   | main    |                               |
| 2   | disk    | /var/lib/proxysql/proxysql.db |
| 3   | stats   |                               |
| 4   | monitor |                               |
+-----+---------+-------------------------------+
4 rows in set (0.00 sec)

This will allow you to control the list of the backend servers, how traffic is routed to them, and other important settings (such as caching, access control, etc). Once you've made modifications to the in-memory data structure, you must load the new configuration to the runtime, or persist the new settings to disk (so that they are still there after a restart of the proxy). A detailed tutorial on how to configure ProxySQL through the Admin interface is available here.

Configuring ProxySQL through the config file

Even though the config file should only be regarded as a secondary way to configure the proxy, we must not discard its value as a valid way to bootstrap a fresh ProxySQL install.

Let's quickly go over the main sections of the configuration file (this overview serves as a very high level overview of ProxySQL configuration).

Top-level sections:

admin_variables: contains global variables that control the functionality of the admin interface.
mysql_variables: contains global variables that control the functionality for handling the incoming MySQL traffic.
mysql_servers: contains rows for the mysql_servers table from the admin interface. Basically, these define the backend servers towards which the incoming MySQL traffic is routed. Rows are encoded as per the .cfg file format, here is an example:
```
 mysql_servers =
 (
 	{
 		address="127.0.0.1"
 		port=3306
 		hostgroup=0
 		max_connections=200
 	}
 )
```
mysql_users: contains rows for the mysql_users table from the admin interface. Basically, these define the users which can connect to the proxy, and the users with which the proxy can connect to the backend servers. Rows are encoded as per the .cfg file format, here is an example:
```
 mysql_users:
 (
 	{
 		username = "root"
 		password = "root"
 		default_hostgroup = 0
 		max_connections=1000
 		default_schema="information_schema"
 		active = 1
 	}
 )
```
mysql_query_rules: contains rows for the mysql_query_rules table from the admin interface. Basically, these define the rules used to classify and route the incoming MySQL traffic, according to various criteria (patterns matched, user used to run the query, etc.). Rows are encoded as per the .cfg file format, here is an example (Note: the example is a very generic query routing rule and it is recommended to create specific rules for queries rather than using a generic rule such as this):
```
 mysql_query_rules:
 (
 	{
 		rule_id=1
 		active=1
 		match_pattern="^SELECT .* FOR UPDATE$"
 		destination_hostgroup=0
 		apply=1
 	},
 	{
 		rule_id=2
 		active=1
 		match_pattern="^SELECT"
 		destination_hostgroup=1
 		apply=1
 	}
 )
```
top-level configuration item: datadir, as a string, to point to the data dir.

proxysql's People

Contributors

Stargazers

Watchers

Forkers

rotron jesmarcannao aismail renecannao thienphung amdmi3 alexeydeyneko littleyang vermeire-mathias ethanblackburn bnprss wqx081 hand-code schatt is00hcw freediver9000 pczerkas ezhangle dtest martinarrieta oneiroi linsenhu guoguobo mnarusze frankie-666 vorodevops hafeez3000 anilyadav hrvojem mac119 tracer0tong tom2jack bytearchive voroonestic yejr fmbiete aydancoskun qiangchangjin dveeden xiejiaguang myoan sansom rameshvs02 rucky2013 ton31337 zorlem 59090939 skateroren grypyrg rishimittal tsuiwiki tangyi1989 klinometr uber-archive lyhiving dadohp awesome-security mmaja jamesblunt tobioboye bogdanr leeclemens neonecosystem dan83 ostico bitonp ddub xiaolushare minichate b-xiang dongzerun ninqing bobbyzhu rongfengliang ruoshan xubingyue notikumi canos junneyang pypwxh frlk zmyer daiguadaidai fnet123 xiaozhenliang freshbooks liqingrikeiikyeong wolferhua igit-cn legendzhu matthiaz mohsensy shubhamjosh hu19891110 haxine santianban devopsbox john-liuming ibuystuff tidyhuang

proxysql's Issues

Enhance Standard_MySQL_Authentication to support different username/password for backend/frontend

It should be possible to use different username/password pair for frontends and backends , and a table to map them

Compile on Mac

Generic parent ticket

Create functions to convert SQLite resultset into text resultset

It should be possible to connect to admin interface via a text interface

deprecated old mysql_protocol.cpp

File is unused, should be deprecated

Write a cleanup function for per-thread MySQL_Connection_Pool

Periodically, a cleanup function should be called in the per-thread MySQL_Connection_Pool instance to clean up all the expired connections.

To note that in this context "expired" means that have been in the per-thread instance for too long and that now they need to be moved into the shared instance

Create a global_variables in Admin module for global configuration

Configuration should be loaded from Admin.
This issue depends from issue #14

[GROUP] Add more debugging lines

Very generic issue, to track all commits that add debug information

Infinite loop if a client disconnect not gracefully

How to reproduce:
mysql -h 127.0.0.1 -P6032
killall -9 mysql

Remove memcached from ProxySQL . It will be added in the future

Remove embedded memcached
Develop it as sysown/memcached-lib
Reintroduce it in the future

Handle error 1044 and error 1049

This is related to #45 .
If a client issue INIT_DB this is always successful.
Although, when a client run a query and INIT_DB is sent to the database server that can answer with OK or ERR for:

1044 : Access denied
1049 : Unknown database
These errors must be returned to the application (probably just as error 1044) and server_userinfo.schemaname needs to be rolled back.

An alternative approach (that should be configurable) is to terminate the session as soon as such errors arise.

These errors can be logged: optionally, configurable

Create a per-thread variable that store current time

Many threads need to know the current time with a good precision, although calling time functions every time is needed is time consuming.
Each thread could have its own per-thread variable to store current time, updated within the thread when required, and accessible to all the functions.

Rename MyHostGroups to something more meaningful

Global object MyHostGroups (class MyHostGroups) has the same name of a private member of its own class. That can be easily confusing

[GROUP] Code cleanup

General issue to clean up the code

Create functions to flush users from GloMyAuth to admindb

To update the content of admindb based on the content of GloMyAuth

Rewrite class MySQL_Connection to not use any per thread memory allocator

Because of issue #27 , objects of MySQL_Connection should be sharable between threads

Configurable target for debug logging (DEBUG only)

Currently debug logging goes only to stderr.
This should be enhanced in a way that logging can go to either stderr (like now) or sent to an internal queue where debug entries can be:
a) written on a log file
b) sent to SQLite3
c) both a and b

Prioritize the execution of the Admin module

The Admin module should start before any other module.
Should be possible to hold the execution of other modules unless they are explicitly started from the admin interface.
Should be even possible to restart other modules without restarting the process.

Create functions to flush users from admindb to GloMyAuth

To update the content of GloMyAuth based on the content of admindb

Hide all the details of class MyConnArray

Almost all members from class MyConnArray should be accessible only from class MySQL_Connection_Pool , in order to hide implementation details

When compiled with g++ -O2 , Admin module doesn't work

The optimizer optimizes a lot of functions in Standard_MySQL_Thread, and therefore aren't available at runtime.

Too much time spent in array2buffer for resultset with a lot of small rows

array2buffer is called in a loop until all the packets are processed, and removed from PtrSizeArray one by one . This can be optimized processing packets in bulk

Define new expire variables for MySQL_Connection_Pool

Two variables need to define the time to lives of a connection:

in the per-thread connection pool before being moved into the shared connection pool
in the shared connection pool before being purged

These need to be defined globally and configurable at runtime.

deps/Makefile doesn't generate my_config.h

deps/Makefile specifies my_global.h as target of mariadb_client .
Should be my_config.h

Enhance Standard_MySQL_Authentication to support SSL

Implement handler for COM_INIT_DB

There is no handler for COM_INIT_DB

Implement or remove support for domains

See issue #9 for a description of what domains are.
Support for domains needs to be implemented in multiple modules.
Maybe is more appropriate to run multiple instances of ProxySQL (one per domain) instead of having one instance processing all domains

Map "SHOW CREATE TABLE" to the equivalent in SQLite3

Equivalent of SHOW CREATE TABLE in SQLite3 is:
SELECT name AS 'table' , sql AS 'Create Table' FROM sqlite_master WHERE type='table' AND name='%s'

Mac doesn't support clock_gettime()

clock_gettime() is used by monolitic_time(), but is not supported by Mac

Child of issue #42

create a table to define debugging levels

Debugging levels should be set from Admin module

Create a global variable that store current time

Many threads need to know the current time with a loose precision.
This can be implemented as a global variables update by a single thread: main() seems a good candidate.

improve performance of memory allocator using builtin GCC functions

Use FFS (http://en.wikipedia.org/wiki/Find_first_set) to improve performance on memory allocator, using GCC builtin functions __builtin_ffs and __builtin_clz . See https://gcc.gnu.org/onlinedocs/gcc/Other-Builtins.html#Other-Builtins

Create rules to completely disable/enable the query cache as session layer

In query rules we need an option to disable the query caching as session layer and re-enable it.
This case priority over any query caching rules at query level.
Considering the small overhead in per-thread QC, maybe this could be a flag only for shared QC

Trace session variables

Most of the session variables need to be traced, so if a client specifically set them actions can be taken.
Ex:

start transaction;
change timeout;
disable qc
change charset

Would be more interesting if all the variables are kept local

sysown / proxysql Goto Github PK

proxysql's Introduction

Introduction

Useful links

Getting started

Installation

Ubuntu / Debian:

Red Hat / CentOS:

Amazon Linux:

Almalinux:

OpenSUSE:

Service management

Starting ProxySQL:

Stopping ProxySQL:

Restarting ProxySQL:

Reinitializing ProxySQL from the config file (after first startup the DB file is used instead of the config file):

Upgrades

How to check the ProxySQL version

Configuring ProxySQL via the admin interface

Configuring ProxySQL through the admin interface

Configuring ProxySQL through the config file

proxysql's People

Contributors

Stargazers

Watchers

Forkers

proxysql's Issues

Recommend Projects

Recommend Topics

Recommend Org

Configuring ProxySQL via the `admin interface`