Getting Started with AWS

Boosting MySQL database performance

with Amazon ElastiCache for Redis

Module 3: Populate your MySQL database

Run your first MySQL table with a pre-populated SQL script.

What You Will Learn

  • How to connect, populate, and run your first MySQL table with a pre-populated SQL script

 Time to Complete

10 minutes

 Services Used

Implementation

  • Populate the database

    You can populate the database with the seed.sql file provided in the tutorial repository. Log into your EC2 instance and run this command:

    syntax: shell

    $ mysql -h endpoint -P 3306 -u admin -p < seed.sql

    If the command hangs, chances are you are being blocked by the Security Group settings. Verify that your EC2 instance has access to the security group assigned to your MySQL instance. For example, let’s say your EC2 instance was assigned to the default security group. You can now modify the security group of your MySQL instance, edit the Inbound rules and add a MYSQL/Aurora rule allowing connections on port 3306 from any instance in the default security group:

    Edit inbound rules

    In Source, you can start typing the name of the security group and you’ll be able to click on the Security Group ID. If you need to learn more about Security Groups, you can check the documentation or the Security Group Rules Reference

    Below is a breakdown of the commands contained in the seed.sql file. If you succeeded in populating the database, you can skip the steps below and go directly to step 4.

    3.1 — Connect to your database:

    syntax: shell

    $ mysql -h endpoint -P 3306 -u admin -p

    When prompted for a password, enter the password you saved in step 2.19.

    3.2 — Create a database.

    syntax: SQL

    mysql> CREATE database tutorial;
    
    Query OK, 1 row affected (0.01 sec)

    At this point you can use the tutorial database, create tables and add some records.

    syntax: SQL

    mysql> USE tutorial;
    
    Database changed
    
    mysql> CREATE TABLE planet (
        -> id INT UNSIGNED AUTO_INCREMENT,
        -> name VARCHAR(30),
        -> PRIMARY KEY(id));
    
    Query OK, 0 rows affected (0.057 sec)
    
    mysql> INSERT INTO planet (name) VALUES ("Mercury");
    
    Query OK, 1 row affected (0.008 sec)
    
    mysql> INSERT INTO planet (name) VALUES ("Venus");
    
    Query OK, 1 row affected (0.011 sec)
    
    mysql> INSERT INTO planet (name) VALUES ("Earth");
    
    Query OK, 1 row affected (0.009 sec)
    
    mysql> INSERT INTO planet (name) VALUES ("Mars");
    
    Query OK, 1 row affected (0.009 sec)
    
    mysql> INSERT INTO planet (name) VALUES ("Jupiter");
    
    Query OK, 1 row affected (0.008 sec)
    
    mysql> INSERT INTO planet (name) VALUES ("Saturn");
    
    Query OK, 1 row affected (0.010 sec)
    
    mysql> INSERT INTO planet (name) VALUES ("Uranus");
    
    Query OK, 1 row affected (0.009 sec)
    
    mysql> INSERT INTO planet (name) VALUES ("Neptune");
    
    Query OK, 1 row affected (0.009 sec)

    In the next steps you will use the planet table in the tutorial database.

Was this module helpful?

Thank you
Please let us know what you liked.
Close
Sorry to disappoint you
Is something out-of-date, confusing or inaccurate? Please help us improve this tutorial by providing feedback.
Close

Caching and Best Practices