Connect to your Aurora MySQL DB cluster¶
To interact with the Aurora database cluster, you will use an Amazon EC2 Linux instance that acts like a workstation to interact with the AWS resources in the labs on this website. All necessary software packages and scripts have been installed and configured on this EC2 instance for you. To ensure a unified experience, you will be interacting with this workstation using AWS Systems Manager Session Manager. With Session Manager you can interact with your workstation directly from the management console, without the need to install any software on your own devices.
This lab contains the following tasks:
- Get the parameters from the Team Dashboard
- Connect to your workstation instance
- Verify lab environment
- Connect to the DB cluster
- Load an initial data set from S3
This lab requires the following prerequisites:
1. Get the parameters from the Team Dashboard¶
At the end of the getting started step, you have reviewed the parameters for your lab environment. For the following Aurora MySQL labs, you will need the following values for lab resources:
|DB Subnet Group||Refer to: [dbSubnetGroup] on the Team Dashboard|
|Security Group||Refer to: [dbSecurityGroup] on the Team Dashboard|
|EC2 Workstation||Refer to: [bastionMySQL] on the Team Dashboard|
|Cluster Endpoint||Refer to: [mysqlClusterEndpoint] on the Team Dashboard|
|Reader Endpoint||Refer to: [mysqlReaderEndpoint] on the Team Dashboard|
|Load Generator Script||Refer to: [mysqlRunDoc] on the Team Dashboard|
|DB username||Preset as environment variable
|DB password||Preset as environment variable
2. Connect to your workstation instance¶
Open the Systems Manager: Session Manager service console. Click the Start session button.
Please select the correct EC3 instance to establish a session with. The workstation is named
auroralab-mysql-bastion, select it and click Start session.
If you see a black command like terminal screen and a prompt, you are now connected to the workstation. Type the following command to use the correct user account and context for the labs:
sudo su -l ubuntu
3. Verify lab environment¶
Let's make sure your workstation has been configured properly. Type the following command in the Session Manager command line:
tail -n1 /debug.log
You should see the output:
* bootstrap complete, rebooting, if that is not the output you see, please wait a few more minutes and retry.
4. Connect to the DB cluster¶
Connect to the Aurora database just like you would to any other MySQL-based database, using a compatible client tool. In this lab you will be using the
mysql command line tool to connect.
Run the command below, replacing the [mysqlClusterEndpoint] placeholder with the cluster endpoint of your DB cluster. You will find the value for the cluster endpoint parameter on the Team Dashboard web page.
mysql -h[mysqlClusterEndpoint] -u$DBUSER -p"$DBPASS" mylab
Can I see the AWS Secrets Manager credentials?
You can view and retrieve the credentials stored in the secret using the following command:
aws secretsmanager get-secret-value --secret-id [secretArn] | jq -r '.SecretString'
Once connected to the database, use the code below to create a stored procedure we'll use later in the labs, to generate load on the DB cluster. Run the following SQL queries:
DELIMITER $$ DROP PROCEDURE IF EXISTS minute_rollup$$ CREATE PROCEDURE minute_rollup(input_number INT) BEGIN DECLARE counter int; DECLARE out_number float; set counter=0; WHILE counter <= input_number DO SET out_number=SQRT(rand()); SET counter = counter + 1; END WHILE; END$$ DELIMITER ;
5. Load an initial data set from S3¶
Once connected to the DB cluster, run the following SQL queries to create an initial table:
DROP TABLE IF EXISTS `sbtest1`; CREATE TABLE `sbtest1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Next, load an initial data set by importing data from an Amazon S3 bucket:
LOAD DATA FROM S3 MANIFEST 's3-us-east-1://awsauroralabsmy-us-east-1/samples/sbdata/sample.manifest' REPLACE INTO TABLE sbtest1 CHARACTER SET 'latin1' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';
Data loading may take several minutes, you will receive a successful query message once it completes. When completed, exit the MySQL command line: