In this episode of our WSO2 tutorial: MySQL Community Edition is one of the most used databasemanagement systems because it is a simple to use, open source database. Although WSO2 products come preconfigured with the embedded H2 database, in the DBSCRIPTS directory of any of the WSO2 products you will find the scripts to exchange H2 for MySQL (as well as other databases like Oracle, MSSQL et cetera). H2 is a database that is suited for test purposes like evaluating a WSO2 product but for anything beyond that is should be exchanged for a ‘real’ RDBMS.
In fact, WSO2 should work with any JDBC enabled database. If there are no scripts available for your database in the directory you can alter one of the scripts available, just see which one most resembles your database.
MariaDB
Recently, another database management system called MariaDB is becoming popular. MariaDB is a fork of MySQL. With fork we mean a copy of source code was taken from MyDQL and independent development started resulting in a competitive RDBMS called MariaDB. This was actually done by the original developers of MySQL.
According to Wikipedia, the reason for the fork was the mistrust by the original developers in Oracle’s purchase of MySQL (as part of the acquisition of SUN). This all happened in 2009 so it is already a long time ago.
Recently, Suse announced that MariaDB is the default RDBMS for Suse Enterprise Linux, underlining again he popularity of MariaDB
Starting from the same codebase, it is not surprising that the two look alike. In fact, MariaDB aims to support all MySQL community functions as well as additional features not offered by MySQL. There is actually a short whitepaper on this available on the website of MariaDB. The gist of it? MariaDB is an enhanced drop in binary compatible replacement for MySQL. So everything should just work with MariaDB like MySQL.
Why MariaDB?
Since MySQl and MariaDB are almost the same the choice for one or the other is quite personal. For myself, the choice for MariaDB is rationalized by the fact that it is often part of standard Linux packages. We do WSO2 training using Suse SLES 12 virtual machines or instances and it makes life easier when I can use a database that part of the standard build.
Compatibility
WSO2 has a page that shows the WSO2 products and the DBMS that they are tested against. In general, all WSO2 products are generally compatible with the products for which there are scripts in the DBSCRIPTS directory like MySQL, MS SQL Server, Oracle, H2, DB2, Derby. Looking at the aforementioned page we see that for instance ESB 4.9.0, Governance Registry 5.0.0 and API Manager 1.9.0, all part of the WSO2 Integration Platform, are compatible with the MariaDB 10.0.20 version. From our own experience as Yenlo with API Manager 1.10.0 (the most recent version of the API Manager) we know that this version is also compatible. In case of a key length violation when generating the required tables use “character set latin1” when creating the database. This is also something that occurs in MySQL.
Exchanging H2 for MariaDB
Basically, the procedure is the same for the installation of MySQL. Since MariaDB is installed on the same port (3306) the settings for an installation on your own pc (local host is exactly the same.
We will show it by making the change in the ESB. First, stop the ESB if it is running.
Create the database
The first thing you need is to create a database before you can store the data in.
Go to MariaDB command line interface and type the following commands:
create database regdb character set latin1;GRANT ALL ON regdb.* TO regadmin@localhost IDENTIFIED BY "regadmin";FLUSH PRIVILEGES;
Each command is followed by Enter.
This creates the database, gives authorization to the regadmin user and makes sure privileges are reloaded.
Finally, create the tables needed by running the MySQL database script that can be found in the DBSCRIPTS directory. Type the following command in MariaDB:
Source [PRODUCT_HOME]/dbscripts/mysql.sql
You can now exit the MariaDB command line interface. Alternatively, and depending on the WSO2 product you are using, you can use the -Dsetup option when starting the product (i.e. wso2server.sh -Dsetup or ./wso2server.sh -Dsetup). This will generate the tables for you. To see if the product you are using supports -Dsetup, see the online documentation (docs.wso2.com).
Change the masterdatasources file
You need to change the [PRODUCT_HOME]/repository/conf/datasources/m aster-datasources.xml to indicate that you want to use MariaDB rather than H2.
The strange thing is that actually you use all of the settings for MySQL rather than MariaDB.
It now becomes:
Place the MySQL Driver
It sounds strange but we still must use the MySQL JDBC driver. Download the MySQL Java connector JAR file from MySQL.com , and copy it to the [PRODUCT_HOME]/repository/components/lib/ directory.
You can now start the ESB again and if all goes well you can log in with the standard ‘admin’ / ‘admin’ and will see the following on the startup screen.
This shows that we are using MariaDB but connect using the MySQL driver. All the other settings (apart from the DBMS version) look similar to a MYSQL installation
Special thanks to my colleague Jochen Traunecker, Director of Yenlo.Labs, for his contribution to this blog.
| WSO2TORIALS help you to change, update or improve WSO2 products and are based on our experiences with the products. WSO2TORIALS will guide you step by step with minimal knowledge required. |  |
