I have had MySql installed on my i520 ever since the possibility of running it was mentioned and before IBM said they would support it. Having just completed the initial design of the new features for RAP/400 I was looking at the next steps. PHP has long been my language of choice for web development, I have tried Websphere but gave up because of it requirements and complexity. So when looking at what else I would like to do for RAP/400 I thought about the interface. MiMiX has had an HTTP based interface for a long time and while RAP/400 is not in the same league I did like the HTTP interface MiMiX used. So I decided that I would refresh the environment ready to explore the possibilities for a HTTP interface for RAP/400 and possibly JobQGenie. The first thing I wanted to do was replace the original installation of MySQL, not that I would be using it for RAP/400 necessarily but because I want to make sure I have a fairly up to date environment to work with. This was more of a challenge than I first expected so I have listed below the steps I took and how I found problems with the new version of MySQL in the i5 PASE environment.
First thing to do was download the binary from the mysql website. I was interested in the Community Server (This is the free one!) so I selected the link from the page and found the required mirror to download from. Because I dont have my i5 setup to be able to browse the internet I simply used the PC to download the object to first then FTP’d it to the i5. You must store the downloaded object in the library where you are going to extract the file into ‘/usr/local’.
The object is downloaded as a .GZ file which is zipped, so I need to unzip the file, I could have unzipped it on the PC and then uploaded the .tar file to the i5 but I decided to get a GZIP utility for the i5. I found a utilitity at www.gzip.org which suited my purposes and again it was free! To install the utility you have to copy the save file from the download to the i5 and then restore the object (*PGM) to a library on the i5. I simply created a library called GZIP and restored the object to there. I could then run the command to unzip the file using
call gzip/gzip parm('-d' '/usr/local/mysql-5.0.41-aix5.2-powerpc-64bit.tar.gz'). The resulted in an uncompressed file called ‘mysql-5.0.41-aix5.2-powerpc-64bit.tar’ which I could now run the tar command against
'tar -xvf mysql-5.0.41-aix5.2-powerpc-64bit.tar'. This resulted in a directory mysql-5.0.41-aix5.2-powerpc-64bit.
The next thing I had to do was create a symbolic link to the directory so I dont have to use the full directory name everytime I need to access something within it. I did this using the command
'ln -s mysql-5.0.41-aix5.2-powerpc-64bit mysql'. The next thing was to change the ownership of all the objects within that library to be owned by a user profile with limited authority. I created a user profile MYSQL using
'CRTUSRPRF USRPRF(MYSQL) PASSWORD(*NONE) STATUS(*DISABLED) INLMNU(*SIGNOFF) TEXT(â€˜MySQL Userâ€™)' This will create a profile which cannot be used as a sign on as it has no password, its disabled and it has an initial menu of *SIGNOFF. To make sure the objects are owned by this profile you need to be in the PASE environment.
'CALL QP2TERM' will put you into a PASE terminal session from where you can issue the following commands
'cd /usr/local/mysql' 'chown -R mysql .' (dont forget the period at the end or nothing happens). Now you have everything installed and owned by the MYSQL profile.
Before starting the server you need to create the base databases that mysql will use, this includes the database for the authorities etc. To create the db simply issue the following command from inside the QP2TERM session
'scripts/mysql_install_db' You have to be in the ‘/usr/local/mysql’ directory to issue this command so if you have completed the ownership changes you should be ok! We had a problem where by we had to exit the QP2TERM and start it again for the command would work so we had to issue the cd command again to set us in the right directory.
One problem we did encounter was the use of new libs within this version of mysql. The previous version we installed was able to be started after the above steps had been taken, but this release failed with a message complaining about libmysqlclient.a(libmysqlclient.so.15) not being allowed to load. After hours of investigation and reading the MYSQL documentation (yes men do read the manuals sometimes) I found a section about setting up the LD_LIB_PATH (or something like that). I then delved into the PASE documentation to find a mention about using the ADDENVVAR command to set PASE Environment variables. So I simply used
ADDENVVAR ENVVAR(LIBPATH) VALUE('/usr/local/mysql/lib') and everything started up and runs OK. You can also use the WRKENVVAR to check if you already have it set and allow you to change it accordingly.
Once you have done this you should be able to start the server from QP2TERM session using
'bin/mysqld_safe -u=mysql' I wrote a number of scripts and created an environment which allows this to be done automatically which I will share with everyone later. I also noticed there are scripts provided with the install which may be useable? But I am not sure if they can isolate the jobs into a specific subsystem which is what I needed to do?
This should give you a working MySQL server, however it will have a major security exposure which you need to close. On installation the product creates authority to the MySQL DB and access to a user ‘root’. It also sets this profile up to not require a password which is where the major exposure comes in! So we need to fix that, to do so we need to be in the QP2TERM session and in the mysql directory. From this directory sign ito the mysql session using the following command
'bin/mysql -u root' this will start a session in the MySQL server and allow you to set up the passwords. On installation the root profile was given access from ‘localhost’, ‘127.0.0.1’ and from the server (in our case ‘SHIELD1.SHIELD.local’). So we have to close down the access to ensure it has a password or remove the access altogether. To change the access we can issue the following command
'SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');' (‘newpwd’ is your own password you want to use) and dont forget the ‘;’ at the end which signifies to the MySQL command processor you have a full command entered (if you forget it you can simply enter it on the next line as the interpretor takes all data until it sees it as being part of the same command Do the same for the other ‘root’ entries or simply remove the others using
'DELETE FROM mysql.user WHERE Host=â€™127.0.0.1â€™ AND User=â€™rootâ€™; ' and 'DELETE FROM mysql.user WHERE Host=â€™SHIELD1.SHIELD.localâ€™ AND User=â€™rootâ€™;'.
This will give you a working MySQL Server, you still have to create and populate a DB before its of much use as the test DB shipped doesnâ€™t do much! We will publish how to install the phpMyAdmin HTTP server next as this is a great tool for being able to build new databases and set privileges accordingly. You can still use the environment we have built to create new DB’s etc except you have to use the command line to build them which for some large DB’s could be time consuming and prone to error.
If you have any questions please let us know.
‘call QP2TERM’ from an i5OS command line simply puts you into the PASE environment. Issuing the command ‘/usr/local/mysql/bin/mysql -u root’ starts a MySQL session in the PASE environment with the user profile ‘root’, this profile does not have to exist on the i5 as it is only valid within the MySQL environment.