Setup MySQL, design your schema (ER model) using SQLWorkbench, forward/reverse engineer from SQLWorkbench to MySQL

Series:

·        Data repository and modeling technique selection

·        Setup MySQL, design your schema (ER model) using SQLWorkbench, forward/reverse engineer from SQLWorkbench to MySQL

·        Forward engineer (generate POJO) from ER model

·        Building and Unit Testing Service Layer with below Persistence Layer

·        Expose Service Layer via RESTful services in Spring + Unit Test with TDD

·        Integrating RESTful API in web llayer with jQuery Mobile

Set up mysql

1.      download the zip files (http://dev.mysql.com/downloads/)

2.      unzip in your dev folder (c:\dev)

3.      run mysqld:

4.      Shutdown mysqld: You can do that by issuing

mysqladmin –u root –p<password> shutdown (there is no space between –p and the actual password.)

5.      Change default root password.

a.      Login to mysql (mysql –u root)

b.      Change password:

Issue the following statements in the mysql client. Replace the password with the password that you want to use.

mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass')

    ->                   WHERE User='root';

mysql> FLUSH PRIVILEGES;

6.      Create a schema and a user to be used by the application

7.      Use my.ini (in Linux my.cnf) for advanced changes (don’t need for local development)

8.      Optionally you can create shortcut to mysqld, mysql, and mysqladmin and place them in your dev folder where other shortcuts are present for convenience.

(I have used the “root” user J and I have used the WorkBench tool to create the DB model. I have used forward engineering to create the database and the tables directly. One could generate DDL and keep in source code for versioning. You could also DCL for better control. However, you hardly need to deal with DCL for local dev.)

Setup MySQL Workbench

1.      Unzip the file

2.      Launch MySQLWorkbench.exe

3.      Optionally you can create a shortcut to the exe and place in your dev folder.

4.      Setup a connection to the localhost:3306 and store userid/password in the vault.

a.      Optionally configure the default schema. You don’t need for localdev.

5.      The screen should look like the one below:

6.     

a.      Ensure that the Workbench sees the mysqld as running.

b.      Note the file and path to error file.

c.      The configuration file is listed wrong…I don’t have anything in the path that’s listed. Everything is under c:\dev.

Commence your ER modeling

Develop DBModel

Forward engineer the model (save DDL for version control in Source Control)

Validate that the tables are created in the DB

mysql –u <user> -p<password>

show databases; (should list the newly created databases)

use <new db>;

show tables; (should display all the tables)