Introduction
As its name suggests, ProxySQL is a SQL compatible proxy server that can be placed between your application and its database.
By configuring MySQL caching, the result of a query is stored so that, when the query is repeated, the result can be returned without having to reorder the database. This system allows you to significantly increase the speed of the most common queries.
To enable the cache, developers generally have to modify the code of their application, with the risk of bugs being created in the source code. To avoid any problem, ProxySQL allows you to set the cache as transparent.
In this tutorial, you will see how to use the ProxySQL tool to configure transparent cache through a MySQL server on Ubuntu 20.04.
First, connect to your server via SSH. If you haven't done so yet, following our guide is recommended to connect securely with the SSH protocol . In case of a local server, go to the next step and open the terminal of your server.
Installing MySQL Server
First, install the MySQL server and configure it so that it can be used via ProxySQL:
sudo apt-get update
sudo apt-get install mysql-server -y
Once the installation is completed, use the following command to create a password as the system administrator user:
mysql_secure_installation
Now that your MySQL server is ready to use, add a monitoring user for ProxySQL. This user will be used by ProxySQL to obtain information about the MySQL server and determine its operational status.
First, log in to MySQL using the root user (system administrator) and the previously created password:
mysql -u root -p
Now create two users, one named -psql_mon- for ProxySQL and another to query clients and grant them the appropriate privileges:
mysql > CREATE USER 'psql_mon'@'%' IDENTIFIED BY 'psql_mon_password';
Through this command, a new user called "psql_mon", associated with the password "psql_mon_password" is being created (remember to change it to ensure an adequate level of security).
Then, continue using the GRANT command to grant the necessary privileges to ProxySQL:
mysql > GRANT SELECT ON sys.* TO 'psql_mon'@'%';
Once it is completed, proceed by creating the user necessary for ProxySQL to access the reference database:
mysql > CREATE USER 'test_user'@'%' IDENTIFIED BY 'test_user_password';
mysql > GRANT ALL PRIVILEGES on *.* TO 'test_user'@'%';
Complete the configuration of the users using the command:
mysql > FLUSH PRIVILEGES;
Finally, exit the MySQL shell:
mysql > exit;
Mysql-server has now been installed and a user for ProxySQL to monitor your MySQL server and another user to run client queries have also been created. Then, install and configure ProxySQL.
Installing and configuring ProxySQL
At this point, proceed with the installation of the ProxySQL server, which will be used as a cache for your queries. The service will be inserted between the queries made by the clients and the real MySQL server, allowing you to obtain results faster.
The official Github page will allow you to download the necessary installation files using the wget command.
wget https://github.com/sysown/proxysql/releases/download/v2.0.4/proxysql_2.0.4-ubuntu16_amd64.deb
Next, install the package using dpkg:
sudo dpkg -i proxysql_2.0.4-ubuntu16_amd64.deb
Once installed, start ProxySQL with this command:
sudo systemctl start proxysql
To configure the ProxySQL service, use its SQL administration interface, which, by default, has the username and password "admin" and listens on port 6032.
mysql -uadmin -p -h 127.0.0.1 -P6032
When prompted, use the password "admin" to log in and proceed by configuring the monitoring user previously created:
mysql > UPDATE global_variables SET variable_value='psql_mon' WHERE variable_name='mysql-monitor_username';
By Using the previous command, the ‘mysql-monitor_username’ variable used by ProxySQL is being modified to check the status of the MySQL server.
However, with the following command the password to be used can be set (remember to change it with the one entered in the previous step):
mysql > UPDATE global_variables SET variable_value='psql_mon_password' WHERE variable_name='mysql-monitor_password';
When creating a change in the ProxySQL admin center, use the correct LOAD command so that the changes can be applied to the running ProxySQL instance. The MySQL global variables have been changed, so upload them to RUNTIME to apply the changes:
mysql > LOAD MYSQL VARIABLES TO RUNTIME;
Then, SAVE the database changes to the physical disk to make the changes between reboots persist. ProxySQL uses a local database of its own relevance, SQLite, so that it can store its own tables and variables:
mysql > SAVE MYSQL VARIABLES TO DISK;
At this point change the settings for the MySQL servers managed by ProxySQL. The "mysql_servers" table contains information about each managed server. So, go edit it using the commands:
mysql >INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 3306);
Now, tell ProxySQL which user will connect to the MySQL server. Using the "mysql_users" table, which contains information about the users used, set the username and password created earlier as follows (remember to change the password if necessary):
INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('test_user', 'test_user_password', 1);
To apply all the changes, execute the LOAD and SAVE commands:
mysql > LOAD MYSQL SERVERS TO RUNTIME;
mysql > SAVE MYSQL SERVERS TO DISK;
You can then exit the management console:
mysql > exit;
Verifying the correct functioning of ProxySQL installation
At this point, proceed by verifying that the connection with ProxySQL works correctly.
In this case, create an example database and data into it. Then, proceed by connecting to the database through the command:
mysql -utest_user -hlocalhost -p -P6033
Enter the "test_user" password when prompted and continue creating the database:
mysql> CREATE DATABASE test;
mysql> USE test;
Use the following syntax to create a table and insert a row into it:
mysql> CREATE TABLE test_table ( field1 VARCHAR( 30 ) );
mysql> INSERT INTO test_table VALUES ( 'test_value' );
At this point, using the SELECT command, check that MySQL returns the line you just entered:
mysql> SELECT * FROM test_table;
+------------+
| field1 |
+------------+
| test_value |
+------------+
1 row in set (0.00 sec)
If the ProxySQL installation works correctly, the result shown above will appear.
To delete the traces of the test you have just performed, use the DROP commands:
mysql> DROP TABLE test_table;
mysql> DROP DATABASE test;