Unveil the secrets to bulletproof ProxySQL backups! This comprehensive guide explores four effective strategies to safeguard your MySQL environment. Discover how to leverage SQLite3, physical snapshots, configuration files, and even mysqldump for seamless ProxySQL backups.
We are well aware that ProxySQL is one of the leading SQL aware proxy for MySQL. In this blog I am going to explain the backup & restore strategies of the ProxySQL . I think, still there is not well structured blog about this topic .
If you are looking for other articles on our ProxySQL Series :
Is ProxySQL backup really required ?
- You have your prod servers a configured under a single ProxySQL. Someone terminated the Proxy server accidentally in AWS console, now the ProxySQL backup is really required?
- You have a request from DEV team to built one more ProxySQL with the same existing configuration, are you ready to manually execute all the commands (Complex Rules) for the new ProxySQL setup configuration?
- Accidentally your DC went down and you need to built ProxySQL in different DC, How you will do this in short time?
ProxySQL backup strategies –
As a DBA, I thought of below four strategies are playing a vital role in backing up the ProxySQL.
- Sqlite3 backup
- Physical snapshot
- Config file backup
- Mysqldump
Before going into the topic, I am giving a short overview of my test environment. I have the 5 servers ( 1 master 3 slaves 1 backup node) configured in the ProxySQL.
Master : 192.168.33.25
Slaves : 192.168.33.26, 192.168.33.27 , 192.168.33.28
Backup : 192.168.33.29
mysql> select hostgroup_id,hostname,port,status,comment from mysql_servers;
+--------------+---------------+------+--------+---------+
| hostgroup_id | hostname | port | status | comment |
+--------------+---------------+------+--------+---------+
| 100 | 192.168.33.25 | 3306 | ONLINE | master |
| 100 | 192.168.33.26 | 3306 | ONLINE | slave1 |
| 100 | 192.168.33.27 | 3306 | ONLINE | slave2 |
| 100 | 192.168.33.28 | 3306 | ONLINE | slave3 |
| 100 | 192.168.33.29 | 3306 | ONLINE | backup |
+--------------+---------------+------+--------+---------+
5 rows in set (0.00 sec)
Let’s jump into the practical experiments ,
Sqlite3 backup
Sqlite is the backend database for the ProxySQL. Sqlite3 is the client tool being used to perform the backup on Sqlite databases. It’s also helps to view the available contents in the data file without restoring the backup .
Example –
[root@mydbopslabs25 proxysql]# ls /var/lib/proxysql/
mysql_server.sql proxysql.db proxysql.log proxysql.pid proxysql_stats.db
[root@mydbopslabs25 proxysql]# sqlite3 proxysql.db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from mysql_servers;
100|192.168.33.25|3306|ONLINE|1|0|1000|0|0|0|master
100|192.168.33.26|3306|ONLINE|1|0|1000|0|0|0|slave1
100|192.168.33.27|3306|ONLINE|1|0|1000|0|0|0|slave2
100|192.168.33.28|3306|ONLINE|1|0|1000|0|0|0|slave3
100|192.168.33.29|3306|ONLINE|1|0|1000|0|0|0|backup
sqlite> .quit
Below is the procedure to perform the logical backup and restore with Sqlite3 .
cmd -
[root@mydbopslabs25 proxysql]# sqlite3 proxysql.db .dump > data.sql
Backup file output -
[root@mydbopslabs25 proxysql]# cat data.sql | head -n5
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE mysql_servers (hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0 , hostname VARCHAR NOT NULL , port INT NOT NULL DEFAULT 3306 , status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE' , weight INT CHECK (weight >= 0) NOT NULL DEFAULT 1 , compression INT CHECK (compression >=0 AND compression <= 102400) NOT NULL DEFAULT 0 , max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000 , max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0 , use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0 , max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0 , comment VARCHAR NOT NULL DEFAULT '' , PRIMARY KEY (hostgroup_id, hostname, port) );
INSERT INTO "mysql_servers" VALUES(100,'192.168.33.25',3306,'ONLINE',1,0,1000,0,0,0,'master');
INSERT INTO "mysql_servers" VALUES(100,'192.168.33.26',3306,'ONLINE',1,0,1000,0,0,0,'slave1');
Backup restoration
[root@mydbopslabs25 proxysql]# mv data.sql /
[root@mydbopslabs25 proxysql]# service proxysql stop
Shutting down ProxySQL: DONE!
[root@mydbopslabs25 proxysql]# sqlite3 /var/lib/proxysql/proxysql.db < /data.sql
[root@mydbopslabs25 proxysql]#
[root@mydbopslabs25 proxysql]# service proxysql start
Starting ProxySQL: 2019-05-02 20:51:46 [INFO] Using config file /etc/proxysql.cnf
DONE!
[root@mydbopslabs25 proxysql]# mysql -h127.0.0.1 -uadmin -padmin -P6032 -e "select hostgroup_id,hostname,port,status,comment from mysql_servers;"
+--------------+---------------+------+--------+---------+
| hostgroup_id | hostname | port | status | comment |
+--------------+---------------+------+--------+---------+
| 100 | 192.168.33.25 | 3306 | ONLINE | master |
| 100 | 192.168.33.26 | 3306 | ONLINE | slave1 |
| 100 | 192.168.33.27 | 3306 | ONLINE | slave2 |
| 100 | 192.168.33.28 | 3306 | ONLINE | slave3 |
| 100 | 192.168.33.29 | 3306 | ONLINE | backup |
+--------------+---------------+------+--------+---------+
Yes, it is working well as expected. It is a logical backup, we can use this method on RUNTIME. It is not mandatory to bring down ProxySQL services.
Physical snapshot
This is a common way we used to take the cold backup. Below are the steps involved in this backup process.
- Stop the ProxySQL
- Copy the data directory to the backup folder
- Start the ProxySQL
[root@mydbopslabs25 proxysql]# pwd
/var/lib/proxysql
[root@mydbopslabs25 proxysql]# service proxysql stop
Shutting down ProxySQL: DONE!
[root@mydbopslabs25 proxysql]# cp -r /var/lib/proxysql/* /backup_snapshot/
[root@mydbopslabs25 proxysql]# service proxysql start
Starting ProxySQL: 2019-05-02 20:59:33 [INFO] Using config file /etc/proxysql.cnf
DONE!
[root@mydbopslabs25 proxysql]# ls -lrth /backup_snapshot/
total 168K
-rw------- 1 root root 108K May 2 20:59 proxysql_stats.db
-rw------- 1 root root 15K May 2 20:59 proxysql.log
-rw-r--r-- 1 root root 42K May 2 20:59 proxysql.db
Below is the procedures for restoration.
[root@mydbopslabs25 proxysql]# service proxysql stop
Shutting down ProxySQL: DONE!
[root@mydbopslabs25 proxysql]# pwd
/var/lib/proxysql
[root@mydbopslabs25 proxysql]# rm -rf /var/lib/proxysql/*
[root@mydbopslabs25 proxysql]# cp -r /backup_snapshot/* /var/lib/proxysql/
[root@mydbopslabs25 proxysql]# chown -R proxysql:proxysql /var/lib/proxysql/
[root@mydbopslabs25 proxysql]# ls -lrth
total 168K
-rw------- 1 proxysql proxysql 15K May 2 21:02 proxysql.log
-rw-r--r-- 1 proxysql proxysql 42K May 2 21:02 proxysql.db
-rw------- 1 proxysql proxysql 108K May 2 21:02 proxysql_stats.db
[root@mydbopslabs25 proxysql]# service proxysql start
Starting ProxySQL: 2019-05-02 21:03:02 [INFO] Using config file /etc/proxysql.cnf
DONE!
[root@mydbopslabs25 proxysql]# mysql -h127.0.0.1 -uadmin -padmin -P6032 -e "select hostgroup_id,hostname,port,status,comment from mysql_servers;"
+--------------+---------------+------+--------+---------+
| hostgroup_id | hostname | port | status | comment |
+--------------+---------------+------+--------+---------+
| 100 | 192.168.33.25 | 3306 | ONLINE | master |
| 100 | 192.168.33.26 | 3306 | ONLINE | slave |
| 100 | 192.168.33.27 | 3306 | ONLINE | slave1 |
| 100 | 192.168.33.28 | 3306 | ONLINE | slave2 |
| 100 | 192.168.33.29 | 3306 | ONLINE | backup |
+--------------+---------------+------+--------+---------+
Taking the snapshot without stopping ProxySQL may cause corruption in the backup .
Note: Make sure you are restoring the backup in same ProxySQL versions to avoid the conflicts over the schema structures.
Config file backup
I personally like this backup method very much. ProxySQL provided, that maximum configuration we can do on runtime, without restarting the ProxySQL. No config file entries required. But, whenever making the changes in ProxySQL, I would suggest to add those entries in ProxySQL config file as well and make a backup too.
This is my backup ProxySQL config file, I added under “/etc/”
cat /etc/proxysql.cnf
.
.
mysql_servers =
(
{ address="192.168.33.25" , port=3306 , hostgroup=100, status="ONLINE", comment="master" },
{ address="192.168.33.26" , port=3306 , hostgroup=100, status="ONLINE", comment="slave1" },
{ address="192.168.33.27" , port=3306 , hostgroup=100, status="ONLINE", comment="slave2" },
{ address="192.168.33.28" , port=3306 , hostgroup=100, status="ONLINE", comment="slave3" },
{ address="192.168.33.29" , port=3306 , hostgroup=100, status="ONLINE", comment="backup" }
)
:
Now, I am going to start the ProxySQL with the updated config file,
#Linux shell
[root@mydbopslabs25 proxysql]# service proxysql start
Starting ProxySQL: 2019-05-03 00:02:01 [INFO] Using config file /etc/proxysql.cnf DONE!
[root@mydbopslabs25 proxysql]# ls
[root@mydbopslabs25 proxysql]# mysql -h127.0.0.1 -uadmin -padmin -P6032 -e "select hostgroup_id,hostname,port,status,comment from mysql_servers;"
+--------------+---------------+------+--------+---------+
| hostgroup_id | hostname | port | status | comment |
+--------------+---------------+------+--------+---------+
| 100 | 192.168.33.25 | 3306 | ONLINE | master |
| 100 | 192.168.33.26 | 3306 | ONLINE | slave |
| 100 | 192.168.33.27 | 3306 | ONLINE | slave1 |
| 100 | 192.168.33.28 | 3306 | ONLINE | slave2 |
| 100 | 192.168.33.29 | 3306 | ONLINE | backup |
+--------------+---------------+------+--------+---------+
This is one of the easiest way to achieve the backup in ProxySQL .We can use this method for new deployments .
Mysqldump
We can perform the ProxySQL backup with mysqldump . But, we have different set of admin commands between MySQL and Sqlite3 databases . Still, we can achieve this by ignoring the unwanted objects from the MySQL dump file.
Example
cmd-
[root@mydbopslabs25 proxysql]# mysqldump -h127.0.0.1 -uadmin -padmin -P6032 --no-tablespaces --no-create-info --no-create-db --skip-triggers main mysql_servers | grep -i 'lock tables\|insert into' > mysqldump_data.sql
[root@mydbopslabs25 proxysql]# ls -lrth mysqldump_data.sql
-rw-r--r-- 1 root root 470 May 3 00:20 mysqldump_data.sql
output-
[root@mydbopslabs25 proxysql]# cat mysqldump_data.sql
LOCK TABLES `mysql_servers` WRITE;
INSERT INTO `mysql_servers` VALUES ('100','192.168.33.25','3306','ONLINE','1','0','1000','0','0','0','master'),('100','192.168.33.26','3306','ONLINE','1','0','1000','0','0','0','slave'),('100','192.168.33.27','3306','ONLINE','1','0','1000','0','0','0','slave1'),('100','192.168.33.28','3306','ONLINE','1','0','1000','0','0','0','slave2'),('100','192.168.33.29','3306','ONLINE','1','0','1000','0','0','0','backup');
UNLOCK TABLES;
Restoring the mysqldump file ,
[root@mydbopslabs25 proxysql]# service proxysql stop
Shutting down ProxySQL: DONE!
[root@mydbopslabs25 proxysql]# pwd
/var/lib/proxysql
[root@mydbopslabs25 proxysql]# rm -rf
[root@mydbopslabs25 proxysql]# service proxysql start
Starting ProxySQL: 2019-05-03 00:26:23 [INFO] Using config file /etc/proxysql.cnf
DONE!
[root@mydbopslabs25 proxysql]# mysql -h127.0.0.1 -uadmin -padmin -P6032 -vvv < /mysqldump_data.sql
--------------
LOCK TABLES `mysql_servers` WRITE
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
INSERT INTO `mysql_servers` VALUES ('100','192.168.33.25','3306','ONLINE','1','0','1000','0','0','0','master'),('100','192.168.33.26','3306','ONLINE','1','0','1000','0','0','0','slave'),('100','192.168.33.27','3306','ONLINE','1','0','1000','0','0','0','slave1'),('100','192.168.33.28','3306','ONLINE','1','0','1000','0','0','0','slave2'),('100','192.168.33.29','3306','ONLINE','1','0','1000','0','0','0','backup')
--------------
Query OK, 5 rows affected (0.00 sec)
--------------
UNLOCK TABLES
--------------
Query OK, 0 rows affected (0.00 sec)
Bye
[root@mydbopslabs25 proxysql]# mysql -h127.0.0.1 -uadmin -padmin -P6032 -e "select hostgroup_id,hostname,port,status,comment from mysql_servers;"
+--------------+---------------+------+--------+---------+
| hostgroup_id | hostname | port | status | comment |
+--------------+---------------+------+--------+---------+
| 100 | 192.168.33.25 | 3306 | ONLINE | master |
| 100 | 192.168.33.26 | 3306 | ONLINE | slave |
| 100 | 192.168.33.27 | 3306 | ONLINE | slave1 |
| 100 | 192.168.33.28 | 3306 | ONLINE | slave2 |
| 100 | 192.168.33.29 | 3306 | ONLINE | backup |
+--------------+---------------+------+--------+---------+
As explained above, the restoration is a bit harder because SQL statements (code) in the MySQL dump file are mostly not supported in Sqlite3 .
I believe this blog definitely will helps someone who is looking for a backup solution for their ProxySQL servers.
Unlock the Full Potential of MySQL with Mydbops! Our open-source database management services encompass expert assistance with ProxySQL implementation, performance tuning, and robust backup solutions. Let our MySQL specialists empower you with a watertight backup strategy for your ProxySQL environment. Contact Mydbops today!
{{cta}}
%20vs%20systemctl%20stop.png)
.png)
.svg)
