MySQL is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. MySQL is a freely downloadable version of the world’s most popular open source database that is supported by an active community of open source developers and enthusiasts. The MySQL development project has made its source code available under the terms of the GNU General Public License, as well as under a variety of proprietary agreements. MySQL was owned and sponsored by a single for-profit firm, the Swedish company MySQL AB, now owned by Oracle Corporation.

Free-software-open source projects that require a full-featured database management system often use MySQL. Applications which use MySQL databases include: TYPO3, Joomla, WordPress, phpBB, Drupal and other software built on the LAMP software stack. MySQL is also used in many high-profile, large-scale World Wide Web products, including Wikipedia, Google(though not for searches), Facebook, and Twitter.

From last couple of days I am struggling to install mysql-server-5 on Ubuntu 11.10 by apt-get. So finally I decided to install mysql from source.

In this tutorial we will install mysql in /usr/local/mysql directory

Get Packages to compile the source.

sudo apt-get install cmake g++ libncurses5-dev bison libaio-dev

Creating directory to download source file

mkdir /usr/local/src/
cd /usr/local/src

Download the 5.5.19 version of mysql.

wget http://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-5.5/mysql-5.5.19.tar.gz
tar -xvzf mysql-5.5.19.tar.gz

If you want to configure additional options you can run this command and see the various options available.

cd /usr/local/src/mysql-5.5.19
make -LAH > options

Make MySQL

cmake .
make

Create a mysql User and Group
If your system does not already have a user and group for mysqld to run as, you may need to create one.

groupadd mysql
useradd -r -g mysql mysql

Note : The above will create a user that has login permissions to your server. You may wish to disable the account, as the user is only required for ownership purposes, not login purposes. You can do this on Linux by using the -r command-line option.

These commands add the mysql group and the mysql user. install target also provided for Makefile based generators. Installation directory can be controlled using configure-time parameter CMAKE_INSTALL_PREFIX (default is /usr/local. It is also possible to install to non-configured directory, using

make install DESTDIR="/custom/path"

Install in the default /usr/local/mysql directory

make install
make install scripts

Now that mysql is installed lets go about and add some finishing touches before starting the server. Go to the directory where mysql is installed.

cd /usr/local/mysql

Change its ownership to mysql by executing the following commands as root in the installed directory:

chown -R mysql .
chgrp -R mysql .

The first command changes the owner attribute of the files to the mysql user. The second changes the group attribute to the mysql group.

If you have not installed MySQL before, you must create the MySQL data directory and initialize the grant tables:

scripts/mysql_install_db --user=mysql

If you run the command as root, include the –user option as shown. If you run the command while logged in as that user, you can omit the –user option.
The command should create the data directory and its contents with mysql as the owner. After creating or updating the grant tables, you need to restart the server manually. Most of the MySQL installation can be owned by root if you like. The exception is that the data directory must be owned by mysql. To accomplish this, run the following commands as root in the installation directory:

chown -R root .
chown -R mysql data

Now lets test our installation.
To start the MySQL server, use the following command:

bin/mysqld_safe --user=mysql &

If you run the command as root, you must use the –user option as shown. The value of the option is the name of the login account that you created in the first step to use for running the server.
If you run the command while logged in as mysql, you can omit the –user option.
If the command fails immediately and prints mysqld ended, you can find some information in the host_name.err file in the data directory.

– here is my output –

110123 23:28:35 mysqld_safe Logging to '/usr/local/mysql/data/ubuntu.home.network.err'.
110123 23:28:35 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

Now we need to set the root password.
Best way to do this is you can run:

./bin/mysql_secure_installation

Hit enter for none since you have nothing set yet.
- Set root password? [Y/n] Y
- Remove anonymous users? [Y/n] Y
- Disallow root login remotely? [Y/n] Y
- Remove test database and access to it? [Y/n] Y
- Reload privilege tables now? [Y/n] Y

Copy Configuration Files

You may want to optionally copy one of the provided configuration files from the support-files directory into your /etc directory. There are different sample configuration files
for different use cases, server types, and CPU and RAM configurations. If you want to use one of these standard files, you should copy it to /etc/my.cnf, or /etc/mysql/my.cnf
and edit and check the configuration before starting your MySQL server for the first time.
If you do not copy one of the standard configuration files,
the MySQL server will be started with the default settings.

cd /usr/local/src/mysql-5.5.19
cp support-files/my-huge.cnf /etc/my.cnf

Configure MySQL to start automatically. I have a feeling this isn’t necessary any more, but no harm trying to do it again

cd /usr/local/src/mysql-5.5.19
cp ./support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
update-rc.d mysql defaults

Test this. The following command should display a few dots then a star. Then hopefully MySQL is running!

/etc/init.d/mysql start

I prefer to add a link to the mysql binaries I use in /usr/bin than add MySQL’s bin directory to the path:

ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
ln -s /usr/local/mysql/bin/mysqldump /usr/bin/mysqldump

Now we can connect:

mysql -u root -p

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.19-log Source distribution
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)
 
mysql> exit
Bye

That’s it. Installation complete.