Backups

File backups

You can take a copy of ProxySQL Admin’s disk persisted database file anytime, it will enforce a database level lock through the system lock and ensure the backup is consistent so it is as simple as:

cp /var/lib/proxysql/proxysql.db /var/lib/proxysql/proxysql-backup.dbysqldump

SQLite backups

SQLite can be used to take a backup of the database as well in two ways, either via a dump file or a backup file:

sqlite3 /var/lib/proxysql/proxysql.db .dump > /var/lib/proxysql/proxysql-dump.sql

sqlite3 /var/lib/proxysql/proxysql.db ".backup 'proxysql-backup.sq3'"

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 and --skip-add-locks,.

For example, to dump the mysql_servers table:

mysqldump -u admin -padmin -h 127.0.0.1 -P6032 --no-tablespaces --skip-triggers -t main mysql_servers --skip-column-statistics > /tmp/dump_servers.sql

mysqldump version 8.0

If mysqldump version 8.0 is used:

• mysqldump also needs the --skip-column-statistics option
• it only works from ProxySQL version 2.0.8