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

 

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

Description:

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 two relational database management systems (RDBMS or DBMS, or simply database server):  MariaDB, a modern version of MySQL (pronounced as “my-ess-cue-ell”) and available from MariaDB.org.  (MySQL is available from dev.mysql.com and www.mysql.com.)  We will also setup and configure PostgreSQL (pronounced as “postgres-que-ell”, and also known as Postgres.)  It is available from www.postgresql.org 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.

Requirements:

Answer the following questions and perform the following tasks:

Part I — MariaDB

  1. What MariaDB and/or MySQL related packages are installed on your system?  How did you determine that?

    Note!  MySQL is now owned by Oracle.  The original developer made a fork of MySQL, named “MariaDB”.  This is intended as a drop-in replacement, and should work the same way.  On more recent Linux systems, MariaDB is the default and not MySQLYou must check for packages of both names.

    If you discover your system has MariaDB, adjust the steps of this project to use that instead of MySQL.  The directions below refer to MySQL, but note most commands have the same name for both.

  2. What package(s) are needed to provide the MariaDB or the MySQL server and the associated client tools?  How did you determine this?  (Hints:  You will need at least two packages.  You can use the “search” feature of dnf, use some GUI tool to list available packages (related to MySQL), or use information obtained from the Internet.)

    Install any required packages using dnf.

  3. Since MariaDB/MySQL is a stand-alone server, examine the unit file(s) in the directory /lib/systemd/system/ to determine the service name (that is, the name of the script that manages that service).  If you don't see anu unit files there, it means that you either didn't install MySQL, or your system uses the old SysV init system (in which case, look in /etc/init.d).  What is the service name of the MySQL server on your system?  How did you find that name?
  4. 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 localhostWhat 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?
  5. 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.

  6. 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”)

  7. 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:
    1. 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>
      
    2. 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 (shown here as auser, but use your correct account name) password:

      mysql> set password for auser@localhost=password('new-password');
      
    3. 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-2301C instructor'
          -> );
      Query OK, 1 row affected (0.00 sec)
      
      mysql> \q
      Bye
      
    4. 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)
      
    5. Now lookup the data:
      mysql> select * from foo;
      +---------------+----------+-------------------------------------------------+
      | NAME          | PHONE    | NOTES                                           |
      +---------------+----------+-------------------------------------------------+
      | Prof. Pollock | 253-7213 | CTS-2301C 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-2301C 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
      
    6. Repeat the previous step, only using the “addrbook-ro” account.  What were the results?
  8. Where does MySQL store the files for databases?  (There are a number of ways to determine this; which did you use?)

Part II — Postgres (PostgreSQL)

  1. 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?
  2. Enable the Postgres server (which may require an additional step compared to MySQL).  If necessary re-configure TCP Wrappers to allow access from localhostWhat 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 documented in a man page.  Read that man page to see the option you need to use.
  3. 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 used “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, as root run the following command:

    # 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 (postgres) 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 (the “-s” option does that), 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?

    Unlike MySQL, PostgreSQL requires a user to connect to the database server by specifying a database to use.  If you omit the database name from the command line, the default is to connect to a database with the same name as the user.  Having created a new user root, let's make it convenient to use by creating a database for them.  As the user root, run the command “createdb”.

    Now create a Postgres user for yourself, using the option to show the SQL commands that createuser generates.  What are the SQL commands used to create the new user?

    You can make things convenient for yourself by creating a database with the same name as your user name.  Run (as root) the command “createdb -O username username”.

  4. To allow command line use of Postgres you must add an extension (or plug-in).  (This was almost certainly done when you ran postgresql_setup earlier.)  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):
    # createlang plpgsql template1
    

    What was the result of running this command?

    (As noted, modern versions of PostgreSQL for Red Hat systems may do this step automatically when you initialize the server.  So if you get an error, don't worry about it.)

  5. Next create a new database for our project, owned by the new user.  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), if any, 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?

  6. 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, just this should work:

    [auser@localhost ~]$ psql addrbook
    

    A recent change to the default PostgreSQL package for Fedora means that out of the box, you can't use the “-U username” option.  If you try, you will see this error message:

       psql: FATAL:  Peer authentication failed for user "auser"
    

    This is fixable, by editing a config file and restarting the server.  For this project, I strongly suggest you do not try the command as root user, but as your normal account (“auser” in my example commands).

    After connecting to the server, you should see this if you then type the “help” command):

    psql (9.3.6)
    Type "help" for help.
    
    addrbook=# help
    You are using psql, the command-line interface to PostgreSQL.
    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
    

    Next, type command shown below (backslash-el-plus); you should see something similar to this:

    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=#
    
  7. Note that the SQL data types are a bit different for Postgres than for MySQLWhat 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-2301C'
    addrbook(# );
    INSERT 17477 1
    addrbook=# select * from foo;
         name      |  phone   |         notes
    ---------------+----------+------------------------
     Prof. Pollock | 253-7213 | Instructor of CTS-2301C
    (1 row)
    
    addrbook=# \q
    

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

To be turned in:

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.