MySQL autostart on the IBM System i5

One of the problems with the initial install of MySQL on the i5 is it is not able to be started in a separate sub-system. When you issue the mysqld_safe -u mysql command from your QP2TERM session it will automatically start in the QINTER subsystem (I Use QCTL as the controlling subsystem) which is not adequate for a lot of companies. Plus to end the server you have to go into the QP2TERM session again and issue either a kill request or use the mysqladmin script.

I wanted the job to have a start program and an end program that could be issued from a batch job or use the autostart feature with the subsystem to start the server. Here is the solution I built. It may not suit everyones requirements but it is a start.

First of all I built a library MYSQLLIB which would contain all the programs and objects associated with the start up.

CRTLIB LIB(MYSQLLIB) TEXT(‘MySQL Lib’)

Next we created the a Source Files to hold the programs we will use for starting and ending the server.

CRTSRCPF FILE(MYSQLLIB/QCLSRC) TEXT(‘Source File’)

Now we will create the members with the following code. We have provided the minimum code and you should consider adding some error checking and clean up routines.

This is the shutdown program: We called the member STOPMYSQL, ensure you replace yourpwd with the actual password associated with the profile.

PGM

CALL PGM(QP2SHELL) +
PARM(‘/usr/local/mysql/bin/mysqladmin’ +
‘–user=root’ ‘-pyourpwd’ SHUTDOWN)
ENDPGM

This is the startup program: We called the member STRMYSQL

PGM

SBMJOB CMD(CALL PGM(QP2SHELL) +
PARM(‘/usr/local/mysql/bin/mysqld_safe’ +
‘–user=mysql’)) JOB(MYSQLD) +
JOBD(MYSQLLIB/MYSQLJOBD) +
JOBQ(MYSQLLIB/MYSQLJOBQ)

ENDPGM

Because we want to have the MySQL server run in its own subsystem we need to create a job queue

CRTJOBQ JOBQ(MYSQLLIB/MYSQLJOBQ) TEXT(‘MySQL JOBQ’)

Next we created a job description, this is a basic job description and you should modify it to suit your needs.

CRTJOBD JOBD(MYSQLLIB/MYSQLJOBD) JOBQ(MYSQLLIB/MYSQLJOBQ) TEXT(Mysql Job Description) RTGDTA(‘MYSQL’) RQSDTA(‘call mysqllib/strmysql’)

We also create a class for completeness of the solution, you could just use the base one but again this gives you the ability to really control the environment.

CRTCLS CLS(MYSQLLIB/MYSQLCLS) RUNPTY(50) TEXT(‘MySQL Class’)

The next important element is the subsystem where the jobs will run.

CRTSBSD SBSD(MYSQLLIB/MYSQLSBS) POOLS((1 *BASE)) TEXT(‘MySQL Subsystem’)

Link the job queue we created to the subsystem.

ADDJOBQE SBSD(MYSQLLIB/MYSQLSBS) JOBQ(MYSQLLIB/MYSQLJOBQ) MAXACT(*NOMAX)

Add some routing entries so the job is loaded to the subsystem from the job queue.

ADDRTGE SBSD(MYSQLLIB/MYSQLSBS) SEQNBR(100) CMPVAL(MYSQL) PGM(QCMD) CLS(MYSQLLIB/MYSQLCLS)
ADDRTGE SBSD(MYSQLLIB/MYSQLSBS) SEQNBR(999) CMPVAL(*ANY) PGM(QCMD)

We added a simple autostart request so if the subsystem was started we would automatically start the MySQL server. this can be omitted if you want to start the server manually everytime.

ADDAJE SBSD(MYSQLLIB/MYSQLSBS) JOB(AUTOSTART) JOBD(MYSQLLIB/MYSQLJOBD)

Now when you start the susbsystem you will automatically start the MySQL server and have a simple server running. To end the server you can call the program we have created.

If the server fails to start you will see various errors returned depending on your configuration. A couple of places you should look to identify the problems are in the Out Queue QPRINT, here you should find a printout from the jobs which failed. No Job Logs are created from the PASE environment for the failing processes. The next place to look is within the error log created as part of the installation ours was SHIELD1.SHIELD.local.err where SHIELD1 = system name, SHIELD = domain. this can be found in the mysql directory under the data directory.

I hope this is useful and would be happy to take any constructive comments on how the setup could be better controlled and maintained.

Chris…