Introduction to MySQL Database Systems 1 Agenda Bureaucracy - PowerPoint PPT Presentation

Introduction to MySQL Database Systems 1

Agenda  Bureaucracy…  Database architecture overview  SSH Tunneling  Intro to MySQL  Comments on homework 2

Homework #1  Submission date is on the website.. (No late arrivals will be accepted)  Work should be done in pairs  Submission is done via moodle, by one of the partners.  Submit a zip file, with  an answers pdf that contains the full names and IDs of both partners on top of the page  A .sql file for every query  Use the format described in the assignment 3

Project  Hard work, but practical.  Work in groups of 4  Project goal: to tackle and resolve real-life DB related development issues  One stage, with a check point in ~the middle  Use JAVA (SWT)  Thinking out of the box will be rewarded 4

Agenda  Bureaucracy…  Database architecture overview  SSH Tunneling  Intro to MySQL  Comments on homework 5

DB System from lecture #1 “Two tier database system” connection (ODBC, JDBC) Database server (someone else’s Applications Data files C program) 6

1,2,3 tiers 7

Examples in this course Runs someone Runs a client via else’s client your code Your computer Your computer at home at home … which connects to the … which connects server on the same machine to the MySQL server on the school server machine 8

Abstractly (DB) system layers may include Application DB infrastructure DB driver Transport DB engine Storage 9

Why? Gui designer Tester App programmer DBA DB programmer 10

Application layer Application  Why should it actually use DB infrastructure DB driver database? Transport DB engine  Persistence layer Storage  Access data storage  Interfacing between systems  Large volumes  Scalability  Redundancy 11

Infrastructure layer Application  Goals: DB infrastructure DB driver  Database “hiding” Transport  Schema abstraction DB engine  Encapsulation of db mechanisms Storage  How: (In two words)  Could be a part of your application – or an external package  E.g., hibernate 12

DB driver / bridge Application  Used for: DB infrastructure DB driver  API for database connectivity Transport DB engine  Protocol converter Storage  Performance improvements  Transaction management  Examples:  In a minute… 13

Transport Application  Mainly TCP but not only DB infrastructure DB driver  Secure Transport DB engine  Efficient Storage  Fast (but not fast enough) 14

DB engine Application  Total management of the DB DB infrastructure DB driver environment including Transport  Security DB engine Storage  Scalability  Fault tolerant (disaster management)  Monitoring  Services  Large DB engines include Microsoft SQL Server, Oracle, SyBase, MySQL, etc. 15

DB engine (2) Application DB engine management includes: DB infrastructure DB driver  Databases/Tables/Fields Transport DB engine Creation/removal/modification/ Storage optimization  Connections/Users/Roles Security/monitoring/logging  Jobs/Processes/Threads Scheduling/balancing/managing 16

Storage Application  NAS/SAN, Raid and other stuff DB infrastructure DB driver  We are interested in the storage-engine Transport interface DB engine Storage 17

A real-life example  We want to build an image sharing Website  What is our data? 18

The application Application  GUI DB infrastructure DB driver  Application-User Management Transport  Do not confuse with DB users! DB engine  Image processing Storage  And so on…  The application needs storage for the images, albums, users, tags…  Runs on the application server  E.g., your computer at home 19

Infrastructure Application  This layer wraps DB infrastructure DB driver  Entities in our application (Images, Transport users,…) DB engine  Relations between entities (Image Storage creator, followers,…)  Common operations (upload/edit/delete image,…)  Some of these may be created by an automatic process  Still on the application machine 20

DB driver / bridge Application  Not written by us, e.g., J connector DB infrastructure DB driver  Used by the infrastructure Transport DB engine  E.g., to upload an image we use an Storage insert command to the image table (and perhaps others)  We want the type of DB used to be configurable 21

Transport Application  Our application connects to the DB infrastructure DB driver database server Transport DB engine  Over TCP/IP Storage 22

DB engine Application  E.g., MySQL Community Server DB infrastructure DB driver  The db stores Transport  Our tables with the data (Images, users, etc.) DB engine Storage  Optimization components (Indexes, triggers)  Predefined operations (procedures, functions)  Executes the requests we sent  E.g., insert an image 23

Storage Application  E.g., the school MySQL server DB infrastructure DB driver stores data on the school machines Transport DB engine Storage 24

Agenda  Bureaucracy…  Database architecture overview  SSH Tunneling  Intro to MySQL  Comments on homework 25

Connecting… You need:  Host st IP/ P/ nam ame  Port  Home install: host=localhost TAU’s server: host=mysqlsrv.cs.tau.ac.il  MySQL default port is 3306 is it t real ally ly th that at eas asy?? ?? 26

Welcome to 27

SSH Standard way Using Tunnel Application Application Client DB infrastructure DB infrastructure Client Machine Machine DB bridge/driver DB bridge/driver TCP proxy Transport SSH (TCP) Tunnel machine Proxy (SSH server) Machine Server TCP DB engine Machine Server DB engine Machine 28

SSH in TAU Application YOUR MACHINE DB infrastructure define DB at localhost, port 3305 Db bridge/driver Putty connects to nova and proxy forward local port 3305 to mysqlsrv.cs.tau.ac.il port 3306 Tunnel machine Nova.cs.tau.ac.il (SSH server) mysqlsrv.cs.tau.ac.il DB engine 29

SSH in TAU  Putty 30

Don’t forget to  CHECK THE CONNECTION GUIDE!! (course website next to these slides) 31

Agenda  Bureaucracy…  Database architecture overview  SSH Tunneling  Intro to MySQL  Comments on homework 32

Products we will be using  MySQL (Community Server – Home)  MySQL (Enterprise Edition – TAU)  MySQL Workbench (GUI Tool..)  MySQL Connector (J) – In two weeks… Free to download on www.mysql.com 33

TAU Server settings..  You can create your own user (schema) by following the connection guide link (course website..)  For the project, each group will get a dedicated user+schema 34

“ Sakila ” Schema (For hw 1)  We will use the “ Sakila ” schema http://dev.mysql.com/doc/sakila/en/ Can be installed  Install and download from with the other MySQL products http://dev.mysql.com/doc/index-other.html  Already installed on TAU’s server: username: sakila Schema: a set of password: sakila tables (and views) in schema: sakila a database. Each schema has its own permissions 35

MySQL Command  In the TAU System website: http://www.cs.tau.ac.il/system/searchview?search_api_views_fulltext=+mysql  How to run: http://www.cs.tau.ac.il/system/MySQLConn  mysql -u sakila -h mysqlsrv.cs.tau.ac.il sakila – p  Common commands: - “show databases;” - “show tables;” - “select.. ;”  Don’t forget the ; 36

Install MySQL at Home  MySQL Community Server http://www.mysql.com/downloads/mysql/ 37

Registration is Optional 38

Installation using an Installer 39

Configuration 40

Installation using an Installer 41

MySQL Workbench  Make sure to install server, workbench and examples 42

Example: connecting to school server  Ope pen the e tunnel el!  Then open workbench and create new connection 43

Configure the connection 44

Support old authentication protocol 45

Open the new connection 46

Now you can query the SQL data 47

… and the result 48

Demo Time   Startup the Server.. 49

Demo Time   Server Administration  run the local instance  create users  export/import 50

Demo Time   SQL Development  browse the schema  create/alter tables  run queries  export results 51

Demo Time   Install the “ sakila ” schema 52

Demo Time   Data Modeling  browse / alter the schema 53

phpMyAdmin 54

phpMyAdmin  Another tool for managing MySQL  Installed on tau, and reachable from home without a tunnel! https://www.cs.tau.ac.il/phpmyadmin/index.php (note the https )  To install at home, download from: http://www.phpmyadmin.net/ (requires php server so its not recommended unless you are familiar with these stuff…) 55

56

Agenda  Bureaucracy…  Database architecture overview  SSH Tunneling  Intro to MySQL  Comments on Homework 57