hk hosting . instructions . mysql

mysql

MySQL is a very powerful and popular SQL server software. You can use PERL with DBD/DBI modules, PHP, or Java Servlets with JDBC to communicate with your database. You can read more about it in their official documentation.

Below is the instruction for people who have selected the option of their own MySQL server i.e. MySQL installed under the home directory. A shared MySQL server is freely available for CGI and Mega package users. Please contact us to create a database for you and we'll tell you the access information.

(a) Getting Started

1. Files

You will have a file called ".my.cnf" in your home directory. This is necessary for MySQL to place its socket in your home directory, and listen on an unused port. It will be assigned for you and shouldn't be changed, but you should know what it is. You will also have a file called ".boot" in your home directory, it will start MySQL automatically if the system is rebooted. The MySQL data is kept in "(home)/mysql/var", and program files in "(home)/mysql/bin".

2. Stopping the database

Type "ps -ef|grep `whoami`" to see your list of running processes. Then, type "kill (pid)", replacing (pid) with the number in the first column of each line. An example is:

xx   402     1  0   Jan 01 ?    0:00 /bin/sh mysql/bin/safe_mysqld
xx   451   402  0   Jan 01 ?    8:26 /(home)/mysql/libexec/mysqld

You type: "kill 402 451"

3. Starting the database

Go to your home directory ("cd ~"), and type "mysql/bin/safe_mysqld &". It should let you know that it's now running.

4. Connecting to your database

The program "(home)/mysql/bin/mysql" is used to talk to your database in command-line mode. Assuming you are in the "(home)/mysql/bin" directory, you type:
"mysql @localhost" - connects as anonymous, won't ask for password
"mysql -u root" - connects as root, won't ask for password
"mysql -u root -p" - connects as root, asks for password

5. Setting the "root" password

First, know the machine name by typing the unix command "uname -n". Once connected as root, you should assign a password to root. Type:
SET PASSWORD FOR root@localhost = PASSWORD('biscuit');
SET PASSWORD FOR root@hostname = PASSWORD('biscuit'); FLUSH PRIVILEGES;
replacing the password biscuit with your own, and the hostname with the output of "uname -n".

6. Starting over again

If you have forgotten the root password or have hopelessly messed things up, you can erase the databases and create them again. First, stop the database. Then, erase the mysql.* files in "(home)/mysql/var" (they contain username/password information). Run "(home)/mysql/bin/mysql_install_db" to re-create the missing mysql database and tables. You can now start the database again and set the root password.

7. Showing data

To list all databases, type "show databases;". The ";" character ends all commands. If you forget to type it, you will get a "->" prompt asking you to continue your command line. You will start out with two databases, mysql and test. To go into the mysql database, type "connect mysql;". You can then type "show tables;" to list all tables in the database you're connected to. To show the actual data inside a table, type "select * from tablename;".

8. Creating/deleting databases

This is done by the "create database dbname;" and "drop database dbname;" commands.

(b) Using Your Database

1. PERL

You will need to add the following to the DSN: ;mysql_socket=(home)/mysql.sock
The full DSN should look like this:
$dsn = "DBI:mysql:database=test;mysql_socket=(home)/mysql.sock";
Then, you typically connect like this:
$dbh = DBI->connect($dsn, $db_user, $db_password);

2. PHP

With PHP, you need to specify the TCP port used by your database. An example: $conn=mysql_connect("127.0.0.1:12345");

instructions to users