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 Databases

When you create a database, one database and user is created. The username is the same as the database name; the password is automatically generated and displayed in the database creation confirmation message. While the database username cannot be changed, the password can be updated.

Create a New Database with the Control Panel

To create a database with the control panel:

1. Log into the control panel.

2. Click Databases ‣ Create a new database. The > Home > Databases > Add page appears.

   

3. Click to select a machine from the Machine drop-down. Typically, choose the machine where the application or script which will be using the database is.

4. Click to select MySql or PostgreSql from the type menu.

5. Enter the name of the database in the Name field.

   Note

   The database name must either be your control panel username or begin with your username and an underscore.

   Note

   MySQL database names are limited to 16 characters, including your username.

6. Click the Create button.

7. Wait while your database is created. Once your database is created, the page will reload with a confirmation message and the automatically generated password for that database.

Change a Database Password with the Control Panel

To change a database password with the control panel:

1. Log into the control panel.

2. Click Databases ‣ Change database password. The Home > Change database user password page appears.

   

3. Click to select the database to change the password for from the Database menu.

4. Enter the new password in the New database password field.

   See also

   See Strengthening Passwords for important information about choosing passwords.

5. Reenter the new password in the Confirm password field.

6. Click the Change button.

7. Wait while the password is updated. When the password record has been updated, the page will automatically refresh with a confirmation.

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 from the Control Panel

To access phpMyAdmin from the control panel:

1. Log into the control panel.

2. Click Databases ‣ MySql phpMyAdmin interface. The Welcome to phpMyAdmin 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.

3. Enter the database name (including leading username and underscore, if applicable) in the Username field.

4. Enter the database password in the Password field.

5. Click the Go button to log in.

Note

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

Access phpPgAdmin from the Control Panel

To access phpPgAdmin from the control panel:

1. Log into the control panel.

2. Click Databases ‣ PostgreSql phpPgAdmin interface. 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.

3. Click Servers on the right side of the page. A server list page will appear.

4. Click PostgreSQL in the list.

5. Enter the database name (including leading username and underscore, if applicable) in the Username field.

6. Enter the database password in the Password field.

7. Click the Login button to log in.

Note

Attempts to load SQL files larger than 2MB will fail. If you want 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. To use these tools, you must specify the username (database name) with command line options.

MySQL Command Line

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

mysql -u database_name -D database_name -p

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

For example, user123 logs in to user123_mysql_db:

user123@webXX ~ $ mysql -u user123_mysql_db -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 database_name

where database_name is the name of the database as it appears on the control panel and press Enter. When prompted for a password, enter the database password.

For example, user123 logs in to user123_postgres_db:

user123@webXX ~ $ psql -U user123_postgres_db
Password for user user123_postgres_db:
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_postgres_db=>

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 using your database name (as username) and password.

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. Enter 3306 in the Source port field.

   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.

   

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). This should be the same value as the Destination field in step 5.

9. Enter 22 in the Port field.

10. Under Connection type, click to select SSH.

11. Click the Open button.

    

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 using your database name (as username) and password.

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_mysql_db',
                     passwd='averysecurepassword',
                     db='user123_mysql_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='user123_pg_db',
                      user='user123_pg_db',
                      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_mysql_db", "averysecurepassword");
@mysql_select_db("user_123_mysql_db") or die("Unable to select database");
$result = mysql_query("SELECT * FROM names");
mysql_close();
echo($result);
?>

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 500 MB.

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_name -f dump.sql and press Enter.

Export

To export a MySQL database through SSH, enter:

mysqldump -u database_name database_name -p > dump.sql

where database_name is the name of the MySQL database, including your username and underscore (if applicable), and press Enter. mysqldump will prompt for the password configured for that database. The contents of the database are exported to dump.sql in the directory where the command was run.

To export a PostgreSQL database through SSH, enter:

pg_dump -U database_name -f dump.sql

where database_name is the name of the PostgreSQL database, including your username and underscore (if applicable), and press Enter. pg_dump prompts for the database password. The contents of the database are exported to dump.sql in the directory where the command was 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 through SSH, enter:

mysql -u database_name -p -D database_name < file

where database_name is the name of the MySQL database, including your username and underscore (if applicable) and file is the path to the file containing the MySQL dump, 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 --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_name -p -D database_name --default_character_set encoding < file

To import a PostgreSQL database through SSH, enter:

psql -U database_name database_name < file

where database_name is the name of the PostgreSQL database, including your username and underscore (if applicable) and file is the path to the file containing the PostgreSQL dump, 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_name -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 databse.

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. 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.

   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_name database_name > $HOME/db_backups/database_name-`date +\%Y\%m\%d`.sql 2>> $HOME/db_backups/cron.log where

   where database_name is the name of the database as it appears on the control panel (for example, username_db).

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 will be backed up at 2 a.m. server time 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_name:password where database_name is the name of the database as it appears on the control panel (for example, username_db) and password is the database password.
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_name database_name > $HOME/db_backups/database_name-`date +\%Y\%m\%d`.sql 2>> $HOME/db_backups/cron.log.

Now the database will be backed up at 2 a.m. server time 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:

1. Log into the control panel.
2. Click > Databases ‣ List databases. The database list page loads.
3. Click the Delete button () next to the database you want to delete. A confirmation dialog will appear.
4. Click the OK button to delete the database. The database list page reloads without the deleted database and with a confirmation message.

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 -d database, where database is the name of the database to be configured, and press Enter. A prompt appears: Password for user database.

3. Enter the password for the database 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 now enabled. For more information on extending PostgreSQL with procedural languages, please see the PostgreSQL Manual chapter entitled Procedural Languages.