{"id":5105,"date":"2021-09-28T12:56:51","date_gmt":"2021-09-28T11:56:51","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=5105"},"modified":"2021-10-05T08:27:17","modified_gmt":"2021-10-05T07:27:17","slug":"discovering-mysql-database-service-episode-9-connect-to-mysql-database-service-using-mysql-workbench","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2021\/09\/28\/discovering-mysql-database-service-episode-9-connect-to-mysql-database-service-using-mysql-workbench\/","title":{"rendered":"Discovering MySQL Database Service \u2013 Episode 9 \u2013 Connect to MySQL Database Service Using MySQL Workbench"},"content":{"rendered":"\n

MySQL<\/a>, the world’s most popular open source database<\/strong>, is available as a managed cloud service<\/strong> in Oracle Cloud Infrastructure<\/a><\/strong> (OCI<\/a><\/strong>) under the name of MySQL Database Service<\/a><\/strong> (MDS<\/a><\/strong>).<\/p>\n\n\n\n

MySQL Database Service is a fully managed cloud service, 100% Developed, Managed and Supported by the MySQL Team.<\/strong><\/p><\/blockquote><\/figure>\n\n\n\n

This is the ninth<\/span> episode of \u00ab\u00a0Discovering MySQL Database Service<\/a><\/em><\/strong><\/em><\/strong>\u00ab\u00a0, a series of tutorials where I will show you, step by step, how to use MySQL Database Service and some other Oracle Cloud Infrastructure services. <\/p>\n\n\n\n

Please also note that you can run this tutorial and thus try MySQL Database Service & the other Oracle Cloud Infrastructure services for free<\/span><\/strong> by starting your 30-day trial<\/a>.<\/p>\n\n\n\n

\"MySQL<\/figure><\/div>\n\n\n\n
\n

Planning episodes<\/h2>\n\n\n\n
  1. Introduction to the series Discovering MySQL Database Service<\/em><\/strong><\/a> <\/li>
  2. Create a compartment<\/a><\/li>
  3. Create a Virtual Cloud Network<\/a><\/li>
  4. Dump your MySQL data into an Object Storage bucket<\/a><\/li>
  5. Create a MySQL DB system from a MySQL Shell dump<\/a><\/li>
  6. Update the Private Subnet Security List<\/a><\/li>
  7. Use a Bastion SSH port forwarding session<\/a><\/li>
  8. Connect to MySQL Database Service Using MySQL Shell<\/a><\/li>
  9. Connect to MySQL Database Service Using MySQL Workbench<\/a><\/li>
  10. Connect to MySQL Database Service Using OCI Cloud Shell<\/a><\/li>
  11. MySQL Database Service Features Overview<\/li><\/ol>\n<\/div><\/div>\n\n\n\n

    <\/p>\n\n\n\n

    <\/div>\n\n\n\n

    Episode 9 – Connect to MySQL Database Service Using MySQL Workbench<\/h1>\n\n\n\n

    In the previous episode<\/a> we’ve seen how to connect to our MySQL Database Service instance with MySQL Shell using a SSH port forwarding from the OCI Bastion service.
    One more step to our Discovering MySQL Database Service<\/em><\/strong> journey. <\/p>\n\n\n\n

    In this episode, we’ll learn how to connect to our MySQL Database Service instance using MySQL Workbench<\/strong> – a unified visual tool for database architects, developers, and DBAs that provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more.<\/p>\n\n\n\n

    <\/div>\n\n\n\n

    MySQL Workbench<\/h2>\n\n\n\n
    \"\"\/<\/figure>\n\n\n\n

    MySQL Workbench is a GUI client for MySQL. It’s an unified visual tool for database architects, developers, and DBAs.<\/p>\n\n\n\n

    MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more.<\/p>\n\n\n\n

    It is available on Windows, GNU Linux and Mac OS X.
    You can download the community version     here<\/a>.<\/p>\n\n\n\n

    <\/div>\n\n\n\n

    Create a SSH port forwarding session<\/h2>\n\n\n\n

    In episode 7 – Discovering MySQL Database Service \u2013 Episode 7 \u2013 Use a Bastion SSH port forwarding session<\/a> – we created a Bastion, a SSH port forwarding (SSH tunneling) session and generated SSH key pair.<\/p>\n\n\n\n


    We are now going to use these in order to connect to MDS with MySQL Workbench.<\/p>\n\n\n\n

    If your session is not valid anymore, you must create a new one (see Create a session paragraph from Discovering MySQL Database Service \u2013 Episode 7 \u2013 Use a Bastion SSH port forwarding session<\/a>).<\/p>\n\n\n\n

    Please note that you need a SSH client on your computer. If you are using a GNU Linux, MacOS, Windows 10 system you are good to go. <\/p>\n\n\n\n

    If not\u2026 all is not lost \ud83d\ude42
    Please read episode 10 :     Connect to MySQL Database Service Using OCI Cloud Shell<\/a>.<\/p>\n\n\n\n

    <\/div>\n\n\n\n

    In the console menu, go to Identity & Security<\/em> \/ Bastion<\/em>, check that you are in the right compartment then click on the Bastion we previously created (BastionMDS<\/em> in this example).<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n
    <\/div>\n\n\n\n

    You are now seeing the session we already created (if not create a new one). <\/p>\n\n\n\n

    We now must get the SSH port forwarding command generated by OCI.<\/p>\n\n\n\n

    To do so, go to the right and click on the action menu (3 vertical dots on the right). A contextual menu is showing up then click on copy SSH command.<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n
    <\/div>\n\n\n\n

    Paste this command in your shell prompt.<\/p>\n\n\n\n

    The SSH command looks like :<\/p>\n\n\n\n

    ssh -i <privateKey> -N -L <localPort>:10.0.1.231:3306 -p 22 ocid1.bastionsession.oc1.eu-frankfurt-1.amaaaaaacicuulyams3rrxazerty1uiopqsd2fghjklm3wxcvbr4m7wpu52a@host.bastion.eu-frankfurt-1.oci.oraclecloud.com<\/code><\/pre>\n\n\n\n
    Replace <privateKey><\/em> with the path to the private key we downloaded in the previous episode – Discovering MySQL Database Service \u2013 Episode 7 \u2013 Use a Bastion SSH port forwarding session<\/a>  <\/p>\n\n\n\n
    Replace <localPort><\/em> with a free port on the local machine from which you want to connect to the bastion (ie your computer\/laptop).<\/p>\n\n\n\n
    Example:<\/p>\n\n\n\n
    ssh -i .\/ssh-key-discovering_MDS.key -N -L 3333:10.0.1.231:3306 -p 22 ocid1.bastionsession.oc1.eu-frankfurt-1.amaaaaaacicuulyams3rrxazerty1uiopqsd2fghjklm3wxcvbr4m7wpu52a@host.bastion.eu-frankfurt-1.oci.oraclecloud.com<\/code><\/pre>\n\n\n\n
    Then run the command…<\/p>\n\n\n\n
    If you hit the following error: WARNING: UNPROTECTED PRIVATE KEY FILE!<\/strong><\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    Don’t worry \ud83d\ude42
    Like stated in the error message, the private key must be protected. A chmod 600 (or equivalent regarding your OS) will deliver you.<\/p>\n\n\n\n
    $ chmod 600 ssh-key-discovering_MDS.key<\/code><\/pre>\n\n\n\n
     Then run the command again:<\/p>\n\n\n\n
    ssh -i .\/ssh-key-discovering_MDS.key -N -L 3333:10.0.1.231:3306 -p 22 ocid1.bastionsession.oc1.eu-frankfurt-1.amaaaaaacicuulyams3rrxazerty1uiopqsd2fghjklm3wxcvbr4m7wpu52a@host.bastion.eu-frankfurt-1.oci.oraclecloud.com<\/code><\/pre>\n\n\n\n
    No error anymore and no prompt.
    But do not<\/strong> close the window if not it will close the SSH tunnel (thus you will need to run the command again).<\/p>\n\n\n\n
    <\/div>\n\n\n\n
    Alternatively, you can add an ampersand (&<\/strong>) at the end of the command.<\/p>\n\n\n\n
    ssh -i .\/ssh-key-discovering_MDS.key -N -L 3333:10.0.1.231:3306 -p 22 ocid1.bastionsession.oc1.eu-frankfurt-1.amaaaaaacicuulyams3rrxazerty1uiopqsd2fghjklm3wxcvbr4m7wpu52a@host.bastion.eu-frankfurt-1.oci.oraclecloud.com  & <\/code><\/pre>\n\n\n\n
    <\/div>\n\n\n\n
    Connect to MDS using MySQL Workbench<\/h2>\n\n\n\n
    Now it is the time to connect to our MySQL instance through the SSH tunnel using MySQL Workbench.<\/p>\n\n\n\n
    Open MySQL Workbench.
    Go to Database \/ Manage Connections<\/em>\u2026<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/div>\n\n\n\n
    The Manage Server Connections<\/em> appears, then click the New<\/em> button (at the bottom left).<\/p>\n\n\n\n
    We can now setup our MDS connection in the form.<\/p>\n\n\n\n
    The main information must be entered in the Connection<\/em> tab:<\/p>\n\n\n\n