OPS345 Lab 4

THIS PAGE IS A DRAFT, NOT A REAL COURSE PAGE

The current schedule for OPS345 is here: OPS335_Weekly_Schedule

Databases

All computer data is stored as 1s and 0s, but noone has the time to write and read millions and billions of those just to see some pictures. Only people in the Matrix can look at a stream of binary data and see pictures. Real people need a library to convert that stream of data to RGB values for pixels in XY coordinates, and a video card to place those on a screen.

The same applies to apparently simple data like strings of text. You could store text as ASCII values in a plain-text file, but for most use cases data stored that way is not usable. For example the text you're reading here is not stored in a plain text file, it is stored in a relational database. That allows me to edit it via a web browser, adding formatting and images, allows you to view it in a web browser, enables features like the edit history, email notifications, etc.

Databases are not the only means to store user data, but they are the most common way to store organized text. For many years now and still today the most common database on the internet is MySQL, which was forked into MariaDB some years ago.

While you're not expected to become gain strong database skills in this course: as a system administrator you should know how to do basic setup tasks like setting up a server, creating a database and users, and setting up a pre-defined schema to be used by a web application. That's what this lab is about.

AWS RDS

You could install MariaDB in one of your AWS VMs (as you did in Vmware in OPS245) but Amazon provides a service called Relational Database Service (RDS). This service performs better, is more reliable, and is professionally administered by highly qualified AWS employees. So it would make no sense for us to compete with that, especially given how valuable data in a database typically is.

RDS supports many backends including most of popular SQL database engines (RDBMSs). We're going to create a MariaDB DB instance.

• In the AWS Management Console go to RDS.
• Under Databases click Create database.
• Pick Standard create, MariaDB, leave the default version
• Pick Free tier from Templates.
• Call the database ops345db.
• Change the default username to dbroot.
• Put in a long password, make sure it's a password that's only used for the database.

Database passwords
Database passwords are a breed of their own. While they are used to protect highly valuable data - unlike other passwords they are often written in plain text on the command-line, in shell scripts, and web service configuration files. Therefore to avoid having your entire account compromised: don't use the same password in a database that you use for other systems in AWS.

• Under Connectivity pick the vpc-ops345 VPC.
• Create a new security group named ops345dbsg.
• Pick us-east-1a for the availability zone.
• The defaults can be left for other options.
• Click Create database
• You'll get an error like this. Understanding it will take some time:

We don't have time in this course to learn about Availability Zones. The following should be enough for you to understand what the problem is. One of the reasons people choose to use AWS is global availability. You may be working in Toronto building an awesome website, but if your clients are in the southern USA, or europe, or asia - they will experience poor performance simply because of the distance. The earth is big enough that even if your data travelled at the speed of light - it would take it too long to get to the other side of the planet.

Amazon assumes that you're building something to be available in the entire world, and that's why RDS insists that your database be available in at least two availability zones.

Unfortunately for you: that means a lot more setup, but that's ok - it will give you more practice with AWS networking.

Add a second subnet

You need to create another subnet, and make sure that your second subnet is in a different availability zone from subnet-ops345.

• Leave the RDS Management Console and go to the VPC Management Console.
• Under Subnets, create a new one the same way you created subnet-ops345 in Lab 2.
  • Pick the VPC vpc-ops345.
  • Name it subnet-ops345-db.
  • Pick the us-east-1b availability zone so that the new subnet is in a different availability zone from subnet-ops345.
  • The CIDR for the new subnet will be a part of your VPC's subnet and not overlapping with subnet-ops345: 10.3.45.128/25

• Go back and repeat the steps to create your database. It should work this time.

It will take some time for your database to be provisioned. While that's happening you can move on to the next steps in the lab.

Set up Nextcloud

We're going to install a handy web application in order to get some practice setting up our database. Nextcloud has all kinds of capabilities, the most obvious one is that you can use it to share files (of any size) with specific users or make them public for anyone to access.

The subnet-ops345-db RDS instance you created is called a database, which is confusing, because inside it you're going to make a database. subnet-ops345-db is actually more akin to an RDBMS (Relational Database Management System). Like installing MariaDB on a linux box. Inside the RDBMS you create what's more commonly called a database by more technical people. You can have many databased on one database server. But you cannot have any database inside another database - that just doesn't make sense.

There are graphical applications for managing databases, and sometimes they're quite helpful. But for you it wouldn't be worth learning their interface for the basic things you need to do for the lab. Also the command-line commands you will learn in this lab are good because they will work on almost any MySQL/MariaDB database server.

You'll need some extra software to be able to connect to subnet-ops345-db from

• Assign temporary elastic IP to yum install mariadb, no need for server.
  • Will disassociate and release the elastic ip at the end of the lab.
• Do the rest of the lab as the regular user, don't use root.
• In the AWS Console go to RDS, and find the FQDN ("Endpoint") for the database. Such as ops345db.cobdogt5aykb.us-east-1.rds.amazonaws.com - record that.
• Try to connect: mysql -u root -plongdbpassword -h ops345db.cobdogt5aykb.us-east-1.rds.amazonaws.com
  • It won't work, the firewall (SG) won't allow it. Modify ops345sgprivate to allow incoming TCP port 3306 (MySQL) from ops345sgprivate.
• Should be able to log in now. If you get something like this: "ERROR 1045 (28000): Access denied for user 'root'@'10.3.45.11' (using password: YES)" then double-check the master username under RDS/ops345db/Configuration and you can reset the password via Modify (might take a few minutes to propagate).
  • See also https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Troubleshooting.html
• Most mysql commands need to have a semicolon at the end.
• The mysql commandline is nothing like the linux shell. Have to use mysql (or plain SQL) commands.
• Out of the box show databases; shows 4 databses, all of which are used internally by mysql, they are not for you to store data.
• use mysql; show tables; select * from user; select user from user;
• mysql root is not the same as the linux root, but it is an administrator and you should only use it for creating users, databases, and assigning permissions.
• Typically you would use something like this: " MariaDB> grant all privileges on DATABASE_NAME.* TO 'USER_NAME'@'%' identified by 'PASSWORD'; " but that won't work on AWS RDS because your root user doesn't have ALL PRIVILEGES, so can't grant them to another user.
• Instead create a user first: CREATE USER 'andrewdb'@'%' IDENTIFIED BY 'andrewdbpassword';
• Find what privileges your root has: show grants for 'root';
• Give the most important ones to your db user: " GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON firstdb.* TO 'andrewdb'@'%'; "
• Log out from root
• mysql -u andrewdb -pandrewdbpassword -h ops345db.cobdogt5aykb.us-east-1.rds.amazonaws.com
• show databases; - note that the list is shorter.
• use firstdb;
• show tables;
• Create tables, insert data, select: https://www.guru99.com/mariadb-tutorial-install.html#6
• A realistic use case for your career: download and instal nextcloud.
• wget the .tar.bz2 (not zip)
• Extract it into /var/www/html so you have a /var/www/html/nextcloud/index.html
• https://docs.nextcloud.com/server/latest/admin_manual/installation/source_installation.html
  • As root, vi /etc/httpd/conf.d/nextcloud.conf

    Alias /nextcloud "/var/www/html/nextcloud/"
    
    <Directory /var/www/html/nextcloud/>
      Require all granted
      AllowOverride All
      Options FollowSymLinks MultiViews
    
      <IfModule mod_dav.c>
        Dav off
      </IfModule>
    </Directory>

  • chown -R apache.apache nextcloud/
  • Get an error:

    This version of Nextcloud requires at least PHP 7.3
    You are currently running 5.4.16. Please update your PHP version.

  • amazon-linux-extras | grep php
  • amazon-linux-extras enable php7.4
  • yum clean metadata
  • yum install php-cli php-pdo php-fpm php-json php-mysqlnd
  • restart apache
  • Get module errors from website, install them:
    • amazon-linux-extras enable httpd_modules
    • yum install php-dom php-gd php-mbstring
  • Should now let you continue the setup.
  • Create an admin account. Use ops345admin/nextcloudadminpass
  • Create a new database and user and password nextclouddb/nextclouduser/nextclouddbpassword
    • CREATE DATABASE nextclouddb;
    • CREATE USER 'nextclouduser'@'%' IDENTIFIED BY 'nextclouddbpassword';
    • GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON nextclouddb.* TO 'nextclouduser'@'%';
  • Fill in the database details in the nextcloud web setup screen.
  • Look around inside the nextcloud, create user yoursenecaid, unlimited quota
  • Log in as yoursenecaid, upload a picture, share a link to it.
• When done with everything, release the elastic ip.
• Confirm that everything still works.
• As an extra challenge: get rid of the index.php in the URLs.