Private Database Instances

Private MySQL and PostgreSQL instances are an alternative to your server’s shared databases. Private database instances are useful for taking greater control over how your databases are configured, or to resolve the “bad neighbor problem,” where one user consumes too much of a shared database’s resources.

Private database instances rely upon the server’s globally-installed MySQL or PostgreSQL binaries, so WebFaction continues to apply security patches for you. Private database instances run under your user account, so the instance process counts toward your account’s memory allotment, and the instance’s files count toward your account’s disk space allotment.

Private MySQL Instances

Private MySQL instances run under your user account, with configuration and data files stored in an application directory, ~/webapps/instance, where instance is the name of the application.

The root user’s password is automatically generated. The password can be found in the control panel, in the application’s Extra info field.

On CentOS 6 servers (web300 and greater), instances run MySQL 5.5. On CentOS 5 servers, instances run MySQL 5.0.

Every instance comes with two cron jobs. One attempts to start the database every ten minutes, if it is not already running. The other attempts to create a dump of your database once per day.

A MySQL instance’s error log file is ~/logs/user/error_instance.log.

Installing a Private MySQL Instance

To install a private MySQL instance:

  1. Log in to the control panel.
  2. Click Domains / websites ‣ Applications. The list of applications appears.
  3. Click the Add new application button. The Create a new application form appears.
  4. In the Name field, enter a name for the application.
  5. In the App Category menu, click to select MySQL.
  6. In the App Type menu, click to select MySQL private instance.
  7. If applicable, in the Machine menu, click to select a web server.
  8. Click the Save button.

The application is installed and added to the list of applications.

Starting, Restarting, and Stopping a Private MySQL Instance

To manually start, restart, or stop a private MySQL instance, open an SSH session to your account, then enter one of these management commands:

  • Start: $HOME/webapps/instance/bin/start
  • Restart: $HOME/webapps/instance/bin/stop && $HOME/webapps/instance/bin/start
  • Stop: $HOME/webapps/instance/bin/stop

where instance is the name of the private database instance application as it appears on the control panel, and press Enter.

Creating Private MySQL Databases and Users

To use your database, you will likely require databases and users. To create a database and user with privileges on that database:

  1. Open an SSH session to your account.

  2. Start a MySQL session. Enter mysql -P port -u root -p --protocol=tcp, where port is the instance’s port number, and press Enter. A password prompt appears.

    Note

    To find the port number:

    1. Log in to the control panel
    2. Click Domains / websites ‣ Applications. The list of applications appears.
    3. Click the name of the application.

    The port number appears in the Port section.

  3. Enter the password for the root user (as generated on the control panel) and press Enter. A prompt appears.

  4. Create a database. Enter CREATE DATABASE database_name;, where database_name is the name of the database, and press Enter.

  5. Create a user. Enter CREATE USER 'username' IDENTIFIED BY 'pass';, where username is the new username and pass is the user’s password, and press Enter.

    See also

    See Strengthening Passwords for important information about choosing passwords.

  6. Switch to the new database. Enter USE database_name; and press Enter.

  7. Grant the user privileges on the database. Enter GRANT ALL ON database_name TO 'username'; and press Enter.

  8. Quit the session. Enter q and press Enter.

The user can now connect to the database and run queries.

See also

See the official MySQL documentation for more information about creating and managing users and databases.

Importing Data from a Shared MySQL Database

To move your data from a shared MySQL database to a private database instance:

  1. Dump the contents of your existing shared database to a file.

  2. Open an SSH session to your account.

  3. Enter mysql -P port --protocol=tcp -u database_user -p -D database_name < dump_file, where:

    • port is the instance’s port number,
    • database_user is a valid database username,
    • database_name is a database that the user has permissions to use, and
    • dump_file is the path the dump containing the contents of your shared database.

    and press Enter.

The contents of the dump file are loaded into the database.

Private PostgreSQL Instances

Private PostgreSQL instances run under your user account, with configuration and data files stored in an application directory, ~/webapps/instance, where instance is the name of the application.

On CentOS 6 servers (web300 and greater), instances run PostgreSQL 9.1. On CentOS 5 servers, instances run PostgreSQL 8.3.

Every instance comes with two cron jobs. One attempts to start the database every ten minutes, if it is not already running. The other attempts to create a dump of your database once per day.

An instance’s log files can be found in the ~/webapps/instance/data/pg_log/ directory.

Installing a Private PostgreSQL Instance

To install a private PostgreSQL instance:

  1. Log in to the control panel.
  2. Click Domains / websites ‣ Applications. The list of applications appears.
  3. Click the Add new application button. The Create a new application form appears.
  4. In the Name field, enter a name for the application.
  5. In the App Category menu, click to select PostgreSQL.
  6. In the App Type menu, click to select PostgreSQL private instance.
  7. If applicable, in the Machine menu, click to select a web server.
  8. Click the Save button.

The application is installed and added to the list of applications.

Starting, Restarting, and Stopping a Private PostgreSQL Instance

To manually start, restart, or stop a private PostgreSQL instance, open an SSH session to your account, then enter one of these management commands:

  • Start: $HOME/webapps/instance/bin/start
  • Restart: $HOME/webapps/instance/bin/stop && $HOME/webapps/instance/bin/start
  • Stop: $HOME/webapps/instance/bin/stop

where instance is the name of the private database instance application as it appears on the control panel, and press Enter.

Creating Private PostgreSQL Databases and Users

To use your database, you will likely require databases and users. To create a database and user with privileges on that database:

  1. Open an SSH session to your account.

  2. Start a PostgreSQL interactive terminal. Enter psql -h localhost -p port postgres, where port is the instance’s port number and press Enter. A terminal prompt appears.

  3. Create a database. Enter CREATE DATABASE database_name;, where database_name is the name of the database, and press Enter.

  4. Create a user. Enter CREATE USER username WITH PASSWORD 'pass';, where username is the new username and pass is the user’s password, and press Enter.

    See also

    See Strengthening Passwords for important information about choosing passwords.

  5. Grant the user privileges on the database. Enter GRANT ALL PRIVILEGES ON DATABASE database_name to username; and press Enter.

  6. Quit the interactive terminal. Enter q and press Enter.

The user can now connect to the database and run queries.

See also

See the official PostgreSQL documentation for more information about creating and managing users and databases.

Importing Data from a Shared PostgreSQL Database

To move your data from a shared PostgreSQL database to a private database instance:

  1. Dump the contents of your existing shared database to a file.

  2. Open an SSH session to your account.

  3. Enter psql -h localhost -p port -U database_user database_name < dump_file, where:

    • port is the instance’s port number,
    • database_user is a valid database username,
    • database_name is a database that the user has permissions to use, and
    • dump_file is the path the dump containing the contents of your shared database.

    and press Enter.

The contents of the dump file are loaded into the database.

Configuring Applications for Private Database Instances

To connect an application to a private MySQL or PostgreSQL instance, use the following configuration details:

Hostname
localhost
Port

Use the port number assigned to your application.

Note

To find the port number:

  1. Log in to the control panel
  2. Click Domains / websites ‣ Applications. The list of applications appears.
  3. Click the name of the application.

The port number appears in the Port section.

Username
A database user you’ve created previously.
Database
A database you’ve created previously.

The following sections provide examples of how to reconfigure applications for use with private database instances.

Configuring Django for a Private Database Instance

To reconfigure a Django application’s default project to use a private PostgreSQL instance:

  1. If applicable, import data from a shared PostgreSQL database to a private database instance.

  2. In a text editor, open ~/webapps/application/myproject/myproject/settings.py, where application is the name of the Django application as it appears in the control panel.

  3. Find the DATABASES dictionary similar to the following:

    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.',
            'NAME': '<DATABASE>',
            'USER': '<USERNAME>',
            'PASSWORD': '<PASS>',
            'HOST': '',
            'PORT': '<PORT_NUMBER>',
        }
    }
    

    and modify the dictionary such that:

    • django.db.backends. is replaced by django.db.backends.postgresql_psycopg2,

    • <DATABASE> is the name of a database you’ve created previously,

    • <USERNAME> is a database user you’ve created previously,

    • <PASS> is the database user’s password, and

    • <PORT> is the port number assigned to your private database instance.

      See also

      To find the port number:

      1. Log in to the control panel
      2. Click Domains / websites ‣ Applications. The list of applications appears.
      3. Click the name of the application.

      The port number appears in the Port section.

  4. Save and close the file.

  5. Restart the Django application.

The Django application is configured to connect to the private PostgreSQL instance.

Configuring WordPress for a Private Database Instance

To reconfigure a WordPress application to use a private MySQL instance:

  1. Import data from the application’s database to a private MySQL instance.
  2. In a text editor, open ~/webapps/application/wp-config.php, where application is the name of the WordPress application.
  3. Find a line starting with define('DB_NAME', and replace the line with define('DB_NAME', 'database');, where database is the name of a database you’ve created previously.
  4. Find a line starting with define('DB_USER', and replace the line with define('DB_USER', 'username');, where username is a database user you’ve created previously.
  5. Find a line starting with define('DB_PASSWORD', and replace the line with define('DB_PASSWORD', 'password');, where password is the database user’s password.
  6. Find a line starting with define('DB_HOST', and replace the line with define('DB_HOST', 'localhost:port');, where port is the port number assigned to your private database instance.
  7. Save and close the file.