welcome: please sign in

Diff for "MemberManual/Databases"

Differences between revisions 2 and 15 (spanning 13 versions)
Revision 2 as of 2007-10-27 05:17:03
Size: 2685
Comment: add content... maybe I've created too many sections?
Revision 15 as of 2007-12-07 04:08:23
Size: 4163
Comment: add instructions about mysql database transfer
Deletions are marked like this. Additions are marked like this.
Line 7: Line 7:
== MySQL == = Introduction =

We use a custom tool called {{{dbtool}}} to handle many of the aspects of creating and deleting databases for MySQL and PostgreSQL.

= MySQL =

== Create an Account ==
Line 10: Line 16:
Run the following command:{{{dbtool mysql adduser}}}. You will be asked for a password. The user created will be the same as your log-in username. Run the following command:
Line 12: Line 18:
=== Creating Databases ===
{{{dbtool}}} is also used to create new MySQL databases. Replace DATABASE with your desired database name: {{{dbtool mysql createdb DATABASE}}}. The database created will be USER_DATABASE (where USER is your username and database is the name you provided {{{dbtool}}}).
{{{
dbtool mysql adduser
}}}
Line 15: Line 22:
=== Deleting Databases ===
Using {{{dbtool}}}, you may drop a specified database. Replace DATABASE with your desired database: {{{dbtool mysql dropdb DATABASE}}}.
You will be asked for a password. The user created will be the same as your log-in username.
Line 18: Line 24:
=== Changing Your Password ===
If you need to change your MySQL password for security purposes or you have forgotten it, you may do so with this command: {{{dbtool mysql passwd}}}. You will be prompted to input a new password.
== Create a Database ==
{{{dbtool}}} is also used to create new MySQL databases. Replace DATABASE with your desired database name:
Line 21: Line 27:
=== Accessing Databases ===
For web applications, set the MySQL host/server to {{{mysql}}}. Default ports apply. Follow your software package's instructions.
{{{
dbtool mysql createdb DATABASE
}}}
Line 24: Line 31:
In addition, you can easily use the {{{mysql}}} shell to manipulate or analyze your databases. Replace USER with your username and DATABASE with the name you gave {{{dbtool}}} to create it: {{{ mysql -p -h mysql USER_DATABASE }}} The database created will be USER_DATABASE (where USER is your username and DATABASE is the name you provided {{{dbtool}}}).
Line 26: Line 33:
== PostgreSQL ==
To create a PostgreSQL user account, enter the following command on mire.hcoop.net: {{{dbtool postgres adduser}}}. You will not be prompted for a password since PostgreSQL utilizes a security model that is quite different from MySQL.
== Delete a Database ==
Delete a database with the following:
Line 29: Line 36:
=== Creating Databases ===
Additionally, {{{dbtool}}} is used to create new MySQL databases. Replace DATABASE with your desired database name: {{{dbtool postgres createdb DATABASE}}}. The database created will be called USER_DATABASE (where USER is your username and database is the name you provided {{{dbtool}}}.
{{{
dbtool mysql dropdb DATABASE
}}}
Line 32: Line 40:
== Changing Your Password ==
If you need to change your MySQL password for security purposes or you have forgotten it, you may do so with this command:
Line 33: Line 43:
=== Deleting Databases ===
Using {{{dbtool}}}, you may also drop specified databases. Replace DATABASE with your desired database: {{{dbtool postgres dropdb DATABASE}}}.
{{{
dbtool mysql passwd
}}}
Line 36: Line 47:
=== Accessing Databases ===
For web applications, set the PostgreSQL host/server to {{{postgres}}}. Default ports apply. Follow your software package's instructions.
You will be prompted to input a new password.
Line 39: Line 49:
It is very easy to access your database using a PostgreSQL shell. Replace USER with your username and DATABASE with the database name you gave {{{dbtool}}}: psql -h postgres USER_DATABASE == Accessing Databases ==
For web applications, set the MySQL host/server to {{{mysql}}}. Default ports apply. Use your HCoop username. Your password will be required. Please safeguard it. Remember that your database is really named USER_DATABASE, where DATABASE is the name you originally gave {{{dbtool}}}. Follow your software package's instructions.

In addition, you can easily use the {{{mysql}}} shell to manipulate or analyze your databases:

{{{
mysql -p -h mysql USER_DATABASE
}}}

For security reasons, you cannot drop a database using the {{{mysql}}} shell.

To learn more about the MySQL shell, review the [http://dev.mysql.com/doc/refman/5.0/en/ MySQL manual].

== Transferring Databases ==
If you have a MySQL database on another system and you wish to put that database onto our systems, you'll have to create the database on `mire` as stated above and do the following:

On the system with the original database:
{{{
mysqldump -p USER_DATABASE > mydatabase.sql
scp ./mydatabase.sql mire.hcoop.net:~/
}}}

On the system that you wish to transfer the database to (i.e., mire):
{{{
mysql -h mysql -p USER_DATABASE < mydatabase.sql
}}}

If you receive errors that the `DROP` command cannot be used, you will have to comment out all DROP commands. There is also another way, using `mysql-fixperms` but I have no idea how that works.

= PostgreSQL =

== Create an Account ==
To create a PostgreSQL user account, enter the following command on mire.hcoop.net:

{{{dbtool postgres adduser}}}

You will not be prompted for a password since PostgreSQL utilizes a security model that is quite different from MySQL. The user created will be the same as your log-in username.

== Create a Database ==
Additionally, {{{dbtool}}} is used to create new Postgres databases. Replace DATABASE with your desired database name:

{{{
dbtool postgres createdb DATABASE
}}}

The database created will be called USER_DATABASE (where USER is your username and DATABASE is the name you provided {{{dbtool}}}).

== Delete a Database ==
Delete a database with the following command:

{{{
dbtool postgres dropdb DATABASE
}}}

For security reasons, you cannot drop a database using the {{{psql}}} shell.

== Accessing Databases ==
For web applications, set the PostgreSQL host/server to {{{postgres}}}. Default ports apply. Use your HCoop username. A password is not required. Remember that your database is really named USER_DATABASE, where DATABASE is the name you originally gave {{{dbtool}}}. Follow your software package's instructions.

It is very easy to access your database using a PostgreSQL shell:

{{{
psql -h postgres USER_DATABASE
}}}

To learn more about the {{{psql}}} shell, take a look at the [http://www.postgresql.org/docs/8.1/interactive/index.html PostgreSQL manual].

This is the chapter of the MemberManual that describes how to use PostgreSQL and MySQL databases on HCoop servers.

TableOfContents

Introduction

We use a custom tool called dbtool to handle many of the aspects of creating and deleting databases for MySQL and PostgreSQL.

MySQL

Create an Account

Creating a MySQL user account is an easy task with dbtool. It is available for use when you login to mire.hcoop.net.

Run the following command:

dbtool mysql adduser

You will be asked for a password. The user created will be the same as your log-in username.

Create a Database

dbtool is also used to create new MySQL databases. Replace DATABASE with your desired database name:

dbtool mysql createdb DATABASE

The database created will be USER_DATABASE (where USER is your username and DATABASE is the name you provided dbtool).

Delete a Database

Delete a database with the following:

dbtool mysql dropdb DATABASE

Changing Your Password

If you need to change your MySQL password for security purposes or you have forgotten it, you may do so with this command:

dbtool mysql passwd

You will be prompted to input a new password.

Accessing Databases

For web applications, set the MySQL host/server to mysql. Default ports apply. Use your HCoop username. Your password will be required. Please safeguard it. Remember that your database is really named USER_DATABASE, where DATABASE is the name you originally gave dbtool. Follow your software package's instructions.

In addition, you can easily use the mysql shell to manipulate or analyze your databases:

mysql -p -h mysql USER_DATABASE

For security reasons, you cannot drop a database using the mysql shell.

To learn more about the MySQL shell, review the [http://dev.mysql.com/doc/refman/5.0/en/ MySQL manual].

Transferring Databases

If you have a MySQL database on another system and you wish to put that database onto our systems, you'll have to create the database on mire as stated above and do the following:

On the system with the original database:

mysqldump -p USER_DATABASE > mydatabase.sql
scp ./mydatabase.sql mire.hcoop.net:~/

On the system that you wish to transfer the database to (i.e., mire):

mysql -h mysql -p USER_DATABASE < mydatabase.sql

If you receive errors that the DROP command cannot be used, you will have to comment out all DROP commands. There is also another way, using mysql-fixperms but I have no idea how that works.

PostgreSQL

Create an Account

To create a PostgreSQL user account, enter the following command on mire.hcoop.net:

dbtool postgres adduser

You will not be prompted for a password since PostgreSQL utilizes a security model that is quite different from MySQL. The user created will be the same as your log-in username.

Create a Database

Additionally, dbtool is used to create new Postgres databases. Replace DATABASE with your desired database name:

dbtool postgres createdb DATABASE

The database created will be called USER_DATABASE (where USER is your username and DATABASE is the name you provided dbtool).

Delete a Database

Delete a database with the following command:

dbtool postgres dropdb DATABASE

For security reasons, you cannot drop a database using the psql shell.

Accessing Databases

For web applications, set the PostgreSQL host/server to postgres. Default ports apply. Use your HCoop username. A password is not required. Remember that your database is really named USER_DATABASE, where DATABASE is the name you originally gave dbtool. Follow your software package's instructions.

It is very easy to access your database using a PostgreSQL shell:

psql -h postgres USER_DATABASE

To learn more about the psql shell, take a look at the [http://www.postgresql.org/docs/8.1/interactive/index.html PostgreSQL manual].

MemberManual/Databases (last edited 2021-11-06 19:46:06 by ClintonEbadi)