Developing Dynamic Web Applications
with MySQL and PHP
Revision 1.4
P
RESENTED
B
Y
M
Y
SQL
AB
T
RAINING
S
ERVICES
[email protected] http://www.mysql.com/training/ INSTRUCTOR GUIDE 1Chapter Course/Chapter/Section Name
Duration
(Hours) Track DBA Devel
o p er S upport Cons ult ing Comments Owner PHP and MySQL 26.5 3 5 4 4 Day One 7.5 1 Introduction 1 5 5 5 5 1.1 Structure of MySQL AB 0 5 5 5 5
1.2 MySQL Products and Services 0.25 5 5 5 5
1.3 MySQL Partners 0 3 3 3 3
1.4 Supported Operating Services 0 5 4 5 5
1.5 MySQL Certification Program 0.25 5 5 5 5
1.6 Training Curriculum Paths 0.25 5 5 5 5
1.7 MySQL Website 0.25 5 5 5 5
2 Verify Installation 3.25 4 4 4 4
2.1 LAMP Architecture 0.25 4 5 4 4
2.2 Install/Verify Apache Installation 1 3 4 3 3
2.3 Install/Verify MySQL Installation 0.75 5 4 3 3
2.4 Install/Verify PHP Installation 0.75 4 4 5 5
2.5 Putting it All Together 0.5 4 4 5 5
3 PHP Foundations 3.25 1 5 3 3
3.1 Delimit PHP Code Within HTML 0.25 1 5 3 3
3.2 Comment PHP Code 0.25 1 4 3 3
3.3 Construct PHP that interacts with end users 0.5 1 5 3 3
3.4 Data Types in PHP 0.75 1 5 3 3
3.5 Key Control Systems 0.75 1 5 3 3
3.6 Use Defined Functions 0.75 1 5 3 3
Day Two 5.75
4 MySQL Foundations 1 5 4 5 5
4.1 General Architecture 0 5 5 5 5
4.2 SQL Parser and Storage Engine Tiers 0.25 5 4 5 5
4.3 MySQL Client 0.5 5 4 5 5
4.4 MySQL Query Browser 0.25 5 4 5 5
5 Manage Databases 1.25 5 4 5 5
5.1 Database Properties 0 5 4 5 5
5.2 Database Design Practices 0.25 5 4 5 5
5.3 MySQL Identifiers 0.25 5 4 5 5 5.4 Create Databases 0.25 5 4 5 5 5.5 Alter Databases 0.25 5 4 5 5 5.6 Drop Databases 0.25 5 4 5 5 6 Manage Tables 2.25 5 4 5 5 6.1 Table Properties 0 5 4 5 5
6.2 MySQL Data Types 0.5 5 4 5 5
6.3 Create Table 0.5 5 4 5 5 6.4 Alter Table 0.5 5 4 5 5 6.5 Empty Table 0.25 5 4 5 5 6.6 Remove Table 0 5 4 5 5 6.7 Indexes 0.25 5 4 5 5 6.8 Foreign Keys 0.25 5 4 5 5 7 SQL SELECT Commands 0.5 5 4 5 5 7.1 SELECT Statements 0.25 5 4 5 5
7.2 Aggregating Query Results 0.25 5 4 5 5
8 SQL Expressions 0.75 5 4 5 5
8.1 SQL Expressions 0.25 5 4 5 5
8.2 Functions in SQL Expressions 0.5 5 4 5 5
Day Three 6.25
10.5 Multiple Table UPDATE and DELETE Statements 0.25 5 4 5 5
11 MySQL Database Driven Web Based Forms 3.75 1 5 3 3
11.1 Connecting to MySQL 0.25 1 5 3 3
11.2 Retrieving Data 1 1 5 3 3
11.3 Inserting Data 0.5 1 5 3 3
11.4 Updating Existing Data 0.5 1 5 3 3
11.5 Deleting Data 0.5 1 5 3 3
11.6 MySQL Metadata 0.5 1 5 3 3
11.7 MySQL Error Information 0.5 1 5 3 3
Day Four 7
12 Session Handling 2 1 5 3 3
12.1 What is Session Handling 0.25 1 5 3 3
12.2 Session Handling Tasks 0.75 1 5 3 3
12.3 PHP Session Handling with MySQL 1 1 5 3 3
13 Object Oriented Programming 1 1 5 3 3
13.1 Benefits of OOP 0.25 1 5 3 3
13.2 Working with Objects 0.5 1 5 3 3
13.3 Exception Handling 0.25 1 5 3 3
14 Authentication 0.75 1 5 3 3
14.1 HTTP Authentication 0.25 1 5 3 3
14.2 PHP Authentication 0.5 1 5 3 3
15 Securing PHP & MySQL 1.75 3 5 4 4
15.1 PHP Configuration Parameters 0.5 1 5 3 3
15.2 Data Encryption 0.25 1 5 3 3
15.3 Securing the MySQL Server 0.25 5 5 5 5
15.4 Privileges 0.75 5 5 5 5
16 Conclusion 1.5 4 4 3 3
16.1 Course Overview 0.25 3 3 3 3
16.2 MySQL System Overview 0.25 3 3 3 3
16.3 Training and Certification Website 0.25 3 3 3 3
16.4 Course Evaluation 0.25 5 5 3 3
16.5 Thank You! 0 5 5 3 3
16.6 Q&A Session 0.5 5 5 3 3
NOTES:
Using Scale of 0-5 on Importance (0-not needed, 5-high)
A duration of zero (0) means that the section should take less than 15 minutes (from 5 to 14) .
Some section/chapter durations are padded to adjust for zero durations as well as for labs quizzes, further practices and breaks. Lunch breaks are NOT accounted for in this
Developing Dynamic Web Applications with MySQL and
PHP Training Guide
This is the training guide for the Developing Dynamic Web Applications with MySQL and PHP training course. This training guide was developed using the MySQL 5.0 Community Edition - Generally Available (GA) Release and MySQL 5.1 Community Edition - Beta Release.
MySQL AB would like to thank the many individuals that played a part in bringing this training material to the numerous students who will benefit from the knowledge and effort that each of these contributors put into the training. Even though there were a large number of contributions from many MySQL AB personnel, the following list of contributors played a vital role in developing this material and ensuring that its contents were accurate, timely and most of all presented in a way that would benefit those that are utilizing it for the benefit of improving their skills with MySQL.
Max Mether, Course Development Manager
Jeffrey Gorton, Course Developer and Editor
Tom Hanlon, Subject Matter Expert
Developing Dynamic Web Applications with MySQL and PHP Training Guide Copyright © 2007 by MySQL AB
All rights reserved. No part of this training guide shall be reproduced, stored in a retrieval system, or transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise, without the written permission of MySQL AB. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this training guide, MySQL AB and the associated contributors assume no responsibility for errors or omissions. Nor is any liability assumed for damages resulting from the use of the information contained herein.
First Printing: May 2007 Trademarks
The copyright to this training guide is owned by the Swedish company MySQL AB. MySQL® and the MySQL logo are registered trademarks of MySQL AB. Other trademarks and registered trademarks referred to in this manual are the property of their respective owners, and are used for identification purposes only.
Warning and Disclaimers
Every effort has been made to make this training guide as complete and accurate as possible to meet the needs of the training herein, but no warranty or fitness is implied. The information provided is on an “as is” basis.
Worldwide Office Locations and Contact Information
MySQL HQ MySQL AB Bangårdsgatan 8 S-753 20 Uppsala Sweden Tel: +46-18-10 18 90 (Administration only) Fax: +46-18-10 18 91 Sales (Scandinavia): +46 730 234 111 US Headquarters MySQL Inc.Cupertino City Center
20450 Stevens Creek Boulevard Suite 350
Cupertino, CA 95014 USA
Sales & Support (Toll Free): 1-866-697-7522
Tel: 408-213-6540 Fax: 408-213-2807
MySQL Inc.
2510 Fairview Avenue East Seattle, WA 98102
USA
Tel: 206-336-2030 Fax: 206-336-2002
Germany, Austria and Switzerland MySQL GmbH Radlkoferstraße 2 D-81373 München Germany Tel. +49 (0)89 724 99-150 Fax: +49 (0)89 724 99-250 Finland MySQL Finland Oy Laaksotie/Dalvägen 10 02700 KAUNIAINEN/GRANKULLA Finland Tel/Fax: +358 9 852 1421 Sales/Myynti: GSM: +358 40 551 9358 Iko Rein France MySQL AB (France)
123, rue du Faubourg St. Antoine 75011, Paris
France
Tel (Sales / Service Commercial): 0800.908.683 Tel (outside France): 33 (0)1.43.077.099 Fax: 33 (0)1.43.077.100 Ireland
MySQL AB
Fleet Chambers, 3rd Floor 8-9 Westmoreland Street Dublin D2, Republic of Ireland Tel: +353 1 6177878 Fax: +44 207 900 3955 Japan MySQL K.K.
N30F Shinjuku Park Tower, 3-7-1 Nishi-Shinjuku, Shinjuku-ku, Tokyo 163-1030 Tel: 81-(0)3-5326-3133 Fax: 81-(0)3-5326-3001 Italy MySQL AB (Italy) Numero Verde: 800 788 345 UK Tel: 0845 300 4266 Fax: +44 207 900 3955 Australia, Asia & Pacific Tel: +61 2 83 073 824
Conventions Used in This Training Guide
This training guide uses certain typographical conventions:
• TEXT IN THIS STYLE is used for SQL statements; database, table, and column names; program listings and source code; and environment variables. Example: “To reload the grant tables, use the FLUSH PRIVILEGES statement.”
• Text in this style indicates input that you type in examples.
• Text in this style indicates the names of executable programs and scripts, examples being mysql (the MySQL command line client program) and mysqld (the MySQL server executable).
• Text in this style is used for variable input for which you should substitute a value of your own choosing.
• Filenames and directory names are written like this: “The global my.cnf file is located in the /etc directory.”
• Character sequences are written like this: “To specify a wildcard, use the '%' character.” • Text in this style is used for emphasis.
• Text in this style is used in table headings and to convey especially strong emphasis.
When commands are shown that are meant to be executed from within a particular program, the prompt shown preceding the command indicates which command to use. For example, shell> indicates a command that you execute from your login shell, and mysql> indicates a statement that you execute from the mysql client
program:
shell> type a shell command here
mysql> type a mysql statement here
The “shell” is your command interpreter. On Unix, this is typically a program such as sh, csh, or bash. On Windows, the equivalent program is command.com or cmd.exe, typically run in a console window.
When you enter a command or statement shown in an example, do not type the prompt shown in the example. Database, table, and column names must often be substituted into statements. To indicate that such substitution is necessary, this manual uses db_name, tbl_name, and col_name. For example, you might see a statement like this:
mysql> SELECT col_name FROM db_name.tbl_name;
This means that if you were to enter a similar statement, you would supply your own database, table, and column names, perhaps like this:
mysql> SELECT author_name FROM biblio_db.author_list;
SQL keywords are not case sensitive and may be written in any lettercase. This training guide uses uppercase. In syntax descriptions, square brackets ('[' and ']') indicate optional words or clauses. For example, in the following statement, IF EXISTS is optional:
DROP TABLE [IF EXISTS] tbl_name
When a syntax element consists of a number of alternatives, the alternatives are separated by vertical bars ('|'). When one member from a set of choices may be chosen, the alternatives are listed within square brackets ('[' and ']'): TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)
When one member from a set of choices must be chosen, the alternatives are listed within braces ('{' and '}'): {DESCRIBE | DESC} tbl_name [col_name | wild]
An ellipsis (...) indicates the omission of a section of a statement, typically to provide a shorter version of more complex syntax. For example, INSERT ... SELECT is shorthand for the form of INSERT statement that is followed by a SELECT statement.
Supporting Images Used in This Training Guide
The following is a summary of the standard images used in this manual to support the instruction:
IMAGE NAME DESCRIPTION Preparation This image is used to describe the
steps required to be completed prior to performing a hands-on exercise.
Written Exam This image is used to identify that the student is going to be tested upon the material previously presented in the instructional material.
InLine Lab Throughout the course the instructor will conduct labs in line with the instruction, which are designed to help you to understand the “nuts and bolts” (inner-workings) of the topic.
Further Practice Lab
This image is used to convey to the student that there is a final exercise to complete prior to the completion of the chapter.
Student Notes This image identifies an area on a page designated for students to write notes associated with the class.
Slide Number
Boxes This image is used throughout the course guide to indicate the existence and number of a corresponding instruction slide. 123
COURSE OBJECTIVES
This instructor-led course is designed for students planning on developing applications that make use of MySQL and PHP. Through a hands-on approach, this course will help developers with a reasonable knowledge of PHP improve their development skills to the point where they can use PHP and MySQL as a cohesive platform for the development of complex dynamic web applications. Upon completion of this course you will be able to:
• Verify the correct installation of the LAMP Components
• Utilize the basic components of PHP to build a foundation for more complex web applications • Understand the basic components of MySQL to build a foundation for the development of fully
dynamic and database-driven sites • Manage Databases within MySQL • Manage Tables within MySQL
• Utilize SQL query commands to retrieve data from the MySQL Database • Utilize SQL expressions
• Manipulate table data using the SQL Data Manipulation Language (DML) commands • Retrieve data from multiple MySQL tables using Joins
• Create web based forms that interact with the end user and the data within MySQL • Utilize session handling within PHP to track user-specific behavior and preferences.
• Utilize error and exception handling in PHP to detect, log and report issues that may arise during program execution
• Integrate PHP's object oriented programming (OOP) functionality into the design of web applications • Authenticate user identities utilizing PHP built-in authentication capabilities and MySQL's user and
privilege management capabilities
• Securing PHP and MySQL to improve the integrity of the application and subsequent data
2
Table of Contents
1 Introduction ... 1-1 1.1 Learning Objectives... 1-1 1.2 MySQL AB... 1-2 1.3 MySQL Products ... 1-3 1.3.1 MySQL Database Products... 1-3 1.3.2 MySQL GUI Tools ... 1-5 1.3.3 MySQL Drivers ... 1-5 1.4 MySQL Services... 1-6 1.4.1 MySQL Training ... 1-6 1.4.2 MySQL Certification ... 1-6 1.4.3 MySQL Consulting... 1-6 1.4.4 MySQL Support... 1-6 1.5 Community vs. Enterprise ... 1-7 1.6 MySQL Partners ... 1-9 1.7 Supported Operating Systems...1-10 1.8 MySQL Certification Program ...1-11 1.9 Training Curriculum Paths...1-12 1.10 MySQL Website ...1-14 1.10.1 MySQL Community Web Page ...1-15 1.11 Chapter Summary ...1-18 2 LAMP ... 2-2 2.1 Learning Objectives... 2-2 2.2 LAMP Architecture ... 2-3 2.3 Apache Server... 2-4 2.4 MySQL Server... 2-8 2.5 PHP...2-12 2.6 Putting It All Together...2-16 2.7 Chapter Summary ...2-19 3 PHPFOUNDATIONS... 3-1 3.1 Learning Objectives... 3-1 3.2 Delimiting PHP Code ... 3-2 3.3 Comments... 3-3 3.4 Construct Feedback ... 3-5 3.5 PHP Data Types... 3-7 3.5.1 Scalar Data Types ... 3-7 3.5.2 Compound Data Types ... 3-9 3.5.3 Special Data Types ...3-11 3.6 Flow Control Statements ...3-12 3.6.1 if ... else ... elseif ...3-13 3.6.2 Switch ...3-15 3.6.3 While ...3-16 3.6.4 Do ... While...3-16 3.6.5 For...3-17 3.6.6 Foreach ...3-18 3.6.6.1 Break and Continue ...3-19 3.7 User Defined Functions ...3-20 3.8 Chapter Summary ...3-22 4 MYSQL FOUNDATIONS... 4-1 4.1 Learning Objectives... 4-1
4.4 The mysql Client ... 4-6 4.4.1 Using mysql Interactively... 4-6 4.4.2 Statement Terminators... 4-7 4.5 MySQL Query Browser... 4-9 4.5.1 Using the Connection Dialog... 4-9 4.5.2 The MySQL Query Browser Window...4-10 4.6 Chapter Summary ...4-13 5 MANAGING DATABASES... 5-1 5.1 Learning Objectives... 5-1 5.2 Database Properties ... 5-2 5.3 Database Design Practices ... 5-4 5.4 MySQL Identifiers... 5-5 5.5 Creating Databases ... 5-6 5.6 Drop a Database... 5-7 5.6.1 CAUTION: When Using DROP DATABASE... 5-7 5.7 Chapter Summary ...5-11 6 MANAGING DATABASE TABLES... 6-1 6.1 Learning Objectives... 6-1 6.2 Table Properties... 6-2 6.2.1 Storage Engines ... 6-3 6.3 MySQL Data Types... 6-5 6.3.1 Numeric Data Type... 6-6 6.3.2 String Data Types ... 6-8 6.3.3 Temporal Data Types... 6-9 6.4 Creating Database Tables ...6-10 6.4.1 Displaying Table Creation Information ...6-11 6.4.2 Table Properties ...6-12 6.4.3 Column Options...6-13 6.4.4 Temporary Tables...6-14 6.5 Altering Tables ...6-15 6.5.1 Add Columns ...6-15 6.5.2 Remove Columns...6-16 6.5.3 Modifying Columns...6-16 6.5.4 Changing Columns ...6-17 6.5.5 Renaming Tables ...6-17 6.5.6 Emptying Tables...6-17 6.6 Drop Tables ...6-18 6.7 Table Indexing...6-19 6.8 Foreign Keys...6-21 6.9 Chapter Summary ...6-30 7 SQL SELECT COMMANDS... 7-1 7.1 Learning Objectives... 7-1 7.2 SELECT Statements ... 7-2 7.2.1 SELECT with DISTINCT ... 7-4 7.2.2 SELECT with WHERE ... 7-6 7.2.3 SELECT with ORDER BY... 7-8 7.2.4 SELECT with LIMIT...7-10 7.2.5 SELECT with MySQL Query Browser ...7-12 7.3 Aggregating Query Results...7-13 7.4 Chapter Summary ...7-18
8.3.1 Comparison Functions ... 8-9 8.3.2 Control Flow Functions ...8-10 8.3.3 Numeric Functions...8-13 8.3.4 String Functions...8-15 8.3.5 Temporal Functions ...8-16 8.3.6 NULL-Related Functions ...8-18 8.4 Chapter Summary ...8-23 9 SQL DMLCOMMANDS... 9-1 9.1 Learning Objectives... 9-1 9.2 The INSERT Statement ... 9-2 9.2.1 INSERT with SELECT... 9-3 9.2.2 INSERT with LAST_INSERT_ID ... 9-3 9.2.3 INSERT with NULL Values... 9-3 9.3 The UPDATE Statement ... 9-4 9.3.1 UPDATE with SET ... 9-5 9.3.2 UPDATE with ORDER BY ... 9-5 9.3.3 UPDATE with LIMIT ... 9-5 9.4 The REPLACE Statement ... 9-6 9.4.1 INSERT with ON DUPLICATE KEY UPDATE Instead of REPLACE ... 9-7 9.5 The DELETE Statement ... 9-8 9.6 Chapter Summary ...9-13 10 SQL JOINS...10-1 10.1 Learning Objectives...10-1 10.2 What is a Join?...10-2 10.3 Inner Joins...10-3 10.3.1 Comma Separated Inner Join...10-3 10.3.2 The INNER JOIN Keywords...10-4 10.4 Outer Joins...10-6 10.4.1 Left Outer Join...10-6 10.4.2 Right Outer Join...10-8 10.5 Using Qualifiers and Aliases ...10-9 10.5.1 Qualifying Column Names ...10-9 10.5.2 Qualifying and Aliasing Table Names...10-11 10.5.3 Self Joins...10-12 10.6 Multi-Table UPDATE and DELETE Statements ...10-13 10.7 Chapter Summary ...10-17 11 MYSQL DATA DRIVEN WEB BASED FORMS...11-1 11.1 Learning Objectives...11-1 11.2 Connecting to MySQL...11-2 11.2.1 Independent Connection Information ...11-4 11.3 Retrieving Data...11-6 11.4 The Web Interface ...11-10 11.5 Update Existing Data...11-16 11.6 Delete Data ...11-18 11.7 Inserting Data ...11-21 11.8 MySQL Metadata ...11-24 11.8.1 PHP Functions for Retrieving Metadata ...11-27 11.9 MySQL Error Information...11-29 11.10 Chapter Summary ...11-31 12 SESSION HANDLING...12-1 12.1 Learning Objectives...12-1 12.2 What is Session Handling...12-2
12.5 Chapter Summary ...12-13 13 OBJECT ORIENTED PROGRAMMING...13-1 13.1 Learning Objectives...13-1 13.2 Benefits of OOP...13-2 13.3 Working with Objects...13-3 13.4 Exception Handling ...13-6 13.5 Chapter Summary ...13-9 14 ESTABLISHING USER AUTHENTICATION...14-1 14.1 Learning Objectives...14-1 14.2 HTTP Authentication...14-2 14.3 PHP Authentication ...14-3 14.3.1 Hard-Coded Authentication...14-4 14.3.2 File-Based Authentication ...14-5 14.3.3 Database Authentication ...14-7 14.4 Chapter Summary ...14-10 15 SECURING PHP AND MYSQL...15-1 15.1 Learning Objectives...15-1 15.2 Building a Wall of Security ...15-2 15.2.1 PHP Configurations ...15-3 15.3 Data Encryption...15-5 15.4 Securing the MySQL Server (mysqld) ...15-6 15.5 MySQL Access Privilege System...15-8 15.5.1 MySQL versus Operating System Accounts ...15-8 15.5.2 Creating and Dropping User Accounts ...15-9 15.5.3 Setting Account Password ...15-9 15.6 Privileges ...15-10 15.6.1 Types of Privileges That MySQL Supports...15-10 15.6.2 The Grant Tables ...15-12 15.6.3 Granting Privileges ...15-14 15.6.4 The GRANT Statement ...15-15 15.6.5 The SHOW GRANT Statement...15-16 15.6.6 Revoking Privileges...15-18 15.6.7 Changing Account Passwords...15-19 15.6.8 When Privilege Changes Take Effect ...15-19 15.7 Chapter Summary ...15-21 16 CONCLUSION...16-1 16.1 Course Objectives...16-1 16.2 Training and Certification Website...16-2 16.3 Course Evaluation...16-4 16.4 THANK YOU! ...16-5 16.5 Q&A Session ...16-6
Appendix A Introduction to Linux...A-1 Appendix B Basic vi Commands ...B-1 Appendix C Upload Form Example...C-1 Appendix D PHP Strings and Regular Expressions...D-1 Appendix E Further Practice Solutions... E-1
Instructor Notes: This slide number corresponds to a slide with the course Table of Contents (Structure). There will be one of these slides before each chapter to show the current chapter being covered. This is to give the students a sense of the progress of the class.
C
HAPTER
1
INTRODUCTION
5 4
Instructor Notes: This slide number corresponds to a slide with information that is not included in the instructor or student guides.
It is pertaining to getting to know the students in the class.
Instructor Notes: This slide number corresponds to a slide with information that is not included in the instructor or student guides.
It is pertaining to the checklist of items to discuss before starting the class.
Instructor Notes: This slide number corresponds to a slide with information that is not included in the instructor or student guides.
It is pertaining to explaining the format of the majority of the slides.
1 INTRODUCTION
1.1 Learning Objectives
This chapter introduces you to MySQL and MySQL AB, the support organization for MySQL. In this chapter, you will learn:
• The details of the MySQL AB Business Model
• What MySQL products and professional services are available from MySQL AB • Define and differentiate between Enterprise and Community
• Which Operating Systems are currently supported
• What is available through the MySQL Community web page • What is the MySQL Certification program
• Which courses are available to you after this course
6
7
8
MySQL is installed on every
continent in the world
(Yes, even Antarctica!)
1.2 MySQL AB
MySQL is a Relational Database Management System (RDBMS). MySQL AB develops and markets a family of high performance, affordable database servers and tools. Contributing to building the mission-critical, high-volume systems and products worldwide is what makes MySQL the world’s most popular open source database, as well as its reliability, excellent performance and ease of use.
MySQL is not only the world’s most popular open source database, it’s also the fastest growing database in the industry, with more than 11 million active installations and 50,000 downloads per day.
The company was founded in Sweden by two Swedes and a Finn: David Axmark, Allan Larsson and Michael "Monty" Widenius who
have worked together since the 80's. MySQL AB(Swedish for “Inc.”) is the sole owner of the MySQL server source code, the MySQL trademark and the mysql.com domain worldwide.
MySQL has always been a strong supporter of the Open Source philosophy and the Open Source Community, and strives to work with partners that share the same values and mind-set.
• The MySQL mission: To make superior database software available and affordable to all.
1.3 MySQL Products
1.3.1 MySQL Database ProductsMySQL provides database products to meet the needs of ISV/OEM, Enterprise, and Community Server users. MySQL database products are recognized for superior ease of use, performance, and reliability.
• Enterprise Server
o The most reliable, secure and up-to-date version of the world's most popular open source database for cost-effectively delivering E-commerce, Online Transaction Processing (OLTP), and multi-terabyte Data Warehousing applications. (Available only with the MySQL Enterprise
subscription).
• Community Server
o The MySQL database server for open source developers and technology enthusiasts who want to get started with MySQL. Supported by the large MySQL open source community. Under the General Public License (GPL), benefits to the open source community include a commercial-grade framework that is free of charge.
• Embedded Database
o The most popular choice for OEMs/ISVs who want to cost-effectively embed or bundle a reliable and high-performance relational database.
• Cluster
o A fault tolerant database clustering architecture for deploying highly available mission-critical database applications.
• MaxDB
o Formerly known as SAP DB, MaxDB is the result of a strategic alliance between MySQL and SAP to jointly develop and market an open source database for SAP/R3.
MySQL Enterprise
A comprehensive set of enterprise-grade software, support and services directly from the developers of MySQL to ensure the highest levels of reliability, security and uptime. As a proactive service that helps you eliminate problems before they occur, it gives you everything you need in a single, unified offering to successfully develop and deploy business critical applications using MySQL.
• Enterprise Server
o The most reliable, secure and up-to-date version of the world's most popular open source database for cost-effectively delivering E-commerce, Online Transaction Processing (OLTP), and multi-terabyte Data Warehousing applications.
• Network Monitoring and Advisory
o Services to ensure the maximum quality, uptime, and performance of MySQL Enterprise systems.
• Production Support
o Around-the-clock support for the MySQL Enterprise server, along with consulting services and self-help knowledge aids.
• Network Enterprise Dashboard...
12
1.3.2 MySQL GUI Tools
A comprehensive set of open source visual database tools. These easy to use visual tools enable database developers and DBAs to be more productive.
• MySQL Migration Toolkit
o Using a wizard-driven interface, the MySQL Migration Toolkit implements a proven methodology and walks you through the necessary steps to successfully complete a database migration project.
• MySQL Administrator
o A powerful visual administration console that enables you to easily administer your MySQL environment and gain significantly better visibility into how your databases are operating. • MySQL Query Browser
o An extremely user-friendly visual tool for creating, executing, and optimizing SQL queries for your MySQL Database Server.
Bundled Tools
The MySQL graphical user interface (GUI) tools discussed here come bundled together when downloaded from the MySQL website.
1.3.3 MySQL Drivers
Standards-based drivers (also known as Connectors) enable developers to build database applications in their language of choice. In addition, a native C library allows developers to embed MySQL directly into their ‘C’ applications. MySQL provides the following drivers:
• MySQL Connector/ODBC
o Connect to a MySQL database server using the ODBC database API on all Microsoft Windows and most Unix platforms.
• MySQL Connector/J
o A native Java driver that converts JDBC (Java Database Connectivity) calls into the network protocol used by the MySQL database.
• MySQL Connector/Net
o An ADO.NET provider that allows access from the .NET platform using the network protocol used by the MySQL database.
• MySQL Connector/MXJ
o An MBean that allows seamlessly embedding the MySQL database server within the J2EE environment.
• MySQL Connector/PHP (New to the MySQL family of Connectors)
o For connecting to MYSQL from PHP on Windows (not necessary for Unix variants).
14
1.4 MySQL Services
1.4.1 MySQL Training
MySQL offers a comprehensive set of MySQL training courses that give you a competitive edge in building world-class database solutions.
• Courses can be chosen individually, as part of a bundle, and/or following our suggested curriculum path for Developers and Database Administrators (DBAs).
1.4.2 MySQL Certification
MySQL Certification Program is a high quality certification program that provides Developers and DBAs with the credentials to prove they have the knowledge, experience and skills to use and manage MySQL Server.
• With MySQL personal certifications, you get to show that you are among the best-of-breed MySQL users and lay the foundation for becoming a trusted and valuable resource for your company and customers.
1.4.3 MySQL Consulting
MySQL AB offers a full range of consulting services. Whether you are starting a new project, needing to optimize an existing MySQL application, or migrating from a proprietary database to MySQL, we have an affordable solution for you.
• Using industry best practices and proven methodologies, your MySQL certified consultant will help you deliver on-time and on-budget.
1.4.4 MySQL Support
MySQL offers a full range of support options. MySQL Technical Support is designed to save you time and to ensure you achieve the highest levels of performance, reliability, and uptime.
• Community support o Mailing Lists
o Forums (http://forums.mysql.com/)
o Community Articles (http://dev.mysql.com/tech-resources/articles)
o Bugs Database (http://bugs.mysql.com/)
o No direct access to support engineers
o PlanetMySQL Blogs (http://www.planetmysql.org/)
o MySQL Reference Manuals (http://dev.mysql.com/doc)
o MySQLForge (http://forge.mysql.com/)
• Purchased support
o Enterprise subscription o Support for MySQL Cluster
o Support for MySQL Embedded (OEM/ISV)
1.5 Community vs. Enterprise
There are many things to consider when choosing between using the Community server versus the Enterprise server subscription. The following charts summarize the uses and requirements for each and do a comparison to assist in understanding the differences.
How does the MySQL Community work?
The left side of this chart shows what MySQL provides to the user community. On the right, is what our user community provides back to us and to the whole community.
MySQL AB
MySQL Community
New Ideas Bug Reports… Help, Forums… Spread MySQL Goodwill… Code Contributions… Open Source Database Server Minimum Two Binaries/Year Fixes/Feature Constant Source Code Drops 17 Free Mgmt Tools/Connectors Help, Forums…
How does the MySQL Enterprise work?
The above chart shows the progression of code feature implementation into the MySQL Enterprise Server; from user input, to MySQL Community Server placement/testing, to further improvements which are placed in the MySQL Enterprise Server product.
MySQL
Enterprise Server
MySQL
Community Server
Community /
Third Party
Development
Design/Development
•
Community Recommendations•
Community Server Code Contributions - Direct•
Community Code Contributions – MySQL Forge•
Third party contributionsCommunity Purpose
Enterprise Grade
•
Delivery of Community and Customer Recommendations•
Acceptance/Rejection of Community Code Contributions•
Community Testing and Validation•
“As Needed” Release Schedule, including Bug Fixes and New Features•
Selected Feature Set for Enterprise Installations•
Predictable Service Pack Schedule•
Strong Internal Quality Assurance Testing•
External Quality Assurance Testing•
Certified for Popular Platforms and Applications•
Formal Technical SupportMany Ideas /
Submissions Selected Submissions Become Features Mature Features Only
1.6 MySQL Partners
MySQL has had the privilege of forming alliances with excellent partners and attracting some of the most impressive customers in the industry! When you join our ranks, you are joining a winning team with a wide variety of MySQL implementations. It never ceases to amaze us, the innovative and powerful ways in which our tools are being used. To name only a few;
Education
• AUF (Agence Universitaire de la Francophonie)
• University of California, Berkeley World Wide Web
• Yahoo! • Wikipedia Finance
• Chicago Mercantile Exchange • HypoVereinsbank
Healthcare
• Finnish National Public Health Institute • UNICEF Manufacturing • Yamaha • Epson Technology • Apple • Motorola Media • BBC
• The Weather Channel (Weather.com)
Telecom
• AT&T Wireless • Nokia
Open Source Projects • PHProjekt • eGroupWare Transport • AXS Marine • Viasuisse Retail • Axfood AB • Macy’s Travel • Continental Airlines • Orbitz
"We have used MySQL far more than anyone expected. We went from experimental to mission-critical in a couple of months."
Jeremy Zawodny--MySQL Database Expert Yahoo! Finance
1.7 Supported Operating Systems
MySQL runs on more than 20 platforms, giving users the kind of flexibility that puts them in control. Windows, Linux and Solaris are the most popular operating systems for running MySQL. Versions of MySQL are currently available for the following operating systems;
• Windows (multiple) • Mac OS/X • SCO
• Linux (multiple) • HP-UX • Open BSD
• Solaris • IBM AIX • SGI Irix
• FreeBSD • QNX • Novell NetWare
This list is continually being updated. For the most current information, please check our website:
http://www.mysql.com . Source code and special builds are also available.
1.8 MySQL Certification Program
The MySQL Certification Program is a high quality certification program that provides developers and DBAs with the credentials to prove they have the knowledge, experience and skills to use and manage MySQL Server. MySQL will offer two tiers of certification for MySQL 5.0 (most recent version):
1. Certified MySQL 5.0 Developer (CMDEV) - proves mastery of the fundamental skills of using MySQL including creating and using databases and tables, inserting, modifying, deleting, and retrieving data.
2. Certified MySQL 5.0 Database Administrator (CMDBA) - proves mastery of the ability to manage MySQL Server including such advanced areas of database management, installation, security, disaster prevention and optimization.
3. Certified MySQL Cluster DBA (CMCDBA) - part of the DBA track which represents an advancement level exceeding CMDBA certification. In order to attain CMCDBA certification, you must attain CMDBA certification and pass one CMCDBA exam.
The chart above shows the curriculum path for Developers, Database Administrators and Cluster. They show the current course available for certification preparation, the respective exams, and the receipt of the certification.
Certification Web Page
For more information on the certification program and the content of the exams, see our Certification web page; http://www.mysql.com/certification/ .
Exam Administration
All exams are administered through one of more than 3,000 Pearson VUE testing centers available world-wide. And through the Linux Professional Institute proctor network. Visit the MySQL certification webpage, online forum or email [email protected] for more information.
1.9 Training Curriculum Paths
MySQL offers the most comprehensive set of MySQL training courses that enable the building of database solutions and competitiveness now and into the future. In addition to our open courses, we also offer in-house training.
The MySQL training services staff has put together great courses designed for success, and an excellent training path for each individual to reach his/her training goals. There are two tiers to the curriculum paths: Developer Path
• Introduction Courses
o Developing Dynamic Web Applications with MySQL and PHP –This course will provide the tools needed for the development of dynamic web application. This course takes the student from the basics to the advanced features in four hands-on, heavy duty training days. Each student will have the opportunity to build a real-world application during the course.
Prerequisites: Basic experience with designing HTML pages including HTML forms and experience with any programming language. Basic PHP skills. 4 days in length.
o MySQL for Beginners – This course covers the fundamentals of SQL and relational databases, using MySQL as a teaching tool. Prerequisites: Basic computer literacy is
required. Previous experience with any command-line program (such as MS-DOS or the MS Windows command prompt) is beneficial. 4 Days in length.
• Intermediate Courses
o MySQL for Developers - This instructor-led course is designed for students planning on developing applications that make use of MySQL 5.0 (and higher). This course covers essential SQL statements for data design, querying, and programming. In addition, it will prepare students for the MySQL Developer certification. Prerequisites: Some experience
with Relational Databases and SQL. 5 days in length.
o MySQL 5.0 Upgrading and New Features – This instructor-led course will provide in-depth knowledge needed to become proficient using MySQL 5.0. This training course will provide quality time both on the topic, hands-on labs and with the expert instructor. Prerequisites:
Existing MySQL users who want to become proficient using MySQL 5.0. 3 Days in length.
• Advanced Courses
o Advanced Stored Procedures – This instructor-led course with focus on labs is designed to teach you how to maximize the use of stored procedures along with the knowledge to discern when an application should contain stored procedures and when they should not. 2 Days in length.
• Virtual Courses
o MySQL Database Design - This instructor-led virtual course is designed for developers who wish to use sound database design principles while creating MySQL databases. This course will prepare each student with the skills needed to create databases that maintain data consistency, data integrity and data accuracy while improving SQL efficiency. Students will be provided with demonstrations that will support their ability to apply their learning immediately upon completion of the training. 4 hours in length.
Database Administrator (DBA) Path • Introduction Courses
o MySQL for Beginners – This course covers the fundamentals of SQL and relational databases, using MySQL as a teaching tool. Prerequisites: Basic computer literacy is
required. Previous experience with any command-line program (such as MS-DOS) is beneficial. 4 Days in length.
• Intermediate Courses
o MySQL 5.0 Upgrading and New Features – This instructor-led course will provide in-depth knowledge needed to become proficient using MySQL 5.0. This training course will provide quality time both on the topic, hands-on labs and with the expert instructor. Prerequisites:
Existing MySQL users who want to become proficient using MySQL 5.0. 3 Days in length.
o MySQL for Database Administrators – This course covers essential DBA tasks such as, installation and upgrading, user management, disaster recovery, and optimization. In addition, it will prepare students for the MySQL Database Administrator certification.
Prerequisites: Some experience with Relational Databases and SQL. 5 days in length.
• Advanced Courses
o MySQL Performance Tuning – The MySQL Performance Tuning course is designed for Database Administrators and others who wish to monitor and tune MySQL. This course will prepare each student with the skills needed to utilize tools for monitoring, evaluating and tuning. Students will evaluate the architecture, learn to use the tools, configure the database for performance, tune application and SQL code, tune the server, examine the storage engines, assess the application architecture, and learn general tuning concepts. 4 Days in Length. o MySQL Cluster - Learn how to install and configure the cluster nodes to ensure high
availability. Also learn about all of the high availability features that are implemented in the new storage engine for the MySQL Cluster — fail-over between storage nodes, network partitioning protocol, two-phase commit and much more. Prerequisites: Attendance to the
MySQL for Database Administrators or an equivalent mastery of database concepts, SQL and the MySQL server. 3 Days in length.
Note: This is the sole course offering in the Cluster certification (CMCDBA) path.
o MySQL High Availability - This course is designed for experienced database administrators and system architects that want to analyze and form a basis of understanding different high availability options, including clustering and replication solutions within MySQL. This course will provide the tools required to make the decision of what high availability solution is appropriate and how to implement a system with the correct design.. 3 days in length.
1.10 MySQL Website
Everything you ever wanted to know about MySQL and more can be found on our website:
http://www.mysql.com. From the home page, you may navigate the web site with the tabs (pull-down
menus) across the top, the menu along the left side, and/or the many links on the page.
1.10.1 MySQL Community Web Page
The MySQL Community web page is located at http://dev.mysql.com and is maintained by MySQL AB.
This is the main support tool for the MySQL open source community and can provide valuable insight for Enterprise users also. Information such as the following can be found:
• Product Downloads
o MySQL Products are available under the "dual licensing" model. Under this model, users may choose to use MySQL products under the free software/open source GNU General Public License (commonly known as the "GPL") or under a commercial license. ISVs and Resellers, who are embedding and reselling MySQL as part of their own commercial solutions, can purchase a MySQL commercial license.
• Documentation
o The MySQL Reference Manual covers most of the areas of MySQL use. This manual is for both MySQL Community Edition and MySQL Network. If the answer(s) cannot be found from the manual, support can be purchased through MySQL Network, which provides comprehensive support and services. MySQL Network also provides a comprehensive knowledge base library that includes hundreds of technical articles resolving difficult problems on popular database topics such as performance, replication, and migration.
• Articles
o Check out the many articles available regarding specialized uses of MySQL, by the top experts from all over the globe.
• MySQL Forums
o Community and Enterprise Users alike can utilize the MySQL forums to interact with each other and the staff of MySQL AB. The forums are a great way to continue the community support for MySQL and keep both MySQL AB and the MySQL users in constant communication thus making MySQL even better.
• Mailing Lists
o MySQL provides its customers, both community and enterprise users, a way to keep up with the latest updates to MySQL software along with many other MySQL specific issues through the use of mailing lists. Mailing lists are similar to the forums but with the option to receive discussions and information about MySQL through a users local mail client. If a user cannot get an answer for their questions from one of our mailing lists, one option is to pay for support from MySQL AB, which puts the user in direct contact with MySQL developers.
• MySQL Bugs
o Bugs can be reported through the MySQL Bug page within MySQL, search for bugs submitted by MySQL users and MySQL AB staff and view bug statistics.
• PlanetMySQL Blogs
o Find the link to this site containing dozens of high quality articles every week from community and MySQL employees: http://www.planetmysql.org/ .
• And much more!
Lab 1-A
In this exercise you will review some web pages on the MySQL AB website. Make sure that your web browser window is displayed.
ACTION (You Do)
COMPUTER RESPONSE / Comments
1. Click in the text area of the Address/Location
Toolbar. The URL currently listed will be selected.
2. In the toolbar text area, type:
www.mysql.com The MySQL Homepage is displayed.
3. Click on the Products tab located at the top of the MySQL home page.
Scroll down the list to review the various product information provided.
A list of currently available products will appear, with links for further information and downloads.
4. Review the details of the new MySQL Enterprise program, by clicking on the Learn More >> link.
MySQL® Enterprise™ provides a comprehensive set of enterprise-grade software, support and services directly from the developers of MySQL to ensure the highest levels of reliability, security and uptime.
5. Click on the Services tab located at the top of the MySQL home page.
A list of currently available services will appear, with links for further information and downloads. 6. Review the details of the new MySQL
Training program, by clicking on the Learn More >> link.
Featured information on this page will be updated periodically. For specific Training sub-topics select one of the links in the sub-menu in the upper-left corner of the page.
7. From the Training web page, select the
Certification link in the sub-menu and review the contents.
Featured information on this page will be updated periodically. For specific Certification sub-topics select one of the links in the sub-menu in the upper-left corner of the page.
8. Click on the Services tab located at the top of
the MySQL home page. Returns to the top level Services page. 9. Review the details of MySQL Support
programs, by clicking on the Learn More >> link.
Shows the various support programs available.
10. Click on the Community tab located at the
top of the MySQL home page. The community page contains many links to more information and access to various open communication forums.
11. Click on the News & Events tab located at the
1.11 Chapter Summary
This chapter introduced you to MySQL and MySQL AB, the support organization for MySQL. In this chapter, you learned:
• The details of the MySQL AB Business Model.
• What MySQL products and professional services are available from MySQL AB. • Define and differentiate between Enterprise and Community
• Which Operating Systems are currently supported.
• What is available through the MySQL Community web page. • What is the MySQL Certification program.
• Which courses are available to you after this course.
CHAPTER 2
LAMP
29 28
2 LAMP
2.1 Learning Objectives
This chapter provides the knowledge and methods used to verify the correct installations of MySQL and PHP on the web server being utilized. At the completion of this chapter, you will be able to:
• Explain the LAMP architecture
• Verify that the Apache Server is installed and running • Verify that MySQL is installed and running
• Verify that PHP is installed and accessible
2.2 LAMP Architecture
The strength of the world wide web and the internet are that they stand upon open standards such as TCP-IP, SMTP, UDP, HTTP and the like. This allows a Mac to talk to another systems mainframe and to send email to a windows computer. This open-ness may or may not extend to the tools used to deliver or create content on the world wide web. Proprietary tools such as Macromedia/Adobe FLASH, ADOBE, Cold Fusion, Microsoft IIS are tools developed by companies to generate revenue.
An alternative paradigm exists. That is where individuals and companies collaborate in order to help each other to get various tasks done. This is called "open source". Open source software gives an end user the freedom to view and modify the source code. Typically they are free of cost but the main freedom is that the end user can view and modify the source code. This means that talented programmers who find ways to improve the source code can contribute. It means that companies that need a tool, say a webserver, can utilize Apache rather than paying for a commercial tool. In open source we all work together to get our jobs done. The value is in the access to quality tools without restrictions. In closed source products we pay someone else to solve our problems. They may or may not have our best interest in mind, or our needs and the needs for revenue may collide.
The Open source tools are Linux, Apache, MySQL and PHP. In many ways they can be used as a replacement or alternative to IDE's such as J2EE, ASP.NET.
• Linux - This is the operating system (O/S) of the LAMP architecture and serves as foundation for the rest of the applications used in the architecture. Linux, which refers to the Linux kernel that is a Unix-like operating system kernel, can be seen in many different "flavors" of operating system distributions that utilize the linux kernel to include Fedora, Ubuntu, Knoppix, OpenSUSE, etc. The majority of the distributions (along with the Linux kernel itself) is released under the GNU General Public License (GPL) and developed by contributors worldwide.
• Apache - Apache refers to the Apache HTTP Server that is a web server that can be run on Unix-like systems (Linux for example), Microsoft Windows, Novell NetWare, Mac OS X and other operating systems. The Apache web server has played a major role in the success of the web by being a direct competitor to early proprietary web servers and opening the path for individuals and organizations to place their own "voice" on the web with little to no cost. The Apache Software Foundation, which is a non-profit corporation to support Apache software projects, develops and maintains the actual code (with a large number of open community of developers). The software itself is released under the Apache License which identifies the Apache HTTP Server as free software.
• MySQL - MySQL refers to the multithreaded, multi-user SQL database management system which has more than 10 million installations worldwide. Supported by MySQL AB, a company which is dual headquartered in Uppsala, Sweden and Cupertino, California, USA maintains the mysql DBMS system and holds the copyright to most of the codebase. The MySQL DBMS acts as the database component that provides the back-end to a large number of the web applications in place today. MySQL has been a large player in such web applications as online stores, social networking sites, wiki's, etc.
• PHP - PHP refers to the programming language that is used in the design of a large number of dynamic web pages. PHP is a reflective programming language, meaning the program code can be modified in the process of being executed based on its runtime behavior. This is ideal for the dynamic and interactive nature of the web. The rights to the PHP falls under a group called the "The PHP Group" and the ability to use the software is based on the PHP License, which according the Free Software Foundation is a license that identifies itself as being free software.
Instructor Notes: It may be more advantageous to show the websites where a person would download the files;
2.3 Apache Server
Being one of the components of the LAMP architecture, it is important to ensure that the Apache Server is installed and running on the Linux distribution being used. The easiest and least intrusive way of checking this is by opening a web browser on the local system and attempting to view the localhost web location. If there is a positive response when opening localhost from the web browser, then the Apache HTTP server is installed and running on the system. If the response is a web error, then the Apache HTTP is not running properly or not installed on the system.
Downloading the Software
The Apache Software Foundation is responsible for maintaining and supporting the Apache HTTP Server. Its website is http://www.apache.org which provides the details associated with the product lines that it offers and further information to install, configure and run the Apache HTTP Server on a wide range of operating systems. For the purpose of this course, the focus will be on installing and executing the Apache HTTP Server on the Linux distribution being used in the classroom environment.
Installing the Software
Once the files are downloaded to the operating system, it is time to extract the download file to an appropriate location for the server to execute from. In the linux environment, the choice used for the classrooms will be the /usr/local directory. This will be the location for all the software that will be installed in this class.
Configuring the Install Script
The next step is to configure the Apache HTTPd source tree for the Linux operating system and any additional specific requirements needed. The configuration of the install script is accomplished using the script configure included in the root directory of the distribution. There are multiple installation options that can be manipulated during the configuration. These configuration options are beyond the scope of this class. For class purposes, the default options will be chosen by running just the configure command without any options.
Build the Package
The next step is to build the various parts which form the Apache HTTPd package by simply running the make command. Due to the fact that the make command will build a base configuration it will take several minutes to compile. The actual time it takes to compile will vary widely depending on the hardware and the number of modules that have been enabled.
Once the package has been made with the make command, it is now time to install the package using make install. Once this step is accomplished, a directory called apache2 will be created that will house the apache server files.
Configuring
The Apache server is configured by placing directives (which pass optional settings into the Apache server) in plain text configuration files. By default, the main configuration file that the Apache server will look for is called httpd.conf. This file is located in the apache2/conf directory by default. The majority of configuration options associated with this file are beyond the scope of this class and the default httpd.conf directives will be used.
Static vs. Dynamic Modules
Apache can load static modules or they can be loaded dynamically. Dynamic is more common in the last few years. It takes less time to load a dynamic module. In order to see what modules are installed apache has an info and a status module. We will enable those modules and use them to make sure we have all the tools that we need. Mod status and Mod info display the output as a web page so we will need to make sure that the modules are loaded and that we have configured apache to deliver a "virtual URL " to display the content.
Starting and Testing the Server
To start the Apache server, choose the apache2/bin directory. This directory contains the files associated with executing the components of the Apache server. The Apache server can be started by executing apachectl start from this directory.
To test if the Apache server is working on the system, open a web browser on the local system and attempt to open http://localhost. If an error appears, the system is not configured properly and the steps should be repeated to determine the problem. If a web page appears, the server is running properly.
Lab 2-A
In this exercise you will determine if the Apache HTTP Server is currently running on the local operating system. If the Apache HTTP Server is not installed, you will follow the steps required to download and install the latest version.
ACTION (You Do)
COMPUTER RESPONSE / Comments
1. In your web browser, attempt to open the
localhost web page. If http://localhost displays a web page, then the Apache server is installed on your system and no additional steps in this lab are required. However, if an error is returned, complete the remaining steps to install the Apache server on your system.
2. In your web browser's toolbar text area, type:
httpd.apache.org/download.cgi This will open the download page for the Apache HTTPd Server. 3. In the best available version section, choose
the tar & zipped Unix Source link (httpd-#.#.#.tar.gz) and save the file to the /tmp directory.
The downloaded file will take some time to download and patience should be expected.
Note: The instructor may have already downloaded this file and made it available in a local file system, check with the instructor before downloading.
4. In the /usr/local directory, type the following:
tar -xvf /tmp/httpd-#.#.#.tar.gz replacing the #.#.# with the exact file identifier downloaded in step 3.
This will extract the file downloaded in step 3 into the /usr/local directory making a new subdirectory labeled httpd-#.#.#.
5. In the /usr/local/httpd-#.#.# directory, execute the following command: ./configure --prefix=/usr/local/
apache2 --enable-mods-shared= most
This step configures the Apache HTTPd source tree for your specific operating system with the root directory for the Apache server being the apache2 subdirectory in the /usr/local directory. In addition, the enable-mods-shared=most tells the apache install script to build most of the available loadable modules.
6. In the /usr/local/httpd-#.#.# directory, execute the following command: make
This step builds the various parts which form the Apache HTTPd install package.
7. In the /usr/local/httpd-#.#.# directory, execute
ACTION (You Do)
COMPUTER RESPONSE / Comments
8. With the apache server files completed, thereare a few configuration options that must be entered into the httpd.conf file before we are completed with the Apache step of the LAMP installation. However, a best practice is to make copies of existing configuration files prior to changing them. In the /usr/local/apache2/conf directory, create a copy of the httpd.conf file by entering the following command: cp ./httpd.conf httpd.conf.bak
The original httpd.conf is now copied to
httpd.conf.bak giving us the ability to go pack and start over if need be (without having to remember all the changes that we made to the file). In addition, adding components and then testing them before going on to adding the next component is not a bad approach if your configuration files continue to cause you problems.
9. With the original configuration copied it is time to edit the httpd.conf file using your favorite editor. For this example the vi editor will be used:
vi httpd.conf
vi stands for visual editor and is the default editor that comes with the Linux operating systems. This editor will allow us to make modifications to the httpd.conf file and complete the apache server setup.
Note: Refer to Appendix B for a simple list of vi commands if you are unfamiliar with this program. 10. First things that need to be addressed are the
modules and their status (will they be loaded). The two modules that need to be loaded (among many) are mod_info and mod_status. You can search for the mod_info module by typing in vi the following:
/mod_info.so
You can locate the mod_status module in the httpd.config file by typing in vi the following:
/mod_status.so
If these lines are commented out (the start of the line contains a pound sign:#), please remove the comment. If you do not see this lines then add them. If you do not see mod_info.so or mod_status.so in your modules directory then contact your instructor
11. The next step is add the "Virtual URL's" for status and info to the httpd.conf file. Locate the end of the file and add the following lines: <Location /info> SetHandler server-info </Location> <Location /status > SetHandler server-status </Location>
The first section is responsible for allowing mod_info's output to be available. mod_info provides a detailed configuration and module information for your server (corresponding to modules, mostly) in display-order that are hyper-linked.
The second section is responsible for allowing mod_status' output to be available. mod_status provides a representation of your web server's internal state and the comings and goings of its child processes.
12. Save your changes to the httpd.conf and exit the vi program.
ACTION (You Do)
COMPUTER RESPONSE / Comments
13. Test the configuration file for typos bytyping the following in the
/usr/local/apache2/bin directory: ./apachectl configtest
If the response is not "syntax OK" then return to the httpd.conf file and correct the error.
14. Once the configuration test of the httpd.conf file responds with "syntax OK", it is now time to start the server by executing the following command:
./apachectl start
This will start the Apache HTTPd server on your system. Note: If an error is shown after executing this command that says "Could not reliably determine the server's fully qualified domain name ...", simply ignore this error for class purposes. This is stating that Apache does not know what server name to listen for.
15. Open the web browser on your system and attempt to browse to http://localhost/info and http://localhost/status which refers to the apache server running on your system.
If both of the web pages come up and provide
information about the server, then apache was configured correctly and is executing properly. If an error appears, ask your instructor for assistance.
16. Stop the Apache server after ensuring that it is properly configured and running by typing the following command:
./apachectl stop
This will stop the Apache HTTPd server on the system. This step is important when installing the next two components of the LAMP architecture.
2.4 MySQL Server
Being another of the components of the LAMP architecture, it is important to ensure that the MySQL Server is installed and running on the Linux distribution being used.
Downloading the Software
MySQL AB is responsible for maintaining and supporting the MySQL Relation Database Server. Its website is http://www.mysql.com which provides the details associated with the product lines that it offers and further information to install, configure and run the MySQL Server on a wide range of operating systems. For the purpose of this course, the focus will be on installing and executing the MySQL 5.1 Server on the Linux distribution being used in the classroom environment.
Installing the Software
There are multiple methods for installing the MySQL Server on the operating system being used. For Linux, the easiest and least cumbersome is using a non-RPM distribution. These files can be located under the "Linux (non RPM packages) downloads" section. These files are called binary distributions that have already been compiled to work on the operating system that is selected. These files are tar files that have been compressed (tar.gz). Extracting the files is the extent of installing the actual MySQL binaries onto the operating system.
Setting the MySQL user
The MySQL server should not be run as root but instead should be run as another user whose sole purpose is to execute the MySQL server. This can be accomplished by creating a user called mysql that is assigned to a group called mysql. Once the user is created, all the data directory (/usr/local/mysql/data) should be assigned to this user and group. In addition, the mysql directory should at least be assigned to the mysql group (root can still own the directory).
Initializing the MySQL databases
There are a few databases that must be initialized prior to running MySQL for the first time. These databases include the mysql database which is used to control the security of all the databases on the server and test database which can be used for testing the server. There is a built in script to accomplish this step (/usr/local/mysql/scripts/mysql_install_db).
Starting and Testing the Server
With the MySQL binaries installed and the initial databases installed it is time to start the server. MySQL provides multiple ways to start the server, but the safest and most user friendly is using the mysqld_safe script located in the /usr/local/mysql/bin directory. This script should be run as the mysql user by executing the following conditions:
/usr/local/mysql/bin/mysql_script -u mysql &
After starting the MySQL server, the MySQL client (/usr/local/mysql/bin/mysql) can be run to interface with the server itself.
Root Password?
After the MySQL server is started for the first time, it is important to set a root password. MySQL, by default, does not have a password assigned to the root password that is given complete access to all the databases; including the mysql database that controls all the authentication for the databases. To set the root password initially, execute the following command:
/usr/local/mysql/bin/mysqladmin -u root password new_password
Lab 2-B
In this exercise you will determine if the MySQL Server is currently running on the local operating system. If the MySQL Server is not installed, you will follow the steps required to download and install the latest version.
ACTION (You Do)
COMPUTER RESPONSE / Comments
1. To determine if MySQL is installed on your system, enter the following command in the O/S terminal window:
whereis mysql
A directory will be shown where MySQL is installed (similar to the one listed below) if the system has MySQL installed.
mysql: /usr/local/mysql
If MySQL is installed already, you can either skip the remaining steps or contact your instructor to provide assistance in removing MySQL from your system so you can have practice in installing MySQL.
2. In your web browser's toolbar text area, type:
dev.mysql.com/downloads This will open the download page for the MySQL Server. Note: Ensure that the downloads panel in the left corner of the page has the 5.1 version of the server selected. If not, click on the 5.1 link under the Downloads section.
3. Choose the Download >> link under the "I Can
Do It MySelf" column. This is the link for the Community Server which will be used for class purposes. The Enterprise Server is the server that is recommended for production use systems, but in the case of this class, we will use the Community Server addition.
4. Choose the Linux (non RPM Packages) link from the list of available operating system downloads.
This link will place you in the Linux (non RPM Packages) section where you will be able to download the MySQL binary distribution. 5. Choose the download link to the left of the proper
operating system that your system is running. This step will perform the actual download of the MySQL installation files (mysql-5.1.#.*.tar.gz).
6. In the /usr/local directory, type the following:
tar -xvf /tmp/ mysql-5.1.#.*.tar.gz replacing the
/tmp/mysql-5.1.#.*.tar.gz with the exact file identifier downloaded in step 5 along with the exact location of the download.
This will extract the file downloaded in step 5 into the /usr/local directory making a new subdirectory labeled mysql-5.1.#.*.
