Planning and enabling a database service is a very important part of a system administrator's work. Planning requires determining which type of database server to run, how much hard disk space is needed, types of hardware to use, types of filesystems to use, backup plans, and security. Most large organizations hire a database administrator that the system administrator must work with to determine these things.

In this project you will setup and configure MySQL (pronounced as my-ess-cue-ell, and available from www.mysql.org and www.mysql.com) and Postgres (also known as PostgreSQL, and available from www.postgresql.org) relational database management systems (RDBMS or DBMS, or simply database server). These are very popular database servers, available as open source. For this project we won't need any special planning, and we will use all default configuration options. However you will need to make sure they have been correctly secured.

You will also create a simple database in each DBMS. Make sure you keep an accurate system journal of any and all changes you make to your system! You will need to turn this in, along with the answers to the questions asked below.

When in doubt remember to use the man pages (and other informational resources), including for file descriptions, and use the keyword search feature if in doubt as to the name of some command. Do not hesitate to communicate with your instructor if you wish any help. Some background material can be found at Database Overview for System Administrators.

Answer the following questions and perform the following tasks:

Part I — MySQL

What MySQL related packages are installed on your system? How did you determine that?
What package(s) are needed to provide the MySQL server and the MySQL client tools? How did you determine this? (Hints: You will need at least two packages. You can use the "search" feature of yum, use some GUI tool to list available packages (related to MySQL), or use information obtained from the Internet.)
Install any required packages using yum.
Since MySQL is a stand-alone server, examine the files in the directory /etc/init.d to determine the service name (that is, the name of the script that manages that service). If you don't see it there, it means that you either didn't install MySQL, or your system has migrated the service from the old SysV init to some newer init system. What is the service name of the MySQL server on your system? How did you find that name? (Hint: Fedora 16 uses the systemd init system, which you can manage using the systemctl command.)
Using the skills you learned by enabling other services, enable MySQL service both now, and to automatically start at each boot-up. Determine if MySQL uses TCP Wrappers and, if necessary, re-configure TCP Wrappers to allow access from localhost. What steps exactly did you do (i.e., show all commands you ran to determine if TCP Wrappers is used by MySQL, and if so, what changes did you make to enable access from localhost)? How will you summarize those steps in your system journal entry?
MySQL has a default configuration with an administrator account (predictably named root) enabled, with no password. MySQL doesn't use the system user IDs and passwords. However, if you don't say which user to use, MySQL will guess to use $USER. As with most database servers, it keeps its own list of users, passwords, and privileges.
Test the server by running:
```
# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.5.20

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> exit
Bye
```
This shows that MySQL is running, and that the user root has access with no password required.
MySQL stores all users, passwords, and privilege information in a database named mysql. While you could log into MySQL as root and manage users and permissions by standard SQL statements on this database, an easier way is provided.
Use the mysqladmin command to set a password for the root user, and then refresh the server's tables. As in real life you may not have exact directions for such a task, but by knowing the command to use, you must work it out by reading the man pages. What are the exact (two) steps you must take to set a password for the root user, and to refresh the server?
(Note that once you have set a password, you will need to add the -p option to have mysql command prompt you to enter a password, like so: “mysql -u root -p”)

MySQL by default has a database configured named test, which provides all privileges to all users. However in real life each application will usually require its own database and one or more user accounts (administrators with all privileges on that DB, and users who can access and/or update the data in that DB.) Follow these steps to create and use a table in a new database, as a new (privileged) user:

Create a new database:

$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.5.20

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.02 sec)

mysql> create database addrbook;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+----------+
| Database |
+----------+
| addrbook |
| mysql    |
| test     |
+----------+
3 rows in set (0.01 sec)

mysql>

Create new MySQL users, one with administrator privileges on the new database, and one with limited access (to read and update the data only):
```
mysql> grant all privileges on addrbook.*
    -> to 'addrbook-admin'@'localhost' identified by 'secret'
    -> with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> grant select,insert,update,delete on addrbook.*
    -> to 'addrbook-user'@'localhost' identified by 'secret';
Query OK, 0 rows affected (0.00 sec)

mysql>
```
Also add a user named addrbook-ro for a user with read-only access to the tables in the addrbook database. Exactly what command(s) did you run to do this? (Hint: The SQL command that allows a user to view (but not modify) data is SELECT.)

Remember that to change user passwords easily you should use the mysqladmin command. However you can also run the following SQL statements to change the current user's password:
```
mysql> set password for auser@localhost=password('new-password');
```

Next create a table in this database, using the new addrbook-admin user, and add a row of data:

$ mysql -u addrbook-admin -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.5.20

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use addrbook;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table foo (
    -> NAME varchar(22) not NULL,
    -> PHONE varchar(14),
    -> NOTES varchar(255)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> describe foo;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| NAME  | varchar(22)  |      |     |         |       |
| PHONE | varchar(14)  | YES  |     | NULL    |       |
| NOTES | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from foo;
Empty set (0.00 sec)

mysql> insert into foo values (
    -> 'Prof. Pollock',
    -> '253-7213',
    -> 'CTS-2301 instructor'
    -> );
Query OK, 1 row affected (0.00 sec)

mysql> \q
Bye

Next add some more data to the table, this time using the addrbook-user account (note the use of the database name on the command line, to save a step):

$ mysql -u addrbook-user -p addrbook
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.5.20

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> insert into foo values (
    -> 'Dean Johnson',
    -> '253-7479',
    -> 'Dean of AS programs at Dale Mabry campus of HCC'
    -> );
Query OK, 1 row affected (0.00 sec)

Now lookup the data:

mysql> select * from foo;
+---------------+----------+-------------------------------------------------+
| NAME          | PHONE    | NOTES                                           |
+---------------+----------+-------------------------------------------------+
| Prof. Pollock | 253-7213 | CTS-2301 instructor                             |
| Dean Johnson  | 253-7479 | Dean of AS programs at Dale Mabry campus of HCC |
+---------------+----------+-------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from foo\G
*************************** 1. row ***************************
 NAME: Prof. Pollock
PHONE: 253-7213
NOTES: CTS-2301 instructor
*************************** 2. row ***************************
 NAME: Dean Johnson
PHONE: 253-7479
NOTES: Dean of AS programs at Dale Mabry campus of HCC
2 rows in set (0.00 sec)

mysql> select name, phone from foo
    -> where notes like "%instructor%";
+---------------+----------+
| name          | phone    |
+---------------+----------+
| Prof. Pollock | 253-7213 |
+---------------+----------+
1 row in set (0.00 sec)

mysql> \q
Bye

Repeat the previous step, only using the addrbook-ro account. What were the results?

Where does MySQL store the files for databases? (There are a number of ways to determine this; which did you use?)

Using any method you wish, install any required packages needed to provide both the PostgreSQL server and client tools. What package(s) are required? How did you determine this?
Enable the Postgres server (which may require an additional step compared to MySQL). If necessary re-configure TCP Wrappers to allow access from localhost. What steps exactly did you do? (Hint: with systemd, some commands had to be added to allow for tasks that could be done in a shell script, but not from a systemd service file. The command you need is postgresql-setup, which is not well documented. Read the file README.rpm-dist, found in /usr/share/doc/postgresql-version/.)
Unlike MySQL, Postgres uses the system user names and passwords. By default Postgres only has a single administrator account named postgres. However you can configure other accounts with administrative privilege. While in real life you would probably create one or more accounts per database, in this project you will make root a Postgres administrator, and your own user account will have access to read and modify data in the addrbook database. (In the steps that follow I use auser, but you should use your own account name!)
Like MySQL, PostgreSQL uses command line tools to administer the server, or you can use SQL commands. Since we used SQL in part I, in this part we will use some PostgreSQL command line utilities just for contrast. First you will add a new user to the PostgreSQL system (actually PostgreSQL refers to users as roles). To add the user root as a Postgres administrator:
```
# cd /tmp; su -c 'createuser -s root' postgres
```
(By default the only user (and admin user) is postgres. You must run the createuser -s root command as user postgres. Note that this user doesn't have privileges to your home directory, so you need to change to /tmp before running!) Once you have completed this step, root is also a postgres administrator, and any further commands can be done as root rather than as postgres.

What is the option to createuser to have the command show the equivalent Postgres (SQL) commands?
To allow command line use of Postgres you must add an extension (or plug-in). (You may find this was done already.) To add the procedural language PL/pgSQL to the default database template, you must run the following command (this only needs to be done one time as we are adding it to the default template used to create all other databases later):
```
# su -c 'createlang plpgsql template1' postgres
```
What was the result of running this command?
Next create a new database. This can be done with the createdb Postgres command. The -O name (capital Oh) says who will be the database owner, which must be some user that Postgres already knows about:
```
# createdb -O auser addrbook 'simple address book DB'
```
What was the message(s) produced by running this command? If the command did not appear to work, what was the problem and exactly what command(s) must you run to fix the problem?

Next connect to the server, create a table, and use it. The SQL statements to create a table and to add data to a table are (nearly) the same as for MySQL. First use one of the following commands to connect to your new database:


# psql -d addrbook -U auser

Or, if logged in as auser:

[auser@localhost ~] psql addrbook
Welcome to psql 8.0.8, the Postgres interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

addrbook=# \l+
                      List of databases
   Name    |  Owner   | Encoding |        Description
-----------+----------+----------+---------------------------
 addrbook  | auser    | LATIN1   | Simple address book DB
 rscds     | postgres | UNICODE  |
 template0 | postgres | LATIN1   |
 template1 | postgres | LATIN1   | Default template database
(4 rows)

addrbook=#

Note that the SQL data types are a bit different for Postgres than for MySQL. What is the Postgres command to display a list of the supported data types? How did you figure that out? The following shows the required Postgres SQL to create the table, to insert a row of data, and to use the table:

addrbook=# \dt
No relations found.
addrbook=# create table foo (
addrbook(# NAME varchar not NULL,
addrbook(# PHONE varchar,
addrbook(# NOTES varchar
addrbook(# );
CREATE TABLE
addrbook=# \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | foo  | table | wpollock
(1 row)

addrbook=# select * from foo;
 name | phone | notes
------+-------+-------
(0 rows)

addrbook=# \d foo
           Table "public.foo"
 Column |       Type        | Modifiers
--------+-------------------+-----------
 name   | character varying | not null
 phone  | character varying |
 notes  | character varying |

addrbook=# insert into foo values (
addrbook(# 'Prof. Pollock',
addrbook(# '253-7213',
addrbook(# 'Instructor of CTS-2301'
addrbook(# );
INSERT 17477 1
addrbook=# select * from foo;
     name      |  phone   |         notes
---------------+----------+------------------------
 Prof. Pollock | 253-7213 | Instructor of CTS-2301
(1 row)

addrbook=# \q

Which SQL commands are different between MySQL and Postgres database servers, for the simple database just created?

The answers to the questions above and the portion of your system journal describing all the changes you made.

You can submit your project as email to . Please see your syllabus for more information about submitting projects.

CTS 2301 (Unix/Linux Administration I) Project #7
Database Administration

Due: **by the start of class** on the date shown on the syllabus

Description:

Requirements:

Part I — MySQL

Part II — Postgres (PostgreSQL)

To be turned in:

CTS 2301 (Unix/Linux Administration I) Project #7 Database Administration

Due: by the start of class on the date shown on the syllabus

Description:

Requirements:

Part I — MySQL

Part II — Postgres (PostgreSQL)

To be turned in:

CTS 2301 (Unix/Linux Administration I) Project #7
Database Administration

Due: **by the start of class** on the date shown on the syllabus