By Gail Seymour
In this series, we’re looking at the Linux operating system (OS) as a Web hosting platform. In the series overview, we looked at how Linux was developed and its popularity. We’ve also looked at the scripting languages used on Linux OS distributions. Later we will look at the open source movement. In this article, we’re focusing on MySQL database software.
Many Web sites provide user accounts, or Web applications that rely on dynamic information. Standard HTML browsers can’t gather and manipulate data without help. On a Linux platform, PHP scripting provides a way to find, filter and present data, but sites still need a way to store information on the server. The most popular way to do this is with a relational database, generally using MySQL. This is why many Linux servers are referred to as LAMP installations, because they have the Linux OS, the Apache Web server, MySQL database and either PHP, Perl or Python installed as the scripting language.
What happens when a user visits one of your dynamic pages on the LAMP system is:
- The Web browser requests the page from the Apache server
- Where the page includes a PHP script, Apache passes this request to the PHP interpreter which executes the code
- The PHP interpreter connects to the MySQL database and requests the content
- The data returns down the path, from MySQL to the PHP script, where it is stored as variables, used to create output and returned to the Apache server as HTML. This is then served back to the browser in a format it can understand.
What is MySQL?
MySQL is an open-source relational database management system (RDBMS) which can be downloaded from mysql.com. A relational database stores sets of information in tables, where column headings are field names, and each row represents a data set. Multiple tables can be connected by creating relations between fields, and data sets selectively pulled from the tables for inclusion in Web pages using Structured Query Language (SQL). Although the standard installation of MySQL does not support transactions, like SQL Server, these can be achieved by using the Berkley engine driver rather than the standard. Similarly support for Open Database Connections (ODBC) can be added by installing the MyODBC plugin.
This enables developers to create complex dynamic sites, such as sites with user accounts, without having to create individual pages for each user. Instead, a single user page template can pull in only the current users’ information. In addition, pages can be created to add, edit and delete user information without the administrator having to add them manually to the database.
Using Databases with MySQL
You can interact various ways with MySQL on a Linux server. One is through a command line tool, using SSH access. To do this you would first connect to your main user account, and then connect to the database from the command line prompt. This enables administrators to interact directly with the database, but most users will never use this.
Instead, the database server responds to requests from client programs, mostly PHP scripts. Web hosts provide a graphic user interface to make managing user databases easier. The most common of these is
phpMyAdmin
and is generally found on the Web hosting control panel.
Using phpMyAdmin
Your Web host might set your database up for you and provide you with the four pieces of information you need to connect to the database, or you may be able to choose your own:
- Database name. Standard practice is to name the database as username_ databasename, to enable multiple users to install the same Web application.
- Database user name. Frequently this will be the same as the database name.
- Password. You should make passwords as difficult to guess as possible, and keep a record of them in a secure place.
- Server name. The name of the server where the database resides. On Hostway.com accounts, for example, this would be in the format: mysql.databaseusername.yourdomainname.
If your host does not set the database up for you, you should be able to log into phpMyAdmin and create a database, user and password, and give the user permissions to connect to the database.
Databases are created empty, so you have to create the tables, assign column names and data types and then populate the tables with data. This can be done by entering commands in the phpMyAdmin interface, by running an SQL script, or using PHP scripts. Fortunately, many PHP based Web applications come with install scripts that will create the database structure for you. If you use one of these, you should always remember to delete the install files after initial setup, to prevent your site being reset accidentally or maliciously. The data can then be accessed using queries or views to serve only the selected data that matches the search criteria. This data can be used to show user specific information, and users might be able to add, edit or delete data, depending on the permissions you assign them.
Linux vs Windows
For a long time the decision was simple. If you wanted to use PHP scripting and a MySQL database, you opted for a Linux server. If you needed the enhanced features of SQL server, or were using an ASP script, you went with Windows. Although the boundaries between Linux and Windows have begun to blur, with Windows servers now capable of running MySQL , PHP and Apache, the transitions are less than perfect, with some features either not fully supported or not implemented at all. As such, the MySQL/PHP combination remains a solution ideally suited to Linux servers. Although it’s possible to connect to Microsoft SQL Server and even to run ASP from a Linux machine, this should only be attempted by experienced administrators. For the most part SQL Server and ASP are best used on a Windows OS.
Read the other articles in this series:
Linux Web Hosting Part 1: Overview
Linux Web Hosting Part 2: Scripting
Linux Web Hosting Part 3: Databases
Linux Web Hosting Part 4: Conclusion
About the Author
Gail Seymour has been a Web site designer for more than 10 years. During that time she has won three design awards and has provided the content and copy for dozens of Web sites and more than 50,000 Web pages.