The Various Methods to Backup and Restore ProxySQL

January 3, 2024

Author

Balchandar Reddy Voodem

Insight for DBAs

MySQL

ProxySQL

Share this Post:

ProxySQL is a high-performance SQL proxy that runs as a daemon watched by a monitoring process. The process monitors the daemon and restarts it in case of a crash to minimize downtime.

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

The proxy is designed to run continuously without needing to be restarted. Most configurations can be done at runtime using queries similar to SQL statements in the ProxySQL admin interface. These include runtime parameters, server grouping, and traffic-related settings.

In this blog, we are discussing various methods to backup and restore ProxySQL. Below are backup methods to perform a backup restore of the ProxySQL server.

Config file backup

Mysqldump

Physical snapshot

Here will consider ProxySQL configured for async replication with Primary and Replica as shown below in topology.

ProxySQLAdmin> select * from runtime_mysql_servers;<br>+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+<br>| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |<br>+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+<br>| 1            | 192.0.0.1     | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 600                 | 0       | 0              |         |<br>| 2            |192.0.0.2      | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 600                 | 0       | 0              |         |<br>+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+<br>2 rows in set (0.002 sec)

ProxySQLAdmin> select * from runtime_mysql_servers; +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 192.0.0.1 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 600 | 0 | 0 | | | 2 |192.0.0.2 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 600 | 0 | 0 | | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2 rows in set (0.002 sec)

Config file backup:

ProxySQL backup can be performed by the config file. This method is beneficial in creating multiple instances.

Config backup file can be created by using the below commands and it can be used to start new ProxySQL instances.

ProxySQLAdmin> select config into outfile /var/lib/proxysql/proxybkp.cnf;<br>Query OK, 9920 rows affected (0.00 sec)<br>File /var/lib/proxysql/proxybkp.cnf is saved.<br><br>proxysql $ sudo cat /var/lib/proxysql/proxybkp.cnf |grep -wi mysql_servers -A 35<br>mysql_servers:<br>(<br>	{<br>		hostgroup_id=1<br>		hostname="192.0.0.1"<br>		port=3306<br>		gtid_port=0<br>		status="ONLINE"<br>		weight=1<br>		compression=0<br>		max_connections=1000<br>		max_replication_lag=600<br>		use_ssl=0<br>		max_latency_ms=0<br>	},<br>	{<br>		hostgroup_id=2<br>		hostname="192.0.0.2"<br>		port=3306<br>		gtid_port=0<br>		status="ONLINE"<br>		weight=1<br>		compression=0<br>		max_connections=1000<br>		max_replication_lag=600<br>		use_ssl=0<br>		max_latency_ms=0<br>	}<br>)<br>mysql_replication_hostgroups:<br>(<br>	{<br>		writer_hostgroup=1<br>		reader_hostgroup=2<br>		check_type="read_only"<br>	}<br>

ProxySQLAdmin> select config into outfile /var/lib/proxysql/proxybkp.cnf; Query OK, 9920 rows affected (0.00 sec) File /var/lib/proxysql/proxybkp.cnf is saved. proxysql $ sudo cat /var/lib/proxysql/proxybkp.cnf |grep -wi mysql_servers -A 35 mysql_servers: ( { hostgroup_id=1 hostname="192.0.0.1" port=3306 gtid_port=0 status="ONLINE" weight=1 compression=0 max_connections=1000 max_replication_lag=600 use_ssl=0 max_latency_ms=0 }, { hostgroup_id=2 hostname="192.0.0.2" port=3306 gtid_port=0 status="ONLINE" weight=1 compression=0 max_connections=1000 max_replication_lag=600 use_ssl=0 max_latency_ms=0 } ) mysql_replication_hostgroups: ( { writer_hostgroup=1 reader_hostgroup=2 check_type="read_only" }

Mysqldump:

A ProxySQL backup restore can be performed using mysqldump. It is also possible to dump all (or some) of the tables of ProxySQL’s admin using mysqldump.

mysqldump must be run with –no-tablespaces , –skip-triggers, –skip-add-locks, and –skip-column-statistics

Backup:

$ mysqldump -u admin -p -h 127.0.0.1 -P6032 --no-tablespaces --no-create-info --no-create-db --skip-triggers --skip-column-statistics main mysql_servers > proxy_mysql_servers_bkp.sql<br>

1	$ mysqldump -u admin -p -h 127.0.0.1 -P6032 --no-tablespaces --no-create-info --no-create-db --skip-triggers --skip-column-statistics main mysql_servers > proxy_mysql_servers_bkp.sql<br>

Restoration:

ProxySQLAdmin> select * from mysql_servers;<br>+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+<br>| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |<br>+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+<br>| 1            | 192.0.0.1     | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 600                 | 0       | 0              |         |<br>| 2            | 192.0.0.2     | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 600                 | 0       | 0              |         |<br>+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+<br>2 rows in set (0.00 sec)<br><br>ProxySQLAdmin> delete from mysql_servers;<br>Query OK, 2 rows affected (0.01 sec)<br><br>ProxySQLAdmin> select * from mysql_servers;<br>Empty set (0.00 sec)<br><br>$ mysql -uadmin -p -h 127.0.0.1 -P 6032  -vvv < proxysql_mysql_servers_bkp.sql<br>Enter password:<br>Query OK, 0 rows affected (0.00 sec)<br>Bye<br><br>ProxySQLAdmin> select * from mysql_servers;<br>+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+<br>| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |<br>+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+<br>| 1            | 192.0.0.1     | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 600                 |  0      | 0              |         |<br>| 2            | 192.0.0.2     | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 600                 | 0       | 0              |         |<br>+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+-------------+-------+--------------------+---------------+<br>2 rows in set (0.00 sec)

ProxySQLAdmin> select * from mysql_servers; +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 192.0.0.1 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 600 | 0 | 0 | | | 2 | 192.0.0.2 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 600 | 0 | 0 | | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2 rows in set (0.00 sec) ProxySQLAdmin> delete from mysql_servers; Query OK, 2 rows affected (0.01 sec) ProxySQLAdmin> select * from mysql_servers; Empty set (0.00 sec) $ mysql -uadmin -p -h 127.0.0.1 -P 6032 -vvv < proxysql_mysql_servers_bkp.sql Enter password: Query OK, 0 rows affected (0.00 sec) Bye ProxySQLAdmin> select * from mysql_servers; +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 192.0.0.1 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 600 | 0 | 0 | | | 2 | 192.0.0.2 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 600 | 0 | 0 | | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+-------------+-------+--------------------+---------------+ 2 rows in set (0.00 sec)

If mysqldump version 8.0 is used:

mysqldump also needs the –skip-column-statistics option

It only works from ProxySQL version 2.0.8 and above

Physical snapshot:

A ProxySQL backup can be performed using the physical snapshot method. Ensure to stop ProxySQL services to get a consistent backup. This method contains the following steps:

Stop ProxySQL

Create a copy of the data directory

Start ProxySQL

Backup:

$ sudo service proxysql stop<br>Redirecting to /bin/systemctl stop proxysql.service<br><br>$ sudo cp /var/lib/proxysql/* /proxysql_snapshot/<br><br>

1	$ sudo service proxysql stop<br>Redirecting to /bin/systemctl stop proxysql.service<br><br>$ sudo cp /var/lib/proxysql/* /proxysql_snapshot/<br><br>

Restoration:

$ sudo rm -rf /var/lib/proxysql/*<br><br>$ sudo cp /proxysql_snapshot/* /var/lib/proxysql/<br><br>$ sudo chown -R proxysql:proxysql /var/lib/proxysql/<br><br>$ sudo service proxysql start<br>Redirecting to /bin/systemctl start proxysql.service<br><br>ProxySQLAdmin> select * from mysql_servers;<br>+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+<br>| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |<br>+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+<br>| 1            | 192.0.0.1     | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 600                 | 0       | 0              |         |<br>| 2            | 192.0.0.2     | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 600                 | 0       | 0              |         |<br>+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+<br>2 rows in set (0.00 sec)<br><br><br>

$ sudo rm -rf /var/lib/proxysql/* $ sudo cp /proxysql_snapshot/* /var/lib/proxysql/ $ sudo chown -R proxysql:proxysql /var/lib/proxysql/ $ sudo service proxysql start Redirecting to /bin/systemctl start proxysql.service ProxySQLAdmin> select * from mysql_servers; +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 192.0.0.1 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 600 | 0 | 0 | | | 2 | 192.0.0.2 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 600 | 0 | 0 | | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2 rows in set (0.00 sec)

Conclusion

These methods provide solutions for ProxySQL backups and restores, which play a pivotal role in safeguarding the integrity of your data and providing defense against various disasters, hardware malfunctions, data loss, and corruption. Without reliable backups, the consequences of data loss can be severe, as businesses risk operational disruptions, financial losses, damage to their reputation, and could even result in compliance violations.

If you are interested in more hands-on learning of ProxySQL, Percona’s professional training department offers a one-day, hands-on intensive tutorial covering all things ProxySQL. In the tutorial, we cover the basics of installation and configuration, read-write splitting, async/GR/PXC-based high availability, query firewalling, query whitelisting, results caching, and much, much more. Reach out to us today to schedule your instructor-led class!

Learn more about Percona Training