Manage your databases with Toad

I am constantly deploying web-based applications that require the use of MySQL databases; typically I use MySQL Workbench for this task. If you aren’t fond of that application, there are a number of other options that can help you manage your databases.

One of those options is Toad, which has client tools that can help you manage MySQL, Oracle, Microsoft SQL, and IBM DB2. When connected to one of those databases, Toad allows you to do various things, which include:

• Create databases
• Version control
• Macro recorder
• Code snippet editor
• Security manager
• SQL Editor
• Multi-tabbed schema browser
• DB Extract, Compare-and-search utility
• Import/Export utility
• Local or remote management capabilities

The Toad products are free and are only available for the Windows platform. I will demonstrate the use of Toad for MySQL databases. Here’s what you’ll need for this tutorial on Toad for MySQL:

• A Windows machine
• Access to a MySQL database server (it can be local or remote)
• A database account on a server that will allow access (this can be root or any other user with correct privileges)

Installation

1. Download the installer (an EULA agreement is required).
2. Once the file is downloaded, double-click the installer.
3. Walk through the installation wizard.

Connecting to the server

You connect to your database server from the Toad main window (Figure A).
Figure A

From this window you have quick access to the Toad for MySQL community. (Click the image to enlarge.)

To create a new connection, go to File | New | Connection. When the Create New Connection window opens (Figure B), enter the details for the connection.
Figure B

Create and save your connection to the MySQL server. (Click the image to enlarge.)

From the Connection Type drop-down, you can select from these types: TCP, Named Pipe, Memory, SSL, SSH, HTTP Proxy, HTTP Tunnel; the TCP connection will often work just fine. If you want to save this connection so you don’t have to re-enter the details each time you want to connect, make sure to fill out the details at the bottom of the window. The categories you can choose from are: None, Development, Production, Test. You can also create your own categories by selecting New Category from the drop-down.

After you fill out all of the required information, click the Save button. This new connection will be listed in the Connection Manager (Figure C).
Figure C

To open one of the connections, double-click on the connection desired. (Click the image to enlarge.)

You can set up a connection to specific databases or the entire set of schema on the server. If you connect to a specific database, you must make sure the user connecting has the right privileges for that database.

Upon correct authentication to the database server, the Object Explorer will open along with a new tab for the connected database (Figure D).
Figure D

You can have as many connections open as you like — each one will show up in its own tab. (Click the image to enlarge.)

To connect to a specific database, click the Schema drop-down and select the database. You can now start to work with that database.

Creating a database

1. Open Toad.
2. Connect to your server.
3. Go to File | New | Database.
4. Give the database a name.
5. Click OK.

The database is now on the server.

Create a table on a database

You must select the database you want to work with from the Schema drop-down, and then follow these steps:

1. Go to File | New | Table.
2. Give the table a name in the Create Table window (Figure E).
3. Configure the table as needed for the database.
4. Click the Columns section.
5. Give the First column a name, a datatype, a collation, etc.
6. Continue adding columns until the table is complete.
7. Click OK.

Figure E

If don’t configure something correctly within the table, you can go back and alter the table. (Click the image to enlarge.)

Once the table is created, it will show up in the database Columns view (Figure F).
Figure F

Full access to the scheme and all its constituent pieces can be had here. (Click the image to enlarge.)

You should continue creating your tables until the database is complete. Once it’s complete, the database is ready to use.