This Web page provides instructions on how to setup your environment in the computer science labs to run a mysql database server. For the Windows environment on your personal computer, setup instructions are provided in the course text.
Your web application running in tomcat needs to use an implementation of the Java database API (JDBC) designed to work with MySQL. This implementation is sometimes referred to as the MySQL database driver (or Connector/J). First, go to mysql.org and download the most recent version of Connector/J. Expand the compressed archive that you get and copy the connector/j jar file into the lib folder of your tomcat installation. Tomcat will load the driver classes from this file as you use them in your web application.
Open the file .bashrc and add the following lines.
# mysql
export MYSQL_HOME=/u/faculty/turner/mysql
export PATH=${MYSQL_HOME}/bin:${PATH}
Log out and then log back in again to make the above changes current.
Create a directory to hold configuration files and data.
mkdir ~/mysql cd ~/mysql ln -s $MYSQL_HOME/support-files ln -s $MYSQL_HOME/bin ln -s $MYSQL_HOME/share
Create a mysql configuration file.
cp $MYSQL_HOME/support-files/my-small.cnf ~/.my.cnf
Open the file ~/.my.cnf in a text editor and find the line under the section [client] that sets the socket variable. Because other people can use the machine you are working on, they may not properly shut down the mysql server when they log out and/or shutdown. This results in a zombie file /tmp/mysql.sock, which will block you from starting the server if you use the same name for the socket. To avoid this, embed your name into the name of the socket. For example, in the [client] section of .my.cnf I would use the following:
socket = /tmp/turner_mysql.sock
You need to repeat the above setting under the [mysqld] section. So, under the [mysqld] section of .my.cnf use the following.
socket = /tmp/turner_mysql.sock
For security purposes, you should restrict tcp access to the server to the localhost. To do this, add the following line to the the [mysqld] section.
bind-address = 127.0.0.1
Finally, you need to tell the server the location of your data directory. To do this, add the following line to the the [mysqld] section.
datadir=$HOME/mysql/data
Change to the mysql directory and run the initialization script.
cd ~/mysql $MYSQL_HOME/scripts/mysql_install_db --no-defaults
Run the server in the foreground (so you don't forget to shutdown when logging out).
cd ~/mysql bin/safe_mysqld --log-error=error.log --pid-file=mysql.pid
Set the password for the mysql root user with the following command.
mysqladmin -u root password 'root'
Start the mysql command line client.
mysql -u root -p
Delete unnecessary users.
mysql> use mysql; mysql> delete from user where password = ''; mysql> quit
Shutdown the server.
mysqladmin -u root -p shutdown
Make sure that the mysql server is running by trying to start the mysql command line client as follows.
mysql -u root -proot
Create a database called temp and a user called temp who can access the database
from any host with password temp.
Issue the following commands at the mysql prompt.
create database temp; grant all on temp.* to temp@'%' identified by 'temp';
Check that you can log in as user temp.
To do this, enter the command quit
at the mysql command line prompt,
and then run the following.
mysql -u temp -p -D temp
Alternatively, you can enter the password in the command line as follows.
mysql -u temp -ptemp -D temp
Delete the database by starting the mysql command line client as root and then issuing the following command at the mysql prompt.
drop database temp;
When we started mysqld, we told it to write to a log file called error.log. Open the file in a text editor to examine its contents.
In this section, we will perform an experiment to better understand how to use this log file to debug configuration problems. To do this, perform the following sequence of activities.
~/.my.cnf,
so that the parameter datadir is
incorrectly spelled as xdatadir, and save your changes.