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

MinSize: 2MaxSize: 4DesiredCapacity: 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 proxysqlsudo 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;"