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/abbr> 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.
Answer the following questions and perform the following tasks:
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 MySQL! You 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.
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
.
/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?
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? 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
.
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
”)
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:
$ 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>
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');
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
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)
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
addrbook-ro
” account.
What were the results? 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 documented in a man page.
Read that man page to see the option you need to use.
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
”.
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.)
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?
# 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=#
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?
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.