|
|
| (27 intermediate revisions by the same user not shown) |
| Line 1: |
Line 1: |
| − | = THIS PAGE IS A DRAFT, NOT A REAL COURSE PAGE =
| + | [http://wiki.littlesvr.ca/wiki/OPS345_Lab_4 This page has moved.] |
| − | | |
| − | ''' 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".
| |
| − | *
| |
| − | | |
| − | * In the AWS console go to RDS. Create ops345db, with a long password that's different from other passwords. Put it in ops345sgprivate. This one will be used/seen in plain text sometimes.
| |
| − | ** It will complain about not having two subnets in different availability zones.
| |
| − | * In VPC/Subnets, create a new one in vpc-ops345 named subnet2-ops345, in us-east-1b instead of 1a. 10.3.45.128/25
| |
| − | * Go back and try again to create the database.
| |
| − | * 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 <source>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></source>
| |
| − | ** chown -R apache.apache nextcloud/
| |
| − | ** Get an error: <source>This version of Nextcloud requires at least PHP 7.3
| |
| − | You are currently running 5.4.16. Please update your PHP version.</source>
| |
| − | ** 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.
| |
| − | | |
| − | [[Category:OPS345]]
| |
