如何结合使用 ProxySQL 和开源平台来拆分 Amazon Aurora 集群上的 SQL 读写操作

MinSize: 2
MaxSize: 4
DesiredCapacity: 2

DBAuroraCluster:
    Type: "AWS::RDS::DBCluster"
    Properties:
      DatabaseName: !Ref DatabaseName
      Engine: aurora-mysql
      MasterUsername: !Ref DatabaseUser
      MasterUserPassword: !Ref DatabasePassword
      VpcSecurityGroupIds: 
        - !Ref DBFirewall
      Tags:
        - Key: Project
          Value: !Ref ProjectTag
  DBAuroraOne:
    Type : "AWS::RDS::DBInstance"
    Properties:
      DBClusterIdentifier: !Ref DBAuroraCluster
      Engine: aurora-mysql
      DBInstanceClass: !Ref DbInstanceSize
      Tags:
        - Key: Project
          Value: !Ref ProjectTag
  DBAuroraTwo:
    Type : "AWS::RDS::DBInstance"    
    Properties:
      DBClusterIdentifier: !Ref DBAuroraCluster
      Engine: aurora-mysql
      DBInstanceClass: !Ref DbInstanceSize
      Tags:
        - Key: Project
          Value: !Ref ProjectTag
  DBAuroraThree:
    Type : "AWS::RDS::DBInstance"    
    Properties:
      DBClusterIdentifier: !Ref DBAuroraCluster
      Engine: aurora-mysql
      DBInstanceClass: !Ref DbInstanceSize
      Tags:
        - Key: Project
          Value: !Ref ProjectTag 

sudo yum install -y mysql

wget -P /tmp/ https://github.com/sysown/proxysql/releases/download/v2.0.0-rc2/proxysql-rc2-2.0.0-1-centos7.x86_64.rpm

#Install proxysql
sudo yum install -y /tmp/proxysql-rc2-2.0.0-1-centos7.x86_64.rpm 

sudo service proxysql start

mysql -u admin -padmin -h 127.0.0.1 -P6032 main < proxysql.sql

delete from mysql_servers where hostgroup_id in (10,20);
delete from mysql_replication_hostgroups where writer_hostgroup=10;

INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('${ClusterEndpoint}',10,3306,1000,2000);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('${ReaderEndpoint}',20,3306,1000,2000);

INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment,check_type) VALUES (10,20,'aws-aurora','innodb_read_only');

LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;

delete from mysql_query_rules where rule_id in (50,51);

INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (50,1,'^SELECT.*FOR UPDATE$',10,1), (51,1,'^SELECT',20,1);

LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

delete from mysql_users where username='${DatabaseUser}';

insert into mysql_users (username,password,active,default_hostgroup,default_schema,transaction_persistent) values ('${DatabaseUser}','${DatabasePassword}',1,10,'${DatabaseName}',1);

LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;

UPDATE global_variables SET variable_value='${DatabaseUser}' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='${DatabasePassword}' WHERE variable_name='mysql-monitor_password';

UPDATE global_variables SET variable_value='${BackendMySQLVersion}' WHERE variable_name='mysql-server_version';
UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

0.0.0.0:6033 # 如果更改，需要重新启动服务。

0.0.0.0:6032

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

INSERT INTO Persons (PersonID, LastName, FirstName, Address, City)
VALUES (1, 'Doe', 'John', '123 Main Street', 'North York');
INSERT INTO Persons (PersonID, LastName, FirstName, Address, City)
VALUES (2, 'Doe', 'Jane', '456 Second Street', 'Toronto');
INSERT INTO Persons (PersonID, LastName, FirstName, Address, City)
VALUES (3, 'Smith', 'Robert', '789 Third Street', 'Vaughan');

mysql -u admin -padmin -h 127.0.0.1 -P6032 -e "select hostgroup, schemaname, username, digest, digest_text, count_star from stats_mysql_query_digest;"

select * from Persons;

mysql -u admin -padmin -h 127.0.0.1 -P6032 -e "select hostgroup, schemaname, username, digest, digest_text, count_star from stats_mysql_query_digest;"

BEGIN;
    INSERT INTO Persons (PersonID, LastName, FirstName, Address, City)
VALUES (4, 'Garg', 'Raj', '234 Fourth Street', 'North York');

    INSERT INTO Persons (PersonID, LastName, FirstName, Address, City)
VALUES (5, 'Doe', 'John', '373 Fifth Street', 'Brampton');
    SELECT * from Persons where PersonID > 3;
COMMIT;

mysql -u admin -padmin -h 127.0.0.1 -P6032 -e "select hostgroup, schemaname, username, digest, digest_text, count_star from stats_mysql_query_digest;"