CS:Database

From Wiki

There are many reasons to want to use different databases. One of the most simple would be to test queries for a class (i.e. a database class). For things like this, I would recommend using sqlite. However, for larger projects (i.e. backends for content management systems) sometimes it is more convenient and preferable to have a full-fledged dbms like mysql running. This section describes some of the different options available.

Running mysql on progeny

Before starting, you may want to consider alternatives to mysql such as sqlite. These options can be better suited to a learning multi-user environment and can be easier to administer.

Because there are many local versions of mysqld running on progeny, this document will help you to get your own started. This document is by no means the only way (or the best way) of accomplishing this task (or a similar task). It's aim is to be easy to follow.

Create a place to put your mysql stuff in your home directory. In this tutorial, we will assume that $HOME is /home/mine and we will create a directory called mysql2

$ cd [enter]
$ mkdir mysql2 [enter]

Next, setup the grant tables. Grant tables give users different permissions. This can be accomplished semi-automatically with the mysql_install_db command. Use the appropriate options.

 $ mysql_install_db --socket=/home/mine/mysql2/mysql.sock --port=3307 \
   --pid-file=/home/mine/mysql2/mysql2.pid --datadir=/home/mine/mysql2 \
   --log-error=/home/mine/mysql2/mysqld2.log --log-bin=/home/mine/mysql2/mysql.logbin [enter]

The port number cannot collide with other port numbers. If you experience a problem, just change the number.

Next, start a version of mysql

$ mysqld_safe --socket=/home/mine/mysql2/mysql.sock --port=3307 \
   --pid-file=/home/mine/mysql2/mysql2.pid --datadir=/home/mine/mysql2 \
   --log-error=/home/mine/mysql2/mysqld2.log --log-bin=/home/mine/mysql2/mysql.logbin [enter]

This takes the same options as the grant tables to specify the version in your home directory.

You should get a mysqld process. If not, something is wrong. Check your log file you specified. Chances are, you typed in something wrong or you picked a port that's already in use (If this is the case, just pick another port). See the links below for more troubleshooting.

Now, to start a client, type:

$ mysql --socket=/home/mine/mysql2/mysql.sock -u root

You should get a prompt with the > sign.

Now it's important to create a password (at least for the root user). This can be accomplished by connecting to yourmysql server as root. If you do not do this anyone on progeny could potentially connect as root to your database.

$ mysql --user=root –socket=/home/mine/mysql2/mysql.sock 
> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');

You can, of course, put all this in a configuration file that runs whenever you first login (which I recommend if you plan on using mysql a lot).

You should now be ready to go. At the mysql prompt, you can type SQL commands. I recommend starting with the mysql tutorial.

Links to Additional Information

http://dev.mysql.com/doc/refman/5.0/en/multiple-servers.html

http://dev.mysql.com/doc/refman/5.0/en/multiple-unix-servers.html

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html