Assignment 5
Due Date: April 29
Value: 10 points total
Requirements
Part 1 (7 points)
Gererate a private/public key pair for your system root account to setup public-key authentication to the dediated database server.
Establish a SSH Tunnel to the dedicated MySQL server. Configure your system to automatically establish this tunnel at system boot.
Backup your Joomla database and import it to the dedicated MySQL server (delta.lt.unt.edu).
Disable your local MySQL server.
Install a web application that uses PHP and MySQL so that it is accessible via your second FQDN (e.g. http://beta-vh.lt.unt.edu). This new web application will use the dedicated MySQL database server (delta.lt.unt.edu) to store its database. Some suggested applications include (but are not limited to):
Part 2 (3 points)
Locate and read, from any professional journal, two articles that discuss an Internet service that we have yet to cover in the course that you think is interesting or would be useful in education. Write a two paragraph review for each article. The 1st paragraph should describe the contents of the article. The 2nd paragraph should be your reaction to the article. Use APA format, and only one article review per post on Moodle.
Reply to at least two postings in a meaningful manner.
Grading Method
A Joomla installation should still be accessible via your main FQDN (e.g. http://beta.lt.unt.edu).
A new web application should be accessible via your secondary FQDN (e.g. http://beta-vh.lt.unt.edu).
Your system should have a SSH tunnel to the dedicated MySQL database server (delta.lt.unt.edu), and should automatically establish this tunnel at system boot.
Assignment Instructions
Configuring the Web Host
Since we are migrating to a centralized database server, we need to backup our existing application databases (the Joomla database, in this case) so that we can import it on the new server.
Using the mysqldump command
[vis0001@beta ]$ mysqldump -u root -p joomla > /home/vis0001/joomla.sqlUsing phpMyAdmin
Log into phpMyAdmin using the MySQL root account. Once logged in, select the Joomla database from the list of databases on the left-hand side.
Once the database has been selected, click the Export link at the top of the right-hand view.
The default settings are sufficient, so just click the Go button and save the file to your home directory when prompted (e.g. /home/vis0001).
Take care to only backup your Joomla database and not the entire selection of databases ('information_schema', 'mysql', 'performance_schema', 'test'), because the attempt to import your Joomla database will fail on the dedicated MySQL server, as your specified accounts do not have permission to write to those databases.
Once your Joomla database is backed up, you can shut down your local MySQL daemon and disable the service from starting automatically at boot:
[vis0001@beta ]$ sudo service mysqld stop [vis0001@beta ]$ sudo chkconfig mysqld off
Next, in order for our SSH tunnel to be established without having to be created manually, we'll be using public key authentication, and in order to do so, we must create the keys for our root account:
[vis0001@beta ]$ su - Password: [root@beta ]# ssh-keygen -t rsa
Hit Enter to save to the default path (/root/.ssh), and hit Enter when it asks for a passphrase (no passphrase): DO NOT ENTER A PASSPHRASE!
Now we must transfer the public key for the root account to the database server. Since the server has been configured to not allow the root account to log in over SSH, you'll have to use your regular user account:
[root@beta ]# scp /root/.ssh/id_rsa.pub vis0001@delta.lt.unt.edu:beta_id_rsa.pub [root@beta ]# exit
While we're still on the host system, we can go ahead and make some changes to phpMyAdmin's configuration file (/etc/phpMyAdmin/config.inc.php).
Change this line:
$cfg['Servers'][$i]['host'] = 'localhost';To this:
$cfg['Servers'][$i]['host'] = '127.0.0.1';Also, while we're here, we need to change an SELinux boolean so that Apache can connect to the MySQL database over the network:
[vis0001@beta ]$ sudo setsebool -P httpd_can_network_connect_db 1
Configuring the Remote Database Server
We've done all we can do on our web host for now, so we'll have to move on to the dedicated database server (delta.lt.unt.edu). For this course, the first six steps (in red) have already been done, so DO NOT DO THESE NOW.
Install the MySQL client and server packages:
[vis0001@delta ]$ sudo yum groupinstall 'MySQL Database server' 'MySQL Database client'
Edit the MySQL configuration file (/etc/my.cnf).
Find this line:
socket=/var/lib/mysql/mysql.sockComment it out and add the following line:
#socket=/var/lib/mysql/mysql.sock bind-address=127.0.0.1Start the MySQL daemon and set it to run at boot:
[vis0001@delta ]$ sudo service mysqld start [vis0001@delta ]$ sudo chkconfig mysqld on
Set password for the MySQL root account:
[vis0001@delta ]$ mysql -u root Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 141 Server version: 5.1.66 Source distribution Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SET PASSWORD FOR root@localhost=PASSWORD('MYSQL_ROOT_PASSWORD_HERE'); Query OK, 0 rows affected (0.00 sec) mysql> exit ByeCreate a user account for the SSH tunnel creation (NOTE: you can name this account anything you want):
[vis0001@delta ]$ sudo useradd tunnel [vis0001@delta ]$ sudo passwd tunnel Changing password for user tunnel. New password: Retype new password: passwd: all authentication tokens updated successfully. [vis0001@delta ]$ sudo mkdir /home/tunnel/.ssh [vis0001@delta ]$ sudo chown -R tunnel:tunnel /home/tunnel/.ssh [vis0001@delta ]$ sudo chmod 700 /home/tunnel/.ssh [vis0001@delta ]$ sudo chmod 600 /home/tunnel/.ssh/authorized_keys
We'll have to set an SELinux boolean to allow SSH to forward ports through the tunnel:
[vis0001@delta ]$ sudo setsebool -P sshd_forward_ports 1
Now you will have to copy the public key we copied earlier to the authorized_keys file for the SSH tunnel account:
[vis0001@delta ]$ sudo bash [sudo] password for vis0001: [root@delta ]# cat /home/vis0001/id_rsa.pub >> /home/tunnel/.ssh/authorized_keys [root@delta ]# exit
Final Steps on the Web Host
Back on the web host, we'll first established the SSH tunnel:
[vis0001@beta ]$ su - Password: [root@beta ]# ssh -fNL 3306:127.0.0.1:3306 tunnel@delta.lt.unt.edu [root@beta ]# exitAccessing your MySQL databases will now require a different username and password, and the host address will need to be 127.0.0.1 instead of localhost. Each server will have their own MySQL user account:
- Username: servername + "_user" (e.g. beta_user)
- Password: same as system root password
Each system will also have two databases:
- servername + "_assign4" (e.g. beta_assign4)
- servername + "_assign5" (e.g. beta_assign5)
Import your Joomla database dump into the appropriate database on delta.lt.unt.edu:
Using the mysql client
[vis0001@beta ]$ mysql -h 127.0.0.1 -u beta_user -p beta_assign4 < /home/vis0001/joomla.sql
Using phpMyAdmin
Log into phpMyAdmin using the new credentials specified above. Once logged in, select the database on the left-hand side that corresponds to your server's Assignment 4 database.
Once the database has been selected, click the Import link at the top of the right-hand view.
In the File to Import section, click Browse your computer, select the .sql dump file, and click the Go button.
We must make some changes to the Joomla configuration file so that it can access the new database.
Replace the values of the following object properties:
public $host = 'localhost'; public $user = 'joomla_user'; public $password = 'HfjwjHaS4qzfNWnR'; public $db = 'joomla';With info similar to this:
public $host = '127.0.0.1'; public $user = 'beta_user'; public $password = 'betasa1!'; public $db = 'beta_assign4';In order for our SSH tunnel to automatically establish at system boot, we need to edit the file /etc/rc.d/rc.local:
#!/bin/sh touch /var/lock/subsys/local ssh -fNL 3306:127.0.0.1:3306 tunnel@delta.lt.unt.eduYou are now ready to download and install your PHP and MySQL-based web application for Assignment 5.