Using a MySQL database

MySQL is a simple, fast relational database which is often used as a "back end" for web applications. We offer MySQL database facilities with all standard accounts.

You may have been supplied with a MySQL database name, username and password when you set up your account. Typically the database name and username will be the same as your UNIX username, but the password will never be the same as your UNIX password. If you have not been given this login information, then please email support@mythic-beasts.com and we will set up a database for you.

Once you have the database name, username and password, you can connect to the database and check that everything is working:

$ mysql -u username -p database-name
Enter password: enter your password; it won't be echoed
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 396905 to server version: 3.23.x

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Note that, depending on when it was created, your database may be on one of our database servers rather than on the shell server. If so you will have been given the name of the database server, for instance "db1.int.mythic-beasts.com". In that case you should add an appropriate -h option to the above command line:

$ mysql -h db1.int.mythic-beasts.com -u username -p database-name

At this point you can enter SQL commands. There is extensive documentation available on-line.

To use the MySQL database from within a program or script, you will need to make the database name, username and password available in the script. It is normal to put this in a variable or an external included file. You can then use whatever library facilities the language makes available to access the database. For instance, in perl,

my $dbname = 'database-name';
my $dbuser = 'username';
my $dbpass = 'password';

use DBI;

my $dbh = DBI->connect("dbi:mysql:database=$dbname", $dbuser, $dbpass);

or in PHP,

$link = mysql_connect("localhost", "username", "password");
mysql_select_db("database-name", $link);

See documentation for the individual languages for more information. Again, if your database is on a different server, then you will need to have your script connect to that host; consult the language documentation for more details.

Connecting to your database remotely

For security reasons, we don't allow users to directly connect an external tool (for instance running on your desktop PC) to MySQL databases. However, you can achieve the same effect by using an SSH tunnel. To do so you need to forward a port on your local machine (say 13306) to port 3306 on the server which hosts your database, going via sphinx. The UNIX ssh syntax for that would be:

ssh -N -L 13306:db1.int.mythic-beasts.com:3306 sphinx.mythic-beasts.com

(-N means "don't run a command" and -L sets up the port forwarding. If your database is on the shell server itself, then substitute "localhost" for "db1.int.mythic-beasts.com".)

Alternatively, if you're using Windows, grab a copy of plink (the command-line version of PuTTY and use the same syntax as above, substituting "plink" for "ssh".

Having done this you can connect an external program to port 13306 on your local machine to access your database on our server. Note that if you are running the mysql monitor program under Unix, you must specify "-h 127.0.0.1" rather than "-h localhost"—if you use the latter, the program will try to make a connection to a Unix socket on the local machine, rather than a local TCP socket.