OPS705 Lab 8 (2207)

LAB PREPARATION

Purpose / Objectives of Lab 8

In this lab, you will learn how to modify the MySQL database created in Lab 7 to add a new user with the wordpress Author permission. You will also create a new RDS MySQL database from scratch using the same setup at the first database.

While you are working through this lab, it is highly recommended that you write down general notes and commands to help you remember how to do this lab. You may use your notes during tests, so fill it out accordingly!

If you encounter technical issues, please contact your professor via e-mail or in your section's Microsoft Teams group.

Minimum Requirements

Before beginning, you must have:

1. Working knowledge of databases and MySQL commands
2. A MySQL client on your host machine
3. Successfully completed Lab 7
4. Watched the Week 8 video lecture
5. Your AWS Educate login credentials

INVESTIGATION 1: Modifying the RDS MySQL Database from Lab 7

In this investigation, you'll modify the database created for your Wordpress installation from Lab 7 to add a new user to the blog.

Part 1: Finding the Database

1. Login to the AWS Console. Under Services, click on Elastic Beanstalk.
2. Under Environments, click your Wordpress environment name.
3. In the left-hand menu, click on Configuration.
4. Scroll down to Database, make note of the database name in the endpoint link (the first part), then click the link.
5. In the new RDS screen, you should see a database with the same name. Click it.
6. You now see information about the database you created.
7. This page includes information you'll need to connect to it and run MySQL queries. However, you can't connect to it under its current settings. Part 2 will show you how to set up access.

Part 2: Modifying the VPC Security Group

1. On the main information page of the database from Part 1, find the Security header section.
2. Underneath, there's a single entry for VPC security groups. Click it.
3. In the new Security Groups page, click the only listed entry.
4. Now, click the Edit inbound rules button.
5. Click the Add rule button.
6. For the new rule, use the following settings:
   • Type: MYSQL/Aurora
   • Protocol: Leave defaults
   • Port range: Leave defaults
   • Source: My IP
   • Description: Leave blank
7. Click on Save rules to apply your settings.

Part 3: Connecting to the Database

1. From the AWS Console, select the RDS service.
2. Navigate to your database.
3. Using your favourite MySQL database client (GUI or CLI) and the information from the database information page, connect to your database. Use the following as a template, but supply your own information.
   • Host: something.us-east-1.rds.amazonaws.com (The database endpoint from the info page.)
   • Port: 3306
   • Username: admin
   • Password: Saved password from Lab 7
4. If you need a MySQL database client, this cross-platform open-source client is a good choice: https://mariadb.com/downloads/#mariadb_platform

Part 4: Adding a Secondary Author User To Your Blog

1. Once logged in to the MySQL server, navigate to the ebdb database.
2. List all tables to get an idea what's here.
3. Let's take a look at the wp_users table description and current entries.
4. If using the CLI, enter the following commands:
   • DESCRIBE wp_users;
   • SELECT * FROM wp_users;
5. Take a look at the wp_usermeta table. We'll be using both.
6. Add a new user with the following command (but supply your own username, password, e-mail, blog URL, and current date!): INSERT INTO wp_users (ID, user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_status, display_name) VALUES (2, 'cjohnson31', MD5('apassword'), 'cjohnson31', 'cjohnson31@myseneca.ca', 'http://wordpress-env.eba-mukvj5kp.us-east-1.elasticbeanstalk.com', '2020-12-01 03:47:00', 0, 'cjohnson31');
7. List your wp_users table entries again to verify your work.
8. Give your new user the Author role with the following command: INSERT INTO wp_usermeta (umeta_id, user_id, meta_key, meta_value) VALUES (NULL, '2', 'wp_capabilities', 'a:1:{s:6:"author";s:1:"1";}');
9. Once complete, load your blog and login as your new user. You should be able to login as the new user and make a blog entry.
10. Make a blog entry as this new user. In the entry, paste all the (modified) MySQL commands you used and publish the entry.
11. Back in the MySQL server, create a third user with the following exact commands (but use your blog URL):
    • INSERT INTO wp_users (ID, user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_status, display_name) VALUES (3, 'chris.johnson', '$P$BpUviJtx.VK1cZHEwIFZQKJMlq3One0', 'chris.johnson', 'chris.johnson@senecacollege.ca', 'http://yourblog.us-east-1.elasticbeanstalk.com', '2020-12-01 03:47:00', 0, 'Prof. Chris Johnson');
    • INSERT INTO wp_usermeta (umeta_id, user_id, meta_key, meta_value) VALUES (NULL, '3', 'wp_capabilities', 'a:1:{s:6:"author";s:1:"1";}');
12. You're done Investigation 1!

INVESTIGATION 2: Create A Second Database Instance

In this investigation, you will create a new MySQL instance in AWS RDS, and write down its information.

Part 1: Create A Second MySQL Instance

1. Navigate to the RDS service in AWS.
2. Click on the Databases link in the left-hand menu bar.
3. On the new page, click the Create database link button.
4. In the new window, use the following settings (leave all others on defaults):
   • Choose a database creation method: Standard create
   • Engine type: MySQL
   • Templates: Free Tier
   • DB instance identifier: wordpress-db2
   • Master username: admin
   • Master password: Choose a strong password
   • Confirm password: Same as above
   • Allocated storage: 5 GB
   • Enable storage autoscaling: Unchecked
   • Public access: Yes
   • New VPC security group name: lab8
   • VPC security group: Create new
   • (Under Additional configuration) Initial database name: ebdb
   • Enable automatic backups: Unchecked
5. Create the database.
6. Once complete, add a second Inbound rule entry to your new security group (lab8) as you did in Investigation 1, Part 2. (Note: If the proper inbound rule already exists, you don't have to create it again.)
7. To finish, connect to your new database.

Part 2: Record the Database Connection Info

Write down the connector information for this second database. You'll need it for Lab 9.

Lab Submission

Submit to Blackboard's Lab Submission section full-desktop screenshots (PNG/JPG) of the following:

1. List of table entries in wp_users from Investigation 1 showing all three users. (CLI or GUI)
2. Logged in as your first user, a view of the Users page on your Wordpress blog showing all three users.
3. The new blog entry from your second user.
4. The RDS database info page for the database from Investigation 1.
5. The RDS database info page for the second database created in Investigation 2.
6. Connection to secondary database instance.

In the Comments text box for your submission on Blackboard, include the URL of your blog post.

Your professor will review your blog directly; the screenshots are a backup in case of catastrophic issues.

Labs aren't marked until screenshots have been submitted.