If 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 Seiden 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 configured and started, 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. The logs will be prefixed with the hostname of your system./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”. Read further for details on how MariaDB uses ports by default.
Enabling and disabling external (network) access
By default, MariaDB only listens on the domain socket and TCP port 3306. Compared to setting up an IP address and port, the domain socket is easier to use (no need to worry about conflicts between versions) and more secure (no need to worry about other systems).
If you find you can’t start MariaDB due to another daemon claiming 3306 for itself, you can either disable the TCP socket (recommended) or switch to another port (documented in enabling). (Note that the “mysql” command line tool will connect over the domain socket.)
Disabling external access
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] skip-networking skip-bind-address
This will make MariaDB skip binding the TCP ports and only bind a domain socket.
Enabling external access
Only required if you plan to access MariaDB from another system, or from an external tool such as MySQL Workbench.
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.
Using a loopback (127.0.0.1/::1) IP may be useful if you need the TCP port on the local system only; this isn’t recommended when the domain socket can be used instead, however.
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.