1 / 31

ADMINISTRING DATABASE SERVICES IN RED HAT LINUX

ADMINISTRING DATABASE SERVICES IN RED HAT LINUX. DATA BASE. DATA BASE TYPES . MySQL PostgreSQL. Responsibilities of a DBA. 1. Installing & maintaining database servers Installing patches Upgrading softwares 2. Installing & maintaining database clients

ruby
Download Presentation

ADMINISTRING DATABASE SERVICES IN RED HAT LINUX

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. ADMINISTRING DATABASE SERVICES IN RED HAT LINUX DATA BASE

  2. DATA BASE TYPES MySQL PostgreSQL

  3. Responsibilities of a DBA 1. Installing & maintaining database servers Installing patches Upgrading softwares 2. Installing & maintaining database clients Installing & maintaining client programs 3. Maintaining accounts and users. Adding /deleting users from the database 4. Ensuring Database security Access control permissions 5. Ensuring data integrity Protects data changes and avoids duplication in multiple users

  4. FORMS OF DATABASE 1. Flat Files Simple text files having Space Tab Other characters Example: /etc/passwd 2. Relations The are also called RDBMS Example: Spreadsheets

  5. FLAT FILES LIMITATIONS/ DISADVANTAGES 1. Do not scale well Cannot perform random access on data Search line by line (only sequential access) 2. Unsuitable for multi user environments. Simultaneous changes by two users can lead to overwriting

  6. RDBMS ADVANTAGES Very good at finding relationships between data Stores data in tables with fields which makes searching and sorting data easy.

  7. SQL BASICS Database language Very similar to English and so it’s very simple SQL statements have a defined structure End users are unaware of it

  8. CREATING A TABLE IDEA Different column types for data Special column types e.g DATE , VAR, VARCHAR etc.. SQL commands are not case sensitive Whitespace is generally ignored SQL statement terminates with a semi colon (;)

  9. CREATING A TABLE . 1 CREATE TABLE phonebook (Last_name VARCHAR (25) NOT NULL, First_name VARCHAR (25) NOT NULL, Phone INT (20) NOT NULL);

  10. INSERTING VALUES INSERT INTO phonebook VALUES (‘Doe’, ‘John’, ‘555-555-1212’);

  11. TABLE VIEW phonebook

  12. DISPLAYING DATA SELECT * FROM phonebook; SELECT first_name FROM phonebook; SELECT Last_name, Phone FROM phonebook; SELECT CONCAT(First_name, “ ” , Last_name) AS Name FROM phonebook;

  13. DISPLAYING DATA SELECT * FROM phonebook WHERE first_name= “John”; OPPOSITE SELECT * FROM phonebook WHERE first_name!= “John”;

  14. CHOOSING A DATABASE: MySQL V PostgreSQL SPEED: MySQL is faster as compare to PostgreSQL. Queries are executed and results are displayed faster Data Locking: PostgreSQL is better then MySQL MySQL locks the whole table when some one is accessing a single field where as PostgreSQL just locks the raw which is currently being accessed, the rest whole table is open and unlock.

  15. Locking table in MySQL and PostgreSQL PostgreSQL MySQL • PostgreSQL

  16. PostgreSQL IS ACID COMPLIANCE Atomicity: If you have a power failure or server crash after an original record has been deleted but before the updated one has been added , you done lose the record because atomic transaction ensure that the original record is not deleted unless and untill the new record is added. new record to be added(before crash) Record recovered Deleted old record before crash

  17. PostgreSQL IS ACID COMPLIANCE Consistency: Incomplete transactions are rolled back to maintain consistency. Isolation: Ensures that multiple transactions operating on the same data are completely isolated from each other. Prevents data corruption if two users try to write to the same record . (locking ) Durability: If server crashed the database examines the log file and makes the changes accordingly .

  18. Procedural Languagesorimperative language PostgreSQL , C++, JAVA , SQL Programming Languages PHP, Pearl, Visual Basic

  19. Installing and configuring MySQL Two ways for installing and configuring 1-Source Version 2-Binary RPM (MySQL-server and MySQL-client) i- Install it by rpm –icommand. ii- Initialize the grand tables by mysql_install_db command as root user. iii- Go to mysql environment by typingmysqlcommand . iv- Setting a password for MySQL root user bymysql> SET PASSWORD FOR root = PASSWORD (“secretword”); command inside mysql environment. v- type exit command to exit mysql environment . NOTE: Once installed through RPM , necessary user and group is created automatically by the name MySQL .

  20. Creating a database in MySQL mysql> CREATE DATABASE animal; OR mysqladmin –u root –p create animals

  21. Granting and Taking away Privileges in MySQL GRANT ALL ON animals.* TO usman IDENTIFIED BY ‘usman123’; REVOKE ALL on animals FROM usman; Database User DatabaseAlltableUserPassword

  22. Installing and configuring PostgreSQL Two ways for installing and configuring 1-Source Version 2-Binary RPM (postgresql, postgresql-server and postgresql-libs) i- Install it by rpm –icommand. ii- Create the data directory by mkdir /usr/local/pgsqlcommand as root user. iii- Then change the user ownership of data directory to postgres user by typingchownpostgres /use/local/pgsqlcommand as root user. iv- Then change the group ownership of data directory to postgres group by typing chgrppostgres /use/local/pgsqlcommand as root user . v- Then change your self to a postgres user by the command su- postgresin order to initialize the data directories. vi- To initialize give the command initdb –D /user/local/pgsql/data . vii- Now start the database server by the command /usr/bin/postmaster –D /usr/local/pgsql/data OR /usr/bin/pg_ctl -D /usr/local/pgsql/data –l logfile start viii- At the end issue the command postmaster –D /usr/local/pgsql/data & NOTE: Once installed through RPM , necessary user and group is created automatically by the name postgres .

  23. Creating a database in PostgreSQL CREATE DATABASE animal; OR createdb animals

  24. Creating a database user in PostgreSQL CREATE USER usman; OR createuserusman

  25. Deleting a database user in PostgreSQL DROP USER usman; OR dropuserusman

  26. Granting and Taking away Privilages in PostgreSQL GRANT ALL ON animals TO usman ; REVOKE ALL on animals FROM usman; Database User DatabaseUser

  27. Database clients User Database Client Database Server

  28. Database clients model 1 User Database Client Database Server Your local host Remote host

  29. Database clients model 2 Database Client User Database Server Your local host Remote host 1 Remote host 2

  30. Database clients model 3 User Web Browser Database Client Web Server Database Server Your local host Remote host 1 Remote host 2

  31. Changing a database and getting help mysql> help; mysql> use animals; database name

More Related