MySQL Database Service

Connecting to MySQL

UVM-related web applications should generally be hosted on our silk hoting service, although WebDB can be accessed from anywhere on the campus network.

Most programming languages have multiple database libraries from which you can choose to work with MySQL. We currently suggest the following:

Encrypted connections

Although authentication details are always protected, full connections between MySQL clients and the server are not encrypted by default. For many web sites in a controlled environment this is acceptable. However, if your application needs extra security, it is possible to use SSL/TLS encryption for all database communication.

Command-line

On zoo and the silk hosting service, usage of the mysql command-line tool is already configured to use encryption.

On other systems, you can do the following:

1. Save a copy of the WebDB certificate authority public key to the computer you will be connecting from.
2. In either /etc/my.cnf (for system-wide use) or ~/.my.cnf (for your personal use), either find or create a [client] section. Add to it the following:

   [client]
   host = webdb.uvm.edu
   ssl-ca = /path/to/where/you/saved/webdb-cacert.pem
   ssl-verify-server-cert
   

New connections to webdb.uvm.edu should now be encrypted. You can verify this by starting a new session and typing status. You should see something similar to:

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.33-36, for Linux (x86_64) using  7.0

Connection id:          783
Current database:
Current user:           myusername@localhost
SSL:                    Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
…

The "SSL:" line describes the type of encryption in use.

Drupal

Download a copy of the WebDB certificate authority public key.

Look in your site's settings.php. Add a pdo section to the database configuration, describing where to find the certificate authority file. For example:

$databases = array (
    'default' => 
     array (
       'default' => 
        array (
          'database' => 'mydbname',
          'username' => $username,
          'password' => $password,
          'host' => 'webdb.uvm.edu',
          'port' => '3306',
          'driver' => 'mysql',
          'prefix' => '',
          'pdo' => array(
              PDO::MYSQL_ATTR_SSL_CA => '/etc/pki/tls/certs/webdb-cacert.pem',
           ),
       ),
    ),
  );

Perl

If you will also use MySQL from the command-line, follow the directions above. Then tell DBI to load your my.cnf/.my.cnf file from your DSN. For example:

my $dsn = "DBI:mysql:mydbname;host=webdb.uvm.edu;mysql_ssl=1;mysql_read_default_file=/etc/my.cnf";
my $dbh = DBI->connect( $dsn, $username, $password );

Alternately, you can download the WebDB certificate authority public key and describe it directly:

my $dsn = "DBI:mysql:mydbname;host=webdb.uvm.edu;mysql_ssl=1;mysql_ssl_ca_file=/etc/pki/tls/certs/webdb-cacert.pem";
my $dbh = DBI->connect( $dsn, $username, $password );

PHP

When using PDO for database connections, you can simply give it the location where you downloaded a copy of the WebDB certificate authority public key.

$dbh = new PDO(
    'mysql:host=webdb.uvm.edu;dbname=mydbname',
    $username,
    $password,
    array(
        PDO::MYSQL_ATTR_SSL_CA => '/etc/pki/tls/certs/webdb-cacert.pem',
    )
);

Python

Download a copy of the WebDB certificate authority public key and use it in your connection options.

ssl_args = {'ssl': {'ca': '/etc/pki/tls/certs/webdb-cacert.pem'}}
db_engine = create_engine(
        'mysql://username:password@webdb.uvm.edu/mydbname',
        connect_args=ssl_args)
Session = sessionmaker(bind=db_engine)
db = Session()

WordPress

Download a copy of the WebDB certificate authority public key.

By default, WordPress does not yet provide configurable access to SSL/TLS settings (see this enhancement request). For the time being, you can set up WordPress with SSL/TLS by installing the Secure DB Connection plugin and adding the following to wp-config.php:

define( 'MYSQL_SSL_CA', '/etc/pki/tls/certs/webdb-cacert.pem' );
define( 'MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL );

Note

On zoo and silk, a copy of the certificate authority file can be found and referenced at /etc/pki/tls/certs/webdb-cacert.pem rather than downloaded.