Skip to content

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

Notifications You must be signed in to change notification settings

ivangfr/springboot-proxysql-mysql

Repository files navigation

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.

Proof-of-Concepts & Articles

On ivangfr.github.io, I have compiled my Proof-of-Concepts (PoCs) and articles. You can easily search for the technology you are interested in by using the filter. Who knows, perhaps I have already implemented a PoC or written an article about what you are looking for.

Additional Readings

Project Architecture

project-diagram

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

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.

  1. 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"}'
    
  2. 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

  1. 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
    
  2. 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

About

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

Topics

Resources

Stars

Watchers

Forks

Releases

No releases published

Sponsor this project

 

Packages

No packages published