springboot-proxysql-mysql

The goal of this project is to use ProxySQL to load balance requests from a Spring Boot application to MySQL Replication Master-Slave Cluster.

Project Architecture

Applications

• MySQL

  MySQL is the most popular Open Source SQL database management system, supported by Oracle. In this project, we set a MySQL Replication Master-Slave Cluster that contains three MySQL instances: one master and two slaves. In the replication process, the data is copied automatically from master to the slaves.

• ProxySQL

  ProxySQL is an open-source, high-performance MySQL proxy server. It seats between application and database servers by accepting incoming traffic from MySQL clients and forwards it to backend MySQL servers. In this project, we set two hostgroups: writer=10 and reader=20. Those hostgroups say to which database servers write or read requests should go. The MySQL master belongs to the writer hostgroup. On the other hand, the slaves belong to reader one.

• customer-api

  Spring Boot Web Java application that exposes a REST API for managing customers. Instead of connecting directly to MySQL, as usual, the application will be connected to ProxySQL.

  customer-api has the following endpoints:

     GET /api/customers
     GET /api/customers/{id}
    POST /api/customers {"firstName":"...", "lastName":"..."}
     PUT /api/customers/{id} {"firstName":"...", "lastName":"..."}
  DELETE /api/customers/{id}
  

Prerequisites

• Java 17+
• Docker

Start Environment

• Open a terminal and, inside springboot-proxysql-mysql root folder, run the following script

  ./init-environment.sh
  

• Wait until the environment is up and running

Check MySQL Replication

• In a terminal, make sure you are inside springboot-proxysql-mysql root folder

• To check the replication status run

  ./check-replication-status.sh
  

  You should see something like

  mysql-master
  ------------
  File  Position  Binlog_Do_DB  Binlog_Ignore_DB  Executed_Gtid_Set
  mysql-bin-1.000003  1397      62a2f52f-b16b-11ed-91fc-0242c0a85002:1-14
  
  mysql-slave-1
  -------------
  *************************** 1. row ***************************
                 Slave_IO_State: Waiting for master to send event
                    Master_Host: mysql-master
                    Master_User: replication
                    Master_Port: 3306
                  Connect_Retry: 60
                Master_Log_File: mysql-bin-1.000003
            Read_Master_Log_Pos: 1397
                 Relay_Log_File: fa249eba35d6-relay-bin.000003
                  Relay_Log_Pos: 1614
          Relay_Master_Log_File: mysql-bin-1.000003
               Slave_IO_Running: Yes
              Slave_SQL_Running: Yes
                                 ...
  
  mysql-slave-2
  -------------
  *************************** 1. row ***************************
                 Slave_IO_State: Waiting for master to send event
                    Master_Host: mysql-master
                    Master_User: replication
                    Master_Port: 3306
                  Connect_Retry: 60
                Master_Log_File: mysql-bin-1.000003
            Read_Master_Log_Pos: 1397
                 Relay_Log_File: cbfd1f4bb01a-relay-bin.000003
                  Relay_Log_Pos: 1614
          Relay_Master_Log_File: mysql-bin-1.000003
               Slave_IO_Running: Yes
              Slave_SQL_Running: Yes
                                 ...
  

Check ProxySQL configuration

• In a terminal and inside springboot-proxysql-mysql root folder, run the script below to connect to ProxySQL command line terminal

  ./proxysql-admin.sh
  

• In ProxySQL Admin> terminal run the following command to see the MySQL servers

  SELECT * FROM mysql_servers;
  

• The following select shows the global variables

  SELECT * FROM global_variables;
  

• In order to exit ProxySQL command line terminal, type exit

Start customer-api

• In a terminal and navigate to springboot-proxysql-mysql root folder

• Run the following Maven command to start customer-api

  ./mvnw clean spring-boot:run --projects customer-api
  

Simulation

1. Open three terminals: one for mysql-master, one for mysql-slave-1 and another for mysql-slave-2

2. In mysql-master terminal, connect to MySQL Monitor by running

   docker exec -it -e MYSQL_PWD=secret mysql-master mysql -uroot --database customerdb
   

3. Do the same for mysql-slave-1...

   docker exec -it -e MYSQL_PWD=secret mysql-slave-1 mysql -uroot --database customerdb
   

4. ... and mysql-slave-2

   docker exec -it -e MYSQL_PWD=secret mysql-slave-2 mysql -uroot --database customerdb
   

5. Inside each MySQL Monitor's terminal, run the following commands to enable MySQL logs

   SET GLOBAL general_log = 'ON';
   SET global log_output = 'table';
   

6. Open a new terminal. In it, we will just run curl commands.

7. In the curl terminal, let's create a customer.

   curl -i -X POST http://localhost:8080/api/customers \
     -H 'Content-Type: application/json' \
     -d '{"firstName": "Ivan", "lastName": "Franchin"}'
   

8. Go to mysql-master terminal and run the following SELECT command

   SELECT event_time, command_type, SUBSTRING(argument,1,250) argument FROM mysql.general_log
   WHERE command_type = 'Query' AND (argument LIKE 'insert into customers %' OR argument LIKE 'select c1_0.id%' OR argument LIKE 'update customers %' OR argument LIKE 'delete from customers %');
   

   It should return

   +----------------------------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
   | event_time                 | command_type | argument                                                                                                                                        |
   +----------------------------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
   | 2023-02-20 22:13:15.400178 | Query        | insert into customers (created_at, first_name, last_name, updated_at) values ('2023-02-20 22:13:15', 'Ivan', 'Franchin', '2023-02-20 22:13:15') |
   +----------------------------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
   

   Note: If you run the same SELECT in the slave's terminal, you will see that just the mysql-master processed the insert command. Btw, it's in mysql-master where all inserts, updates and deletes are executed.

9. Now, let's call to the GET endpoint to retrieve customer 1. For it, go to curl terminal and run

   curl -i http://localhost:8080/api/customers/1
   

10. If you run, in one of the slave's terminal, the SELECT command below

    SELECT event_time, command_type, SUBSTRING(argument,1,250) argument FROM mysql.general_log
    WHERE command_type = 'Query' AND (argument LIKE 'insert into customers %' OR argument LIKE 'select c1_0.id%' OR argument LIKE 'update customers %' OR argument LIKE 'delete from customers %');
    

    It should return

    +----------------------------+--------------+-------------------------------------------------------------------------------------------------------------------+
    | event_time                 | command_type | argument                                                                                                          |
    +----------------------------+--------------+-------------------------------------------------------------------------------------------------------------------+
    | 2023-02-20 22:14:06.582449 | Query        | select c1_0.id,c1_0.created_at,c1_0.first_name,c1_0.last_name,c1_0.updated_at from customers c1_0 where c1_0.id=1 |
    +----------------------------+--------------+-------------------------------------------------------------------------------------------------------------------+
    

    Note: Just one slave should process it.

11. Next, let's UPDATE the customer 1. For it, go to the curl terminal and run

    curl -i -X PUT http://localhost:8080/api/customers/1 \
      -H 'Content-Type: application/json' \
      -d '{"firstName": "Ivan2", "lastName": "Franchin2"}'
    

12. Running the following SELECT inside the mysql-master terminal

    SELECT event_time, command_type, SUBSTRING(argument,1,250) argument FROM mysql.general_log
    WHERE command_type = 'Query' AND (argument LIKE 'insert into customers %' OR argument LIKE 'select c1_0.id%' OR argument LIKE 'update customers %' OR argument LIKE 'delete from customers %');
    

    It should return

    +----------------------------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
    | event_time                 | command_type | argument                                                                                                                                        |
    +----------------------------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
    | 2023-02-20 22:13:15.400178 | Query        | insert into customers (created_at, first_name, last_name, updated_at) values ('2023-02-20 22:13:15', 'Ivan', 'Franchin', '2023-02-20 22:13:15') |
    | 2023-02-20 22:14:33.019875 | Query        | update customers set created_at='2023-02-20 22:13:15', first_name='Ivan2', last_name='Franchin2', updated_at='2023-02-20 22:14:33' where id=1   |
    +----------------------------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
    

    Note: During an update, Hibernate/JPA does a select before performing the record update. So, you should see another select in one of the slaves

13. Finally, let's DELETE the customer 1. For it, go to the curl terminal and run

    curl -i -X DELETE http://localhost:8080/api/customers/1
    

14. Running the following SELECT inside the mysql-master terminal

    SELECT event_time, command_type, SUBSTRING(argument,1,250) argument FROM mysql.general_log
    WHERE command_type = 'Query' AND (argument LIKE 'insert into customers %' OR argument LIKE 'select c1_0.id%' OR argument LIKE 'update customers %' OR argument LIKE 'delete from customers %');
    

    It should return

    +----------------------------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
    | event_time                 | command_type | argument                                                                                                                                        |
    +----------------------------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
    | 2023-02-20 22:13:15.400178 | Query        | insert into customers (created_at, first_name, last_name, updated_at) values ('2023-02-20 22:13:15', 'Ivan', 'Franchin', '2023-02-20 22:13:15') |
    | 2023-02-20 22:14:33.019875 | Query        | update customers set created_at='2023-02-20 22:13:15', first_name='Ivan2', last_name='Franchin2', updated_at='2023-02-20 22:14:33' where id=1   |
    | 2023-02-20 22:14:52.358207 | Query        | delete from customers where id=1                                                                                                                |
    +----------------------------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
    

    Note: As it happens with an update, during a deletion, Hibernate/JPA does a select before performing the deletion of the record. So, you should see another select in one of the slaves

Shutdown

• To stop customer-api application, go to the terminal where it's running and press Ctrl+C.
• In order to get out of MySQL Monitors type exit.
• To stop and remove MySQLs and ProxySQL containers, network and volumes, make sure you are inside springboot-proxysql-mysql root folder and run the following script

  ./shutdown-environment.sh
  

References

• https://proxysql.com/documentation/
• https://proxysql.com/documentation/ProxySQL-Configuration/