Spludlow Web Header

MySQL - Basics


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

Server Install Notes

·         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.

Client Notes

·         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.


Spludlow Web Footer