Setting up Amazon RDS for MySQL in PHP and Heroku

You need to encrypt your connection to RDS. Get a certificate and more info at http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html.

The heroku docs are at https://devcenter.heroku.com/articles/amazon-rds. Put the certificate where php can find it, eg. config/rds-combined-ca-bundle.pem.

Procfile

web: vendor/bin/heroku-php-apache2 public/

public/index.php

  <?php
  $url = parse_url(getenv("DATABASE_URL"));

  $host = $url["host"];
  $port = $url["port"];
  $username = $url["user"];
  $password = $url["pass"];
  $db = substr($url["path"], 1);

  echo '<pre>Connecting to db host: ', $host, '</pre>';

  $mysqli = mysqli_init();
  mysqli_options($mysqli, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);
  mysqli_options($mysqli, MYSQLI_OPT_CONNECT_TIMEOUT, 5);

  $mysqli->ssl_set(NULL,NULL,"/app/config/rds-combined-ca-bundle.pem",NULL,NULL);

  $mysqli->real_connect($host, $username, $password, $db, $port, NULL, MYSQLI_CLIENT_SSL);
  if (mysqli_connect_errno()) {
    printf("<pre>Connection failed: [%s] %s<pre>", mysqli_connect_errno(), mysqli_connect_error());
  }
  else {
    echo '<pre>Connection OK</pre>';
    $rs = $mysqli->query("SHOW STATUS LIKE 'ssl_cipher'");
    echo '<pre>', print_r($rs->fetch_assoc()), '</pre>';
  }

  $mysqli->close();
  phpinfo();
  ?>

Config

> heroku config:set DATABASE_URL=mysql2://username:[email protected]:3306/db-name

Open the MySQL port (3306) for inbound connections to your instance in the security group settings. Your local IP might have been added here already when you set up the RDS instance. Since your Heroku dyno doesn’t have a fixed IP, you need to open in up for all IP’s: 0.0.0.0/0.

Then finally, require SSL access to your MySQL database for the user by executing the following query.

GRANT USAGE ON *.* TO 'username'@'%' REQUIRE SSL;