Verify MariaDB – MySQL on IBM i

Verify MariaDB – MySQL on IBM i

Following Part 2 – where we installed IBM i MariaDB (MySQL) – the next burning question is “OK, so it looks like it installed, so how do we check it and use it?”

Me – with a coffee

Now that we have MariaDB – MySQL is on your IBM i Power System, let’s fire it up and see if it works.

Get ready to Setup MariaDB

Create USRPRF(MYSQL)

Let’s create a user profile to *OWN all the MariaDB Objects.

You may already have this user account installed, but if not, run command:

CRTUSRPRF USRPRF(MYSQL) PASSWORD(*NONE) STATUS(*DISABLED) TEXT('MYSQL/MARIADB User')

Now we are going to run the MariaDB setup scripts that come with the package that we just downloaded.

Install/Setup the MariaDB (MySQL) Package

NOTE: I am going to precede all the MariaDB scripts that I reference with the location (/QOpenSys/pkgs/bin/something) If you performed the CD (Change to Directory) command you will not need this, but I am including it for clarity.

From the SSH Terminal screen (QSH), use the commands:

cd /QOpenSys/pkgs/bin

/QOpenSys/pkgs/bin/mysql_install_db --user=mysql

This will put you in the OpenSource packages binaries folder and let you run the MariaDB installer scripts.

If the installer works, it will show you a very friendly message:

Installing MariaDB/MySQL system tables in '/QOpenSys/var/lib/mariadb/data' …
OK

To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following command: 
'/QOpenSys/pkgs/bin/mysql_secure_installation'

which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers.

See the MariaDB Knowledgebase at http://mariadb.com/kb or the MySQL manual for more instructions.

You can start the MariaDB daemon with: 
cd '/QOpenSys/pkgs' ; /QOpenSys/pkgs/bin/mysqld_safe --datadir='/QOpenSys/var/lib/mariadb/data'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/QOpenSys/pkgs/mysql-test' ; perl mysql-test-run.pl

Please report any problems at http://mariadb.org/jira                    
                                                                         
The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:             
http://dev.mysql.com                                                     
                                                                         

Gives you a warm, glowing feeling in the seat of your pants, right?

Is that it?

Shall we try to fire this thing up?

Start MariaDB (MySQL) Server

You can start your MariaDB Server with this SSH:

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

Now, you should see a status message something like this:

220523 14:23:53 mysqld_safe Logging to '/QOpenSys/var/lib/mariadb/data/yoursystemname.err'.
220523 14:23:58 mysqld_safe Starting mysqld daemon with databases from /QOpenSys/var/lib/mariadb/data  

If you look in this logging file you will see all kinds of useful debugging information :

Hopefully you will see a message in the bottom saying something like:

/QOpenSys/pkgs/bin/mysqld: ready for connections.

We can connect?!?!

#HUZZAH

Before we start playing – let’s secure this a little…

Set MariaDB Master password

MariaDB uses this special user called Root, the MariaDB/MySQL equivalent of *SECOFR. So, we need to set the master MariaDB with this SSH:

mysqladmin -u root password YourPassword 

NOTE: obviously, the password “YourPassword” should be whatever password you want to use as your master password in MariaDB

Stop MariaDB (MySQL) Server

This command send an instruction into the MariaDB Server Daemon to stop your MariaDB Server. From your SSH session:

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

Pat yourself on the back.

You have nearly finished with MariaDB – MySQL on IBM i

Now that we have the Database installed and running – let’s try to use it!

Let’s try to use PHP with this MySQL Database on our IBM i Server