Inexpensive Web Hosting, MySQL, Java, JSP, Php4, Php5 Programming, Servlet Blog

268 Part II . PHP and MySQL Table 14-1 (continued) Privilege Global Database Table Column SHOW DATABASES . SHUTDOWN . SUPER . . UPDATE . . . . USAGE . . GRANT OPTION . . . Obviously, there s no point in trying to give anyone the SHUTDOWN privilege at the table level. You will merely get an error message telling you to RTFM. If you grant ALL to a column, table, or database, the user will get only the basket of privileges appropriate to that level. You should be extra-careful about giving users the following privileges, which are all dangerous: GRANT, ALTER, CREATE, DROP, FILE, SHUTDOWN, PROCESS. No normal database user, especially a PHP user, should need these permissions in production. The syntax for revoking privileges is very similar, although simpler: REVOKE priv_type [(column1, column2, column3)] ON database[.table] FROM user@host; After you grant or revoke privileges to any user, you need to force the database to reload the new privilege data into memory. You do this by issuing the FLUSH PRIVILEGES command. You could also start and stop the server, but that s impractical in many circumstances. This is all well and good, but by now you re probably thinking: But what actual permissions should I actually grant to my actual PHP user? Let s look at some common cases from the real world. Local development For purely local stuff, especially on a machine that isn t connected to the Internet all the time or is tucked securely behind a good firewall, almost anything goes. If you need to experiment with your schema, this is the place to do it so it s appropriate to have permissions like ALTER, CREATE, DELETE, and DROP in addition to the normal SELECT, INSERT, UPDATE. A lot of people will find it convenient to just grant ALL PRIVILEGES on a certain database to a local user, like this: GRANT ALL PRIVILEGES on database.* TO username@localhost IDENTIFIED BY password ; Standalone Web site A self-hosted database probably needs to accept connections from numerous Web servers in the same domain. In production, all machines should be limited to SELECT, INSERT, UPDATE, and possibly DELETE although many systems never actually delete data, and it s a little safer not to do so. Since there probably won t be multiple databases on a standalone Web
Searching for affordable and proven webhost to host and run your servlet applications? Go to Linux Web Hosting services and you will find it.

Chapter 14 . MySQL Database Administration 267 There is no way to grant a user the ability to create or drop any table of a database without also giving that user the ability to drop the database entirely. However, you can prevent the user from creating or dropping other databases on the same server. You also cannot use the MySQL grant tables to block connections from certain IP addresses or hostnames. There are two different ways to add or edit user permissions in MySQL (assuming you re the root database user): by direct SQL statements (for example, putting a Y by hand into every relevant field of every relevant grant table) or by use of the GRANT and REVOKE syntax. The latter is easier, and less dangerous if you make a small mistake, since in most cases your query will choke with a SQL error instead of just leaving a gaping security hole. To add a new MySQL user: GRANT priv_type [(column1, column2, column3)] ON database.[table] TO user@host IDENTIFIED BY new_password ; where columns and tables are optional and additional priv_types can be appended in a comma-separated list. The types of privileges and their scope are shown in Table 14-1. Table 14-1: MySQL Privilege Scope Privilege Global Database Table Column ALL . . ALTER . . . CREATE . . . CREATE TEMPORARY TABLE . . . DELETE . . . DROP . . . EXECUTE . . FILE . . INDEX . . . INSERT . . . . LOCK TABLES . . PROCESS . . REFERENCES . . RELOAD . . REPLICATION CLIENT . REPLICATION SLAVE . SELECT . . . . Continued Caution
We would like to recommend you tested and proved virtual web hosting services, which you will surely find to be of great quality.

266 Part II . PHP and MySQL MySQL allows you to grant quite fine-grained permissions to different users from different client locations. There are four descending levels of privileges: global, database, table, and column. So in theory, you could allow a particular user to write data only to certain columns of certain tables of certain databases on your MySQL server. Or you could just as easily give any database user connecting from anywhere the same powers as the root database user (although this is totally not recommended). Of course, for security reasons it s generally a good rule of thumb to grant each user only the minimal permissions necessary to perform his or her function. But here s the tradeoff: the more fine-grained your permissions scheme, the slower each and every INSERT, SELECT, UPDATE, and DELETE will be. This makes sense, because MySQL is checking more grant tables for more fine-grained permissions. Realistically, not everyone really needs to worry about the performance hit but if you do, you ll have to make some tradeoffs between security and performance. The heart of the MySQL permission system is a table that every database administrator should become very familiar with: the user table of the mysql database (which, along with a database called test, ships with every installation of MySQL). Let s look at a simplified version of this table (apologies for the line-wraps, but that s how you ll see it in many shell windows too). mysql> select * from user; +———–+——+———-+————-+————-+ ————-+————-+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | +———–+——+———-+————-+————-+ ————-+————-+ | localhost | root | | Y | Y | Y | Y | Y | Y | | dhcppc2 | root | | Y | Y | Y | Y | Y | Y | | localhost | | | N | N | N | N | N | N | | dhcppc2 | | | N | N | N | N | N | N | +———–+——+———-+————-+————-+ ————-+————-+ 4 rows in set (0.00 sec) As you can see, there are several specific global permissions, which are represented in the table by a Y or an N. A Y in the user table stands for a global privilege affecting all tables in all databases on this MySQL server. If the MySQL server gets a request from a user who has an N in the field corresponding to that action, it will start going down the hierarchy of privilege scope first to the db table for database-level privileges; then if it finds all Ns in that table too, to the tables_priv table for table-level privileges; and finally to the columns_priv table for column-level privileges. Only after exhaustively checking all the grant tables will it report an authentication error to the client. If you grant column or table level privileges to even a single user among many, MySQL will check these grant tables for all users. Therefore, giving column or table privileges to even one user could significantly slow down all your SQL statements for all users. Caution
Looking for affordable and reliable webhost to host and run your business application? Then look no more and go to servlet web hosting services.

Chapter 14 . MySQL Database Administration 265 Basic MySQL client commands It may surprise you to know that the binary named mysql in your mysql/bin directory is not the server, but the client (the server is mysqld). When you type mysql into a shell, you are using the MySQL command-line client to access some MySQL server. To connect to the MySQL server using the command-line client, the basic command is: mysql [-h hostname] [-P portnumber] -u username -p You almost certainly need to pass the username; if you don t, the client will try the name of your shell user. If you don t pass the password flag, mysql will check whether a password is needed for the user you claim to be and if so, it will reject you. If you re connecting to a local host, you don t need the hostname flag; if you re connecting to the default port (3306), you don t need the port number flag. There are a bunch of other options, but usually this is all you need the first time. Assuming you use the username root, you will be prompted for the root password that you just set in the previous step. At this point, you will need to select a database to use. The command for that is: USE databasename; The semicolon is optional for this command, but you need one for every other SQL command so you might as well get used to using it. Until you create new databases, there are only two databases in a fresh install: mysql and test. If you just connected to MySQL as the root user, you have access to both; if you are connected as any other user, you have access only to test. The command SHOW TABLES; will dump a list of all the tables in this database. To quickly see the structure of a database table, use SHOW COLUMNS FROM tablename;. This displays all the columns with their types, sizes, default values, and other helpful information. To see all the values in a table, just do a SELECT with unrestrictive conditions: SELECT * FROM tablename; Be careful though, since in live databases this kind of query can be huge and take up a lot of resources. If you have reason to suspect that the data set is more than a few rows, you should take steps to limit the query. See Chapter 13 for more information on how to write SQL statements like SELECT, INSERT, and so forth. Remember that one of the best ways of debugging problems with SQL statements in your PHP code is to try them out (with suitable fake data plugged into the variables) using the MySQL command-line client rather than the PHP client. See Chapter 19 for more information on debugging SQL in your PHP. Finally, to get out of the MySQL client session, use the command quit;. Again, the semicolon is optional for this command. This should drop you back into your normal shell. MySQL User Administration A big part of using MySQL safely and effectively is understanding its privilege system, and learning how to use the tools provided for controlling user privileges. Cross- Reference
Looking for affordable and reliable webhost to host and run your business application? Then look no more and go to servlet web hosting services.

264 Part II . PHP and MySQL cd mysql scripts/mysql_install_db chown -R root . chown -R mysql data chgrp -R mysql . bin/mysqld_safe –user=mysql & Users of MySQL 3.x should note that the new startup script for MySQL is now called mysqld_safe rather than safe_mysqld. However, the latter will still exist as a symbolic link during some transition period for backward compatibility. Now you are ready to build PHP with the MySQL client libraries. Use the –with-mysql=/ path/to/mysql flag for older versions of MySQL or the –without-mysql –with-mysqli=/ path/to/mysql_config flags for 4.1+ versions of MySQL. Note that in MySQL 4, you should link to the actual location of mysql_config rather than just to the MySQL directory. The mysql_config script is a tool that helps provide information about compiling MySQL clients, such as library location. Installing MySQL on Mac OS X MySQL AB now maintains an OS X specific binary installer distribution that delivers a disk image rather than a tarball. Simply download the .dmg file, and double-click the resulting icon. The installer will walk you through the process, and suggest a default installation path. Mac Internet Explorer users may find that the MySQL file downloads under the name download.php rather than as mysql-standard-4.x.x.dmg. In this case, simply allow the download to complete and then change the name of the file. Post-installation housekeeping MySQL ships with a blank password for the root MySQL user. As soon as you have successfully installed the database and clients preferably even before you build PHP with MySQL support you need to set a root password: mysqladmin -u root password new_password ; Obviously, you will replace the preceding word new_password with an actual password. Under no circumstances whatsoever should you even think about using your server machine s root user s password as the root password here! The server root user and the database root user have no relationship to each other. Also, don t use your normal user password as the database root password. Come on, don t be lame make up a fresh password. Unix users will also want to put your MySQL directory in your PATH, so you won t have to keep typing out the full path every time you want to use the command-line client. For bash, it would be something like: export PATH=$PATH:/usr/local/mysql Adjust this to suit your own shell. If you add an entry for this location to the PATH line in your shell s startup file (for example, .bashrc), you won t have to do this step every time you log in to the machine. Your MySQL server is now ready to use. Caution
Searching for affordable and proven webhost to host and run your servlet applications? Go to Linux Web Hosting services and you will find it.

Chapter 14 . MySQL Database Administration 263 Test the server by firing it up from the command prompt the first time. Go to the location of the mysqld server, which is probably C:\mysql\bin, and type: mysqld –console If all went well, you will see some messages about startup and InnoDB. If not, you may have a permissions issue. Make sure that the directory that holds your data is accessible to whatever user (probably mysql) the database processes run under. However, despite the nifty new install, MySQL AB has not gone all the way with the Windows UI paradigm. The preferred way to run the MySQL server, client, and tools is still from the command prompt. MySQL will not add itself to the start menu, and there is no particularly nice GUI way to stop the server either. Therefore, if you tend to start the server by doubleclicking the mysqld executable, you should remember to halt the process by hand (using mysqladmin, Task List, Task Manager, or other Windows-specific means) before you shut down the computer. Another rather odd way in which Windows users have it much harder than Unix users is that the MySQL manual currently comes distributed in one huge HTML or text file for Windows users, both in the Windows build and for download as a zip file. This file is so big that you may find it unusable if your Windows machine is not new and fast. If possible, grab the tarball version with one HTML file per chapter. You can extract it on a Unix machine and then copy the files over to your Windows box. Or you can always use the online documentation if you have reliable Internet access. The Windows version of PHP comes with MySQL enabled by default, so you should now be good to go (modulo user management stuff, which we will describe in a later section). If you wish to turn off the mysql extension in favor of the mysqli extension, you need to comment out the mysql line and uncomment the mysqli line in the modules section of php.ini. Installing MySQL on Unix If possible, use one of the binary versions of MySQL, preferably one with an installer. On some platforms (notably Linux), you will need to download the server and clients separately; on others, they are conveniently bundled. There is now a good selection of binaries, so it will not be necessary for most people to build MySQL by hand. Some packages are distributed by third parties, such as Debian, rather than by MySQL AB. Look around your usual source for binary packages specifically built for your platform if you don t see a binary build on mysql.com. There can be very wide variation in where MySQL programs and data files are located, based on precisely which package you re using and where you got it. The mysql.com manual contains a section on installation layouts, but it s often inapplicable or inaccurate. The most common locations are /usr, /usr/local, and /var. If you have to use a generic binary instead of a cushy installer-based version, installation will require a few extra steps. Type the following lines at the prompt to create a new mysql user and install MySQL to run as that user (you ll have to be the root user): groupadd mysql useradd -g mysql mysql cd /usr/local gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf - ln -s full-path-to-mysql-VERSION-OS mysql
If you are in need for cheap and reliable webhost to host your website, we recommend http web server services.

262 Part II . PHP and MySQL Transaction-safe tables For most of its existence, MySQL used tables of a proprietary type called MyISAM. Late in the 3.xx release cycle, it introduced two new types, ISAM and heap, but they have not become hugely popular. To this day, MyISAM is the default and by far the most common type. However, to support transactions in MySQL 4.1+ the MySQL team created two new types of transaction-safe tables: InnoDB and BDB. If you want to use commits and rollbacks, you must compile MySQL with the ability to recognize one of these types and define each table as InnoDB. You can mix different table types in the same database, and also convert a MyISAM table to an InnoDB table. You might also consider mixing types by using InnoDB tables on a master database that accepts writes, while sticking with MyISAM for slave databases that provide only reads. Think hard about whether you really need transaction-safe tables. They impose quite a bit of extra overhead and are thus slower, take up more room on disk, and require different tools and procedures. Some things, such as recovering from database corruption, are considerably different and possibly harder (although also potentially less common) if you re using transaction-safe tables. On the other hand, if you wish to use MySQL in enterprise situations with transactions, row-level locking, foreign keys, and hot backup, you ll want to research the InnoDB alternative. The other type of transaction-safe table, BDB, is based on Sleepycat Software s BerkeleyDB storage engine. BDB does not offer some of the other features of InnoDB, such as foreign keys and row-level locking, and it s a bit unclear which company will provide support for this setup. Because transaction-safe tables are still so uncommon, and presumably used mostly in situations where resources are available for specialized database administrators and tools, the bulk of this chapter will concentrate on MyISAM tables. For more information on InnoDB tables, refer to www.innodb.com or www.mysql.com/doc/en/InnoDB.html. The Windows binary version of the MySQL server is built with InnoDB enabled by default. However, your tables will not actually be of the InnoDB type unless you define them to be. Downloading MySQL All downloads for MySQL are located at www.mysql.com/downloads/index.html. Pick the version number you want and, as exactly as possible, the platform you want. One peculiarity of MySQL is that, unlike most other Open Source servers, the producers prefer installation from binary rather than source. There may be situations where you have to build yourself, but in general it should be avoided if at all possible. MySQL is now sometimes distributed in Linux distros or as part of other packages; for the freshest builds, however, it s better to uninstall these versions using whatever tools are provided by your platform and then reinstall a new version. Installing MySQL on Windows Default installation on any version of Windows is now much easier than it used to be, as MySQL now comes neatly packaged with an installer. Simply download the installer package, unzip it anywhere, and run setup.exe. This will walk you through the trivial process and by default will install everything under C:\mysql, which is probably as good a place as any. Tip
From our experience, we are can tell you that you can find a reliable and cheap webhost service at Java Web Hosting services.

Chapter 14 . MySQL Database Administration 261 Transactionality basically means the ability to treat a group of database operations as a single unit for the purposes of accepting or rejecting the data. So for instance, an e-commerce transaction might have several steps touching numerous different tables registering you as a new user, collecting your payment and shipping information, debiting the product from inventory, and so forth but you don t want any of these changes to be made unless the credit card charge goes through successfully, even though that step comes at the end of the purchasing process. In this case, you need a transactional database that will keep track of changes throughout the process and either commit them all as a unit or roll them all back as a unit. Until version 4, MySQL was not a transactional database, but now it supports transactionality. New client libraries The client libraries for MySQL 3 and 4.0 are forwards-incompatible with the MySQL server from version 4.1 and up. Therefore, if you want to use MySQL 4.1+, you will have to rebuild PHP with the new libraries. The reverse is not true: MySQL 4.1 client libraries can still be used with older versions of MySQL server. You may also have to update your permissions table and any columns containing password hashes calculated by MySQL that you have created in any tables. The main difference between the client libraries has to do with authentication. The MySQL PASSWORD() function used to result in a 16-byte hash. From version 4.1.1 onward, it now results in a 41-byte hash. Since MySQL uses the PASSWORD() function to set its own user permissions schemes, you will need to update pre-4.1.1 MySQL grant tables using the mysql_fix_privilege_tables script. You will also potentially need to alter by hand any columns in other tables that take input from MySQL s PASSWORD() function, making them 41-bytes long. The actual contents of these columns do not need to change MySQL 4.1 will continue to accept hashes shorter than 41 bytes but the column sizes need to be increased to accommodate new values. mysqli The i in mysqli stands for improved. The new mysqli extension to PHP was designed to let you access the new functionality of MySQL 4.1 and above especially transactionality, which is the biggest new feature of MySQL 4. The mysql extension works only with versions of MySQL below 4.1; the mysqli extension works only with versions 4.1 and above of MySQL. Unfortunately, this extension is not easily compatible with the old mysql extension and its associated functions. Therefore, it s best to choose one or the other at compile time. At the time of this writing, the mysqli extension is considered experimental and should probably not be used in production. It s theoretically possible to compile PHP with both mysql and mysqli extensions if for instance you want to use both a 3.x and a 4.1+ version of MySQL on the same machine but you ll have to be very careful to avoid conflicts between client libraries. In practice, it s better to simply choose one or the other. Comp Svcs: Note two successive hyphens in the following paragraph. Do not change to an em dash. If you choose to try mysqli, remember to disable the mysql extension, which is usually enabled by default. (In Unix builds, use the without-mysql flag; in Windows, comment out the mysql.dll extension in php.ini.) Caution Caution Note
If you are looking for affordable and reliable webhost to host and run your business application visit our ftp web hosting services.

260 Part II . PHP and MySQL less restrictive licensing scheme than before, but it happened to be incompatible with PHP s Apache-style license, and therefore the PHP Group no longer felt able to distribute the database libraries. At press time this issue was in the process of being amicably worked out to allow Open Source combined works to be distributed without charge but you should still check to be absolutely positive that this is the case. The definitive location for MySQL licensing information is www.mysql.com/doc/en/Licensing_and_Support.html. There are separate sections for commercial use and for use under the GPL which is probably the use most relevant to most PHP developers. You should read the license carefully, but here are some common use cases that will be affected by MySQL s new licensing scheme: . Web site only: If you use MySQL solely as part of a commercial or noncommercial Web site, you may use it without worrying about licensing issues. MySQL AB suggests you purchase a support contract to further development work. . Open Source project under GPL: If you distribute MySQL server or client libraries as part of an Open Source project properly released under the GNU General Public License, you can (and must) redistribute MySQL and its source code freely. . Commercial redistribution: If you bundle MySQL server or client libraries as part of a commercial product, you must purchase a commercial license from MySQL AB. . Open Source project under non-GPL license: This is the potentially problematic situation at the moment, because if you bundle MySQL server or client libraries with your non-GPLed Open Source project, your code could be infected by the GPL. Check the MySQL licensing page to determine the most current status of MySQL in relation to your project. Remember that in many cases you can evade these licensing strictures simply by not redistributing the database yourself but rather requiring your users to procure and install MySQL separately. Installing MySQL: Moving to Version 4 Remember to install the MySQL server and client libraries before installing PHP! Although it s not strictly necessary in every circumstance, especially on Windows, it s always a good habit with PHP to make sure that all third-party servers and libraries are properly installed before telling PHP to link to them. Preinstall considerations MySQL was in version 3 for a long time, and many PHP developers got used to working with it during this period. However, MySQL 4 has introduced some innovations and changes, both on the database side and the PHP side. Both new and experienced webdevs should take the time to familiarize themselves with these changes. Even if you have a lot of experience with MySQL 3, you shouldn t necessarily expect to be able to install and use MySQL 4 with exactly the same procedures. There are three main MySQL-specific issues to consider before you install new versions of MySQL and PHP: incompatible new client libraries, the new PHP mysqli extension, and new table types that support transactionality.
Searching for affordable and reliable webhost to host and run your web applications? Go to our java web server services and you will be pleased.

MySQL Database Administration MySQL is one of the easiest databases to administer on all platforms; and because it s so lightweight, it can run on even lowpowered PCs. Thus, PHP developers have long found it convenient to throw a copy of MySQL on client machines even on laptops for a complete local Web development environment. Many developers learn to run their own MySQL installations so they can work at home or on the road, using the OS of their choice. Work teams also sometimes prefer developers to each use a separate local MySQL installation, so that there is no single point of failure that could affect an entire development group. And many PHP-based Open Source projects assume complete familiarity with MySQL database administration for all developers. Unlike some other databases, it should be well within the capability of any PHP developer to self-administer a MySQL database. There are a plethora of tools, both in MySQL itself and available from third parties, to make this job even easier. Many PHP-based application packages, both commercial and Open Source, also require familiarity with a MySQL database to install, run, and debug the Web app. So even if you don t plan to write all your own PHP code yourself, getting comfortable with MySQL administration will pay many dividends. MySQL Licensing Before installing any piece of Open Source software, you should clearly understand all the associated licensing issues. This is especially true of products like MySQL that have dual commercial and Open Source licenses. Unfortunately, MySQL licensing at the time of this writing is in flux and has caused momentary incompatibilities with PHP s license. Until this situation gets definitively ironed out, PHP developers need to be extra careful to ensure that they are in compliance. This goes double for anyone who distributes (rather than simply uses) MySQL in either a commercial or open source context. For some of the later releases of PHP4, MySQL client libraries were bundled with PHP. In the summer of 2003, MySQL AB creators of the MySQL database decided to adopt the General Public License (GPL) for noncommercial use. In many ways, this is a simpler and 1C H A4P4T E R . . . . In This Chapter MySQL licensing Installing MySQL Administering MySQL PHPMyAdmin Backups Replication Recovery . . . .
From our experience, we are can tell you that you can find a reliable and cheap webhost service at Java Web Hosting services.