6.5 Administering Your Database

Database administration encompasses such tasks as installing and configuring the DBMS, backing up the data, adding users and setting their various permissions, applying updates or new capabilities to the system, and similar tasks . If you just have yourself and a fairly small lab to deal with, it's not too bad. But organizations often hire one or more database administrators to do this work full time; even a smallish project, if it's critical and the budget exists, can benefit from the attention of a professional database administrator.

If you are a beginning programmer and need to install and maintain a database management system, you'll need to read the manuals and learn the tools. Even if you have some computer administration experience, there is a bit of learning involved. The best thing you can do is get help from an experienced database administrator.

Failing such expert help, it's necessary to get good documentation and follow it. This depends on the system you're using, of course. The following sections describe some of the basics.

6.5.1 Adding Users

One function a database administrator needs to know is how to add users and set their permissions, that is, what operations they're allowed to perform, and what resources they're allowed to view or change. In MySQL, for example, each user needs an account name and a password for access (these aren't tied to the rest of the account names and passwords on the computer system). Security can be important as well. You may use the database to manage your new data and results, which you don't want to release to the public just yet; at the same time, you may be providing the public, through a web site, access to your more established data and results. A system such as MySQL provides several tools to set up and manage accounts and security permissions.

6.5.2 Backup and Reloading

One essential task for any computer system's effort, including working with databases, is to back up your work. All computers will break; every disk drive will crash and become inoperable. If you don't have timely backups of your data, you will certainly lose it.

There are many ways to back up data; even MySQL has more than one method. However, even if you back up your data from the database to a backup file, it's still necessary to make a copy of the backup file in another location that's not on the same hard disk. For the small to medium project, it's possible to run a program that simply makes a text file containing MySQL commands that repopulates your database. This is often a convenient and workable method, but check the MySQL documentation if you wish for alternatives.

Here, then, is how you can make a backup, or dump, of a database, in this case to the disk file homologs.dump :

 [tisdall@coltrane development]$ mysqldump homologs -u tisdall -p > homologs.dump Enter password:  [tisdall@coltrane development]$ 

After that command, a dump file called homologs.dump is created. Here's what it looks like for my little two-table database:

 [tisdall@coltrane development]$ cat homologs.dump # MySQL dump 8.14 # # Host: localhost    Database: homologs #-------------------------------------------------------- # Server version        3.23.41 # # Table structure for table 'genename' # CREATE TABLE genename (   name char(20) default NULL,   id int(11) default NULL,   date date default NULL ) TYPE=MyISAM; # # Dumping data for table 'genename' # INSERT INTO genename VALUES ('aging',118,'1984-07-13'); INSERT INTO genename VALUES ('wrinkle',9223,'1987-08-15'); INSERT INTO genename VALUES ('hairy',273,'1990-09-30'); # # Table structure for table 'organism' # CREATE TABLE organism (   organism char(20) default NULL,   gene char(20) default NULL ) TYPE=MyISAM; # # Dumping data for table 'organism' # INSERT INTO organism VALUES ('human','118'); INSERT INTO organism VALUES ('human','9223'); INSERT INTO organism VALUES ('mouse','9223'); INSERT INTO organism VALUES ('mouse','273'); INSERT INTO organism VALUES ('worm','118'); [tisdall@coltrane development]$ 

Once you've backed up your data, you can drop the database (wiping out the data) and then create the (empty) database again. You can then use your dump file to redefine the tables and repopulate them with your saved rows of data.

Here's how you might delete a database and reload it from your dump backup file homologs.dump :

 mysql> drop database homologs; Query OK, 0 rows affected (0.00 sec) mysql> show databases; +----------+  Database  +----------+  caudyfly   dicty      gadfly     master     mysql      poetry     yeast     +----------+ 7 rows in set (0.00 sec) mysql> create database homologs; Query OK, 1 row affected (0.00 sec) mysql> use homologs; Database changed mysql> source homologs.dump; Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) mysql> show tables; +--------------------+  Tables_in_homologs  +--------------------+  genename             organism            +--------------------+ 2 rows in set (0.00 sec) mysql> select * from genename; +---------+------+------------+  name     id    date        +---------+------+------------+  aging     118  1984-07-13   wrinkle  9223  1987-08-15   hairy     273  1990-09-30  +---------+------+------------+ 3 rows in set (0.00 sec) mysql> select * from organism; +----------+------+  organism  gene  +----------+------+  human     118    human     9223   mouse     9223   mouse     273    worm      118   +----------+------+ 5 rows in set (0.00 sec) mysql>