Free Blog Themes and Blog Templates

Running mysql instances of multiple versions on the same machine

Running multiple instances of the same binary of mysql is not a big deal. However trying to install and run mysql 5.0, 5.1 and 5.5 on the same machine... that's somewhat more tricky.
Mysql documentation does not give an exact way how to do that (AFAIK), only
doc1 and
doc2
is available as reference.

The first problem is around the installation. I you already have and rpm based install of one mysql version, trying to install an other one results in conflict.

# rpm -qa |grep -i mysql
MySQL-client-percona-highperf-5.0.87-b20.29.rhel5
MySQL-server-percona-highperf-5.0.87-b20.29.rhel5
MySQL-shared-percona-highperf-5.0.87-b20.29.rhel5
rpm -i MySQL-shared-community-5.1.42-0.rhel5.x86_64.rpm 
	package MySQL-shared-community-5.1.42-0.rhel5.x86_64 is already installed
	file /usr/lib64/libmysqlclient.so from install of MySQL-shared-community-5.1.42-0.rhel5.x86_64 conflicts with file from package MySQL-shared-percona-highperf-5.0.87-b20.29.rhel5.x86_64
	file /usr/lib64/libmysqlclient_r.so from install of MySQL-shared-community-5.1.42-0.rhel5.x86_64 conflicts with file from package MySQL-shared-percona-highperf-5.0.87-b20.29.rhel5.x86_64

So we need to install them to an alternate location.

rpm -i --relocate /=/opt/mysql_5.1.42/  MySQL-client-community-5.1.42-0.rhel5.x86_64.rpm
rpm -i --relocate /=/opt/mysql_5.1.42/ MySQL-shared-community-5.1.42-0.rhel5.x86_64.rpm
rpm -i --relocate /=/opt/mysql_5.1.42/ --noscripts MySQL-server-community-5.1.42-0.rhel5.x86_64.rpm

The last command's --noscript option is because the post install scripts in the package, responsible to install the basic tables of mysql (mysql_install_db) are not affected by the --relocate option, so they fail as well. So let us do the job of the post install script by hand.

mkdir -p /opt/mysql_5.1.42/data
/opt/mysql_5.1.42/usr/bin/mysql_install_db --datadir=/opt/mysql_5.1.42/data --basedir=/opt/mysql_5.1.42/usr/
ln -s /opt/mysql_5.1.42/etc/init.d/mysql /etc/init.d/mysql_5.1.42
chown -R mysql:mysql /opt/mysql_5.1.42/data

Also we define a new defaults file for this install: /opt/mysql_5.1.42/my.cnf

[mysqld]
basedir=/opt/mysql_5.1.42/usr/
datadir=/opt/mysql_5.1.42/data
socket=/opt/mysql_5.1.42/data/mysql.sock
user=mysql
language=/opt/mysql_5.1.42/usr/share/mysql/english/
port=3307

[mysqld_safe]
log-error=/var/log/mysqld_5.1.42.log
pid-file=/opt/mysql_5.1.42/data/mysqld_5.1_42.pid
ledir=/opt/mysql_5.1.42/usr/sbin

Obviously, we redefined the base and data directories, and also we created new socket, port, and pid file to avoid collisions. The later two new options requires some explaination. The language option shows the location of the errmsg.sys. If not not the correct file is loaded, the server will cry about "the incorrect number of lines" (and we have fake error messages as well :) ).

The option ledir is a hint for mysqld_safe for looking for mysqld-bin. If not defined, our script will start the mysql server at the "default" location: /usr/sbin/mysqld.

So let's have a try with the new instance of mysql.

#/opt/mysql_5.1.42/usr/sbin/mysqld  --defaults-file=/opt/mysql_5.1.42/my.cnf

#mysql -uroot  --port=3307 --host=127.0.0.1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.42-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Starting the mysql alone is nice, but using mysqld_safe or rather the rc script mysql.server is a bigger headache..
A big problem with the mysql.server rc script and the mysqld_safe script, that they use somewhat hardcoded values, and also use the my_print_defaults program to gather config info. The resulting algorithm to find the final set of attributes like socket,port, pid-file is so complex, that is hard to define our alternate path for mysql ( /opt/mysql_5.1.42 in our case) in an elegant way. So take a shortcut :) The mysql.server script is to be modified at two points. The line containing:

 parse_server_arguments `$print_defaults $extra_args mysqld server mysql_server mysql.server`

has to be replaced with the following 3 lines

 conf=/opt/mysql_5.1.42/my.cnf
 print_defaults="/opt/mysql_5.1.42/usr/bin/my_print_defaults" 
 parse_server_arguments `$print_defaults --defaults-file=$conf $extra_args mysqld server mysql_server mysql.server`

and also the call for mysqld_safe is to be modified:

 $bindir/mysqld_safe --defaults-file=/opt/mysql_5.1.42/my.cnf --datadir=$datadir --pid-file=$server_pid_file $other_args >/dev/null 2>&1 &

(it is important the --defaults-file option to be first in the row, otherwise it is ignored :( )

With these changes:

# /etc/init.d/mysql_5.1.42 start
Starting MySQL.                                            [  OK  ]

# mysql -uroot  --port=3307 --host=127.0.0.1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.42-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

And here we have our processes:

 6796 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/host20.terem4.hu.pid
 6833 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host20.terem4.hu.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock
 6835 logger -p daemon.err -t mysqld_safe -i -t mysqld
13742 /bin/sh /opt/mysql_5.1.42/usr//bin/mysqld_safe --defaults-file=/opt/mysql_5.1.42/my.cnf --datadir=/opt/mysql_5.1.42/var/lib/mysql --pid-file=/opt/mysql_5.1.42/data/host20.terem4.hu.pid
13839 /opt/mysql_5.1.42/usr/sbin/mysqld --defaults-file=/opt/mysql_5.1.42/my.cnf --basedir=/opt/mysql_5.1.42/usr/ --datadir=/opt/mysql_5.1.42/var/lib/mysql --user=mysql --log-error=/var/log/mysqld_5.1.42.log --pid-file=/opt/mysql_5.1.42/data/host20.terem4.hu.pid --socket=/opt/mysql_5.1.42/data/mysql.sock.5.1.42 --port=3307

Feedback awaiting moderation

This post has 220 feedbacks awaiting moderation...

Leave a comment


Your email address will not be revealed on this site.

Your URL will be displayed.
(Line breaks become <br />)
(Name, email & website)
(Allow users to contact you through a message form (your email will not be revealed.)
Miami Real Estate Blog Theme
Free Blog Themes and Templates