Databases

Databases simplify the way you can store data, so you can access them quickly and reliably. WebFaction supports two kinds of databases, MySQL and PostgreSQL (also known as Postgres). Both of database types support many advanced features and have large user-bases. In this section, you will learn how to create, manage, and access databases of both types.

Creating a New Database with the Control Panel

To create a new database with the control panel:

  1. Log into the control panel.

  2. Click Databases ‣ Databases. The list of databases appears.

  3. Click the Add new database button. The Create a new database form appears.

  4. In the Name field, enter a name for the database.

  5. In the Database type menu, click to select PostgreSQL or MySQL.

  6. If applicable, choose a server to host the database. In the Machine menu, click to select a server.

  7. If desired, change the database encoding. To change the database encoding:

    1. Click Change. A menu appears.
    2. In the menu, click to select an encoding. For example, latin7.
  8. Choose a database owner.

    To choose an existing user, in the Database owner menu, click to select a username.

    To create a new user:

    1. In the Database owner menu, click to select Create a new postgresql user or Create a new mysql user, as applicable. The Username, Password, and Confirm password fields appear.

    2. In the Username field, enter a username.

    3. In the Password field, enter a password.

      See also

      See Strengthening Passwords for important information about choosing passwords.

    4. In the Confirm Password field, reenter the password.

  9. Optional: Enable additional database features. In the Add Ons section, click to select one or more features.

  10. Click the Save button.

The database is created and a confirmation message appears. See Connecting to a Database for connection details.

Managing Database Users

Each database must have a user as owner of the database, but additional users may be granted access to the database. You may use the control panel to create or delete users, or grant users permission to use specific databases.

Creating a Database User with the Control Panel

To create a database user with the control panel:

  1. Log into the control panel.

  2. Click Databases ‣ Database Users. The list of database users appears.

  3. Click the Add new database user button. The Create a new database user form appears.

  4. In the Username field, enter a name for the database user.

  5. In the Database type menu, click to select the type of database the new user may access.

  6. If applicable, choose the server on which the user is created. In the Machine menu, click to select a server.

  7. In the Password field, enter a password.

    See also

    See Strengthening Passwords for important information about choosing passwords.

  8. In the Confirm password field, reenter the password.

  9. Click the Save button.

The database user is created and a confirmation message appears. You may choose the new user as owner of a new database or grant the user permissions on an existing database.

Modifying a Database User’s Permissions with the Control Panel

You can grant or remove a database user’s access to one or more databases. To modify a database user’s permissions:

  1. Log into the control panel.

  2. Click Databases ‣ Database Users. The list of database users appears.

  3. Click on the user to modify. The user’s details appear, including the Permissions list.

  4. Add or remove the databases.

    To revoke access to a database, click the - (minus) button beside a database.

    To grant access to a database:

    1. At the end of the permissions list, click the + (plus) button. A menu of databases appears.
    2. In the menu of databases, click to select a database to grant the user access to.
  5. Click the Save button.

The database user’s privileges are updated and a confirmation message appears.

Changing a Database User’s Password

To change a database user’s password:

  1. Log in to the control panel.

  2. Click Databases ‣ Database Users. The list of database users appears.

  3. Click on the user to modify. The user’s details appear.

  4. Click Change. The Password and Confirm password fields appear.

  5. In the Password field, enter the new password.

    See also

    See Strengthening Passwords for important information about choosing passwords.

  6. In the Confirm password field, reenter the password.

  7. Click the Save button.

The user’s password is changed and a confirmation message appears. Use the new password to connect to any databases that the user has access to.

Connecting to a Database

To connect to a local database (for example, from a script running on a WebFaction server), use the following connection details:

  • hostname: localhost or webX.webfaction.com, where webX is the server name (for example, Web310)
  • port: For MySQL, use 3306. For PostgreSQL, use 5432.
  • database: The name of the database.
  • username: A valid database username with ownership of or permissions to the database.

For instructions on common connection methods, including how to connect remotely or using the command line tools, please see the following sections.

Accessing a Database from the Web

WebFaction provides two tools, phpMyAdmin and phpPgAdmin, to simplify many common database administration tasks and provide web-based access to your databases.

Access phpMyAdmin

To log into phpMyAdmin:

  1. Log in to the control panel.

  2. Click Databases ‣ Databases. The list of databases appears.

  3. In the row of the database to access, click phpMyAdmin. The phpMyAdmin home page appears.

    Note

    You may be prompted about the security certificate. Please accept the certificate. The prompt occurs since you are connecting directly to one of the WebFaction web servers, rather than the main control panel site.

  4. In the Username field, enter the username of a database user with access to one or more databases.

  5. In the Password field, enter the database user’s password.

  6. Click the Go button.

In the menu on the left, a list of databases appears.

Note

Attempts to load SQL files larger than 20MB will fail. To import SQL data too large for phpMyAdmin, import data with the mysql command line tool.

Access phpPgAdmin

Note

Only databases owned by a logged in user are available in phpPgAdmin. Additional users granted access to a database will not be able to access or modify that database with phpPgAdmin.

To log into phpPgAdmin:

  1. Log in to the control panel.

  2. Click Databases ‣ Databases. The list of databases appears.

  3. In the row of the database to access, click phpPgAdmin. The phpPgAdmin home page appears.

    Note

    You may be prompted about the security certificate. Please accept the certificate. The prompt occurs since you are connecting directly to one of the WebFaction web servers, rather than the main control panel site.

  4. In the menu on the left, click PostgreSQL. A login form appears.

  5. In the Username field, enter the username of a database user that owns one or more databases.

  6. In the Password field, enter the database user’s password.

  7. Click the Login button.

A list of databases appears.

Note

Attempts to load SQL files larger than 20MB will fail. To import SQL data too large for phpPgAdmin, import data with the psql command line tool.

Accessing a Database from the Command Line

MySQL and PostgreSQL each provide a command line interface, which you can use to issue commands to your databases while logged into your web server with SSH. MySQL uses mysql; PostgreSQL uses psql.

MySQL Command Line

To log in to a MySQL database enter this command at the terminal prompt, enter:

mysql -u username -D database_name -p

where:

  • database_name is the name of the database as it appears on the control panel, and
  • username is the name of a database user with permission to use the database,

and press Enter.

For example, user123 logs in to user123_mysql_db:

user123@webXX ~ $ mysql -u dbuser123 -D user123_mysql_db -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 8047 to server version: 5.0.19

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

PostgreSQL Command Line

To log in to a PostgreSQL database enter this command at the terminal prompt:

psql -U username database_name

where:

  • database_name is the name of the database as it appears on the control panel, and
  • username is the name of a database user with permission to use the database,

and press Enter.

For example, user123 logs in to example_db:

user123@webXX ~ $ psql -U user123 example_db
Password for user user123:
Welcome to psql 8.3.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

user123=>

Accessing a Database Remotely

You can access a database remotely by using an SSH tunnel, which establishes a secure connection through which ordinary database traffic can travel. It allows you to work with your database as if it were running on your local computer. You can establish an SSH tunnel with PuTTY or the ssh command line tool.

Starting an SSH Tunnel with ssh

  1. Open a terminal session.

  2. Enter ssh -L 3306:127.0.0.1:3306 username@webXX.webfaction.com where webXX is your machine name (e.g. web57) and press Enter.

    Note

    For PostgreSQL, substitute 3306 with 5432: ssh -L 5432:127.0.0.1:5432 username@webXX.webfaction.com.

  3. You may be alerted that the authenticity of the host cannot be established. Enter yes and press Enter.

  4. When prompted, enter your password and press Enter.

  5. Leave the SSH session open as long as you want to maintain the tunnel.

    You can now connect to your database at 127.0.0.1, port 3306.

  6. Enter exit and press Enter to close the connection.

Starting an SSH Tunnel with PuTTY

  1. Start PuTTY.

  2. In the Category tree, click to expand SSH.

  3. In the Category tree, click to select Tunnels.

  4. In the Source port field, enter 3306.

    Note

    For PostgreSQL, substitute 3306 with 5432.

  5. In the Destination field, enter 127.0.0.1:3306.

    Note

    For PostgreSQL, substitute 3306 with 5432: 127.0.0.1:5432.

  6. Click the Add button.

    _images/putty-06.png
  7. In the Category tree, click to select Session.

  8. In the Host Name (or IP address) field, enter webXX.webfaction.com where webXX is your machine name (e.g., web57).

  9. In the Port field, enter 22.

  10. Under Connection type, click to select SSH.

  11. Click the Open button.

    _images/putty-11.png
  12. A PuTTY Security Alert dialog may open. If so, click the Yes button.

  13. When prompted, enter your username and press Enter.

  14. When prompted, enter your password and press Enter.

  15. Leave the PuTTY window open as long as you want to maintain the tunnel.

    You can now connect to your database at 127.0.0.1, port 3306.

  16. Enter exit and press Enter to close the connection.

Accessing a Database from a Script or Application

You can also access your databases from scripts or applications. The MySQL and PostgreSQL instances are available on the web server. Many languages offer libraries to simplify making and executing SQL queries.

For example, the Python package MySQL-Python provides access to MySQL databases. This script, for example, prints all of the rows in the names table

import MySQLdb
db = MySQLdb.connect(host='127.0.0.1',
                     user='user123',
                     passwd='averysecurepassword',
                     db='user123s_my_db',)
cursor = db.cursor()
cursor.execute("""SELECT * FROM names""")
result = cursor.fetchall()
print result

Likewise, you might access a PostgreSQL database with Psycopg in a Python script. Psycopg is a PostgreSQL database adapter for Python; the package is installed by default. Here’s a script equivalent to the previous Python script, except that it reads from a PostgreSQL database:

import psycopg2
db = psycopg2.connect(host='127.0.0.1',
                      database='user123s_pg_database',
                      user='user123',
                      password='averysecurepassword',)
cursor = db.cursor()
cursor.execute("""SELECT * FROM names;""")
result = cursor.fetchall()
print result

PHP provides built-in functions for working with MySQL. In this example, the PHP script prints all of the rows in the names table:

<?php
mysql_connect("127.0.0.1", "user123", "averysecurepassword");
mysql_select_db("user123s_my_db") or die("Unable to select database");
$result = mysql_query("SELECT * FROM names");
mysql_close();
while ( $row = mysql_fetch_row($result) ) {
    echo implode(" ", $row), "\n";
}
?>

Import and Export Database Records

SQL-style databases make it easy to export, or dump, the contents of a database in a form which allows you to restore, or import, the state of that database on a bare database. This can be used to copy, rename, or backup databases.

Note

To minimize load and preserve performance on your server, please use ionice with database imports and exports larger than 500MB.

To use ionice, add ionice -c2 -n6 before your database shell commands. For example, to export a PostgreSQL database with ionice, enter ionice -c2 -n6 pg_dump -U database_user -f dump.sql database_name and press Enter.

Export

To export a MySQL database, enter:

mysqldump -u database_user database_name -p > dump.sql

where database_user is the name of a user with access to a MySQL database of the name database_name, and press Enter. mysqldump prompts for the password configured for that database. The contents of the database are written to dump.sql in the directory where the command is run.

To export a PostgreSQL database, enter:

pg_dump -U database_user -f dump.sql database_name

where database_user is the name of a user with access to a PostgreSQL database of the name database_name, and press Enter. pg_dump prompts for the database password. The contents of the database are written to dump.sql in the directory where the command is run.

Note

On some WebFaction servers, pg_dump is not in your default search path and may result in a command not found error. Use the complete path to pg_dump, /usr/local/pgsql/bin/pg_dump, instead.

Once you have created the dump file, you can download that file with scp or SFTP.

Import

To import a MySQL database, enter:

mysql -u database_user -p -D database_name < file

where database_user is the name of a user with access to a MySQL database of the name database_name and file is the path to a MySQL dump file, and press Enter. mysql prompts for the database password.

Note

If you’re importing a dump file encoded with a character set other than utf8 (for example, latin1), when invoking mysql you must use the option --default_character_set encoding, where encoding is the name of the dump file’s encoding. Thus, the complete command for importing a non-utf8 dump file is:

mysql -u database_user -p -D database_name --default_character_set encoding < file

To import a PostgreSQL database, enter:

psql -U database_user database_name < file

where database_user is the name of a user with access to a PostgreSQL database of the name database_name, and file is the path to a PostgreSQL dump file, and press Enter. psql prompts for the database password.

Note

If your dump file was created as a PostgreSQL custom dump, then you must use pg_restore to import the data. To import a PostgreSQL database with pg_restore enter this commmand:

pg_restore -U database_user -d database_name file

and press Enter.

Backing Up a Database Automatically

Regular backups of your data are important. You can schedule a cron job to automatically backup a MySQL or PostgreSQL database.

MySQL

To schedule an automatic backup of a MySQL database:

  1. Open an SSH session to your account.

  2. Create a directory to store the database backups. Enter mkdir ~/db_backups and press Enter.

  3. Store your database password in a file.

    1. Create the file. Enter touch $HOME/db_backups/database_name.cnf where database_name is the name of the database, and press Enter.

    2. Set the file permissions such that only you can read and write to the file. Enter chmod 600 $HOME/db_backups/database_name.cnf and press Enter.

    3. Open the file in a text editor.

    4. Insert these lines:

      [client]
      password=secret
      

      where secret is the password for the database user.

    5. Save and close the file.

  4. Edit your crontab to contain this line:

    0 2 * * * mysqldump --defaults-file=$HOME/db_backups/database_name.cnf -u database_user database_name > $HOME/db_backups/database_name-`date +\%Y\%m\%d`.sql 2>> $HOME/db_backups/cron.log,

    where database_user is the name of a user with access to a MySQL database of the name database_name.

    Note

    On some WebFaction servers, mysqldump is not in the search path used by cron and may result in a command not found error in your $HOME/db_backups/cron.log file. If this happens, then use the complete path to mysqldump, /usr/local/bin/mysqldump, instead.

Now the database is backed up at 2 a.m. each day.

PostgreSQL

To schedule an automatic backup of a PostgreSQL database:

  1. Open an SSH session to your account.
  2. Open a ~/.pgpass in a text editor (or create a new file, if it does not already exist).
  3. Add a new line containing *:*:database_name:database_user:password where database_user is the name of a user with access to a PostgreSQL database of the name database_name and password is the password of the database user.
  4. Secure the ~/.pgpass file. Enter chmod 600 ~/.pgpass and press Enter.
  5. Create a directory to store the database backups. Enter mkdir ~/db_backups and press Enter.
  6. Edit your crontab to contain this line: 0 2 * * * /usr/local/pgsql/bin/pg_dump -Fp -b -U database_user database_name > $HOME/db_backups/database_name-`date +\%Y\%m\%d`.sql 2>> $HOME/db_backups/cron.log.

Now the database is backed up at 2 a.m. each day.

Delete a Database

If you no longer need a database, you can delete it with the control panel. Keep in mind, however, that the database will be deleted immediately and completely. Please make a backup; see Export for details.

Delete a Database with the Control Panel

To delete a database with the control panel:

Warning

Deleting a database cannot be undone.

  1. Log into the control panel.
  2. Click Databases ‣ Databases. The list of databases appears.
  3. Click the name of the database to be deleted. The database details appear.
  4. Click the Delete button. A prompt, Are you sure you want to delete this database?, appears.
  5. To delete the database, click the Yes, I’m sure button. To keep the database, click No, Cancel button.

If you to delete the database, it is immediately and irrecoverably deleted.

Enabling Procedural Languages for PostgreSQL

PostgreSQL databases can be extended with three trusted procedural languages: PL/pgSQL, PL/Tcl, and PL/Perl. By default, these languages are not enabled and must be specifically configured.

Note

PL/Perl is not available on web20 or less or dweb6 through dweb17.

PL/Python is not a trusted procedural language and cannot be enabled on your server’s shared PostgreSQL database.

To enable PL/pgSQL, PL/Tcl, or PL/Perl:

  1. Open an SSH session to your account.

  2. Start a shell session for your database. Enter psql -U database_user -d database_name, where database_user is the name of a user with access to a PostgreSQL database of the name database_name, and press Enter. A password prompt appears.

  3. Enter the password for the database user and press Enter.

  4. Enable the procedural language.

    • To enable PL/pgSQL, enter create language plpgsql; and press Enter.
    • To enable PL/Perl, enter create language plperl; and press Enter.
    • To enable PL/Tcl, enter create language pltcl; and press Enter.

    CREATE LANGUAGE appears in the shell.

  5. End your database shell session. Enter \q and press Enter.

The procedural language you selected is enabled. For more information on extending PostgreSQL with procedural languages, please see the PostgreSQL Manual chapter entitled Procedural Languages.