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
Recommend
More recommend
