What is MySQL?
MySQL is an open source relational database.
MySQL is cross-platform which means it runs on several different platforms like Windows, Linux, Mac OS, etc.
Why use MySQL?
There are a number of relational database management systems on the market.
Examples of relational databases include Microsoft SQL Server, Microsoft Access, Oracle, DB2, etc.
One might ask why we would choose MySQL over other database management systems.
The answer to this question depends on a number of factors.
Let's take a look at the strengths of MySQL compared to more relational databases like SQL Server −
- MySQL supports multiple storage engines, each with its own specifications, while other systems such as SQL Server support only one storage engine. To appreciate this statement, let's look at the two storage engines supported by MySQL.
- InnoDB: is the default storage engine provided with MySQL since version 5.5. InnoDB supports foreign keys for referential integrity and also supports ACID standard transactions.
- MyISAM: This was the default storage engine for MySQL prior to version 5.5. MyISAM does not support transactions. Its advantages over InnoDB include simplicity and high performance.
- MySQL has high performance compared to other relationship database systems. This is due to the simplicity of design and support for multiple storages.
- Cost-effective, it is relatively cheaper in terms of cost compared to other relational databases. In fact, the community version is free. The commercial version has a license fee that is also cost effective compared to the licensing fees for products such as Microsoft SQL Server.
- Cross-Platform − MySQL runs on many platforms, which means it can be deployed on most machines. Other systems such as MS SQL Server only run on the Windows platform.
To interact with MySQL, you will need a server access tool that can interact with the MySQL server. MySQL supports multiple user connections.
What is MySQL Workbench?
MySQL WORKBENCH is a visual design and database modeling tool for the MySQL server relational database . It facilitates the creation of new physical data models and the modification of existing MySQL databases with reverse/forward engineering and change management features. The goal of MySQL Workbench is to provide an interface for easier and more structured work with databases.
Getting Started MySQL Workbench - Modeling and Design Tool
- Models are at the heart of the most robust and high-performance databases. MySQLworkbench has tools that allow developers and DBAs to visually create physical database design models that can be easily translated into MySQL databases using forward design.
- MySQL Workbench supports creating multiple models in the same environment.
- It supports all the objects like tables, views, stored procedures, triggers, etc. that make up a database.
- MySQL Workbench has a built-in model validation utility that reports any problems it might find to data models.
- It also allows various modeling notations and can be extended using the LUA scripting language.
The figure below shows the simulation window for MySQLWorkbench.
MySQL Workbench - SQL development tool
Structured Query Language (SQL) allows us to manipulate our relational databases. SQL is at the heart of all relational databases.
- MySQLworkbench has a built-in visual SQL editor.
- The Visual SQL Editor allows developers to create, edit, and run queries against MySQL server databases. It has utilities for viewing data and exporting it.
- Its syntax highlighting helps developers easily write and debug SQL statements.
- You can run multiple queries and the results will automatically appear in different tabs.
- Requests are also saved in the history panel for later search and launch.
The figure below shows the SQL development window for MySQL Workbench.
MySQL Workbench - Administration Tool
Server administration plays a critical role in protecting company data. The main issues regarding server administration are user management, server configuration, server logs, and more. MySQL Workbench has the following features that make MySQL server administration process easier;
- User Administration is a visual user management utility that allows database administrators to easily add new and remove existing users as needed, grant and remove privileges, and view user profiles.
- Server Configuration - Provides advanced server configuration and fine-tuning for optimal performance.
- Database Backup and Restore is a visual tool to export/import MySQL dump files. MySQL dump files contain SQL scripts for creating databases, tables, views, stored procedures, and inserting data.
- Server logs is a visual tool for viewing MySQL server logs. The logs include error logs, binary logs, and InnodDB logs. These logs are useful when performing diagnostics on the server. The figure below shows the simulation window for MySQL Workbench.
The figure below shows the admin panel for MySQL Workbench.
Install MySQL Workbench Guide (for Windows)
It's a two step process
1) Install MySQL Community Server
2) Install MySQL Workbench . - You can install the working environment using a zip file or an MSI installer (recommended)
Note. You will need administrator or power user privileges to install.
Once you finish above installation, you need to set up MySQL Workbench as shown below −
1. The first step is to launch MySQL Workbench. What you see is called Home Window
2. Next, you need to create a MySQL Server connection that contains detailed information about the target database server, including how to connect to it. Click "+" in the main window of MySQL Workbench. The "Setting up a new connection" window will open . witch
3. As a beginner, you can create a connection for a locally installed server. Click the Configure Server Management button on the New Connection Setup window to check the MySQL server setup.
4. A new window will open named Configure Local Management . Click the Next button to continue.
5. Next, the wizard will check the database connections. If the test fails, go back and fix your database connection settings. It will then open a popup asking for your root password to test your connection to the local mysql server instance. The password is the one you set during MySQL Workbench installation. Enter your password and click OK
6. Next, the wizard will check the database connections. If the test fails, go back and fix your database connection settings. Otherwise, if all tests pass successfully, click Next to continue.
7. This will open a new wizard about Local Service Management - it allows you to switch between multiple mysql servers installed on the same machine. As a beginner, you can bypass this and click Next to continue.
8. The wizard will then check the ability to access the MySQL server configuration file and check the start/stop commands.
9. Next, you can view the current configurations. After reviewing the configurations, click Finish to complete the server setup.
10. The next step is to set up a connection that can be used to connect to the server. If you haven't created a connection yet, you can use these default values. Click on Test Connection  after entering the connection name .
11. A new dialog will open asking for a password for the root user / selected user. If your MySQL root user has a password, you can enter it using the Store in Vault feature. Click OK.
If the password entered for the user is correct, the following screen will appear. Click on both OK buttons and everything will be fine.
The new instance is displayed on the main page
Double click and start querying.
- MySQL is an open source cross-platform relational database.
- MySQL supports several storage engines that greatly improve server performance customization and flexibility. Prior to version 5.5, the default storage engine was MyISAM, which did not support transactions since version 5.5; The default storage engine is InnoDB, which supports transactions and foreign keys.
- The MySQL server can be administered with a range of mysql server access tools, which include both commercial and open source products. Popular examples include;
- phpMyAdmin is a cross-platform open source web tool for server access
- SQLYog - focused on the Windows platform, a desktop tool for accessing commercial servers
- MySQL Workbench is an open source cross-platform tool for server access.
- MySQL Workbench is an integrated development environment for the MySQL server. It has utilities for database modeling and design, SQL development, and server administration.