COSC2406/2407 Objectives
Database Systems
Access your AWS linux instance
Installing MongoDB on your linux instance Installing Apache Derby on your linux instance
AWS linux instance
Week 2 Lab Tasks
We will be using the Amazon AWS cloud as our main teaching environment for the Database Systems course:
● Every student in the course will be assigned an individual machine on which to
install database systems and implement database components in Java
● You will be given a key in a file called sXXXXXXX-cosc2406.pem that will grant you root
access to your specific machine
● You are responsible for securely maintaining your own key and your machine
● You are also responsible for maintaining backup of any important files (in the event
of a system failure you may not be able to retrieve any files on your machine)
● Keys MUST NOT be shared between students
Make sure you securely save a permanent copy of your sXXXXXXX-cosc2406.pem file as you will need it every time you access your AWS instance!
In the unlikely event that your AWS Linux VM crashes and does not reboot or if you have other similar problems, you should contact RMIT ITS support online via \url{https://rmit.service-now.com/serviceandsupport/}, and make sure you include in the Subject field:
“COSC2406 – Database Systems – Student VM Issue sXXXXXXX.cosc2406.route53.aws.rmit.edu.au”
Connecting to your AWS linux instance
Connecting from a Windows machine (only works while at the university):
(1) Before connecting (for the first time from Windows), you will need to convert your sXXXXXXX-cosc2406.pem file to a .pkk using puttygen.
(2) If using putty, use the following settings
● hostname: ec2-user@sXXXXXXX.cosc2406.route53.aws.rmit.edu.au
● connection/ssh/auth –> point to your PPK file (private key) ● connection/proxy:
proxyType: HTTP proxyHostname: aproxy port: 8080
Please note you cannot connect to your aws instance from outside the university directly as the service is firewalled. This has been done to simplify management of security of your instance as an attacker would need to break into the university first (although that has been done before – relatively recently). For this reason it is encouraged for you to store your key on titan/jupiter/saturn and connect via there to your amazon instance.
Connecting from titan / jupiter / saturn
with the -i flag to the ssh command, you can use the .pem key you have been provided with to connect to your amazon instance.
For example: if your student id was 34123423:
login to titan / jupiter or saturn using the normal way. If you have forgotten how to do this, consult the UNIX Survival guide available here: https://jupiter.csit.rmit.edu.au/~e70949/inductionguide.pdfError! Hyperlink reference not valid.
You will also need to upload the .pem key to the server and ensure its permissions are only 600 (that is, only you have read and write permissions to it – any more permissions and it won’t work with ssh as it is rather strict with security). We would suggest using winscp on windows (https://winscp.net/eng/index.php) or cyberduck on the mac (https://cyberduck.io/).
Once done, change the permissions of the file using chmod:
chmod 600 pemfile
where pemfile is the name of the .pem file you have uploaded.
Enter the following command (replace the student number here with your own): ssh -i pemfile ec2-user@s34123423.cosc2406.route53.aws.rmit.edu.au
After connecting to your AWS linux instance
When you log into your AWS instance (for the first time or when updates are available), it will prompt you to run:
sudo yum update
which you should run. This will update the database of software on the server and download the latest versions of important software.
Installing MongoDB on your AWS linux instance
MongoDB is an example of a NoSQL database system. To read about MongoDB the documentation is at https://docs.mongodb.com/manual/ , how does it differ from a relational database?
To install MongoDB on your AWS linux instance, follow directions given at the following url:
https://docs.mongodb.org/manual/tutorial/install-mongodb-on-amazon/
If you follow the instructions on the above link, please be sure to click on the “Amazon Linux (2013.03+)” tab and not the “Amazon Linux 2” tab as the “Amazon Linux 2” instructions won’t work as that’s not the machine type we are using.
Some tips on the key steps.
To create the yum repo file use an editor (such as vi/vim)
sudo vim /etc/yum.repos.d/mongodb-org-4.2.repo
to insert the following 6 lines into the file
[mongodb-org-4.2]
name=MongoDB Repository baseurl=https://repo.mongodb.org/yum/amazon/2013.03/mongodb-org/4.2/x86_64/ gpgcheck=1
enabled=1
gpgkey=https://www.mongodb.org/static/pgp/server-4.2.asc
Then the following command should install mongodb (it takes a little while to finish)
sudo yum install -y mongodb-org
Try running the mongodb:
sudo service mongod start
To run a mongo shell (see https://docs.mongodb.com/manual/mongo/ ):
mongo
If you have time, experiment with inserting a document and writing a query to retrieve it. The “help” command shows a list of commands in the mongo shell.
Installing Apache Derby on your AWS linux instance
Apache Derby is an open source relational database written in Java. You can read about its history on Wikipedia https://en.wikipedia.org/wiki/Apache_Derby. The source code is accessible – you can view the code but do not need to download it now http://svn.apache.org/repos/asf/db/derby/code/branches/10.13/java/engine/org/apache/derby/
First you need to update the java version on your AWS instance
sudo yum install java-1.8.0-openjdk-devel sudo yum remove java-1.7.0-openjdk
Then follow these directions to install DerbyDB:
https://db.apache.org/derby/papers/DerbyTut/install_software.html
In particular, to download the bin distribution:
wget http://apache.mirror.amaze.com.au/db/derby/db-derby-10.14.2.0/db-derby-10.14.2.0-bin.tar.gz
to unpack the distribution in the directory /opt/Apache
sudo mkdir /opt/Apache
sudo cp db-derby-10.14.2.0-bin.tar.gz /opt/Apache cd /opt/Apache
sudo tar xzvf db-derby-10.14.2.0-bin.tar.gz
add the following lines to the ~/.bashrc file on AWS instance:
export DERBY_INSTALL=/opt/Apache/db-derby-10.14.2.0-bin
export DERBY_HOME=$DERBY_INSTALL
export CLASSPATH=$DERBY_INSTALL/lib/derby.jar:$DERBY_INSTALL/lib/derbytools.jar:.
and then run
source ~/.bashrc
Then to complete installation
cd $DERBY_INSTALL/bin . setEmbeddedCP
Now to verify it works run
java org.apache.derby.tools.sysinfo
If time permits continue on to the interactive SQL tool
https://db.apache.org/derby/papers/DerbyTut/ij_intro.html
Before starting the interactive SQL return to your home directory:
cd
and the interactive SQL can be then invoked with the following command:
java org.apache.derby.tools.ij
Finally, make everything runnable by the user you logged in as:
Run the following command to change the owning user to ec2-user: sudo chown -R ec2-user:ec2-user /opt/Apache