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.
- 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
- Install ProxySQL:
sudo apt-get install proxysql
- Start the ProxySQL service:
sudo service proxysql start
- Connect to the ProxySQL admin interface:
mysql -u admin -padmin -h 127.0.0.1 -P 6032
- 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);
- 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);
- 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;
- Change the proxysql global variable to connect to backend mysql server
SET mysql-default_hostgroup = 0;
- Verify the configuration
SELECT * FROM mysql_servers;
SELECT * FROM mysql_users;
- 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