Create new utility proxysqldump

[renecannao]

mysqldump can be used to dump ProxySQL configuration, although it has a lot of limitations.
A new tool (proxysqldump) can be created to create dumps specific for ProxySQL

[ManjotS]

Why not just make a wrapper script? proxysqldump could run mysqldump with correct flags and options. proxysqlimport could do import. This would be easier to implement in the short term and provide the same functionality.

[renecannao]

@ManjotS : it seems mysqldump 5.7 run some queries yet not handled by proysql's admin

[reinholdfuereder]

@renecannao I found this issue via https://groups.google.com/forum/#!topic/proxysql/JfA6X44-iMI and https://docs.ansible.com/ansible/latest/modules/proxysql_manage_config_module.html#proxysql-manage-config-module

Frankly, considering the good documentation (including very nice ASCII diagram!) at https://github.com/sysown/proxysql/wiki/Configuring-ProxySQL I was also rather expecting (and still hoping for) a supplementing step "[6] SAVE MYSQL * TO CONFIG"; of course also for the sake of symmetry and elegance ;-)

[bakaut]

@renecannao i just use
sqlite3 /var/lib/proxysql/proxysql.db -batch ".dump" > disk.sql

[michelamarie]

Thanks for the tip, @bakaut! @renecannao, are there any issues or potential pitfalls with regard to backing up ProxySQL's configuration with SQLite? It seems one potential issue is that ProxySQL must be shut down in order to restore an SQLite back-up (I am not certain of that, but it appears to be the case).

It would be ideal if we can back-up and restore the configuration database with mysqldump / mysql.

Also, is there an "officially" recommended work-around for users affected by this mysqldump issue? It appears that many people / environments are affected by it, as the problem also affects mysqldump 10.18, packaged with MariaDB 10.3 -- the version shipped on RHEL/CentOS/OL 8.

May I suggest this information (about the mysqldump issue, and recommended back-up methods and work-arounds to the mysqldump issue) be added to the documentation on ProxySQL configuration back-up at https://proxysql.com/documentation/Admin-and-mysqldump/? That would be quite helpful.

[pondix]

@michelamarie if you are using mysqldump then ProxySQL must be running, for sqlite3 backups ProxySQL can be running or stopped, its irrelevant. You can also do a backup via regular file copy of proxysql.db. Both sqlite3 and file copy will take a consistent backup of the database as the locks they require to run will ensure that all transactions have completed before taking the backup, also consider that your "disk" database will only change when you issue some SAVE xxx TO DISK command so it should be in a fairly "static" state most of the time in any case.

I've updated the documentation at https://proxysql.com/Documentation/proxysql-backups/ with some additional information, hope this helps!

[michelamarie]

Thanks so much for your reply, @pondix! ProxySQL was running when I attempted to back-up its saved configuration with mysqldump, though it still produced errors when I ran it.

The information on backing up the ProxySQL configuration with SQLite is very helpful, and is exactly what I needed to know in order to have confidence in the back-ups we are currently taking with SQLite. Indeed, we are running those back-ups after all the current configuration parameters have been saved to disk.

It's great to see that you've included this information in the ProxySQL documentation! It would probably also be helpful for users to include restore procedures. :p

Here is a functional restore procedure for an sqllite3 back-up, based on my understanding:

# First stop ProxySQL and make a back-up copy of the current database, for extra safety:
systemctl stop proxysql
cp /var/lib/proxysql/proxysql.db ~/backup/

# Next, restore the database back-up, then start ProxySQL again:
sqlite3 /var/lib/proxysql/proxysql.db < /back_up_file.sql 
systemctl start proxysql ; systemctl status proxysql

Thank you again!