ProxySQL MySQL MySQL · DBA · Operations

ProxySQL — Backup & Restore Configuration

Back up and restore ProxySQL configuration using SQLite file copies, SQL exports and automated backup scripts. Includes migration to a new server.

Backing Up ProxySQL Configuration

ProxySQL stores its persistent configuration in a SQLite database at /var/lib/proxysql/proxysql.db. Backing up this file and the configuration tables ensures you can quickly restore ProxySQL to its exact state after a failure, rebuild, or migration.

Method 1 — Backup SQLite Database File

BASH — Backup SQLite DB

# Simple file backup — copy proxysql.db
cp /var/lib/proxysql/proxysql.db /backup/proxysql_$(date +%Y%m%d_%H%M%S).db

# Backup with compression
tar -czf /backup/proxysql_backup_$(date +%Y%m%d).tar.gz   /var/lib/proxysql/proxysql.db   /etc/proxysql.cnf   /var/lib/proxysql/proxysql_errorlog

# Scheduled daily backup via cron
echo "0 2 * * * root cp /var/lib/proxysql/proxysql.db /backup/proxysql_\$(date +\%Y\%m\%d).db"   >> /etc/crontab

Method 2 — Export Config as SQL

SQL — Export Config as SQL

-- Connect to ProxySQL Admin
-- mysql -u admin -padmin -h 127.0.0.1 -P 6032

-- Export MySQL servers
SELECT CONCAT(
  "INSERT INTO mysql_servers (hostgroup_id,hostname,port,status,weight,max_connections,comment) VALUES (",
  hostgroup_id, ",'", hostname, "',", port, ",'", status, "',", weight, ",", max_connections, ",'", comment, "');"
) AS sql_dump
FROM mysql_servers;

-- Export MySQL users (passwords are hashed)
SELECT CONCAT(
  "INSERT INTO mysql_users (username,password,default_hostgroup,active,transaction_persistent) VALUES ('",
  username, "','", password, "',", default_hostgroup, ",", active, ",", transaction_persistent, ");"
) AS sql_dump
FROM mysql_users;

-- Export query rules
SELECT CONCAT(
  "INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply,comment) VALUES (",
  rule_id, ",", active, ",'", IFNULL(match_pattern,''), "',", IFNULL(destination_hostgroup,'NULL'), ",", apply, ",'", IFNULL(comment,''), "');"
) AS sql_dump
FROM mysql_query_rules
ORDER BY rule_id;

-- Export global variables
SELECT CONCAT(
  "UPDATE global_variables SET variable_value='", variable_value,
  "' WHERE variable_name='", variable_name, "';"
) AS sql_dump
FROM global_variables
WHERE variable_name LIKE 'mysql-%' OR variable_name LIKE 'admin-%';

Method 3 — Full Admin Dump Script

BASH — Backup Script

#!/bin/bash
# proxysql_backup.sh — Full ProxySQL config backup

ADMIN_HOST="127.0.0.1"
ADMIN_PORT=6032
ADMIN_USER="admin"
ADMIN_PASS="admin"
BACKUP_DIR="/backup/proxysql"
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p $BACKUP_DIR

MYSQL="mysql -u$ADMIN_USER -p$ADMIN_PASS -h$ADMIN_HOST -P$ADMIN_PORT -N -s"

echo "-- ProxySQL Backup $DATE" > $BACKUP_DIR/proxysql_$DATE.sql

# Backup each section
for TABLE in mysql_servers mysql_users mysql_query_rules mysql_replication_hostgroups; do
    echo "-- Table: $TABLE" >> $BACKUP_DIR/proxysql_$DATE.sql
    $MYSQL -e "SELECT * FROM $TABLE" 2>/dev/null >> $BACKUP_DIR/proxysql_$DATE.sql
done

# Backup SQLite file
cp /var/lib/proxysql/proxysql.db $BACKUP_DIR/proxysql_$DATE.db

echo "Backup complete: $BACKUP_DIR/proxysql_$DATE.sql"

Restoring ProxySQL Configuration

BASH — Restore from SQLite

# Method 1: Restore SQLite file (full restore)
systemctl stop proxysql
cp /backup/proxysql_20260424.db /var/lib/proxysql/proxysql.db
chown proxysql:proxysql /var/lib/proxysql/proxysql.db
systemctl start proxysql

# Verify restore
mysql -u admin -padmin -h 127.0.0.1 -P 6032 -e "SELECT * FROM mysql_servers;"

SQL — Restore from SQL Dump

-- Method 2: Restore from SQL dump via Admin interface
-- First clean existing config
DELETE FROM mysql_servers;
DELETE FROM mysql_users;
DELETE FROM mysql_query_rules;

-- Then re-insert from your saved SQL dump
INSERT INTO mysql_servers ...;
INSERT INTO mysql_users ...;
INSERT INTO mysql_query_rules ...;

-- Activate and persist
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

Migrating ProxySQL to New Server

BASH — Migrate to New Server

# On old ProxySQL server
systemctl stop proxysql
scp /var/lib/proxysql/proxysql.db newserver:/var/lib/proxysql/proxysql.db
scp /etc/proxysql.cnf newserver:/etc/proxysql.cnf

# On new ProxySQL server
chown proxysql:proxysql /var/lib/proxysql/proxysql.db
systemctl start proxysql

# Verify all config migrated
mysql -u admin -padmin -h 127.0.0.1 -P 6032   -e "SELECT * FROM mysql_servers; SELECT * FROM mysql_users;"