### 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 ``` -------------------------------- ### Install ProxySQL from Repository (OpenSUSE Leap) Source: https://proxysql.com/documentation/installing-proxysql Add the ProxySQL repository and install ProxySQL 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 ``` -------------------------------- ### 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 ``` -------------------------------- ### 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> ' ``` -------------------------------- ### Install ProxySQL from Repository (Almalinux) Source: https://proxysql.com/documentation/installing-proxysql Configure the ProxySQL repository and install ProxySQL using yum. ```bash cat < 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) ``` -------------------------------- ### 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" ] } ``` -------------------------------- ### 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; ``` -------------------------------- ### 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 ``` -------------------------------- ### Basic proxysql.cnf for Docker Source: https://proxysql.com/documentation/installing-proxysql A sample configuration file for ProxySQL when running with Docker, suitable for development and testing. ```ini datadir="/var/lib/proxysql" admin_variables= { admin_credentials="admin:admin;radmin:radmin" mysql_ifaces="0.0.0.0:6032" } mysql_variables= { threads=4 max_connections=2048 default_query_delay=0 default_query_timeout=36000000 have_compress=true poll_timeout=2000 interfaces="0.0.0.0:6033" default_schema="information_schema" stacksize=1048576 server_version="5.5.30" connect_timeout_server=3000 monitor_username="monitor" monitor_password="monitor" monitor_history=600000 monitor_connect_interval=60000 monitor_ping_interval=10000 monitor_read_only_interval=1500 monitor_read_only_timeout=500 ping_interval_server_msec=120000 ping_timeout_server=500 commands_stats=true sessions_sort=true connect_retries_on_failure=10 } ``` -------------------------------- ### 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 ``` -------------------------------- ### 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> ' ``` -------------------------------- ### 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. ``` -------------------------------- ### 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; ```