Table of Contents
This tutorial provides a quick hands-on introduction to using MySQL Workbench for beginners. If you have used MySQL Workbench before you can safely skip this tutorial.
To complete this tutorial you will need to have a locally installed MySQL Server. If you only have access to a remote MySQL server you will need to enter appropriate connection parameters when required. This tutorial requires MySQL Workbench version 5.2.16 or above. You also need a basic understanding of MySQL concepts. This tutorial demonstrates the procedures on Microsoft Windows, they are, however, the same for all supported platforms.
In this section you will see how you can use MySQL Workbench to connect to a server in order to carry out administrative functions, such as starting and stopping the server.
Launch MySQL Workbench. You will be presented with the Home screen:
In order to administer your MySQL Server you need to first create a Server Instance. This contains information about the target server, including how to connect to it. From the Home screen of MySQL Workbench, click New Server Instance. The Create New Server Instance Profile wizard will be displayed.
In this tutorial we will connect to a locally installed server, so click
.Next you will set up a connection, or select an existing connection to use to connect to the server. Assuming you have not already created a connection, you can use the default values here, although if your MySQL Server has a password set for root, you can set it here by clicking on Store in Vault. This allows you to connect to the server without needing to enter a password each time. It is also possible to use another account to connect to the server by setting the username and password here, if required.
You can now click
.The connection will now be tested. You should see that the connection was successful. If not click
and check that you have entered the information required.If everything tested correctly, click
.On this screen you will set the operating system and installation type. In this case the installation is Microsoft Windows, and the installation type is MySQL 5.1 x86 Installer Package. Setting these options allows MySQL Workbench to determine location of configuration files, and the correct start up and shut down commands to use for the server.
Once you have set the operating system and installation type, click
.The wizard will now check that it is able to access the start up and shut down commands, and access the MySQL Server configuration file.
Check that everything is in order and then click
.You now have a chance to review the configuration settings so far. The information displayed varies slightly depending on platform, connection method and installation type:
Click
.Finally you can give the server instance a suitable name. This will be used to select this particular instance from a list of available instances.
Having set the desired name, you can click
to complete the server instance creation process.You will now be returned to the Home screen. You will see the new server instance you created, along with the new connection you created as part of the above procedure.
You are now ready to test your new server instance.
From the Home screen, double-click the Server Instance you created. The Administrator will open on the Startup configuration page.
Click the
button. The message window will show that the server has stopped.Click the
button to resume the server. The message window will confirm that the server is running.You have now seen how to create a server instance to allow you to manage a MySQL server.
For further information see Chapter 8, Server Administration.
In this section you will learn how to create a new database model, create a table, create an EER Diagram of your model, and then forward engineer your model to the live database server.
Start MySQL Workbench. On the Home screen select Create
new EER Model. A model can contain multiple
schemata. Note that when you create a new model, it contains
the mydb
schema by default. You can change
the name of this schema to serve your own purposes, or simply
delete it.
On the Physical Schemata toolbar, click the button dvd_collection”, by typing into the field called Name. Ensure that this change is reflected on the Physical Schemata tab. Now you are ready to add a table to your schema. If at this stage you receive a message dialog asking to rename all schema occurrences, you can click to apply your name change.
to add a new schema. This will create a new schema and display a tabsheet for the schema. In the tabsheet, change the name of the schema to “In the Physical Schemata section double-click Add Table.
Double-click table1 to launch the table editor (you may not have to do this as the table editor will automatically load at this point if you are using later versions of MySQL Workbench). In the table editor, change the name of the table to “movies” and press Enter.The table editor will then switch from the Table tab to the Columns tab, to allow you to enter details of your table columns.
Change the name of the first column to
“movie_id”. Select a data type of
INT
. You will then make this column have
the following properties: primary key, not null,
autoincrement. To do this click the PK,
NN, and AI
checkboxes.
Add two further columns:
Column Name | Data Type | Column Properties |
---|---|---|
movie_title | VARCHAR(45) | NN |
release_date | DATE (YYYY-MM-DD) | None. |
Now you can obtain a visual representation of this schema so far. From the main menu select
, . The EER Diagram will be created and displayed.Now, in the table editor, change the name of the column “movie_title” to “title”. Note that the EER Diagram is automatically updated to reflect this change.
At this point you can save your model. Click the main toolbar
button Home_Media”. The Home_Media model may contain
further schemata in addition to
dvd_collection
, such as
cd_collection
. Click
to save the model.
You can synchronize your model with the live database server. First you need to tell MySQL Workbench how to connect to the live server. From the main menu select
, .In the Manage DB Connections dialog click .
Enter “Big Iron Server” for the connection name. This allows us to identify which server this connection corresponds to, although it is possible to create multiple connections to the same server.
Enter the username for the account you will use to connect to the server.
Click on the
button and enter the password for the username you entered in the previous step. You can optionally ignore this step, and you will be prompted for this password whenever MySQL Workbench connects to the server.Click
to test your connection parameters. If everything is OK at this point you can click .You are now ready to forward engineer your model to the live server. From the main menu select Forward Engineer to Database wizard will be displayed.
, . TheThe first page of the wizard is the Catalog Validation page. Click the Next to continue.
button to validate the Catalog. If everything is in order the wizard will report that validaton finished successfully. ClickThe Options page of the wizard shows various advanced options. For this tutorial you can ignore these and simply click Next.
On the next page you can select the object you want to export to the live server. In this case we only have a table, so no other objects need to be selected. Click
.The next screen, Review SQL Script, displays the script that will be run on the live server to create your schema. Review the script to make sure that you understand the operations that will be carried out. Click
.Select the connection you created earlier, “Big Iron Server”. Click . Check the messages for any erros, and then click to exit the wizard.
Ensure that the script ran without error on the server and
then click SHOW DATABASES;
and identify your schema.
Enter USE dvd_collection;
, to select your
schema. Now enter SHOW TABLES;
. Enter
SELECT * FROM movies;
, this will return the
empty set as you have not yet entered any data into your
database. Note that it is possible to use MySQL Workbench to carry
out such checks, and you will see how to do this later, but
the MySQL Command Line Client has been used here as you have
probably used this previously.
Ensure that your model is saved. Click Save Model to Current File on the main toolbar.
In the previous section you created a model, schema, and table. You also forward engineered your model to the live server. In this section you will see how you can use MySQL Workbench to add data into your database on the live server.
On the Home screen click the link Edit Table Data in the SQL Development area of the Workspace. This launches Edit Table Data wizard.
In the wizard select the “Big Iron Server” connection from the stored connection drop down listbox. Click .
Select the schema, dvd_collection
. Select
the table to edit, movies
. Click
.
You will see a data grid. This is where you can enter the data
for your database. Remember that the
movie_id
was set to be autoincrement, so
you do not need to enter values directly for this column. In
the data grid enter the following movie information:
title | release_date |
---|---|
Gone with the Wind | 1939-04-17 |
The Hound of the Baskervilles | 1939-03-31 |
The Matrix | 1999-06-11 |
Above the Law | 1988-04-08 |
Note: do not modify any values in the
movie_id
column.
Now click the
button in the toolbar located in the bottom right corner. A list of SQL statements will be displayed. Confirm that you understand the operations to be carried out. Click to apply these changes to the live server.Confirm that the script was executed correctly and then click
.View the data grid again and observe that the autoincrement values have been generated.
Now you will check that the data really has been applied to
the live server. Launch the MySQL Command Line Client. Enter
SELECT * FROM movies;
to see the data just
entered.
You can also carry out a similar check from within MySQL Workbench. Click on the Home screen tab.
Click the link Open Connection to start Querying in the SQL Development section of the Workspace. This will launch the Connect to Database dialog. Select “Big Iron Server” from the drop down listbox. Click .
A new SQL Editor tab will be displayed. In the SQL Statements area enter the following code:
USE dvd_collection; SELECT * FROM movies;
Now click the
toolbar button. This resembles a small lightning bolt. The SQL Editor will display a new Result tab contain the result of executing the SQL statements.In this section of the tutorial you have learnt how to add data to your database, and also how to execute SQL statements using MySQL Workbench.