Toad World Blog

Using SSH in Toad Edge 1.1 with MySQL Database

Oct 9, 2017 6:56:00 PM by Deepak Vohra

Introduction

SSH (Secure Shell) is a protocol for secure remote login to a machine. SSH is designed for a client-server model; connecting an SSH Client to an SSH Server. While SSH supports several options for user authentication, the main options are using a public/private key pair, and using a password. After a connection is established the data is transferred in an encrypted form. The non-protected alternatives to SSH include telnet and rlogin. In this tutorial we shall discuss using an SSH connection from Toad Edge 1.1 to a MySQL database. We shall also discuss some of the other Toad Edge 1.1 features not discussed in earlier tutorials; features such as adding table data using Insert Row, exporting table data to a SQL script, and exporting SQL script for creating a table. This tutorial has the following sections.

  • Setting the Environment
  • Creating a Docker Service for MySQL Database
  • Creating an SSH User
  • Configuring a Connection in Toad Edge with MySQL Database
  • Adding SSH and Creating a Connection
  • Creating a Database Table
  • Adding Table Data
  • Exporting SQL Script
  • Exporting Table Data

 

Setting the Environment

We shall use a Docker service for running MySQL database. Any platform that supports SSH login may be used. First, launch an EC2 instance with AMI Container Linux by CoreOS (Stable), which has Docker installed out-of-the-box.  The Security Group used for the EC2 instance should have Inbound/Outbound rules set to allow access from the Public IPv4 address of the machine on which the Toad Edge client is installed.  The EC2 instance is launched using a key pair and is shown in Figure 1. The private key is used along with the Public DNS or the Public IP Address to login to the instance with SSH.

4237.first.jpg-1100x19998

Figure 1. CoreOS EC2 Instance

For Toad Edge to be able to connect to MySQL database on the EC2 instance, the Security Group for the EC2 instance must have Inbound/Outbound rules set to allow traffic from the Public IPv4 address of the machine on which Toad Edge is installed. If the Public IPv4 address of the local machine is not known or not easily found, allow access from Public IPv4 address 0.0.0.0/0, which allows access from all IPv4 address. Click on the Security Group link associated with the EC2 instance as shown in Figure 2.

1565.d2.jpg-1100x19998

Figure 2. Security Group Link

Click on the Inbound tab and add an inbound rule for access from the local machine’s Public IPv address. Or, allow all traffic by setting the Public IPv4 address to access the EC2 instance as 0.0.0.0/0, as shown in Figure 3.

3632.d3.jpg-1100x19998

Figure 3. Inbound Rules

Click on the Outbound tab for the outbound rules as shown in Figure 4. Outbound traffic is allowed to all IPv4 addresses by setting Destination as 0.0.0.0/0.

5707.d44.jpg-1100x19998

Figure 4. Outbound Rules

To SSH login to the CoreOS instance, obtain the Public IPv address from the EC2 management console as shown in Figure 1. With user as core and the private key for the EC2 instance, SSH login to the instance.

ssh -i "coreos.pem" core@54.152.55.112

Next, we shall initialize the Docker Swarm mode. Obtain the Private IP address of the EC2 instance from the Dashboard as shown in Figure 5.

 8422.d5.jpg-1100x19998

Figure 5. Private IP Address

Initialize the Swarm mode using the docker swarm init command.

docker swarm init --advertise-addr 10.0.0.210

The Swarm mode gets initialized and the current node becomes the manager node, as indicated by the output in Figure 6.

6864.d6.jpg-1100x19998

Figure 6. Initializing the Swarm Mode

List the Swarm nodes.

docker node ls

The single node in the Swarm gets listed, as shown in Figure 7. If additional nodes are to be added, start EC2 instances with CoreOS AMI and run the docker swarm join command output when the Swarm is initialized.

4212.d7.jpg-1100x19998

Figure 7. Listing the Nodes in the Swarm

 

Creating a Docker Service for MySQL Database

Next, create a Docker service for MySQL database using the docker service create command with Docker image mysql. Set the number of replicas with the --replicas option, the host port on which to expose the service with --p, the mandatory environment variable MYSQL_ROOT_PASSWORD for the mysql Docker image with --env, and service name with the --name option.

docker service create \
  --replicas 1 \
  -p 3306:3306\
  --env MYSQL_ROOT_PASSWORD='mysql'\
  --name mysql \
     mysql

A String for the service id gets output, as shown in Figure 8.

2654.d8.jpg-1100x19998

Figure 8. Creating a Docker Service for MySQL Database

List the services.

docker service ls

The mysql service gets listed, as shown in Figure 9. While the service is being started the service may be listed with 0/1 replicas.

7444.d9.jpg-1100x19998

Figure 9. Listing the Docker Service mysql

List the service replicas.

docker service ps mysql

The one replica gets listed, as shown in Figure 10.

3225.d10.jpg-1100x19998

Figure 10. Listing Service Replicas

The password for MySQL database and the port on which the MySQL database service is exposed on the host were set with the docker service create command. If the password or the port are not known to a user, list the service details which include the password used for the MySQL database and the port on which the service is exposed on the host.

docker service inspect mysql

The ContainerSpec lists the password, as shown in Figure 11.

7848.d11.jpg-1100x19998

Figure 11. Listing Service Detail

The Ports lists the PublishedPort, as shown in Figure 12.

 

Figure 12. Finding PublishedPort and other Service Detail

 

Creating an SSH User

The default user for a CoreOS instance is “core” and the core user is not associated with a password. To be able to login with SSH protocol from Toad Edge the SSH user must be associated with a password. Create a new user (example, a user called “mysql”) and add the user to the sudo group.

sudo useradd -p "*" -U -m mysql -G sudo

The useradd command does not create a password for the user and only initializes the password field with “*”.  The –m option creates the new user’s home directory.  Subsequently, set the password with the passwd command.

sudo passwd mysql

Specify the password when prompted, as shown in Figure 13.

8015.d13.jpg-1100x19998

Figure 13. Creating User and setting Password

Using the new user, establish an ssh connection to the EC2 instance with the following command, which, unlike the command used earlier, does not specify the private key for the EC2 instance.

ssh mysql@54.152.55.112

Specify the password at the Password prompt, and the SSH connection gets established, as shown in Figure 14.

7457.d14.jpg-1100x19998

Figure 14. Connecting to EC2 Instance with SSH

We shall use the user we created to create an SSH connection in Toad Edge next.

 

Configuring a Connection in Toad Edge with MySQL Database

To create a new connection, select Connect>New Connection, as shown in Figure 15.

4705.d15.jpg-1100x19998

Figure 15. Connect>New Connection

The New MySQL Connection wizard gets started. We need to configure a connection with the MySQL database on the EC2 instance. For Host specify the Public DNS of the EC2 instance. Obtain the Public DNS from the EC2 Dashboard, as shown in Figure 16.

3247.d16.jpg-1100x19998

Figure 16. Public DNS

Specify the Host as the Public DNS as shown in Figure 17. Specify Port as 3306. Specify Database as mysql. For Authentication specify User as root and Password as the password configured with the MYSQL_ROOT_PASSWORD env variable in the docker service create command. By default, the Options to enable AutoCommit and restore previous connections after login are selected. Click on Test Connection to test the connection, as shown in Figure 17.

0676.d17.jpg-1100x19998

Figure 17. Configuring and Testing the Connection

If the connection gets established the message Connection is OK gets output, as shown in Figure 18.

 8037.d18.jpg-1100x19998

Figure 18. Connection is OK

The connection should not be finalized (created) with OK yet, as we shall be adding SSH to the connection next.

 

Adding SSH and Creating a Connection

To add SSH to the connection so that the connection will be secure, click on the SSH tab as shown in Figure 19.

1104.d19.jpg-1100x19998

Figure 19. SSH Tab

Click in the Enable SSH checkbox as shown in Figure 20.

 4718.d20.jpg-1100x19998

Figure 20. Enabling SSH

Specify User as the SSH user mysql created earlier. Specify Password as the password set on the user. Specify Host as the Public DNS of the EC2 instance. The Port is set to 22 by default and the Timeout is 10000 ms. Click on Test Connection as shown in Figure 21.

0508.d21.jpg-1100x19998

Figure 21. Test Connection

If the message is Connection is OK as shown in Figure 22 click on OK.

5488.d22.jpg-1100x19998

Figure 22. Configuring SSH

A new connection with SSH enabled gets created and gets added to the Connections view, as shown in Figure 23.

7455.d23.jpg-1100x19998

Figure 23. New Connection Added

 

Creating a Database Table

To create a database table, right-click on the default database, which is mysql, as shown by the blue (ish) color in the ObjectExplorer>Databases. This is shown in Figure 24.

5126.d24.jpg-1100x19998

Figure 24. New Table

In the New Table wizard specify a Table Name (wlslog) as shown in Figure 25.

8831.d25.jpg-1100x19998

Figure 25. New Table

Next add the table columns logid, time_stamp, category, type, servername, code, and msg. In Table Columns click on New to add a column as shown in Figure 26.

2063.d26.jpg-1100x19998

Figure 26. Table Columns>Add

In the New Column wizard specify a column Name (logid) and select the column Data Type as shown in Figure 27. Specify the column Precision (10) and click in Primary Key checkbox to make the logid column as the primary key column. Click on OK.

0412.d27.jpg-1100x19998

Figure 27.  Adding a New Column

The logid column gets added, as shown in Figure 28. To add another column click on New again.

7853.d28.jpg-1100x19998

Figure 28. Column logid Added

In the New Column wizard add the column name for the column and select the Data Type, as shown in Figure 29. Click on OK.

5282.d29.jpg-1100x19998

Figure 29. Adding another Column

Similarly add other columns. The Table Columns are shown in Figure 30. Click on Finish to create the new table.

7357.d30.jpg-1100x19998

Figure 30. Table Columns

A new table wlslog gets created, as shown in Figure 31.

8424.d31.jpg-1100x19998

Figure 31. Table wlslog Created

Initially the new table does not include any table data, as indicated by the Data tab not listing any data in Figure 32.

2148.d32.jpg-1100x19998

Figure 32. The new Table is Empty

 

Adding Table Data

In this section we shall add data to the new table. Several options exist to add data:

-Run DML SQL statements in a SQL Worksheet

-Import Data into the Table

-Use Insert Row

We have discussed the first two options in earlier tutorials. In this tutorial we shall use the third option. Right-click below the columns header row and select Insert Row as shown in Figure 33.

1581.d33.jpg-1100x19998

Figure 33. Insert Row

A row with empty (null value) columns gets added, as shown in Figure 34.

 7838.d34.jpg-1100x19998

Figure 34. An empty row added

Next, we shall add the following data (the first row is the columns header row) to the table.

logid,time_stamp,category,type,servername,code,msg
1,'Apr-8-2014-7:06:16-PM-PDT','Notice','WebLogicServer','AdminServer','BEA-000365','Server state changed to STANDBY'
2,'Apr-8-2014-7:06:21-PM-PDT','Notice','WebLogicServer','AdminServer','BEA-000365','Server state changed to RUNNING'

To add column data, click in the column; as an example, the category column shown in Figure 35. The data may be directly copied into the column or you can click on Define in Text Editor, as shown in Figure 35.

0005.d35.jpg-1100x19998

Figure 35.  Define in Text Editor

In the text editor add the data as shown in Figure 36 and click on OK.

2161.d36.jpg-1100x19998

Figure 36. Adding Column Data in Text Editor

The column data gets added, as shown in Figure 37. Similarly, click on Define in Text Editor for the code column.

5775.d37.jpg-1100x19998

Figure 37. Column Data added for category Column

Specify data in the Text Editor and click on OK as shown in Figure 38.

7851.d38.jpg-1100x19998

Figure 38. Adding Data in Text Editor

The code column data also gets added, as shown in Figure 39. Required columns are indicated by the (required) field text, as shown for the primary key column logid in Figure 39.

0027.d39.jpg-1100x19998

Figure 39. Required Column logid

Click on Define in Editor as shown in Figure 40.

3632.d40.jpg-1100x19998

Figure 40. Define in Editor

Specify the logid in the Editor and click on OK as shown in Figure 41.

5707.d41.jpg-1100x19998

Figure 41. Adding logid in  Editor

The logid gets added, as shown in Figure 42. Add the data in the msg column with Define in Text Editor as shown in Figure 42.

6864.d42.jpg-1100x19998

Figure 42. Adding data to msg Column with Define in Text Editor

In the Text Editor add the data and click on OK as shown in Figure 43.

1588.d43.jpg-1100x19998

Figure 43. Adding Data in Text Editor

Similarly, add data in the other columns in the first row as shown in Figure 44.

2654.real 44.jpg-1100x19998

Figure 44. Adding a Row of Data

To refresh the data, right-click on the row and select Refresh as shown in Figure 45.

 6378.d45.jpg-1100x19998

Figure 45. Refresh

Because Auto Commit is ON by default, the row of data gets committed with Refresh and the one row of data gets fetched, as shown in Figure 46.

 7444.d46.jpg-1100x19998

Figure 46. One row of Data Committed and Fetched

To add the second row of data, right-click below the first row of data and select Insert Row as shown in Figure 47.

0511.d47.jpg-1100x19998

Figure 47. Insert Row

An empty (null value) row gets added, as shown in Figure 48.

7848.d48.jpg-1100x19998

Figure 48. An empty Row Added

Adding data to the second and subsequent rows is simplified by providing the data-assist feature. If the column data is the same as a previous row’s column data the data may be selected from the data-assist drop-down, as shown in Figure 49.

5381.d49.jpg-1100x19998

Figure 49. Selecting Data from Data-Assist Drop-Down

Similarly, add data using data-assist for the code column as shown in Figure 50.

7457.d50.jpg-1100x19998

Figure 50. Adding Data to the Code Column with Data Assist

The logid is a unique field, being the primary key field, and the data assist is not available for it, but column data may be added using an Editor, as shown in Figure 51.

4705.d51.jpg-1100x19998

Figure 51. Adding Logid for 2nd Row in an Editor

If the column value is different, the data assist cannot be used, as for the msg column, and new data must be added either directly in the column or with Define in Text Editor, as shown in Figure 52.

 8037.d52.jpg-1100x19998

Figure 52. Define in Text Editor for a Field with a Different Value

Add field value in the Text Editor and click on OK as shown in Figure 53.

2642.d53.jpg-1100x19998

Figure 53. Adding Data in Text Editor

Similarly, add column values for the other columns. To commit and fetch the data right-click on the row and select Refresh as shown in Figure 54.

 4718.d54.jpg-1100x19998

Figure 54. Refreshing 2nd Row

The second row data gets refreshed, as shown in Figure 55.

6874.d55.jpg-1100x19998

Figure 55. Displaying Table Data

Table data may be refreshed with the refresh button in the table toolbar, as shown in Figure 56.

 2664.d56.jpg-1100x19998

Figure 56. Refresh Button in the Toolbar

The wlslog table view with the data added and the Connections and Object Explorer views is shown in Figure 57.

5488.d57.jpg-1100x19998

Figure 57. The wlslog Table View including Connections and Object Explorer Views

 

Exporting the SQL Script

To export the SQL script (the CREATE TABLE script) right-click on the table in the Object Explorer>Databases>mysql>Tables>wlslog and select Export Script as shown in Figure 58.

7455.d58.jpg-1100x19998

Figure 58. Object Explorer>Databases>mysql>Tables>wlslog >Export Script

In the Export Script wizard select Destination for Exported Script from one of the three options (Worksheet, Clipboard, and Single File) as shown in Figure 59. Select options from the Script Options.  The Script Preview displays the SQL script that shall get exported. Click on Finish to export the SQL script.

5126.d59.jpg-1100x19998

Figure 59. Export Script

The SQL Script gets exported to a Worksheet. If the SQL Script is to be exported to a file, select Destination for Exported Script as Single File and click on the browse button to select an external file as shown in Figure 60.

2555.d60.jpg-1100x19998

Figure 60. Using the Single File Option

Select a file path as shown in Figure 61 and click on Finish as shown in Figure 61.

4621.d61.jpg-1100x19998

Figure 61. Exporting SQL Script to a Single File

If the SQL Script gets exported the Script Export Finished dialog gets displayed, as shown in Figure 62.

0412.d62.jpg-1100x19998

Figure 62. Script Export Finished

The SQL Script exported is listed.

-- ----------------------------------------------------------------
--  TABLE wlslog
-- ----------------------------------------------------------------
 
CREATE TABLE mysql.wlslog
(
   logid         int(10) NOT NULL,
   time_stamp    varchar(200)
                 CHARACTER SET latin1
                 COLLATE latin1_swedish_ci
                 NULL,
   category      varchar(20)
                 CHARACTER SET latin1
                 COLLATE latin1_swedish_ci
                 NULL,
   type          varchar(20)
                 CHARACTER SET latin1
                 COLLATE latin1_swedish_ci
                 NULL,
   servername    varchar(20)
                 CHARACTER SET latin1
                 COLLATE latin1_swedish_ci
                 NULL,
   code          varchar(20)
                 CHARACTER SET latin1
                 COLLATE latin1_swedish_ci
                 NULL,
   msg           varchar(200)
                 CHARACTER SET latin1
                 COLLATE latin1_swedish_ci
                 NULL,
   PRIMARY KEY(logid)
)
ENGINE InnoDB
COLLATE 'latin1_swedish_ci'
ROW_FORMAT DEFAULT;

 

Exporting Table Data

To export table data right-click on the Object Explorer>Databases>mysql>Tables>wlslog>Export Data as shown in Figure 63.

2577.d63.jpg-1100x19998

Figure 63. Object Explorer>Databases>mysql>Tables>wlslog>Export Data

In the Export Data wizard select the table/s to export as shown in Figure 64 and click on Next.

3644.d644.jpg-1100x19998

Figure 64. Selecting Tables to Export

In Destination and Format select one of the export formats from the Export As listing. Select the default format CSV as shown in Figure 65. The Line Terminator is Environmental Default. The Destination for Exported Data has three options: Worksheet, Clipboard and Single File; the Worksheet is the default. Click on Next.

5710.d65.jpg-1100x19998

Figure 65. Selecting Tables to Export

In Excluded Data Types, the data types to exclude in the export may be selected, as shown in Figure 66. By default none of the data types are excluded from the export. Click on Next.

2148.d66.jpg-1100x19998

Figure 66. Excluded Data Types

Next, select columns to include in the export as shown in Figure 67. By default all columns are selected. Click on Next.

4214.d67.jpg-1100x19998

Figure 67. Selecting Columns to Include in Import

In SQL Settings the Data Format Preview is displayed, as shown in Figure 68. Click on Finish.

6371.d68.jpg-1100x19998

Figure 68. SQL Settings

The table data gets exported to a Worksheet as DML statements, as shown in Figure 69.

0005.d69.jpg-1100x19998

Figure 69. Table Data Exported as DML Statements to a Worksheet

If table data is to be exported to a file, designate Destination for Exported Data as Single File, select a file path, and click on Next as shown in Figure 70.

 2161.d70.jpg-1100x19998

Figure 70. Exporting to a Single File

The subsequent procedure is the same (Figure 66, Figure 67, Figure 68) as for exporting to a Worksheet. The Export Data finished dialog gets displayed when the export is complete, as shown in Figure 71. Click on OK.

7851.d71.jpg-1100x19998

Figure 71. Export Data Finished

The data exported to the export.sql file is listed:

INSERT INTO wlslog(logid,
                   time_stamp,
                   category,
                   type,
                   servername,
                   code,
                   msg)
     VALUES (1,
             'Apr-8-2014-7:06:16-PM-PDT',
             'Notice',
             'WebLogicServer',
             'AdminServer',
             'BEA-000365',
             'Server state changed to STANDBY');
 
INSERT INTO wlslog(logid,
                   time_stamp,
                   category,
                   type,
                   servername,
                   code,
                   msg)
     VALUES (2,
             'Apr-8-2014-7:06:21-PM-PDT',
             'Notice',
             'WebLogicServer',
             'AdminServer',
             'BEA-000365',
             'Server state changed to RUNNING');
COMMIT;

 

Conclusion

In this tutorial we discussed some of the other features in Toad Edge not discussed in earlier tutorials, including using SSH to connect to MySQL database, adding table data with Insert Row, exporting SQL Script, and exporting table data. Toad Edge 1.1 is an integrated development environment (IDE) for accessing and managing MySQL database.

Tags: Toad Edge MySQL Development

Deepak Vohra

Written by Deepak Vohra

Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.