1353
Comment: 2PhUof design and policy problems that require joint attention to noise, air quality and climate, [[http://technetnepal.net/members/malaysian_2D00_airline/default.aspx|malaysian airline]]safe landings
|
7118
cat
|
Deletions are marked like this. | Additions are marked like this. |
Line 1: | Line 1: |
2PhUof design and policy problems that require joint attention to noise, air quality and climate, [[http://technetnepal.net/members/malaysian_2D00_airline/default.aspx|malaysian airline]]safe landings, takeoffs and taxiing.1, :-P, [[http://prisonplace.com/members/thai_2D00_airline/default.aspx|thai airline]]growth is correlated with economic growth. Placing inappropriate constraints on aviation, bxcuh, [[http://drowningintechnicaldebt.com/members/continental_2D00_airline/default.aspx|continental airline]]Authority (JAA). In addition to these governmental bodies, the communities around air-, 9465, [[http://cankayabmt.com/members/las_2D00_vegas_2D00_airfare/default.aspx|las vegas airfare]]design, the influence of environmental controls has been growing. Further, there have, fpuil, [[http://codesapien.com/members/cheap_2D00_flight_2D00_to_2D00_vegas/default.aspx|cheap flight to vegas]]can be mitigated. We begin with a review of the local, national and international regulatory, 378, [[http://forum.planetrock.co.uk/members/northwest_2D00_airline/default.aspx|northwest airline]]are technically feasible through basic research (NRC, 2002)., lvgw, [[http://cnug.co.in/members/dog_2D00_airlines/default.aspx|dog airlines]]on Aviation Environment Protection (CAEP) is responsible for setting standards relat-, 8], | #pragma section-numbers off 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 log in to `ssh.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 }}} For technical reasons, you cannot drop a database using the {{{mysql}}} shell (using "DROP DATABASE..."). == Delete a Table == For technical reasons, users at first do not have the privilege to drop their tables. You need to create database tables and then run '''mysql-fixperms'''. Upon command completion, you will have the privilege to drop all tables that existed at the time of running the command. If you are trying to import a database structure into MySQL and your script does an unconditional "drop and re-create" on database tables, the process will of course fail on first attempt. The way to go about it is to either remove unconditional DROP statements from your SQL script, or to create same-named empty tables ("CREATE TABLE ''NAME'' (a INT)") and run '''mysql-fixperms'''. == 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 }}} To learn more about the MySQL shell, review the [[http://dev.mysql.com/doc/refman/5.0/en/|MySQL manual]]. There is a web interface available for managing your databases at https://phpmyadmin.hcoop.net/. Use your database password when logging into it. == 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 o as stated above and do the following. On the system with the original database (remember to replace USER with your username and DATABASE with the name of your database): The "-h" option is for the hostname, which is "mysql" in our case. (The full hostname is `mysql.hcoop.net`.) {{{ mysqldump --skip-add-drop-table -p USER_DATABASE -h mysql> mydatabase.sql scp ./mydatabase.sql USER@mire.hcoop.net:~/ }}} `--skip-add-drop-table` is important since by default no tables are granted the `DROP` command. Finally, import the database using `mire`: {{{ mysql -h mysql -p USER_DATABASE < ~/mydatabase.sql }}} Now that your database has tables, you may grant `DROP` permissions to them using `mysql-fixperms` without any arguments. Now you will be able to `DROP` tables when needed. If for some reason you have a MySQL dump that includes the `DROP` command, then you may run this script on your dumpfile to remove them: {{{ sed 's/DROP TABLE/-- DROP TABLE/g' ~/mydatabase.sql > ~/mydatabase_nodrop.sql }}} (Assuming, of course, that you don't have any jerk users in your database named "[[http://xkcd.com/327/|Robert'); DROP TABLE Students;--]]".) Then import the dumpfile as written above. == Resetting Permissions == Sometimes we change our policy on which permissions users are granted on their databases. If your database has different permissions set than our current defaults, you can run this command line to update the permissions: {{{ dbtool mysql grant DATABASE }}} = PostgreSQL = The following instructions are for PostgreSQL 9.1, supported by navajos and bog. If you are using mire and want to access your PostgreSQL 8.1 database, simply use database `postgres` instead of `postgres-9.1`. An artifact of supporting 8.1 and 9.1 simultaneously is that 9.1 is running on port 5433 instead of 5432. You will need to supply the port when connecting to the database. == Create an Account == To create a PostgreSQL user account, enter the following command on bog.hcoop.net: {{{ dbtool postgres-9.1 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-9.1 createdb DATABASE }}} The database created will be called USER_DATABASE (where USER is your username and DATABASE is the name you provided {{{dbtool}}}). If you would like to specify an alternate text encoding for your database (e.g., UTF8), you can run `dbtool` like this: {{{ dbtool postgres-9.1 createdb DATABASE UTF8 }}} == Delete a Database == Delete a database with the following command: {{{ dbtool postgres-9.1 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 -p PORT 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]]. == Transferring Databases == If you're using PostgreSQL, then you probably already know how to do this. Let us know if that's not the case and you need instructions. = Database quotas = We currently are not enforcing any quotas on database volumes because the amount of data stored is so small, and we've never had issues with users filling up file systems. This may change in the future, but would be like all other quota requests, only used to prevent accidental overcommitting of storage. |
Line 3: | Line 143: |
CategoryHomepage | CategoryMemberManual |
This is the chapter of the MemberManual that describes how to use PostgreSQL and MySQL databases on HCoop servers.
Contents
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 log in to ssh.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
For technical reasons, you cannot drop a database using the mysql shell (using "DROP DATABASE...").
Delete a Table
For technical reasons, users at first do not have the privilege to drop their tables. You need to create database tables and then run mysql-fixperms. Upon command completion, you will have the privilege to drop all tables that existed at the time of running the command.
If you are trying to import a database structure into MySQL and your script does an unconditional "drop and re-create" on database tables, the process will of course fail on first attempt. The way to go about it is to either remove unconditional DROP statements from your SQL script, or to create same-named empty tables ("CREATE TABLE NAME (a INT)") and run mysql-fixperms.
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
To learn more about the MySQL shell, review the MySQL manual.
There is a web interface available for managing your databases at https://phpmyadmin.hcoop.net/. Use your database password when logging into it.
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 o as stated above and do the following.
On the system with the original database (remember to replace USER with your username and DATABASE with the name of your database):
The "-h" option is for the hostname, which is "mysql" in our case. (The full hostname is mysql.hcoop.net.)
mysqldump --skip-add-drop-table -p USER_DATABASE -h mysql> mydatabase.sql scp ./mydatabase.sql USER@mire.hcoop.net:~/
--skip-add-drop-table is important since by default no tables are granted the DROP command.
Finally, import the database using mire:
mysql -h mysql -p USER_DATABASE < ~/mydatabase.sql
Now that your database has tables, you may grant DROP permissions to them using mysql-fixperms without any arguments. Now you will be able to DROP tables when needed. If for some reason you have a MySQL dump that includes the DROP command, then you may run this script on your dumpfile to remove them:
sed 's/DROP TABLE/-- DROP TABLE/g' ~/mydatabase.sql > ~/mydatabase_nodrop.sql
(Assuming, of course, that you don't have any jerk users in your database named "Robert'); DROP TABLE Students;--".)
Then import the dumpfile as written above.
Resetting Permissions
Sometimes we change our policy on which permissions users are granted on their databases. If your database has different permissions set than our current defaults, you can run this command line to update the permissions:
dbtool mysql grant DATABASE
PostgreSQL
The following instructions are for PostgreSQL 9.1, supported by navajos and bog. If you are using mire and want to access your PostgreSQL 8.1 database, simply use database postgres instead of postgres-9.1. An artifact of supporting 8.1 and 9.1 simultaneously is that 9.1 is running on port 5433 instead of 5432. You will need to supply the port when connecting to the database.
Create an Account
To create a PostgreSQL user account, enter the following command on bog.hcoop.net:
dbtool postgres-9.1 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-9.1 createdb DATABASE
The database created will be called USER_DATABASE (where USER is your username and DATABASE is the name you provided dbtool).
If you would like to specify an alternate text encoding for your database (e.g., UTF8), you can run dbtool like this:
dbtool postgres-9.1 createdb DATABASE UTF8
Delete a Database
Delete a database with the following command:
dbtool postgres-9.1 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 -p PORT USER_DATABASE
To learn more about the psql shell, take a look at the PostgreSQL manual.
Transferring Databases
If you're using PostgreSQL, then you probably already know how to do this. Let us know if that's not the case and you need instructions.
Database quotas
We currently are not enforcing any quotas on database volumes because the amount of data stored is so small, and we've never had issues with users filling up file systems. This may change in the future, but would be like all other quota requests, only used to prevent accidental overcommitting of storage.