ProxySQL

https://proxysql.com/documentation
Admin
ProxySQL is a high-performance, high-availability, protocol-aware proxy for MySQL and its forks,...
Tokens:196,893
Snippets:2,545
Trust Score:10
Update:2 months ago
Show doc for...
Tokens:
Raw
### Start ProxySQL from Source (Foreground)

Source: https://proxysql.com/documentation/step-by-step-installation-guide

Starts ProxySQL in the foreground for testing purposes after installation from source.

```bash
proxysql -f -c /etc/proxysql.cnf
```

--------------------------------

### Start ProxySQL from Source (Background)

Source: https://proxysql.com/documentation/step-by-step-installation-guide

Starts ProxySQL as a background service after installation from source.

```bash
proxysql -c /etc/proxysql.cnf
```

--------------------------------

### Start ProxySQL Service

Source: https://proxysql.com/documentation/step-by-step-installation-guide

Starts the ProxySQL service using systemd or SysVinit.

```bash
# systemd
sudo systemctl start proxysql

# SysVinit
sudo service proxysql start
```

--------------------------------

### Datadir Configuration Example

Source: https://proxysql.com/documentation/getting-started

Example configuration for the data directory in proxysql.cnf.

```ini
datadir="/var/lib/proxysql"
```

--------------------------------

### MySQL Servers Configuration Example

Source: https://proxysql.com/documentation/getting-started

Example configuration for MySQL servers in proxysql.cnf.

```ini
mysql_servers=(
  {
    address="127.0.0.1"
    port=3306
    hostgroup=0
    max_connections=200
  }
)
```

--------------------------------

### Compile and Install ProxySQL from Source

Source: https://proxysql.com/documentation/step-by-step-installation-guide

Compiles and installs ProxySQL after cloning the repository and installing dependencies.

```bash
make
sudo make install
```

--------------------------------

### MySQL Users Configuration Example

Source: https://proxysql.com/documentation/getting-started

Example configuration for MySQL users in proxysql.cnf.

```ini
mysql_users=(
  {
    username="root"
    password="root"
    default_hostgroup=0
    max_connections=1000
    default_schema="information_schema"
    active=1
  }
)
```

--------------------------------

### ProxySQL Version Output

Source: https://proxysql.com/documentation/getting-started

Example output when checking the ProxySQL version.

```text
ProxySQL version v3.0.5, codename Truls
```

--------------------------------

### Start ProxySQL with Initial Configuration

Source: https://proxysql.com/documentation/frequently-asked-questions

Example of starting ProxySQL using the `--initial` flag with a configuration file.

```bash
proxysql --initial -f -c /etc/proxysql.cnf
```

--------------------------------

### Admin Variables Configuration Example

Source: https://proxysql.com/documentation/getting-started

Example configuration for admin variables in proxysql.cnf.

```ini
admin_variables=
{
  admin_credentials="admin:admin"
  mysql_ifaces="127.0.0.1:6032"
  pgsql_ifaces="127.0.0.1:6132"
}
```

--------------------------------

### MySQL Variables Configuration Example

Source: https://proxysql.com/documentation/getting-started

Example configuration for MySQL variables in proxysql.cnf.

```ini
mysql_variables=
{
  threads=4
  max_connections=2048
  interfaces="0.0.0.0:6033"
}
```

--------------------------------

### Start ProxySQL Service

Source: https://proxysql.com/documentation/getting-started

Starts the ProxySQL service using the service command.

```bash
service proxysql start
```

--------------------------------

### MySQL Query Rules Configuration Example

Source: https://proxysql.com/documentation/getting-started

Example configuration for MySQL query rules in proxysql.cnf.

```ini
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
  }
)
```

--------------------------------

### Debug Build ProxySQL Version

Source: https://proxysql.com/documentation/getting-started

Example output for a debug build of ProxySQL.

```text
ProxySQL version v3.0.5_DEBUG, codename Truls
```

--------------------------------

### Check ProxySQL Version

Source: https://proxysql.com/documentation/getting-started

Checks the installed ProxySQL version.

```bash
proxysql --version
```

--------------------------------

### MySQL Binlog Reader Startup Example

Source: https://proxysql.com/documentation/mysql-binlog-reader

Example command to start the MySQL Binlog Reader with specified host, user, password, port, listen port, and log file.

```bash
proxysql_binlog_reader -h 127.0.0.1 -u root -proot -P 3306 -l 3307 -L /var/log/proxysql_binlog_reader.log
```

--------------------------------

### Clone ProxySQL Repository

Source: https://proxysql.com/documentation/step-by-step-installation-guide

Clones the ProxySQL source code repository from GitHub.

```bash
git clone https://github.com/sysown/proxysql.git
cd proxysql
```

--------------------------------

### Verify ProxySQL Installation

Source: https://proxysql.com/documentation/step-by-step-installation-guide

Checks the status of the ProxySQL service and its version.

```bash
sudo systemctl status proxysql
proxysql --version
```

--------------------------------

### Install ProxySQL on Debian/Ubuntu

Source: https://proxysql.com/documentation/step-by-step-installation-guide

Installs ProxySQL using the apt package manager on Debian or Ubuntu systems.

```bash
sudo apt update
sudo apt install proxysql
```

--------------------------------

### Show Available Databases in Admin Interface

Source: https://proxysql.com/documentation/getting-started

Displays the available databases within the ProxySQL Admin interface.

```sql
Admin> SHOW DATABASES;
```

--------------------------------

### Install Build Dependencies on Debian/Ubuntu

Source: https://proxysql.com/documentation/step-by-step-installation-guide

Installs the necessary build dependencies for compiling ProxySQL from source on Debian/Ubuntu systems.

```bash
sudo apt-get install cmake libssl-dev libboost-dev \
  libboost-program-options-dev libboost-filesystem-dev \
  libboost-thread-dev libboost-regex-dev libboost-system-dev \
  libmysqlclient-dev
```

--------------------------------

### Configuration Example

Source: https://proxysql.com/documentation/clickhouse-configuration

An example of how to insert user credentials, load them to runtime, and save them to disk.

```SQL
INSERT INTO clickhouse_users VALUES ('clicku','clickp',1,100);
LOAD CLICKHOUSE USERS TO RUNTIME;
SAVE CLICKHOUSE USERS TO DISK;

```

--------------------------------

### Install ProxySQL on Almalinux

Source: https://proxysql.com/documentation/installing-proxysql

Configure the repository for Almalinux and install via yum.

```bash
cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name=ProxySQL repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-3.0.x/almalinux/$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-3.0.x/repo_pub_key
EOF

yum install proxysql
```

--------------------------------

### Example: CentOS 7 Build

Source: https://proxysql.com/documentation/packaging

Example command to build packages for CentOS 7.

```bash
make centos7
```

--------------------------------

### Example: Debug Build

Source: https://proxysql.com/documentation/packaging

Example command to build a debug version for CentOS 7.

```bash
make centos7-dbg
```

--------------------------------

### Install ProxySQL on RedHat/CentOS

Source: https://proxysql.com/documentation/installing-proxysql

Create the repository configuration file and install ProxySQL using yum.

```bash
cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name=ProxySQL repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-3.0.x/centos/$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-3.0.x/repo_pub_key
EOF

yum install proxysql
```

--------------------------------

### Install ProxySQL on OpenSUSE Leap

Source: https://proxysql.com/documentation/installing-proxysql

Add the ProxySQL repository and install the package using zypper.

```bash
zypper addrepo -g -n 'ProxySQL repository' \
  'https://repo.proxysql.com/ProxySQL/proxysql-3.0.x/opensuse/$releasever_major' proxysql

zypper --gpg-auto-import-keys install proxysql
```

--------------------------------

### Connect to Admin Interface (PostgreSQL Protocol)

Source: https://proxysql.com/documentation/getting-started

Connects to the ProxySQL Admin interface using the PostgreSQL protocol.

```bash
psql -U admin -h 127.0.0.1 -p6132 -d admin --prompt='Admin> '
```

--------------------------------

### Access ProxySQL Admin Interface

Source: https://proxysql.com/documentation/step-by-step-installation-guide

Connects to the ProxySQL admin interface using a MySQL client.

```bash
mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='ProxySQLAdmin> '
```

--------------------------------

### Activate REST API Example

Source: https://proxysql.com/documentation/rest-api

Command to load REST API configuration to runtime.

```bash
LOAD RESTAPI TO RUNTIME;
```

--------------------------------

### Install ProxySQL on CentOS/RHEL

Source: https://proxysql.com/documentation/step-by-step-installation-guide

Installs ProxySQL using the yum package manager on CentOS or RHEL systems.

```bash
sudo yum install epel-release
sudo yum install proxysql
```

--------------------------------

### Example mysql_server_read_only_log query

Source: https://proxysql.com/documentation/the-admin-schemas/monitor-schema

An example query to retrieve the last 5 entries from the mysql_server_read_only_log table, ordered by the start time of the check.

```sql
Admin> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 5;
+-----------+------+------------------+----------------+-----------+-------+
| hostname  | port | time_start_us    | success_time_us | read_only | error |
+-----------+------+------------------+----------------+-----------+-------+
| 10.0.0.1  | 3306 | 1711027320000000 | 512            | 0         | NULL  |
| 10.0.0.2  | 3306 | 1711027319999000 | 498            | 1         | NULL  |
| 10.0.0.3  | 3306 | 1711027319998000 | 531            | 1         | NULL  |
| 10.0.0.1  | 3306 | 1711027315000000 | 489            | 0         | NULL  |
| 10.0.0.2  | 3306 | 1711027314999500 | 507            | 1         | NULL  |
+-----------+------+------------------+----------------+-----------+-------+
5 rows in set (0.00 sec)

```

--------------------------------

### Example mysql_server_ping_log query

Source: https://proxysql.com/documentation/the-admin-schemas/monitor-schema

An example query to retrieve the last 5 entries from the mysql_server_ping_log table, ordered by the start time of the ping.

```sql
Admin> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 5;
+-----------+------+------------------+----------------------+------------+
| hostname  | port | time_start_us    | ping_success_time_us | ping_error |
+-----------+------+------------------+----------------------+------------+
| 10.0.0.1  | 3306 | 1711027260001000 | 312                  | NULL       |
| 10.0.0.2  | 3306 | 1711027260000500 | 298                  | NULL       |
| 10.0.0.3  | 3306 | 1711027259998000 | 0                    | timeout    |
| 10.0.0.1  | 3306 | 1711027255000000 | 301                  | NULL       |
| 10.0.0.2  | 3306 | 1711027254999500 | 315                  | NULL       |
+-----------+------+------------------+----------------------+------------+
5 rows in set (0.00 sec)

```

--------------------------------

### Enabling and Verifying FFTO

Source: https://proxysql.com/documentation/ffto

This example demonstrates how to check if FFTO is enabled, configure a user for fast forward, and then query the stats tables to view metrics from fast forward sessions.

```sql
-- Check FFTO is enabled (default)
SELECT * FROM global_variables WHERE variable_name LIKE '%ffto%';

-- Configure a user for fast forward
UPDATE mysql_users SET fast_forward=1 WHERE username='app_user';
LOAD MYSQL USERS TO RUNTIME;

-- After some traffic, check query digests from fast forward sessions
SELECT hostgroup, digest_text, count_star, sum_time
FROM stats_mysql_query_digest
ORDER BY sum_time DESC LIMIT 10;
```

--------------------------------

### REST API /api/tsdb/metrics Example Response

Source: https://proxysql.com/documentation/monitoring-stats/tsdb

Example JSON response for the GET /api/tsdb/metrics endpoint, listing available metric names.

```json
{
  "metrics": [
    "proxysql_client_connections_connected",
    "proxysql_client_connections_created",
    "proxysql_questions"
  ]
}
```

--------------------------------

### TSDB Query API Example Request

Source: https://proxysql.com/documentation/monitoring-stats/tsdb

Example of a GET request to the TSDB query API to retrieve metric data within a specified time range.

```http
GET /api/tsdb/query?metric=proxysql_client_connections_connected&from=1743000000&to=1743003600
```

--------------------------------

### Restart ProxySQL Service

Source: https://proxysql.com/documentation/getting-started

Restarts the ProxySQL service using the service command.

```bash
service proxysql restart
```

--------------------------------

### Stop ProxySQL Service

Source: https://proxysql.com/documentation/getting-started

Stops the ProxySQL service using the service command.

```bash
service proxysql stop
```

--------------------------------

### ProxySQL Startup Options

Source: https://proxysql.com/documentation/startup-options

A list of command-line options that can be used when starting ProxySQL to control its behavior and configuration.

```bash
-c, --config ARG: Start ProxySQL with passing the location of the configuration file
-D, --datadir ARG: Start ProxySQL with passing the location of the data directory
-e, --exit-on-error: ProxySQL restarts in the event of a crash by default. Since `v2.6.2` this restart procedure has an exponential backoff. Supplying this option disables this mechanism.
-f, --foreground: ProxySQL will be running in the foreground, without starting the daemon (without forking), writing error messages to `stderr`.
-h, -help, --help, --usage: Invoking any of these will display usage options
-M, --no-monitor: ProxySQL will not start the Monitor Module
-n, --no-start: ProxySQL will only start the Admin service
-r, --reuseport: ProxySQL will use `SO_REUSEPORT`
-S, --admin-socket ARG: Start ProxySQL with passing the location of the Administration Unix Socket
-U, --uuid ARG: Start ProxySQL with the passing of the specific UUID
-V, --version: Displays the version of ProxySQL
--clickhouse-server: Enable Clickhouse Server
--idle-threads: Create auxiliary threads to handle idle connections
--initial: Rename/empty the database file
--no-version-check: Do not check for the latest version of ProxySQL
--reload: Merge the configuration file into the database file
--sqlite3-server: Enable the built-in SQLite3 Server
```

--------------------------------

### Restart ProxySQL via Admin Interface

Source: https://proxysql.com/documentation/getting-started

Restarts ProxySQL using the Admin interface.

```sql
Admin> proxysql restart
```

--------------------------------

### Query Rewriting Rule Example

Source: https://proxysql.com/documentation/proxysql-configuration

Example of how to insert a query rewriting rule using match_pattern and replace_pattern.

```SQL
INSERT INTO mysql_query_rules
  (rule_id,active,username,match_pattern,replace_pattern,apply)
  VALUES (30,1,'stnduser','DISTINCT(.*)ORDER BY c','DISTINCT\1',1);
LOAD MYSQL QUERY RULES TO RUNTIME;
```

--------------------------------

### Stop ProxySQL via Admin Interface

Source: https://proxysql.com/documentation/getting-started

Stops ProxySQL using the Admin SQL interface.

```sql
Admin> proxysql stop
```

--------------------------------

### Configuration - Prepare Catalog

Source: https://proxysql.com/documentation/genai/nl2sql

The metadata catalog needs to be prepared by running `discovery.run_static`.

```text
2. **Prepare Catalog** : Run `discovery.run_static` via the MCP server to harvest metadata.
```

--------------------------------

### Example: CentOS 7 with ClickHouse Build

Source: https://proxysql.com/documentation/packaging

Example command to build packages for CentOS 7 with ClickHouse support.

```bash
make centos7-clickhouse
```

--------------------------------

### Connect to Admin Interface (MySQL Protocol)

Source: https://proxysql.com/documentation/getting-started

Connects to the ProxySQL Admin interface using the MySQL protocol.

```bash
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
```

--------------------------------

### Adding Servers

Source: https://proxysql.com/documentation/backend-server-configuration

Example of inserting a new server into the mysql_servers table and applying the changes.

```sql
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '10.0.0.1', 3306);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
```

--------------------------------

### Loading Servers to Runtime

Source: https://proxysql.com/documentation/proxysql-configuration

Load MySQL servers to runtime.

```sql
LOAD MYSQL SERVERS TO RUNTIME;
```

--------------------------------

### Reinitialize ProxySQL from Config File (systemd)

Source: https://proxysql.com/documentation/getting-started

Resets ProxySQL to use the configuration file instead of the SQLite database, using systemd.

```bash
# systemd
systemctl start proxysql-initial
```
Tokens:
Raw