ProxySQL Series : ProxySQL Backup Startegies

Introduction

            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.

Photo by Samuel Zeller on Unsplash
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s