Connecting an external application

Connecting with a language's driver

All the information a driver needs to make a connection to your deployment is in the "mysql" section of your connection strings. The table contains a breakdown for reference.

• 0... indicates one or more of these entries in an array.

Many MySQL drivers are able to make a connection to your deployment when given the URI-formatted connection string found in the "composed" field of the connection information. For example

mysql://ibm_cloud_30399dec_4835_4967_a23d_30587a08d9a8:$PASSWORD@981ac415-5a35-4ac7-b6bb-fb609326dc42.8f7bfd8f3faa4218aec56e069eb46187.databases.appdomain.cloud:32704/ibmclouddb?ssl-mode=verify-full

For more information on ssl-mode states, see Additional Connection parameters.

The following example uses the information from your connection string and the Java driver jdbc to connect to your database.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class App {
 private final String STATUS_COMMAND = "SHOW VARIABLES LIKE '%version%';";
 
 private Connection connect = null;
 private Statement stmt = null;
 private ResultSet rs = null;

 private final String url = "mysql://127.0.0.1:30799";
 private final String username = "";
 private final String password = "";
 private final Boolean useSSL = true;

 public static void main(String args[]) throws Exception {
   App app = new App();
   final byte maxConnectionAttempt = 5;
   byte currentConnectionAttempt = 0;

   while (!app.connectDatabase() && currentConnectionAttempt < maxConnectionAttempt)
      ++currentConnectionAttempt;

   if (currentConnectionAttempt >= maxConnectionAttempt) {
      System.out.println(currentConnectionAttempt + " weren't successfull!");
         } else {
            app.printStatus();
            app.closeConnection();
         }
   }

   public void printStatus() {
         try {
            stmt = connect.createStatement();
            rs = stmt.executeQuery(STATUS_COMMAND);
            while (rs.next())
            System.out.println(rs.getString(1) + ": " + rs.getString(2));
         } catch (SQLException ex) {
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
            closeConnection();
         }
   }

   private void closeConnection() {
      try {
         if (rs != null) {
            rs.close();
         }

         if (stmt != null) {
            stmt.close();
         }

         if (connect != null) {
            connect.close();
         }
      } catch (Exception e) {
         System.out.println(e.getMessage());
      }
      System.out.println("Connection closed.");
   }

   public boolean connectDatabase() {
      // https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-connect-drivermanager.html
      try {
            Properties props = new Properties();
            props.setProperty("user", username);
            props.setProperty("password", password);
            props.setProperty("useSSL", Boolean.toString(useSSL));
            props.setProperty("sslMode", (useSSL ? "REQUIRED" : "DISABLED"));
            props.setProperty("requireSSL", Boolean.toString(useSSL));
            props.setProperty("verifyServerCertificate", Boolean.toString(useSSL));

            Class.forName("com.mysql.cj.jdbc.Driver");
            connect = DriverManager.getConnection("jdbc:" + url, props);
            return true;
      } catch (SQLException ex) {
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
      } catch (ClassNotFoundException ex) {
            System.out.println("Connector class can not be found: " + ex.getMessage());
      }
      return false;
   }
}

The following example uses the information from your connection string and the Python driver pymysql to connect to your database. This is just a simple connection example, without error handling or retry logic and might not be suitable for production.

import pymysql
connection = pymysql.connect(
  host="hostname",
  port=32089,
  user="username",
  passwd="password",
  ssl_ca="/home/user/mysql_ca.crt",
  ssl_verify_cert=True,
  ssl_verify_identity=True)
  
cursor = connection.cursor()
cursor.execute("SHOW STATUS;")

for row in cursor:
    print(row[0] + "\t" + row[1])
    
cursor.close()
connection.close()

Driver TLS and self-signed certificate support

All connections to Databases for MySQL are TLS 1.2 enabled, so the driver you use to connect needs to be able to support encryption. Your deployment also comes with a self-signed certificate so the driver can verify the server upon connection.

For more information, see Cloud Databases Certificates FAQ.

Using the self-signed certificate

1. Copy the certificate information from the Endpoints panel or the Base64 field of the connection information.
2. If needed, decode the Base64 string into text.
3. Save the certificate to a file. (You can use the name that is provided or your own file name).
4. Provide the path to the certificate to the driver or client.

Other Drivers

MySQL has an array of language drivers. The table below covers a few of the most common. Consult MySQL's Connectors and APIs for more information.

When connecting to MySQL using PHP, it is necessary to change the auth plug-in from sha256_password to mysql_native_password.