#########################################################################
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>
