Using My SQL - Data Base Management System - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Using My SQL - Data Base Management System - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Using MySQL

Downloading MySQL

To install MySQL download it

from

http://www.mysql.com

• This site will be your best

friend when using MySQL!

• The search contains the

entire MySQL

documentation

• Open Source under GPL

Downloading MySQL

• You want MySQL database server &

standard clients -> MySQL 4.

This is the latest tested general release.

Downloading MySQL

• MySQL Standard
  • Standard tested general release
• MySQL Max
  • All the features of standard plus experimental enhancements. Not recommended for production environments

Downloading MySQL

• Binary Distribution
  • Get the binaries to install MySQL with the most common configuration
• Source Distribution
  • Compile MySQL with alternate command line options
  • Only if you have way too much time on your hands

Installing MySQL

• Once you have downloaded the ZIP file from a

suitable mirror, unzip it and run the

setup.exe installation file

• Follow the installation instructions

I recommend installing in the default directory of c:\mysql

Starting MySQL for the 1 st^ Time

• A dir on the bin

directory will show you all the executables for MySQL

• Important are
  • mysql
  • mysqld
  • mysqladmin
  • mysqldump

Starting MySQL for the 1 st^ Time

• On Windows machine, install MySQL as a

system service:

• C:\mysql\bin>mysqld --install

If the service was already started, stop it with:

• C:\mysql\bin>mysqladmin shutdown

MySQL as a Windows Service

• Double clicking on MySQL in the Services window gives you its properties box:

From here you can set MySQL to start automatically or manually

The MySQL Directory

• The MySQL directory (c:\mysql) is where all the datafiles and your databases are stored.
• Databases are listed in the data folder

Creating Accounts in MySQL

• The command show databases gives you a list of all databases in the system
• To use a database simply issue use
• The mysql database is necessary for the system and is where all the grant tables are stored

Creating The root Account

• MySQL grant tables can be manipulated just like any other table
• Create a root user: INSERT INTO user VALUES (‘localhost’,’root’,PASSWORD(‘testPassword’),’Y’,’Y’,’Y’,’Y ’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’)

NOTE: Password is an encrypted field and the function PASSWORD() must be used anytime a password is set or reset

MySQL Grant Tables

• user - Determines whether or not the connecting user is allowed to connect to the server. Assuming the connection is allowable, the privilege fields contain the user's global privileges.
• db - Determines which users can access which databases from which hosts. The privilege contained within the db table apply to the database identified within this table.
• host - The host table is used when you want to extend an entry within the db table's range. For example, if a certain db is to be accessed by more than one host, then the superuser would leave the host column empty within the db table and fill the host table with all of the necessary hostnames.
• tables_priv - In principle works just like the db table, except that it is used for tables instead of databases. This table also contains one other field category (Other) in which a timestamp and grantor column is stored.
• columns_priv - Works just like the db and tables_priv tables, except that it provides access privileges for certain columns of certain tables. This table also contains one other field category (Other) in which a timestamp column is stored.

MySQL Grant Tables

• Two types of user requests:

• Adminstrative Requests
  • Shutdown
  • Reload
  • Process…and so forth
• Database Requests
  • Select
  • Insert
  • Delete…and so forth