Transcript Database Administration

Database Administration
Rehema Baguma
Who is a DBA
• A person responsible for designing, implementing,
and maintaining the database system; establishing
policies and procedures pertaining to the
management, security, maintenance, and use of the
database management system.
Skills for a DBA
•
•
•
•
•
•
Communication skills
Knowledge of database theory
Knowledge of database design
Knowledge about the RDBMS e.g. Microsoft SQL Server
Knowledge of Structured Query Language (SQL)
General understanding of distributed computing architectures
e.g. Client/Server, Internet/Intranet, Enterprise
• General understanding of the underlying operating system,
e.g. Windows, Linux
• General understanding of storage technologies, memory
management, disk arrays
Roles of a DBA
• Installing and upgrading the database server and application tools
• Allocating system storage and planning future storage requirements for the
database system
• Modifying the database structure, as necessary, from information given by
application developers verbally
• Enrolling users and maintaining system security
• Ensuring compliance with database vendor license agreement
• Controlling and monitoring user access to the database
• Monitoring and optimizing the performance of the database
• Planning for backup and recovery of database information
• Maintaining archived data
• Backing up and restoring databases
• Contacting database vendor for technical support
• Generating various reports by querying from database as per need
Understanding the DBMS Architecture
• The database architecture is the set of specifications, rules,
and processes that dictate how data is stored in a database
and how data is accessed by components of a system.
– It describes the organization of all database objects and
how they work together.
– It affects integrity, reliability, scalability, and performance.
Data Model
• Data model: Collection of concepts that
describe the structure of a database
• Provides means to achieve data abstraction
• Suppression of details of data organization & storage
Categories of Data Models
– High-level or conceptual data models: close to the way
many users perceive data
– Low-level or physical data models: Describe the
details of how data is stored on computer storage
media
OR
• External; How data is viewed by an individual user
• Conceptual: How data is viewed by a community of users
• Internal: How data is physically stored
Categories of Data Models
!
Categories of Data Models cont..
Entity
• Represents a real-world object or concept e.g. student
Attribute
• Represents some property of interest e.g. year of study
• Further describes an entity
• Relationship among two or more entities
• Represents an association among the entities
• Entity-Relationship model
• Relational data model
– Based on relation/table principle for data storage and management
Categories of Data Models cont..
• Object data model
– New family of higher-level implementation data models
– Closer to conceptual data models
• Physical data models
– Describe how data is stored as files in the computer
•
Access path
– Structure that makes the search for particular database records
efficient
•
Index
– Example of an access path
– Allows direct access to data using an index term or a keyword
Schemas instances and database State
• Database schema
– Description of a database
• Schema diagram
• Displays selected aspects of schema
• Schema construct
– Each object in the schema
• Database state or snapshot
– Data in database at a particular moment in time
•
Initial state
– Populated or loaded with the initial data
• Valid state
– Satisfies the structure and constraints specified in the
schema
Example of a Database Schema
!
DBMS Interfaces
Menu-based interfaces for Web clients or browsing
Forms-based interfaces
• Graphical user interfaces
Natural language interfaces
• Speech input and output
Database Components
• Buffer management
• Stored data manager
• DDL compiler
– DDL compiler is the compiler whose job is to convert high level commands to low level
commands..
•
•
•
•
•
•
Interactive query interface
Query compiler
Runtime database processor
System catalog
Concurrency control system
Backup and recovery system
DB system Utilities
• Loading
– Load existing data files
•
Backup
– Creates a backup copy of the database
• Database storage reorganization
– Reorganize a set of database files into different file
organizations
• Performance monitoring
– Monitors database usage and provides statistics to the DBA
Centralised Vs Client server DB
architectures
• Centralized DBMSs Architecture
– All DBMS functionality, application program execution, and
user interface processing carried out on one machine
•
Servers with specific functionalities
– File server
– Maintains the files of the client machines.
• Printer server
– Connected to various printers; all print requests by
the clients are forwarded to this machine
• Web servers or e-mail servers
Two Tier, Three Tier and N-Tier
• Server handles
– Query and transaction functionality related to SQL processing
• Client handles
–
–
–
–
User interface programs and application programs
Open Database Connectivity (ODBC)
Provides application programming interface (API)
Allows client-side programs to call the DBMS
• Both client and server machines must have the necessary
software installed
• Three Tier & N-Tier
• Application server or Web server
– Adds intermediate layer between client and the database server: Runs
application programs and stores business rules
Two Tier, Three Tier and N-Tier
• N-tier
– Divides the layers between the user and the stored data further
into finer components
Understanding and using the system log for
DB admin tasks
• DBMS e.g. MySQL Server has several logs that can help you find
out what activity is taking place.
• By default, no logs are enabled (except the error log on
Windows).
• By default, the server writes files for all enabled logs in the data
directory.
• You can force the server to close and reopen the log files (or in
some cases switch to a new log file) by flushing the logs
– Using a FLUSH LOGS statement
• The binary log is flushed when its size reaches the value of the
max_binlog_size system variable.
• You can enable or disable logging, or change the log file name.
Understanding and using the system log
in database admin cont..
Log Type
Error log
Information Written to Log
Problems encountered starting, running, or stopping
DBMS
Established client connections and statements received
DBMS e.g. MySQL Server has several
logsclients
that can help
from
General query log
Binary log
Statements that change data (also used for replication)
Relay log
Data changes received from a replication master server
Slow query log
Queries that took more than the established thresh hold
(long query time) to execute
Database Tuning
• What is database tuning, and how can it be
achieved in database administration?
Database backup and recovery
• If you're storing anything in databases that you do not want to
lose, it is very important to make regular backups of your data
to protect it from loss
• There are two easy ways to backup and restore the data in
MySQL database.
– Back up from the Command Line (using mysqldump)
– Back up with Compress
Back up From the Command Line (using mysqldump)
• You can backup MySQL data using the mysqldump command.
– This command connects to the MySQL server and creates an SQL
dump file.
• The dump file contains the SQL statements necessary to recreate the database. The syntax is :
– mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]
• [uname] Your database username
• [pass] The password for your database (note there is no space between -p and
the password)
• [dbname] The name of your database
• [backupfile.sql] The filename for your database backup
• [--opt] The mysqldump option
B
Back up from the Command Line (using mysqldump)
• E.g. mysqldump -u root -p Tutorials > tut_backup.sql
– Backs up database named 'Tutorials' with the username 'root' and with
no password to a file tut_backup.sql
• You can also choose to specify certain tables of the database
you want to backup by separating each table name with space.
– mysqldump -u root -p Tutorials php_tutorials asp_tutorials >
tut_backup.sql
• You can back up more than one database at once using the -database option followed by the list of databases you would
like to backup separated by space.
– mysqldump -u root -p --databases Tutorials Articles Comments >
content_backup.sql
Back up From the Command Line (using mysqldump)
• To back up all the databases in the server at once you use the
--all-databases option.
mysqldump -u root -p --all-databases > alldb_backup.sql
• The mysqldump command has also some other useful
options:
– --add-drop-table: Tells MySQL to add a DROP TABLE statement before
each CREATE TABLE in the dump.
– --no-data: Dumps only the database structure, not the contents.
– --add-locks: Adds the LOCK TABLES statement you can see in the dump
file.
Backing up MySQL Database with Compress
• If your mysql database is very big, you might want to
compress the output of mysqldump.
– pipe the output to gzip to get the output as gzip file.
• mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]
• To extract the .gz file, use the command below:
– gunzip [backupfile.sql.gz]
Restoring MySQL Database
• To re-create the Tutorials database follow two steps:
– Create an appropriately named database on the target machine
– Load the file using the mysql command:
• mysql -u [uname] -p[pass] [db_to_restore] <
[backupfile.sql] e.g.
• mysql -u root -p Tutorials < tut_backup.sql
• To restore compressed backup files, do the following:
• gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass]
[dbname]
• To restore a database that already exists, you'll need to use
mysql import command as follows: mysqlimport -u [uname] p[pass] [dbname] [backupfile.sql]
Backing Up and Restoring using PHPMyAdmin
• To backup MySQL database using PHPMyAdmin, just follow a
couple of steps using phpMyAdmin’s menu options:
– Open phpMyAdmin.
– Select your database by clicking the database name in the list on the
left of the screen.
– Click the Export link. This should bring up a new screen that says View
dump of database (or something similar).
– In the Export area, click the Select All link to choose all of the tables in
your database.
– In the SQL options area, click the right options.
– Click on the Save as file option and the corresponding compression
option and then click the 'Go' button. A dialog box should appear
prompting you to save the file locally.
Restoring your database using phpMyAdmin
– Open phpMyAdmin.
– Create an appropriately named database and select it by
clicking the database name in the list on the left of the
screen.
• If you would like to rewrite the backup over an existing database
then click on the database name, select all the check boxes next
to the table names and select Drop to delete all existing tables in
the database.
– Click the SQL link. This should bring up a new screen where
you can either type in SQL commands, or upload your SQL
file.
– Use the browse button to find the database file.
– Click Go button. This will upload the backup, execute the
SQL commands and re-create your database.
Securing your Databases
• General factors that affect security.
– choosing good passwords, not granting unnecessary privileges to
users, ensuring application security by preventing SQL injections and
data corruption, etc.
• Security of the installation itself.
– The data files, log files, and the all the application files of your
installation should be protected to ensure that they are not readable
or writable by unauthorized parties e.g. not storing them in the
default directory.
• Access control and security within the database system itself,
– Grant users only required access to the databases, views and stored
programs in use within the database.
• Network security of MySQL and system.
– Ensure appropriate permissions for users, an up-to-date anti virus,
firewalls, ensure other Network security measures
• Adequate & appropriate backups & tested recovery solution. See
http://dev.mysql.com/doc/refman/5.5/en/security.html
Using an example of an organisation of your
choice, list and explain best practices for
database management-feedback from group discussion.
•
•
•
•
•
•
Replication in different locations
Normalised tables
Vendor specification guidelines
Backup & recovery plan/schedule
Configure audit logs/moninotring usage
Protect sensitive data e.g. encryption of passwords & credit
card information
• Database documentation that provides a clear understanding
of the DB
• Secure default configurations
• Applying software updates for the DBMS & other applications
• DB & web server must be placed in different machines for
better security & performance
• Routine check of DB & log size
• Appropriate management of user access privileges
• Run frequently penetration & intrusion detection tests
Develop & execute good database management
practices
After people, data is your most important asset: below are some
best practices for good DB management
• Left unattended, databases will turn into piles of mush.
– develop data entry policies and procedures, and train everyone who
enters data.
• Have some one in charge of training, user support, and data
quality .
– At a small organization, this might be a small part of someone's job. At
a large organization, it might need to be a whole department.
• Run backups religiously. Do test them periodically to make
sure you can recover data.
• Keep a copy of your data off-site e.g.
– online backup service, a vendor that provides offsite storage and
retrieval, or even by sending a backup home with a staff member but
be aware about risks like theft, loss, aunauthorised access, etc.
Develop & execute good database management
practices
• Do all you can to prevent staff from downloading sensitive
data (S.S numbers or credit card data) to laptops or memory
sticks.
– Use encryption on laptops and memory sticks.
– Tools like Identity Finder, Proventsure, SENF, and Spider will scan your
computers for sensitive data.
• Require strong passwords. Do not share passwords. Change
passwords regularly.
• Monitor your data entry.
– Run reports that look for common errors.
– Identify staff members who repeatedly make errors and provide
additional training.
– If the errors continue, take away their data entry permissions.
Develop & execute good database management practices
• Run your database on a need-to-know basis.
– Give staff and volunteers access to only the data they need to see or
change to do their jobs -- but make sure that they do have access to
the data they need.
• Make sure your database has enough security options.
– provide read-only access where rights are not necessary e.g. names
and addresses but not gifts.
– provide update access to only certain portions of your data, like
updating addresses but not entering gifts.
– restrict access to sensitive functions like posting a gift batch
– restrict the ability to run mass updates or delete records.
See more at: http://www.rlweiner.com/best-practices-formanaging-a-database#sthash.K8GPelsu.dpuf
• See also PDF about best practices for database management
Individual Assignment
Using an example and a case study of either a bank,
hospital or a university,
a) State and explain 5 best practices for:
i. Effective web design
ii. Effective web administration
iii. Effective database administration (20 marks)
b) Develop a web based application using the practices
given in (a) (20marks)