Installing MySQL on Linux
Contents |
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 echoHaving 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