WBITT

We Bring In Tomorrows Technology

  • Full Screen
  • Wide Screen
  • Narrow Screen
  • Increase font size
  • Default font size
  • Decrease font size

Multiple versions of MySQL + phpmyAdmin on same machine

E-mail Print PDF
User Rating: / 3
PoorBest 

#########################################################################

Title: Multiple versions of MySQL + phpmyAdmin on same machine
Author: Muhammad Kamran Azeem ( This e-mail address is being protected from spambots. You need JavaScript enabled to view it )
Created:      20080228
Last Revised: 20080228
System :      Centos 5.1
Note:         This howto has very sharp edges. Please read completely before attempting to use it.
YOU HAVE BEEN WARNED.
#########################################################################

MySQL 5.0 already installed on system in rpm form, which came with the distribution.


MySQL 5.1 needs to be installed on the system, in addition to the mySQL 5.0 already installed and running. [Stop it before continuing.]


Download MySQL 5.1 from www.mysql.com . There are two options. Either download the binary form or download the pure source code.

I will use the binary one which should be much easier.

wget http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.23a-maria-alpha-linux-i686-glibc23.tar.gz/from/http://mysql.easynet.be/


tar xzf mysql-5.1.23a-maria-alpha-linux-i686-glibc23.tar.gz

mv mysql-5.1.23a-maria-alpha-linux-i686-glibc23 /usr/local/mysql

chown mysql:mysql /usr/local/mysql -R

cd /usr/local/mysql


# ./scripts/mysql_install_db --user=mysql   
Installing MySQL system tables...
ERROR: 1136  Column count doesn't match value count at row 1
080228 17:06:00 [ERROR] Aborting

080228 17:06:00 [Note] ./bin/mysqld: Shutdown complete


Installation of system tables failed!  Examine the logs in
/var/lib/mysql for more information.

....
...


Move the my.cnf of MySQL 5.0 existing in /etc to another location or rename.

# mv /etc/my.cnf /etc/my.cnf.orig

# rm -fr /usr/local/mysql/data/*

# scripts/mysql_install_db --user=mysql
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h testserver.pakcyber.local password 'new-password'

Alternatively you can run:
./bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd ./mysql-test ; perl mysql-test-run.pl

Please report any problems with the ./bin/mysqlbug script!

The latest information about MySQL is available at http://www.mysql.com/
Support MySQL by buying support/licenses from http://shop.mysql.com/
...
...



Start the mysql server to make sure it works.

# bin/mysqld_safe --user=mysql &
[1] 10815
[root@testserver mysql]# 080228 17:19:01 mysqld_safe Logging to '/usr/local/mysql/data/testserver.pakcyber.local.err'.
080228 17:19:01 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data


Try connecting to it:

[root@testserver mysql]# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
[root@testserver mysql]#

nmap shows that mysql is running.

The above error occured because this MySQL 5.1 didn't find a /etc/my.cnf and thus started with a socket file in /tmp as /tmp/mysql.sock


# mysql --socket=/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.23a-maria-alpha

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>                    



OK Lets shut it down and start it on a different port, and with a different my.cnf file.

kill `cat /usr/local/mysql/data/testserver.pakcyber.local.pid`

## /usr/local/mysql/bin/mysqld  --port=3306 --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --log-error=/usr/local/mysql/data/testserver.pakcyber.local.err --pid-file=/usr/local/mysql/data/testserver.pakcyber.local.pid


# mkdir /usr/local/mysql/etc
# mkdir /usr/local/mysql/log
# mkdir /usr/local/mysql/run

cp /etc/my.cnf.orig /usr/local/mysql/etc/my.cnf



chown mysql:mysql  /usr/local/mysql  -R



vi /usr/local/mysql/etc/my.cnf  # I will use this directory to store my.cnf for MySQL 5.1

[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
port=3307

[mysql.server]
user=mysql
basedir=/usr/local/mysql

[mysqld_safe]
log-error=/usr/local/mysql/log/mysqld.log
pid-file=/usr/local/mysql/run/mysqld.pid



Also setup the mysql51 startup script as following:-

cp /etc/init.d/mysqld /etc/init.d/mysql51d


It is tooooooo damn important to setup the /etc/init.d/mysql51d file with eyes fully open and mind fully awake.

vi /etc/init.d/mysql51d







service mysql51 start


chkconfig --level 35 mysql51 on




sometimes nmap will not show a port, even if it is running properly. and you will dis-assemble everything in desparation. Whereas the netstat will show you it is running. In normal situations you will be pulling your hair. However. The problem is that nmap will only show output of those services/ports which are defined in it's own nmap-services file. That means, even if you add two entries such as these in your /etc/services file. The nmap output will NOT show. Look at the output below:

/etc/services file:
-------------------
...
...
# Local services
mysql51         3307/tcp                        # mysql 5.1 server
mysql51         3307/udp                        # mysql 5.1 server



netstat output:
---------------
# netstat -antp | grep mysql
tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      15729/mysqld


nmap output:
------------

# nmap localhost
...
...
PORT    STATE SERVICE
22/tcp  open  ssh
25/tcp  open  smtp
111/tcp open  rpcbind
631/tcp open  ipp
705/tcp open  unknown



Notice no port 3307 shown above in nmap output.


Add the following line in /usr/share/nmap/nmap-services:
mysql51         3307/tcp


And check the nmap localhost command again:

# nmap localhost

Starting Nmap 4.11 ( http://www.insecure.org/nmap/ ) at 2008-02-28 19:16 PKT
Interesting ports on testserver.pakcyber.local (127.0.0.1):
Not shown: 1675 closed ports
PORT     STATE SERVICE
22/tcp   open  ssh
25/tcp   open  smtp
111/tcp  open  rpcbind
631/tcp  open  ipp
705/tcp  open  unknown
3307/tcp open  mysql51

Nmap finished: 1 IP address (1 host up) scanned in 0.251 seconds

See !


The "ps a" output shows:

# ps a | grep mysql

15641 pts/0    S      0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/etc/my.cnf --port=3307 --socket=/usr/local/mysql/data/mysql.sock --pid-file=/usr/local/mysql/run/mysqld.pid --log-error=/usr/local/mysql/log/mysqld.log
15729 pts/0    Sl     0:00 /usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --log-error=/usr/local/mysql/log/mysqld.log --pid-file=/usr/local/mysql/run/mysqld.pid --socket=/usr/local/mysql/data/mysql.sock --port=3307


Alhumdulillah. OK. All done.

Stop MySQL 5.1 server:
----------------------

service mysql51d stop


Make sure no mysql processes are running at the moment.


Let's start the default (RPM based) MySQL 5.0 server:

Make sure that /etc/my.cnf exists:

/etc/my.cnf # For mysql 5.0 server:
-----------------------------------

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


# service mysqld start
Starting MySQL:                                            [  OK  ]



And now start the (source based / binary) MySQL 5.1 server:
-----------------------------------------------------------

# service mysql51d start
Starting MySQL:                                            [  OK  ]


Alhumdulillah.


Check the nmap output now and the "ps a" output as well:
--------------------------------------------------------

# ps a | grep mysql
14928 pts/2    S+     0:00 tail -f /tmp/mysql.output
17255 pts/0    S      0:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid --log-error=/var/log/mysqld.log
17291 pts/0    Sl     0:00 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --socket=/var/lib/mysql/mysql.sock
17461 pts/0    S      0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/etc/my.cnf --port=3307 --socket=/usr/local/mysql/data/mysql.sock --pid-file=/usr/local/mysql/run/mysqld.pid --log-error=/usr/local/mysql/log/mysqld.log
17549 pts/0    Sl     0:00 /usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --log-error=/usr/local/mysql/log/mysqld.log --pid-file=/usr/local/mysql/run/mysqld.pid --socket=/usr/local/mysql/data/mysql.sock --port=3307


# nmap localhost
. . .
. . .
PORT     STATE SERVICE
22/tcp   open  ssh
25/tcp   open  smtp
111/tcp  open  rpcbind
631/tcp  open  ipp
705/tcp  open  unknown
3306/tcp open  mysql
3307/tcp open  mysql51


Last check, connect to both servers one by one:
------------------------------------------------

# mysql --port 3306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> quit
Bye


# mysql --port 3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> quit
Bye



ALHUMDULILLAH.

===============================

PHPmyAdmin two versions at same machine:-

Download the latest version from phpmyadmin.net :

cd /var/www

wget http://jaist.dl.sourceforge.net/sourceforge/phpmyadmin/phpMyAdmin-2.11.5-rc1-english.tar.bz2

tar xjf phpMyAdmin-2.11.5-rc1-english.tar.bz2

Remember, we have two mysql versions running on this machine, on two different ports (3306 and 3307). So we need to have two copies of phpmyadmin on this web servers.

cp -r phpMyAdmin-2.11.5-rc1-english phpmyadmin50 # to be connected with MySQL 5.0 on port 3306

cp -r phpMyAdmin-2.11.5-rc1-english phpmyadmin51 # to be connected with MySQL 5.1 on port 3307

chown apache:apache php* -R


vi /etc/httpd/conf.d/phpmyadmin.conf

Alias /phpmyadmin50  /var/www/phpmyadmin50
<Directory "/var/www/phpmyadmin50">
Options Indexes FollowSymLinks
AllowOverride All
Order allow,deny
Allow from all
</Directory>


Alias /phpmyadmin51  /var/www/phpmyadmin51
<Directory "/var/www/phpmyadmin51">
Options Indexes FollowSymLinks
AllowOverride All
Order allow,deny
Allow from all
</Directory>

You are here How To / Tutorials Multiple versions of MySQL + phpmyAdmin on same machine