| Table of Contents | Previous Next |
17.2 Using MySQL17.2.1 Installing and configuring MySQLBy supporting more than 10 different operating systems, MySQL has become one of the most popular free database packages available. From Microsoft Windows, LINUX, and Solaris, to SGI Iris, it is very likely that you will find a distribution directly suitable for your machine and operating environment. Also, since all source codes related to the product are freely available, you will have total freedom to modify the product to suit your own needs. Once you know the machine and operating system, you can download the MySQL package from the official Web site: www.mysql.com. If you don't want to compile the product yourself, the binary distribution is recommended. Since MySQL is designed to work on TCP/IP networking (or Internet), the package comes with two parts. The server part should be installed on a server and the client MySQL should be run on machines connecting to the server. Client and server can be the same machine running at the same time. To handle MySQL, some information on how to install and configure it on Red Hat LINUX and Microsoft Windows XP systems is provided. Installation for UNIX/LINUX systemsIf you are using the LINUX operating system or other UNIX system with a resource package module (RPM), you may want to download the following MySQL binary distribution:
where VERSION should be replaced by the version number of the installation. For example, MySQL-3.23.44-1.i386.rpm is the MySQL package with version number 3.23.44-1. For more information on the latest versions and platforms, the official site of MySQL www.mysql.com is recommended. These two files contain the standard minimal installation of the MySQL package. To perform the installation, execute the following command in a LINUX shell window: shell> rpm -i MySQL-VERSION.i386.rpm MySQL-client-VERSION.i386.rpm If you just want to install the MySQL client, you can run the command shell> rpm -i MySQL-client-VERSION.i386.rpm After installing the RPM files, the MySQL server program mysqld daemon should be up and running and you should now be able to start using MySQL. Installation for Microsoft Windows systemsCompared to UNIX/LINUX systems, installation and configuration on Windows are less automatic. For example, since MySQL is designed for networking, you may need to have PWS, IIS, or other Web server software on your Windows 9.x/NT/ 2000 or XP system to make it work. For our system, Windows XP with the Apache server is used. To install MySQL on a system, you can download the following file from the official MySQL site: mysql-VERSION-win.zip where the VERSION is the version number. Again, you may download the latest version for your installation. The MySQL used in this chapter is mysql-3.23.43-win.zip. The actual installation process is quite simple. All you have to do is to unzip the package into an empty directory and run the setup.exe program. By default, the MySQL package is installed in "C:\mysql." If you want to install MySQL elsewhere, it is recommended that you install it in "C:\mysql" first and then move it. If you move MySQL to another location, you may need to set up some configuration files or variables to tell the system where MySQL is located.
After installation, you will have the directories in Table 17.2 set up by MySQL. They are all under the installation directory of your choice. Suppose you have installed MySQL in "C:\mysql." Inside the bin directory, you will find two server and one standard client program:
To start the server program mysqld on Windows 9.x, you should run it inside a DOS window. That is, C:\mysql\bin> mysqld This will start mysqld in the background. You can kill the server program by executing C:\mysql\bin> mysqladmin -u root shutdown If you are using Windows NT/2000/XP, the server program is mysqld-nt. Normally you should install this program as a service. Go to the bin directory and install the server program once using C:\mysql\bin>mysqld-nt --install After you have installed the server as a service, you can start and stop the service by the two commands below: C:\> NET START mysql C:\> NET STOP mysql Whether you are using UNIX/LINUX or Microsoft Windows, the important thing is to get the server program running. Once a server program is running somewhere on a remote site, you can use the standard MySQL client program mysql to gain access to the server and start your database application. For example, if you have a MySQL server running in your local machine, you can start MySQL by C:\> mysql This program connects you to the local server so that you can start your MySQL database application. If MySQL is installed on a remote server called www.pwt-ex.com and you have a user name and a password to access its databases, you can make the connection by C:\>mysql h www.pwt-ex.com -u johnsmith p Enter Password: ******** where www.pwt-ex.com is the address of the site. The directive p instructs MySQL to display the "Enter Password" prompt for you to enter your password. If you are using UNIX/LINUX systems, the prompt C:\> should be replaced by your shell prompt shell> from a shell window. From now on, we generally use the shell> prompt to represent a DOS window or a shell window in the UNIX/LINUX environment. MySQL is a database system designed for TCP/IP networking (or Internet). The client program mysql is a console application with browsing functionalities providing an effective tool to control your databases. Of course, MySQL databases can also be accessed and controlled by SQL via browsers. This is the main topic of this chapter. If the connection is successful, you will see some introductory information followed by a mysql> prompt. When you see this prompt, your MySQL is ready for action. Typing help will show the commands available from the mysql monitor. A screen shot of MySQL running on Windows XP is displayed in Fig. 17.1. Figure 17.1. MySQL welcome message
A simple quit command from the client terminates the connection. A simple way to see whether you have connected to the server is to enter a query string at the mysql> prompt. For example, you can use the following commands to create a new database called personnel and view any database in MySQL: Example: ex17-01.sql - Creating Database On MySQL 1: mysql>CREATE DATABASE personnel; 2: mysql>SHOW DATABASES; You may issue the command SHOW database; (line 2) to see all the MySQL databases available to you. To delete a database in MySQL, you can use the DROP command, e.g., DROP DATABASE personnel. A screen shot of example ex17-01.sql in action is shown in Fig. 17.2. Figure 17.2. Show database
Some readers may find that the client/server dialog of MySQL is similar to Oracle's. You can use this mysql program to do just about everything you want with your databases. Indeed this client program and the client/server dialog have been the center of all database applications for many years. In this chapter, we will transform this client and client/server dialog into Web applications using Web pages and browsers. More precisely, we will show you how to program MySQL on the Web using different Web technologies. In addition to database functionalities, MySQL also offers security. All user accounts and passwords are handled entirely by MySQL to a high standard of protection. This feature, in many cases, can be used to compensate some of the security black holes of Windows systems. 17.2.2 Setting up user accounts, passwords, and securityMySQL is a database package with networking (or Internet) in mind. Without security anyone with the client program mysql can gain access to your databases. Also, just like all other database systems, MySQL at some point may store important information about you, your colleagues, and/or your company business that you don't want to share with others. Therefore, it is recommended that security measures be imposed as soon as MySQL is up and running. At a minimum level, every user should have a user account and be protected by a password. When MySQL is installed on a system, it will create a root account and maybe some anonymous (no user name) accounts automatically. All user accounts, passwords, and security are stored in a database called mysql (see Fig. 17.2). The root account has the absolute power to change everything inside the database system. Therefore, your first tasks would be to:
To set up a password to the root, the following commands can be used from the mysql prompt:
Example: ex17-02.sql - Adding Password To Root Account
1: shell> mysql -u root mysql
2: mysql> USE mysql
3: mysql> UPDATE user SET Password=PASSWORD('my_password')
4: -> WHERE user='root';
5: mysql> FLUSH PRIVILEGES;
The first line connects to the MySQL server as the root. If the connection is successful, the mysql> prompt appears. The second line is to use the mysql database as the current database. Line 3 is an SQL statement to update the user table and set up a password "my_password" to the root account. The password is encrypted by the PASSWORD() function. Note that you can split a long command into two lines such as lines 34. By executing the FLUSH PRIVILEGES; statement the change has immediate effect. To remove any empty account, you can use the DELETE command: Example: ex17-03.sql - Deleting Anonymous Accounts 1: mysql> DELETE FROM user WHERE User= ""; 2: mysql> FLUSH PRIVILEGES; Once you have done this, your MySQL is protected by the password. The following local and remote access will be denied: shell> mysql shell> mysql u root mysql shell> mysql h www.pwt-ex.com If you have a machine with the client program mysql, you can access the remote server (www.pwtex.com) using the new password as Shell> mysql h www.pwt-ex.com -u root p Enter Password: **** Now you have the power to add new users to MySQL. For example, to add a new user, you can use
Example: ex17-04.sql - Creating New Account With Default Privileges
1: mysql> INSERT INTO user (Host,User,Password)
2: -> VALUES ('%','johnsmith',PASSWORD('john'));
3: mysql> INSERT INTO user (Host,User,Password)
4: -> VALUES ('localhost','johnsmith',PASSWORD('john'));
This statement will add two records to the user table from the database mysql. This database controls all administrations of MySQL. The first record is
Host = '%'
User = 'johnsmith'
Password=PASSWORD('john')
This will set up a new account for johnsmith with default privileges. By using Host='%', he can connect to the server from anywhere using the user name johnsmith and password john. The second statement (lines 34) is used to set up the same privileges on the server machine (localhost) for johnsmith so that he can use the server machine to connect. To set up an account for johnsmith with all privileges, you can use
Example: ex17-05.sql - Creating New Account With All Privileges
1: mysql>DELETE FROM user WHERE user='johnsmith';
2: mysql> INSERT INTO user VALUES ('%','johnsmith',PASSWORD('johnsmith'),
3: ->'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
The statement in line 1 is to delete any existing user johnsmith before new settings can be made. The 14 Ys set all privileges as true. MySQL also allows you to set privileges on individual databases. For example, you can use the GRANT command to grant certain SQL keywords or statements to johnsmith. Example: ex17-06.sql - Granting Privileges To Existing Account 1: mysql> GRANT SELECT,INSERT,UPDATE,DELETE,DROP,LOAD DATA LOCAL INFILE 2: -> ON personnel.* TO johnsmith@'%' IDENTIFIED BY 'johnsmith' This statement only allows user johnsmith to use SELECT, INSERT, UPDATE, DELETE, CREATE, DROP and LOAD DATA commands on database personnel when he connects from a remote client. Now, let's create some databases and tables on MySQL for our programming purposes. 17.2.3 Creating tables for databasesTables are important for all SQL databases (or relational databases); a database without a table (empty database) is not very useful. In this section, a simple method is introduced to create a table in MySQL. This table is called people and will use the information given in Table 17.1. In order to use memory effectively, the data type of each field (or column) of the table people is defined as:
Also, the id field should be the primary key for MySQL to identify each record. Therefore id cannot be null and should be incremented by 1 automatically. In terms of SQL statements, this table can be created by the CREATE statement below: Example: ex17-07.sql - Creating A Table Called people 1: mysql> CREATE TABLE people ( 2: -> id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, 3: -> name VARCHAR(30), 4: -> birth DATE, 5: -> sex CHAR(1), 6: -> location VARCHAR(30), 7: -> salary INTEGER, 8: -> PRIMARY KEY (id) 9: -> ); 10: Query OK, 0 rows affected (0.00 sec) 11: 12: mysql> 13: mysql> DESCRIBE people; 14: +----------+------------------+------+-----+---------+----------------+ 15: | Field | Type | Null | Key | Default | Extra | 16: +----------+------------------+------+-----+---------+----------------+ 17: | id | int(10) unsigned | | PRI | NULL | auto_increment | 18: | name | varchar(30) | YES | | NULL | | 19: | birth | date | YES | | NULL | | 20: | sex | char(1) | YES | | NULL | | 21: | location | varchar(30) | YES | | NULL | | 22: | salary | int(11) | YES | | NULL | | 23: +----------+------------------+------+-----+---------+----------------+ 24: 6 rows in set (0.01 sec) This example shows the clientserver dialog of a typical MySQL session. Lines 19 are the CREATE statement input by you to create the table people. Note that you may need to issue the SQL command "USE personnel" to get into the database first. Line 10 is the message returned by MySQL. To see the existence and internal data type of people, DESCRIBE people; can be used. Before you can input data into this table, you need to construct the data first. One simple way to construct table data in MySQL is to use a text file. You can use your favorite text editor to type the data field. The following is a simple example: Listing: 17-01.txt - Text Data File (people.dat) NULL Michael 19501218 M London 30000 NULL Mary 19800622 F Paris 23000 ... ... ... ... ... ... NULL Anne 19761102 F London 28000 The format of the text file is that each record should occupy one line. Fields are separated by a tab and the id field is NULL so that MySQL can insert the index automatically. When the data are saved into a text file called people, you can use the statement LOAD DATA INFILE to read them into the table. Consider the following example: Example: ex17-08.sql - Loading A File Into Database Table 1: mysql> LOAD DATA INFILE 'people.dat' 2: -> INTO TABLE people 3: -> LINES TERMINATED BY '\n'; 4: Query OK, 10 rows affected (0.00 sec) 5: Records: 10 Deleted: 0 Skipped: 0 Warnings: 10 6: 7: mysql> SELECT * FROM people; 8: +----+---------+------------+------+----------+--------+ 9: | id | name | birth | sex | location | salary | 10: +----+---------+------------+------+----------+--------+ 11: | 1 | Michael | 19501218 | M | London | 30000 | 12: | 2 | Mary | 19800622 | F | Paris | 23000 | 13: | 3 | Peter | 19751011 | M | New York | 28000 | 14: | 4 | Tom | 19590224 | M | Paris | 28000 | 15: | 5 | Richard | 19690308 | M | New York | 23000 | 16: | 6 | Ben | 19790914 | M | London | 31000 | 17: | 7 | Sue | 19690119 | F | Paris | 30000 | 18: | 8 | Rose | 19760818 | F | Tokyo | 22000 | 19: | 9 | Jenny | 19721027 | F | New York | 27000 | 20: | 10 | Anne | 19761102 | F | London | 28000 | 21: +----+---------+------------+------+----------+--------+ 22: 10 rows in set (0.01 sec) The LOAD DATA INFILE in line 1 can load any text file into a table. Since records in the text file are separated by a new line "\n," you need an additional instruction, LINES TERMINATED BY '\n', to read the file properly. Again, this example shows the clientserver interaction in MySQL. The MySQL client program mysql basically can do everything related to your databases. However, mysql is a console (text mode) program and not all machines have it. Can we access MySQL databases using a Web browser over the Internet? There are a number of ways to put an SQL database onto the Internet and accessible by a Web browser. One popular way is to use ODBC described below. |
| Table of Contents | Previous Next |
