PHP and MariaDB: Configuring ZendHQ and MariaDB for IBM i Disaster Recovery

No web application is immune to disaster, including PHP applications running on IBM i. From failed hardware to user error to other critical failures, your team will need to act quickly to recover lost data, keep your PHP IBM i applications running, and return to normal operations as fast as you can.

In this blog, I talk through considerations for creating a PHP IBM i disaster recovery strategy using database redundancy and discuss why pairing PHP and MariaDB databases is a strong option for IBM i teams. I then explore ZendHQ's new disaster recovery features, explain how this new functionality can aid in recovering lost ZendHQ data, and provide a step-by-step guide for configuring ZendHQ and MariaDB on IBM i.

IBM i Disaster Recovery, PHP, and MariaDB: Overview

Configuring PHP and MariaDB or other databases is an efficient way to create database redundancy for disaster recovery. In this section, I give an overview of how to create an IBM i disaster recovery plan, explain how PHP and MariaDB can benefit your team and disaster recovery strategy, and explore other database options for PHP IBM i applications.

Creating an IBM i Disaster Recovery Plan

Disasters strike without warning, and the best way to ensure successful IBM i disaster recovery is to build a recovery plan before a failure occurs. One approach to creating this plan is to utilize database redundancy, which involves backing up data to one or more secondary locations. These backups may be stored in databases such as MariaDB, PostgreSQL, or others. In the event of a disaster, outage, or other event, simply use one of your backups to recover any lost data.

In addition to creating database redundancy and data backups, answering the following questions can aid in building your IBM i disaster recovery plan:

• What elements of your system need to be backed up, and are they backed up on a regular schedule?
• What PHP or other applications are involved, and what environment do they run on?
• How is your data structured?
• What database (such as MariaDB) do you use to store your data?
• Which members of your team are responsible for what steps in the case of the disaster, and are they adequately trained for their role?
• Does any of your software require licenses to reinstall, and are those licenses easily accessible?

For more information about creating a disaster recovery plan, please visit our guide, How to Build a Backup and Recovery Plan for PHP Web Apps.

Benefits of Using PHP and MariaDB Together

PHP and MariaDB deliver many benefits for IBM i teams creating a backup and recovery plan. These two technologies easily integrate with one another, use relatively simple syntaxes, and are supported by most web hosting providers. These traits make PHP and MariaDB accessible options for most developers, regardless of skill level. Additionally, MariaDB is suited to handling heavy traffic and can help scale IBM i PHP applications to meet evolving business needs. 

Finally, as both PHP and MariaDB are open source technologies, developers can take advantage of robust and extensive community support without worrying about licensing costs, freeing resources for other uses.

Which Database is Best for PHP?

While PHP and MariaDB provide powerful results, the best database for your PHP IBM i application may vary depending on your developer team skill sets, available resources, and overall business goals. In addition to MariaDB, you may consider:

• SQLite
• MySQL
• PostgreSQL 
• MongoDB
• Or others

Introducing New ZendHQ Disaster Recovery Features for IBM i

ZendPHP runtimes, paired with the advanced ZendHQ extension, provide excellent performances for IBM i PHP applications. As a part of a recent database expansion, ZendHQ users can now take advantage of built-in features designed to regain or migrate ZendHQ data. This keeps your ZendHQ data protected in the event of a corrupted database, crash, or other failure.

Prior versions of ZendHQ only used SQLite, which is often challenging to use as a part of a database redundancy plan for disaster recovery. With the new ZendHQ disaster recovery features, your team can use MariaDB and PostgreSQL to create backups for ZendHQ. This allows your IBM i team to establish a primary server with one or more replicas, and in the event the primary fails, promote a replica to regain lost ZendHQ data.

How to Configure ZendHQ and MariaDB on IBM i

The ZendHQ daemon uses database engines to store configuration data and historical data. While the default database engine for ZendHQ is SQLite, the recent database expansion allows users to choose to configure the ZendHQ daemon to use remote relational databases such as PostgreSQL or MariaDB. 

For the purposes of this guide, I will be walking through the steps for installing and configuring MariaDB as a database alternative for the default local SQLite database. For more information on ZendPHP, ZendHQ, and MariaDB, please visit the associated documentation:

• ZendPHP IBM i Installation
• ZendPHP + ZendHQ Installation
• ZendHQ Database Configuration
• MariaDB Primer

IBM i – Access Client Solution (ACS)

IBM i ACS is the preferred method for installing open source related software on an IBM i server. It is a requirement for configuring ZendHQ and MariaDB. Visit IBM's documentation for full IBM i ACS installation instructions.

Begin by updating to the latest IBM i ACS version. In general, IBM recommends using bash shell for SSH connections:

• SSH sessions runs bash shell
• QP2TERM uses the sh shell
• Change the default IBM i shell to bash

To use a SQL client interface, choose STRSQL or iACS RunSQL:

CALL QSYS2.SET_PASE_SHELL_INFO('*DEFAULT','/QOpenSys/pkgs/bin/bash’)

To use the chsh package, use the following:

$ /QOpenSys/pkgs/bin/chsh -s /QOpenSys/pkgs/bin/bash

Visit the IBM i website for further documentation regarding how to troubleshoot setting bash.

Install MariaDB Using ACS or yum Command

Execute from the command line and use SSH termal QP2Term/MobaXterm/PuTTy:

$   yum list available mariadb*
$   /Qopensys/pkgs/bin/yum install mariadb-10.6 mariadb-10.6-server

Next, launch the IBM i ACS, then select "Open Source Package Management," as pictured below.

Once the Open Source Package Management utility is available, select the "Available Packages" tab. Next, locate the "mariadb-10.6" and "mariadb-10.6-server" options. Click the "Install" Button.

 Follow the directions on the Install screen, and wait for the "completed" message to appear.

Now, from the Open Source Package Management menu, select the "Installed Packages" tab. Select View | Refresh, or hit F5 to refresh the list.

MariaDB Server Configuration

I will now explain how to configure the MariaDB server for use with ZendHQ.

Begin by creating and initializing the MariaDB database. If installing for the first time, run the following command to initialize MariaDB in the directory /QOpenSys/var/lib/mariadb/data:

$ /QOpenSys/pkgs/bin/mysql_install_db

Set the MariaDB server to listen to a particular IP address or all IP addresses by modifying the MariaDB server configuration file created in the directory /QOpenSys/etc/mariadb/my.cnf. Next, set the IP binding address and TCP port to use any text editor, such as EDTF on IBM i, vim/nano on Linux, or NotePad on Windows:

$ WRKLNK ‘/QOpenSys/etc/mariadb/my.cnf’     
[mysqld]
bind-address=0.0.0.0
port=3306

When you have successfully completed the above steps, you can start the MariaDB server daemon. Change the directory if not set in your path or in your home/user/.profile file.

$ cd /QOpenSys/pkgs  
$ /QOpenSys/pkgs/bin/mysqld_safe --datadir=/QOpenSys/var/lib/mariadb/data

The following messages will appear:

mysqld_safe Logging to '/QOpenSys/var/lib/mariadb/data/I74SUP3.err’
mysqld_safe Starting mariadbd daemon with databases from /QOpenSys/var/lib/mariadb/data

Next, use an IBM i command line or CL program:

$ SBMJOB CMD(CALL PGM(QP2SHELL) PARM('/QOpenSys/pkgs/bin/mysqld_safe' '--datadir= QOpenSys/var/lib/mariadb/data' )) JOB(MariaDB) JOBD(QGPL/QINTER)

Please note: when starting the MariaDB mysqld daemon server from an SSH terminal, the terminal session will be locked. Use CTRL-C to exit. Once you exit the SSH session, the MariaDB mysqld daemon JOBS will remain active in SBS QUSRWRK.

Next, you will need to show MariaDB active processes. Get the list of all running MariaDB processes using the ps utility or WRKACTJOB:

$ ps ax | grep mariadb  or  ps -ef | grep mariadb  or ps -ef | grep -i mariadb
 1877270      - A    0:09 /QOpenSys/pkgs/bin/mariadbd --basedir=/QOpenSys/pkgs

$ WRKACTJOB SBS(QUSRWRK)
QP0ZSPWT     SHLOMO     BCI      .0  PGM-sh           THDW
QP0ZSPWT    SHLOMO     BCI      .0  PGM-mariadbd     SELW

The process is started from a CLP or a 5250 session, and SBS is used based on JOBD:

$ WRKACTJOB SBS(QBATCH)  
$ WRKACTJOB JOB(MARIADB)

Verify the server is listening on port 3306:

   $ *NETSTAT CNN 

Now, set the root user password. The root user password must be set using the mysqladmin tool after first use of MariaDB:

$ /QOpenSys/pkgs/bin/mysqladmin  -u root password MyPassword

You can use the following command as well:

$ /QOpenSys/pkgs/bin/mariadb-secure-installation

Make sure to answer NO(N) to the questions. Enable unix_socket authentication? [Y/N] n. If this step is missed, an error will appear reading: ERROR 1524 (HY000): Plugin 'unix_socket' is not loaded." On IBM i, this is disabled due to limitations in PASE.

Next, you will set MariaDB remote access. By default, MariaDB is configured to listen on localhost (127.0.0.1), and as such only native IBM i users and apps can access the MariaDB server. For using remote-management software with MariaDB databases – such as MySQL Workbench, HeidiSQL, DBeaver, or others – you must establish remote access first.

To allow MariaDB root users and other users remote access to the MariaDB server from hosts on the network, simply use the mariadb or mysql command from any SSH Terminal (QP2Term/Moba/PuTTy). After running the command, you will be prompted for your password.

   $  mariadb -u root –p
   $ mysql -u root –p
   $ GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY ‘MyPassword’ WITH GRANT OPTION;

To stop the Maria DB server daemon, use the following:

$ /QOpenSys/pkgs/bin/mysqladmin  --no-defaults --user=root --password=mypassword  shutdown

You can also use an IBM i command line or CL program:

$ SBMJOB CMD(CALL PGM(QP2SHELL) PARM(('/QOpenSys/pkgs/bin/mysqladmin') ('--no-defaults') ('--user=root') ('--password=mypassword') ('shutdown'))) JOB(MariaDB) JOBD(QGPL/QINTER)

Next, configure MariaDB and set the log files directory location:

$ WRKLNK    OBJ('/ QOpenSys /etc/mariadb/*') DETAIL(*EXTENDED) DSPOPT(*ALL)
$ WRKLNK    OBJ('/QOpenSys/var/lib/mariadb/data/*.err') DETAIL(*EXTENDED) DSPOPT(*ALL)

Finally, test the MariaDB server using ZendPHP. Simply install the PHP MySQL mysqlnd extension to complete testing:

<?php 
$servername = "localhost:3306"; 
$username = "root"; 
$password = "mypassword"; 
$dbname = "test";  
$conn = new mysqli($servername, $username, $password, $dbname);    
if($conn->connect_error)   { 
    die("Connection Failed" . $conn->connect_error); 
}
echo 'Connected successfully’;
?>

Configuring ZendHQ Using MariaDB

Create the MariaDB ZendHQ database and user. You will need to create the ZendHQ MariaDB database first. The ZendHQ daemon will create the tables. Simply log in to MariaDb, and enter the password when prompted.

$ mariadb -u root –p

Once you've logged in, create a new database. You can use any name selected, but I've used "zendhq" for the purposes of this walk through.

MariaDB > CREATE DATABASE zendhq;

You can then create a new user. Again, you can use any name selected, but I've used "zendhq" for this example.

MariaDB > CREATE USER 'zendhq'@'localhost' IDENTIFIED BY 'zendhq';
 MariaDB > GRANT ALL PRIVILEGES ON *.* TO zendhq'@'%' IDENTIFIED BY 'YourPassword’;
 MariaDB > GRANT ALL PRIVILEGES ON zendhq.* TO ‘zendhq’@’localhost’ IDENTIFIED BY 'YourPassword’ ;

Configure MariaDB as a Database Instead of SQLite

The default database for ZendHQ is SQLite, so you will need to configure MariaDB database to replace it. Begin by changing the /opt/zend/zendphp/etc/zendhqd.ini. Comment the directive for SQLite.

# The default SQLite database engine
;include = zendhqd_sqlite.ini

Uncomment the directive for MySQL/MariaDB.

# Mysql/Mariadb database engine
include = zendhqd_mysql.ini

Change the ‘/opt/zend/zendphp/etc/zendhqd_mysqli.ini host/port/user/password/socket path.

# Type of the database engine (Do Not Change)
zendhqd.database.type = mysql
# Database host # Default: localhost
zendhqd.database.hostname = localhost
# Database port 
zendhqd.database.port = 3306
# Database user name # Default: (empty)
zendhqd.database.username = zendhq
# Database password # Default: (empty)
zendhqd.database.password = zendhq
# Options # Default: (empty)
zendhqd.database.connect_options = UNIX_SOCKET=/qopensys/var/lib/mysql/mysql.sock

Restart the ZendHQ daemon to apply changes, then log in to MariaDB to confirm the files were created:

$ mariadb -u zendhq –p

Once you log in, select to work with the ZendHQ database:

MariaDB > show databases;
MariaDB > use zendhq;
MariaDB > show tables;
MariaDB > describe zendhq.conf_values;
MariaDB > select id, value from zendhq.conf_values;

Test and Migrate ZendHQ Databases Using zendhqctl Database Commands

The final step in configuring ZendHQ using MariaDB is to test and migrate the ZendHQ database using zendhqctl database commands. View the full documentation here.

To test, use the following:

zendhqctl database test [-t|--table-prefix prefix]

To migrate to the new database, use the following:

zendhqctl database migrate [-y|--yes] [-k|--keep-migration-file] FROM [TO] 

Final Thoughts

Configuring ZendHQ and MariaDB on IBM i allows your team to take advantage of the many benefits that come from combining PHP and MariaDB. From aiding in disaster recovery to simplifying deployments and beyond, PHP and MariaDB – particularly when also including ZendPHP and ZendHQ – unlock access to advanced data management, observability, and orchestration tooling for IBM i applications.

Make Our PHP Experts Your PHP Experts

Zend Professional Services make migrating, updating, administrating, and managing IBM i PHP applications easy. Explore our services, and contact us today to learn more about how we can support your mission-critical apps.

See Professional Services  Talk to an Expert

Additional Resources

• Documentation - ZendPHP + ZendHQ
• Guide - PHP and IBM i
• Announcements - What's New in ZendPHP + ZendHQ
• Blog - Db2 PHP: How to Use Db2 Services for PHP Apps on IBM i
• Blog - Exploring ZendHQ for IBM i
• Blog - IBM i Modernization: Modernizing Smartly and On a Budget