Adsense 1

Monday, August 04, 2008

Dual MySQL services in single server

Requirement:
To install one more mysql instance (server) and make it run on another port. The second instance must use a different data directory (ofcourse!). I assume that you have a working copy of mysql in the server which may be listening on any port (default: 3306) and storing data any where (default: /var/lib/mysql).

Steps:

# cd /usr/local/src/
downloaded mysql from here: http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.26-rc.tar.gz/from/pick#mirrors

# tar zxfv mysql-5.1.26-rc.tar.gz
# cd mysql-5.1.26-rc
See what options are available with ./configure --help

Below are options we need to consider:
  --bindir=DIR           user executables [EPREFIX/bin]
  --sbindir=DIR          system admin executables [EPREFIX/sbin]
  --libexecdir=DIR       program executables [EPREFIX/libexec]
  --datadir=DIR          read-only architecture-independent data [PREFIX/share]
  --sysconfdir=DIR       read-only single-machine data [PREFIX/etc]
  --sharedstatedir=DIR   modifiable architecture-independent data [PREFIX/com]
  --localstatedir=DIR    modifiable single-machine data [PREFIX/var]
  --libdir=DIR           object code libraries [EPREFIX/lib]
  --includedir=DIR       C header files [PREFIX/include]
  --oldincludedir=DIR    C header files for non-gcc [/usr/include] - installation will take some libs from here.
  --infodir=DIR          info documentation [PREFIX/info]
  --mandir=DIR           man documentation [PREFIX/man]

Note that all are created by default under PREFIX/EPREFIX. By default EPREFIX=PREFIX.

Some more options to look into:
--with-tcp-port=port-number (default 3306)
--with-mysqld-user=username - no default

--with-unix-socket-path=SOCKET
    Where to put the unix-domain socket.  SOCKET must be an absolute file name.

 --with-plugins=PLUGIN[,PLUGIN..]
                          Plugins to include in mysqld. (default is: none)
                          Must be a configuration name or a comma separated list of plugins.
                          Available configurations are: none max max-no-ndb all.
                          Available plugins are: partition daemon_example
                          ftexample archive blackhole csv example federated
                          heap innobase myisam myisammrg ndbcluster.

Also use this: CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti

I will be using /data/mysql for this instance's settings and data. The new instance should listen on port 33060. Let us start the config now:

# ./configure --prefix=/data/mysql --with-unix-socket-path=/data/mysql/mysql.sock --with-tcp-port=33060 --with-plugins=all
# make
# make install

This will take about 15 minutes

#  cp support-files/my-medium.cnf /data/mysql/my.cnf (If you want to config anything).
# cd /data/mysql/
# chown -R mysql .
# chown -R mysql .
# ls (there won't be var directory in that). We have to create it with the below command which creates all necessary databases.

# ./bin/mysql_install_db --user=mysql

Start the server:
# /data/mysql/bin/mysqld_safe &

And test it:
# mysql -P 33060 -p

It worked.

You can setup other accounts, change root password etc after this. Refer the below manual for all other options.

http://dev.mysql.com/doc/refman/5.1/en/quick-install.html