Delete MySQL for a clean install, or just to get rid of it

Sometimes you may need to get a fresh start with MySQL.  If so, you will want to do a clean install, which is just deleting the existing installation and doing a new installation. Other times, you may just want to remove MySQL.  This article can help you out either way.

Before you start, you may want to verify that MySQL is in fact installed, and determine the version.  The version will affect some of the directory names to be backed up and deleted, so it is a good idea to run through this, even if you are sure it is installed:

How to Verify MySQL is Installed on your IBM i

1. Back up any MySQL data you want to retain. To learn how to back up and restore your data, please review this chapter in the MySQL manual:

Chapter 7 Backup and Recovery

2. Sign on to a 5250 session as QSECOFR.

3. Setting the CCSID to 37 during installation can help prevent some issues. From the 5250 command line:

CHGJOB CCSID(37)

4. Uninstall MySQL

• Stop the ZENDDBI subsystem:

ENDSBS ZENDDBI OPTION(*IMMED)

• Back up your existing MySQL directories. Navigator makes this an easy copy and paste. Create some back up directory. Click the directory you want to back up, and select Copy. Click the back up directory and select paste. The directories to back up are:

/usr/local/mysql-5.1.59-i5os-power-64bit
(this directory name depends on the MySQL version, so may be different)

/usr/local/mysqldata

• Delete the ZMYSQL library:

DLTLIB ZMYSQL

• Enter the PASE shell:

CALL QP2TERM

• Remove the /usr/local/mysql link

rm -f /usr/local/mysql

• Remove the /usr/local/mysql-5.1.59-i5os-power-64bit directory (the directory name depends on the MySQL version, so may be different)

rm -r -f /usr/local/mysql-5.1.59-i5os-power-64bit

(wait for the # or $ to appear, before entering the next PASE command)

• Remove the /usr/local/mysqldata directory

rm -r -f /usr/local/mysqldata

(wait for the # or $ to appear, before entering the next PASE command)

• Remove the /etc/my.cnf file

rm -f /etc/my.cnf

• If the file /tmp/mysql.sock exists, remove it:

rm -f /tmp/mysql.sock

• Exit the PASE shell by using F3.

5. Install MYSQL from the Zend Server setup menu. This article tells how:

http://rodflohr.com/install-mysql-using-the-zenddbi-installer-with-zend-server/

6. Restore your saved MySQL data from step 1.

Edit any file in Navigator

It is possible to use Navigator to look at and edit the contents of just about any file in the IFS.  By default, it is only possible to edit files with extensions that can be edited with Notepad.  Examples are files that end with .log, or .txt.  Other files that cannot by default be edited that would be nice to be able to edit are files that end in .mbr (source file members), or the Apache logs that end with an extension that is a date stamp, or even .php files. This is very easy to set up.

In Navigator, go to File Systems, and find Integrated File System directly underneath.

navedit01

Right click Integrated File System and select Properties from the context menu.

navedit02

In the dialog that appears, under “Enable edit menu option for”, click the “All files” radio button.  Click OK.

Now you can view and edit any file in the IFS.  Just right click the file and select Edit.  But you need to be careful!

The editor is similar to Notepad, if a bit quirky.  You may see some contents that look a little weird.  For example, content encoded CCSID 13488 (UTF-16) will appear to have a space between each character, which makes it tough to read.  Binary files are of course mostly unreadable.  Try not to edit any files that look strange to you.

Also, be aware that some files installed by Zend Server use Linux style End-of-line characters (lf) rather than Windows style (cr-lf).  Editing these files in Navigator (or Notepad, by the way) can make them unusable.  Better to use an editor like Zend Studio or Notepad ++ for these files.  Looking won’t hurt, though, so just don’t make any changes.

It would be very bad to edit binary files, or any configuration files that you are not familiar with.  Proceed with caution.  Never change a file unless you are sure of what you are doing. Always make a back up copy of any file before editing.

This change only affects your installed copy of Navigator, and only for the IFS within the connection.  If you are connected to more than one partition, you will need to make this change for each partition’s connection.

End jobs on the IBM i by User and Job Name – ENDUSRJOB

Sometimes we know the name and user of a job we might want to end, but we won’t know the job number until the job is submitted. There does not seem to be an IBM i command to end jobs when the job numbers are not known, and I have a use for one, so I built one.

To create the command, there were two options.  Use an API to list the jobs and end them, or use existing commands to find the jobs, convert a spool file to a flat file, and parse the flat file to examine the list and end the desired jobs.  I tend to prefer commands to API’s. so I went with the second option.

While I had a particular use in mind for this thing, I did decide to go ahead and make it a little more flexible, so it might be of use to more people.  I have to warn you that I did not give the thing a full QA, so it could have a bug or two in it.  I hope it is all OK, but if you see a bug, please add a comment to the post.  Also, if you use it, test it first, to make sure it does exactly what you expect it to do.

For your convenience, text files for the source have been posted here:

ENDUSRJOB

There is a command definition, a CL program, and an RPG ILE program.  The RPG program uses the RPG cycle, a program defined input file, a procedure prototype, and a line of free formatted code, so you may find it a bit stylistically eclectic.  On the other hand, it is so simple a child with a basic understanding of RPG could understand it, which is something we probably could not say if it was written with APIs, so there’s that.  Anyway, let’s take a look.

Here is the command, prompted:

endusrjob

endusrjob2

The parameters are mostly familiar from the WRKUSRJOB and ENDJOB commands.  The user is a user name, or * for current user.  Job Type is batch, interactive, or both.  The job name can be specified or left *ALL for all jobs that meet the other selection criteria.  I did add one new option to the ENDJOB option.  In addition to *CNTRLD and *IMMED, there is *ABN which will run ENDJOBABN instead of ENDJOB.

Hopefully the comments in the code explain well enough what each object does.

Here is the source for the ENDUSRJOB command:

/* The ENDUSRJOB command ends jobs with the selected option.  The      */       
/* jobs to end are selected based on User, Job Type, and Job Name.     */       
/* The WRKUSR job command is used to select jobs by User and Job Type. */       
/* The jobs selected by WRKUSRJOB are then matched to the given Job    */       
/* Name to make the final selection.  Selected jobs are ended using    */       
/* the ENDJOB command with the specified Option.                       */       
                                                                                
/* The ENDUSRJOB command is processed by CL program ENDUSRJOBC:        */       
/* CRTCMD CMD(ENDUSRJOB) PGM(ENDUSRJOBC)                               */       
                                                                                
/* This program is a demonstration.  Please test this program          */       
/* thoroughly before using it in any production setting.  It is        */       
/* provided as is.  Rod Flohr, 2014-09-11                              */       
                                                                                
             CMD        PROMPT('End User Jobs')                     
             PARM       KWD(USER) TYPE(*NAME) LEN(10) DFT(*) +                  
                          SPCVAL((*)) PROMPT('User whose jobs to end')          
             PARM       KWD(JOBTYPE) TYPE(*CHAR) LEN(12) RSTD(*YES) +           
                          DFT(*BATCH) VALUES(*BATCH *INTERACTIVE +              
                          *ALL) PROMPT('Job type')                              
             PARM       KWD(JOBNAME) TYPE(*NAME) LEN(10) DFT(*ALL) +            
                          SPCVAL((*ALL)) PROMPT('Job name')                     
             PARM       KWD(OPTION) TYPE(*CHAR) LEN(7) RSTD(*YES) +             
                          DFT(*CNTRLD) VALUES(*CNTRLD *IMMED *ABN) +            
                          PROMPT('How to end')

Here is the source for the ENDUSRJOBC program:

/* The ENDUSRJOBC program is the command processor for the ENDUSRJOB   */       
/* command.  The ENDUSRJOB command ends jobs based on selection parms. */       
/* Jobs to end are selected based on User, Job Type, and Job Name.     */       
                                                                                
/* The ENDUSRJOBC program uses the WRKUSRJOB command to pre-select     */       
/* jobs by User and Job Type. The spool file ouput is converted to a   */       
/* flat physical file for final processing by the ENDUSRJOBR program.  */       
                                                                                
/* This program is a demonstration.  Please test this program          */       
/* thoroughly before using it in any production setting.  It is        */       
/* provided as is.  Rod Flohr, 2014-09-11                              */       
                                                                                
             PGM        PARM(&USER &JOBTYPE &JOBNAME &OPTION)                   
                                                                                
             DCL        VAR(&USER) TYPE(*CHAR) LEN(10)                          
             DCL        VAR(&JOBTYPE) TYPE(*CHAR) LEN(12)                       
             DCL        VAR(&JOBNAME) TYPE(*CHAR) LEN(10)                       
             DCL        VAR(&OPTION) TYPE(*CHAR) LEN(7)                         
                                                                                
/* Find current user if passed in user is '*'.  */                              
             IF         COND(&USER = '*') THEN(RTVJOBA USER(&USER))             
                                                                                
/* Pre-select jobs by User and Job Type.  Output to spool file.  */             
             WRKUSRJOB  USER(&USER) STATUS(*ACTIVE) OUTPUT(*PRINT) +            
                          JOBTYPE(&JOBTYPE)                                     
                                                                                
/* Set up the flat physical for processing.  */                                 
             DLTF       FILE(QTEMP/DSPSBJ)                                      
             MONMSG     MSGID(CPF0000)                                          
             CRTPF      FILE(QTEMP/DSPSBJ) RCDLEN(80)                           
             CPYSPLF    FILE(QPDSPSBJ) TOFILE(QTEMP/DSPSBJ) +                   
                          SPLNBR(*LAST)                                         
             OVRDBF     FILE(DSPSBJ) TOFILE(QTEMP/DSPSBJ)                       
                                                                                
/* Call ENDUSRJOBR to finalize the selection by Job Name and */                 
/* end the selected jobs.                                    */                 
             CALL       PGM(ENDUSRJOBR) PARM(&JOBNAME &OPTION)                  
                                                                                
/* Clean up. */                                                                 
             DLTF       FILE(QTEMP/DSPSBJ)                                      
             ENDPGM

Here is the source for the ENDUSRJOBR program:

      * ENDUSRJOBR is called by ENDUSRJOBC, the command processor for the                   
      * ENDUSRJOB command.  The ENDUSRJOB command ends jobs based on selection parms.       
      * Jobs to end are selected based on User, Job Type, and Job Name.                     
                                                                                            
      * ENDUSRJOBC pre-selects active jobs by User and Job Type, using WRKUSRJOB            
      * with OUTPUT(*PRINT).  The spool file is copied to a flat file in QTEMP.             
      * The flat file is processed in this program to make the final selection for          
      * Job Name and end the selected jobs.                                                 
                                                                                            
      * This program is a demonstration.  Please test this program                          
      * thoroughly before using it in any production setting.  It is                        
      * provided as is.  Rod Flohr, 2014-09-11                                              
                                                                                            
      * Process the flat file DSPSBJ using the RPG cycle.                                   
     FDSPSBJ    IPE  F   80        DISK                                                     
                                                                                            
     DJOBID            S             28                                                     
     DCMD              S            200A                                                    
                                                                                            
      * Prototype for QCMDEXC                                                               
     D QCMDEXC         PR                  EXTPGM('QCMDEXC')                                
     D   CMD                        200A   OPTIONS(*VARSIZE) CONST                          
     D   CMDLEN                      15P 5 CONST                                            
                                                                                            
      * Input specs for flat file DSPSBJ                                                    
     IDSPSBJ    AA                                                                          
     I                                  1   80  RECORD                                      
     I                                  4   13  JOBNAME                                     
     I                                 17   26  USER                                        
     I                                 30   35  JOBNUM                                      
                                                                                            
     C     *ENTRY        PLIST                                                              
     C                   PARM                    JOBNAMEIN        10                        
     C                   PARM                    OPTION            7                        
                                                                                            
      * Only process lines with valid job numbers (skip headings, etc.)                     
     C                   IF        %CHECK('1234567890':JOBNUM) = 0              Valid JOBNUM
                                                                                            
      * Compare for selected Job Name, if not '*ALL'                                        
     C                   IF        JOBNAMEIN = JOBNAME OR JOBNAMEIN = '*ALL'    JOBNAME Match
                                                                                            
      * Format the ENDJOB command                                                           
     C                   EVAL      CMD = *BLANKS                                            
     C                   EVAL      JOBID = JOBNUM + '/' + %TRIM(USER) + '/' +               
     C                              %TRIM(JOBNAME)                                          
                                                                                            
      * If OPTION is *ABN, use ENDJOBABN, else use ENDJOB                                   
     C                   IF        OPTION = '*ABN'                              OPTION *ABN 
     C                   EVAL      CMD = 'ENDJOBABN JOB(' + %TRIM(JOBID) +                  
     C                                     ')'                                              
     C                   ELSE                                                   OPTION *ABN 
     C                   EVAL      CMD = 'ENDJOB JOB(' + %TRIM(JOBID) +                     
     C                                     ') OPTION(' + OPTION + ')'                       
     C                   ENDIF                                                  OPTION *ABN 
                                                                                            
      * Call the ENDJOB command, ignoring any errors.                                       
      /FREE                                                                                 
       CALLP(E) QCMDEXC (%TRIM(CMD) : %LEN(%TRIM(CMD)));                                    
      /END-FREE                                                                             
                                                                                            
     C                   ENDIF                                                  JOBNAME Match
                                                                                            
     C                   ENDIF                                                  Valid JOBNUM

Probably the simplest way to pick up the source is to download the ENDUSRJOB zip file linked to above. Create empty source members for each object.  Then go into Navigator, find your source members in the IFS under QSYS.LIB, right click each source member and click Edit, and then copy and paste the text for that member from the downloaded text file, and save the file.  After pasting in the text, go back to SEU or RDi and verify the source is lined up OK and compile everything.

For an example of how this might be useful, check out this post:

Can’t restart Apache on the IBM i because of zombie ZENDSVR6 jobs in QHTTPSVR

Deploy phpMyAdmin compatible with MySQL 5.1

The latest version of phpMyAdmin available for download from the Zend Server Guide Page requires MySQL 5.5.  At the time of this writing, MySQL 5.5 is not yet available on IBM i.  It is in the works, but in the meantime, we need a version of phpMyAdmin that will work with MySQL 5.1.

Here is a deployable file for phpMyAdmin 4.0.5.4, which will work with MySQL 5.1:

phpMyAdmin 4.0.5.4

Please download the above zip file to your desktop.  The file will be phpMyAdmin-4.0.5.4.zpk .

Prerequisites:  Before performing the phpMyAdmin deployment, you must first install Zend DBistart the MySQL server daemon, and set the root password.  These articles can help with that:

Install MySQL using the Zend DBi installer with Zend Server

Start the Zend DBi MySQL Daemon job and verify it is running

Set the Zend DBi MySQL root user password

Please note that the deployment will restart your Apache server, making your web site unavailable for a few minutes.  Please plan this activity for a time when you can have the site down for a short while.  To begin, please open your browser and go to the Zend Server Admin:

http://<your IBM i IP address>:10081

If prompted, log in as admin.  Go to the Administration -> Settings tab:

deploy_phpmy01

Set the Default Server to match the IP address for your IBM i that appears in the browser address line.  In this example we are using a numeric ip address, 192.168.15.103. However, this could also be a domain name in the form of ‘something.com’ or whatever appears between ‘http://’ and ‘:10081’.  Please click the ‘Save; button to apply your changes.

In your Zend Server User Interface, navigate to Applications -> Apps:

deploy_phpmy15

Click the Deploy Application button.

In the dialog that appears, use the “Browse” button:

deploy_phpmy16

 

Browse to the file and open it:

deploy_phpmy17

The file will upload onto the server.  When it is completed, click “Next”:

deploy_phpmy18

The Readme will display.  Please read the information provided, and then click the ‘Next’ button.  

deploy_phpmy05

 

On the Application details display, leave the Display Name set to ‘phpMyAdmin.  The Virtual host should default to the Default Server entered earlier.  In our example, this is 192.168.15.103. DO NOT leave the path blank.  Set it to ‘/phpmyadmin’. This will be the last part of the URL used to access phpMyAdmin on your server.  Please click the ‘Next’ button.

 

deploy_phpmy06

 

Please read the License Agreement and click the check box to indicate your agreement. Click the ‘Next’ button to continue.

 

deploy_phpmy07

 

The Prerequisites Validation will verify your version of PHP and that you have all the required extensions enabled.  The installation of Zend Server 6 for IBM i sets all of these on by default.  If any have been disabled, you will need to enable them and retry the phpMyAdmin deployment.  If everything is green here, please click the ‘Next’ button to continue.

 

deploy_phpmy08

 

On the User Parameters display, leave the Hostname set to ‘127.0.0.1’.  The port should be left at 3306, unless it was changed.  Leave the user ‘root’, and enter the password set for the root user.  In our example the password is ‘belladonna’.  DO NOT check the Apache Basic Authentication box, and do not change anything else on this display.  Please click the ‘Next’ button to continue.

 

deploy_phpmy09

 

The Deployment Summary shows the information you have entered and some information about the application.  The Base URL will be the address used in your browser to access phpMyAdmin.  Notice the last part is ‘/phpmyadmin’, the path specified in the Application Details display.  Take a moment to review the information on this display.  When you are ready, please click the ‘Deploy’ button.  This will launch the deployment.  Please remember that during this time your web site will be restarted and will not be available for a few minutes.

 

deploy_phpmy10

 

The deployment can take a while, depending on your IBM i performance.  Please don’t interrupt it.  You will probably see the ‘staging’ status for some time.

 

deploy_phpmy11

 

And then the activating status.  I was not quick enough to grab a screen shot of the restart.

 

deploy_phpmy12

 

And then finally the application is deployed.  Congratulations! You now have phpMyAdmin. Position your cursor on the line where the application is displayed to expand it.

 

deploy_phpmy13

 

There is a lot of information about your application here.  Take a look around, click on the tabs, kick the tires.  Notice that the Base URL is a link.  Give it a click.

 

deploy_phpmy14

 

A new tab opens up, and there you have phpMyAdmin.  Success!

 

How to exclude Apache Access log entries

The Apache Access log records each request for content received by your Apache web server.  This is important information to have available.  You can see where requests are coming from and detect requests that may be attacks from hackers seeking to get in to your system.  However, you can also see a lot of routine requests that may not interest you.  In this post, a practical example will be used to show how to eliminate repetitive, routine entries from this log.

Zend Server 6 for IBM i introduced a new service, the Zend Server daemon.  This service performs a couple of routine checks about twice a minute, resulting in these two entries in the Apache access log:

127.0.0.1 - - [12/May/2014:13:50:15 -0700] "GET /UserServer/zsd_print_extensions.php HTTP/1.1" 200 3915 "-" "Mozilla/5.0"
127.0.0.1 - - [12/May/2014:13:50:15 -0700] "GET /UserServer/zsd_is_webserver_alive.php HTTP/1.1" 200 6 "-" "Mozilla/5.0"

At that rate, these messages are filling up the log with 240 messages an hour, or 5760 a day in a 24 hour shop.  That’s a lot of messages.  All these messages are really telling us is that the Zend Server daemon process is running, and there are other ways to determine that.  Other than that, they are mostly just taking up space, and can be a real hindrance when checking the access log for more interesting requests.

For this example, we are using IBM HTTP Server powered by Apache on an IBM i at 7.1. We are modifying Zend Server for IBM i version 6.3.0.

Important: Before making this change, please make a back up copy of the Apache configuration file.:
/www/zendsvr6/conf/httpd.conf

You can edit the httpd.conf (Apache configuration) file in Zend Studio from the Remote System Explorer perspective, or you can edit it in the IBM HTTP Administrator in your browser.

This is a simple change.  It adds a SetEnvIf directive, and modifies the existing CustomLog directive to test for the environment variable.

In the httpd.conf file, please find this line:

CustomLog logs/access_log combined

Replace that line with these:

# Check for requests to exclude from the access log
SetEnvIf Request_URI "^/UserServer/zsd_.+\.php$" log_exclude=true
# Change log to test for not log_exclude
CustomLog logs/access_log combined env=!log_exclude

The lines that start with ‘#’ are comments.  The SetEnvIf directive tests for some condition, and sets an environment variable based on the result.  In the example above, the Request_URI is checked for a value that matches the given regular expression.  If it matches, environment variable log_exclude is created.  The Request_URI is a constant defined for this directive that will test against the part of the request after the host, but not including the query string (GET parameters).  Basically, it is the file being requested.

The next part is a regular expression.  It is a pattern that can be used to test a string.  If the string matches the pattern, the test returns true.  This regular expression says the string should start with “/UserServer/zsd_” and end with “.php”.  This should match any requests for “/UserServer/zsd_print_extensions.php” and “/UserServer/zsd_is_webserver_alive.php”, the files being requested in the messages shown at the start of this post.  It would also match anything that fit the pattern.  For example, “/UserServer/zsd_Paul_is_dead.php” or “/UserServer/zsd_I_am_the_walrus.php” would also match.

Be careful constructing your regex pattern. Try not to make it too generic, so that you do not accidentally exclude entries you might want to see. If regular expressions are new to you, consider using a regex tester and try putting in some strings to see what matches. You can also look at a regex tutorial to learn how to make the regex pattern.

The last argument of the SetEnvIf specifies the environment variable to create if the test returns true, and optionally sets a value in the environment variable.  In this example, I did not really need to set log_exclude to true.  The “= true” part of the expression could be left off.  It just seems to make the argument in the CustomLog directive a little more obvious to a PHP programmer.

To modify the CustomLog directive, we are just adding the env argument, “env=!log_exclude”.  In this case, the environment variable will only exist if we want to bypass the request, so the CustomLog will NOT record the request if the environment variable exists.  The exclamation mark means “not” in this expression, just as it would in PHP.  The difference is that in this case, “not” infers “does not exist”, rather then just boolean false.

After making the changes to the httpd.conf file, stop and start Apache for the change to take effect.

You can have more than one SetEnvIf directive in your httpd.conf .  For example, if you really want to see those requests for “/UserServer/zsd_Paul_is_dead.php”, you can test for each of the two files specifically:

SetEnvIf Request_URI "^/UserServer/zsd_print_extensions.php$" log_exclude=true

SetEnvIf Request_URI "^/UserServer/zsd_is_webserver_alive.php$" log_exclude=true

You could also add more SetEnvIf directives to check for more requests you would like to exclude.  Here are some reference links to help you learn more.

SetEnvIf 7.1 documention in IBM i Knowledge Center

CustomLog 7.1 documentation in IBM i Knowledge Center

Regular-Expressions.info – An excellent guide to regular expressions.  Keep in mind that IBM HTTP Server has an older regex engine, so keep it simple.  In particular, avoid back references. This is not supported.

regexpal – A free online regex tester.

 

Heartbleed does not affect Zend Server for IBM i

The word is already out on this, but this is just to add another post to amplify the message.  The Heartbleed vulnerability in OpenSSL does not affect Zend Server for IBM i.  In fact, Heartbleed does not affect IBM i at all.   Basically, the latest version of OpenSSL on IBM i provided in the 5733SC1 Licensed Program is 0.9.8.  Heartbleed affects version 1.0.1, up to 1.0.1f.

Having said that, there are some adventurous folks out there who like to install their own Linux software for PASE.  Please note that it is not recommended to install OpenSSL into PASE.  Instead, use the IBM i Licensed Program 5733SC1 to install OpenSSL, OpenSSH, and zLib.  Still, it is a doable thing, so somebody may have done it.  If that somebody might be you, here is how to verify what version of OpenSSL you may be running:

Log into a 5250 session as QSECOFR, or as a *SERCOFR class user.  Run this command:

call qp2term

This brings up the PASE shell.  In the shell, run this command:

openssl version

The result should be similar to this:

> openssl version           
  OpenSSL 0.9.8m 25 Feb 2010
  $

As long as the version is not in the range 1.0.1 to 1.0.1f, you should be OK.

If you have been living in a cave, or just want to know more, this page explains the Heartbleed vulnerability pretty well.  The CVE can be found here.  c/net has a pretty good list of major sites that were or were not affected.

 

Install MySQL using the Zend DBi installer with Zend Server

This article tells how to install MySQL on a partition that does not have MySQL currently installed. To check whether MySQL is installed, please review this post:

How to Verify MySQL is Installed on your IBM i

Zend DBi is a MySQL build for IBM i that is packaged by Zend.  Zend DBi provides a very simple installation process for MySQL on IBM i.  MySQL is optional on the IBM i.  Not everybody needs it.  To learn a little more about what MySQL is and help decide if you want to install it, please review this article:

Zend DBi, MySQL, and IBMDB2I introduction

Zend DBi is included in Zend Server for IBM i, and can be installed during the Zend Server installation, or later from a menu. Examples in this article use Zend Server for IBM i version 6.3.0.

During the interactive install of Zend Server for IBM i, near the end of the process, the following display will appear:

 MySQL installation (optional) 

 Press ENTER to start ZendDBi installation or press F3 to 
 skip ZendDBi installation 

 F3=Exit

Pressing enter here will install MySQL, which is fine, but I usually don’t do it.  Being a Support Engineer, I like to check out the Zend Server installation and verify everything is OK with that, before bothering to put MySQL on.  That is just my personal preference though.  It is very easy to just do the MySQL install later.  It is the same process, either way.  So I like to press F3 here, and then do the MySQL install from the menu.

To install from the menu, please log on to a 5250 session as QSECOFR or as a *SECOFR class user.  Enter this command:

go zendsvr6/zsmenu

This should bring you to the Zend Server 6 for IBM i Setup Menu.

Use option “6. ZendDBi Management menu”.

Zend DBI will detect that MySQL is not installed (remember this is for a new install, as mentioned at the top of the article).  So, instead of taking you to the menu, this will be displayed:

 MySQL installation (optional) 

 Press ENTER to start ZendDBi installation or press F3 to 
 skip ZendDBi installation 

 F3=Exit

This is the exact same display as was shown during the Zend Server install, because this is the exact same installer.  Press ENTER to run the installation.  You will see this:

MySQL installation (optional) 

 ZendDBi is being installed and configured 
 Please wait ... 

 F3=Exit

Status messages will display as the installer continues.  This all happens quickly on larger systems, but can take a while on smaller systems, especially if the partition is using a partial CPU.  (I don’t recommend running Zend Server on a partial CPU; certainly never in production.  It might be OK for a developer partition, provided your developers are inclined to be patient.)

At the end of the installation, there is an inquiry message displayed:

INSTALLATION SUCCESSFUL!

There is no need to type anything on the Reply line.  Just press enter.  This will come up:

MySQL installation (optional) 

 ZendDBi is installed in directory /usr/local/MySql
 and library ZMYSQL 

 F3=Exit

Press F3.  This should return you to the Zend Server 6 for IBM i Setup Menu.  MySQL is now installed on your system.

Sometimes another screen will be displayed that shows some messages from the shell script.  There will be a reply line, but no response is required.  If you see it, please just press enter to continue.  (Next time I see an example of this, I will come back and update this post to show it.)

So, that’s it!  MySQL is installed.  Just a few clicks, and there it is.  However (and you knew this was coming), there are a few more things that need to be done, before MySQL is ready to use.

1. Restrict public permissions for my.cnf

2. Start the Zend DBi MySQL Daemon job and verify it is running

3. Set the Zend DBi MySQL root user password

4. Install the IBMDB2i storage engine for Zend DBI MySQL (Optional, Cool)

5. Deploy phpMyAdmin using Zend Deployment (Optional, Highly Recommended)

6. Automatically start Zend DBi MySQL at start up (Optional, Very Convenient)

 

Zend DBi may not respond to changes in the my.cnf configuration file

If changes made to the my.cnf configuration file for MySQL do not seem to be changing the behavior of your Zend DBi installation of MySQL, there are two issues to look at.

1. my.cnf is “World-writable”

MySQL will not process the my.cnf configuration file on start up if the file has all permissions granted to user *PUBLIC.  The following message will appear in the PASE start up:

Warning: World-writable config file '/usr/local/mysql/bin/my.cnf' is ignored

It is important to be able to configure MySQL with directives in the my.conf file.  As distributed, this file has public permissions that must be revoked.  This article tells how to do it.

Prerequisite:  Zend DBi must be installed.  The following article can help with that:

Install MySQL using the Zend DBi installer with Zend Server

To begin, please log in to a 5250 session as QSECOFR or a *SECOFR class user.  Please enter these two commands:

CHGAUT OBJ('/usr/local/mysql/bin/my.cnf') USER(*PUBLIC) DTAAUT(*EXCLUDE) OBJAUT(*NONE)

CHGAUT OBJ('/usr/local/mysql/bin/my.cnf') USER(MYSQL) DTAAUT(*RX) OBJAUT(*NONE)

You can copy the commands from here and paste them into your 5250 session command line.  Copy the first command.  Paste it onto the command line.  Press enter.  Copy the second command.  Paste it onto the command line.  Press enter.

Stop and start Zend DBi daemon for the change to take effect.  This can be done from the Zend DBi Management menu.  If you are not familiar with this menu, please take a look at this article:

Start the Zend DBi MySQL Daemon job and verify it is running

2. my.cnf is in a special directory for Zend DBi

Lots of documentation refers to file /etc/my.cnf as the file to use to configure MySQL.  This is not the file to use to configure Zend DBi MySQL.  The correct file to use is /usr/local/mysql/bin/my.cnf .  Changes made to /etc/my.cnf will not affect the Zend DBi MySQL server daemon.  This is because the start up script includes this clause in the mysqld command:

--defaults-file=/usr/local/mysql/bin/my.cnf

Changes to your MySQL configuration should be made in this file.  This file should be backed up before and after changes.  Before, so you can back out any changes that might cause a problem.  After, so you always have a current copy of your configuration, in case you ever need to reinstall Zend DBi.

Set the Zend DBi MySQL root user password

Before you begin using MySQL, it is important to password protect the root user.  User root in MySQL is the most powerful user, similar to QSECOFR on IBM i, admin on Windows, or root on Linux/Unix.

Prerequisites:  Before you can set the root user password, you must first install Zend DBi and start the MySQL server daemon.  These articles can help with that:

Install MySQL using the Zend DBi installer with Zend Server

Start the Zend DBi MySQL Daemon job and verify it is running

After verifying that the Zend DBi MySQL server daemon is running, please sign on to a 5250 session as QSECOFR or a *SECOFR class user.  Please enter this command:

call qp2term

This will bring up a PASE terminal shell. Please enter this command:

cd /usr/local/mysql/bin

This sets your current directory to the MySQL executables directory.  Next, please enter this command to set your root password (substitute your own password instead of ‘bobbysox’):

mysqladmin -u root password 'bobbysox'

Your password for the MySQL root user is now ‘bobbysox’ (or whatever you put in there). The command can only be run with this syntax once. Now that you have a root password, you must specify the password when using the root profile. To use the password, append ‘-p’ to the start of the password. For example, to change the password to belladonna, do this:

mysqladmin -u root -pbobbysox password 'belladonna'

Your root password is now ‘belladonna’ (or whatever you put in there).

Test your password by starting MySQL using the password. Again, to use the password, append ‘-p’ to the start of the password:

mysql -u root -pbelladonna

This should give you a mysql prompt.  You are logged in to MySQL as the root user.  Now, please run this command:

SELECT User, Host, Password FROM mysql.user;

Notice that in MySQL, as is common in many languages, statements are terminated with a semicolon. The output will be similar to this:

+------+------------------+-------------------------------------------+
| User | Host             | Password                                  |
+------+------------------+-------------------------------------------+
| root | localhost        | *2198373A8F20B74720DF9155B88C65981F214C19 |
| root | I61SUP1.ZEND.COM |                                           |
| root | 127.0.0.1        |                                           |
|      | localhost        |                                           |
|      | I61SUP1.ZEND.COM |                                           |
+------+------------------+-------------------------------------------+
5 rows in set (0.00 sec)

Root password is set for localhost, but notice there are additional rows for user root from other hosts, and that their passwords are blank.  Run the following command for each host to password protect user root from other hosts.  In our example, we need to protect I61SUP1.ZEND.COM and 127.0.0.1.  For this demonstration, we will just use the same password, but it can be different for each of them:

SET PASSWORD FOR 'root'@'I61SUP1.ZEND.COM' = PASSWORD('belladonna');
SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('belladonna');

After each command runs, you will see a message like this one:

Query OK, 0 rows affected (0.01 sec)

Don’t worry about the “0 rows affected”.  To verify that passwords have been entered for user root from all hosts, please run this command again:

SELECT User, Host, Password FROM mysql.user;

The output should look like this:

+------+------------------+-------------------------------------------+
| User | Host             | Password                                  |
+------+------------------+-------------------------------------------+
| root | localhost        | *2198373A8F20B74720DF9155B88C65981F214C19 |
| root | I61SUP1.ZEND.COM | *2198373A8F20B74720DF9155B88C65981F214C19 |
| root | 127.0.0.1        | *2198373A8F20B74720DF9155B88C65981F214C19 |
|      | localhost        |                                           |
|      | I61SUP1.ZEND.COM |                                           |
+------+------------------+-------------------------------------------+

Notice that all rows for user root now have passwords.

Type ‘exit’ and press enter to quit mysql.

You should now be able to connect to your MySQL data base at the ip address of your IBM i, port 3306 (or another port, if it has been changed), user root, and password ‘belladonna’ (or whatever you put in there).

You can learn more about securing MySQL from the online MySQL documentation.  Here are some links to get you started:

Securing the Initial MySQL Accounts

User Names and Passwords

How to Reset the Root Password

For reference, here is the entire PASE shell session from this demo:

> cd /usr/local/mysql/bin                                                     
  $                                                                           
> mysqladmin -u root password 'bobbysox'                                      
  $                                                                           
> mysqladmin -u root -pbobbysox password 'belladonna'                         
  $                                                                           
> mysql -u root -pbelladonna                                                  
  Welcome to the MySQL monitor.  Commands end with ; or \g.                   
  Your MySQL connection id is 3                                               
  Server version: 5.1.59 MySQL Community Server (GPL)                         

  Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
  Oracle is a registered trademark of Oracle Corporation and/or its            
  affiliates. Other names may be trademarks of their respective                
  owners.                                                                      

  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement
  .                                                                            

  mysql>                                                                       
> SELECT User, Host, Password FROM mysql.user;                                 
  +------+------------------+-------------------------------------------+      
  | User | Host             | Password                                  |      
  +------+------------------+-------------------------------------------+      
  | root | localhost        | *2198373A8F20B74720DF9155B88C65981F214C19 |      
  | root | I61SUP1.ZEND.COM |                                           |      
  | root | 127.0.0.1        |                                           |
  |      | localhost        |                                           |
  |      | I61SUP1.ZEND.COM |                                           |
  +------+------------------+-------------------------------------------+
  5 rows in set (0.00 sec)                                               

  mysql>                                                                 
> SET PASSWORD FOR 'root'@'I61SUP1.ZEND.COM' = PASSWORD('belladonna');   
  Query OK, 0 rows affected (0.01 sec)                                   

  mysql>                                                                 
> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('belladonna');          
  Query OK, 0 rows affected (0.00 sec)                                   

  mysql>                                                                 
> SELECT User, Host, Password FROM mysql.user;                           
  +------+------------------+-------------------------------------------+
  | User | Host             | Password                                  |
  +------+------------------+-------------------------------------------+
  | root | localhost        | *2198373A8F20B74720DF9155B88C65981F214C19 |
  | root | I61SUP1.ZEND.COM | *2198373A8F20B74720DF9155B88C65981F214C19 |
  | root | 127.0.0.1        | *2198373A8F20B74720DF9155B88C65981F214C19 |
  |      | localhost        |                                           |
  |      | I61SUP1.ZEND.COM |                                           |
  +------+------------------+-------------------------------------------+
  5 rows in set (0.00 sec) 

  mysql>                   
> exit                     
  Bye                      
  $

Install the IBMDB2i storage engine for Zend DBI MySQL

One really interesting feature of MySQL is that it can use different storage engines. One of the engines available for MySQL is IBMDB2i. This engine uses DB2 on the IBM i to store data. This provides the possibility of downloading and installing a free LAMP application, and being able to access the data using DB2 SQL, or even viewing the data as physical and logical files.

IBMDB2i is included with Zend Dbi, but it does require a separate installation.  This article tells how to do it.

Prerequisites:  Before performing this installation, you must first install Zend DBi, start the MySQL server daemon, and set the root password.  These articles can help with that:

Install MySQL using the Zend DBi installer with Zend Server

Start the Zend DBi MySQL Daemon job and verify it is running

Set the Zend DBi MySQL root user password

To begin, please sign on to a 5250 session as QSECOFR or a *SECOFR class user. Please enter this command:

call qp2term

This brings you into the PASE shell.  Please change your current directory to the MySQL executables directory:

cd /usr/local/mysql/bin

Log in to MySQL as the root user (substitute your own root user password instead of ‘belladonna’):

mysql -u root -pbelladonna

Install the plugin

install plugin ibmdb2i soname "ha_ibmdb2i.so";

After running the install command, you should see a message similar to this:

Query OK, 0 rows affected (1.26 sec)

To verify the engine is installed and available, please use this command:

SHOW ENGINES;

IBMDB2i should be listed in the output (page up or down to view all the engines):

+------------+---------+-----------------------------------------------------
-----------+--------------+------+------------+                              
| Engine     | Support | Comment                                             
           | Transactions | XA   | Savepoints |                              
+------------+---------+-----------------------------------------------------
-----------+--------------+------+------------+                              
| InnoDB     | YES     | Supports transactions, row-level locking, and foreig
n keys     | YES          | YES  | YES        |                              
| MRG_MYISAM | YES     | Collection of identical MyISAM tables               
           | NO           | NO   | NO         |                              
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it d
isappears) | NO           | NO   | NO         |                              
| CSV        | YES     | CSV storage engine                                  
           | NO           | NO   | NO         |                              
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary t
ables      | NO           | NO   | NO         |                              
| FEDERATED  | NO      | Federated MySQL storage engine                      
           | NULL         | NULL | NULL       |                              
| IBMDB2I    | YES     | IBM DB2 for i Storage Engine                        
           | YES          | NO   | YES        |                              
| ARCHIVE    | YES     | Archive storage engine                              
           | NO           | NO   | NO         |                              
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performan
ce         | NO           | NO   | NO         |                              
+------------+---------+-----------------------------------------------------
-----------+--------------+------+------------+                              
9 rows in set (0.00 sec)

Type ‘exit’ and press enter to quit mysql. Use F3 to exit the PASE shell.

APAR II14442 lists all the PTF requirements to enable the new query engine. Please review the APAR and verify all PTFs required for your version of IBM i are applied.

Please also verify that the following PTF is applied:

VersionPTFProduct
 V5R4SI465525722SS1
 V6R1SI495765761SS1
 7.1SI497715770SS1

Further information about IBMDB2i can be found in the following resources:

Young i Professionals: DB2 Storage Engine Document

Redbook: Discovering MySQL

Redbook: Using IBM DB2 for i as a Storage Engine of MySQL

MySQL Documentation: The IBMDB2I Storage Engine