LPI Linux Certification in a Nutshell (46 page)

while

while
test-commands
do
commands
done

Execute
test-commands
(usually a
test
command), and if the exit
status is nonzero (that is, the test fails), perform
commands
and repeat. Opposite of
until
.

Example 13-7
shows a
typical script from a Linux system. This example is
/etc/rc.d/init.d/
sendmail
, which is the
script that starts and stops Sendmail. This script demonstrates
many of the built-in commands referenced in the last
section.

#!/bin/bash
#
# sendmail      This shell script takes care of starting and stopping
#               sendmail.
#
# chkconfig: 2345 80 30
# description: Sendmail is a Mail Transport Agent, which is the program \
#              that moves mail from one machine to another.
# processname: sendmail
# config: /etc/mail/sendmail.cf
# pidfile: /var/run/sendmail.pid
# Source function library.
. /etc/rc.d/init.d/functions
# Source networking configuration.
[ -f /etc/sysconfig/network ] && . /etc/sysconfig/network
# Source sendmail configureation.
if [ -f /etc/sysconfig/sendmail ] ; then
. /etc/sysconfig/sendmail
else
DAEMON=no
QUEUE=1h
fi
[ -z "$SMQUEUE" ] && SMQUEUE="$QUEUE"
[ -z "$SMQUEUE" ] && SMQUEUE=1h
# Check that networking is up.
[ "${NETWORKING}" = "no" ] && exit 0
[ -f /usr/sbin/sendmail ] || exit 0
RETVAL=0
prog="sendmail"
start() {
# Start daemons.
echo -n $"Starting $prog: "
if test -x /usr/bin/make -a -f /etc/mail/Makefile ; then
make all -C /etc/mail -s > /dev/null
else
for i in virtusertable access domaintable mailertable ; do
if [ -f /etc/mail/$i ] ; then
makemap hash /etc/mail/$i < /etc/mail/$i
fi
done
fi
/usr/bin/newaliases > /dev/null 2>&1
daemon /usr/sbin/sendmail $([ "x$DAEMON" = xyes ] && echo -bd) \
$([ -n "$QUEUE" ] && echo -q$QUEUE) $SENDMAIL_OPTARG
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && touch /var/lock/subsys/sendmail
if ! test -f /var/run/sm-client.pid ; then
echo -n $"Starting sm-client: "
touch /var/run/sm-client.pid
chown smmsp:smmsp /var/run/sm-client.pid
if [ -x /usr/sbin/selinuxenabled ] && /usr/sbin/selinuxenabled; then
/sbin/restorecon /var/run/sm-client.pid
fi
daemon --check sm-client /usr/sbin/sendmail -L sm-msp-queue -Ac \
-q$SMQUEUE $SENDMAIL_OPTARG
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && touch /var/lock/subsys/sm-client
fi
return $RETVAL
}
reload() {
# Stop daemons.
echo -n $"reloading $prog: "
/usr/bin/newaliases > /dev/null 2>&1
if [ -x /usr/bin/make -a -f /etc/mail/Makefile ]; then
make all -C /etc/mail -s > /dev/null
else
for i in virtusertable access domaintable mailertable ; do
if [ -f /etc/mail/$i ] ; then
makemap hash /etc/mail/$i < /etc/mail/$i
fi
done
fi
daemon /usr/sbin/sendmail $([ "x$DAEMON" = xyes ] && echo -bd) \
$([ -n "$QUEUE" ] && echo -q$QUEUE)
RETVAL=$?
killproc sendmail -HUP
RETVAL=$?
echo
if [ $RETVAL -eq 0 -a -f /var/run/sm-client.pid ]; then
echo -n $"reloading sm-client: "
killproc sm-client -HUP
RETVAL=$?
echo
fi
return $RETVAL
}
stop() {
# Stop daemons.
if test -f /var/run/sm-client.pid ; then
echo -n $"Shutting down sm-client: "
killproc sm-client
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && rm -f /var/run/sm-client.pid
[ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/sm-client
fi
echo -n $"Shutting down $prog: "
killproc sendmail
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/sendmail
return $RETVAL
}
# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
reload)
reload
RETVAL=$?
;;
restart)
stop
start
RETVAL=$?
;;
condrestart)
if [ -f /var/lock/subsys/sendmail ]; then
stop
start
RETVAL=$?
fi
;;
status)
status sendmail
RETVAL=$?
;;
*)
echo $"Usage: $0 {start|stop|restart|condrestart|status}"
exit 1
esac
exit $RETVAL

MySQL is popular enough that it is distributed by default
with most modern Linux distributions. Like many client-server
applications, MySQL is usually distributed as multiple packages. Most
often this means there are separate packages for the server binaries and
the client binaries, although there may also be a third package that
includes
“common” code. Be sure to familiarize yourself with your
distribution’s package management system so you can effectively
determine what software is
installed
on your system. Refer to
Chapter 5
for more
information on the common Linux package managers.

Assuming that the MySQL server package is installed, the server is
started the same way that most Linux services are started:

#
/etc/rc.d/init.d/mysqld start
Starting MySQL:           [  OK  ]

You should now have a running mysqld process, listening on TCP
port 3306 by default. You can verify both of these with the
ps
and
netstat
commands,
respectively:

# ps aux | grep -i mysqld
root 1865 0.0 0.2 4656 1132 pts/0 S 22:20 0:00 /bin/sh \
/usr/bin/mysqld_safe --datadir=/var/lib/mysql \
--socket=/var/lib/mysql/mysql.sock --log-error=\
/var/log/mysqld.log --pidfile=\
/var/run/mysqld/mysqld.pid
mysql 1989 0.3 3.6 161508 19012 pts/0 Sl 22:20 0:00 \
/usr/libexec/mysqld --basedir=/usr --datadir=\
/var/lib/mysql --user=mysql --pid-file=\
/var/run/mysqld/mysqld.pid --skipexternal-locking \
--socket=/var/lib/mysql/mysql.sock
#
netstat -anp | grep "LISTEN" | grep "mysqld"
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN \
1989/mysqld

By default, communication with the MySQL server takes place over
the TCP port that the server listens on (normally tcp/3306). This
communication can be either through the
mysql
command-line client program, a programming language such as PHP or Perl,
or a GUI application. A number of useful GUI applications are available
as free downloads from
http://dev.mysql.com/downloads/gui-tools
. This section
will focus on using the
mysql
command-line program,
both interactively and in shell scripts.

To access the MySQL server, use the
mysql
command-line program:

#
mysql –uroot –p –hlocalhost
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.0.45 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

The options passed on the command line are:

Now that we have successfully connected to our database, it’s time
to review basic database concepts.

A full understanding of database management
recommendations and design methodology is outside the scope of this book
and outside the scope of the LPI 102 exam. However, you will need to
know the basics of storing data in a MySQL table, managing that data
(adding, updating, and deleting), and performing relatively complex
queries on that data.

A MySQL server instance allows the creation and access of multiple
databases simultaneously. The MySQL server actually creates a directory
in the filesystem for each new database created. Each database may
contain many tables, the layout of which is set upon initial table
creation, but can be modified later. Although there are many options and
pros and cons regarding database formats and storage engines, for the
purposes of the LPI 102 exam we will assume the default storage engine
(MyISAM) and concern ourselves more with table layout and querying. For
more information on storage engines and other advanced MySQL topics,
visit
http://dev.mysql.com/doc/
.

A table is made up of a number of columns, each given a certain
datatype that defines what data may be stored in this column.
Table 13-2
describes some of the more common
MySQL
datatypes.

Why are datatypes important? Wouldn’t it be easier if we had only
two datatypes, ASCII and binary? It is important to specify datatypes
because MySQL queries are datatype-aware. For example, if I want to
create a query that says, “Give me all the records earlier than a
certain date,” I can use the less-than operator (<) on a column that
has a datatype of DATE. MySQL will know that less-than in this case
means “on a date earlier than the one given.” This would be much more
difficult to accomplish if we did not have strongly defined
datatypes.

Let’s start with an example table and walk through the creation
steps. For our example, we will create a database called
community
. In this database, our first table will
be
families
, which will store names and birthdates
for the members of a family:

#
mysql –uroot –p –hlocalhost
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 53
Server version: 5.0.45 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
create database community;
Query OK, 1 row affected (0.02 sec)
mysql>
use community;
Database changed
mysql>
CREATE TABLE families (
->
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
->
father_name VARCHAR(100),
->
father_dob DATE,
->
mother_name VARCHAR(100),
->
mother_dob DATE,
->
number_of_children INTEGER UNSIGNED,
->
child1_name VARCHAR(100),
->
child1_dob DATE,
->
child2_name VARCHAR(100),
->
child2_dob DATE,
->
child3_name VARCHAR(100),
->
child3_dob DATE,
->
notes TEXT,
->
PRIMARY KEY (id)
->
);
Query OK, 0 rows affected (0.05 sec)

The
CREATE TABLE
command is used to create a
table in an existing database. Each column of the table is named and
given a datatype. In addition, options can be given to columns to change
their behavior. For example, we gave the
id
column
the option
AUTO_INCREMENT
. This means that MySQL
will automatically increment this integer value for every row added to
the table. This is a common practice and ensures that every row will be
unique, because even if all the other columns have the same data, the
id
field will always be different. This is enforced
by the line “PRIMARY KEY (id)”. By naming the
id
field as the primary key, we’re instructing MySQL to enforce the
uniqueness of this value for each row. This means that if we tried to
add a row of data that included an
id
value that
already existed, MySQL would display an error and the data would not be
added.

The other fields added are names, which we defined as a varchar
with an upper limit of 100 characters (more than enough to hold a first
name, middle name and last name), dates of birth (denoted by the DATE
datatype), and a notes field, which we defined as type TEXT.

To add data to this table, we use the
INSERT
command:

mysql>
INSERT into families
-> (father_name, father_dob, mother_name, mother_dob,
-> number_of_children, child1_name, child1_dob,
-> child2_name, child2_dob, notes)
-> VALUES
-> ("Joe Smith", "1970-04-01", "Jan Smith", "1970-05-10",
-> "2", "Jimmy Smith","2000-08-10", "Janey Smith",
-> "2002-12-12", "This is the smith family
-> of Chicago, IL");
Query OK, 1 row affected, 0 warnings (0.02 sec)

Notice that we did not insert data into every column of the table.
By identifying the fields we wished to use and then the values for each,
we’re able to insert exactly the data we wish into the table. To view
the data we just added, use the
SELECT
command
:

mysql>
SELECT
id, father_name, mother_name, number_of_children from families;
+----+-------------+-------------+--------------------+
| id | father_name | mother_name | number_of_children |
+----+-------------+-------------+--------------------+
|  1 | Joe Smith   | Jan Smith   |                  2 |
+----+-------------+-------------+--------------------+
1 row in set (0.00 sec)

In this case, we instructed the
SELECT
statement to show us only the columns
id
,
father_name
,
mother_name
, and
number_of_children
. If we had wished to see all of
the columns, the command would have been
SELECT * from families
.

Notice that the
id
column has a value of 1.
We did not insert this value; it was set by the MySQL server when we
inserted our first record. Subsequent inserts will continue to increment
this number. Let’s add one more row of data:

mysql>
INSERT into families set
->
father_name = "Ken Anderson",
->
father_dob = "1971-06-06",
->
mother_name = "Mary Anderson",
->
mother_dob = "1971-01-29",
->
number_of_children = "3",
->
child1_name = "Shawn Anderson",
->
child1_dob = "1999-10-17",
->
child2_name = "Kyle Anderson",
->
child2_dob = "2001-10-12",
->
child3_name="Lillie Anderson",
->
child3_dob = "2004-11-12",
->
notes = "This is the Anderson family of Omaha, NE";
Query OK, 1 row affected (0.02 sec)

In this example, we accomplished the same goal as our original
INSERT
statement, but we used an alternate
syntax.

Now repeat our
SELECT
query to verify that
the table contains two rows:

mysql>
SELECT id, father_name, mother_name, number_of_children from families;
+----+--------------+---------------+--------------------+
| id | father_name  | mother_name   | number_of_children |
+----+--------------+---------------+--------------------+
|  1 | Joe Smith    | Jan Smith     |                  2 |
|  2 | Ken Anderson | Mary Anderson |                  3 |
+----+--------------+---------------+--------------------+
2 rows in set (0.01 sec)

Now that we know how to add data to our table, the next step is
modifying existing data. In our initial insert, we didn’t capitalize the
last name “smith” in the
notes
column. Use the
UPDATE
command with a
WHERE
clause to correct this:

mysql>
UPDATE families set
->
notes = "This is the Smith family of Chicago, IL"
->
WHERE id = "1";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

The
UPDATE
command is used to modify values
in rows that already exist. In order to identify what rows to modify
(assuming you don’t want to update all rows in the table), you need to
give a
WHERE
clause that uniquely identifies the
rows you wish to modify. In this instance, we took advantage of the fact
that the
id
field is unique among rows to ensure
that our modification affected only the row we wanted it to affect. Use
the
SELECT
command again to verify our change took
place:

mysql>
select id, notes from families;
+----+------------------------------------------+
| id | notes                                    |
+----+------------------------------------------+
|  1 | This is the Smith family of Chicago, IL  |
|  2 | This is the Anderson family of Omaha, NE |
+----+------------------------------------------+
2 rows in set (0.00 sec)

To remove data from the table, the
DELETE
command is used. The syntax is similar to the
UPDATE
command:

mysql>
delete from families where id = "2";
Query OK, 1 row affected (0.03 sec)
mysql>
select id, notes from families;
+----+-----------------------------------------+
| id | notes                                   |
+----+-----------------------------------------+
|  1 | This is the Smith family of Chicago, IL |
+----+-----------------------------------------+
1 row in set (0.00 sec)

The
SELECT
command shows us that we’re back
to one record in the table.

It is possible to alter the layout of a table after it has been
created. You can either modify the datatype of an existing column or
add/delete columns from the table. Be careful when you modify a datatype
on a column that already contains data because you run the risk of
losing your data! For example, if you change a column from
varchar(255)
to
char(1)
, you will
lose all but the first character of any data you had in that
column.

The
ALTER TABLE
command is used to modify a
table after it has been created. Let’s add two new columns to our table
to track the city and state where the families live:

mysql>
ALTER TABLE families
->
ADD COLUMN city VARCHAR(100) AFTER notes,
->
ADD COLUMN state CHAR(2) AFTER city;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

Reinsert our second family that we previously deleted:

mysql>
insert into families set father_name = "Ken Anderson",
-> father_dob = "1971-06-06", mother_name = "Mary Anderson",
-> mother_dob = "1971-01-29", number_of_children = "3", child1_name =
-> "Shawn Anderson", child1_dob = "1999-10-17", child2_name =
-> "Kyle Anderson", child2_dob = "2001-10-12", child3_name=
-> "Lillie Anderson", child3_dob = "2004-11-12", notes =
-> "This is the Anderson family of Omaha, NE";