|
|
|
Support sites: Documentation | Forum | Control panel | Status blog | Tickets
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.
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.
To create a database with the control panel:
Log into the control panel.
Click Databases ‣ Create a new database. The > Home > Databases > Add page appears.
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.
Click to select MySql or PostgreSql from the type menu.
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.
Click the Create button.
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.
To change a database password with the control panel:
Log into the control panel.
Click Databases ‣ Change database password. The Home > Change database user password page appears.
Click to select the database to change the password for from the Database menu.
Enter the new password in the New database password field.
Reenter the new password in the Confirm password field.
Click the Change button.
Wait while the password is updated. When the password record has been updated, the page will automatically refresh with a confirmation.
WebFaction provides two tools, phpMyAdmin and phpPgAdmin to simplify many common database administration tasks and provide web-based access to your databases.
To access phpMyAdmin from the control panel:
Log into the control panel.
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.
Enter the database name (including leading username and underscore, if applicable) in the Username field.
Enter the database password in the Password field.
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.
To access phpPgAdmin from the control panel:
Log into the control panel.
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.
Click Servers on the right side of the page. A server list page will appear.
Click PostgreSQL in the list.
Enter the database name (including leading username and underscore, if applicable) in the Username field.
Enter the database password in the Password field.
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.
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.
To log in to a MySQL database enter this command:
mysql -u database_name -p database_password
For example, user123 logs in to user123_mysql_db:
user123@webXX ~ $ mysql -u user123_mysql_db -p db_pass
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>
To log into a PostgreSQL database enter this command:
psql -U database_name
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=>
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.
Note
These directions assume MySQL. To connect to a PostgreSQL database, substitute appearances of 3306 with 5432.
Open a terminal session.
Enter ssh -L 3306:localhost:3306 username@webXX.webfaction.com where webXX is your machine name (e.g. web57) and press Enter.
You may be alerted that the authenticity of the host cannot be established. Enter yes and press Enter.
When prompted, enter your password and press Enter.
Leave the SSH session open as long as you want to maintain the tunnel.
You can now connect to your database at localhost, port 3306 using your database name (as username) and password.
Enter exit and press Enter to close the connection.
Start PuTTY.
In the Category tree, click to expand SSH.
In the Category tree, click to select Tunnels.
Enter 3306 in the Source port field.
In the Destination field, enter localhost:3306.
Click the Add button.
In the Category tree, click to select Session.
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.
Enter 22 in the Port field.
Under Connection type, click to select SSH.
Click the Open button.
A PuTTY Security Alert dialog may open. If so, click the Yes button.
When prompted, enter your username and press Enter.
When prompted, enter your password and press Enter.
Leave the PuTTY window open as long as you want to maintain the tunnel.
You can now connect to your database at localhost, port 3306 using your database name (as username) and password.
Enter exit and press Enter to close the connection.
You can also access your databases from scripts or applications. The MySQL and PostgreSQL instances are available as localhost 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='localhost',
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='localhost',
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("localhost", "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);
?>
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.
To export a MySQL database through SSH, run:
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). 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, run:
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). 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.
To import a MySQL database through SSH, run:
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. mysql prompts for the database password.
Note
By default, mysql uses the Latin-1 (ISO 8859-1) character set. However, data exported with mysqldump use the UTF-8 character set by default. If you’re importing a dump file encoded with a character set other than Latin-1, you must use --default_character_set encoding` when invoking mysql. Thus, the complete command for importing a UTF-8 dump file is:
mysql -u database_name -p -D database_name --default_character_set utf8 < file
To import a PostgreSQL database through SSH, run:
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. 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 use this commmand:
pg_restore -U database_name -d database_name file
Regular backups of your data are important. You can schedule a cron job to automatically backup a MySQL or PostgreSQL databse.
To schedule an automatic backup of a MySQL database:
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.
To schedule an automatic backup of a PostgreSQL database:
Now the database will be backed up at 2 a.m. server time each day.
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.
To delete a database with the control panel:
) next to the database you
want to delete. A confirmation dialog will appear.