• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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:

    • DIR(‘/usr/local’) sets the installation location for the MySQL files. The directory will be created if it does not already exist.
    • DATADIR(‘/QOpenSys/mysal/data’) sets the location of the directory that will be used to store the database files and binary logs. The selected is the same as the default: /QOpenSys/MySQL/data.
    • USRPRF(MYSQL) sets the user profile that will own the files that are installed. The profile will be created if it does not already exist. It is created as disabled and cannot be used as a sign on profile.

    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?



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Midrange Dynamics North America

    Git up to speed with MDChange!

    Git can be lightning-fast when dealing with just a few hundred items in a repository. But when dealing with tens of thousands of items, transaction wait times can take minutes.

    MDChange offers an elegant solution that enables you to work efficiently any size Git repository while making your Git experience seamless and highly responsive.

    Learn more.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    COMMON:  Join us at the annual 2008 conference, March 30 - April 3, in Nashville, Tennessee
    WMCPA:  23rd Annual Spring Technical Conference, April 9 & 10, 2008, Lake Geneva, WI
    Seagull Software:  Update your System i apps with LegaSuite GUI

    IT Jungle Store Top Book Picks

    Getting Started with PHP for i5/OS: List Price, $59.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket Developers' Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    iSeries Express Web Implementer's Guide: List Price, $59.00
    Getting Started with WebSphere Development Studio for iSeries: List Price, $79.95
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    WebFacing Application Design and Development Guide: List Price, $55.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    The All-Everything Machine: List Price, $29.95
    Chip Wars: List Price, $29.95

    Group 1 Updates Data Integration Tool Welcome to Legacy Status, Windows Server

    Leave a Reply Cancel reply

Volume 8, Number 7 -- February 20, 2008
THIS ISSUE SPONSORED BY:

Help/Systems
WorksRight Software
Guild Companies

Table of Contents

  • Getting MySQL Working With PHP
  • LPEX Edit in Hex Mode
  • Configuring Messaging Software for Overnight Monitoring

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • FAX/400 And CICS For i Are Dead. What Will IBM Kill Next?
  • Fresche Overhauls X-Analysis With Web UI, AI Smarts
  • Is It Time To Add The Rust Programming Language To IBM i?
  • Is IBM Going To Raise Prices On Power10 Expert Care?
  • IBM i PTF Guide, Volume 27, Number 20
  • POWERUp 2025 –Your Source For IBM i 7.6 Information
  • Maxava Consulting Services Does More Than HA/DR Project Management – A Lot More
  • Guru: Creating An SQL Stored Procedure That Returns A Result Set
  • As I See It: At Any Cost
  • IBM i PTF Guide, Volume 27, Number 19

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle