Setting-up phpMyAdmin to administer MySQL databases from a web console.

In unix environment we usually use mysql command prompt to work with mySQL databases. Sometimes its hard to view large data tables from this manner. phpMyAdmin comes handy in this.

Below are the simple steps how you could setup it in Ubuntu.

1. Do an apt-get install to phpmyadmin

sudo apt-get install phpmyadmin

2. During the installation process it'll configure a web server and a database to run phpMyAdmin.

3. As final steps give user name and password to access the web console.

4. After the installation you can access phpMyAdmin via web console via http://localhost/phpmyadmin

5. Inside the admin console you may find all available mySQL databases listed in the top left corner of the window. Simply click on the required database to access it.

6. You can then browse, query, track, drop tables\data, import\export data using this console.

Creating a MySQL database for WSO2 Carbon 3.x.x products

WSO2 Carbon products have an inbuilt H2 database. Additionally the Carbon framework allows you to switch to the databases such as Oracle, MSSQL,MySQL, Derby, DB2.

You can easily switch between these databases by;
- creating a database schema within the relevant RDBMS
- point the configuration scripts to that db
- copy the drivers
- start the server with -Dsetup
I will describe these steps in a separate post.

This post is about how you can manually configure the database. I will be using MySql in my illustration. And I will be using WSO2 Identity Server 3.0 -alpha releases for this.


Creating MySQL database for WSO2 Carbon
-----------------------------------------------------------------
1. Download a wso2 identity server 3.0 alpha release

2. Navigate to the location where you have the mysql script. i.e. CARBON_HOME/dbscripts

3. Open a command prompt from that location and login to mysql from cmd prompt

mysql -u root -p

4. Create a database. Create user and grant access

create database regdb;
GRANT ALL ON regdb.* TO regadmin@localhost IDENTIFIED BY "regadmin";

6. Run the mysql.sql script. This will configure the database.

use regdb;
source mysql.sql

7. Copy the database driver to CARBON_HOME\repository\components\lib. I used mysql-connector-java-5.1.7-bin.jar, which is the official JDBC driver for MySQL. It can be downloaded from here.



8. Configure usermanager and registry scripts, which reside in CARBON_HOME/repository/conf

registry.xml

<currentDBConfig>mysql-db</currentDBConfig>
        <dbConfig name="mysql-db">
            <url>jdbc:mysql://localhost:3306/regdb</url>
            <userName>regadmin</userName>
            <password>regadmin</password>
            <driverName>com.mysql.jdbc.Driver</driverName>
            <maxActive>80</maxActive>
            <maxWait>6000</maxWait>
            <minIdle>5</minIdle>
       </dbConfig>

user-mgt.xml

<Database>
         <URL>jdbc:mysql://localhost:3306/regdb</URL>
         <UserName>regadmin</UserName>
         <Password>regadmin</Password>
         <Dialect>mysql</Dialect>
         <Driver>com.mysql.jdbc.Driver</Driver>
    </Database>