|
|
| (16 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'''.
| |
| − | * 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.
| |
| − | {{Admon/important|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:
| |
| − | | |
| − | [[File:AWSCreateDBError.png|border|center]]
| |
| − | | |
| − | 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'''
| |
| − | [[File:AWSSecondSubnet.png|border|center]]
| |
| − | * 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 ops345db RDS instance you created is called a database, which is confusing, because inside it you're going to make a '''database'''. ops345db is actually more akin to an '''RDBMS''' (Relational Database Management System). Like installing MariaDB on a linux box. It would be better called a '''database server'''. Inside the RDBMS you create what's more commonly called a database by more technical people. You can have many databases on one database server. But you cannot have any database inside another database - that just doesn't make sense, at least not with SQL databases.
| |
| − | | |
| − | 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 ops345db from your www server.
| |
| − | | |
| − | * Use ssh to connect to www.
| |
| − | * Install '''mariadb'''. This is the client software for MariaDB. You do not need the server software, the server is already set up in AWS.
| |
| − | * Note that in order to install anything with yum: www needs to connect to the internet. So create a temporary elastic IP and assign it to www, just as you did in the end of the previous lab.
| |
| − | * Disassociate the elastic IP from www after your yum install. You will need it again later, so don't delete it yet.
| |
| − | | |
| − | {{Admon/tip|Don't be lazy|The only way to learn this stuff is to practice it. So practice it, don't skip steps. In fact: make up your own extra steps to give yourself more practice (a.k.a. experience). That's what employers pay for. Nobody in the industry will care what grades you got in school. They will care quite a bit about what you are able to do, and how willing and able you are to learn more.}}
| |
| − | | |
| − | * As a fundamental security principle: '''do the rest of the lab as a regular user, don't use root'''.
| |
| − | * To run the MariaDB client you need to know what to connect to. Find the FQDN ("endpoint") for ops345db in the web interface:
| |
| − | [[File:AWSDBFQDN.png|800px|border|center]]
| |
| − | * And connect to it from www:
| |
| − | <source>mysql -u dbroot -pyourdbpassword -h ops345db.cnjsjcelkwzu.us-east-1.rds.amazonaws.com</source>
| |
| − | * It will almost certainly time out. That's because ops345db is behind a firewall, with rules defined in the ops345dbsg security group.
| |
| − | * Find your way to the Inbound rules of ops345dbsg.
| |
| − | ** Delete the existing rule.
| |
| − | ** Add a new rule for MySQL (TCP port 3306), with the source ops345wwsg. That's the only machine that needs direct access to the database server.
| |
| − | * Try again to connect using the mysql client software. It should work this time.
| |
| − | ** If you get an error like this then double-check the master username under RDS/ops345db/Configuration and you can reset the password via the Modify button on the same page (the password change might take a few minutes to complete).
| |
| − | <source>ERROR 1045 (28000): Access denied for user 'root'@'10.3.45.11' (using password: YES)</source>
| |
| − | [[File:AWSDBUserPass.png|800px|border|center]]
| |
| − | * See also [https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Troubleshooting.html Troubleshooting for Amazon RDS].
| |
| − | | |
| − | == Some basic SQL ==
| |
| − | | |
| − | You're going to learn some basic SQL commands to..........
| |
| − | | |
| − | Though the SQL console looks a little bit like the Linux commandline: they have almost nothing in common. In the SQL console you can write some MariaDB commands, but most of the time you write SQL statements. You can't execute any shell commands. SQL statements (queries) end with a semicolon, to make it clear that you're not trying to run a multi-line query.
| |
| − | | |
| − | * Out of the box your ops345db has four databases, all of which are used internally by MariaDB. They are not used to store data for your applications. Run this:
| |
| − | <source>MariaDB [(none)]> show databases;
| |
| − | +--------------------+
| |
| − | | Database |
| |
| − | +--------------------+
| |
| − | | information_schema |
| |
| − | | innodb |
| |
| − | | mysql |
| |
| − | | performance_schema |
| |
| − | +--------------------+
| |
| − | 4 rows in set (0.00 sec)
| |
| − | </source>
| |
| − | * To run SQL queries you need to choose the database you're working with. Let's look at the "mysql" database:
| |
| − | <source>use mysql;
| |
| − | MariaDB [(none)]> use mysql;
| |
| − | Database changed</source>
| |
| − | * Now let's see what tables are inside this database:
| |
| − | <source>MariaDB [mysql]> show tables;
| |
| − | +-------------------------------+
| |
| − | | Tables_in_mysql |
| |
| − | +-------------------------------+
| |
| − | | column_stats |
| |
| − | | columns_priv |
| |
| − | | db |
| |
| − | | event |
| |
| − | ...
| |
| − | | transaction_registry |
| |
| − | | user |
| |
| − | +-------------------------------+
| |
| − | 40 rows in set (0.00 sec)
| |
| − | </source>
| |
| − | An SQL table is made of columns and rows. The columns are part of the definition of the table. The rows are the data that you can insert into the table. You will never look at most of these particular tables, but one of them is interesting.
| |
| − | * Show all the rows (*) in the '''user''' table:
| |
| − | <source>SELECT * FROM user;</source>
| |
| − | * This table has many columns, you can limit which columns you want to see for each row by specifying them explicitly. Choose some columns from the previous output that looks interesting and display just those:
| |
| − | <source>MariaDB [mysql]> SELECT User,Password,password_expired from user;
| |
| − | +-------------+-------------------------------------------+------------------+
| |
| − | | User | Password | password_expired |
| |
| − | +-------------+-------------------------------------------+------------------+
| |
| − | | mariadb.sys | | Y |
| |
| − | | rdsadmin | *0BC5B85E564E990A9F12E40305716A5E3B2D703E | N |
| |
| − | | dbroot | *0463BE0B12D9D44F189C02D447529D16242028CD | N |
| |
| − | +-------------+-------------------------------------------+------------------+
| |
| − | 3 rows in set (0.00 sec)
| |
| − | </source>
| |
| − | | |
| − | The mysql '''dbroot''' user is not the same as the '''root''' user on the Linux machine that's running MariaDB. But it is the administrator of the entire database server, and therefor has access to all the data in all the tables, in all the databases. You should only user the root user for creating other users, creating and deleting databases, and for assigning permissions.
| |
| − | | |
| − | == Create an SQL user ==
| |
| − | | |
| − | Just as root: regular users in the DBMS are not in any way related to Linux users in /etc/passwd. Typically you will have on SQL user for each web application you have using the database server. SQL users also have SQL permissions, which have nothing to do with Linux permissions - they describe what operations that SQL user can do, on which databases.
| |
| − | | |
| − | If you were running your own MariaDB - you would create a user and assign p
| |
| − | | |
| − | <source></source>
| |
| − | <source></source>
| |
| − | <source></source>
| |
| − | | |
| − | * 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]]
| |
