Getting MySQL Working With PHP
February 20, 2008 Brian Kelly
|
This is the third in a series of four articles by Brian Kelley on PHP. MySQL is a common open source SQL database management system. It is developed and distributed by MySQL AB, a company that builds its business by providing services around MySQL. This relational database was first developed for Unix and Linux applications. It became popular when Internet Service Providers found out that MySQL was a relational database that could be offered free of charge to their Internet customers. The MySQL “product” could provide all the storage and retrieval functionality that a dynamic Web application needs. MySQL is a multi-threaded (can execute multiple commands at the same time) and multi-user (more than one user at a time can run commands) database management system. There are literally millions of installations worldwide. Some of the more famous companies that use MySQL include: Amazon.com, Google AdWords, and NASA, among many others. So, if you learn MySQL on i5 or any other system, you will have upped your career capabilities, big-time. The reason that MySQL has entered the domain of the i5 is because it is the preferred database for PHP. The many PHP applications that exist are in fact dependent on MySQL as a database. MySQL is the most popular database to use with PHP applications. A recent tech resources survey cited that the more than 6,800 MySQL developers questioned said PHP is the language they used 60 percent of the time. The thousands of PHP applications written to MySQL include customer relationship management systems, portals, content management systems, wikis, blogs, surveys, and forums. You now have the ability to download these and run them on your System i. Just go out to sites like: HotScripts, PHPJunkyard, and PHP Freaks. There is also custom development that occurs around MySQL: LAMP stack and XAMPP stack, for instance, are literally used by millions of trained developers. Once you have Apache, MySQL, and PHP in your toolbelt, a whole new world of application opportunity is opened up to you. MySQL has had some problems recently after the company offering MySQL, its former storage engine InnoDB, was purchased by Oracle. You can read more information about this acquisition here. The trick is MySQL can and does use many storage engines. So, it is either an incomplete DB or it is a flexible DB. Personally, I think it is the latter. The MySQL personality is what permeates the Internet and it is what makes MySQL the companion of choice to PHP. It does have its own default storage engine, which is installed with the System i package, called “MyISAM.” It is supported in all MySQL configurations. It is the default storage engine for 2007/2008 unless you have configured MySQL to use a different one. IBM is working feverishly to make the DB2 structure function as the storage engine for MySQL. Obviously this will give IBM big benefits and it will give MySQL a more robust database to handle its storage tasks for enterprise systems. Finding, Downloading, and Installing MySQL with i5/OS Start your efforts by creating the user profile MySQL by issuing the following command in i5/OS:
CRTUSRPRF USRPRF(MYSQL) STATUS(*DISABLED) LCLPWDMGT(*NO)
TEXT('MySQL user id')
Note that by executing this command the resultant MySQL profile is a disabled ID, and thus, it cannot be used to sign onto the System i, but it can be used by MySQL. To download MySQL, go to this site and grab the i5/OS (POWER, 64-bit) version of MySQL 5.0 for your workstation. You will see a page that shows the download information and your download site:
Select the i5/OS (POWER, 64-bit) download from the page as shown above by clicking on the word “Download.” Save the downloaded file in a folder for use in installing and leave it there after the process in case you need it again.
As you can see on my PC, I placed the file in my Downloads Folder. In a subfolder that I named “MYSQL,” I created a “checkitout” folder and put the distribution in there. This file is already in Save File format so it is ready now for its trip to the System i. At the time of this writing, “MySQL-max-5.0.45b-i5os-power-64bit.savf” was the name of the file for 64-bit Power processors such as those for System i. When it is actually created on the System i from the Save file, however, the “b” disappears. Removing MySQL From a System Before a Reinstall In case you make a big mistake and the installation test programs or the PERL scripts will not run and you just wish you could start all over, you can. You can start from scratch by performing the following Remove commands. The RMDIR command removes a Unix directory. If the subdirectory, say, MYDIR exists in your current directory and it is not empty, you can delete it by entering the following command at the Unix prompt: rm -r mydir Don’t do this to delete MySQL from the system since it is not stored in MYDIR. Instead, use the following Unix commands: cd /usr/local rm -r MySQL rm -r MySQL-5.0.45-i5os-power-64bit After setting the directory, the first “rm” command removes the symbolic link to the MySQL code. The second command assumes that your MySQL package is at the same level as shown. If not, replace the “MySQL-5.0.45-i5os-power-64bit” file with the name of the version that you have installed. After this, most of the MySQL code is gone from your system, but you are not finished yet. From a PASE command line, type the following: cd /qopensys rm -r MySQL These two commands switch to the System i Unix directory (QOpenSys) and then remove the data and set up tables for MySQL from QOpenSys. Be careful if you have created your own tables or modified objects here. If you have, save them someplace first before executing these commands or use an upgrade MySQL method instead. Now, there is one more thing. Start by changing to the “root /etc” directory: cd /etc List the files in the “/etc” directory, hit Enter, and then delete (remove in Unix) the “my.cnf” file as follows: ls In this command, “ls” is the Unix equivalent to the DOS DIR. Hit Enter and you will see a display similar to this: entropy magic php.ini text ibm my.cnf prngd-seed vfs $ While in the “/etc” directory, remove the “my.conf” file, execute the following from PASE (QP2Term): rm my.cnf If you want to see what is in the file, you can go back to i5/OS and perform a WRKLNK “/etc” (etc directory in the root) and place a “5” next to my.cnf. From PASE, you can also run the good old “ed” single line editor to display the file by typing in: ed my.cnf Hit Enter. The PASE terminal looks real dumb and offers no clues. Type a “1” and hit Enter again. Then type “2” and hit Enter. Type “3” and hit Enter. Type “4” and hit Enter. This primitive editor will display the four lines of my.cnf for you and it will look like the following: 1 # Created at installation. 2 [MySQLd] 3 datadir = /QOpenSys/MySQL/data 4 user = MYSQL This little “my.cnf” file is very important to MySQL and if it exists when you are installing, MySQL will not fully re-install. If you already have data in MySQL, this file may really be what you want anyway. The installation package default is to use your existing configuration if you have previously installed MySQL. If you do not delete or rename this file, the new installation parameters will be ignored, and the values will be determined from the “/etc/my.cnf” file. If you are actually upgrading, and not retrying the same installation because of errors, instead of performing the delete as shown, follow the then current upgrade instructions such as those available at the MySQL Developer Zone. Uploading to the System i The most straightforward method of getting your MySQL Save file from your PC to the System i is to FTP it directly. Start this process by creating a Save file in QGPL. Call it MYSQLSAV:
CRTSAVF FILE(QGPL/MYSQLSAV)
TEXT('Save file for MYSQL upload to IFS')
The FTP instructions to get the file to QGPL are as follows: ftp> bin ftp> cd qgpl ftp> put /downloads/MySQL/checkit out/ MySQL-5.0.45b-i5os-power-64bit MYSQLSAV Since Microsoft‘s default FTP no longer supports passive mode, and i5/OS now uses the more secure passive mode, my cohorts and I have all struggled with Windows FTP uploads. So, I just don’t do them anymore. Instead I send the file to the IFS and from there I run the i5/OS FTP client and connect to the i5/OS FTP server. Since the client and server are separate pieces of code on System i, your system does not care that the from-file and to-file (or “get” and “put” files) are both on the same System i. The two methods I am about to describe also help me get around some firewalls that universally block off FTP as a security measure. There are two ways to get the file to the IFS. One is to map a network drive and the other is to connect with iSeries Navigator. With both of these options, you copy and paste to get your file to the IFS. First you must create a directory or use the TMP directory in the IFS. I like to create a new directory. From an OS/400 command line type in the following and press Enter: MD '/MySQLupload' Uploading with iSeries Navigator Using iSeries Navigator, take the following path once you are connected to your System i: >>File Systems ??Integrated File System >>Root >>MYSQLupload Once you have iSeries Nav positioned to MYSQLupload, click the following on your PC: c:downloads MySQLcheckit out MySQL-max-5.0.45b-i5os-power-64bit.savf Go to the MYSQLupload folder icon provided by iSeries Navigator and right-click on it. Then select Paste. Depending on the speed of your connection, this may take several minutes to a half an hour since it is a 60-plus MB file. During the upload, if you are on client XP you will observe the following pop-up display:
Uploading with NetServer If you would prefer to use NetServer and the “map a drive” technique, the steps are as follows: Network TCP/IP iSeries netServer From NetServer, right-click on Shared Objects and pick New to create a new share. Call it “MySQLupload”, map it to the MySQLupload IFS Directory, and assure that it is read-and-write and that you have authority to use it. Then, right-click on the Network Neighborhood or My Network Places icon on your desktop. Select Map Network Drive. On the next panel, make note of the drive letter assigned. Use whatever drive letter you are given. Pull down the Folders menu. If you have drives already mapped then pick one of them and change the file name to “MySQLupload” and click the Finish button. If you see no mapped samples for this system type in the pull-down window then type one of the following: myi5ipaddressMySQLupload myi5domainnameMySQLupload Finally, click the Finish button. Moving the Save File to QGPL via FTP Now that the Save file is on the i5 IFS, it’s time to get it FTPed to QGPL and restore the program. This is the process: 1. Log into the System i server using a user in the *SECOFR class, such as the QSECOFR user ID. 2. Display the MySQLupload directory. The results are shown below:
Work with Object Links
Directory . . . . : /MySQLupload
Type options, press Enter.
2=Edit 3=Copy 4=Remove 5=Display 7=Rename
8=Display attributes 11=Change current directory ...
Opt Object link
8 MySQL-5.0.45b-i5os-power-64bit.savf
IBMTools
MYSQL.sav
3. Take option 8 to display the Attributes on the uploaded file so that you can see its size. It should be over 60 MB. Page down from the first Attributes panel to see the size. 4. To make the FTP easier, rename the uploaded IFS file to a simple name such as MySQL.
RNM OBJ('/MySQLupload/MySQL-5.0.45b-i5os-power-64bit') NEWOBJ(MySQL)
5. FTP the file to your System i via its IP address or its host.domainname.
FTP RMTSYS('192.168.10.254')
The log of the FTP session is shown below. Any place where you see the greater than sign (>), input is required. The triple asterisk statements are comments that I have made to help explain what you do and what is happening. Previous FTP subcommands and messages: Connecting to remote host 192.168.10.254 using port 21. 220-QTCP at s103y42m.lccsaves.com. 220 Connection will close if idle more than 5 minutes. Enter login ID (smiley): > smiley *** sign on with a security officer capable user ID 331 Enter password. xxxxxxxx 230 SMILEY logged on. OS/400 is the remote operating system. The TCP/IP version is "V5R4M0". 250 Now using naming format "0". *** Name format 0 is the library/file naming convention *** Name format 1 is the IFS naming convention *** For FTP to the same system, both the client and server must use same naming 257 "QGPL" is current library. > namefmt 1 *** Change Name format to 1 = IFS style 250 Now using naming format "1". Server NAMEFMT is 1. Client NAMEFMT is 1. > binary 200 Representation type is binary IMAGE *** Must use binary command so no code conversion occurs on save file. > get /MySQLupload/MySQL /QSYS.lib/qgpl.lib/MYSQLSAV.file (r *** Format of the get command is -- Get remotefile [ localfile ] [(REPLACE] *** The FTP client is local. It goes to the server (same system) to "get" the file 227 Entering Passive Mode (192,168,1,252,116,52). 150 Retrieving file /MySQLupload/MySQL 226 File transfer completed successfully. 62722176 bytes transferred in 4.149 seconds. Transfer rate 15115.911 KB/sec. 6. Restore the installation library stored in the MYSQLSAV save file in QGPL: RSTLIB MYSQLINST DEV(*SAVF) SAVF(QGPL/MYSQLSAV 7. Install the MYSQL product by executing the installation command:
MYSQLINST/INSMYSQLM DIR('/usr/local') DATADIR('/QOpenSys/MySQL/data')
USRPRF(MYSQL)
The three instillation parameters can be overridden if you choose. In our scenario, we opted for the following defaults:
The database facility MySQL can be installed anywhere on your System i’s IFS. As you can see for this installation, the MySQL package has been installed into “/usr/local”. The MySQL user profile that you created at the beginning of this sequence should be used for the profile. When the install program works on the data from the MYSQLSAV save file it creates an installation directory in “/usr/local” in the IFS. For the directory name, it drops the “b” next to “45” from the download name and calls it “MySQL-5.0.45-i5os-power-64bit”. So be careful that you do no confuse the download file from the Internet with the install directory on the system. When you run the installation program, it automatically switches to PASE and performs some functions for you. It then tells you about some things you should do. Some of these work fine and others can cause some issues. The message on the terminal at the end of the installation program looks like the following: PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: ./bin/MySQLadmin -u root password 'new-password' ./bin/MySQLadmin -u root -h hostname.domainname.domainsuffix password 'new-password' See the manual for more instructions. You can start the MySQL daemon with: cd . ; ./bin/MySQLd_safe & You can test the MySQL daemon with MySQL-test-run.pl cd MySQL-test ; perl MySQL-test-run.pl Please report any problems with the ./bin/MySQLbug script! The latest information about MySQL is available on the Web at http://www.MySQL.com Support MySQL by buying support/licenses at http://shop.MySQL.com Press ENTER to end terminal session. For experimentation purposes, do not create a root password. When you are ready to go live with an application is soon enough to create your password. MySQL commands require authentication and my experience is that changing the root password can create issues, especially for a novice. Now that MySQL is on the system, let’s start it up and make sure it works. How To Start MySQL 1. Stay logged on as you are at this point or if you are coming back to the system after completing the installation, then log on to your System i with a user profile within the *SECOFR class, such as the QSECOFR user ID. There are a number of commands that actually start the MySQL daemon. All of these MySQL commands begin with “MySQLd.” The “MySQLd_safe” command is the recommended way to start a MySQLd server on Unix and NetWare and thus it is also the recommended way to start MySQL on i5/OS. MySQLd_safe adds some safety features over the other versions of the start command such as restarting the server when an error occurs and logging runtime information to an error log file. If you read the installation instructions, you will see that you are asked to work the commands with a user that has the “id=0”. This makes little sense to i5/OS users. It is however, the equivalent of the standard Unix root user. The Unix fact is that the “superuser” or all powerful user in Unix and in MySQL is named simply “root”. 2. Enter the PASE environment from an i5/OS command line by typing “call qp2term” and pressing the Enter key. 3. Start the MySQL server by changing to the installation directory and running the MySQL start command, “MySQLd_safe”. Specify the user name that was used to install the server (MySQL): > cd /usr/local/MySQL-5.0.45-i5os-power-64bit > bin/MySQLd_safe --user=MySQL & You should see a message similar to the following: $ Starting MySQLd daemon with databases from /QOpenSys/MySQL/data How To Stop MySQL From a security officer profile in the PASE environment, stop the MySQL server by changing to the installation directory and running the shutdown command. Specify root as the user name: > cd /usr/local/MySQL-5.0.45-i5os-power-64bit > bin/MySQLadmin -u root shutdown You should see a message similar to the following: $ STOPPING server from pid file /QOpenSys/MySQL/data/hostname/domainname/suffix.pid 070826 16:30:28 MySQLd ended I would recommend setting up the Perl scripts that are included with MySQL to further check the installation. The instructions in the manual suggest that you download the iSeries Tools for Developers (5799-PTL). These do contain PERL but the PERL here in an i5/OS ILE PERL not a PASE/Unix PERL. There are things missing in this recommended version of PERL that will cause your test to fail. For example, it took me about a man-day of deleting and redoing the MySQL installation to figure out for example that it was PERL that was the problem and not MySQL. Click here to see a few one-liners on how to bring down and install the correct PERL on your System i. Simple Installation Verification To verify that installation was successful, issue the following command using the root profile: bin/MySQLcheck -u root MySQL You should get output similar to that shown below: > bin/MySQLcheck -u root MySQL MySQL.columns_priv OK MySQL.db OK MySQL.func OK MySQL.help_category OK MySQL.help_keyword OK MySQL.help_relation OK MySQL.help_topic OK MySQL.host OK MySQL.proc OK MySQL.procs_priv OK If you get error messages, it means that the MySQL server is probably not running and perhaps was not installed properly. For example, you might see: bin/MySQLcheck: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/MySQL.sock' (2) when trying to connect $ Using MySQL with PHP In order to utilize MySQL with PHP in Zend Core, you must enable the MySQL Extensions in the Zend Core control center. You can do this simply by un-commenting (removing the “#” character) from the following lines in “/usr/local/Zend/Core/etc/php.ini” file: extension=MySQL.so extension=MySQLi.so 1. Stop the PHP server from its console and stop the MySQL Server. 2. Go to i5/OS side and get a command line with security officer authority and type in:
wrklnk ('/usr/local/Zend/Core/etc/php.ini')
You will see this panel: Work with Object Links Directory . . . . : /usr/local/Zend/Core/etc Type options, press Enter. 2=Edit 3=Copy 4=Remove 5=Display 7=Rename 8=Display attributes 11=Change current directory ... Opt Object link 2 php.ini 3. Take option 2 to edit php.ini and press Enter. Be very careful. 4. In the Control field of the next panel, type in “extension=MySQL.so” and hit F16. You will see these two entries on top: ;extension=MySQL.so ;extension=MySQLi.so 5. Remove the semicolons on both lines and hit F2 followed by F3 to exit the editor. 6. For MYSQL to work with PHP, you need to restart PHP and MYSQL There are other Unix post-installation procedures in the MySQL 5.0 Reference Manual at this link. But, if you have made it this far, you are already up and running with MySQL on your i5/OS System i. Congratulations! Brian Kelly was an IBM Midrange SE for 30 years, and has spent nearly a decade as a System i5 consultant based in Scranton, Pennsylvania. He is also author of dozens of AS/400, iSeries, and System i5 books and he serves as an assistant professor at Marywood University, which uses the OS/400 and i5/OS platform and teaches courses in the box as well. Kelly is also one of the contributing technical editors to The Four Hundred newsletter. He can be contacted through the IT Jungle contact page. RELATED STORIES IBM Apache Servers Needed by PHP Setting Up A PHP/Web Environment On System i: Where Do I Start?
|
