Introduction
MySQL is the most commonly used open source relational
database.
Key Ponits
Management software “MySQL Workbench”
Free Product Name: “MySQL Community Server”
Download Server: http://dev.mysql.com/downloads/mysql/
Default Administrator: root
Port: 3306
Full Control Role: DBA (Database
Administrator)
Notes: Object Names in
SQL are quoted using back ticks (`), top left on the keyboard.
By default, case insensitive,
will store all names to lower case
·
You will need an Oracle web account to download
·
Used the standard MSI installer
·
Installed x64 Server & x64 Workbench only
·
Workbench requires - Visual C++ Redistributable Packages for
Visual Studio 2013.
·
Enter a strong root password. Will be used for administration
only.
·
Configured "Server" medium memory usage (Options
self-explanatory)
·
Windows Firewall – Open port for domain and private networks.
·
Add a client login: Workbench->Instance->Server
Menu->Users and privileges->Add Account->Enter Details, make sure: put
“%” in “Limit to hosts matching” to allow remote connections, in the
“Administrative Roles” tab click the “DBA” checkbox to make it a full
administrator. Use a simple password for now.
·
ADO.NET Provider Name: “MySQL Connector/Net”
·
Download from: https://dev.mysql.com/downloads/connector/net/
·
Used the .Net & Mono (Architecture Independent), ZIP Archive
·
Extract the archive and link the DAL implementation to: MySql.Data.dll
·
No other requirements, good lads!
When starting up Workbench and connecting to your instance
(the database server) you are presented with a user friendly UI with pretty
much everything self-explanatory, the panels to the left have handy quick links
to all the basic tasks (you can do them all on the menu also). The “schemas”
window is showing you the individual databases on the server.
To manage logins: Navigator->Management->Users and
privileges.
To restart the database server (after configuration
changes): Navigator->Instance->Start-up/Shutdown.
To manage databases: Navigator->Schemas->Right click
database for tasks
To edit tables: Navigator->Schemas->My
Database->Tables->Right click My Table->Select “Alter Table…”. Table
edit UI with everything you need.
To create a diagram from and existing database:
Workbench->Instance->Database Menu->Reverse Engineer…->Select
Server connection->Select schema(database)->Next->Bingo. You end up
with an “EER Diagram”. You can now save this diagram within its model to a
“.mwb” file there will also be a directory saved out that must be kept with
this file.
To update the diagram: Make changes in the diagram, you can
right click on tables and select edit. Database Menu->Synchronize
Model…->Next->Bingo
To create a new diagram: Create an empty schema then reverse
engineer it as in the create diagram step above (it will be empty). Save the
model. Start work adding tables… Synchronize when you want.