ProxySql is a service that can help you to load balance read/write, sharding and caching request to your mysql server. It can handle more than 100k connection.
Here’s how to install Proxysql server on your Ubuntu server.

  1. Install the ProxySQL package repository:
wget -O- 'https://repo.proxysql.com/ProxySQL/repo_pub_key' | sudo apt-key add -
sudo add-apt-repository -y "deb [arch=amd64] https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/ubuntu $(lsb_release -sc) main"
sudo apt-get update
  1. Install ProxySQL:
sudo apt-get install proxysql
  1. Start the ProxySQL service:
sudo service proxysql start
  1. Connect to the ProxySQL admin interface:
mysql -u admin -padmin -h 127.0.0.1 -P 6032
  1. Configure the proxysql to connect to the backend mysql server, by adding the backend mysql server in mysql_servers table
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (0, '<hostname or IP>', 3306);
  1. Configure the proxysql to connect to the backend mysql server, by adding the backend mysql server in mysql_users table
INSERT INTO mysql_users (username, password, active, default_hostgroup) VALUES ('<username>', '<password>', 1, 0);
  1. Load the mysql_servers and mysql_users table to runtime
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
  1. Change the proxysql global variable to connect to backend mysql server
SET mysql-default_hostgroup = 0;
  1. Verify the configuration
SELECT * FROM mysql_servers;
SELECT * FROM mysql_users;
  1. Restart the proxysql service
sudo service proxysql restart

You should now be able to connect to the backend MySQL server through the ProxySQL service.

Reference https://proxysql.com/

See you on next part on configure-proxysql-on-ubuntu-server-2

Previous ArticleNext Article