ProxySQL 2 configuration/installation

ProxySQL Installation/Setup - Percona XtraDB Cluster Part 2

ProxySQL, is an open-source high-performance MySQL/MariaDB proxy server, it serves as an intermediary between MySQL database nodes and the applications.

ProxySQL, a SQL-compatible proxy server, can improve performance by routing traffic among a pool of multiple MySQL/MariaDB/Percona Server database (cluster) nodes and improve availability by automatically failing over to standby if one (or more) of the database node fails.

ProxySQL can be used for

  ✓ Load balancing

  ✓ Query caching

  ✓ Query routing/redirection

  ✓ Query rewriting

  ✓ Database firewall

  ✓ Sharding

  ✓   Data masking

  ✓   Zero-downtime changes

In an earlier article, I've written Percona XtraDB Cluster (PXC) 8.0 Installation/Configuration steps. This article describes how to configure the basic components of ProxySQL.

Part 3 of the article - Percona Toolkit Usage and Installation

Part 4 of the article - Percona Xtrabackup Backup & Recovery

Part 5 of the article - Percona Monitoring and Management Installation

Step I. Installing ProxySQL

Most of the time, ProxySQL is configured on a separate server, and all the connections are routed from the application to the ProxySQL server. ProxySQL in turn directs all the connections to the database server depending on the query rules configured.

ProxySQL 2 is the latest major release of ProxySQL and it is recommended for new installations.

To install ProxySQL on RPM-based distributions, run:

# yum list all | grep "proxysql"

# yum install proxysql2

# yum list all | grep percona

# yum install percona-xtradb-cluster-client

# cat /etc/proxysql.cnf

# proxysql --version

The daemon accepts incoming traffic from MySQL clients and forwards it to backend MySQL servers.

# service proxysql start

# service proxysql stop

# service proxysql status

# tail -100f /var/lib/proxysql/proxysql.log

Step II. Setting the ProxySQL Administrator Password

ProxySQL’s internals can be reconfigured using the standard SQL ProxySQL Admin interface, accessible via MySQL command-line utility available by default on port 6032.

Configuration is stored in an SQLite database.

Connect to the ProxySQL administration interface with the default password 'admin'

# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='ProxySQLAdmin> '

If you want to change ProxySQL administration password,

ProxySQLAdmin> UPDATE global_variables SET variable_value='admin:password' WHERE variable_name='admin-admin_credentials';

ProxySQLAdmin> LOAD ADMIN VARIABLES TO RUNTIME;     # to copy the memory settings to the runtime

ProxySQLAdmin> SAVE ADMIN VARIABLES TO DISK;        #  save variables to disk to make them persist

Verify that the configuration is empty by checking that there are no entries in the mysql_servers, my_users, mysql_replication_hostgroups and mysql_query_rules tables.

ProxySQLAdmin> SELECT * FROM mysql_servers;

ProxySQLAdmin> SELECT * FROM mysql_users;

ProxySQL has native support for Galera Cluster and Group Replication.

ProxySQLAdmin> SELECT * from mysql_replication_hostgroups;

Query rules are very useful to control traffic passing through ProxySQL and are configured in the mysql_query_rules table.

ProxySQLAdmin> SELECT * from mysql_query_rules;

Step III. Add backend database nodes to the ProxySQL Server Pool

3 Percona XtraDB Cluster (PXC)/MySQL servers will be configured by adding them to the mysql_servers table.

ProxySQLAdmin> INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('192.168.1.4',10,3306,1000);

ProxySQLAdmin> INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('192.168.1.5',10,3306,1000);

ProxySQLAdmin> INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('192.168.1.6',10,3306,1000);

ProxySQLAdmin> INSERT INTO mysql_galera_hostgroups (writer_hostgroup, backup_writer_hostgroup, reader_hostgroup, offline_hostgroup, active, max_writers, writer_is_also_reader,max_transactions_behind) VALUES (10, 12, 11, 13, 1, 1, 2, 100);

ProxySQLAdmin> LOAD MYSQL SERVERS TO RUNTIME;    # Activate current in-memory MySQL server and replication hostgroup configuration

ProxySQLAdmin> SAVE MYSQL SERVERS TO DISK;       # Save the current in-memory MySQL server and replication hostgroup configuration to disk

Modifying the config at runtime is done through the ProxySQL Admin port of ProxySQL.

Changes will NOT be activated until they are loaded to RUNTIME and any changes which are NOT saved to DISK will NOT be available after a ProxySQL restart.

ProxySQL considers backend instances with a read_only = 0 as WRITER instances so this should only be set on primary MySQL servers or all primaries in the case of Percona XtraDB Cluster (PXC)/Group (multi-primary) replication. The backend MySQL servers have read_only = 1 configured on all replicas.

ProxySQLAdmin> SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;

Step IV. MySQL Users

After configuring the MySQL server backends in mysql_servers the next step is to configure mysql users,  add users to specify the username, password and default_hostgroup for basic configuration.

ProxySQLAdmin> INSERT INTO mysql_users (username,password,default_hostgroup) VALUES ('sbuser','sbpass',10);

ProxySQLAdmin> LOAD MYSQL USERS TO RUNTIME;      # Activate current in-memory MySQL user configuration

ProxySQLAdmin> SAVE MYSQL USERS TO DISK;         # Save the current in-memory MySQL user configuration to disk

ProxySQL Admin (proxysql-admin) is a powerful tool for configuring Percona XtraDB Cluster nodes into ProxySQL. ProxySQL v2 natively supports Percona XtraDB Cluster.

The proxysql-admin tool comes with the ProxySQL package from Percona apt/yum repositories.

Step V. Configure monitoring in MySQL

ProxySQL constantly monitors the MySQL backend nodes configured to identify the health status.

The credentials for monitoring the backend database servers need to be created in MySQL and also configured in ProxySQL along with the environment-specific check intervals.

ProxySQL runs as a daemon watched by a monitoring process, restarts it in case of a crash to minimize downtime.

To create the user in MySQL connect to the PRIMARY and execute:

ProxySQLAdmin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name in ('mysql-monitor_username','mysql-monitor_password');

To allow ProxySQL access to the MySQL database, we need to create a user on MySQL database with the same credentials mentioned on the ProxySQL server.

On Percona XtraDB Cluster (PXC) nodes, create monitoring user, grant privileges,

mysql> CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';

mysql> GRANT USAGE ON *.* TO 'monitor'@'%';

mysql> select user, host, super_priv, password_expired, plugin from mysql.user;

On Percona XtraDB Cluster (PXC) nodes or MySQL cluster servers, create ProxySQL client user

mysql> CREATE DATABASE sbtest;

mysql> CREATE USER 'sbuser'@'192.168.1.7' IDENTIFIED WITH mysql_native_password BY 'sbpass';

mysql> GRANT ALL ON *.* TO 'sbuser'@'192.168.1.7';

Step VI. Configuring Monitoring in ProxySQL

ProxySQLAdmin> UPDATE GLOBAL_VARIABLES SET variable_value='8.0' WHERE variable_name='mysql-server_version';

ProxySQLAdmin> UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');

ProxySQLAdmin> UPDATE GLOBAL_VARIABLES SET variable_value='true' WHERE variable_name='admin-web_enabled';

ProxySQLAdmin> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';

Changes made to the MySQL Monitor in table global_variables will be applied after executing the LOAD MYSQL VARIABLES TO RUNTIME statement.

To persist the configuration changes across restarts the SAVE MYSQL VARIABLES TO DISK must also be executed.

ProxySQLAdmin> LOAD MYSQL VARIABLES TO RUNTIME;    # Activate current in-memory MySQL variable configuration

ProxySQLAdmin> SAVE MYSQL VARIABLES TO DISK;       # Save the current in-memory MySQL variable configuration to disk

Step VII. Backend’s health check

Once the configuration is active verify the status of the MySQL backends in the monitor database tables in ProxySQL Admin:

ProxySQLAdmin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC limit 10;

ProxySQLAdmin> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC limit 10;

ProxySQL is ready to serve traffic on port 6033 (by default), the reverse of MySQL default port 3306.

# mysql -usbuser -psbpass -h 127.0.0.1 -P6033 --prompt='ProxySQLClient> '

# mysql -usbuser -psbpass -h 127.0.0.1 -P6033 --prompt='ProxySQLClient> ' -e 'select @@hostname,@@port';

If needed, allow ProxySQL service port 6033/tcp in Linux Firewall.

# firewall-cmd --permanent --add-port=6033/tcp

# firewall-cmd --reload

Step VIII. Functional tests / Testing Cluster with sysbench

Sysbench is a useful tool to verify that ProxySQL is functional and benchmark system performance.

On ProxySQL node:

# yum list all | grep sysbench

# yum install sysbench -y

On ProxySQL node:

# mysql -umonitor -pmonitor -h192.168.1.4 -P3306 -e"SELECT @@hostname,@@port"

# mysql -usbuser -psbpass -h192.168.1.5 -P3306 -e"SELECT @@hostname,@@port"

# sysbench /usr/share/sysbench/oltp_insert.lua --mysql-db=sbtest --mysql-host=127.0.0.1 --mysql-port=6033 --mysql-user='sbuser' --mysql-password='sbpass' --db-driver=mysql --threads=4 --tables=20 --table-size=15 prepare

# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-db=sbtest --mysql-host=127.0.0.1 --mysql-port=6033 --mysql-user='sbuser' --mysql-password='sbpass' --db-driver=mysql --threads=4 --tables=20 --table-size=15 --time=200 --report-interval=20 run

# sysbench /usr/share/sysbench/oltp_point_select.lua --mysql-db=sbtest --mysql-host=127.0.0.1 --mysql-port=6033 --mysql-user='sbuser' --mysql-password='sbpass' --db-driver=mysql --threads=4 --tables=20 --table-size=15 --time=200 --report-interval=20 run

# sysbench /usr/share/sysbench/oltp_update_index.lua --mysql-db=sbtest --mysql-host=127.0.0.1 --mysql-port=6033 --mysql-user='sbuser' --mysql-password='sbpass' --db-driver=mysql --threads=4 --tables=20 --table-size=15 --time=200 --report-interval=20 run

Step IX. Verifying the ProxySQL Configuration/Automatic failover

From the command line of one of the MySQL servers, stop the MySQL process to simulate a failure.

# systemctl stop mysql

We can check that by querying the runtime_mysql_servers table from the ProxySQL administration prompt.

ProxySQLAdmin> SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;

The node we stopped is now shunned, which means it’s temporarily deemed inaccessible, so all traffic will be distributed across the two remaining online nodes.

Switch back to the MySQL server and bring the node back up.

# systemctl start mysql

Wait a moment, then query the runtime_mysql_servers table from the ProxySQL administration prompt again.

ProxySQLAdmin> SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;

Step X. Query statistics

ProxySQL collects a lot of real time statistics in the stats schema, each table provides specific information about the behavior of ProxySQL and the workload being processed:

ProxySQLAdmin> SELECT * FROM stats_mysql_global;

ProxySQLAdmin> SELECT * FROM stats_mysql_connection_pool order by hostgroup,srv_host ;

ProxySQLAdmin> SELECT * FROM stats_mysql_processlist;

ProxySQLAdmin> SELECT * FROM stats_mysql_query_digest;

ProxySQLAdmin> SELECT * FROM stats_mysql_commands_counters ORDER BY Total_cnt DESC;

ProxySQL is a high performance, high availability, protocol aware proxy for MySQL database clusters and its forks like Percona Server and MariaDB.

ProxySQL supports thousands of concurrent connections.

Related MySQL Articles: PXC 8 (Percona XtraDB Cluster) Configuration/Installation  How to use tools in Percona Toolkit