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>
|
لطفا منتظر باشید ...
