How to Migrate ProxySQL from old server to new server?
Take the backup of ProxySQL from old server from below command
If you want to take single table backup use below command,
Take backup
#Shell> mysqldump -u admin -p -h 127.0.0.1 -P6032 –no-tablespaces –replace –skip-triggers -t main mysql_servers > mysql_servers.sql
Restore table on new server
#Shell> mysql -u admin -p -h 127.0.0.1 -P6032 main < mysql_servers.sql
If you want to take full DB backup use below command
#Shell> mysqldump -u admin -p -h 127.0.0.1 -P6032 –no-tablespaces –skip-triggers main > proxysql.sql
Restore DB on new server
#Shell> mysql -u admin -p -h 127.0.0.1 -P6032 main < proxysql.sql
Done… Now you can check the server, user and query rules configuration by below command,
#Shell> mysql -u admin -p -h 127.0.0.1 -P6032
it will prompt for password;
mysql> select * from mysql_servers; (This will list all server configured)
mysql> select * from mysql_users; (This will list all username created)
mysql> select * from mysql_query_rules; (This will list all query rules configured)
If everything seems fine, then you migration is complete, otherwise you need to repeat the steps, as you might have made come mistake while taking backup or restoring.