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, you can use SSL/TLS encryption for all database communication.

To use encrypted connections, your client must be told to do so and should verify that the server certificate was signed by a known authority. On silk and zoo, this happens automatically for command-line access or you can reference /etc/pki/ca-trust/extracted/pem/tls-ca-bundle.pem in your code. On other systems, you can use your operating system's standard CA bundle or download a copy.

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: in either /etc/my.cnf (for system-wide use) or ~/.my.cnf (for your personal use), find or create a [client] section. Add to it the following:

[client]
host = webdb.uvm.edu
ssl-ca = /etc/pki/ca-trust/extracted/pem/tls-ca-bundle.pem
ssl-mode = VERIFY_CA

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 8.0.30-22 for Linux on x86_64 (Percona Server (GPL), Release 22, Revision 7e301439b65)

Connection id:          783
Current database:
Current user:           myusername@localhost
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
…

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

Drupal

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/ca-trust/extracted/pem/tls-ca-bundle.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 escribe your settings directly:

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

PHP

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

Python

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

WordPress

By default, WordPress does not provide configurable access to SSL/TLS settings (see this enhancement request). 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/ca-trust/extracted/pem/tls-ca-bundle.pem' );
define( 'MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL );