Computer Laboratory

Installing MySQL on Linux

Introduction

This page describes how to install and run MySQL server and clients on Lab managed Linux machines.

This page is not meant to be comprehensive - it gives just the basics to get a server running. To do anything more than that you will need to read the system manual pages, installed with the packages. The relevant ones are

mysqld_safe Starting the MySQL daemon. before MySQL 4.0 it was called safe_mysqld
mysqld The database server daemon
mysqladmin A tool for doing administrative things like creating databases and reloading privilege tables.
mysqldump Client for dumping or backing up databases.
mysql The SQL command line client.

There are four stages detailed below: installing the software, initialising the database directory, creating the database and setting user privileges. The final sections give some simple examples to allow you to test the setup using the SQL command line interpreter and offer some hints on backing up databases.

The commands in pink boxes are designed to be copied and pasted from this web page, in order. After running each command, carefully check the output. If something goes wrong, stop and try to understand what and why. Only continue when you are sure you know what you are doing, otherwise you may get into deeper difficulties. Mail unix-admin@cl.cam.ac.uk for help if you cannot easily understand a problem. When you mail, explain what you are trying to do, on which machine, and what the problem is. Copy output from commands that fail. The better the information you supply the better we are able to help.

Installing software

Below are instructions for installing on Redhat based systems (e.g. Fedora and CentOS). Under SUSE use YaST as usual

Choosing bundles

If you do have permission then add the mysql RPMs or bundles you require. There may be three:

mysql The command-line client, perl DBD driver and Libraries & C language header-files for linking other client programs.
mysql-server The database server.
mysql-python Python language bindings. (may not be available or needed)

For each bundle you require add them using a command like

echo mysql >>/etc/user-config/bundles

If you want to add several you could use

for b in mysql mysql-server mysql-python
   do echo $b >>/etc/user-config/bundles
done

The commands above should not give any error messages. If they do give errors it is probably because you do not have sufficient permissions. To install bundles will need to be the main user of the machine and registered as such. Then you will have permission to edit the file /etc/user-config/bundles. If you do not have appropriate permissions then e-mail unix-admin@cl.cam.ac.uk or, if it is not your machine, you will need to ask the registered user to install the software for you.

Triggering the install process

The next step is to run the system command to update the installed packages. For recent systems this is

cl-update-system

For older systems, use

start-system-update --fixpkg

Check the resulting output. If there are errors then e-mail unix-admin@cl.cam.ac.uk. Tell them what you did and include a transcript of what occurred.

Creating and Initialising the Database Directory

Databases work best on local disk, which means scratch space on Lab hosts. Remember SCRATCH SPACE IS NOT BACKED UP and you risk losing the data unless you regularly make backups yourself.

If you have only a small amount of data you can use tar to copy it to your home directory (after stopping the server.)

Creating the directory

Assuming you are going to use scratch space, if you have not already created one, create a scratch directory using the command

sudo cl-mkscratchdir

Next, set an environment variable to point to where you want to keep the data, and create the directory

export DIR=/local/scratch/$USER/mysql-data
mkdir $DIR

Here $USER is your user-id. You should not need to set this variable - it is set by default.

Creating a MySQL configuration file

Create ~/.my.cnf to direct the server to use $DIR for its database files:

cat >~/.my.cnf <<EOT
[mysqld]
datadir=$DIR
socket=$DIR/mysql.sock

[client]
socket=$DIR/mysql.sock
EOT

Initialising the database

Checking security levels on old systems

If you are using a machine running Fedora Core 2 or 3 then it may have selinux security settings which prevent the database service from running under user accounts. Check the selinux configuration using the following command

getenforce

This should output permissive or disabled. If it doesn't then e-mail unix-admin@cl.cam.ac.uk. Tell them which machine you are using, that you want to run a MySQL database and that you need selinux set to permissive mode to do so.

Running mysql_install_db

Now run the command

/usr/bin/mysql_install_db --datadir=$DIR

This created the basic directory structure for storing databases. The output should look something like this:

bann:~$ /usr/bin/mysql_install_db --datadir=$DIR
Preparing db table
Preparing host table
Preparing user table
Preparing func table
Preparing tables_priv table
Preparing columns_priv table
Installing all prepared tables
050425 12:23:08  /usr/libexec/mysqld: Shutdown Complete


To start mysqld at boot time you have to copy support-files/mysql.server
to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
This is done with:
/usr/bin/mysqladmin -u root  password 'new-password'
/usr/bin/mysqladmin -u root -h bann.cl.cam.ac.uk  password 'new-password'
See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory:
cd sql-bench ; run-all-tests

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at 
http://www.mysql.com
Support MySQL by buying support/licenses at https://order.mysql.com

bann:~$ 

Setting the database superuser passwords

MySQL maintains a set of database user-id's that are independent of the Unix user Id's though they can correspond if you choose them to. The typical name of the database super-user is root. The first task is to set a password for the root user to secure the server.

To do this you must first start the server with an unsecured root account:

mysqld_safe --defaults-file=~/.my.cnf &

Or

/usr/bin/mysqld_safe --pid-file=$DIR/mysqld.pid \
                      --err-log=$DIR/error.log &

Then (quickly!) set a root password. This is done by issuing a command with the root password in the command line options. To make this a little more secure, first load the root password into an environment variable so that it doesn't get saved in your shell history file and doesn't appear on the screen:

read -s -p 'Password for root : ' RPASSWD ; echo

Or

echo -n 'Password for root : '; stty -echo && read RPASSWD ; echo ; stty echo
Having entered the password blind, it should be stored in the variable $RPASSWD.

Now run mysqladmin to set the root password for all hosts the database could be accessed from, (presumably just the local machine.)

/usr/bin/mysqladmin -u root  password "$RPASSWD"
/usr/bin/mysqladmin -u root -h $(hostname)  password "$RPASSWD"

Creating databases

In this step you create databases for each user and set their privileges. Here we show how to create just one database for $USER (i.e. you.)

As you did for the root password, first load the password into an environment variable:

read -s -p 'Password for $USER : ' UPASSWD ; echo

Or

echo -n "Password for $USER: "; stty -echo && read UPASSWD ; echo ; stty echo

Next, run mysqladmin to create the database, calling it $USER:

/usr/bin/mysqladmin --user=root --password="$RPASSWD" create $USER

Now set privileges:

cat <<EOT | /usr/bin/mysql --user=root --password="$RPASSWD" mysql
GRANT ALL PRIVILEGES ON $USER.* TO '$USER'@'localhost'
    IDENTIFIED BY '$UPASSWD' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON $USER.* TO '$USER'@'%'
   IDENTIFIED BY '$UPASSWD' WITH GRANT OPTION;
EOT

Finally, use mysqladmin to force the server to reload its privilege cache from the tables:

/usr/bin/mysqladmin --user=root --password="$RPASSWD" reload

Running the mysql client to check the install

/usr/bin/mysql --password="$UPASSWD"

Here is an example session

bann:~$ /usr/bin/mysql --password="$UPASSWD"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 3.23.58

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

mysql> \u ig206
Database changed
mysql> CREATE TABLE text (code INT, description TEXT);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO text VALUES (99, 'Description for code 99');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM text;
+------+-------------------------+
| code | description             |
+------+-------------------------+
|   99 | Description for code 99 |
+------+-------------------------+
1 row in set (0.00 sec)

mysql> \q
Bye
bann:~$ 

Stopping the server

To stop the server

/usr/bin/mysqladmin -p -u root shutdown

and enter the password for root when prompted.

Backing-up Databases

To create an SQL representation of your $USER database, use the mysqldump command (the server must be running.)

DBNAME=$USER
echo -n 'Password: '; stty -echo && read PASSWD ; echo ; stty echo
/usr/bin/mysqldump --user=root --password="$RPASSWD" \
  --opt $DBNAME | bzip2 >$BAK/mysql-data-$DBNAME.asc.bz2

To examine the file use

bzcat $BAK/mysql-data-$DBNAME.asc.bz2 | less

You can also back up database files directly after stopping the server:

BAK=/local/scratch/ig206
(cd $DIR/.. && tar -cpjf $BAK/mysql-data.tar.bz2 mysql-data)

Starting MySQL from a cron job

To start the server on reboot, run a cron job when crond starts that checks if the server is required, and if it is, starts it if it is not already running. To create the script run these commands:

cat <<EOT >>$DIR/../start-mysql
#! /bin/sh
DIR=$DIR
if [ ! -e \$DIR/nomysql ] ; then
   /bin/ps -p \$(cat \$DIR/mysqld.pid 2>/dev/null) >/dev/null 2>&1 || \\
      (/usr/bin/mysqld_safe \\
            --defaults-file=$HOME/.my.cnf \\
            --pid-file=\$DIR/mysqld.pid \\
            --err-log=\$DIR/error.log &)
fi
EOT
chmod +x $DIR/../start-mysql

To make a crontab entry run these commands:

cat <<EOT | crontab -
$(crontab -l | grep -v '^#')
@reboot $DIR/../start-mysql
EOT

This will try to restart the server whenever the machine boots. If you want the server to not be started, create the file $DIR/nomysql:

touch $DIR/nomysql