MySQL on i5/OS Example
p. MySQL is an open source database that has gained popularity in the web application world and is used in most of the leading/hot PHP (Hypertext Pre Processor) applications. Now that System i is a supported hardware platform for PHP with the availability of Zend Core for i5/OS, we have documented instructions for downloading, installing and running MySQL in the Portable Application Solution Environment (PASE) on the System i. MySQL is not an IBM i5/OS supported environment, use accordingly.
p. If you are an ISV interested in running your PHP application on System i please contact sandberg@us.ibm.com or jrush@us.ibm.com. We would like to work with you.
p. This example was created by interpreting the instructions on www.mysql.com for the PASE environment on the System i.
p. 1. Create user profile mysql. It is a disabled id so it can not be used to sign on to the i5 system but it can be used by MySQL.
CRTUSRPRF USRPRF(MYSQL) STATUS(*DISABLED) TEXT('MySQL user id')
p. 2. Go to www.mysql.com and download the AIX 5.1 (POWER 64-bit) version of MySQL 5.0 to the PC. mysql-standard-5.0.21-aix5.2-powerpc-64bit.tar.gz is the name of the file.
p. 3. Use a zip utility to unzip mysql-standard-5.0.21-aix5.2-powerpc-64bit.tar.gz creating mysql-standard-5.0.21-aix5.2-powerpc-64bit.tar
p. 4. Copy the mysql-standard-5.0.21-aix5.2-powerpc-64bit into the IFS. In this example it is copied to \usr\local
p. 5. Logon to the System i using the QSECOFR user id
p. 6. Enter the PASE environment using call qp2term
p. 7. Change the directory to usr/local cd usr/local/
p. 8. Untar mysql-standard-5.0.21-aix5.2-powerpc-64bit.tar tar -xvf mysql-standard-5.0.21-aix5.2-powerpc-64bit.tar
p. 9. Create a symbolic link ln -s mysql-standard-5.0.21-aix5.2-powerpc-64bit mysql
p. 10. Create the MySQL grant tables. Change directories to mysql cd mysql and then run the command scripts/mysql_install_db –user=mysql
p. 11. Once MySQL has been installed follow the Unix Post-Installation Procedures
p. 12. Start & Stop MySQL
* Start the MySQL server: bin/mysqld_safe –user=mysql &
* Stop MySQL server bin/mysqladmin -u root shutdown
p. 13. Other useful commands for verifying the install are:
* bin/mysqladmin version
* bin/mysqladmin variables
* Simple tests to verify that you can retrieve information from the server:
* bin/mysqlshow
* bin/mysqlshow mysql
h2. Other Topics:
USERS and AUTHORITIES
- GLOBAL: GRANT ALL PRIVILEGES ON *.* TO 'wfusr'@'%' IDENTIFIED BY 'wfpass' WITH GRANT OPTION;
(refer to MySQL documentation)
- SCHEMA: GRANT ALL PRIVILEGES ON workflow.* TO 'wfusr'@'%' IDENTIFIED BY 'wfpass' WITH GRANT OPTION;
(refer to MySQL documentation)
SAMPLES
- Sample CL program to start MySql in QBATCH subsystem
- Sample PHP app to take user input, write to MySQL DB, and display table to user
- Sample PHP app to take user input, write to DB2 for i5, and display table to user
TOOLS and URLs
- www.i5php.net - iSeries MySQL forum
- MySQL Administrator -- Administer MySQL Server
- MySQL Query Browser -- Use this graphical client to work with your MySQL databases and run queries
7 comments to “MySQL on i5/OS Example”
October 19th, 2006 at 4:17 pm
Where would we find the "Sample CL program to start MySql in QBATCH subsystem"?
November 13th, 2006 at 3:55 pm
I’m still having some problems, but this may help.
MYSQL userid (in my testing) must be in the *SECOFR usrcls.
Currently I have the daemon running and it is allowing the
‘bin/mysqlshow test’ work and give results. However; bin/mysqlshow mysql’ does NOT work, I get an access denied for user…….
anyway here is the program I am using to start the daemon, EVEN THOUGH I THINK I STILL SOME ISSUES. It may help you help everyone.
>>>>>>>>>> Start code that at least gets the daemon going
PGM
DCL VAR(&CMD) TYPE(*CHAR) LEN(33)
DCL VAR(&PARM1) TYPE(*CHAR) LEN(16)
DCL VAR(&NULL) TYPE(*CHAR) LEN(1) VALUE(X’00′)
CHGVAR VAR(&CMD) VALUE(‘/usr/local/mysql/bin/mysqld_safe’)
CHGVAR VAR(&CMD) VALUE(&CMD *TCAT &NULL)
CALL PGM(QP2SHELL) PARM(&CMD)
ENDPGM
<<<<<<<<<< End Code
>>>>>>>>>> Start code that is shown in examples by zend and others, BUT does NOT work for me
PGM
DCL VAR(&CMD) TYPE(*CHAR) LEN(33)
DCL VAR(&PARM1) TYPE(*CHAR) LEN(16)
DCL VAR(&NULL) TYPE(*CHAR) LEN(1) VALUE(X’00′)
CHGVAR VAR(&CMD) VALUE(‘/usr/local/mysql/bin/mysqld_safe’)
CHGVAR VAR(&CMD) VALUE(&CMD *TCAT &NULL)
CHGVAR VAR(&PARM1) VALUE(‘ –user=mysql &’)
CHGVAR VAR(&PARM1) VALUE(&PARM1 *TCAT &NULL)
CALL PGM(QP2SHELL) PARM(&CMD &PARM1)
ENDPGM
<<<<<<<<<<< End Code
If any one can help with the bin/mysqlshow mysql problem I’d be Greatfull!
November 13th, 2006 at 4:05 pm
I’m now sure I have an authority problem with user mysql.
this gives the correct results
\bin>mysqlshow -u root mysql
while this does NOT
\bin>mysqlshow mysql
At this point it almost has to be some auth issue that I don’t get…
I am not very good with unix etc. But working on it.
sid
November 13th, 2006 at 4:05 pm
I’m now sure I have an authority problem with user mysql.
this gives the correct results
\bin>mysqlshow -u root mysql
while this does NOT
\bin>mysqlshow mysql
At this point it almost has to be some auth issue that I don’t get…
I am not very good with unix etc. But working on it.
sid
November 13th, 2006 at 4:06 pm
sid
November 13th, 2006 at 9:01 pm
I copied from a windows example and both the \ and > sould be /
sorry.
sid
December 7th, 2006 at 9:59 am
I was unable to find the 5.0.21 file for my install of MySQL, but found the 5.0.27 version instead, so this issue might relate to that.
Everything was going fine until step #10. The ‘mysql_install_db’ process is unable to find ‘my_print_defaults’ and the help file ‘fill_help_tables.sql’ in /support-files. For this second file, I found a copy of it instead in the ‘share’ folder, but I can’t locate ‘my_print_defaults’.
Any help would be GREATLY appreciated.