Before You Begin
Purpose
This tutorial shows you how to connect to your MySQL Cloud Service in several ways:
- Using an SSH client on a UNIX-like platform or Windows to connect to the MySQL Cloud Service mysql command-line client
- Using the mysql command-line client on your local computer to connect remotely to the MySQL Cloud Service instance
- Using MySQL Workbench over Standard TCP/IP
- Using MySQL Workbench over TCP/IP with SSH
Time to Complete
Approximately 1 hourBackground
By default, the MySQL Cloud Service instance includes an installed mysql command-line client. When you created the instance, you provided the public key of a public/private key pair to use for SSH connections. You can use the private key of that pair and an SSH client on your UNIX-like platform or Windows computer to connect to the mysql command-line client of the MySQL Cloud Service instance.
To connect over TCP/IP from mysql command-line client or MySQL Workbench on your local computer, you need to add an access rule to your MySQL Cloud Service instance.
You can connect using MySQL Workbench on your local computer using SSH without adding an access rule to your MySQL Cloud Service instance.
Context
This tutorial includes examples of connecting to MySQL Cloud Service using SSH. To use SSH, you must have a public/private key pair that you provided when you created your instance as described in Getting Started with MySQL Cloud Service.
What Do You Need?
- For all options: an Oracle Cloud account with an Oracle MySQL Cloud Service instance, the public IP address of the instance, the user name for the MySQL Admin user you entered when you created the instance (default: root), and the password you set for that Admin user
- For the SSH client option with either UNIX-like platforms or Windows, or for the MySQL Workbench with SSH option: the private key that goes with the public/private key pair that you used when you created the MySQL Cloud Service instance
- For the SSH client option: an SSH client such as the ssh utility for UNIX-like platforms or the PuTTY program for Windows
- For the mysql command-line client option: a mysql command-line client installed on your local machine
- For either MySQL Workbench option: MySQL Workbench installed on your local machine
Connecting to the MySQL Cloud Service mysql Command-line Client using SSH
For UNIX and UNIX-like platforms, including Linux and Solaris, the ssh utility is an SSH client. For Windows, PuTTy is a freely available SSH client program.
Connecting to the MySQL Cloud Service mysql Command-line Client using the ssh Utility on UNIX-Like Platforms
-
Navigate to the directory on your client computer where the SSH key pair specified during creation of the MySQL Cloud Service instance is stored.
Set the permissions for the key files (for example, privateKey and publickKey) to read and write but not execute for the owner, but no permissions for the group or others:
$ chmod 600 *Key $ ls -l -
Launch the ssh utility with the -i option to specify the private key file (for example, privateKey) and connect to the opc user at the public IP address of the MySQL Cloud Service instance (for example 160.34.11.41). The opc user can perform operations that require root access to the compute node.
$ ssh -i privateKey opc@160.34.11.41The first time you connect to an ssh server, it indicates that the authenticity of the host can't be established, displays the RSA key fingerprint and prompts to store its fingerprint and continue. Enter yes to continue.
The authenticity of host '160.34.11.41 (160.34.11.41)' can't be established. RSA key fingerprint is 44:0d:75:44:90:77:d9:d2:fb:65:86:2a:55:1f:da:95. Are you sure you want to continue connecting (yes/no)? yes -
The instance displays a welcome and status message.
Warning: Permanently added '160.34.11.41' (RSA) to the list of known hosts. ******************************************************************************** * Welcome to * * MySQL Cloud Service * * by * * Oracle * * If you are an unauthorised user please disconnect IMMEDIATELY * ******************************************************************************** ******************************* MySQL Information ****************************** * Status: RUNNING * * Version: 5.7.17 * ******************************************************************************** ************************** Storage Volume Information ************************** * Volume Used Use% Available Size Mounted on * * MySQLlog 6.1G ---- 11% 50G 59G /u01/translog * * bin 2.6G ------- 28% 6.7G 9.8G /u01/bin * * data 122M -- 1% 24G 25G /u01/data * ********************************************************************************The opc user can perform operations that require root access to the compute node. To use MySQL commands, switch to the oracle user with the sudo command.
$ sudo su - oracle -
Start the command-line client installed with the MySQL Cloud Service instance and log in as the MySQL admin user (default: root) and password you set when you created the service.
$ mysql -u root -p Enter password: -
The STATUS command shows the current user as the Admin user at localhost and that you are connected via a socket on Localhost.
mysql> STATUS . . Current user: root@localhost . . Connection: Localhost via UNIX socket . . mysql>
Connecting to the MySQL Cloud Service mysql Command-line Client using the PuTTY Program on Windows
-
Open the PuTTY program. For Host Name (or IP address) enter the public IP address (for example 160.34.11.41) of the MySQL Cloud Service instance. The Port should be 22, and the Connection Type should be SSH.
View Image
Description of this image -
Expand Connection and select Data. For Auto-login username enter opc. The opc user can perform operations that require root access to the compute node.
View Image
Description of this image -
Expand SSH and select Auth. Click Browse to select and open the private key file that matches the public key used when the instance was created.
View Image
Description of this image -
To save the configuration so that you can load it for future sessions, select the Session category. For Saved Sessions enter a name for the connection configuration (for example, MySQLCS) and click Save. Click Open to connect to the MySQL Cloud Service instance.
View Image
Description of this image -
If this is the first time you are connecting, the PuTTY Security Alert prompts you to confirm the public key.
Click Yes to continue.
View Image
Description of this image -
The instance displays a welcome and status message.
Using username "opc". Authenticating with public key "imported-openssh-key" ******************************************************************************** * Welcome to * * MySQL Cloud Service * * by * * Oracle * * If you are an unauthorised user please disconnect IMMEDIATELY * ******************************************************************************** ******************************* MySQL Information ****************************** * Status: RUNNING * * Version: 5.7.17 * ******************************************************************************** ************************** Storage Volume Information ************************** * Volume Used Use% Available Size Mounted on * * MySQLlog 6.1G ---- 11% 50G 59G /u01/translog * * bin 2.6G ------- 28% 6.7G 9.8G /u01/bin * * data 122M -- 1% 24G 25G /u01/data * ********************************************************************************The opc user can perform operations that require root access to the compute node. To use MySQL commands, switch to the oracle user with the sudo command
$ sudo su -oracle -
Start the command-line client installed with the MySQL Cloud Service instance and log in as the MySQL admin user (default: root) and password you set when you created the service.
$ mysql -u root -p Enter password: -
The STATUS command shows the current user as the Admin user at localhost and that you are connected via a socket on Localhost.
mysql> STATUS . . Current user: root@localhost . . Connection: Localhost via UNIX socket . . mysql>
Adding an Access Rule to your MySQL Cloud Service Instance
By default MySQL Cloud instances include an access rule to permit public access to SSH. To provide access over TCP/IP, you must add an access rule.
-
From the MySQL Cloud Service Console menu, select Access Rules.
View Image
Description of this image -
The default access rules permit public access to SSH and local connection to MySQL. To provide access from your client computer, you must add an access rule. Click Create Rule
View Image
Description of this image -
In the Create Access Rule dialog, enter a unique Rule Name and Description. For Source select <custom>.
View Image
Description of this image -
Enter the IP address of your client computer that has the mysql command-line client or MySQL Workbench. For Destination, select mysql_MASTER.
View Image
Description of this image -
For Destination Port, enter 3306. For Protocol, select TCP. Click Create.
View Image
Description of this image Refresh the screen occasionally, and after a few moments, the new access rule appears in the list.
View Image
Description of this image
Connecting Remotely using TCP/IP from the mysql Command-Line Client
-
If you haven't added an access rule for TCP/IP, follow the instructions in Adding an Access Rule to Your MySQL Cloud Service Instance to add an access rule for TCP/IP.
-
On your client computer, at an operating system command prompt, start the mysql command-line client, specifying the hostname as the public IP address of the MySQL Cloud Service instance, and the user as the Admin user you set when you created the instance with the password for that Admin user.
$ mysql -h 160.34.11.41 -u root -p Enter password: -
Enter a STATUS command.
The output of the STATUS command shows that the current user is root@myIPaddress and the connection is to the public IP address of the MySQL Cloud Service instance via TCP/IP.
mysql> STATUS . . Current user: root@myIPaddress . . Connection: 160.34.11.41 via TCP/IP . . mysql>
Connecting remotely using TCP/IP from MySQL Workbench
MySQL Workbench can connect to a remote instance via standard TCP/IP or SSH.
Connecting Remotely from MySQL Workbench using Standard TCP/IP
-
If you haven't added an access rule for TCP/IP, follow the instructions in Adding an Access Rule to Your MySQL Cloud Service Instance to add an access rule for TCP/IP.
-
On your local client computer, in MySQL Workbench, set up a new connection.
In the Setup New Connection dialog, enter a Connection Name. For the Hostname, enter the public IP address of the instance (for example, 160.34.11.41), Port of 3306, username of the Admin user for the MySQL Cloud Service instance (for example, root). Verify that the Connection Method is Standard (TCP/IP).
View Image
Description of this image -
Optionally, you can store the password for the MySQL Cloud Service instance Admin user in a local keychain. Click Store in Keychain
View Image
Description of this image Enter the password for the MySQL Cloud Service instance Admin account and click OK.
View Image
Description of this image -
After entering the information, click Test Connection.
View Image
Description of this image If you did not store the password, enter the password when prompted. MySQL Workbench connects successfully. You now have a new MySQL Workbench connection that you can select to access your MySQL Cloud Service instance.
View Image
Description of this image
Connecting Remotely from MySQL Workbench using SSH
MySQL Workbench includes an option to connect to a MySQL service via SSH. This does not require adding an access rule to your MySQL Cloud Service instance.
-
On your local client computer, in MySQL Workbench, set up a new connection.
In the Setup New Connection dialog, change the Connection Method to Standard TCP/IP over SSH.
View Image
Description of this image -
In the Setup New Connection dialog, enter a Connection Name and then enter the following values:
- SSH Hostname: the public IP address of the instance and port 22 (for example: 160.34.11.41:22)
- SSH username: opc
View Image
Description of this image -
Skip the SSH Password field. For the SSH Key File, browse for the local copy of the private key you created when you created the instance (for example, privateKey). The MySQL Hostname and MySQL Server Port options are relative to the SSH host, the MySQL Cloud Service instance, in this case the localhost with IP address 127.0.0.1 and port 3306. The Username is the Admin user created when the instance was created (default: root).
View Image
Description of this image -
Optionally, you can store the password for the MySQL Cloud Service instance Admin user in a local keychain. Click Store in Keychain
View Image
Description of this image Enter the password for the MySQL Cloud Service instance Admin account and click OK.
View Image
Description of this image -
After entering the information, click Test Connection.
View Image
Description of this image If you did not store the password, enter the password when prompted. MySQL Workbench connects successfully. You now have a new MySQL Workbench connection that you can select to access your MySQL Cloud Service instance.
View Image
Description of this image
Want to Learn More?
-
Using Oracle MySQL Cloud Service in the Oracle Help Center
-
Oracle MySQL Cloud Service Videos in the Oracle Help Center