How To Use Mysql Workbench On Windows

next → ← prev

MySQL Workbench (Download and Installation)

MySQL Workbench is a unified visual database designing or graphical user interface tool used for working with database architects, developers, and Database Administrators. It is developed and maintained by Oracle. It provides SQL development, data modeling, data migration, and comprehensive administration tools for server configuration, user assistants, backup, and many more than. We can use this Server Administration for creating new concrete data models, E-R diagrams, and for SQL development (run queries, etc.). Information technology is available for all major operating systems like Mac OS, Windows, and Linux. MySQL Workbench fully supports MySQL Server version v5.6 and higher.

MySQL Workbench covers five main functionalities, which are given below:

SQL Development: This functionality provides the capability that enables you lot to execute SQL queries, create and manage connections to the database Servers with the help of built-in SQL editor.

Data Modelling (Design): This functionality provides the capability that enables you to create models of the database Schema graphically, performs contrary and forwards technology between a Schema and a live database, and edit all aspects of the database using the comprehensive Table editor. The Table editor gives the facilities for editing tables, columns, indexes, views, triggers, partitioning, etc.

Server Assistants: This functionality enables you to administer MySQL Server instances by administering users, inspecting audit information, viewing database health, performing fill-in and recovery, and monitoring the performance of MySQL Server.

Information Migration: This functionality allows you lot to migrate from Microsoft SQL Server, SQLite, Microsoft Access, PostgreSQL, Sybase ASE, SQL Anywhere, and other RDBMS tables, objects, and data to MySQL. Information technology also supports migrating from the previous versions of MySQL to the latest releases.

MySQL Enterprise Supports: This functionality gives the support for Enterprise products such as MySQL firewall, MySQL Enterprise Backup, and MySQL Audit.

MySQL Workbench Editions

MySQL Workbench is mainly available in three editions, which are given below:

1. Community Edition (Open Source, GPL)
2. Standard Edition (Commercial)
3. Enterprise Edition (Commercial)

Community Edition

The Community Edition is an open up-source and freely downloadable version of the most popular database system. It came under the GPL license and is supported by a huge community of developers.

Standard Edition

It is the commercial edition that provides the capability to deliver high-operation and scalable Online Transaction Processing (OLTP) applications. It has made MySQL famous along with industrial-force, functioning, and reliability.

Enterprise Edition

It is the commercial edition that includes a set of advanced features, management tools, and technical support to achieve the highest scalability, security, reliability, and uptime. This edition also reduces the risk, cost, complexity in the development, deployment, and managing MySQL applications.

Let the states empathise it with the following comparison chart.

Functionality	Community Edition	Standard Edition	Enterprise Edition
Visual SQL Development	Yep	Yep	Aye
Visual Database Assistants	Aye	Yes	Yes
Operation Tuning	Yes	Yes	Yes
User and Session Management	Yes	Yeah	Yes
Connection Direction	Yes	Yep	Yes
Object Direction	Yes	Yes	Yes
Data Direction	Aye	Yeah	Yep
Visual Data Modelling	Yes	Yes	Yep
Reverse Technology	Yes	Yes	Yes
Frontward Engineering	Yes	Yes	Yes
Schema Synchronization	Yeah	Yes	Yes
Schema & Model Validation	No	Yes	Yep
DBDoc	No	Yes	Yep
GUI for MySQL Enterprise Fill-in	No	No	Yep
GUI for MySQL Enterprise Audit	No	No	Yes
GUI for MySQL Enterprise Firewall	No	Yes	Yeah
Scripting & Plugins	Yes	Yes	Yes
Database Migration	Yep	Yes	Yes

MySQL Workbench Environs Setup

Here, we are going to acquire how we can download and install MySQL Workbench.

Prerequisites

The post-obit requirements should exist available in your arrangement to piece of work with MySQL Workbench:

• MySQL Server: You can download it from here.
• MySQL Workbench: Yous can download it from here.
• Microsoft .NET Framework iv.5.two
• Microsoft Visual C++ Redistributable for Visual Studio 2019
• RAM 4 GB (6 GB recommended)

Installation

Pace 1: Install the MySQL Customs Server. To install MySQL Server, double click the MySQL installer .exe file. Later on clicking the .exe file, you tin can run across the following screen:

Step 2: Choose the Setup Type and click on the Adjacent button. There are several types available, and you need to choose the advisable option to install MySQL product and features. Hither, nosotros are going to select a Custom option because there is a demand for only MySQL Server and Workbench. If yous need more features, you lot can choose the Total choice.

Step 3: When y'all click on the Next button, information technology will give the following screen.

In this screen, go to the MySQL Server section, click the plus (+) icon. Here, yous need to choose the MySQL Server and add it to the right side box by clicking on the correct arrow symbol.

Now, in the Application section, you lot need to do the aforementioned matter that you had to perform with MySQL Server and click on the Next button. The following screen explains information technology more conspicuously.

Footstep 4: When you click on Next, it volition requite the following screen. This screen checks all the requirements for installing MySQL Server and Workbench. As before long every bit you click on the Execute button, it volition install all requirements automatically. Now, click on the Side by side push.

Footstep v: In this screen, click on the Execute push to download and install the MySQL Server and Workbench.

When the downloading and installation is complete, click on Next button.

Footstep six: In the adjacent screen, we need to configure the MySQL Server and click on Next push.

Step 7: As soon equally yous will click on the Next button, yous can run into the screen below. Here, we take to configure the MySQL Server. At present, choose the Standalone MySQL Server/Classic MySQL Replication option and click on Next.

Step eight: In the next screen, the organisation will ask you to choose the Config Type and other connectivity options. Here, nosotros are going to select the Config Type equally 'Development Machine' and Connectivity every bit TCP/IP, and Port Number is 3306, then click on Next.

Pace 9: Now, select the Authentication Method and click on Side by side.

Footstep x: The adjacent screen will inquire you to choose the account, username, and password. Later filling all the details, click on the Next push button.

Step 11: The adjacent screen will ask you lot to configure the Windows Service. Go on the default setup and click on Next.

Footstep 12: In the next screen, the organisation will ask you lot to utilize the Server Configuration. For this configuration, click on the Execute button.

Pace 13: Once the configuration has completed, y'all will get the screen below. At present, click on the Finish push to continue.

Stride fourteen: In the side by side screen, you can see that the Product Configuration is completed. Keep the default setting and click on the Adjacent-> Terminate button to complete the MySQL package installation.

Footstep 15: Once you lot click the Finish button, the MySQL Workbench should be open on your system, as shown in the screen below.

Step 16: In the to a higher place screen, you need to brand a connection. To practice this, double click the box designated by the red arrow. Here, you lot will get the popup screen that asks to enter the password created earlier during the installation. Afterward entering the password, you are able to connect with the Server.

Step 17: If you practise not have a connection, you can create a new connection. To make a connection, click the plus (+) icon or go to the bill of fare bar -> Database -> Connect to Database, the following screen appears. Now, you need to fill all the details. Hither, you accept to brand sure that the entered password should be the same as y'all have created earlier.

Step eighteen: After entering all the details, click on the Exam Connection to examination the database connectivity. If the connection is successful, you lot will become the post-obit screen. Now, click on OK->OK push button to end the setup.

Step 19: Once yous have finished all the setup, it volition open the MySQL Workbench screen. Now, double click on the newly created connection, y'all will get the post-obit screen where the SQL command can be executed.

MySQL Workbench Administration Tool

The Assistants Tool plays an important part in securing the data of the visitor. Here, we are going to discuss the user'south management, Server configuration, Database backup and restorations, Server logs, and many more than.

User Administration

Information technology is a visual utility that allows for managing the user that relate to an active MySQL Server case. Here, y'all tin can add and manage user accounts, grant and drop privileges, view user-profiles, and expire passwords.

Server Configuration

It allows for advanced configuration of the Server. It provides detailed information about the Server and condition variable, a number of threads, buffer allocation size, fine-tuning for optimal performance, and many more.

Database backup and restorations

It is a visual tool, which is used for importing/exporting MySQL dump files. The dump files incorporate SQL scripts for creating databases, tables, views, and stored procedures.

Server Logs

Information technology displays log information for the MySQL Server by each connection tab. For each connection tab, it includes an boosted tab for the general error logs.

Performance Dashboard

This tab provides the statistical view of the Server performance. Y'all can open it past navigating to the Navigation tab, and under the Operation section, choose Dashboard.

MySQL Workbench Create, Alter, Drib Database

In this section, we are going to see how a database is created, contradistinct, and driblet by using the MySQL Workbench. Permit u.s.a. see in detail ane by i.

Create Database

To create a database, practice the post-obit steps:

1. Open up the MySQL Workbench and logged in using username and password. Then, go to the Navigation tab and click on the Schema bill of fare. Here, you can see all the previously created databases.

2. If you desire to create a new database, right-click under the Schema menu and select Create Schema or click the database icon (cherry-red rectangle), every bit shown in the post-obit screen.

3. The new Schema window screen open up. Enter the new database proper name (for case, mytestdb) and use default Collation. Collation is used to store specific data characters, mainly useful for storing strange languages. At present, click on the Utilise button as shown in the screen below:

4. A new popup window appears, click Utilise->Finish button to create a new database.

v. After the successful cosmos of the database, you tin come across this new database in the Schema card. If you do non come across this, click on the refresh icon into the Schema carte.

half dozen. If you want to see more information almost the database, select mytestdb database, and click on the 'i' icon. The information window displays several options, similar Table, Column, Functions, Users, and many more.

seven. MySQL Workbench does non provide an selection to rename the database proper name, merely nosotros can create, update, and delete the table and data rows from the database.

Driblet Database

1. To delete a database, you demand to cull the database, correct-click on it, and select the Driblet Schema option. The following screen appears:

2. Select Drop At present option in the popup window and the database including table, information rows will be deleted from the database Server.

MySQL Workbench Create, Alter, Drop Table

In this section, we are going to see how a table is created, altered, and drib by using the MySQL Workbench. Let usa see in particular 1 by one.

Create Table

To create a table, do the following steps:

1. Open the MySQL Workbench and logged in using username and password. Then, go to the Navigation tab and click on the Schema menu. Hither, yous can see all the previously created databases. You can also create a new database.

ii. Select the newly created database, double click on it, and you lot will get the sub-card nether the database. The sub-bill of fare nether the database are Tables, Views, Functions, and Stored Procedures, as shown in the below screen.

iii. Select Tables sub-menu, right-click on it and select Create Table option. You tin likewise click on create a new table icon (shown in red rectangle) to create a tabular array.

4. On the new table screen, you need to make full all the details to create a table. Here, nosotros are going to enter the table name (for example, student) and use default collation and engine.

v. Click inside the centre window and fill the column details. Here, the column proper noun contains many attributes such as Primary Primal(PK), Not Null (NN), Unique Index (UI), Binary(B), Unsigned Data type(UN), Automobile Incremental (AI), etc. The following screen explains information technology more clearly. Afterward filling all the details, click on the Use button.

6. As soon every bit y'all click on the Apply button, information technology will open up the SQL argument window. Once again, click on the Apply button to execute the statement and Finish button to save the changes.

7. Now, go to the Schema carte du jour and select the database which contains the newly created table, equally shown in the screen below.

Change Table

To modify a table, do the following steps:

1. Select the table you lot want to modify, click on the 'i' icon, and you volition get the following screen.

2. In the above screen, y'all tin alter the column proper noun, data type, and other tabular array settings.

Drib a Table

1. To delete a table, y'all need to choose the tabular array, correct-click on it, and select the Drop Table option. The following screen appears:

two. Select Drop Now choice in the popup window to delete the table from the database instantly.

MySQL Workbench Insert, Read, Update, Delete Information Rows

In this section, we are going to run across how we can insert, read, update, and delete data rows by using the MySQL Workbench. Permit us see in detail ane by i.

1. Open up the MySQL Workbench and logged in using username and password. So, go to the Navigation tab and click on the Schema menu. Here, nosotros have successfully created a database (mystudentdb) and student tabular array using MySQL Workbench.

2. Select the table, and when we hour a mouse pointer over the pupil tabular array, you tin run across the tabular array icons appears here. Click the table, which volition open a new window where the upper section shows the MySQL statement, and the lower section shows the data rows.

iii. To enter a information row, select the respected column, and insert the data value. Inserting data value in rows is like to the Microsoft Excel Worksheet.

4. After entering the data rows, click on the Apply->Apply>Finish button to salvage the data rows.

5. Similarly, we tin can edit or modify the previously saved data rows. After modification, salve new value, click on the Apply button to salvage changes. Information technology will generate an SQL update argument save the changes to the database.

Delete Row

1. To delete an individual row from the tabular array, you lot demand to select a data row, right-click on the right icon in front of the row and select Delete Row(s) option.

two. Now, click Apply->Employ->Finish button to save changes to the database.

MySQL Workbench Export and Import Database(Table)

In this section, we are going to learn how we can export and import the database or table past using the MySQL Workbench.

Export Databases(Tables)

1. To export databases or tables, go to the Menu bar, click on Server, and select the Data Export option, as shown in the following screen. It will open a new window of data export settings and options.

2. Select any database, and it volition brandish all the corresponding tables nether the selected database. Here, we tin can also select one or multiple database checkboxes to include the database in the Consign file. Similarly, we can select i or multiple tables from the left section of the window.

3. Allow usa select two databases, namely (myproductdb and mystudentdb), including all tables under this database. Now, get to the drop-downwards setting, nosotros tin select 'Dump Construction and Data', 'Dump Data But', and 'Dump Structure Only' option.

• Dump Data and Structure: It volition salve both table structure and information rows.
• Dump Data Only: Information technology volition save simply the inserted rows in the tables.
• Dump Structure Simply: It will save only the table structure, which are database columns and information types defined by us.

4. In the Consign pick, yous tin select the export path of your choice. Here, I will continue the default setting. Besides, there are two radio buttons that are explained below.

• Export to Dump Project Folder: Information technology will relieve all the tables as separate SQL files under one binder. It will be useful when y'all import or restore the export file one by one table.
• Export to Self-Contained File: Information technology will store all the databases and tables in a single SQL file. It is a good option when you want to import all the databases, tables, and information rows using a single SQL file.

five. Click the Starting time Export button, which displays the progress bar and log. Now, open the Document binder in your system to locate the export files.

Import Databases (Tables)

1. To import databases or tables, become to the Carte du jour bar, click on Server, and select the Data Import option. Information technology will open a new window of data import settings and options.

ii. Here, you lot can come across the two radio options to import databases and tables, which are:

• Import from Dump Project Folder
• Import by using Cocky-Contained File

3. We are going to select 'Import from Dump Projection Folder' and click on 'Load Folder Content' to display all the available databases in the projection folder.

iv. Select myproductdb database from the Data Import option and as well select the corresponding product table.

5. Choose the 'Dump Structure and Data' choice and click the Commencement Import button to import the databases and tables from the backup file.

6. Now, become to the Schema->myproductdb->table and refresh information technology to see the currently imported database or tabular array.

---

Next Topic MySQL Create User

← prev next →

Source: https://www.javatpoint.com/mysql-workbench

Posted by: pittmancalown.blogspot.com

Share this post