MariaDBIf your application requires a current version of MySQL or MariaDB on IBM i, and you need to migrate from ZendDBi or another older MySQL, there is a great option. As we explained in our blog post “Welcome back, MariaDb,” IBM now provides a MariaDB distribution in a standard RPM package. After you install this newer MariaDB, you can migrate your MySQL data to it. This installation guide has been updated with our current best practices and is optimized for use with CommunityPlus+ PHP.

Installing MariaDB

This part of the documentation is based on notes from Richard Schoen.

Before you begin, make sure you have installed the prerequsites for open source packages.

Install the MariaDB packages

Install the mariadb and mariadb-server packages. You can do this from Access Client Solutions (ACS ) or by running yum manually like so:

yum install mariadb mariadb-server

Initialize MariaDB

From a PASE shell (i.e. from CALL QP2TERM or ssh), run the following command:

/QOpenSys/pkgs/bin/mysql_install_db

This will create the initial database and housekeeping information.

Starting and stopping the server

While you can start and stop the server manually like so:

# Start
/QOpenSys/pkgs/bin/mysqld_safe --datadir=/QOpenSys/var/lib/mariadb/data
# Stop
/QOpenSys/pkgs/bin/mysqladmin --no-defaults --user=root --password=yourpassword shutdown
# (...or send a termination signal to the daemon)

…it won’t run in the background; the server takes control over your terminal. (If this happens, you can use Control-C from SSH or SysRq+2 from 5250 to end it gracefully.) What’s more, it will run under your own user profile.

For a quick way to manage MariaDB more professionally, you can submit batch jobs. For example:

# Start
SBMJOB CMD(QSH CMD('/QOpenSys/pkgs/bin/mysqld_safe --datadir=/QOpenSys/var/lib/mariadb/data')) JOB(STRMARIADB) JOBQ(QSYSNOMAX) JOBMSGQFL(*WRAP)
# Stop
SBMJOB CMD(QSH CMD('/QOpenSys/pkgs/bin/mysqladmin --no-defaults --user=root --password=YourPassword shutdown') JOB(ENDMARIADB) JOBQ(QSYSNOMAX) JOBMSGQFL(*WRAP)

Since these are normal Unix shell commands inside the CMD parameter, you can tweak these batch jobs; for example:

  • Change the user via the USER parameter of SBMJOB
  • Add any needed environment variables (i.e. PATH=/QOpenSys/pkgs/bin:$PATH /QOpenSys/pkgs/bin/mysqld_safe [...])
  • Redirect the standard output/error to files as needed (i.e. /QOpenSys/pkgs/bin/mysqld_safe [...] > /out.log 2> /err.log)

Change the default MySQL root password

Root is the MySQL equivalent of QSECOFR. For security, it’s important to set a password for the root user. From a PASE shell (substituting the password as appropriate):

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

For your application’s use, you should create new MariaDB users; you should never use root for your application database connection.

(Optional) Backup and restore from an old MySQL

If you have data from an existing ZendDBi installation, it can be migrated.

You first want to export the old database using mysqldump. This will dump out the database into SQL DDL/DML. You’ll probably want to use the database’s root account to run this command, for it has permission to export everything in the database.

We’ll dump all databases, since that’s what you likely want when migrating or backing up. (If you want to only dump specific databases, use --databases foo bar xyzzy .... Other options are more contextually dependent and out of scope for this article.) In addition, it may make sense to use the older version of the mysqldump command, so we’ll explicitly specify its path. (For example, the path for some ZendDBi versions is /usr/local/mysql/bin/.) Run the following, adjusting as needed for your old MySQL setup :

$ /path/to/bin/mysqldump -u root -pPASSWORD --all-databases > /path/to/dump.sql

Since mysqldump outputs the SQL to standard output, we’ll redirect it to a file. The file will also function as a backup of your database; it might be good to get in the habit of running it on your new database, too!

Once the server is set up, we can restore any old database dump. Since the dump is just SQL, we can easily restore it using the mysql command, which takes in SQL.

We’ll run the following, adjusting the password needed for your MariaDB setup:

$ # We'll specify the absolute path to make it obvious that this is new MariaDB
$ /QOpenSys/pkgs/bin/mysql -u root -pPASSWORD < /path/to/dump.sql

Note that IBM MariaDB database names are case sensitive, unlike ZendDBi MySQL, because QOpenSys is a case-sensitive file system. If needed, you can change the casing of directives in the dump file so that database and table names are consistent.

The contents of your old database should now be available.

Directories and files of MariaDB

MariaDB executables and support files are installed into the standard open source package directory of /QOpenSys/pkgs. If you have any issues getting it to work (e.g. permissions), you should check these first:

  • /QOpenSys/var/lib/mariadb: Where the databases and error logs are stored.
  • /QOpenSys/var/lib/mysql/mysql.sock: The MariaDB domain socket. The socket file should be accessible by anything needing to talk to MariaDB.

Using MariaDB from PHP

MariaDB can be accessed using the standard MySQL extensions for PHP like mysqli or PDO_mysql.

With the latest version of CommunityPlus+, you don’t need to configure a TCP port or address if MariaDB is running on the same IBM i server as PHP. Instead, you can use MariaDB’s domain socket (a socket file in the IFS), which means MariaDB doesn’t need to listen on a port (there’s no more need for port 3306). The domain socket is used by default when you specify “localhost”.

(Optional) Enabling network access

By default, MariaDB only listens on the domain socket. Compared to setting up an IP address and port, the domain socket is easier (no need to worry about conflicts between versions) and more secure (no need to worry about other systems).

Unless you need to access MariaDB from another system, you don’t need to enable network access as described below.

If you do need to enable access from another system, make a file called tcp.cnf (the exact name doesn’t matter) in /QOpenSys/etc/mariadb/my.cnf.d. (Files in this directory are parsed as part of the configuration. We do it this way, instead of modifying my.cnf directly, to make changes more manageable.) It must be ASCII/UTF-8 text. It should have the contents:

[mysqld]
bind-address = 0.0.0.0
port = 3306

The binding address is set to listen on all interfaces (you could change to a specific IP to listen only on that interface, or 127.0.0.1 for localhost only), and 3306 is the port used. If you have another instance running that is using that port (i.e. ZendDBi), you must change it. Otherwise, MariaDB will fail to start.

Once MariaDB has started, you can verify that it’s listening on the port by checking NETSTAT *CNN from a 5250.

Keep your MariaDB current

Whether you are upgrading from ZendDBi or an older version of MySQL, the new MariaDB distribution from IBM should meet your needs. The instructions on this documentation page reflect improvements to CommunityPlus+ PHP and our experience installing MariaDB and migrating data successfully.

If you have specific needs regarding MySQL or MariaDB, get in touch.