Use MySQL 5.5 as MDS database repository in FMW 11g R1 PS5

One of the new features of Fusion Middleware 11G R1 PatchSet 5 (PS5) is the support for MySQL 5.5 as Meta Data Services (MDS) repository. This means you can use MySQL database as a store for ADF personifications or as policies store for your OWSM domain. For MDS you just need a small database which store these configurations. For the SOA infra repository ( not supported on MySQL )  I think the Oracle Database will always be the best choice ( tuning, transactions, RAC ).
This way you add the MySQL database on the same machine as your WebLogic Domain without taking a lot of machine resources, less maintenance or thinking about licensing costs.

In this blogpost I will show you the steps how to create a MDS repository for OWSM and ADF.  

First we start with downloading MySQL 5.5 and install it on a machine. I will use InnoDb as database engine and UTF8 as character set ( this is probably best tested by Oracle).

Add or change the following database settings in the my.cnf of the MySQL database.


innodb_file_per_table
innodb_file_format=Barracuda
innodb_large_prefix
log_bin_trust_function_creators

sql-mode=NO_BACKSLASH_ESCAPES

max_sp_recursion_depth=10

The first 4 settings are required by the RCU installer ( Repository Creation Utility )
The sql-mode is necessary in OWSM else you will get a SQL error ( escape ' \'  on a like ) when you start the WebLogic Domain.
the max_sp_recursion_depth=10 else you will get this error Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine mds_internal_createPackageRecursive

Start the RCU installer and select MySQL Database as database type. I will use root as username cause it needs to create a table ( schema_version_registry)  in the test database  and it wants to create a new database.

MySQL does not support all options but for us, the Metadata Services option is enough. Besides MDS, MySQL is also supports ODI ( Oracle Data Integrator ) and Enterprise Performance Management, I personally don't know these products so I can't test them for you .

RCU creates a dev_mds database and an user dev_mds user.

It will create the following tables

For OWSM, I also created a dev1_mds database with the RCU which I will use in the WebLogic Domain creation ( EM, JRF and OWSM as domain options ) . I used the dev1_mds as database and DEV1_MDS as MySQL username

Now we can start the OWSM WebLogic domain and deploy our protected Web Services.

We can also use it in ADF, for this we need to open the Enterprise Manager application, select your domain and go to Metadata Repositories. Click on Register.

I create a new MySQL user which has access to the test and dev_mds database.


grant all on dev_mds.* to mds@'%' identified by 'welcome';
grant all on test.* to mds@'%' identified by 'welcome';

the test database contains the RCU schema_version_registry table which contains all the entries.

use mds as username and test as service name.

Now you can register  you own MDS database repository.

That's all.