MySQL Database Hosting

From Campus Web Server Help

Revision as of 16:38, 19 December 2011

Pricing details coming soon! Meanwhile, if you have a database hosted elsewhere, you may certainly connect to it using the MySQL client installed on the web servers. You will likely need to reference the list of web servers to create users and firewall rules in your third-party database.

The documentation below is for our beta testers. As of 11/7/2011, no new beta testing accounts are being accepted.

Accessing Your Database

By logging in using SSH, you will have full control of your databases. For a given database, there are actually three databases: one for development, staging, and production. To communicate with the proper database from the terminal, you must specify the fully qualified domain name of the database server, such as webhostingdb-c1n0.cc.uga.edu. In your application code, you need only use the basic host name of webhostingdb-c1n${node_number}, where ${node_number} is the particular clustered database node (typically 0). Since the development web servers are in the same domain as their corresponding database servers, one need not modify code as it is moved from on stage of development to another (i.e. stage to production). The code will automatically connect to the database which corresponds to the current stage of development.

List of Database Server Names

• webhostingdb-c1n0.dev.uga.edu - development
• webhostingdb-c1n0.stage.uga.edu - staging
• webhostingdb-c1n0.cc.uga.edu - production
• webhostingdb-c1n1.cc.uga.edu - production

Connecting From a Third-Party MySQL Client

Of course, since SSH is supported, you may choose to administer your databases with third-party software, such as "MySQL Query Browser". To do so, simply use a port forward to the desired database server when connecting to the management node (ugawebhosting.uga.edu). With a standard, terminal-based SSH client, this is done by using the "-L" switch, as in:

ssh -L 3306:webhostingdb-c1n0.dev.uga.edu:3306 myusername@ugawebhosting.uga.edu

In the third-party software, specify localhost as the database server. Communication from your client to the management node will be encrypted and then forwarded to the designated database server.

Database Security

Be sure to protect your database credentials. In your code, consider placing credentials in include files above the document root (public_html), possibly in the etc directory. You should be able to remove all group and other user permissions for this include file (600 / rw-------).

Database servers can only be accessed from the management node, to which one's SSH sessions connect, and from the UGA Webhosting web servers which correspond to it's domain. For example, the staging web server is the only web server which can connect to the staging database server. In an SSH session, all database servers are accessible.

Redundancy

The production database is configured with a multi-master replica. This means that you can point your code at either webhostingdb-c1n0 or webhostingdb-c1n1. The data should replicate bidirectionally very quickly. If you are writing custom code, you might consider balancing requests across them.

Support

We do not offer support for this feature. Our backups are for disaster-recovery purposes only.

Command-Line Operations

From the terminal (SSH), you can do anything to your database, from creating tables to backing up and migrating the database between development stages.

Here are some examples (using the uw_ballpark user as an example):

Backing Up A Database

Here's an example of how to backup the uw_ballpark database in the development environment.

mysqldump -h webhostingdb-c1n0.dev.uga.edu -u uw_ballpark -p uw_ballpark > /usr/www/dev/ballpark/other/uw_ballpark.sql

Migrating A Development Database to Staging

One can use a backup of the development database to populate the staging database. This simple example completely obliterates any existing content in the staging database:

mysql -h webhostingdb-c1n0.stage.uga.edu -u uw_ballpark -p uw_ballpark < /usr/www/dev/ballpark/other/uw_ballpark.sql

Getting to a MySQL Session Prompt

To issue raw SQL statements to MySQL for the uw_ballpark, do the following:

mysql -h webhostingdb-c1n0.dev.uga.edu -u uw_ballpark -p uw_ballpark

Discover mysql Command Secrets

The manual page tells you about all the options.

man mysql
man mysqldump