MariaDB, PHP, and IBM i: Installation and Configuration Guide

Want to use MariaDB, PHP, and IBM i together? With a recent refresh for MariaDB now available via the IBM i Yum-based Access Client Solutions Open Source Package Management solution, teams working with IBM i OS V7R3 can now replace MySQL and ZENDDBi with MariaDB.

In this blog, we walk through how to install configure, and control MariaDB on IBM i, and how to pair it with your ZendPHP deployment.

Getting Started With MariaDB, PHP, and IBM i

In 2020, IBM i 7.3 introduced support for the open source RDBMS MariaDB. This drop-in replacement for MySQL is a common inclusion in PHP applications, which makes this feature a great addition for teams working with PHP-based IBM i applications.

In the following steps, we'll walk through how to install MariaDB via the Access Client Solution, set up and configure your installation, control your MariaDB database server, and then connect ZendPHP to your newly-created database.

Installation Via Access Client Solution

To start your installation, launch the IBM i Access Client Solution and select "Open Source Package Management." 

When the Open Source Package Management utility is available, select the "Available packages" tab.

From there, scroll to locate the "MariaDB, MariaDB-server option, then click "Install."

After you click install, follow the install screen then wait for the "completed!" message.

After the install is completed, move to the Open Source Package Management Menu, then select "Installed Packages." From there, select View then hit F5 to refresh the list.

Configuration and Setup

Once you have completed your install, it's time to set up and configure your MariaDB.

To start, find your MariaDB server configuration file created in the directory /QOpenSys/etc/mariadb/my.cnf and set the IP binding address and TCP port.

You can use any text editor (e.g. EDTF on IBMi, vim/nano on Linux, or NotePad on Windows).

The bootstrap mysql_install_db initialized the MariaDB data directory and creates the system tables in the MySQL database, if they do not exist. MariaDB uses these tables to manage privileges, roles, and plugins. It also uses them to provide the data for the help command in the MySQL client.

To invoke mysql_install_db, use the following syntax:

mysql_install_db --user=mysql

Note: You can find the latest information about mysql_install_db here.

Controlling the Server

Now that you have your MariaDB installation configured, it's time to start your MariaDB server.

To start, run the mysqld_safe command to start MariaDB database server daemon:

QOpenSys/pkgs/bin/mysqladmin -u root password yournewpassword

/QOpenSys/pkgs/bin/mysqladmin -u root -pyourpassword

Once you've started the server daemon, the SSH terminal session will lock-up when the MariaDB mysqld server starts up.

Then, from a 5250 session, use WRKACTJOB SBS(QUSRWRK) JOB(QP0ZSPWP).

After the SSH terminal shell is closed, the mysqld JOBS remain in SBS QUSRWRK.

Next, use a SBMJOB to start the MariaDB server from an IBM i command line or CLP as follows:

SBMJOB CMD(CALL PGM(QP2SHELL) PARM('/QOpenSys/pkgs/bin/mysqld_safe' '--datadir=/QOpenSys/var/lib/mariadb/data') ) JOB(MARIADB)

Note: Jobs Submitted will use the IBM i SBS defined in the JOBD (QDFTJOBD, QBATCH):

WRKACTJOB JOB(MARIADB)

WRKACTJOB SBS(QBATCH)

Pairing With ZendPHP

Once you have the MariaDB server configured, it's time to pair your MariaDB deployment with ZendPHP.

PHP.INI changes Z:\QOpenSys\etc\php\81zend

; Default port number for mysqli_connect().  
; If unset, mysqli_connect() will use the $MYSQL_TCP_PORT or the mysql-tcp entry in /etc/services or the; compile-time value defined MYSQL_PORT (in that order).  
;Win32 will only look at MYSQL_PORT.
; https://php.net/mysqli.default-port
mysqli.default_port = 3306

; Default socket name for local MySQL connects.  
If empty, uses the built-in MySQL defaults.
; https://php.net/mysqli.default-socket
mysqli.default_socket = /QOpenSys/var/lib/mysql/mysql.sock

<? php
 $servername = "localhost:3306";
 $username = "root";
 $password = "shlomo";
 $dbname = "test"; 

 $conn = new mysqli($servername, $username, $password, $dbname);
    if($conn->connect_error) {
        die("Connection Failed" . $conn->connect_error);
 }

echo 'Connected successfully';

/* change default database to "test" */
mysqli_select_db($conn, "test")  or die('Could not select database');

/* get the name of the current default database */
$result = mysqli_query($conn, "SELECT DATABASE()");
$row = mysqli_fetch_row($result);
echo "<br>";
printf("Default database is %s.\n", $row[0]);

/* Retrieve all rows from my file */
$query = "SELECT id, name FROM shltbl1";
$result = mysqli_query($conn, $query);

echo "<table>\n";
while ($line = mysqli_fetch_row($result)) {
   echo "\t<tr>\n";
   foreach ($line as $col_value) {
       echo "\t\t<td>$col_value</td>\n";
   }
   echo "\t</tr>\n";
}
echo "</table>\n";
?>

Legacy Storage Engines

One thing to note when using MariaDB is that the IBMDB2I storage engine introduced in MySQL 5.1.33 was considered production-ready in MySQL 5.1.35, but was removed in MySQL 5.1.54. It is not supported in MariaDB.

You can check the full list of legacy storage engines for MariaDB here.

Final Thoughts

In this blog we walked through many of the steps needed to deploy MariaDB in IBM i, and how to pair your MariaDB with ZendPHP. While this isn't a comprehensive guide for configuring MariaDB, it should give you everything you need to get started.

Try ZendPHP for Free

ZendPHP is now free to try for teams using IBM i. Start your trial by visiting our ZendPHP trial page today.

Get Started Here

Additional Resources

• Resource Collection - Zend IBM i Blog
• Blog - IBM i Modernization: Modernizing Smartly and on a Budget
• Blog - Using ZendPHP, PHP-FPM, and Nginx on IBM i
• Blog - Using Mezzio on IBM i
• Blog - Using HTTP Proxy Support for IBM i
• Blog - IBM i Merlin and What it Means for PHP Teams
• Blog - Installing Zend Server on IBM i 
• Blog - Installing ZendPHP on IBM i
• Blog - IBM i 7.5 for PHP: Exploring Db2 Services