Install and Configure MySQL as a non-root user on Linux

Recently, I want to install MySQL on a server in my lab. It should not be difficult considering the wide usage of MySQL. I must install MySQL as a non-root user. This results in difficulties of installation.
My target OS is Red Hat Enterprise Linux Server release 5.2.
My username is gerald and my home directory is /home/gerald.

MySQL website provides a binary package(rpm) for RedHat. I downloaded package MySQL-server-community-5.0.67-0.rhel5.i386.rpm.
After executing command
> rpm -q -p MySQL-server-community-5.0.67-0.rhel5.i386.rpm -i
I got following output:

Name        : MySQL-server-community       Relocations: (not relocatable)
Version     : 5.0.67                       Vendor: MySQL AB
Release     : 0.rhel5                      Build Date: Mon 04 Aug 2008 03:31:42 PM EDT
Install Date: (not installed)              Build Host: blade11.mysql.com
Group       : Applications/Databases       Source RPM: MySQL-community-5.0.67-0.rhel5.src.rpm
Size        : 42294654                     License: GPL
Signature   : DSA/SHA1, Wed 06 Aug 2008 05:36:22 AM EDT, Key ID 8c718d3b5072e1f5
Packager    : MySQL Product Engineering Team <build@mysql.com>
URL         : http://www.mysql.com/
Summary     : @COMMENT@ for Red Hat Enterprise Linux 5

See the red text above?
So It did not work because the rpm package should be installed as root.

As a result, I must install MySQL from source. Steps:

Installation
(1) Download and untar MySQL-5.1.30. Then Change location into the top-level directory of the unpacked distribution.
(2) Configure
CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti" ; \
./configure --prefix=/home/gerald/share/mysql \
            --enable-assembler \
            --with-mysqld-ldflags=-all-static \
            --with-client-ldflags=-all-static \
            --with-mysqld-user=gerald \
            --with-unix-socket-path=/home/gerald/share/mysql/tmp/mysql.sock \
            --localstatedir=/home/gerald/share/mysql/data
--localstatedir: overrides the default location for database directories (normally /usr/local/var for installation from source)
--with-unix-socket-path: where unix socket file would be stored. (must be accessible)
--with-mysqld-ldflags=-all-static: compile statically linked daemon programs
--with-client-ldflags=-all-static: compile statically linked mysql client programs
--enable-assembler: Use assembler versions of some string functions if available
--prefix: Install architecture-independent files in the specified directory.

Values of some of options above should be replaced with your own paths.
Also this option --enable-thread-safe-client may should be applied in your case if your client programs use multi-thread.
Description of all available configuration options is here http://dev.mysql.com/doc/refman/5.1/en/configure-options.html.
(3) make
(4) make install

Post-installation
http://dev.mysql.com/doc/refman/5.1/en/unix-post-installation.html
(5) Create and edit configuration file (also called option file) my.cnf.
http://dev.mysql.com/doc/refman/5.1/en/option-files.html
My my.cnf file looks like this:

[mysqld]
user=gerald
basedir=/home/gerald/share/mysql
datadir=/home/gerald/share/mysql/data
port=3307
socket=/home/gerald/share/mysql/tmp/mysql.sock

[mysqld_safe]
log-error=/home/gerald/share/mysql/log/mysqld.log
pid-file=/home/gerald/share/mysql/mysqld.pid

[client]
port=3307
user=gerald
socket=/home/gerald/share/mysql/tmp/mysql.sock

[mysqladmin]
user=root
port=3307
socket=/home/gerald/share/mysql/tmp/mysql.sock

[mysql]
port=3307
socket=/home/gerald/share/mysql/tmp/mysql.sock

[mysql_install_db]
user=gerald
port=3307
basedir=/home/gerald/share/mysql
datadir=/home/gerald/share/mysql/data
socket=/home/gerald/share/mysql/tmp/mysql.sock

On Unix, MySQL programs read startup option file from the following files:

Filename	Purpose
/etc/my.cnf	Global options
/etc/mysql/my.cnf	Global options (as of MySQL 5.1.15)
SYSCONFDIR/my.cnf	Global options
$MYSQL_HOME/my.cnf	Server-specific options
defaults-extra-file	The file specified with --defaults-extra-file=path, if any
~/.my.cnf	User-specific options

I use the fourth option. So I set environment variable MYSQL_HOME using this command:
    > export MYSQL_HOME=/home/gerald/share/mysql
This environment variable contains the directory where mysql is installed.
If you don't want to manually set the environment variable every time you start up the mysql daemon, you can add it to file ~/.bash_profile(if you are using bash).
Then copy the option file to the directory specified by MYSQL_HOME.
Note: options specified in my.cnf can also be supplied on the command line when you execute a mysql program.
(6) Change location to the directory where mysql is installed (in my case, it is /home/gerald/share/mysql).
(7) Initialize system tables using command
    >bin/mysql_install_db
Options of this command are specified in our option file $MYSQL_HOME/my.cnf. Of course, you can specify the options in command line. But I think using the option file is more convenient.
I got a warning: [Warning] Ignoring user change to 'gerald' because the user was set to 'mysql' earlier on the command line. I don't know how to fix it now. But mysql seems to work well despite the warning.
Note: If you install MySQL using a binary package, usually this step is automatically done when you install the package.
(8) mkdir log
create log directory under which log files are saved. This must match the configuration of option log-error in my.cnf.
In my case, log-error is set to /home/gerald/share/mysql/log/mysqld.log
(9) Start up mysql using command
    > bin/mysqld_safe &
(10) Verify that mysql daemon runs well. Use any of following commands:

> bin/mysqladmin version
> bin/mysqladmin ping
> bin/mysqladmin status
> bin/mysqladmin variables

Show the created databases
> bin/mysqlshow
Output:
+--------------------+
|      Databases     |
+--------------------+
| information_schema |
| test               |
+--------------------+
> bin/mysqlshow-u root
Output:
+--------------------+
|      Databases     |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
(11) shut down mysql daemon
   > bin/mysqladmin -u root shutdown

Initial Account Security
http://dev.mysql.com/doc/refman/5.1/en/default-privileges.html
By default, password of root user is empty. In other words, anyone who can access the host can also access information of your mysql database. You can set password of root user using
    > mysqladmin -u root password newpwd
or
    > mysqladmin -u root -h host_name password newpwd

Account management statements
http://dev.mysql.com/doc/refman/5.1/en/account-management-sql.html

Privilege system
http://dev.mysql.com/doc/refman/5.1/en/privilege-system.html
Usually, you need to check table mysql.user:
    > select * from mysql.user;
How can you know the indentity used by mysql?
After running mysql successfully, use command
    > select CURRENT_USER();
Set password of a user:
    > SET PASSWORD FOR 'someuser'@'somehost' = PASSWORD('newpass');
Grant privileges:
Usually, commands are like this:

    > GRANT ALL ON mydb.* TO 'someuser'@'somehost';
    > GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';
    > GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost'; 

Revoke privileges:
    > revoke all on mydb.* from 'someuser'@'somehost';
Remove user:
    > drop user 'someuser'@'somehost'

Resources
Related environment variables:
http://dev.mysql.com/doc/refman/5.1/en/environment-variables.html
List of programs/tools in the mysql package:
http://dev.mysql.com/doc/refman/5.1/en/programs.html