Some people are born lucky; they install a piece of software without a hitch and the appliation does exactly what they expect. Most people find a couple of snags and have to get expert help to resolve them. Finally, some unlucky people are hit by Murphy's law that if something can go wrong it will go wrong and will fall into all possible traps, with nobody willing to help them [neuroscientists have suggested that in some people there is a slight synchronization problem between the local clocks in different brain areas and that this causes an unconscious aversion from fellow humans].

Here is a map trying to steer around the land mines. Read this when all your options are exhausted and you are ready to jump out of the window. The system used is HP-UX B.10.20 A 9000/782

Documentation

The installation is different depending on the platform and on whether you install from source or binaries. Also the code evolves in time and information gets quickly dates. Follow the instructions in the correct order, namely latest to oldest:

• Read the messages displayed on the console
• Read the readme files
• Read the html file manual_toc.html in the installation directory
• Read http://www.mysql.com/articles/mysql_intro.html
• Read a book. The Dubois book is the one recomended by the Direktor
• For the official MySQL tutorial see http://mysql.he.net/documentation/mysql/bychapter/manual_Tutorial.html

Note that the directory structure is different depending on whether you install from binaries or from source files, so read very carefully to which case each explanation applies. "Regarding different directories in binary and source distros, there is no way of making it otherwise as source distro contains source files from which binaries are built."

Pre-installation

Make sure you have all required patches. In our case the system had all recommended patches, but three patch packages that fixed a fatal bug for MySQL where not part of the recommended list in our company. Use the search engines to find war stories of installing MySQL on a system like yours. Get the patches from the software vendor directly, because they will have updated information of dependencies & conflicts between patches (you cannot just install all patches).

Installation of a package generally happens in four steps. First you run a Perl script that generates a make file for your system, then you run the makefile and run a test on the new system. Finally, you run an installer that integrates the new software in the existing system.

Before you start, make sure you have the latest version of Perl and test it to make sure it is installed correctly. Install the database modules for Perl and MySQL. These modules must be installed in the correct order, otherwise incorrect information is generated by the makefile generation script and the scripts will not find required modules.

Finding out the machine size

MySQL needs to appropriately configure its working set depending on the RAM on the machine. To find out how much physical memory you have, log in as superuser and use command

/etc/dmesg

The RAM memory is the last lne of the output, which in our case is 262144 Kbytes. Now go to the directory with the config files

cd /usr/local/mysql/support-files

and pick the closest configuration file, which in this case is my-medium.cnf and copy it to /etc/my.cnf, i.e.,

cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf

Starting and Stopping

Start the database server as superuser with

# cd /usr/local/mysql; ./bin/safe_mysqld --user=mysql &

That script can only run from the mysql directory, just giving the full path for the script is not sufficient. Shut down the database server as superuser with

# /usr/local/mysql/bin/mysqladmin -p shutdown

Passwords and users

Changing the access configuration file as in the O'Reilly book locks up the installation (to recover edit again the cnf file, re-create the grant tables completely, removing all the `.frm', `.MYI', and `.MYD' files in the directory containing the mysql database. (This is the directory named `mysql' under the database directory, which is listed when you run mysqld --help.) Then run scripts/mysql_install_db, and kill the mysqld process, which will be automatically restarted by the safe_mysqld demon. The correct method to set up the root password is

./bin/mysqladmin -u root -p password 'new-password'

./bin/mysqladmin -u root -h chroma -p password 'new-password'

A password starting with a number will not work.

To add a new user, start mysql without specifying a database, then enter the command

mysql>GRANT usage ON *.* TO newUser@localhost IDENTIFIED BY 'password';

Backup and reload or transfer

MySQL is often used with Perl and PHP. This are scripting systems and easily mess up the system. You cannot avoid them when they are used by administration tools, but in your own developement you should use a real programming language and be very religious in catching all exceptions cleanly and dealing with them appropriately.

When the scripted administration tools fail and mess up your system, you need to recover. Because either the entire server or just one database can be ruined, it is best to do two back-ups: all databases and each database individually. Put the backup in a path than is on your regular backup path, not the MySQL directory, which you may lose.

Command to backup all databases:

mysqldump -u root -p --all-databases > ~berettag/Backup/all_databases.sql

Note that the --opt option is not used. This is because the MySQL just goes catatonic if it is used on more than one database. Killing the command messes up MySQL's directory structure information and you need to reinstall it. Backing up just one database:

mysqldump -u root -p --opt location > ~berettag/Backup/location.sql

Was not able to get the mysqlhotcopy script to work. It starts up, creates a new file, and then just sits there.

Reading back a database in MySQL:

mysql -u root -p location < location.sql

Comatose MySQL

Sometimes MySQL loses its socket. If you get the message

ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (239)

and it does not exist, do a ps -e to find the mysqld demon and kill it. Do a ps again to see if safe_mysqld was able to recover, otherwise restart it manually. In the latter case something might have gone very wrong and you should check very carefuly all aspects of the system still work. When this is not the case, read on.

Sometimes MySQL goes catatonic when it tries to access any database. Kill it with ^C, do a shutdown, kill the mysqld demon and restart it. If the demon is not there, check the error log for your local host, as in

tail /usr/local/mysql/data/benchath.err

or

tail /usr/local/mysql/data/chroma.err

In one instance we got the message

/usr/local/mysql/bin/mysqld: Can't find file: './mysql/host.frm' (errno: 13)

actually, the path for that file is

./data/mysql/host.frm

This means that some index sequential access method (ISAM) tables are corrupted or missing. In this case there is hope. As superuser, edit the safe_mysqld script. About half-way in the file there is a comment

# Uncomment the following lines if you want all tables to be automaticly

# checked and repaired at start

Uncomment the three lines after the comment and save the script leaving it as is for the time being. You are an unlucky gal or guy and those lines will fix up the ISAM files each time you start again.

Using MySQL

You access MySQL from your regular user login, you are not Unix root. To create a databse, you log into MySQL as root with the command

mysql -u root -p

and use command SHOW DATABASES; to see what is already there. Type CREATE DATABASE foo; and then grant yourself access with GRANT ALL ON foo.* TO bar;. Finally quit, and log into MySQL as yourself. Now you can set up the tables as follows:

CREATE TABLE PhotoCD (id CHAR(14) NOT NULL PRIMARY KEY, scanDate DATE, packs TINYINT UNSIGNED, originalType SMALLINT UNSIGNED, productType SMALLINT UNSIGNED, profile SMALLINT UNSIGNED, comment VARCHAR(255));

This creates the following table:

mysql> describe PhotoCD;
+--------------+----------------------+------+-----+---------+-------+
| Field        | Type                 | Null | Key | Default | Extra |
+--------------+----------------------+------+-----+---------+-------+
| id           | varchar(14)          |      | PRI |         |       |
| scanDate     | date                 | YES  |     | NULL    |       |
| packs        | tinyint(3) unsigned  | YES  |     | NULL    |       |
| originalType | smallint(5) unsigned | YES  |     | NULL    |       |
| productType  | smallint(5) unsigned | YES  |     | NULL    |       |
| profile      | smallint(5) unsigned | YES  |     | NULL    |       |
| comment      | varchar(255)         | YES  |     | NULL    |       |
+--------------+----------------------+------+-----+---------+-------+